Find Table Scans in Query Plan Cache

Question: I’m managing a SQL Server with queries running much slower than normal. I’ve analysed the sys.dm_db_index_usage_stats  for heap  scans. To identify the heap statistics I identified rows where the column index_id returned a value of zero.

I’ve identified some heaps with a very high level of usage . How can I find the queries which are creating these large amounts of user scans?

Answer: Using the  sys.dm_db_index_usage_stats  is a good method  to identify heaps. Checking the user scans is a good way of finding clues for inefficient queries. It does not guarantee these queries are the root cause, but it gives you an insight to which queries are consuming high amounts of resources.

Once you’ve found your table – use this query to find queries, in the plan cache. It is possible the query plan cache has been cleared via space management, a SQL Server restart or a DBCC FREESYSTEMCACHE . In that case it won’t be possible to find the query.

This query uses the sys.dm_exec_cached_plans and sys.dm_exec_query_plan SQL Server DMV. Read more on Top 5 SQL Server DMV for Index Analysis - SQL Server DBA

 

 

DECLARE @Table_Name sysname = 'my_table_name';
;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.plan_handle
,operators.value('(TableScan/Object/@Schema)[1]','sysname') AS Schema_Name
,operators.value('(TableScan/Object/@Table)[1]','sysname') AS Table_Name
,operators.value('(TableScan/Object/@Index)[1]','sysname') AS Index_Name
,operators.value('@PhysicalOp','nvarchar(50)') AS Physical_Operator
,cp.usecounts
,qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//RelOp') rel(operators)
WHERE operators.value('@PhysicalOp','nvarchar(60)') IN ('Table Scan')
AND operators.value('(TableScan/Object/@Table)[1]','sysname') = QUOTENAME(@Table_Name,'[');

Read More on Page Life Expectancy, query timeouts, root cause analysis

SQL Server page life expectancy and memory bottleneck

SQL Server – How to troubleshoot query timeouts

Find the Root cause of a SQL Server Performance downgrade.

 

How to list all SQL Server Caches and use DBCC FREESYSTEMCACHE to clear

 

Question: How can I list all SQL Server Caches? 

Rather than clearing down all the caches , I'd like to have control of every cache.  How can I generate a DBCC FREESYSTEMCACHE statement for every cache?

Answer: The sys.dm_os_memory_clerks DMV returns the set of all memory clerks that are currently active in the instance of SQL Server. Read more on the difference between sys.dm_os_memory_clerks and Sys.dm_os_memory_objects  on  Memory objects allocated by SQL Server

Using the information derived from sys.dm_os_memory_clerks , you can generate DBCC FREESYSTEMCACHE statements for every cache.

Example 1 : Use FREESYSTEMCACHE to clear just the  SQL Plans

DBCC FREESYSTEMCACHE(‘SQL Plans’)

Example 2 : List all SQL Server caches and generate DBCC FREESYSTEMCACHE statements

select 'DBCC freesystemcache ('+''''+name+''''+')'  from   sys.dm_os_memory_clerks group by name

Example 3 : Clear all caches . Don’t run in PROD

DBCC FREESYSTEMCACHE(‘ALL’)

These are strong commands . Before executing in a busy Production environment consider the impact and whether it's acceptable to the users.

Theses commands could clear a problem but spend some time to Find the Root cause of a SQL Server Performance downgrade.

 

Read More on PLE ,sys.dm_os_memory_clerks,Query Timeouts

SQL Server page life expectancy and memory bottleneck

Sys.dm_os_memory_clerks and AWE memory allocation

SQL Server – How to troubleshoot query timeouts

SQL Server RUNNABLE – SUSPENDED – RUNNING status model

Question: When I’m viewing activity on SQL Server , for example, sp_who2 – the status column displays different states – RUNNABLE – SUSPENDED – RUNNING.   Could you explain the difference?

Answer: Some background information on the SQL Schedulers , will make understanding the RUNNABLE – SUSPENDED – RUNNING  model clearer.

Schedulers are made up of three parts . A thread cycles though these three parts

1) Processor

2) Waiter list – threads waiting for resources.    Use Sys.dm_os_waiting_tasks to view resource waits for the resources

3) Runnable – thread has all the resources and waiting for the processor. Explore runnable status with the  sys.dm_os_schedulers and sys.dm_exec_requests  DMVs

This leads us into the RUNNABLE – SUSPENDED – RUNNING model definitions

1)      RUNNING – thread is executing on the server

2)      SUSPENDED – thread is waiting for resources to become available.

3)      RUNNABLE – the thread is waiting to execute on the processor

 

Why does RUNNING transition to SUSPENDED ? Thread is executing and if waiting for a resource moves to SUSPENDED into the waiter list 

Why does SUSPENDED  transition into RUNNABLE? The resource is now available and moves to the bottom of the RUNNABLE queue. 

Why does RUNNABLE transition into RUNNING?  Top spid at head of RUNNABLE queue moves to processor

Read More on using DMV for troubleshooting sql queries

Top 5 SQL Server DMV for Index Analysis

SQL Server - Find all DMV and DMF

SQL Server sql_handle returns sql statement

Calculate sql row size

Question: How can I calculate the sql row size?

 Answer: Calculating the  row-level row size is complicated and challenging.  One of the key problems is SQL Server defines space at the extent level .

 An extent is made up of 8 x 8 KB physically  contiguous pages .  The  complication for calculating a row size is : Even if one row exists – for example 4 bytes , an extent has to be allocated.

Read this post for information on the difference between uniform and mixed extents - Disk IO performance , disk block size tuning and SQL Servers

 There is complicated formula on the MSDN Online - http://msdn.microsoft.com/en-us/library/ms189124.aspx to predict the space required to store data in a heap

There are some reliable methods of estimating  row sizes . Using DBCC SHOWCONTIG  , sys.dm_db_index_physical_stats  and SysColumns.

Each method offers a slightly different perspective  on row size.

 

 

---METHOD 1  : DBCC SHOWCONTIG

dbcc showcontig ('TableName') with tableresults

--check max min and average record size



--METHOD 2 : sys.dm_db_index_physical_stats
--dbcc showcontig is to be deprecated 
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'DatabaseName'), OBJECT_ID(N'TableName'), NULL, NULL , 'DETAILED')


--METHOD 3 : SysColumns

use db_name
go
create table ##tmpRowSize (TableName varchar(100),RowSizeDefinition int)
exec sp_msforeachtable 'INSERT INTO ##tmpRowSize Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '
select * from ##tmpRowSize order by RowSizeDefinition  desc
drop table ##tmpRowSize

 Read More

SQL Server - How to find the largest sql index and table size

SQL Server - Calculate table size with existing data

Storage Checklist FAQ

Amount of sql server processors with sys.dm_os_schedulers

I needed to establish the amount of processors available to a SQL Server. A SQL Server scheduler is mapped to a single logical processor, so using this query you can establish the amount of processors. Any scheduler_id < 255 is a scheduler.

It gets lightly more complicated if hyperthreading is enabled. . Find out from the server administrator , use a product  such as the coreinfo or this Powershell script  - Number of physical processors with Powershell – number of cores to establish if hyperthreading is turned on.

If hyperthreading is enabled – this equates to double the schedulers. 

SELECT * 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255;

 Read More

SOS_SCHEDULER_YIELD and how to reduce it - SQL Server DBA

Affinity Mask on SQL Server 2008 R2 - SQL Server DBA

How to report a SQL Server performance problem - SQL Server DBA