March 4, 2008 at 11:46 am
Here's the scenario: we are having a performance issue on one of the databases in production. Production server is 4-CPU, 16GB RAM, 64-bit WIndows 2003 on SQL2005 64-bit SP2 on a HP EVA SAN. This db is 1.5GB with a 200MB transaction log. The perfomance problem was found on a store proc that does SQL select query and using table variable to INSERT data, the result of that table variable insert is displayed to the end user; it takes 6.5 mins compareas to 1 sec. This db is in FULL recovery mode with trans log backed up every 2 hrs. The 200MB trans log mentioned above stays pretty stable and always < 200MB.
So when I restored that db to a much weaker 2-CPU, 4GB RAM, Windows 2005 32-Bit SQL 2005 on SP2 test box. It would generates the same bad 6.5 mins performance UNLESS I do sp_updatestats after the restore, then the query would return its great performance of 1 sec. Now, here's the strange part, as soon as SET its RECOVERY to FULL (with or without update its stats), the query would perform poorly again.
SQL Profiler shows a bunch of index scans on the poor performance scenarios.
So, we knows the symptoms is indexes is not update todate (thus sp_updatestats would fixed it), but what is the problem with FULL recovery mode and more importantly, how would I rule other things out and fix it. Thanks in advance.
March 4, 2008 at 12:39 pm
Just a guess, but are there multiple transactions within the query?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 5, 2008 at 8:03 am
Did you have a chance to compare the execution plans for that stored procedure, when it was performing well as well as when it was performing poorly? The differences may turn up some hints.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply