RE: How to Know If TEMPDB is a Bottleneck?

Creating additional database files might help, but if it did, it wouldn't help much as the user database in on the same disk as the tempdb database.

Check Perfmon and see what it shows as far as disk IO and queuing on the disk with the database and tempdb on it.

Also check the index tuning adviser and make sure that you have the indexes setup correctly.

When looking at perfmon you should also look at the Buffer Manager and see when then estimated length of time SQL will be keeping data in the buffer is. If this number is low then someone is wrong with your indexes, or you need more RAM.

RE: How to Know If TEMPDB is a Bottleneck?

This is the Enterprise 2005 edition and we have 20 GB memory set to dynamically adjust.

I have been running Perfmon for a couple days and memory looks very good, but the disk counters seem to indicate a problem there.

Avg Disk Queue Length rnages from 3.0 to as high as 28 and for long periods it will be > 12. I think you have to divide the number by # of physical disks, is that correct?

I have captured other disk counters and will post those tomorrow. Disk % (whatever that is) floats between 300 and 1400.

I have a maint plan running every Sunday. This server has only 15 concurrent user sessions on average and the main db is only 18 GB. We should not be having a problem but it is a new app and it takes time to get everything tuned. I am mirroring this db.

?? About the DB maint Plan - it does a reindex on all tables, followed by a reorganize task, then update stats and usage. Am I doing those in the correct sequence??

RE: How to Know If TEMPDB is a Bottleneck?

Avg disk que should not be more that twice the number of disks you have. if your array is 5 drives then numbers over 10 for any period of time show a bottleneck in your disk subsystem.

TempDB should be on a seperate drive. Adding more files to tempdb might help but given the IO bottleneck it won't help much. When adding more files you should not have more than 1 file per cpu core allocated to SQL.