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

Track queries that caused index scan Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 2:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:20 PM
Points: 114, Visits: 396
Hi there
I'm looking for a way to find the queries that are causing index scans on the SQL Server instance.I know it's easy to do so with Extended_Events in SQL server 2012 but the "sqlserver.scan_started" event does not exists in 2008 version of extended events collection. By using SQL Profiler it's possible to track indexes and tables which has been scanned but I can't easily figure out which SQL query caused the scan and have to search the plan catch to find the queries referencing the index that has been scanned.
Is there a better way to do so?



Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1422308
Posted Thursday, February 21, 2013 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 2,837, Visits: 3,954
-- Get all SQL Statements with "table scan" in cached query plan 
;WITH
XMLNAMESPACES
(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
,EQS AS
(SELECT EQS.plan_handle
,SUM(EQS.execution_count) AS ExecutionCount
,SUM(EQS.total_worker_time) AS TotalWorkTime
,SUM(EQS.total_logical_reads) AS TotalLogicalReads
,SUM(EQS.total_logical_writes) AS TotalLogicalWrites
,SUM(EQS.total_elapsed_time) AS TotalElapsedTime
,MAX(EQS.last_execution_time) AS LastExecutionTime
FROM sys.dm_exec_query_stats AS EQS
GROUP BY EQS.plan_handle)
SELECT EQS.[ExecutionCount]
,EQS.[TotalWorkTime]
,EQS.[TotalLogicalReads]
,EQS.[TotalLogicalWrites]
,EQS.[TotalElapsedTime]
,EQS.[LastExecutionTime]
,ECP.[objtype] AS [ObjectType]
,ECP.[cacheobjtype] AS [CacheObjectType]
,DB_NAME(EST.[dbid]) AS [DatabaseName]
,OBJECT_NAME(EST.[objectid], EST.[dbid]) AS [ObjectName]
,EST.[text] AS [Statement]
,EQP.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_cached_plans AS ECP
INNER JOIN EQS
ON ECP.plan_handle = EQS.plan_handle
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
WHERE EQP.[query_plan].exist('data(//RelOp[@PhysicalOp="Table Scan"][@EstimateRows * @AvgRowSize > 50000.0][1])') = 1
-- Optional filters
AND EQS.[ExecutionCount] > 1 -- No Ad-Hoc queries
AND ECP.[usecounts] > 1
ORDER BY EQS.TotalElapsedTime DESC
,EQS.ExecutionCount DESC;



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1422493
Posted Thursday, February 21, 2013 7:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:20 PM
Points: 114, Visits: 396
Thanks for your reply. I was looking for a way other than searching the plan cache.
I was using the same DMVs but your script is a little bit more complete.


Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1422588
Posted Thursday, February 21, 2013 8:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:26 PM
Points: 2,926, Visits: 2,529
Have a look at [sys].[dm_db_index_usage_stats] - it has info about scans and might be what you are after. You will need to join to sys.objects and sys.indexes to get the names of the tables and indexes


Post #1422851
Posted Friday, February 22, 2013 3:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
pooyan_pdm (2/21/2013)
Thanks for your reply. I was looking for a way other than searching the plan cache.


The only way to find out what queries had specific query plan operators is to search the query plan cache.

Also, be careful about scan started and those kind of events, that doesn't necessarily mean a full index scan. Could be a range scan (ie an index seek)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1422957
Posted Friday, February 22, 2013 4:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:20 PM
Points: 114, Visits: 396
It takes almost 6 min for sql server to look into the plan catch on our production. What kind of overhead does this search have? I guess it should put some latches on the memory pages containing the plans am I right?

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1422970
Posted Friday, February 22, 2013 6:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
pooyan_pdm (2/22/2013)
What kind of overhead does this search have?


A lot. That XQuery is heavy on CPU on an unindexed column

Don't do that on a production server. At most, pull the plan cache unfiltered to a table, move the table to another server, do your filtering there.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse