Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

JohnBates (MIS) (OP)
13 Sep 08 2:00
hi all,


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
Helpful Member!  mrdenny (Programmer)
13 Sep 08 18:55
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?

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 (MIS) (OP)
13 Sep 08 20:18
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.


Helpful Member!  MDXer (TechnicalUser)
13 Sep 08 22:08
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.

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

mrdenny (Programmer)
18 Sep 08 4:54
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.

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close