How to view query execution plan of a running SPID

Below query can give us execution plan of a running SPID. Clicking on XML will display the graphical plan.

select plan_handle,query_plan,TEXT from sys.dm_exec_requests
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
where session_id=spid_number


How to know the available free space in every data file of a SQL 2005/2008 database

Below query will display the amount of free space available in every data file of a database.

SELECT name AS ‘File Name’, physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’,

(size/128.0)-(CAST(FILEPROPERTY(name,‘SpaceUsed’) AS int)/128.0) as ‘free space’ ¬†FROM sys.database_files

select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(,’SpaceUsed’)/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(,’SpaceUsed’))/128.000,2)) , a.NAME, a.FILENAME from dbo.sysfiles a

Calculating the Percentage of I/O for Each Database using DMV

Below query will display the percentage of I/O for each database on a running SQL 2005/2008 system.

WITH Agg_IO_Stats
DB_NAME(database_id) AS database_name,
CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576.
AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats
GROUP BY database_id
ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS row_num,
CAST(io_in_mb / SUM(io_in_mb) OVER() * 100
AS DECIMAL(5, 2)) AS pct
FROM Agg_IO_Stats
ORDER BY row_num;

Read-only replicas using database mirror, snapshots and synonyms

Database mirroring can be used as a load balancing mechanism to divert all reader applications to a different instance provided that applications are modified to take advantage of the feature.

Database mirror
Database should be in FULL recovery model for mirroring to be configured. Once mirror is configured, transactions happening at the source will be transmitted to destination using synchronous/asynchronus mode.

Database snapshots
Snapshots are read-only copies of a database and can be created on a schedule (once every 15 minutes/30 minutes etc). Snapshots need to be created at the mirror destination SQL instance.

Once snapshots are created, they can be used like any other database using snapshot name but applications will not be able to automatically benefit from this because original database name will be different from snapshot name.

We can create a database (Ex: master_readonly) for exclusively holding synonyms for various objects of all the databases pointing to the respective database snapshots on mirror destination SQL instance. These synonyms can be recreated as and when new snapshot is generated. This will not affect the reader queries running on  mirror destination SQL instance.

A new database schema needs to be created under  master_readonly database for each mirrored database and synonyms for various database objects could be created inside that database schema. Ex: If we have 10 databases that are being mirrored, 10 database schemas with various database names should be created under master_readonly database at destination SQL instance.


Summing up together, a SQL agent job could be scheduled on mirror destination SQL instance to periodically create new database snapshots, recreate synonyms to point to latest snapshots and cleanup older snapshots.

However, applications and underlying SQL code should be modified to take advantage of this setup. Application specific database views, stored procedures and user defined functions should be modified as well to follow the new pattern of master_readonly.database.object name. Following this model will seggregate reader applications from writer applications and provides for load-balancing, reduced contention, scalability.

Query performance tuning guidelines for MS SQL Server

  • Compare the query execution time, cost of the execution plan, number of logical reads to determine the effect of creating new index, modifying the SQL code etc.
  • Make sure that proper clustred index is created. Click here for guidelines on cluster index creation.
  • Creating indexes will not always improve performance. INSERT,UPDATE,DELETE may get affected if there are too many indexes. Even some of the SELECT queries will run slow if there are many unused indexes.
  • Determine the columns for index creation by looking at FROM, WHERE Clause of the queries.
  • Higher Selectivity is criteria for creating indexes on a column. The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1.
  • Order of tables in query doesn’t really matter incase of INNER JOINs
  • Order of tables in query does matter for OUTER JOINs because the semantics of the query changes depending on what order you specify the tables.
  • Order of columns in SELECT clause doesn’t really matter
  • For queries with single column filter in WHERE clause, only an index that includes the filtered column as the first key in it will be used.
  • Order of conditions in WHERE clause doesn’t really matter incase of multi-column filter. Only selectivity of the index matters.   
  • An SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Ex: CREATE INDEX idx_composite ON mytab (x, y, z): leading portions of the index: X, XY, and XYZ.
  • Avoid using user-defined/default functions in queries where they invalidate the use of indexes
  • Clustered index scan or table scan should be avoided by filter using WHERE clause and non-clustered index on filter columns.
  • Bookmark/Key lookup should be avoided by creating a INCLUDE index or covering index that includes all columns of the query in the index.
  • Consider creating a composite index on columns that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either column individually.
  • Consider indexing columns that are used frequently to join tables in SQL statements.
  • Plan guides can be used to guide query optimizer to use a specific query execution plan every time when a query runs.
  • Parameterizing the queries using stored procedures is good because it will ensure that similar queries with different parameters are not compiled every time and single query execution plan will be used. 
  • Avoid cursors because they consume more memory and locks. Check if cursors could be replaced by WHILE loop or user defined functions.
  • Avoid using views particularly with complex joins and aggregate operations like GROUP BY.
  • Avoid using local variables in SELECT statements. Use sp_executesql or stored procedures with parameters instead of local variables.

Query Tuning Recommendations from Microsoft

When to Break Down Complex Queries

How to determine appropriate columns for clustered index on a table

Below things should be considered while creating clustered index on a table.

  • Unique: Primary key columns that can be used to uniquely identify each row are  ideal for clustered index key
  • Identity columns of normalized tables based on business needs (Ex: empid for employee table, orderid for orders table) can be used as keys of clustered index
  • Narrow: Clustered index key should be narrow as much as possible because it is included in all nonclustered indexes and is used for looking up data rows. A wide clustered index will increase the size of nonclustered indexes as well
  • Static: Frequently updated columns are not suitable as clustered index key because whenever data changes, table has to reorganized to follow the order, nonclustered indexes also should be updated.
  • Clustered index on date, identity columns will improve the performance of range queries.