Ever wondered why SQL Server takes long time to perform below operations?
Create a database
Expand data files for additional growth
Automatic data file growth due to auto-grow settings
Restoring a database, etc.
Today we will be looking in to a powerful and yet not very popular feature that many DBAs can benefit from called SQL-WIFI (Windows Instant File Initialization).
What Happens Internally:
Every time SQL Server needs space for data or log file for any activities listed above, it will grab the required space on the disk and initialize every block of that disk with Zeros before it can use that space for data or log files. It does that to overwrite any existing data left on the disk from previously deleted files.
Imagine if you were to create a 100GB database or a restore a 500 GB database with the default configuration of SQL Server, it will first call the APIs that will overwrite every block on the disk where the file will be restored with 0 before it starts creating or restoring the database on that disk. This can take a long time depending upon how powerful your server is or the resources available on the server.
Here’s an example of creating a 100 GB database called WIFI_TEST. As can be seen from the image below, it took 11 minutes and 43 seconds to create a 100 GB database.
SQL Server WIFI (Windows Instant File Initialization) to the rescue
Initializing blocks to zero every time on an empty disk space is simply a waste of time. To prevent this, SQL Server allows you to initialize data files instantaneously. This means, SQL Server will no longer need to write Zeros on every disk block before using the disk space for above listed operations. This can save tremendous amount of time and improve the performance of above listed operations.
Before using WIFI (Creating a 100 GB database called WIFI_TEST)
After using WIFI (Creating a 100 GB database called WIFI_TEST)
As can be seen from both before and after images, a 100GB database took 11 minutes and 43 seconds and after enabling WIFI, it took only 14 seconds. This test was performed on a x64 i7 4470 CPU @ 3.4 GHz 8 Core Intel machine with 12 GB RAM. That’s a 99+% performance improvement.
Important: Only the data files can be initialized instantaneously and not the log files. Log files will always have to be zero initialized every time before they are being used by SQL Server as SQL Server uses zeros to track the point at which crash recovery is marked as complete.
In order to bypass the zeroing process, you should enable WIFI for SQL Server.
How to Enable WIFI for SQL Server:
All you need is grant SQL Server service account to PERFORM VOLUME MAINTENANCE TASK local security policy. Any user of the local administrators group can do this.
Here are the steps you can perform:
From Widows Server, Launch Local Security Policy, You can also type SECPOL.MSC and it will launch local security policy window.
From Local Security Policy, Expand Local Policies
Click on User Rights Assignment
From the right pane, double click perform volume maintenance tasks and add the account you are using to run SQL Server service and close the Window
Restart SQL Server Service
NOTE: WIFI is not available for databases that have TDE enabled.
Security Implication: There is however a security implication of Using Instant File Initialization. Without zeroing out the database files (I.e. by using WIFI), someone could copy the DB files (or take a backup of the database), and examine the data in the un-formatted pages to read the data that had previously been on that region of the disks. In that way they could gain access to data which they have no rights to, in the form of previously deleted files.
The best practice is to weigh the benefits and risks of Windows Instant File Initialization before it is implemented. However, if you are implementing it in a properly controlled environment, you’ll notice a huge performance increase for your database file operations.
We use EXEC or EXECUTE command almost every day of our database life. Its a command that allows us execute Stored Procedures (extended, system, user, CLR, etc.) and Functions. However, EXEC command also has a powerful feature that allows you to run any command remotely. Not many developers/DBA’s are aware of this feature. Historically, EXEC / EXECUTE executed commands locally, meaning if you have a stored procedure, you can only run that on your local instance of SQL Server.
EXEC or EXECUTE statement now supports a parameter called AT which allows you to specify the SQL Server instance at which you plan to execute your command from your local SQL Server instance. To use this feature, you will need to establish a linked server connection from your local SQL Server instance to the remote SQL Server instance.
Lets take an example of using EXEC or EXECUTE:
Step 1 – Setup a Linked Server
EXEC SP_AddLinkedServer 'SQLHAKANI','SQL Server'
Go
Step 2 – Enable Linked Server Connection to allow RPC Calls
Exec SP_ServerOption 'SQLHAKANI','RPC OUT',TRUE
Go
Step 3 – Execute the query on a remote server using EXECUTE
Execute ('Select * from Sys.Databases') AT [SQLHAKANI]
Go
Here’s another example of using EXECUTE
Execute ('Master.[dbo].[SP_HELPDB] ''Execute''') AT [SQLHAKANI]
Go
--Alternatively, you can also execute a parameterized stored procedure
EXECUTE ('SQLOPS.[dbo].[SP_HelpDB] ''MASTER''') AT [SQLHAKANI]
Go
DBA’s and Developers often have a need to query the current state of database file size and growth information. This script allows you to obtain file-growth information from single or all databases. It provides the following information:
1. Local Server Name 2. Database Name 3. Logical File Name 4. Initial File Size 5. File Growth Value/Percentage 6. Any Growth Restrictions
Please create and run the below stored procedure without any parameters to obtain help on how to execute this stored procedure.
CREATE Procedure [dbo].[SQLOPS_FileGrowthREPORT] (@Cmd Varchar(20)=null, @DBName Varchar(255)='ALL') as
SET NOCOUNT ON
--Author: Saleem Hakani (http://sqlcommunity.com)
--Desc: This SPROC provides you with filegrowth information for all databases of your local SQL Server instance.
If (@Cmd is null or @Cmd <> 'Execute')
Begin
Print 'Get file growth details of all databases for all files from your local SQL Server instance.'
Print ' ========================================================================='
Print 'Author: Saleem Hakani (http://sqlcommunity.com)'
Print ' '
Print 'Syntax: Exec SQLOPS_FileGrowthREPORT <@cmd>'
Print '@Cmd can be '
Print ' '
Print 'To obtain help for this stored procedure:'
Print 'Example: Exec SQLOPS_FileGrowthREPORT ''Help'';'
Print ' '
Print 'To obtain file growth information for all data and log files for all databases:'
Print 'Exec SQLOPS_FileGrowthREPORT ''Execute'';'
Print ' '
Return
End
If (@Cmd='Execute' and @DBName='ALL')
Begin
Select
UPPER(@@ServerName) as 'SERVER_NAME',
UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',
UPPER(Name) as LOGICAL_FILENAME,
SIZE/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',
(
CASE is_percent_Growth
WHEN '1' THEN convert(Varchar,Growth) +' %'
ELSE Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',
(
CASE max_size
WHEN 0 THEN 'RESTRICTED'
WHEN -1 THEN 'UNRESTRICTED'
WHEN 268435456 THEN 'UPTO 2 TB'
ELSE 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'
END
) as 'RESTRICTIONS'
FROM sys.master_files
RETURN
End
If (@Cmd='Execute' and @DBName<>'ALL')
Begin
IF Not Exists (Select Name from Sys.Databases Where Name=@DBName)
Begin
Print 'Database ['+@DBName+'] does not exist. Please enter a valid database name.'
RETURN
End
If Exists (Select Name from Sys.Databases Where Name=@DBName)
Begin
Select
UPPER(@@ServerName) as 'SERVER_NAME',
UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',
UPPER(Name) as LOGICAL_FILENAME,
SIZE/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',
(
CASE is_percent_Growth
WHEN '1' THEN convert(Varchar,Growth) +' %'
ELSE Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',
(
CASE max_size
WHEN 0 THEN 'RESTRICTED'
WHEN -1 THEN 'UNRESTRICTED'
WHEN 268435456 THEN 'UPTO 2 TB'
ELSE 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'
END
) as 'RESTRICTIONS'
from sys.master_files Where Database_ID=db_ID(@DBName)
--where Max_Size not in (-1, 268435456)
RETURN
END
END
GO
Setting the database to the right compatibility level is very important as it can have negative impact on the performance and behavior of your databases. I’ve seen many customers upgrade their SQL Servers from older versions to newer but forget to upgrade the compatibility of their databases. This means that even though you have an upgraded version of SQL Server, your database cannot benefit from any new or enhanced features of SQL Server since your database is running on an old compatibility level.
Incompatible database could also lead to inefficient query plans. Therefore, it is important that you upgrade the compatibility of databases right after the upgrade or right after you restore the database from older version to a newer version.
This script can help scan thru all the databases and provide you with a list of current and recommended database compatibility level.
CREATE Procedure [dbo].[SQLOPS_dbCompatibility] (@cmd Varchar(20)=null) as
--Author: Saleem Hakani (Http://sqlcommunity.com)
--Description: This procedure will check for database level compatibility and will report any anomoly.
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
If (@cmd is null or @cmd='Help' or @cmd <> 'Execute')
Begin
Print 'SQLOPS_dbCompatibility: Checks for database compatibility and reports if the database compatibility is not configured correctly'
Print '=================================================================='
Print 'Author: Saleem Hakani (Http://sqlcommunity.com)'
Print ' '
Print 'Syntax: Exec SQLOPS_dbCompatibility <@cmd>;'
Print '@cmd can be ''HELP'' (To get help on this stored procedure) or it can be ''EXECUTE'' (to execute this stored procedure)'
Print ' '
Print 'Example: To get help about this stored procedure'
Print 'Exec SQLOPS_dbCompatibility ''Help'';'
Print ' '
Print 'Example: To execute this stored procedure'
Print 'Exec SQLOPS_dbCompatibility ''Execute'';'
Print ' '
Return
End
If (@Cmd='Execute')
Begin
Declare @SQLVersion int
SELECT @SQLVersion = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);
BEGIN
DECLARE @DB_Compat int
SELECT @DB_Compat = COUNT([compatibility_level]) FROM master.sys.databases (NOLOCK) WHERE [compatibility_level] <> @SQLVersion * 10;
IF @DB_Compat >= 1
BEGIN
Select 'Compatibility_Level' AS [Information], name AS [Database_Name], Ltrim(str([compatibility_level]))+' (Please upgrade to '+ltrim(str(@SQLVersion))+'0)' AS [Compatibility_Level]
FROM master.sys.databases (NOLOCK)
WHERE [compatibility_level] <> @SQLVersion * 10;
END
ELSE
BEGIN
SELECT 'Compatibility level for all databases' AS [Check], '[OK]' AS [Results]
END
END
End
GO
DBAs and Developers at times may need to detach and attach the databases for various reasons including the following:
1. Moving files to other drives 2. Server migration 2. Upgrades, 3. system maintenance, etc.
This script takes away the headache of remembering the filename or folder location of every single data or log file for every single user databases on your server. It allows you to generate script to detach and attach one or all databases avoiding the need to remember the long hard-coded file path after detaching the database.
CREATE Procedure [dbo].[SQLOPS_AttachDETACH] (@Cmd Varchar(20)=null, @DBName Varchar(50)='ALL') as
Set NOCOUNT ON
--------------------------------------------
--Author: Saleem Hakani (Http://sqlcommunity.com)
--Compatible with SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017 +
--Desc: This procedure can be used to generate detach and attach script for one or all the databases
--Note: This SPROC also takes care of the 16 file limitation with SP_Attach_DB system stored proceure.'
--------------------------------------------
If (@Cmd is null or @Cmd <> 'Execute')
Begin
Print 'HELP for SQLOPS_AttachDETACH'
Print '============================'
Print 'Author: Saleem Hakani (Http://sqlcommunity.com)'
Print ' '
Print 'This procedure can be used to generate detach and attach script for one or all the databases.'
Print 'It also takes care of the 16 files limitation by that SP_attach_db system sproc has.'
Print ' '
Print 'SYNTAX: Exec SQLOPS_AttachDETACH <@cmd>, <@DBName>'
Print '@cmd: Can be either ''Help'' (to get help) or ''Execute'' (to execute this sproc)'
Print '@DBName: Can either be the name of the database or ''ALL'' (default) which means run this on all databases'
Print ' '
Print 'Example: To generate a script for attaching and detaching a specific database (Ex: SQLOPS database)'
Print 'Exec SQLOPS_AttachDETACH ''Execute'',''SQLOPS'';'
Print ' '
Print 'Example: To generate a script for attaching and detaching all user databases from local SQL Server'
Print 'Exec SQLOPS_AttachDETACH ''Execute'',''ALL'''
Print ' '
Print 'To obtain help'
Print 'Exec SQLOPS_AttachDETACH ''HELP'''
Return
End
If (@cmd='Execute' and @DBName='All')
Begin
Exec SP_MSForEachDB 'Exec [SQLOPS_AttachDETACH] ''Execute'',[?]'
RETURN
End
If (@cmd='Execute' and @DBName <> 'All')
Begin
If NOT EXISTS (SELECT NAME from Sys.Databases Where Name=@DBName)
Begin
Print 'Database ['+@DBName+'] does not exist. Please enter a valid database name.'
RETURN
End
If Exists (SELECT NAME from Sys.Databases Where Name=@DBName)
Begin
Declare @a Varchar(8000),@b Varchar(8000),@c Varchar(8000),@d Varchar(8000)
Declare @FileID Int,@str int,@Count1 int
Create Table ##Count1 (Count1 int)
Declare @FileName Varchar(500)
Select @str=0
Print '--------------------------------------------------------------------'
Print ''
Print 'SP_Detach_DB ['+Ltrim(@DBName)+']'
Print 'Go'
Print ''
Print 'Create Database ['+Ltrim(@DBName)+'] ON PRIMARY'
Exec('Insert into ##count1 Select Count(*) from '+@DBName+'..SysFiles')
Select @Count1=Count1 from ##Count1
Exec('Declare CheckTab Cursor For Select FileID,FileName from '+@DBName+'..SysFiles Order by FileID')
Open CheckTab
Fetch Next from CheckTab into @FileID,@FileName
While (@@Fetch_Status=0)
Begin
Select @Str=@str+1
If @Str < @Count1
Begin
Select @b='(FileName='''+Ltrim(Rtrim(@FileName))+'''),'
End
If @Str = @Count1
Begin
Select @b='(FileName='''+Ltrim(Rtrim(@FileName))+''') FOR ATTACH'
End
Print @b
Fetch Next from CheckTab into @FileID,@FileName
End
Print 'Go'
Drop Table ##Count1
Close CheckTab
Deallocate CheckTab
End
RETURN
END
GO
Index Statistics are important for query optimizer to generate optimal query plans. If indexes have outdated or stale statistics, your query’s performance will suffer. It is important to update the statistics to make sure query optimizer always generates the best and optimal plan.
This script allows you to report and update outdated statistics.
In reporting mode, this script provides the following information:
1. Name of the Database 2. Table Name 3. Index Name 4. Last date when Statistics was Updated 5. Row Count of the Table 6. Number of Rows Changed 7. Percentage of Row Changes Occurred Since Last time Statistics were Updated 8. Script for you to manually execute Update Statistics on individual objects
Create Procedure [dbo].[SQLOPS_UpdateStatistics] (@cmd Varchar(10)=null,@DBName Varchar(255)=null,@Update Varchar(5)='NoFix') as
SET NOCOUNT ON
--Author: Saleem Hakani (Http://sqlcommunity.com)
IF (@cmd is null or @cmd <> 'Execute' or @DBName is null)
Begin
Print 'SQLOPS_UpdateStatistics (Author: Saleem Hakani)'
Print '=========================================='
Print ' '
Print 'This procedure allows you to report and/or update outdated/stale statistics for individual indexes,'
Print 'for all indexes in a databases and for all indexes across all user databases. When generating the report, it provides:'
Print '1. Name of the Database'
Print '2. Table Name'
Print '3. Index Name'
Print '4. Last date when Statistics was Updated'
Print '5. Row Count of the Table'
Print '6. Number of Rows Changed'
Print '7. Percentage of Row Changes Occurred Since Last time Statistics were Updated'
Print '8. Script for you to manually execute Update Statistics on individual objects'
Print ' '
Print 'Syntax: SQLOPS_UpdateStatistics <@Cmd>, <@DBName>, <@Update>'
Print '@Cmd: Can be EXECUTE (To execute this SPROC) or HELP (To obtain this Help)'
Print '@DBName: Can be the name of an individual database or ''ALL'' for all databases'
Print '@Update: Can be ''Fix'' (to Update Statistics) or ''NoFix'' (Just provide the Report)'
Print ' '
Print 'To Get a list of outdated statistics from SQLOPS database:'
Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''SQLOPS'''
Print ' '
Print 'To Get a List and to fix Outdated Statistics from SQLOPS database:'
Print 'Exec SQLOPS_UpdateStatistics ''EXECUTE'',''SQLOPS'',''Fix'''
Print ' '
Print 'To Get a List of Outdated Statistics from all User Databases:'
Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''ALL'''
Print ' '
Print 'To Get a list and to fix Outdated Statistics for all User Databases'
Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''ALL'', ''Fix'''
Return
End
Declare
@DBNameALL Varchar(255),
@SQLStr1 NVarchar(4000),
@SQLStr NVarchar(4000),
@ExedbSTR NVARCHAR(4000)
Declare @DBStats Table
(
DBName Varchar(255),
TableName Varchar(255),
IndexName Varchar(255),
Statistics_Last_Update Varchar(30),
ROW_COUNT BigInt,
ROWS_CHANGED BIGINT,
Percent_Changed Decimal(16,2),
Script Varchar(2000)
)
If (@cmd ='Execute') and (@DBName <> 'ALL')
Begin
IF Not Exists (Select Name from Sys.Databases where Name=@DBName)
Begin
Print 'Database ['+Upper(@DBName)+'] Does Not Exist in the System. Please Re-Enter a Valid Database Name'
Return
End
Set @SQLStr='Use ['+@DBName+'];
SELECT db_Name() as DB_Name
,SYSSCH.name+''.''+SYSTAB.name AS TableName
,SYSIND.name AS IndexName
,ISNULL(Convert(Varchar,STATS_DATE(SYSIND.id,SYSIND.indid)),''DATE NOT FOUND'') AS ''Statistics_Last_Updated''
,SYSIND.rowcnt AS ''RowCount''
,SYSIND.rowmodctr AS ''Rows_Changed''
,CAST((CAST(SYSIND.rowmodctr AS DECIMAL(28,8))/CAST(SYSIND.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''%_Changed''
,''USE [''+DB_Name()+'']; Update Statistics ''+SYSSCH.NAME+''.''+SYSTAB.Name+'' [''+SYSIND.name+''];'' as ''Script''
FROM sys.sysindexes SYSIND
INNER JOIN sys.tables SYSTAB ON SYSTAB.[object_id] = SYSIND.[id]
INNER JOIN sys.schemas SYSSCH ON SYSSCH.[schema_id] = SYSTAB.[schema_id]
WHERE SYSIND.id > 100
AND SYSIND.indid > 0
AND SYSIND.rowcnt >= 500
AND SYSIND.rowmodctr > 0
ORDER BY 7 DESC'
Insert into @DBStats Exec SP_ExecuteSQL @SQLSTR
End
--If All Databases
If (@cmd='Execute' and @DBName = 'ALL')
Begin
Declare CheckDB Cursor for Select Name from Sys.Databases where Name not in ('Master','Model','MSDB','TempDB') and State=0
Open CheckDB
Fetch Next from CheckDB into @DBNameALL
While (@@FETCH_STATUS=0)
Begin
Set @SQLStr1='Use ['+@DBNameALL+']; SELECT db_Name() as DB_Name
,SYSSCH.name+''.''+SYSTAB.name AS TableName
,SYSIND.name AS IndexName
,ISNULL(Convert(Varchar,STATS_DATE(SYSIND.id,SYSIND.indid)),''DATE NOT FOUND'') AS ''Statistics_Last_Updated''
,SYSIND.rowcnt AS ''RowCount''
,SYSIND.rowmodctr AS ''Rows_Changed''
,CAST((CAST(SYSIND.rowmodctr AS DECIMAL(28,8))/CAST(SYSIND.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''%_Changed''
,''USE [''+DB_Name()+'']; Update Statistics ''+SYSSCH.NAME+''.''+SYSTAB.Name+'' [''+SYSIND.name+''];'' as ''Script''
FROM sys.sysindexes SYSIND
INNER JOIN sys.tables SYSTAB ON SYSTAB.[object_id] = SYSIND.[id]
INNER JOIN sys.schemas SYSSCH ON SYSSCH.[schema_id] = SYSTAB.[schema_id]
WHERE SYSIND.id > 100
AND SYSIND.indid > 0
AND SYSIND.rowcnt >= 500
AND SYSIND.rowmodctr > 0
ORDER BY 7 DESC'
Insert into @DBStats Exec SP_ExecuteSQL @SQLSTR1
Fetch Next from CheckDB into @DBNameALL
End
Close CheckDB
Deallocate CheckDB
End
If (@Update='Fix')
Begin
If (@DBName<>'ALL')
Begin
Declare CheckDBFix Cursor for Select Script from @DBStats
End
If (@DBName='ALL')
Begin
Declare CheckDBFix Cursor for Select Script from @DBStats
End
Open CheckDBFix
Fetch Next from CheckDBFix into @ExedbSTR
While (@@FETCH_STATUS=0)
Begin
Exec SP_ExecuteSQL @ExedbSTR
Print 'Executed: '+@ExedbSTR
Fetch Next from CheckDBFix into @ExedbSTR
End
Close CheckDBFix
Deallocate CheckDBFix
End
If (@Update <> 'Fix')
Begin
If (Select count(*) from @DBStats) >=1
Begin
Select * from @DBStats order by 7 DESC
Return
End
If (Select count(*) from @DBStats) = 0
Begin
Print 'There are no statistics to be updated.'
Return
End
Return
End
GO
Recent Comments