Today I had the opportunity to present on TempDB to the Spokane SQL Server users group (PASS Chapter). The session was titled TempDB – Do This and Don’t Do That”, and it covers a bunch of tips and best practices around what to do and avoid relating to TempDB on your SQL Server.
Here is the download of the presentation and samples.
Some of the sample code from the session:
DECLARE @logInfoResults AS TABLE ( [RecoveryUnitId] BIGINT, -- only on SQL Server 2012 and newer [FileId] TINYINT, [FileSize] BIGINT, [StartOffset] BIGINT, [FSeqNo] INTEGER, [Status] TINYINT, [Parity] TINYINT, [CreateLSN] BIGINT ); INSERT INTO @logInfoResults EXEC sp_executesql N'DBCC LOGINFO WITH NO_INFOMSGS'; --SELECT AVG(FileSize) / 1024.0 / 1024.0 as AvgSizeMB, count(*) as VLFs FROM @logInfoResults ; --SELECT FileSize / 1024 / 1024, [Status] FROM @logInfoResults ; SELECT * FROM @logInfoResults ; SELECT ISNULL(cast([Status] as nvarchar(10)), 'Total'), COUNT(*) FROM @logInfoResults GROUP BY [Status] WITH ROLLUP;
I hope you enjoyed the session.