Script to Check TempDB Speed

The following script adopted from Brent Ozar’s blog on his TempDB Performance and Configuration topic. I strongly encourage you to pay a visit to his site.

This query hits the dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write (and read) requests:

SELECT files.physical_name, files.name,
    stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
    stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
    ON stats.database_id = files.database_id
    AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

Brent Ozar suggested to look for two things from the result generate from the script:

  • Are writes being evenly distributed between data files?
  • Are writes finishing in 20ms or less?

If either of those question is no, then we have some performance tuning work need to kick in.

How to: Identify Blocking Queries

The following scripts will generate a list of blocking SPIDs in the SQL Server instance

SELECT
	convert(nvarchar(100), SERVERPROPERTY('Servername')) AS [Server Name],	
	db.name AS [Database Name],
	tl.request_session_id AS [SPID],
	wt.blocking_session_id AS [Blk By],
	OBJECT_NAME(p.OBJECT_ID) AS [Blk Object Name],
	tl.resource_type AS [Resource Type],
	h1.TEXT AS [Requesting Text],
	h2.TEXT AS [Blocking Text],
	tl.request_mode AS [Lock Mode]
FROM 
	sys.dm_tran_locks AS tl
	INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
	INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
	INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
	INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
	INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
	CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
	CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Reference:

Identify Blocking Queries
http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

Find Blocking Processes
http://www.techrepublic.com/blog/datacenter/find-blocking-processes-using-recursion-in-sql-server-2005/275

How to: Performance Tunining on Long Wait Transaction

Most of the time DBA will fire the “sp_who2 active” command to find out the active session on the SQL Server instance and quickly kill the dead lock as soon as he/she find it blocking one another. While, there’s always other way to find out more detail about it before you pull your trigger to kill the running transaction. Thus, you can avoid possible lost of data after you kill the running transaction.

Although you can look at wait statistics to understand specifically what is causing the server to slow down. It’s a great metrics for understanding the system as a whole. You could run a query against sys.dm_os_wait_stats and other by the number of currently waiting task, the cumulative wait time or the max wait time to see what, in general is causing the server to run slow.

To see what’s currently running on the server, you could run a query against the Dynamic Management Object (DMO) sys.dm_exec_requests.

SELECT 
  der.session_id , --internal identifier for the running session
  der.status , --determines if the query is active or waiting
  der.start_time , --gives you an idea when the query started
  der.command , --the type of command involved
  der.database_id , --which database you're connected to
  der.user_id , --which login is running the command
  der.blocking_session_id , --session id of blocking session
  der.wait_type , -- what is the waiting session it waiting on
  der.wait_time , --how long has it been waiting
  der.last_wait_type , --what caused it to last wait
  der.cpu_time , --how much of the CPU has been used
  der.total_elapsed_time , --how long has the command been running
  der.reads , --has the command hit the disk for information
  der.writes , --how much information was written to the disk
  der.logical_reads --how many reads came out of memory
FROM sys.dm_exec_requests AS der;

After you capture the long running transaction that probably use plenty of the resources. Now you need to figure out what is the query this session was running and its execution plan. The query below will help you to pull all this information together in a hurry.

SELECT SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
(der.statement_end_offset - der.statement_start_offset )
/ 2 + 1)
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.session_id = 442;

Reference :
Red-Gate.com – The Top 5 Hard-earned Lessons of a DBA

How to: Find Currently Running Queries

You can use sys.dm_exec_requests DMV to list all requests. This view also contains a hash map of the SQL text – sql_handle, which can be passed to DMF sys.dm_exec_sql_text to get the query text:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text
FROM        sys.dm_exec_requests REQUEST
CROSS APPLY
            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

In case you are trying to find the query which is causing blocking, you can find the blocking session id by using sp_who or sp_who3.

Once you locate the blocking session id, it can be used to filter above results:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text
FROM        sys.dm_exec_requests REQUEST
CROSS APPLY
            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY
WHERE REQUEST.session_id = 58

How to: Change The Tempdb File Size

You could have been accidentally increased your tempdb data file to more than it actually needs and you have difficultly to reduce the size. Probably you have tried to resize it from the database properties or shrink the file, unfortunately it doesn’t make any changes to the file. The files size remain big.

For instance, your tempdb has 1GB, you accidentally increase it to 81GB which you actually plan to increase it to 8GB. Here is the solution.

First, you should reduce your tempdb file to the size smaller than your desire size.

DBCC SHRINKFILE (N'tempdev', 1024000);  --1GB

After that, you can change the tempdb file size to your desire file size.

ALTER DATABASE [tempdb] MODIFY FILE (
	NAME = N'tempdev', 
	SIZE = 8388608KB );  --8GB
GO;

Activity Monitor Script to Find Head Blocker

I have been searching so long for this useful script. It produce similar result while you execute sp_who2 command. The best thing is you can filter the result. The following script is origin from SQLFRNDZ.com.

This is very handy useful script in production environment. Even the activity monitor does the same but when there is high server high activity then your activity monitor hangs and do not respond. Also it is not recommended to use activity monitor for long in production environments as it uses high resources.

SELECT
 [Session ID] = s.session_id,
 [User Process] = CONVERT(CHAR(1), s.is_user_process),
 [Login] = s.login_name,
 [Database] = ISNULL(db_name(p.dbid), N''),
 [Task State] = ISNULL(t.task_state, N''),
 [Command] = ISNULL(r.command, N''),
 [Application] = ISNULL(s.program_name, N''),
 [Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
 [Wait Type] = ISNULL(w.wait_type, N''),
 [Wait Resource] = ISNULL(w.resource_description, N''),
 [Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
 [Head Blocker] =
 CASE
 -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
 WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
 -- session is either not blocking someone, or is blocking someone but is blocked by another party
 ELSE ''
 END,
 [Total CPU (ms)] = s.cpu_time,
 [Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
 [Memory Use (KB)] = s.memory_usage * 8192 / 1024,
 [Open Transactions] = ISNULL(r.open_transaction_count,0),
 [Login Time] = s.login_time,
 [Last Request Start Time] = s.last_request_start_time,
 [Host Name] = ISNULL(s.host_name, N''),
 [Net Address] = ISNULL(c.client_net_address, N''),
 [Execution Context ID] = ISNULL(t.exec_context_id, 0),
 [Request ID] = ISNULL(r.request_id, 0),
 [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
 -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
 -- waiting for several different threads. This will cause that thread to show up in multiple rows
 -- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
 -- and use it as representative of the other wait relationships this thread is involved in.
 SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
 FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;

I have also attached the screenshot from SQLFRINDZ.com as below


	

How to: Check SQL Process % Complete

The following scripts origin from Mehernosh to help you to find out (SQL Server 2005 and onwards or with compatibility modes of 90 and higher) how long a particular Backup or restore on a database was taking or elapsed time.

USE MASTER
GO
SELECT
	SESSION_ID,
	'[' + CAST(DATABASE_ID AS VARCHAR(10)) + '] ' + DB_NAME(DATABASE_ID) AS [DATABASE],
	PERCENT_COMPLETE, START_TIME, STATUS, COMMAND,
	DATEADD(MS, ESTIMATED_COMPLETION_TIME, GETDATE()) AS ESTIMATED_COMPLETION_TIME,
	CPU_TIME
FROM SYS.DM_EXEC_REQUESTS
--Apply this Where Clause Filter if you need to check specific events
--such as Backups, Restores, Index et al.
WHERE
	COMMAND LIKE '%BACKUP%' OR
	COMMAND LIKE '%RESTORE%' OR
	COMMAND LIKE '%INDEX%' OR
	COMMAND LIKE '%DBCC%'

Breaking Down TempDB Contention

Fellow MVP Glenn Berry has a blog post with some neat scripts using the sys.dm_os_wait_stats DMV, the script will show you what kind of wait is most prevalent on your server.

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

-- Common Significant I/O Wait types with BOL explanations

-- *** I/O Related Waits ***
-- ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish
-- IO_COMPLETION        Occurs while waiting for I/O operations to complete.
--                      This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
--                      as PAGEIOLATCH_* waits
-- PAGEIOLATCH_SH        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
--                      The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
-- PAGEIOLATCH_EX        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
--                      The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
-- WRITELOG             Occurs while waiting for a log flush to complete.
--                      Common operations that cause log flushes are checkpoints and transaction commits.
-- PAGELATCH_EX            Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
--                      The latch request is in Exclusive mode.
-- BACKUPIO                Occurs when a backup task is waiting

If you see that it’s PAGELATCH waits, you can use this following script from newly-minted MCM and Microsoft DBA Robert Davis. It uses the sys.dm_os_waiting_tasks DMV to break apart the wait resource and let you know what’s being waited on in tempdb.

SELECT session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
      ResourceType = Case
WHEN Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
            When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
            When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
            Else 'Is Not PFS, GAM, or SGAM page'
            End
FROM sys.dm_os_waiting_tasks
WHERE wait_type Like 'PAGE%LATCH_%'
AND resource_description Like '2:%'

Query to Identify Current Activity

This query will shows you a significant amount of real-time data including active cursor data and query plans.

This is a current activity query used to identify what processes are currently running on the processors. Use this query to find what user is running a large report or process and consuming system resources. This is a snapshot view of current activity. You should execute the query several times to identify if a query is increasing it’s I/O or CPU time.

SELECT r.session_id,
       se.host_name,
       se.login_name,
       Db_name(r.database_id) AS dbname,
       r.status,
       r.command,
       r.cpu_time,
       r.total_elapsed_time,
       r.reads,
       r.logical_reads,
       r.writes,
       s.text AS sql_text,
       p.query_plan AS query_plan,
       SQL_CURSORSQL.text,
       SQL_CURSORPLAN.query_plan
FROM   sys.dm_exec_requests r
       INNER JOIN sys.dm_exec_sessions se
         ON r.session_id = se.session_id
       OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
       OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
       OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS
       OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL
                   LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS
                     ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle
       OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN
WHERE  r.session_id &lt;&gt; @@SPID
       AND se.is_user_process = 1

How to: Enable CLR Integration

In an effort to improve security, Microsoft has turned many features “off by default”. The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Once you have the CLR enabled, you can write user-defined functions, triggers, stored procedures, user-defined aggregates, and user-defined types in VB.Net and C#.