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

Configuring time in a Forest

Hi friends,

Configure time on a forest it´s easy but here I put the command if you are with a lot of work and have not enought time to check it 😉

Command to configure time in your PDC Domain Controller:

w32tm /config /manualpeerlist:time.windows.com /syncfromflags:manual /reliable:yes /update

Command to force sincronization from your PDC Domain Controller to external time server (time.windows.com in this example):

W32tm /resync /computer:time.windows.com /nowait

Restart your w32time service after these two actions

Configure time on the rest of your Domain Controllers:

w32tm /config /syncfromflags:domhier /update

Restart your w32time service after

This entry on the technet blog it´s very usefull!

http://blogs.technet.com/b/nepapfe/archive/2013/03/01/it-s-simple-time-configuration-in-active-directory.aspx

Regards!