Post reply

Unusual performance issues

  • September 26, 2006 at 11:52 pm

    #170452

    Hi, guys.

    This question (or something similar) may have been posted before, but I'm having difficulty using the search function on the site. It returns one page of results (claiming that there are six pages in total), but when I click the "next" link, I get a message saying "Sorry, no results found."

    Anyway, I've recently upgraded my server, and at the same time I upgraded from SQL Server 2000 to 2005. Before the upgrade, I detached my database from 2000 using the "Detach" option within the Enterprise Manager, and afterward, I reattached it to the new server through the same method. The process appeared to work successfully, and my initial tests proved that my data was indeed now accessible from the newer version of SQL Server.

    However, since this upgrade, I've noticed that the server's performance seems to have dropped rather steeply. Queries that used to take seconds on SQL 2000 can now take up to a minute. And what's worse, the delay doesn't seem to be consistent. Running the same query a dozen times will report random completion times, even when only a single user is connected and the server computer is not busy with any other tasks.

    In fact, the server computer reports no additional CPU activity while the database is being accessed, despite the erratic performance.

    I'm really hoping that this is something I can resolve by tweaking the settings in SQL Server 2005, as I don't want to have to re-write the application itself (which runs fine in 2000). Does 2005 introduce restrictions that weren't there in prior versions? Is there any logistical reason why my queries would run slower in 2005 than they did (and still do) in 2000?

    I've set up a few experiments with SQL Server 2005 Express on a different computer, and after detaching and reattaching the database, I get the same problem. It seems to be evident in all versions of 2005.

    I would very much appreciate any suggestions you may have, no matter how trivial. This is becoming quite frustrating.

    Thanks in advance.

    Andrew.

  • September 27, 2006 at 1:11 am

    #662645

    hi

    I have experienced the same problem

    try these

    1) exec sp_updatestats - Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

    2) DBCC UPDATEUSAGE (0) - Reports and corrects pages and row count inaccuracies in the catalog views. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

    Above information has been taken from SQL Server BOL. Refer to BOL before executing both the statements.

    "Keep Trying"

  • September 27, 2006 at 5:28 pm

    #662880

    Thanks for the tip, Chirag! Those commands seem to have improved performance a little, which is very nice to see. However, the application is still 2-3 times slower when using the 2005 database, as it is when I change it to point back to the old 2000 database. Could there be any settings on the server itself, external to my database, which might determine how fast queries are performed? I just don't understand why the newer version of SQL Server would be so much slower, especially when the hardware on the new server is so much newer and faster than our old 2000 box.

    If it helps at all, the application itself is Access-based, and is connected to the database server using an SQL Server ODBC DSN. Though it's worth mentioning that other applications (such as VB apps) which use the same DSN, also see the same drop in performance. All signs point to the new version of SQL Server, but I can't imagine why the default install would not be configured for maximum performance to begin with.

    Anyway, thanks again for your help.

  • September 27, 2006 at 8:39 pm

    #662890

    Andrew,

     

    I have the exact same problem on my system running the same configuration that you have stated with Access and a SQL Server ODBC DSN. I have found that mine also is doing the exact same thing. Having migrated to a new server we purchased another copy of SBS Server (SBS 2003R2) which had SQL 2005 bundled in it. As part of the migration I simply detached the existing database from the old SQL2000 box and reattached to the new box running SQL2005.

     

    At first all appeared to be working fine, however I then noticed that some queries were taking a long time to complete and even causing time out problems. Generally the overall performance is much the same as before, but instead it seems to stall randomly, sometimes even timing out completely. Other queries also just seem to run awfully slow. I have been through all the logs for the database and am unable to find any reason whatsoever for any of these failures.

     

    I to expected that the default install would have been configured for maximum performance, but have been unable to find any settings that would affect it. I also tried the suggestions from Chirag and have found no change.

     

    Would desperately love to hear from someone with any suggestions as to what could be causing this.

     

    Cheers, 

    Brendan 

  • September 28, 2006 at 12:48 am

    #662912

    hi

    r u running both sql 2000 and sql 2005 in ur new box. Is there any other resource intensive software running in ur new box.

    there are some topics in  BOL about upgrading from SQL 2000 to sql 2005.  u can check just to be sure

     

    "Keep Trying"

  • September 28, 2006 at 12:58 am

    #662916

    The new box is only running 2005, and it's a much faster computer than the old one (in terms of specifications). I still have our old server connected to the network, and if I re-direct the workstations to point back to it, the performance of our database software goes back to normal. The new server doesn't seem to be using much CPU or memory while its database is being accessed, but I still seem to be having these problems.

    Brendan, I'm glad to hear that I'm not the only one! And as a matter of fact, your problem sounds identical to mine. Sometimes a query will just pause for upwards of 30 seconds, without returning any results, nor in fact doing anything at all. During this time, there's no excess load on the server or the network that I can see. It's really starting to confuse me.

    Thanks again, Chirag. I hope we can resolve this soon!

  • September 28, 2006 at 5:20 am

    #662948

    Chirag,

     

    My box is a fresh install (SQL 9 Only) on a new machine which is far higher spec that the previous Version 8 box, also the machines are now on Gigabit Ethernet. I also need to run the client application on a member server and from this box the results are the same as a normal client machine on the network. In fact our setup is probably way over the top as there are only ever 2 or 3 client machines ever connected to the SQL instance at any one time. Throwing queries at the box doesn’t even tickle it resource wise when watching its performance, in fact the machine is so idle I was wondering if it was doing this to me out of sheer boredom

     

    I have searched many forums and tips sites and found plenty of advise that would indicate that the process of detaching from SQL2000 and reattaching to SQL2005 I used is in fact fine and advised as the best way of migrating.

     

    Andrew, my problem sounds identical to yours. The strange thing is how it works fine, then all of a sudden intermittently sits there for a good 30 seconds before either timing out or finally managing to return the results. Again like you I to have the old SQL2000 Server still running and when I connect it up and point to that, everything runs fine

     

    The only thing I negated to mention is that once I had migrated the database over to SQL 2005, I then changed it to version 9 compatibility mode assuming that SQL would automatically make the necessary changes it needed to. Was this a good thing to do?

     

    I have since tried running it back in Version 8 compatibility mode and the problem seems to remain.

     

    Tired, confused and in urgent need of a good DBA…..

     

     

  • September 28, 2006 at 1:15 pm

    #663119

    Switching from ODBC to OLEDB (especially if you are using Access as a front end) would be a good start to increase performance.  SQL 2005 treats ODBC as a legacy connection method so you would be much better off if you used OLEDB or a .NET provider.

    Joshua Perry
    http://www.greenarrow.net

  • September 28, 2006 at 7:16 pm

    #663207

    Having run a few more tests since the original post, it seems as though the problem may be slightly different to what I first thought. Rather than there being a consistent decrease in performance, it looks like I'm simply encountering a pause or delay every few seconds, which is adding to the time that it takes to run a batch of queries in sequence. It'll run fine for a few seconds, then it'll pause for upwards of ten or fifteen seconds, then it'll return to normal again. On the client side, this translates into a massive decrease in overall performance, simply because these pauses are occurring at least once in almost every complex operation that is performed.

    Josh, I appreciate the info. Are you saying that SQL Server 2005 doesn't work as well with ODBC drivers as Server 2000 does? And thus, that there's no hope of having my application run as well on the new server as it always used to (and still does) on the old one? That's rather depressing, as I'd prefer not to have to re-write my applications just because we upgraded our server box. There's no way to configure 2005 to use ODBC drivers as efficiently as the older versions do?

    Thanks again for the advice. I hope I can resolve this without having to change the way the application accesses the database. Fingers are crossed.

  • October 3, 2006 at 10:44 pm

    #664003

    hi

    Iam curious to know whether you have made any progress. Have you solved your problem or are you any where near to solving it?

     

    "Keep Trying"

  • August 26, 2007 at 4:54 am

    #729448

    Hi

    I experience the same problem. Have you got solution?

    Regards

    Kuba

     

  • August 26, 2007 at 9:01 pm

    #729469

    You mention that the application's performance runs slow, but have you tried running the same query on the server itself in SSMS?  You can quickly rule out SQL Server as the cause of the problem if the query run directly on the server consistently runs without the hang you describe.

    What is your network environment like?  Is the new server on the same network segment or VLAN as the old one?  Any known DNS issues?

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • August 26, 2007 at 11:36 pm

    #729480

    This sounds familiar...

    How much memory is on the server? What service pack of SQL 2005 are you using?

    There were some memory-related bugs in SQL 2005 RTM and SP1. If you have more than 20 GB memory and are running SP1 or RTM, upgrade to SP2 as soon as you can

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 27, 2007 at 2:14 am

    #729491

    It runs well in SSMS. Problems appear in Query Analyzer and ADO.

    I use stand alone notebook with SQL Server Express Edition SP2 (latest).

    Maybe I should try new version of ADO. However, even if it helps, it will be difficult to modify the application (Delphi)  and maintain two versions for our customers.

     

  • August 27, 2007 at 2:35 pm

    #729650

    Maybe crazy question but are you running any anti virus software? Seen a web server do something similar a while ago and altering how the anti virus software was configured sorted it out.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply