Internal usage of TEMPDB by SQL Server DB engine

This post is actually a continuation post for TEMPDB – the most important system database in SQL Server which trying to cover one of the important usage of TEMPDB in SQL Server. Some operations in SQL Server uses TEMPDB internally to improve the performance of the operations. It may not be fully aware for the users, but we can understand the usage of tempdb using the below query.

--Query to understand the internal usage of TEMPDB
select
reserved_MB=(unallocated_extent_page_count+
			version_store_reserved_page_count+
			user_object_reserved_page_count+
			internal_object_reserved_page_count+
			mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024., 
internal_object_reserved_page_count,
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage

Few of operations as below:

1. DBCC CHECKDB/CHECKALLOC

DBCC CHECKDB/CHECKALLOC are using TEMPDB space for creating internal database snapshot to perform the operation. This is to efficiently avoid the locking behavior on database to obtain the consistency check. DBCC CHECKDB and CHECKALLOC has an option to estimate the usage of TEMPDB by providing ESTIMATEONLY option as below. Please note, this is an estimated value, may not be the correct one, however, this can be a good indication to estimate the space required for the operation. This can be used to make sure the TEMPDB drive has enough space to run on DBCC operations for large databases.

2. SORT operations can spill over to TEMPDB

When a query is executed, SQL optimizer will choose the plan already created if prsent or will create a new plan based on cost based algorithms. When optimizer creates a plan, SQL optimizer/relational engine will identify the right operator for the plan to execute the queries and it estimates the memory required to execute the query. This plan will be used for further execution. If the estimation is not happening correctly because of wrong statistics or parameters used while creating the plan is returning less number of records, the estimated number of records will be deviating from the actual number of records while executing the query. In such scenario, the memory granted for the execution may not be sufficient for the execution.

If SORT operator needs more memory to sort the data , other words, the memory granted is not sufficient to do the sorting, it will spill over to TEMPDB. This will have a performance impact for the query execution. The spill over to TEMPDB can be observed in the profiler as SORT warnings(below).

In the above snapshot, we can see Sort warnings in profiler with Event Subclass as single or multiple. Whenever the sort operation spills to tempdb, SQL Server raises the ‘Sort Warnings’ event and it takes single or multiple passes to tempdb.

As mentioned, Sort warnings or spilling to TempDB will have some detrimental impact on the query performance, we need to identify those queries and avoid if possible. Through profiler, we may not understand the query is being caused the Sort Warnings, but, we need to identify from the cached plan or by setting up Extended events to capture the Sort warnings.

Once we identified the query caused Sort Warning, the easiest solution would be re-write the query in a manner to avoid the sorting. I have seen queries with CTE using the ORDER BY even though the order by is not required specifically. Try to use ORDER BY genuinely to avoid performance issues. At the same time, it may not be easy for all cases to avoid the ORDER BY completely. Similar scenario, we may need to evaluate adding a supporting index or modify the existing indexes to avoid, but again, this needs more careful and clear understanding of code and index usage. If you are well aware of the reason for Sort warnings are due to incorrect statistics, you can update the stats or use the solutions to avoid the parameter sniffing(As the objective of this post is not going to explain the parameter sniffing, this post does not cover the topic now.).

3. Worktables/intermediate temp objects due to spooling/hash joins/aggregate Operations

Spools are special operators created by SQL optimizer to improve the performance of a query. A spool operator is not an independent operator,but a supporting operator for another operator like clustered index scan, Table scan or even Constant scan. A spool operator reads and stores intermediate “operated” data into TEMPDB from another operator, there by, increasing the performance of the query.

In the below snapshot, we can observe that the spool operator stores the data from the input, here its nothing but constant scan into a temporary tables to avoid multiple rewinds.

There are totally five types of spool operators – Eager, Lazy, Table, RowCount and Non-Clustered Index spools. All spool operators will store the data into TEMPDB, however, it may be different the way it behaves. Please explore further on the topic to know more details.

Hope you enjoyed this post, please post your feedback/thoughts in the comments.

Advertisements

Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.)

Recently we faced an issue that SQL Server was not coming up online in one of our environment.

We found below error messages in Windows application event logs:

Open failed: Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
Unable to open the physical file "E:\TempDB\tempdb.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
CREATE DATABASE failed. Some file names listed could not be created.

As we have a clear message, while SQL Service starts, it was not able to create the TEMPDB as the mdf file is being used by another process.
When it happens, it will not start the SQL Service even we manually start it through services.msc. However, we identified, the service will come online once we restart the server.

We started our investigation verifying whether more than one instances are pointing to the same directory for TEMPDB files. Though we have multiple instances on the environment, we made it sure those instances are configured separately and properly. Once we confirmed the SQL Server configuration, we took our attention to other services especially with AntiVirus services. We found that there is Mcafee software running, but unfortunately, we could not find any reference on ProcessExplorer as well. However, we decided to stop the McAfee and see if the issue is related to the AnitVirus scan. Now, it is almost more than two weeks and we did not experience the issue.

Takeaway, if you find similar issue, Instead of disabling or stopping the services related Antivirus, we can exclude certain files associated with SQL Server from the scan.

Here are few directories that can be excluded from anti-virus scanning tool:

1. C:\Windows\System32\LogFiles
2. C:\Windows\System64\LogFiles
3. C:\windows\System32\GroupPolicy\
4. C:\windows\temp
5. C:\windows\SoftwareDistribution\Datastore
6. C:\windows\SoftwareDistribution\Datastore\Logs
7. C:\\Temporary ASP.NET Files
8. C:\Users\\AppData\Local\Temp
9. C:\Users\Default\AppData\Local\Temp
10. C:\ProgramData\Microsoft\SQL Server
11. C:\Program Files\Microsoft SQL Server
12. Exclude SQL Server Data Files (*.MDF, *.LDF, *.NDF)
13. Exclude SQL Server Backup Files (*.TRN, *.BAK)
14. Exclude Full-Text Catalog Files (*.SLS)
15. Exclude sqlmangr.exe
16. Exclude sqlservr.exe

Please give your feedback if this post helps you!!!

How to move TEMPDB files to different drives in SQL Server

Today, we are going to see how to move the TEMPDB files from one location to another.
First off, we should know the number of files and the its current location.
Query


SELECT NAME,PHYSICAL_NAME FROM SYS.MASTER_FILES WHERE DATABASE_ID=2

Query to move the files to new location

You may change the @NEWPATH variable value as per your new location.

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @NEWPATH VARCHAR(MAX) = 'D:\DATA\DATA'
SET @SQL= @SQL + (SELECT 'ALTER DATABASE TEMPDB MODIFY FILE (NAME = ' + NAME + ', FILENAME = ''' + 
				@NEWPATH + RIGHT(PHYSICAL_NAME,CHARINDEX('\',REVERSE(PHYSICAL_NAME))) + ''');'
FROM SYS.MASTER_FILES WHERE DATABASE_ID = 2 FOR XML PATH(''))

EXEC ( @SQL )

Once you executed the above query, you need to restart the server to see the changes.

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

You can also go to the previous file location and delete the old files from the folder.

It is important to know that the moving of TEMPDB files no longer supporting using backup & restore OR  detaching & attaching(system databases).

TEMPDB – Collation Issues with User databases in SQL Server

Problem Statement:

Today, We faced an issue with one of the projects we engaged with.Project team was experiencing issues having different collation for TEMPDB and USERDB.The error message is given below:

“Cannot resolve the collation conflict between “SQL_Latin1_Genral_CP1_CI_AS” and “Latin1_Genral_CI_AI” in the equal to operation”.

Troubleshooting:

This was a clear indication that the issue is with the collation mismatch. When we further analysed the procedure, we could identify there is a temp table used and being used to match the records with USERDB table.

To understand the issue with collation in detail, we checked the collation of TEMPDB database and USERDB and found the collation is set as “SQL_Latin1_Genral_CP1_CI_AS” and “Latin1_Genral_CI_AI”.

Use the below script to know the collation for databases:

Select name,COLLATION_NAME from sys.databases

As long as we are not sure of the difference in Collation between TEMPDB and USERDBs, as a quick fix, we recommanded to change the SQL Query as below while creating the temp tables, this would by default take the user database’s collation during the temp table creation:


CREATE TABLE #Table
(
VoucherID NVARCHAR(10) COLLATE database_default,
VocherName NVARCHAR(100) COLLATE database_default,
FileTypeID INT
)

Call To Action:

1. Please make sure your USERDBs and systemDBs are in the same collation unless there is a requirement for explicit change.
2. When you want to change the Collation for a USERDB, Please check the Collation of TEMPDB as well.

See Also:

https://msdn.microsoft.com/en-us/library/bb402915.aspx