Microsoft – SQL Server – Performance Tuning – Query Plans – Find Operators – “Sequence Project”

Introduction

Here is a quick follow-up to “Technical: Microsoft – SQL Server – Indexing Requirements – Comparing Windowing Functions against Correlated Join” (“https://danieladeniji.wordpress.com/2013/11/01/technical-microsoft-sql-server-windowing-function-indexing-requirements/“).

If you find your team using Windows Functions and you want to know how actively they are being used and also you will like to “cost” them a bit, please try the following Query.

Query – Utilizing Dynamic Management View (DMV)



WITH XMLNAMESPACES 
(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT top 30
				st.text as sqlText,
				pl.query_plan,
				ps.execution_count,
				ps.last_execution_time,
				ps.last_elapsed_time,
				ps.last_logical_reads,
				ps.last_logical_writes
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
where   pl.[query_plan].exist('data(//RelOp[@PhysicalOp="Sequence Project"])') = 1
and     pl.[query_plan].exist('data(//RelOp[@PhysicalOp="Sort"])') = 1

Order By (ps.last_logical_reads + ps.last_logical_writes) desc

OPTION (RECOMPILE);

Explanation:

  • Checks Query Plan and digs for Sequence Projects that suffer the curse of Sorts.

References

References – SQLTeam.com

References – Microsoft Customer Service and Support (CSS) SQL Support

References – SQL Server Engine Team

References – Simple-Talk (Fabiano Amorim)

References – ScaryDBA

References – Technet.Microsoft.com // Gallery // Script Center

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s