SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving or adding tempdb files best practice


Moving or adding tempdb files best practice

Author
Message
bwelch42
bwelch42
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 190
On some database server VMs, I'm seeing regular messages about tempdb data files such as the one below:

Message SQL Server has encountered 104 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\mssql\tempdev2.ndf]

The servers typically have no other errors and have the same number of tempdb data files as CPUs. Those tempdb files are typically, however, on the same drive and that appears to be a bottleneck. If I create a separate tempdb data drive on the server and move half of the tempdb data files to new drive, the messages about "I/O requests taking longer than 15 seconds" go away. Moving tempdb files, however, requires me to restart SQL Server services.

Question: Would I likely see the same benefit if created additional tempdb files on the new drive instead of moving current ones? It would then have twice as many tempdb files as CPUs, but adding tempdb data files would let me avoid restarting SQL Server services.
RVSC48
RVSC48
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6515 Visits: 2259
If I was in this situation, I would not add additional data files to tempdb for the reason of avoiding the IO messages in the sql error log. I would plan for an outage and restart the instance with all the tempdb files being located (moved) onto the better faster drive and then remove the old tempdb files from the old location and not have to revisit this issue moving forward. There are cases where problems can occur if you have too many data files. Since you are not suffering from that issue, why risk it?
Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 264
First, without special reasons, I suggest we follow the best practice.
1. The count of data files equals to CPU cores, but not greater than 8
2. Set data files the same size (and big enough, if possible).
3. Put them in fast IO device (maybe files are in diff disks).
4. Only one log file
....

If storage is fast enough and there is no driver/firmware issue, I suggest you review the tempdb load. I ever ran into tempdb IO issue. The root cause turned out be some crazy application code.

GASQL.com - Focus on Database and Cloud
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860108 Visits: 47118
bwelch42 - Wednesday, March 7, 2018 8:05 AM
On some database server VMs, I'm seeing regular messages about tempdb data files such as the one below:

Message SQL Server has encountered 104 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\mssql\tempdev2.ndf]

The servers typically have no other errors and have the same number of tempdb data files as CPUs. Those tempdb files are typically, however, on the same drive and that appears to be a bottleneck. If I create a separate tempdb data drive on the server and move half of the tempdb data files to new drive, the messages about "I/O requests taking longer than 15 seconds" go away. Moving tempdb files, however, requires me to restart SQL Server services.

Question: Would I likely see the same benefit if created additional tempdb files on the new drive instead of moving current ones? It would then have twice as many tempdb files as CPUs, but adding tempdb data files would let me avoid restarting SQL Server services.


It's likely that TempDB isn't actually a bottleneck. It's much more likely that it's doing the best it can to handle a bunch of bad code that overuses TempDB because of non_Sargable queries, bad implicit cast queries, and accidental many-to-many joins due to either a bad database design, a misunderstanding of the data, or the propensity to thing that a monster query with many, many joins is somehow "set based" just because it's a single query.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
goher2000
goher2000
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3916 Visits: 1602
run the below script to find out if you need more temp files.
ummm.. do you need enable traceflag 1118 ?


USE Master
GO
SET NOCOUNT ON
GO
PRINT '-- Instance name: '+ @@servername + ' ;
/* Version: ' + @@version + ' */'

-- Variables

DECLARE @BITS Bigint -- Affinty Mask
,@NUMPROCS Smallint -- Number of cores addressed by instance
,@tempdb_files_count Int -- Number of exisiting datafiles
,@tempdbdev_location Nvarchar(4000) -- Location of TEMPDB primary datafile
,@X Int -- Counter
,@SQL Nvarchar(max)
,@new_tempdbdev_size_MB Int -- Size of the new files,in Megabytes
,@new_tempdbdev_Growth_MB Int -- New files growth rate,in Megabytes
,@new_files_Location Nvarchar(4000) -- New files path

-- Initialize variables

Select @X = 1, @BITS = 1
SELECT
@new_tempdbdev_size_MB = 4096 -- Four Gbytes , it's easy to increase that after file creation but harder to shrink.
,@new_tempdbdev_Growth_MB = 512 -- 512 Mbytes , can be easily shrunk
,@new_files_Location = NULL -- NULL means create in same location as primary file.

IF OBJECT_ID('tempdb..#SVer') IS NOT NULL
BEGIN
DROP TABLE #SVer
END
CREATE TABLE #SVer(ID INT, Name sysname, Internal_Value INT, Value NVARCHAR(512))
INSERT #SVer EXEC master.dbo.xp_msver processorCount

-- Get total number of Cores detected by the Operating system

SELECT @NUMPROCS= Internal_Value FROM #SVer
Print '-- TOTAL numbers of CPU cores on server :' + cast(@NUMPROCS as varchar(5))
SET @NUMPROCS = 0

-- Get number of Cores addressed by instance.

WHILE @X <= (SELECT Internal_Value FROM #SVer ) AND @x <=32
BEGIN
SELECT @NUMPROCS =
CASE WHEN CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
FROM sys.configurations
WHERE NAME = 'AFFINITY MASK'
SET @BITS = (@BITS * 2)
SET @X = @X + 1
END

IF (SELECT Internal_Value FROM #SVer) > 32
Begin
WHILE @X <= (SELECT Internal_Value FROM #SVer )
BEGIN
SELECT @NUMPROCS =
CASE WHEN CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
FROM sys.configurations
WHERE NAME = 'AFFINITY64 MASK'
SET @BITS = (@BITS * 2)
SET @X = @X + 1
END
END

If @NUMPROCS = 0 SELECT @NUMPROCS= Internal_Value FROM #SVer

Print '-- Number of CPU cores Configured for usage by instance :' + cast(@NUMPROCS as varchar(5))

-------------------------------------------------------------------------------------
-- Here you define how many files should exist per core ; Feel free to change
-------------------------------------------------------------------------------------

-- IF cores < 8 then no change , if between 8 & 32 inclusive then 1/2 of cores number
IF @NUMPROCS >8 and @NUMPROCS <=32
SELECT @NUMPROCS = @NUMPROCS /2

-- IF cores > 32 then files should be 1/4 of cores number
If @NUMPROCS >32
SELECT @NUMPROCS = @NUMPROCS /4

-- Get number of exisiting TEMPDB datafiles and the location of the primary datafile.

SELECT @tempdb_files_count=COUNT(*) ,@tempdbdev_location=(SELECT REVERSE(SUBSTRING(REVERSE(physical_name), CHARINDEX('\',REVERSE(physical_name)) , LEN(physical_name) )) FROM tempdb.sys.database_files WHERE name = 'tempdev')
FROM tempdb.sys.database_files
WHERE type_desc= 'Rows' AND state_desc= 'Online'

Print '-- Current Number of Tempdb datafiles :' + cast(@tempdb_files_count as varchar(5))

-- Determine if we already have enough datafiles
If @tempdb_files_count >= @NUMPROCS
Begin
Print '--****Number of Recommedned datafiles is already there****'
Return
End

Set @new_files_Location= Isnull(@new_files_Location,@tempdbdev_location)

-- Determine if the new location exists or not
Declare @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)

insert into @file_results(file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @new_files_Location

if (select file_is_a_directory from @file_results ) = 0
Begin
print '-- New files Directory Does NOT exist , please specify a correct folder!'
Return
end

-- Determine if we have enough free space on the destination drive

Declare @FreeSpace Table (Drive char(1),MB_Free Bigint)
insert into @FreeSpace exec master..xp_fixeddrives

if (select MB_Free from @FreeSpace where drive = LEFT(@new_files_Location,1) ) < @NUMPROCS * @new_tempdbdev_size_MB
Begin
print '-- WARNING: Not enough free space on ' + Upper(LEFT(@new_files_Location,1)) + ':\ to accomodate the new files. Around '+ cast(@NUMPROCS * @new_tempdbdev_size_MB as varchar(10))+ ' Mbytes are needed; Please add more space or choose a new location!'

end

-- Determine if any of the exisiting datafiles have different size than proposed ones.
If exists
(
SELECT (CONVERT (bigint, size) * 8)/1024 FROM tempdb.sys.database_files
WHERE type_desc= 'Rows'
and (CONVERT (bigint, size) * 8)/1024 <> @new_tempdbdev_size_MB
)

PRINT
'
/*
WARNING: Some Existing datafile(s) do NOT have the same size as new ones.
It''s recommended if ALL datafiles have same size for optimal proportional-fill performance.Use ALTER DATABASE and DBCC SHRINKFILE to resize files

Optimizing tempdb Performance : http://msdn.microsoft.com/en-us/library/ms175527.aspx
'

Print '****Proposed New Tempdb Datafiles, PLEASE REVIEW CODE BEFORE RUNNIG *****/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

'
-- Generate the statements
WHILE @tempdb_files_count < @NUMPROCS

BEGIN

SELECT @SQL = 'ALTER DATABASE [tempdb] ADD FILE (NAME = N''tempdev_new_0'+CAST (@tempdb_files_count +1 AS VARCHAR (5))+''',FILENAME = N'''+ @new_files_Location + 'tempdev_new_0'+CAST (@tempdb_files_count +1 AS VARCHAR(5)) +'.ndf'',SIZE = '+CAST(@new_tempdbdev_size_MB AS VARCHAR(15)) +'MB,FILEGROWTH = '+CAST(@new_tempdbdev_Growth_MB AS VARCHAR(15)) +'MB )
GO'
PRINT @SQL
SET @tempdb_files_count = @tempdb_files_count + 1
END




Enterprise DBA
Enterprise DBA
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 399
Bwelch42,
The issue is storage latency. Use perfmon to validate and provide proof to your vm admin.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860108 Visits: 47118
Enterprise DBA - Wednesday, March 14, 2018 3:23 PM
Bwelch42,The issue is storage latency. Use perfmon to validate and provide proof to your vm admin.


Maybe. IMHO, usually, it's not. Usually it appears to be storage latency because of some really poor code that over-uses TempDB. It's worth checking on but it's usually not disk latency especially when everything, including TempDB, is all on the same SAN for those that have one.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search