SQL Server tuning script

Friends,

Here I put a script that tune your sql server with the best practice of Microsoft. I used to tune all my sql servers in all instances. Execute it on every instance of your sql servers and all is done.

USE [master]
GO

CREATE TABLE #numprocs
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200)
)

DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @CORES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT

INSERT INTO #numprocs
EXEC xp_msver

SELECT @CORES = Internal_Value FROM #numprocs WHERE [Index] = 16
PRINT @CORES

SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N’tempdb.mdf’, LOWER(physical_name)) – 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH

SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC =’ROWS’)

PRINT @FILECOUNT

SELECT @SIZE = size FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SET @SIZE = @SIZE / 128

SELECT @GROWTH = growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SELECT @ISPERCENT = is_percent_growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1

WHILE @CORES > @FILECOUNT
BEGIN
SET @SQL_SCRIPT = ‘ALTER DATABASE tempdb
ADD FILE
(
FILENAME = “‘+ @BASEPATH + ‘tempdb’ + RTRIM(CAST(@CORES as CHAR)) + ‘.ndf”,
NAME = tempdev’ + RTRIM(CAST(@CORES as CHAR)) + ‘,
SIZE = ‘ + RTRIM(CAST(@SIZE as CHAR)) + ‘MB,
FILEGROWTH = ‘ + RTRIM(CAST(@GROWTH as CHAR)) +”
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + ‘%’
SET @SQL_SCRIPT = @SQL_SCRIPT + ‘)’

PRINT @SQL_SCRIPT

SET @CORES = @CORES – 1
EXEC(@SQL_SCRIPT)
END
GO
DROP TABLE #numprocs

Regards!

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s