SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Profiler


SQL Server 2005 Profiler

Author
Message
itsmeman
itsmeman
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 94
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.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40154 Visits: 32653
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
brandon_ledbetter
brandon_ledbetter
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 14
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40154 Visits: 32653
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
brandon_ledbetter
brandon_ledbetter
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 14
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.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40154 Visits: 32653
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
itsmeman
itsmeman
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 94
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.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40154 Visits: 32653
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search