September 17, 2009 at 4:54 am
Hi all
I have a 2005 server which is used purely for reporting, and is populated with data each morning. Most tables are updated using Insert/Update/Delete routines and as such will have minimal changes made to them.
What I have noticed is that if I run a sp first thing (using SSMS 2005) when I get into work it might take up to 2 mins, subsequently this will take a few seconds. The sp is then okay for a few hours, however if it hasn't been run for a while the query might then take 30 seconds, subsequently back to a few seconds. Then the next day the cycle is repeated.
Could anybody point me in the right direction here? It's as if SSMS has a local cache that is cleared after a a few hours, and then the execution plans are discarded each morning.
Regards
Dan
September 17, 2009 at 7:02 am
Please post your stored procedure (and table layout/ddl), as it may be re-compiling on every execution thus causing the change in execution times. Thanks.
September 17, 2009 at 7:12 am
Hi
I've attached a copy of one of the sps.
Thanks
Dan.
September 17, 2009 at 7:27 am
It sounds like one of two, or even a combination of two, things; caching of the data, and compiling of the execution plans. I'd suggest firing up a server side trace and look at the following events: Performance:Performance statistics, TSQL:SQL:StmtRecompile, TSQL:SQL:BatchCompleted,Stored Procedures:RPC:Completed.
This information will tell you what's happening with the performance of your queries, if you're getting cache hits or misses, compiles or recompiles, all on a per query basis. You can then drill into the cache if you identify queries that are getting lots of cache misses or recompiles and see how long the compile or recompile takes using DMV's.
Do you have big load processes or something like that running over night that would be cleaning out the cache?
"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
September 17, 2009 at 7:41 am
I wrote an import routine that takes approx 3 hours to run, and it imports approx 60 tables. There are a couple of tables with >2 million rows. I'm not sure if this would clear the cache or not?
I use a view to import from another database, for instance:
UPDATE report_table
SET a.cola=b.cola etc.
FROM report_table a
INNER JOIN v_import_table b ON a.id=b.id
WHERE a.colab.cola
INSERT report_table
SELECT * FROM v_import_table b WHERE NOT EXISTS (SELECT '' FROM report_table WHERE id=b.id)
DELETE FROM report_table a
FROM report_table a WHERE NOT EXISTS (SELECT '' FROM v_import_table WHERE id=a.id)
Do you think the view may be the problem?
I have tried the profiler a number of times but unfortunately not first thing in the morning, I will have to try this tomorrow to see what results I get.
Thanks
Dan
September 17, 2009 at 11:39 am
The view all by itself isn't the issue. It really is about caching the plan and caching the data. From the sounds of it, your data cache is well and truly flushed by the overnight process, so that would explain some, maybe all, of the morning slow down. The stuff I sent concentrates on identifying compiles and recompiles if the procedure cache was an issue.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply