Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Showing slowest parts of SQL query

I’ve received interesting question/requirement few days ago:

“… When viewing a query plan graphically, we usually have to hunt for the most expensive operator.  In some queries, it’s hard to spot ‘em due to the high number of operators.  It would be cool to have a keyboard command that would jump to the most expensive operator, then the next most expensive, and so on. …”

I quite like this idea because from time to time I hit this issue as well. I was playing it with half an hour and found out that it is quite possible by doing this:

SET SHOWPLAN_XML ON
GO
-- query to investigate
SELECT * FROM sys.databases WHERE name = 'master'
GO
SET SHOWPLAN_XML OFF

It produces result containing full XML of query plan which you can freely parse directly in T-SQL or using SQL CLR. XSD schemas for that is available on Microsoft web here. By “XPathing” it, you can select e.g. TOP 10 most extensive operators and show it automatically to user as ordinary table. I was googling little bit and found this post by famous Pinal Dave. It schematically describes how to parse this XML and is very good start.

I was thinking about developing mighty stored procedure which would take your query as dynamic SQL string and would display top 10 bottlenecks in grid. You could bind this procedure to keyboard shortcut in SSMS and have quite nice performance tuning solution. Clean and fast.

It is not that easy because you can’t use SET SHOWPLAN_XML within stored procedure (it needs separate batch). I googled more and found sys.dm_exec_query_plan function which takes plan_handle argument and displays exactly the same as SET SHOWPLAN_XML (and you can use it within procedure). You can pull plan_handle value from sys.dm_exec_requests view which contains every request made to SQL Server instance. It needs more investigation and tuning but it could bring this mighty procedure alive.

I am wondering if you are interested in this solution or if you would use it only rarely. Please vote here.

 

Comments

Posted by Anonymous on 10 July 2011

Pingback from  Dew Drop – July 10, 2011 | Alvin Ashcraft's Morning Dew

Posted by Anonymous on 11 July 2011

Pingback from  Weekly Link Post 201 « Rhonda Tipton's WebLog

Posted by ian stirk on 13 July 2011

Hi,

I think you're really looking for the most costly parts of your routines...

To find the slowest part of a routine I typically run the below SQL. It tells me which individual lines of SQL within a given routine run the slowest. The SQL can be changed to report other things too.

Using the execution plan can be misleading, since it doesn’t take into account blocking etc. I’ve seen a situation where the execution plan said a given SQL statement contributed 3% to the cost of the plan, however when it was running it took more than 90% of the time.

Thanks

Ian

Author of SQL Server DMVs In Action www.amazon.com/.../1935182730

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)]

, CAST(total_worker_time * 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% CPU]

, CAST((total_elapsed_time - total_worker_time)* 100.0 /

total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]

, execution_count

, CAST(total_elapsed_time / 1000000.0 / execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)]

, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,

 ((CASE WHEN qs.statement_end_offset = -1

 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

 ELSE qs.statement_end_offset

 END - qs.statement_start_offset)/2) + 1) AS [Individual Query]

, SUBSTRING(qt.text,1,100) AS [Parent Query]

, DB_NAME(qt.dbid) AS DatabaseName

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

WHERE total_elapsed_time > 0

AND qt.text LIKE '%usp_LoadFileData%'  -- toDo: Replace this with the routine you are examining.

ORDER BY total_elapsed_time DESC

Posted by Jakub Dvorak on 14 July 2011

Wow, looks cool, I'll test it. Thanks.

Leave a Comment

Please register or log in to leave a comment.