Ho scritto questo script pensando di raccogliere non solo le informazioni sui diversi datafiles della tempDB ma anche dando qualche raccomandazione utile su come migliorare la sua performance. In questo caso la quantità di Datafiles, secondo le specificazioni di Microsoft in questo articolo.
/*
****************************************************************************************************************************************
Script Name: tempdb_raccomandazione.sql
Client's Name: Service Desk
Create Date: 30/10/2018 - 18:00
Created By: Bruno Bragatto - Senior DBA
Objective: Raccomandazione per la tempDB
----------------------------------------------------------------------------------------------------------------------------------------
Modification Log
Date: 09/11/2020
Modified By: Bruno Bragatto
Modification Details: Correzioni Recomendation e piccole correzioni ortografiche
Initial Created by: Bruno Bragatto
****************************************************************************************************************************************
References:
https://support.microsoft.com/it-it/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d
*/
DECLARE
@total_tempddb_df NVARCHAR(8),
@total_processor NVARCHAR(8)
SET @total_tempddb_df = (select count(file_id) from sys.master_files where database_id = DB_ID('tempdb') and type_desc= 'rows')
SET @total_processor = (select cpu_count AS logicalCPUs FROM sys.dm_os_sys_info)
select @@SERVERNAME + ' - ' +DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name
, CONVERT(NVARCHAR(5),ROW_NUMBER() OVER (ORDER BY file_id)) +'/'+ @total_tempddb_df as total
,@total_processor as LogicalProcessor
, CASE
WHEN
@total_processor <= '8'
THEN
(CASE
WHEN @total_tempddb_df < @total_processor THEN 'You should add at least more '+ CONVERT(nvarchar(max),(8 - @total_tempddb_df)) + ' TempDB Datafiles'
WHEN @total_tempddb_df = @total_processor THEN 'OK'
WHEN @total_tempddb_df > @total_processor THEN 'Too many datafiles can cause several performance problems'
ELSE 'Check it Manually'
END)
WHEN
@total_processor > '8'
THEN
(CASE
WHEN @total_tempddb_df < '8' THEN 'You may want to add at least more ' + CONVERT (NVARCHAR(MAX), 8 - @total_tempddb_df) + ' TempDB Datafiles'
WHEN @total_tempddb_df = '8' THEN 'OK'
WHEN @total_tempddb_df > '8' THEN 'You should add more datafiles to the tempdb ONLY when you have confirmed some performance problems related to it. Normally it s not necessary'
ELSE 'Check it Manually'
END)
ELSE 'Verify'
END as recomendation
from sys.master_files mf
where database_id = DB_ID('tempdb') and type_desc= 'rows'
ORDER BY file_id
