To improve your SQL Server performance or relive TempDB page (wirte) blocking consider splitting\u00a0the TempDB Data file into the amount of logical processors in the server.<\/p>\n
As a general<\/b>\u00a0rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload\/code.<\/p>\n
http:\/\/support.microsoft.com\/kb\/2154845<\/p>\n
Use the following script to split out TempDB<\/p>\n
<\/p>\n
To improve your SQL Server performance or relive TempDB page (wirte) blocking consider splitting\u00a0the TempDB Data file into the amount of logical processors in the server. As a general\u00a0rule, if the number of logical processors<\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_newsletter_tier_id":0,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[21],"tags":[2081],"class_list":["post-2951","post","type-post","status-publish","format-standard","hentry","category-microsoft","tag-sql"],"jetpack_publicize_connections":[],"yoast_head":"\r\n
\nUSE master
\nGO
\nCREATE TABLE #numprocs
\n(
\n[Index] INT,
\n[Name] VARCHAR(200),
\nInternal_Value VARCHAR(50),
\nCharacter_Value VARCHAR(200)
\n)
\nDECLARE @BASEPATH VARCHAR(200)
\nDECLARE @PATH VARCHAR(200)
\nDECLARE @SQL_SCRIPT VARCHAR(500)
\nDECLARE @CORES INT
\nDECLARE @FILECOUNT INT
\nDECLARE @SIZE INT
\nDECLARE @GROWTH INT
\nDECLARE @ISPERCENT INT
\nINSERT INTO #numprocs
\nEXEC xp_msver
\nSELECT @CORES = Internal_Value FROM #numprocs WHERE [Index] = 16
\nPRINT @CORES
\nSET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
\nFROM master.sys.master_files
\nWHERE database_id = 2 and FILE_ID = 1)
\nPRINT @BASEPATH
\nSET @FILECOUNT = (SELECT COUNT(*)
\nFROM master.sys.master_files
\nWHERE database_id = 2 AND TYPE_DESC = 'ROWS')
\nSELECT @SIZE = size FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
\nSET @SIZE = @SIZE \/ 128
\nSELECT @GROWTH = growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
\nSELECT @ISPERCENT = is_percent_growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
\nWHILE @CORES > @FILECOUNT
\nBEGIN
\nSET @SQL_SCRIPT = 'ALTER DATABASE tempdb
\nADD FILE
\n(
\nFILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@CORES as CHAR)) + '.ndf'',
\nNAME = tempdev' + RTRIM(CAST(@CORES as CHAR)) + ',
\nSIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
\nFILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
\nIF @ISPERCENT > 0
\nSET @SQL_SCRIPT = @SQL_SCRIPT + '%'
\nSET @SQL_SCRIPT = @SQL_SCRIPT + ')'
\nEXEC(@SQL_SCRIPT)
\nSET @CORES = @CORES - 1
\nEND
\nGO
\nDROP TABLE #numprocs
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"