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... Read more →

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... Read more →

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... Read more →

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... Read more →

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... Read more →