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 →

Monitor SQL Server TempDB usage

Large queries spilling into tempdb such as SQL Server – SORT IN TEMPDB and Sort Warnings and large temporary tables can use Tempdb space very quickly and lead to outages. Use the DMVs : sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage How to Troubleshoot TempDB First Step - Comprehensive Tempdb Sizing exercise. There are many different methods , such as : a) create a workload b) running large maintenance jobs e.g index rebuild Second Step - monitoring Tempdb usage. As well as a SQL Server monitoring alerts solution based on events , such as disk full, there are some useful DMVs to drill into... Read more →