Friday, October 17, 2014

This script will take a tempdb database with one database file, and create a set of equally-sized files. You can set how much total space you want the files to use, and how many files to have (in total).

From BOL, as a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs. Make each data file the same size; this allows for optimal proportional-fill performance.

"A more pragmatic approach however, is to have a 1:1 mapping between files and logical CPUs up to eight, and then add files if you continue to see allocation contention or if you're looking to push the I/O subsystem harder. The performance benefit from adding files diminishes each time, and in our experience, eight is the sweet spot, especially if you're implementing this as a pro-active measure." (Configuration Best Practices for SQL Server Tempdb--Multiple Files)

These calculations assume that currently the tempdb database has only one datafile. Also, the log file for tempdb is assumed to be on a different drive.

Monday, October 13, 2014

This script runs DBCC CHECKDB against every database on the server. It starts off with the simplest and fastest commands, and works up the the most stringent test. Note that TABLOCK keyword is commented out - you can use it at your own discretion. The commands that actually repair the database can't be run accidentally, and the version that can allow data loss - aptly named REPAIR_ALLOW_DATA_LOSS - is left as an exercise for the reader. (In other words, I'm not giving you that particular big, sharp knife!)

-- Run DBCC CHECKDB commands against all databases, including system. -- Read-only checks are run automatically. For safety's sake, to run -- the "repair" checks, you have to highlight that section and run it. -- SQL Server 2005+
set nocount on
declare @Sql nvarchar(4000)
--------------------------------------- -- READ-ONLY CHECKS --------------------------------------- -- Do quickest check possible.
set @Sql = 'USE [?]
RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT
DBCC CHECKDB (''?'', NOINDEX) WITH /* ?? TABLOCK, */ PHYSICAL_ONLY, ALL_ERRORMSGS '
exec sp_msforeachdb @Sql
-- Now check things beside what PHYSICAL_ONLY does: the integrity of the -- physical structure of the page and record headers, and the consistency -- between the pages' object ID and index ID and the allocation structures.
set @Sql = 'USE [?]
RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT
DBCC CHECKDB (''?'', NOINDEX) WITH /* TABLOCK, */ ALL_ERRORMSGS '
exec sp_msforeachdb @Sql
-- Now also check the indexes. This is the most thorough check possible.
set @Sql = 'USE [?]
RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT
DBCC CHECKDB (''?'') WITH /* TABLOCK, */ ALL_ERRORMSGS '
exec sp_msforeachdb @Sql
go
--------------------------------------- -- READ-WRITE CHECKS -- -- "Use the REPAIR options only as a -- last resort. To repair errors, we -- recommend restoring from a backup." ---------------------------------------
raiserror(' *** THESE ARE READ-WRITE CHECKS - BE SURE YOU KNOW WHAT YOU ARE DOING!', 16, 1) with nowait
set nocount on
declare @Sql nvarchar(4000)
-- Performs both minor, quick repairs, such as repairing extra keys in -- nonclustered indexes, and time-consuming repairs such as rebuilding -- indexes. These repairs can be performed without risk of data loss.
set @Sql = 'USE [?]
RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT
DBCC CHECKDB (''?'', REPAIR_REBUILD) WITH /* TABLOCK, */ ALL_ERRORMSGS '
exec sp_msforeachdb @Sql
-- If you want to use REPAIR_ALLOW_DATA_LOSS, you'll have to write your own command!
go

Before making the move to SQL Server, Larry specialized in Windows programming in C/Win32, C++/MFC, and C#/.NET. His blog focuses on query tuning, database optimization, and system performance, and features handy scripts, practical tips, and occasional dispatches from the many dark corners of SQL Server.