1

While trying to investigate some performance issues with our SQL Server database, I've noticed our wait statistics seem high for ASYNC_IO_COMPLETION and PAGEIOLATCH_*. In Resource Monitor I noticed that tempDB is frequently (at least 1 time per hour) Reading/Writing large amounts of data, Total (B/Sec) range from 5GB - 25GB.

The tempDB is on a separate Virtual Disk than the Database and Log Files. TempDB size is 4 GB. Our main DB and Log file is 40GB in size each. I've tried researching perfmon counters for this but haven't been able to find anything I can make sense of in order to figure out why I'm getting certain numbers.

Is it unusual that the total data for tempDB is so high through resource monitor? Our main Database is only growing about 50MB per day. What should I be looking for to figure out what is causing such high data on tempDB and if this is causing an I/O Bottleneck? If the best method is by using specific perfmon counters, which counters should I be looking at and what values are good or bad?

Improve this question
8
  • How many tempdb data files do you have? Are they all the same size? Are the data and log files on separate drives for both your main database and the tempdb?
    – Aaron
    May 28, 2019 at 15:05
  • Just 1 tempdb datafile. The tempdb log file is on the same drive as the main db log file however both datafiles are on different drives.
    – user3841709
    May 28, 2019 at 15:13
  • Read over this. It may provide some insight. brentozar.com/blitz/tempdb-data-files
    – Aaron
    May 28, 2019 at 15:37
  • Good, helpful article. I will have to test out the scripts he uses to tell if its a problem. Do you have any thoughts on the amount of total data being used by my tempDB according to resource monitor? It is sometimes spiking to almost 30GB of total/sec. Have I listed enough info that you can tell if this seems like it would be unusual?
    – user3841709
    May 28, 2019 at 15:49
  • It will be relative to your work load. If you have a lot of sorting and joins with a lot of records that could be the cause. The blitz scripts can help id the statements that maybe causing the volume of data
    – Aaron
    May 28, 2019 at 16:19

1 Answer 1

Reset to default
0

Tempdb is used to hold:

Temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.To capture sessions which are causing tempdb allocation ,try to use sp_blitz or refer this article more insight into tempdb. https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/

Improve this answer
2
  • It also contains implicitly created objects such as intermediate work tables and space for sort operations. Queries spill to tempdb when their estimated size is large enough (or exceeds available memory). Indexes (on non-temporary tables) can be specified to sort in tempdb as well during creation or defragmentation.
    – Jonathan Fite
    May 29, 2019 at 13:46
  • If I run the first query in the link from this answer, one of the queries returned has a Total Allocation MB size of 5580 MB. My TempDB size is only 4GB. I don't mean to sound dumb but what exactly does this mean? Was the query of size 5580 MB actually hitting the tempDB? my TempDB hasn't grown over 4GB in size so it doesn't appear autogrowth is hitting.
    – user3841709
    May 29, 2019 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.