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?

user3841709
  • 115
  • 4
  • 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. https://www.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
  • You have a single tempdb file? Why? Typical recommendation is to start at 4, giving you more isolation / concurrency. Are you shrinking the file at all? You can see this question and this question for some additional ideas on finding the culprit. – Aaron Bertrand May 28 '19 at 16:23
  • I'm sort of "inheriting" this database and taking over from what was set up a long time ago. There never has been a real DBA in charge of it and I'm, of course, not a DBA myself. I believe the single tempdb has just been overlooked throughout the years if this is in fact something that will help performance for us. – user3841709 May 28 '19 at 17:06
  • I don't see any plan or job that would be shrinking the tempdb file. After reading one of the articles it suggested to pretty well max out the tempdb size to whatever the available hard drive space is. Our tempdb size is 4GB and the drive (only used for tempdb) is 50GB. Would increasing tempdb's size make any difference in performance or does it still come down to the read/write speeds with only one tempdb? – user3841709 May 28 '19 at 17:31

1 Answers1

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/

Vinod Narwal
  • 397
  • 1
  • 5
  • 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