July 13, 2010 at 7:06 am
Recently I ran a SQL Profile trace to determine the reason for slow performance in our application.
Queries that should only take seconds to run were being labeled as having a duration of several minutes or more.
How can I find out why that is happening?
I am clueless.
July 13, 2010 at 8:10 am
You need to do three more things. First, you should also monitor the wait states and queues on the system so you know what is causing things to slow down. You'll also need to monitor blocking on the system to see if all you're running into is simple contention from one or a few processes. Finally, you need to look at the execution plans for the queries to identify why they're taking so long to run, assuming you've eliminated blocking as a cause.
You can simply look at the execution plans, but it will be an incomplete solution if you don't also identify if there are memory problems or contention issues. Any of the the three could cause problems or all three.
"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
July 13, 2010 at 9:07 am
I've had a similar problem that I have been unable to 100% identify. In my case, they are all parameterized quries that will be running subsecond in prod for days, and then all of a sudden they'll start timing out. While they are timing out in production, I'll take a copy of the parameterized query from my source, grab the parameters being passed to it in prod from the log file, and run an adhoc query using those values (separate query plan from the copy being used by the application) and it will run subsecond.
So I compare the query plan from my adhoc query to the query plan that is cached for the parameterized version, and they are different. So I ask my DBA to issue some DBCC commands to flush the plan cache, and production performance returns to normal.
The only solution I have found to make this problem go away is to include a USE PLAN clause and paste in the plan generated for my adhoc query.
Not perfect, but thought it might be helpful.
- Brandon
July 13, 2010 at 9:11 am
brandon_ledbetter (7/13/2010)
I've had a similar problem that I have been unable to 100% identify. In my case, they are all parameterized quries that will be running subsecond in prod for days, and then all of a sudden they'll start timing out. While they are timing out in production, I'll take a copy of the parameterized query from my source, grab the parameters being passed to it in prod from the log file, and run an adhoc query using those values (separate query plan from the copy being used by the application) and it will run subsecond.So I compare the query plan from my adhoc query to the query plan that is cached for the parameterized version, and they are different. So I ask my DBA to issue some DBCC commands to flush the plan cache, and production performance returns to normal.
The only solution I have found to make this problem go away is to include a USE PLAN clause and paste in the plan generated for my adhoc query.
Not perfect, but thought it might be helpful.
- Brandon
That sounds like a case of parameter sniffing.
BTW, flushing the procedure cache is a pretty heavy handed method for dealing with performance issues. Other users on the system can't be happy to have to recompile all queries.
"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
July 13, 2010 at 9:32 am
Thanks - good to have a name to identify the problem with. Suprising that the DBA's at the MS tech conferences (PDC, local TechFest, etc) I have been to weren't able to name the problem so fast...
Also, I have wondered if there was a way that they could just delete the suspect plan from the cache, and leave the rest alone, but the users are just happy to have the timeouts stop, and recompiling the queries doesn't seem to be heavy enough to warrant the time for me to investigate if this can be done, and how.
July 13, 2010 at 9:37 am
brandon_ledbetter (7/13/2010)
Thanks - good to have a name to identify the problem with. Suprising that the DBA's at the MS tech conferences (PDC, local TechFest, etc) I have been to weren't able to name the problem so fast...Also, I have wondered if there was a way that they could just delete the suspect plan from the cache, and leave the rest alone, but the users are just happy to have the timeouts stop, and recompiling the queries doesn't seem to be heavy enough to warrant the time for me to investigate if this can be done, and how.
Yep, you can identify the plan in cache and issue a drop for just that plan based on the sql_handle or the plan_handle. This is from BOL
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ]
Since you are running into something that sounds like, might not be, but sounds like, parameter sniffing, you could try some of the more traditional parameter sniffing solutions, such as using a query hint OPTIMIZE FOR, etc. But, you may still wind up having to force the plan as you did.
"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
July 13, 2010 at 10:08 am
I discovered that the queries in question were victims because of SQL jobs running causing them to die. Just seems odd that the duration would go so long. I can't make the mental connection between the two.
July 13, 2010 at 10:20 am
itsmeman (7/13/2010)
I discovered that the queries in question were victims because of SQL jobs running causing them to die. Just seems odd that the duration would go so long. I can't make the mental connection between the two.
Contention on resources. Process 1 normally runs in 30 seconds, but it needs a table that is in exclusive lock by Process 2. Process 2 runs in 5 minutes. Process 1 completes in 5 minutes and 30 seconds because it has to wait for Process 2 to run.
"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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply