Archive for March, 2013

How to backup a database to split to multiple backup files?

This past week, a request was made for us to configure database mirroring for a database that’s over 100 GB. The native SQL Server backup file was approximately 80 GB. The primary data center is located in NYC and the secondary data center is located in Miami. That’s 1,283 miles apart according to Google Maps.  Based on our past experiences transferring a backup file that’s over 80 GB will likely result in failure as there will be possible data packet losses or intermittent network connections that will cause file transfer problems. We have decided to use the split backups features that’s available in SQL Server to backup to multiple backup files to decrease the backup files sizes, transfer them one by one, and then restore them to the Mirrored server. Here’s the overview steps and the example implementation steps required:

Overview steps:

  1. Backup the database to split to multiple backup files to decrease the files sizes.
  2. Transfer the backup files one by one over to the Mirrored server.
  3. Restore the database in the Mirrored server.
  4. Run the transaction log backup(s) in the Principal server.
  5. Transfer the transaction log backup files over to the Mirrored server.
  6. Restore the transaction log backups in the Mirrored server.
  7. Configure database mirroring once the transaction logs are up-to-date in the Mirrored server.

Implementation steps:

    1. Backup the database to split to multiple backup files to decrease the files sizes.
      -- Backup the database to split to multiple backup files
      BACKUP DATABASE AdventureWorks
      TO DISK = 'C:\SQLBACKUPS\AW\aw_file1.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file2.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file3.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file4.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file5.bak'
      WITH STATS = 10 -- report the backup progress status every with every 10 percent elapses
      
    2. Transfer the backup files one by one over to the Mirrored server.
    3. Restore the database in the Mirrored server.
      -- Restore the database in the Mirrored server
      RESTORE DATABASE AW
      FROM DISK = 'C:\SQLBACKUPS\AW\aw_file1.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file2.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file3.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file4.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file5.bak'
      WITH MOVE 'AdventureWorks_Data' TO 'C:\AW\AW_data.mdf',
      MOVE 'AdventureWorks_Log' TO 'C:\AW\AW_log.ldf',
      NORECOVERY -- restore with no recovery because you will need to restore the transaction logs
      
    4. Run the transaction log backup(s) in the Principal server.
      -- Run the transaction log backup(s) in the Principal server
      BACKUP LOG AdventureWorks
      TO DISK = 'C:\SQLBACKUPS\AW\aw_tlog.trn'
      
    5. Transfer the transaction log backup files over to the Mirrored server.
    6. Restore the transaction log backups in the Mirrored server.
      -- Restore the transaction log backup(s) to the Mirrored server
      RESTORE LOG AW
      FROM DISK = 'C:\SQLBACKUPS\AW\aw_tlog.trn'
      WITH NORECOVERY -- restore with no recovery as you may have more transaction logs to restore
      
    7. Configure database mirroring once the transaction logs are up-to-date in the Mirrored server.

You can refer to the post on how to setup database mirroring for more details on the configuration steps.

REFERENCES:

BACKUP
http://msdn.microsoft.com/en-us/library/ms186865(v=sql.105).aspx

RESTORE
http://msdn.microsoft.com/en-us/library/ms186858(v=sql.105).aspx

How to detect missing database backups?

Equally important to scheduling database backups is to at the same time get alerted when the database backups are missing. This will help you sleep better at night without AmBien or the preferred extra shots of Tequila. When backups are ran, the histories get written to the msdb.dbo.backupset system table.  We can query it to find the backup history information or the lack of it of which implies no backups were ever taken. Yes. It is that simple.

If you have scheduled full database backups to run once per day and transaction log backups to run every 3 hours, it is within your best interest to get alerted if the full backups are missing with the time elapsed passed 24 hours and the same for transaction log backups with the time elapsed passed 3 hours.

Here’s the code to query for missing full backups with time elapsed passed 24 hours or more:

-- missing full backups, time elasped > 24
SELECT db.name as DatabaseName
, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
	ON db.name = bs.database_name and bs.type IN ('D') -- backup type D = Database, I = Differential, L = Log
WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
	AND db.state = 0 -- database state is online
GROUP BY db.name
HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) > 24
	OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
ORDER BY db.name	

Here’s the code to query for missing transaction backups with time elapsed passed 3 hours or more:

-- missing tlog backups, time elasped > 3 hours
SELECT db.name as DatabaseName
, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
	ON db.name = bs.database_name and bs.type IN ('L') -- backup type D = Database, I = Differential, L = Log
WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
	AND db.state = 0 -- database state is online
	AND db.recovery_model < 3 -- recovery model: 1 = full, 2 = bulk_logged, 3 = simple
GROUP BY db.name
HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) > 3
	OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
ORDER BY db.name

To send out the alerts in html report format via database mail, you can embed the code in html and schedule a job to run hourly. Here’s the example for the alert on missing full backups:

-- Alert on missing full backups with time elapsed over 24 hours
-- Replace with your recipients in line #31

-- declare the variable for interval hours to check for full database backups
DECLARE @IntervalInHours TINYINT -- check new database(s) in the past hour(s)
SET @IntervalInHours = 24 -- replace with the number of hours you wish to check for missing full backups with time elapsed

-- check for missing full backups
IF EXISTS(	
	SELECT db.name as DatabaseName
	, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
	FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
		ON db.name = bs.database_name and bs.type IN ('D') -- backup type D = Database, I = Differential, L = Log
	WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
		AND db.state = 0 -- database state is online
	GROUP BY db.name
	HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) >= @IntervalInHours
		OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		) 
		
BEGIN -- missing full backups with time elapsed over 24 hours

-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html><table border = "1">' +
		'<tr>
			<th>Database Name</th>
			<th>Last Backup DateTime</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td>' + db.name + '</td>' +
			'<td>' + ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') + '</td></tr>'
		FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
			ON db.name = bs.database_name and bs.type IN ('D') -- backup type D = Database, I = Differential, L = Log
		WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
			AND db.state = 0 -- database state is online
		GROUP BY db.name
		HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) >= @IntervalInHours
			OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		ORDER BY db.name	
		
		SELECT @TableHTML = @TableHTML + '</table></html>'
		
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' missing full database backups alert!'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
END

This is what it looks like when the alert hits your email inbox (click on the image to view the full size):
Missing_Full_Backups

Here’s the example for the alert on missing transaction log backups:

-- Alert on missing transaction log backups with time elapsed over 3 hours
-- Replace with your recipients in line #32

-- declare the variable for interval hours to check for missing transaction log backups
DECLARE @IntervalInHours TINYINT 
SET @IntervalInHours = 3 -- replace with the number of hours you wish to check for missing transaction log backups with time elapsed

-- check for missing transaction log backups
IF EXISTS(	
	SELECT db.name as DatabaseName
	, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
	FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
		ON db.name = bs.database_name and bs.type IN ('L') -- backup type D = Database, I = Differential, L = Log
	WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
		AND db.state = 0 -- database state is online
		AND db.recovery_model < 3 -- recovery model: 1 = full, 2 = bulk_logged, 3 = simple
	GROUP BY db.name
	HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) >= @IntervalInHours
		OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		) 
		
BEGIN -- missing transaction log backups with time elapsed over 3 hours

-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html><table border = "1">' +
		'<tr>
			<th>Database Name</th>
			<th>Last Backup DateTime</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td>' + db.name + '</td>' +
			'<td>' + ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') + '</td></tr>'
		FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
			ON db.name = bs.database_name and bs.type IN ('L') -- backup type D = Database, I = Differential, L = Log
		WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
			AND db.state = 0 -- database state is online
			AND db.recovery_model < 3 -- recovery model: 1 = full, 2 = bulk_logged, 3 = simple
		GROUP BY db.name
		HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) > @IntervalInHours
			OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		ORDER BY db.name	
		
		
		SELECT @TableHTML = @TableHTML + '</table></html>'
		
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' missing transaction log backups alert!'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
END

This is what is looks like when the alert hits your email inbox(click on the image to view the full size):
Missing_Tlog_Backups

REFERENCES:

sys.databases
http://msdn.microsoft.com/en-us/library/ms178534(v=sql.105).aspx

backupset
http://technet.microsoft.com/en-us/library/ms186299(v=sql.105).aspx

DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794(v=sql.105).aspx

Road Trip: Microsoft Mid-town Manhattan Office

Back on February 28, 2013, I attended the NYC SQL Server User Group meeting event. Rather than bore you all with the technical mumbo jumbo, I have decided to change the scenery and show you the kodak moments and photos that I have captured around 30 Rock.

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

Today, I have found one of the index maintenance jobs in a SQL Server 2005 instance has failed. The history shows a helpful message:

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

This error is caused by the index reorganization step in the attempt to defragment the index in question but the index has the page level locking option disabled. The job fails and the rest of the indexes will not get worked on. On the next subsequent scheduled run of the job, the same scenario will happen until you enable page locking for this index and the indexes with page level locking disabled. Yes. It is really a royal pain in the behind. You can’t just run away from it and hoping the problem will go away. You have to man up to understand and resolve the problem.

By default, page level locking is enabled for indexes. Usually, the page level locking for the indexes get disabled unintentionally either by third-party tools or by using RTM version of SQL Server 2005 and creating indexes via the GUI in the Object Explorer. In case, you know of any other way the page level locking get disabled for the indexes, please let me know.

To find all the indexes in the database that have page level locking disabled, you can run the following query:

-- indexes that have page level locking disabled
SELECT OBJECT_NAME(i.object_id) as TableName
, name as IndexName
, allow_page_locks
FROM sys.indexes as i
WHERE ALLOW_PAGE_LOCKS = 0

You can then submit a change request to have these indexes modified to enable page level locking.

To generate the code to alter the indexes to enable page level locking for all the required indexes, you can run the following query. This query will omit the internal system tables that do not have the page level locking for the indexes:

-- generate the TSQL Statments to enable page level locking for the required indexes
SELECT 'ALTER INDEX ' +i.Name+ ' ON ['
	+ts.TABLE_CATALOG+ '].[' +ts.TABLE_SCHEMA+ '].[' +ts.TABLE_NAME+']'
	+' SET (ALLOW_PAGE_LOCKS = ON)' as TSQL_Statement
FROM sys.indexes i LEFT OUTER JOIN sys.tables t
		ON i.object_id = t.object_id
	JOIN INFORMATION_SCHEMA.TABLES ts
		ON ts.TABLE_NAME = t.name
WHERE i.allow_page_locks = 0
	AND t.Name IS NOT NULL

Save the output and run it on the database in question and then try the index maintenance job again. This will solve the page level locking is disabled error.

 

REFERENCES:

sys.indexes
http://msdn.microsoft.com/en-us/library/ms173760(v=sql.105).aspx

sys.tables
http://msdn.microsoft.com/en-us/library/ms187406(v=sql.105).aspx

INFORMATION_SCHEMA.TABLES
http://msdn.microsoft.com/en-us/library/ms186224(v=sql.105).aspx

Problems with Service Pack 4 Upgrade for a SQL Server 2005 Clustered Instance on Windows Server 2008 due to lower case in the names of the clustered nodes…

Earlier this week, as a security best practices we had been approved to install service pack 4 for a SQL Server 2005 clustered instance on Windows Server 2008. After we had successfully upgraded to the latest service pack 4, we encountered database mail failure and replication LogReader Agent error just to name two of the issues. After spending hours of researching online, we had found that the root cause of the problem is that the Resource database did not get updated along all the required objects for SQL Server 2005 because the Windows Server 2008 clustered nodes are in lower case. As per http://blogs.msdn.com, “…The datastore of the SQL Server setup presents the node names in the same case, however Windows Installer presents them in the upper case. The setup thinks that we are running it from the passive node and it does not upgrade Resource Database and other system databases.” http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/16/unable-to-create-maintenance-plans-after-service-pack-2-is-applied-on-sql-server-2005-fail-over-cluster-instance-running-on-windows-2008-cluster.aspx. The workaround involves evicting the nodes, changing the names to upper case, joining the nodes back to the cluster, and then re-running the service pack 4 upgrade and the cumulative update package.

The Resource database contains all the system objects for the SQL Server instance. It is read-only. To find the service pack and the Resource database version, you can run the following query:

SELECT
@@SERVERNAME AS InstanceName
,SERVERPROPERTY('Edition') AS 'Edition'
,SERVERPROPERTY('ProductVersion') AS 'ProductVersion'
,SERVERPROPERTY('ProductLevel') AS 'ProductLevel'
,SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime'
,SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'

UPDATED March 16, 2013:  In our testing environment, we did not have to evict the nodes and then change the node names to UPPER CASE. All we did was to install the Cumulative update package 3 for SQL Server 2005 Service Pack 4 http://support.microsoft.com/kb/2507769. That resolved the issue.

It is critical to be aware that patching for a SQL Server 2005 clustered instance is different from SQL Server 2008 plus clustered instance. In SQL Server 2005 clustered instances, you will need to patch only the active node. The passive node will get updated as well. But, in SQL Server 2008 and R2 clustered instances, you will need to patch both nodes. You will first need to patch the passive node, do a failover, and then patch the current passive node. In addition, don’t forget the hot fixes for the latest service packs. For example, for SQL Server 2005 service pack 4, which is the latest service pack, if you are not careful you will be left out the cumulative update package 3 for SQL Server 2005 service pack 4.

Let it be warned, before upgrading to service pack 4 and the cumulative update package for a SQL Server 2005 clustered instances on Windows Server 2008, make sure you check the cases for the clustered nodes. Make a request to your System Administration team to correct the nodes to upper case before you work on the service pack upgrades and the cumulative update package

REFERENCES:

Unable to create Maintenance Plans or DBMail Fails after service pack/Hotfix is applied on SQL Server 2005 fail over cluster instance running on Windows 2008 cluster
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/16/unable-to-create-maintenance-plans-after-service-pack-2-is-applied-on-sql-server-2005-fail-over-cluster-instance-running-on-windows-2008-cluster.aspx

Resource Database
http://msdn.microsoft.com/en-us/library/ms190940(v=sql.90).aspx

The job failed. Unable to determine if the owner (DOMAIN\Username) of job “JOB NAME” has server access…

Yesterday, I received a request to troubleshoot one of the SQL Server Agent job failures.  The job history reveals the message in the following format:

“The job failed. Unable to determine if the owner (DOMAIN\Username) of job “JOB NAME” has server access (reason: Could not obtain information about Windows NT group/user ‘DOMAIN\username”. [SQLSTATE 42000] (Error 8198))”

The quick fix is to set the job owner to the SQL Server Agent service account and restart the job if restarting is permitted. If you are not certain what the job does as it may contain critical business logics and functions, you will need to check with the application team, business owner(s) of the server, and all the required contacts before restarting the job and also get the business approval if needed. No kidding.

Let’s elaborate on more details on a more sustainable solution. By default, the job owner will be set to the login that had created the job. When a job executes, SQL Server will run the job as the SQL Server Agent account if the job owner belongs to the sysadmin server role. If the job owner does not belong to the sysadmin role, then SQL Server will impersonate the job owner login to execute the job. Even if the job owner login belongs to the sysadmin server role, if the password for the domain\user login for the job owner has expired or the domain\user account is disabled, then the job will also fail. If you need to tighten security for the job steps, Microsoft recommends you to create SQL Server Agent proxy accounts as the proxy accounts define the security context in which the job step can run. The steps are as follows:

  1. Crate dedicated user accounts for proxies.
  2. Grant the necessary permissions to the proxy user accounts.
  3. Assign the corresponding proxy accounts to the job steps.

 

REFERENCES:

Implementing SQL Server Agent Security
http://msdn.microsoft.com/en-us/library/ms190926(v=sql.90).aspx

How to: Create a Proxy
http://msdn.microsoft.com/en-us/library/ms190698(v=sql.90).aspx

What is an Index in SQL Server?

Similar to the index in the back of a book of which enables you to seek for the relevant terms without scanning the entire book, an index in SQL Server allows search operations to find data in a table without scanning all rows in the entire table.  There are two possible types of indexes in SQL Server. They are clustered and non-clustered.

There can be only one clustered index in a table. This similar to a dictionary – usually a big reference book that lists all the terms in alphabetical order with definitions next to the terms. You may be able to find them in museums, libraries, or in your grandparents’ homes. A clustered index is similar to a dictionary because data can be ordered in only one way. Usually, ordered in numeric sequence or in alphabetical order. When you create a primary key for a table which uniquely identifies each record in the table, by default a clustered index is also created. Even though you can override this default behavior, it is usually not recommended.  When a clustered index is created, it establishes an order for the data.

There can be 249 non-clustered indexes in SQL Server 2005 and 999 in SQL Server 2008. This is because you can order the data in many different ways by creating different non-clustered indexes to speed up the data retrieval process. The non-clustered indexes usually don’t include all the columns of the table. They usually contain only a selected number of columns. Non-clustered indexes are separate ordered structures from the base table. They can be stored in different drives and/or filegroups. This enables SQL Server to access the table and the indexes concurrently if needed to speed up querying.

Considering the benefits of the clustered and non-clustered indexes, even though you can only have one clustered index, you may be tempted to create many non-clustered indexes. Similar to everything in life, you will need to find the perfect balance or you will suffer the consequences. This is similar to a hangover after too many beers or glasses of Hennessy Paradis on a great night out in the city or having too many crispy crème donuts. While the indexes speed up data retrieval, they have negative impacts on insert, update, and delete statements. As data rows get inserted, updated, or deleted, so will the indexes. When you add a row to a table, the row will also be added to the index. The same goes for updates and deletes. In addition, more disk space and memory are required. For clustered indexes, the ordering of the rows will also need to be re-adjusted and this will also require the adjustment of the subsequent rows below the rows being affected.  An interesting note is that in order to find the row to update or delete, the indexes will aid in speeding up the process of finding the row.

 

REFERENCES:

Indexes
http://msdn.microsoft.com/en-us/library/aa933129(v=sql.80).aspx

Table and Index Architecture
http://msdn.microsoft.com/en-us/library/aa174541(v=sql.80).aspx

How to find the query IO in terms of reads and writes?

You are interested in finding out the IO in terms of reads for the query execution before tuning activities such as adding a missing index or updating statistics so that you can see the comparisons of improvements. What can you do?

You can wrap the query around the SET STATISTICS IO ON and SET STATISTICS IO OFF statements to cause SQL Server to display the information regarding the amount of disk activity generated by the query. Needless to say, when STATISTICS is ON, the statistical information is displayed. When set to OFF, the information is not displayed. For example:

SET STATISTICS IO ON
SELECT p.ProductID
, p.Name
, pch.StandardCost
, pch.StartDate
, pch.EndDate
, i.Quantity
, i.Shelf
FROM Production.Product AS p
	JOIN Production.ProductCostHistory AS pch
		ON p.ProductID = pch.ProductID
	JOIN Production.ProductInventory AS i 
		ON p.ProductID = i.ProductID	
SET STATISTICS IO OFF	

Output:

(583 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ProductInventory’. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Product’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ProductCostHistory’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In the output, table is the name of the table. When the scan count is 0, the query is using a clustered or unique index to seek for one value. When 1, the query is searching for one value using a non-unique clustered index which is defined on a non-primary key column. When the scan count is higher than 1, then the number represents the number of different seek/scan to locate the value. Two of the most critical values to pay attention to are the “logical reads” and “physical reads.” Logical reads are the number of pages read from the data cache. Physical reads are the number of pages read from disk. Logical reads are preferred while physical reads are not desirable. You can look up all other output items and meanings in the reference link.

An interesting and very critical note about the STATISTICS IO is that the logical reads is not affected by other system resources. In STATISTICS TIME, the values for the CPU time and elapsed time are affected by the system resources. For example, if a heavy duty data loading job runs at the same time as your query, the CPU time and elapsed time will vary if you had run it on a time where the data loading job is not running. But, the logical reads in STATISTICS IO will be the same if you had not tuned the query with optimization.

You can attempt to add possible missing indexes to the tables or update the statistics and then re-run the query with the SET STATISTICS IO ON statement to do a comparison for the logical reads, physical reads, and other items to observe the improvements if there are any.

REFERENCES:

SET STATISTICS IO
http://msdn.microsoft.com/en-us/library/ms184361(v=sql.105).aspx

How to find the query execution time in terms CPU usage and duration?

You are interested in finding out the query execution time in terms CPU usage and total duration before tuning activities such as adding a missing index or updating statistics so that you can see the comparisons of improvements. What can you do?

To obtain such detailed information on the amount of time in milliseconds to parse, compile, and execute a query, you can wrap the query with the SET STATISTICS TIME ON and SET STATISTICS TIME OFF statements. When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When set to OFF, the time statistics are not displayed. For example:

SET STATISTICS TIME ON
SELECT p.ProductID
, p.Name
, pch.StandardCost
, pch.StartDate
, pch.EndDate
, i.Quantity
, i.Shelf
FROM Production.Product AS p
	JOIN Production.ProductCostHistory AS pch
		ON p.ProductID = pch.ProductID
	JOIN Production.ProductInventory AS i 
		ON p.ProductID = i.ProductID	
SET STATISTICS TIME OFF

Output:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(583 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 106 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

CPU time = 0 ms and elapsed time = 0 ms for parse and compile time are indications that the optimizer reused an existing execution plan and avoided spending time parsing and compiling the query again. In this example, I had already run this query as a test a number of times. On the first execution, the optimizer had parsed and compiled it to produce the execution plan and stored it as a cached plan.

The “SQL Server Execution Times:” section shows the required CPU time is 16 ms and the elapsed time is 106 ms where ms stands for milliseconds.

You can attempt to add possible missing indexes to the tables or update the statistics and then re-run the query with the SET STATISTICS TIME ON statement to do a comparison for the CPU time and elapsed time to observe the improvements if there are any.

REFERENCES:

SET STATISTICS TIME
http://msdn.microsoft.com/en-us/library/ms190287(v=sql.105).aspx

What is an execution plan and how to save its various formats?

On a daily basis, we receive requests to perform tasks that require planning. For example, travel a designated location or prepare a meal for our family. If we had been to the designated location in the past, we will use the same travelling plan or direction to go to that location and if we had ready had experience cooking the dishes we are preparing we will likely use the same steps like we had done in the past. In a similar fashion, when SQL Server receives a request to execute a query, its internal query optimizer will attempt to calculate the most cost effective and efficient way to execute the query and store the plan in the plan cache. If a plan to execute the query had already existed, the SQL Server query optimizer will use the plan that’s already cached.

There are two types of execution plans. They are estimated execution plans and actual execution plans. All execution plans are stored in a section of memory called the plan cache. When a query is submitted to SQL Server, the optimizer creates an estimated execution plan and compares it with an actual plan that’s already stored in the plan cache. If they match, then the optimizer will use the plan that’s already stored in the plan cache since it is an actual query plan and it’s been used previously by the query engine. This reduces the overhead of creating another actual execution plan and then storing it in the plan cache.

You can view the execution plans in 2 formats. They can be in graphical or XML. Graphical plans are quicker and easier to read. You can also save the XML execution plans into graphical plans. Here’s an example of how to use both to view the estimated and actual execution plans for the following query:

SELECT p.ProductID
, p.Name
, pch.StandardCost
, pch.StartDate
, pch.EndDate
, i.Quantity
, i.Shelf
FROM Production.Product AS p
	JOIN Production.ProductCostHistory AS pch
		ON p.ProductID = pch.ProductID
	JOIN Production.ProductInventory AS i 
		ON p.ProductID = i.ProductID			

1. Open SSMS, copy the above query and paste it in a new query window.
2. To view the graphical estimated execution plan, right-click on the query window and then select “Display Estimated Execution Plan.” The plan will be shown on the lower portion of the window. You can save the graphical estimated execution plan by right-clicking on the “Execution Plan” window and click on the “Save Execution Plan As…” button.
3. To view the graphical actual execution plan, right-click on the query window and then select “Include Actual Execution Plan.” You will then need to execute the query. The results will be displayed on the lower portion of the window in addition to the actual execution plan. You may need to click on the “Execution Plan” tab to see the graphical actual execution plan. You can save the graphical actual execution plan by right-clicking on the “Execution Plan” window and click on the “Save Execution Plan As…” button.
4. To view the XML estimated execution plan, you will need to execute the SET SHOWPLAN_XML ON statement, run the query, and then run the SET SHOWPLAN_XML OFF statement to set it off so that the subsequent queries will execute as intended. Once the XML link appears on the results window, you can click on it to open it, and then save it as XML or with the .sqlplan extension so that you can later open it in a graphical plan format. For example:

  1. Execute the statement to set the estimated execution plan on:
    SET SHOWPLAN_XML ON
    
  2. Run the query:
    SELECT p.ProductID
    , p.Name
    , pch.StandardCost
    , pch.StartDate
    , pch.EndDate
    , i.Quantity
    , i.Shelf
    FROM Production.Product AS p
    	JOIN Production.ProductCostHistory AS pch
    		ON p.ProductID = pch.ProductID
    	JOIN Production.ProductInventory AS i 
    		ON p.ProductID = i.ProductID	
    
  3. Execute the statement to set the estimated execution plan off:
    SET SHOWPLAN_XML OFF
    

5. To view the XML actual execution plan, you will need to wrap the query with the SET STATISTICS XML ON and the SET STATISTICS XML OFF statements. Then execute it. Once the XML link appears on the results window, you can click on it to open it, and then save it as XML or with the .sqlplan extension so that you can later open it in a graphical plan format. For example:

SET STATISTICS XML ON
SELECT p.ProductID
, p.Name
, pch.StandardCost
, pch.StartDate
, pch.EndDate
, i.Quantity
, i.Shelf
FROM Production.Product AS p
	JOIN Production.ProductCostHistory AS pch
		ON p.ProductID = pch.ProductID
	JOIN Production.ProductInventory AS i 
		ON p.ProductID = i.ProductID		
SET STATISTICS XML OFF		

REFERENCES:
Displaying Graphical Execution Plans (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

Graphical Execution Plan Icons
http://msdn.microsoft.com/en-us/library/ms175913(v=sql.105).aspx