Thursday 18 July 2013

Perform volume maintenance tasks


To achieve best performance  in SQL server while creating databases, altering the database, adding the space manually, autogrowth, Especially while restoring the database we can achieve better performance using Instant File initialization works better, we will see how it works.

Instant Initialization is a new feature of SQL Server 2005 that is based on an NTFS feature that was added to Windows XP (and therefore is also available in Windows 2003 Server). It's a feature that's seemingly simple; it allows file allocation requests to skip zero initialization on creation. As a result, file allocation requests can occur instantly – no matter what the file size.

This feature is not enabled by default in Microsoft, if you need to enable this we require SQL service account or Local account SQL service

(SQLServer2005MSSQLUser$SERVERNAME$MSSQLserver) group to add to group policy.

Go to run -> GPEDIT.msc -> winowssettings->Security Settings-->Local Policy-->User Right settings-->
or

Go to run--> SECPOL-->Local Policy-->User Right settings


and add SQL servie acc to group
secpol


 
Here is an one example while creating databases where we can achieve performance while creating databases.

Now I’m going to create a database. How long does it take to create a file?

SET STATISTICS TIME ON;
 
CREATE DATABASE IFIOffTest
ON
( NAME = IFIOffTest_dat,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOffTest_dat.mdf',
 SIZE = 1000,
 MAXSIZE = 2000,
 FILEGROWTH = 100 )
LOG ON
( NAME = IFIOffTest_log,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOffTest_log.ldf',
 SIZE = 100MB,
 MAXSIZE = 300MB,
 FILEGROWTH = 20MB ) ;
GO
Statistics Time tells me:
SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 4665 ms.


Now I go to Local Security Policy and add SQL Service acc to Perform volume maintenance tasks, and restart the SQL Server service.

I create a second database.
 
SET STATISTICS TIME ON;
 
CREATE DATABASE IFIOnTest
ON
( NAME = IFIOnTest_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOnTest_dat.mdf',
    SIZE = 1000,
    MAXSIZE = 2000,
    FILEGROWTH = 100 )
LOG ON
( NAME = IFIOnTest_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOnTest_log.ldf',
    SIZE = 100MB,
    MAXSIZE = 300MB,
    FILEGROWTH = 20MB ) ;
GO
 
What does Statistics Time give me here?
SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 659 ms.

I created two databases on the same server, the same hard drive, with the same file sizes. With IFI turned on, the database was created in 14% of the time. And that’s just with a 1,000 MB file! Imagine the time savings as your database scales up
 

No comments:

Post a Comment