July 5, 2016 at 8:54 am
I've taken over a production SQL server (2008 R2 Enterprise) and upon doing my checklist I've uncovered that TempDB currently has 4 files (2) 10500MB and (2) 7500MB.
I'd like to have 8x 8GB files, so my questions are:
-Are there any caveats to shrinking the 2 10500MB files to 8000MB?
-Should TempDB log also be sized at 8000MB?
-Anything i'm overlooking?
-Dan
July 5, 2016 at 9:23 am
dclemens (7/5/2016)
I've taken over a production SQL server (2008 R2 Enterprise) and upon doing my checklist I've uncovered that TempDB currently has 4 files (2) 10500MB and (2) 7500MB.I'd like to have 8x 8GB files, so my questions are:
-Are there any caveats to shrinking the 2 10500MB files to 8000MB?
-Should TempDB log also be sized at 8000MB?
-Anything i'm overlooking?
-Dan
Why do you want 8 files? Have you measured tempdb contention?
You may want to take a look here:
You should have no issue re-sizing the files.
As for the log, I typically leave that as is, unless there is good reason to change it.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 5, 2016 at 10:06 am
Michael L John (7/5/2016)
dclemens (7/5/2016)
I've taken over a production SQL server (2008 R2 Enterprise) and upon doing my checklist I've uncovered that TempDB currently has 4 files (2) 10500MB and (2) 7500MB.I'd like to have 8x 8GB files, so my questions are:
-Are there any caveats to shrinking the 2 10500MB files to 8000MB?
-Should TempDB log also be sized at 8000MB?
-Anything i'm overlooking?
-Dan
Why do you want 8 files? Have you measured tempdb contention?
You may want to take a look here:
You should have no issue re-sizing the files.
As for the log, I typically leave that as is, unless there is good reason to change it.
I want 8 files because I have 8 CPUs. I'm trying to determine the root cause of slowness in a vendor application. I know that the app heavily uses TempDB, which led me to find the varying sized tempdb files, so I want to get that corrected as a starting point.
Using this query, I'm showing avg_write_stall_ms >280ms, which seems very high to me.
SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
July 6, 2016 at 5:47 am
280ms is quite high indeed.
You might want to check the wait stats to confirm that IO is the biggest bottleneck, and not e.g. latch contention on PFS pages in tempdb.
Check if something is abusing tempdb, like huge index rebuilds, hash/sort spills to tempdb from unoptimal queries, monster temp tables etc. That might increase pressure on tempdb, so much that IO subsystem cannot handle the load and therefore IO latency explodes, making everything drag slow.
There should be no issues in changing the size of tempdb files.
Log should be large enough to not have to auto grow, but not larger than that.
Set initial log size to current size, so after sql service restart it will be initially big enough to avoid autogrowth.
July 6, 2016 at 5:55 am
dclemens (7/5/2016)
I want 8 files because I have 8 CPUs.
8 CPUs doesn't mean 8 files for TempDB.
Using this query, I'm showing avg_write_stall_ms >280ms, which seems very high to me.
Adding files won't help with that. Adding extra files is for allocation contention (page latch waits on the allocation pages in TempDB). If you have IO contention (high stalls, slow IO, etc), then you either need to move the TempDB files to faster storage, or move half of them to a separate IO subsystem.
The one thing you do need to do is make all four TempDB data files the same size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply