How to Know If TEMPDB is a Bottleneck? 2

[JohnBates]

hi all,

2005.

This 2005 db server has 6 user dbs. User dbs and tempdb are on same raid array made up of 5 physical disks.

1. Is there a way to determine whether the tempdb database is causing slow performance?

2. Would creating multiple data files for tempdb, say 4 or 8, but keeping them on the above array - provide any benefit? (2 dual-core processors)

Thanks much. John

 

[mrdenny]

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.

What symptoms are you seeing?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JohnBates]

Thanks, mrdenny.

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??

Thanks much.

John

 

[MDXer]

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.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

After doing a reindex, there is no point in doing an index reorg. All you are doing at that point is basically defragging the index which was just recreated so it is already defragged.

Can you provide more info about your disk setup? What drive letters you have, and how many spindles in what RAID config creates them? Also what is SAN, and what is local.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog