Difference between @@servername and SERVERPROPERTY(ServerName)

I could not reliably determine the server's network name using @@servername. I executed a SELECT @@servername and the values returned were different to the SERVERPROPRTY(ServerName). I was under the impression these values are the same , could explain to me the basic difference? Answer: As a first step , this is the text straight from the MSDN site - Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer. @@SERVERNAME returns the value from sys.sysservers.... Read more →

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 →