Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 Profiler Expand / Collapse
Author
Message
Posted Tuesday, July 13, 2010 7:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:59 AM
Points: 25, Visits: 80
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.
Post #951416
Posted Tuesday, July 13, 2010 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #951461
Posted Tuesday, July 13, 2010 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 8:38 AM
Points: 6, 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
Post #951533
Posted Tuesday, July 13, 2010 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #951545
Posted Tuesday, July 13, 2010 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 8:38 AM
Points: 6, 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.
Post #951593
Posted Tuesday, July 13, 2010 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #951602
Posted Tuesday, July 13, 2010 10:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:59 AM
Points: 25, Visits: 80
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.
Post #951635
Posted Tuesday, July 13, 2010 10:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #951643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse