March 7, 2018 at 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.
March 7, 2018 at 9:06 am
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?
March 8, 2018 at 11:13 am
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
March 8, 2018 at 11:38 am
bwelch42 - Wednesday, March 7, 2018 8:05 AMOn 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
March 9, 2018 at 1:40 pm
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
March 14, 2018 at 3:23 pm
Bwelch42,
The issue is storage latency. Use perfmon to validate and provide proof to your vm admin.
March 14, 2018 at 3:59 pm
Enterprise DBA - Wednesday, March 14, 2018 3:23 PMBwelch42,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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply