Verify Best Practices for Tempdb

As a DBA for many years now, I know that tempdb needs some tuning after installation.
I found these 3 articles on the Confio website that explains the best practices for Tempdb very well

You could check these settings manually but I had to check tempdb configuration for more than 100 servers today.
A good DBA tries to automate as much as possible so I build a script that verifies all the best practices for tempdb.

Just run it against your Central Management Server or on a separate server and you have all the “wrong” tempdb settings in no time!
Please note that the script only works from SQL 2005 onwards.

--Place data file: Data files on own disk
if exists(select substring(physical_name,1,2) from sys.master_files
	where database_id=2
	and substring(physical_name,1,2) IN (select substring(physical_name,1,2) from sys.master_files
	where database_id<>2))
	print 'Data files of Tempdb are not on a seperate drive'

--Place data file: Log file not on same disk as data disk
if exists(select substring(physical_name,1,2) from sys.master_files
	where database_id=2 and type=0
	and substring(physical_name,1,2) IN (select substring(physical_name,1,2) from sys.master_files
	where database_id=2 and type=1))
	print 'Log file of Tempdb is on the same drive as the data file'

--Place data file: Tempdb on the C-drive
if exists(select substring(physical_name,1,2) from sys.master_files
	where database_id=2 and type=0
	and substring(physical_name,1,2) = 'C:')
	print 'Tempdb is on the C-drive'

--Initial Size is set to default 8 MB
if (select sum(size) from sys.master_files where  database_id =2 and type=0)<=1024
	print 'Tempdb initial size is too small'

--Autogrowth is set to percentage
if (select sum(convert(tinyint,is_percent_growth)) from sys.master_files where database_id =2)>0
	print 'Tempdb has configured one or more files as percentage autogrowth'

--Multiple data files according to number of logical processors
if ((select count(1) from master.sys.master_files where type = 0 and database_id = 2)<(select cpu_count from sys.dm_os_sys_info)
   and (select cpu_count from sys.dm_os_sys_info)<=8) OR
   ((select count(1) from master.sys.master_files where type = 0 and database_id = 2)<8
	and (select cpu_count from sys.dm_os_sys_info)>8)
		print 'Fewer tempdb data files than logical processors'
ELSE
BEGIN
	IF (select count(1) from master.sys.master_files where type = 0 and database_id = 2)>8
	  print 'Too many tempdb data files according to the number of logical processors'
END

--Verify if all data files have the same size
if (select count(1) from (select  count(1) as counter from master.sys.master_files c where   c.type = 0 and c.database_id = 2 group by c.size) as c)>1
  print 'Different sized tempdb data files detected'
This entry was posted in Tempdb.
One comment on “Verify Best Practices for Tempdb

Leave a Reply

Your email address will not be published. Required fields are marked *