Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: SQL 2005 - Slow Performance

    Hello,
    I am writing a fairly large program in Visual Basic 2008 with SQL 2005. The databases and the Enterprise manager for SQL 2005 is terribly slow. If I change servers to our SQL 2000 server the performance increase is phenomenal. Why is SQL 2005 so slow?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you are using a local instance of SQL 2005, it is likely assigning "auto_close" to all of the databases. This impacts performance significantly. Outside of that, I would have your DBA look over the setup of the 2005 server. There are probably hundreds of variables to look at.

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    It is running on a 64bit Server 2008 system that is spooky fast. I am working on an XP pro workstation with a gigabit network. The sql 2000 is on a 100Mbps LAN and it is just plain faster.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SQL 2008 includes a resource governor. You have not angered your DBA lately, have you? ;-)

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I had the same problem, it was either/or:

    1. Disable Parameter Sniffing: (as so)

    ie
    alter procedure usp_UserProcedure (@InAsOfDate Datetime)
    as
    declare @AsOfDate datetime
    set @AsOfDate = @InAsOfDate

    select * from table where AsOfDate = @AsOfDate
    /*
    Instead of directly using input variable (@InAsOfDate) in query, query plan not created correctly.
    */

    2. sp_updatestats (Do this nightly, and after you restore a 2000 db to 2005. Stats seem to stale right away in 2005.


    We had procedures that ran in under a minute on 2000, then increased to 20 minutes on 2005, implementing first change resolved issues.
    Last edited by PMASchmed; 04-03-09 at 15:04.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •