SQL Server troubleshooting – tempdb utilization

Goal

To obtain information about tempdb database from utilization perspective

My way of achieving it

Using some of the DMV/DMF that come with SQL Server and overall advises (scripts taken from different sources over internet that all refers to SQL Server DMVs in Action Better Queries with Dynamic Management Views by Ian W. Stirk)  from peers colleagues from internet customized to fit my needs  and my own way of troubleshooting. For me the most important thing while troubleshooting is to have the result of the investigation side by side with what it means since in most of the cases I am not dealing with this everyday and my memory about this topic is as good as the number of occurrences I had throughout my working experience. In simple terms for tempdb database as for other user databases we can find information about usage using specific DMVs and DMFs. The utilization information (tempdb) provided by SQL Server comes under the form of

  • present utilization
    • current space occupied by all the objects that uses tempdb – user objects, internal objects, version store, mixed extent
    • current space each session is currently using in tempdb because of running tasks
    • current space each session is currently using in tempdb because of row versioning
  • past utilization
    • used space in tempdb by all the sessions since they were established

The output of the script is ordered by the space still used and all the columns along the table gives me all the information that I need in order to decide the next steps or to say whom, when,  why in regards to tempdb utilization.

tempdbtroubleshooting

The script can be downloaded from here

SQL Server Instance – generate tsql to meet tempdb best practices and display the current configuration

Goal

Generate tsql code required to meet tempdb best practices configuration and display the current configuration

My way of achieving it

Using SSMS, tsql, xp_cmdshell

The backbone for this script was a script made a while ago by VijayRod, and reviewed by AkshayM that was checking tempdb configuration by comparing the current configuration to best practices recommended by Microsoft at that time. Because sometime we need if configuration is not compliant to modify the tempdb settings I decided to use their script and add the code that will generate the tsql commands used to configure tempdb database to meet the requirements. This version of the script has some limitations and assumes the following
– when it comes to tempdb databases that have files created in multiple locations ( different drives ), by default the script will take in consideration only the biggest drive when it comes to create the files.
– it assumes that the free space left on the drive where the files will be created will be only 1 GB from the drive capacity. If you want to leave more space then replace the 1024 in the script in this line – SELECT @availablespace=(MAX(diskspacefreeinmb)-1024)/@suggestedDataFilecount FROM @dbfilesdrivemountpoint WHERE filetype = ‘DATA’ –
– it assumes that the maximum size will be the initial size + 10MB. If you want to have a maximum size bigger then replace 10 in the script in this line – SELECT @maxsize = CONVERT(nvarchar,(@availablespace+10))+’MB’ — assumes a maximum size of initial size + another 10 MB

The output of the script looks like below depending on the current tempdb database configuration

tempdbbpgencomm1

The script can be downloaded from here