Using Indexes Effectively in SQL Server

Adding indexes on a specific table doesn't guarantee you the queries will use these indexes effectively. These are some of the query design rules you should follow to improve the use of indexes:

Avoid nonsargable search conditions
A sargable predicate in a query is one in which an index can be used.

Sargable =, >, >=, <, <=
LIKE 'text%'
Nonsargable <>, !=
LIKE '%text'

Avoid arithmetic operators on the WHERE clause column
For example, assuming an index has been created on NoWeeks column, the use of the multiplication operator column in the first query prevents the optimizer from using (choosing) the index on the column:

-- the index will not be used
WHERE NoWeeks * 7 > @NoDays

-- the index will be used
WHERE NoWeeks > @NoDays / 7

Avoid functions on the WHERE clause column

-- the index will not be taken into account
WHERE SUBSTRING(CustomerName, 1, 1) = 'F' ;

-- the index will be taken into account
WHERE CustomerName LIKE 'F%' ;

How to avoid recompilation

- Don’t interleave DDL and DML statements.

- Avoid recompilation caused by statistics changes: use the KEEPFIXED PLAN option or disable the auto update statistics on the relevant table.

- Use table variables instead of temporary tables (as statistics are not created for table variables)

- Avoid changing SET options within the stored procedure.

- Use the OPTIMIZE FOR query hint that uses parameter values
supplied by you to compile the plan, regardless of the values of the parameter passed in by the calling application. It is recommended to use it in case you have a set of parameters that are "representative" from statistics point of view. This way you'll have a plan that works best most of the time.

@CustomerId INT
       dbo.Sales S
       INNER JOIN ...
WHERE S.CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1)) ;

- Using plan guides. A plan guide allows you to use query hint or other optimization techniques without having to modify the query or procedure text. For example when the query is part of a third-party application and you are not able to modify it by including OPTION (OPTIMIZE FOR).
Example of creating a plan guide:

EXEC sp_create_plan_guide
       @name = N'MyGuide',
       @stmt = N'SELECT ...
                                 dbo.Sales S
                                 INNER JOIN ...
                                 WHERE S.CustomerId = @CustomerId'
       @type = N'OBJECT',
       @module_or_batch = N'dbo.CustomerList',
       @params = NULL,
       @hints = N'OPTION (OPTIMIZE FOR (@CustomerId = 1))' ;

Causes of recompilations

- The schema of regular tables, temporary tables, or views referred to in the stored procedure statement have changed. Schema changes include changes to the metadata of the table or the indexes on the table.

- Bindings (such as defaults) to the columns of regular or temporary tables have changed.

- Statistics on the table indexes or columns have changed past a certain threshold.

- An object did not exist when the stored procedure was compiled, but it was created during execution. This is called deferred object resolution.

- SET options have changed. (SET NOCOUNT doesn’t cause stored procedure recompilation)

- The execution plan was aged and deallocated.

- An explicit call of sp_recompile on a table, view, trigger or stored procedure

- There was an explicit use of the RECOMPILE hint:
CREATE PROCEDURE <spName> WITH RECOMPILE => prevents the caching of the stored procedure plan
EXEC <spname> WITH RECOMPILE; => a new plan is generated temporarily, without being cached of affecting any other existing cached plan
OPTION (RECOMPILE)</sql> => causes statement recompilation

DMVs used to analyze the execution plan cache

You can obtain a lot of information about the execution plans in the procedure cache by accessing various dynamic management objects.

sys.dm_exec_cached_plans: obtain information about the execution plans in the plan cache.

sys.dm_exec_query_plan(plan_handle): retrieve the XML representation of the plan.

sys.dm_exec_sql_text(plan_handle): retrieve the original query text.

sys.dm_exec_query_stats: get aggregate performance metrics about the cached plan.

sys.dm_exec_requests: see execution plans for queries that are currently executing.

Index fragmentation resolutions in SQL Server

After you detected fragmentation of an index, you can "resolve" fragmentation in an index by rearranging the index rows and pages so that their physical and logical orders match.

You achieve this through the following techniques:
1. Dropping and re-creating the index
If the index being dropped is a clustered index, then all the nonclustered indexes on the table have to be rebuilt after the cluster is dropped.

2. Creating the index with the DROP_EXISTING clause

3. Executing the ALTER INDEX REBUILD statement on the index
To be performed in case the fragmentation is over 30%.

ALTER INDEX <IX_IndexName> ON <TableName>

or rebuild all indexes of a specified table:


4. Executing the ALTER INDEX REORGANIZE statement on the index
To be performed in case the index fragmentation is between 5% and 30%

ALTER INDEX <IX_IndexName> ON <TableName>

Characteristics of Four Defragmentation Techniques

Blocking High High Medium Low
Defragment index with constraints Highly complex Moderately Easy Easy
Defragment multiple indexes together No No Yes Yes
Degree of defragmentation High High High Moderate to low
Statistics are updated Yes Yes Yes No
Apply new fill factor Yes Yes Yes No

Find locks in SQL Server

dm_tran_locks contains info about all current locks, granted or pending.

select *
from sys.dm_tran_locks

Finding queries that are using table scans

The T-SQL script used to discover queries that are using table scans by searching within query plan for TableScan tag:

        DB_NAME(ST.dbid)AS database_name
        , ST.text as sql_Statement
        , CP.usecounts
        , QP.query_plan
        sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
        ST.dbid = DB_ID()
        AND CAST(QP.query_plan AS NVARCHAR(MAX)) LIKE '%<TableScan%'
ORDER BY CP.usecounts DESC

Determining when a query was last run

Running the T-SQL script given will identify the top 20 times a given query was run, as ordered by last_execution_time. You can identify the relevant query by supplying part of it as a filter via the WHERE clause.

For example, determine when a specific stored procedure was last time changed:

        DB_NAME(QT.dbid) AS DatabaseName
        , QT.text as sql_statement
        , QS.last_execution_time
        sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as QT
        QT.dbid = DB_ID()               -- limit search on current database
        AND QT.text LIKE '%CREATE PROCEDURE%GetEmployees%'
ORDER BY QS.last_execution_time DESC;

Finding the queries that use the most I/O

Running the SQL script given in the following listing will identify the top 20 queries that use the most I/O.

        DB_NAME(QT.dbid) as database_name
        , QS.total_logical_reads + QS.total_logical_writes as total_io
        , QS.execution_count   
        , SUBSTRING(QT.text, (qs.statement_start_offset/2)+1,
                ((      CASE qs.statement_end_offset
                                WHEN -1 THEN DATALENGTH(QT.text)
                                ELSE qs.statement_end_offset
                        END - QS.statement_start_offset)/2) + 1) as statement_text
        , QT.text as parent_statement_text     
        , O.type_desc
        , QP.query_plan
        sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) QT
        CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
        LEFT JOIN sys.objects O ON O.object_id = QT.objectid
WHERE QT.dbid = DB_ID()
ORDER BY total_io DESC

Factors affecting index performance in SQL Server

Indexes and query performance are strongly related. It’s essential to ensure that the factors that affect the efficiency of an index are optimal:

  • Fill Factor
  • Statistics
  • Fragmentation
  • I/O Sub-System
  • Compression