August 18, 2009 at 3:16 am
Hi Guys
I was using profiler to monitor a couple of queries over DB server, I can see them taking quite a long time from the trace, but as soon as I restart SQL service, they all seem to run a bit better and with time again it suddenly gets worse.
Are there any pointers to this problem, and from looking at perfmon. Are there any guides to the counters and also acceptable thresholds ?
Thanks.
August 18, 2009 at 4:09 am
Check the space in tempdb or the HD partition where tempdb is placed. Due to space constraint tempdb may not be able expand. When you restart the SQL Service it recreate tempdb and then the performance imporves.
Other thing you can check is the autogrowth parameters, it should be by bigger percentage and unrestricted file growth.
August 18, 2009 at 12:37 pm
Are they ad hoc queries or stored procedures? Are they parameterized? Are they the exact same queries including parameters?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2009 at 1:33 pm
Hi
They are stored procedures with different parameters.
August 18, 2009 at 2:20 pm
Could be parameter sniffing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2009 at 3:21 pm
How can this be proven ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply