InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 14:30:25 GMT20http://www.sqlservercentral.com/Forums/Topic1422308-1550-1.aspx[quote][b]pooyan_pdm (2/22/2013)[/b][hr]What kind of overhead does this search have?[/quote]A lot. That XQuery is heavy on CPU on an unindexed columnDon'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.Fri, 22 Feb 2013 06:02:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1422308-1550-1.aspxIt 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?Fri, 22 Feb 2013 04:00:42 GMTpooyan_pdmhttp://www.sqlservercentral.com/Forums/Topic1422308-1550-1.aspx[quote][b]pooyan_pdm (2/21/2013)[/b][hr]Thanks for your reply. I was looking for a way other than searching the plan cache.[/quote]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)Fri, 22 Feb 2013 03:27:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1422308-1550-1.aspxHave 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 indexesThu, 21 Feb 2013 20:28:25 GMThappycat59http://www.sqlservercentral.com/Forums/Topic1422308-1550-1.aspxThanks 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.Thu, 21 Feb 2013 07:41:05 GMTpooyan_pdmhttp://www.sqlservercentral.com/Forums/Topic1422308-1550-1.aspx[code="sql"]-- 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;[/code]Thu, 21 Feb 2013 04:11:45 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1422308-1550-1.aspxHi thereI'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?Wed, 20 Feb 2013 14:32:24 GMTpooyan_pdm