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

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 TempDB usage based on tasks and sessions.

sys.dm_db_file_space_usage -  Lists space usage information for each file in the Tempdb database. Reports on unallocated extent page count.

sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated

sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity

 

go
select * from  sys.dm_db_file_space_usage 
go 
select * from  sys.dm_db_session_space_usage
go
select * from sys.dm_db_task_space_usage

Read More

SQL Server – List current tempdb statements

SQL Server - TempDB performance and strategy checklist

SQL Server – tempdb and solid state drives

SQL Server - Shrink tempdb without sql server restart

 

SQL Server – SORT IN TEMPDB and Sort Warnings

A typical question I receive from application owners  : I have a query with an ORDER BY which is very slow . How can I make it faster?

A clue towards insufficient memory and spilling over into Tempdb are Sort warnings.

 One  way is to check for ‘Sort Warnings’ is on a default trace  - SQL Server – default trace FAQ - SQL Server DBA   or Profiler

Another way is to monitor the Tempdb – looking for  evidence of tempdb activity. SQL Server – List current tempdb statements

If an index exists SQL Server will use the index and avoid the SORT. If an index doesn’t exist SQL Server will sort the rows. SQL Server will sort the rows in memory or on Tempdb.

 If the ORDER BY is created dynamically , creating an index for every combination is complicated . Quite often generic reporting platforms experience this problem – where the user has flexibility to add different columns.

What to do if Sort Warnings appear ?

1) Are SQL statistics are up to date?

Memory assigned is estimation based. SQL Server assigns memory to the query worker buffer to store temporary row data for sorting

As the memory is estimation based and if the Query Optimizer estimates the number\size of rows inaccurately then the memory granted is insufficient , the sort rows will begin to use Tempdb

 It is also possible the memory granted is not enough based on insufficient memory left for SQL server.Read more on SQL Server Performance ,memory pressure and memory usage

2) Move Tempdb to faster disk such as SSD  - SQL Server – tempdb and solid state drives - SQL Server DBA

3) Query Tuning and rewrite query to optimize. There are different tactics available , such as remove ORDER BY and use another column .

 A typical situation is where a column is selected for the ORDER BY based on presenting the data in a certain order. Is it necessary to ORDER BY at the SQL Server level, it may be more efficient to order at the application level.

Read More

TempDB performance and strategy checklist - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Server - BobMgrGetBuf Sort Big Output Buffer write not complete after 60 seconds

Sometimes you may see this message in the SQL Server Error Log files

 

 BobMgrGetBuf Sort Big Output Buffer write not complete after 60 seconds

 

It’s associated with IO subsystem issues , other common errors appearing and related are:

 

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

 

Read more on  IO Requests taking longer than 15 seconds to - SQL Server DBA

The message relates to large SORTS , probably the TempDB. Bob is the Big Output Buffer. Writres are heavy sequential and occur  on transit tables .During the heavy sequential writes, it is possible the disk IO cannot maintain the resource requirements.

 There are two options :

1)Reduce the workload

2)Add resources

Reducing the workload could mean : a) make queries more efficient and can be achieved through analysing the query Execution Plan, b)  review index usage. Both options  can be quick wins without much justification required in spending terms.

Justifying adding more resource such as  disk resource can be trickier – disk can be expensive but including management of the disk can increase costs.

 

Other options:

1)       If relevant – use Lock Pages in Memory

2)       Work with network \ storage administrators to identify the bottleneck . To identify what is delaying the IO. The bottleneck can occur across the whole environment – such as SVC, network switches and Disk arrays.

Read More

TempDB performance and strategy checklist - SQL Server DBA

IO Requests taking longer than 15 seconds to - SQL Server DBA

 Powershell,PSEexec and lock pages in memory – made easy

SQL Server – Tempdb move files to another drive

There is a requirement to move a TempDB file from one drive to another. There may be many different reasons – such as the Tempb is FULL or it is a scheduled change.   Moving a TempDB file requires executing the ALTER DATABASE command and a SQL Server Restart.

View the current location of the TempDB files by :

use tempdb
go
select fileid,name,[filename] from sys.sysfiles

--the output is something like
--fileid	name	filename
--1	tempdev	G:\MSSQLSERVER\MSSQL$INST1\Data\tempdb.mdf
--2	templog	F:\MSSQLSERVER\MSSQL$INST1\Data\templog.ldf
--3	tempdev1	E:\MSSQLSERVER\MSSQL$INST1\Data\tempdev1.ndf

 

You’ll notice in the output three files, the “tempdev1” file was added as an extra file earlier on. All I want to complete is moving the”tempdev1” file, but the same principle applies to the tempdev and templog file

 

USE master GO ALTER DATABASE TempDB 
MODIFY FILE (NAME = tempdev1, FILENAME = 'J:\tempdev1.mdf') 
--then a SQL Server restart

 Read More

TempDB performance and strategy checklist - SQL Server DBA

SQL Server – List current tempdb statements - SQL Server DBA

Shrink tempdb without sql server restart - SQL Server DBA

SQL Server – tempdb and solid state drives - SQL Server DBA

List current SQL Server tempdb statements

Do you know what statements are currently running on the SQL Server tempdb database? Can you tell which statements are creating insufficient disk space in the tempdb?

Use this statement to list the current tempdb statements. It is very useful for troubleshooting tempdb disk usage and which queries are creating resource contention on SQL Server tempdb.

 Maintaining SQL Server tempdb performance is critical for overall performance. Tempdb has many uses – such as Version Stores, Internal objects (join work files) and User objects (tables,indices). Read more on TempDB performance and strategy checklist

The statement uses the DMV:

sys.dm_db_session_space_usage - Returns the number of pages allocated and deallocated by each session for the database

sys.dm_exec_requests - Returns information about each request that is executing within SQL Server.SQL Server – Current queries executing on SQL Server

sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle

sys.dm_db_task_space_usage - Returns page allocation and deallocation activity by task for the database

 

SELECT ssu.session_id, 
(ssu.internal_objects_alloc_page_count + sess_alloc) as allocated, 
(ssu.internal_objects_dealloc_page_count + sess_dealloc) as deallocated 
 , stm.TEXT
from sys.dm_db_session_space_usage as ssu,  
        sys.dm_exec_requests req
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm ,
(select session_id,  
   sum(internal_objects_alloc_page_count) as sess_alloc, 
   sum (internal_objects_dealloc_page_count) as sess_dealloc 
   from sys.dm_db_task_space_usage group by session_id) as tsk 
where ssu.session_id = tsk.session_id 
and ssu.session_id >50 
and ssu.session_id = req.session_id 
and ssu.database_id = 2 
order by allocated DESC

Read More

SQL Server - Shrink tempdb without sql server restart - SQL Server ...

SQL Server – tempdb and solid state drives - SQL Server DBA

TempDB performance and strategy checklist - SQL Server DBA