When starting out with SQL Server, it can be challenging to optimize the performance of your queries without proper guidance. Fortunately, there are several features in SQL Server that can help you fine-tune your queries and improve their performance. One such feature is SET STATISTICS IO.

SET STATISTICS IO is a command that provides information about the input/output (IO) operations performed by a query. By enabling SET STATISTICS IO, you can understand the amount of IO performed by each object in your query. This information can serve as a benchmark for measuring the effectiveness of query optimization techniques.

Unlike measuring query execution time, which can be influenced by various factors such as resource contention, measuring IO operations provides a more consistent and reliable metric. By reducing the number of IO operations, you can significantly improve query performance.

To enable SET STATISTICS IO, you can use the following syntax:

SET STATISTICS IO ON;

Once enabled, SQL Server will return IO information as a message in your user interface. To disable SET STATISTICS IO, simply use the following command:

SET STATISTICS IO OFF;

Let’s see SET STATISTICS IO in action with an example query:

USE msdb;
GO
SET STATISTICS IO ON;
GO
SELECT
   b.machine_name,  
   b.server_name,  
   b.database_name as DBName,  
   b.backup_start_date,  
   b.backup_finish_date,  
   CASE     
      WHEN b.[type] = 'D' THEN 'Database'    
      WHEN b.[type] = 'I' THEN 'Differential database'    
      WHEN b.[type] = 'L' THEN 'Log'    
      WHEN b.[type] = 'F' THEN 'File or filegroup'    
      WHEN b.[type] = 'G' THEN 'Differential file'    
      WHEN b.[type] = 'P' THEN 'Partial'    
      WHEN b.[type] = 'Q' THEN 'Differential partial'    
      ELSE b.[type]  
   END Backup_Type,      
   b.expiration_date,  
   DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,  
   b.recovery_model,  
   b.backup_size
FROM   msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf  ON b.media_set_id=bf.media_set_id
ORDER BY   b.backup_start_date DESC;
GO

By running this query with SET STATISTICS IO enabled, you will receive information about the IO operations performed by the query. This information can help you identify areas for optimization, such as adding indexes or changing join operators.

For example, you can experiment with different join operators to see how they affect the IO operations. In the example below, we use a MERGE join operator:

SELECT
   b.machine_name,  
   b.server_name,  
   b.database_name as DBName,  
   b.backup_start_date,  
   b.backup_finish_date,  
   CASE     
      WHEN b.[type] = 'D' THEN 'Database'    
      WHEN b.[type] = 'I' THEN 'Differential database'    
      WHEN b.[type] = 'L' THEN 'Log'    
      WHEN b.[type] = 'F' THEN 'File or filegroup'    
      WHEN b.[type] = 'G' THEN 'Differential file'    
      WHEN b.[type] = 'P' THEN 'Partial'    
      WHEN b.[type] = 'Q' THEN 'Differential partial'    
      ELSE b.[type]  
   END Backup_Type,      
   b.expiration_date,  
   DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,  
   b.recovery_model,  
   b.backup_size
FROM   msdb.dbo.backupset AS b
INNER MERGE JOIN msdb.dbo.backupmediafamily AS bf  ON b.media_set_id=bf.media_set_id
ORDER BY   b.backup_start_date DESC;
GO

By comparing the IO operations of different join operators, you can identify potential areas for optimization. For example, using a HASH join operator may result in a different IO profile:

SELECT
   b.machine_name,  
   b.server_name,  
   b.database_name as DBName,  
   b.backup_start_date,  
   b.backup_finish_date,  
   CASE     
      WHEN b.[type] = 'D' THEN 'Database'    
      WHEN b.[type] = 'I' THEN 'Differential database'    
      WHEN b.[type] = 'L' THEN 'Log'    
      WHEN b.[type] = 'F' THEN 'File or filegroup'    
      WHEN b.[type] = 'G' THEN 'Differential file'    
      WHEN b.[type] = 'P' THEN 'Partial'    
      WHEN b.[type] = 'Q' THEN 'Differential partial'    
      ELSE b.[type]  
   END Backup_Type,      
   b.expiration_date,  
   DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,  
   b.recovery_model,  
   b.backup_size
FROM   msdb.dbo.backupset AS b
INNER HASH JOIN msdb.dbo.backupmediafamily AS bf  ON b.media_set_id=bf.media_set_id
ORDER BY   b.backup_start_date DESC;
GO

By experimenting with different join operators and analyzing the IO operations, you can optimize your queries for better performance.

While SET STATISTICS IO is a useful feature, it should be used in conjunction with other optimization techniques such as query plans, traces, and extended event sessions. Together, these tools provide a comprehensive understanding of how to optimize your queries and improve overall SQL Server performance.

Remember, optimizing query performance is an ongoing process, and it requires continuous monitoring and fine-tuning. By leveraging features like SET STATISTICS IO, you can gain valuable insights into the IO operations of your queries and make informed decisions to improve their performance.

Stay tuned for more SQL Server tips and tricks!