Microsoft SQL Server XQuery Sample for DBAs

Back in March 2006, Bob Beauchemin blogged about how XQuery could be a useful tool for DBAs during normal administrative and tuning tasks. I decided to take his sample and tweak it a bit to make it a little easier to comprehend.

This SP allows you to look at cached query statistics and plans in the current database in order to find potentially evil physical operations like hash matches, table scans, index scans, etc.

What I came up with is shown below:

/* DBAdminLookForPhysicalOps ================================================================
Description : Query current database to find physical operations like Table scans,
clustered index scans, Hash matches, etc.
Example Usage: EXEC DBAdminLookForPhysicalOps ‘Clustered Index Scan’
EXEC DBAdminLookForPhysicalOps ‘Hash Match’
EXEC DBAdminLookForPhysicalOps ‘Table Scan’
EXEC DBAdminLookForPhysicalOps ‘Index Scan’
Author: Glenn Berry
Date: 12/11/2006
Input:
Output:
Used By: Only used to maintain the database

Last Modified Developer Description
———————————————————————————————————–
12/11/2006 Glenn Berry Created
=========================================================================================================*/
CREATE PROCEDURE [dbo].[DBAdminLookForPhysicalOps]
(@op VARCHAR(30))
AS

SET NOCOUNT ON;

SELECT sql.text, qs.Execution_Count, qs.last_execution_time, qs.total_worker_time, qs.total_elapsed_time,
qs.total_logical_reads, qs.total_logical_writes
,p.query_plan
–,qs.*, p.* — add these back in if you want a lot more data
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p
WHERE query_plan.exist(‘
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
‘) = 1
AND sql.dbid = DB_ID()
ORDER BY qs.Execution_Count DESC

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s