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?

  • 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 '19 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 '19 at 15:13
  • Read over this. It may provide some insight. brentozar.com/blitz/tempdb-data-files – Aaron May 28 '19 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 '19 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 '19 at 16:19
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/

  • 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 '19 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 '19 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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