May 29, 2008 at 11:20 am
We have a production database that has queries that took 15-20 seconds when the same query on a similar dev database took .5 seconds. I traced the problem to a write to the temp db that was taking far longer than it should.
I there anything I should check on this server to try and see why temp db is so slow?
May 29, 2008 at 4:37 pm
Make sure your tempdb is NOT on the same drive as your data file.
It is easy to move if it is sharing the same drive:
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
Go
-- then you have restart your SQL Server service.
Good luck,
deb
😎
May 30, 2008 at 10:52 am
Is this all of the time? or around startup?
the default sizing options for tempDB tend to be entirely inadequate. As I recall - it's 1MB with autogrowth in 1MB increments, meaning it's spending a phenomenal amount of time around startup just trying to grow to a manageable size.
Assuming the default growth factors, assuming your tempDB is now 4GB in size, it's now created roughly 4100 disk fragments which could be scattered all over the OS volume they're on. Essentially bad idea, and not great for perf either.
Make sure you set that to a workable size for your situation, so that it starts out with reasonable amount of space, and a reasonable fixed amount for a growth factor (I use 250MB on my dev box). In this case "reasonable" = 1GB more than I anticipate it needing on a regular basis.
Same advice applies to the temp log files as well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 2, 2008 at 9:25 am
Thank you Matt.
It looks like this is a least one of the issues. I also found that temp DB is on the C: drive and is probably sharing disk I/O with the Vitual Memory Paging file. I am going to look into getting a seperate drive installed for temp db. Also, pre-allocating much more space and possibly creating multiple temp-db files. MS says creating a file for each CPU will improve through put on a multi-cpu server.
Michael
June 2, 2008 at 10:18 am
Also, is the code always slow? If you run it 10 times in the db and in tempdb, is it always slower in tempdb?
June 2, 2008 at 10:22 am
michael.welcome (6/2/2008)
Thank you Matt.It looks like this is a least one of the issues. I also found that temp DB is on the C: drive and is probably sharing disk I/O with the Vitual Memory Paging file. I am going to look into getting a seperate drive installed for temp db. Also, pre-allocating much more space and possibly creating multiple temp-db files. MS says creating a file for each CPU will improve through put on a multi-cpu server.
Michael
TempDB on C: (and/or on the paging drive) is definitely slowing you down. Several files (perhaps not one per CPU, but more than one) will also help, especially if they're on drives independent of other SQL-tasked drives (paging, data files, log files). Depending on how busy it is - your mileage may vary, but those are definitely places to start.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply