Counter of Festivals

Ashok Blog for SQL Lovers

Tuesday, 12 August 2014

Blocking and DeadLocks and Types of Locks in SQL Server

What is Blocking and Deadlock in SQL Server? Different Types of Lock in SQL Server

Sometimes it helps to use analogies away from computers.
Let's say you have a ball and two children. Only one child can have the ball at any one time. However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball.
The other child is blocked from that resource.
If we compare this to the TV, for example, several children can watch TV at any one point.
If we move over to the database world, we see that there are different ways to use resources (just like our two examples above). We can perform "reads" or we can perform "writes".
When we want to read the data, there's no reason that other's can't read the data as well--just like two people watching TV. However, if we want to write the data, then we need to make sure that no one else is looking at it. If they are reading it while we're writing it, they will get "dirty" reads. (Meaning, they'll see the data partially written out, which will be invalid.)
In order to insure that these dirty reads never occur, we have two primary types of locks, Read Locks and Exclusive Locks.
Read Lock
You can have several different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.
Once a connection has a read lock on a piece of data, all other connections must wait until the Read Lock is released before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.
Exclusive Lock
If a connection wants to update/insert/delete a piece of data, they have to take out an exclusive lock. This prevents any other connection from also taking out a lock on the data (making the lock exclusive to that connection).
When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while its being written.
"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.
Compare this to the case with a child holding the ball. The child holding the ball is blocking all other children from holding the ball.
I know you didn't ask this, but it's only one more step to get to deadlocks (and it's related very directly to blocking).
Deadlocks can happen when you have two connections that each have a lock, but they want each others resource. In this scenario, it's like two children that each has a ball, but wants the other's ball.
Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connections) can have access to both of the resources.
Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.
So, the concept of a deadlock is where you have two resources that are blocking each other.

Understanding Locking in SQL Server:


Microsoft® SQL Server™ 2000 has multigranular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, SQL Server locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency, but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but has a lower overhead because fewer locks are being maintained.
SQL Server can lock these resources (listed in order of increasing granularity).
RIDRow identifier. Used to lock a single row within a table.
KeyRow lock within an index. Used to protect key ranges in serializable transactions.
Page8 kilobyte –(KB) data page or index page.
ExtentContiguous group of eight data pages or index pages.
TableEntire table, including all data and indexes.

SQL Server locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.
SQL Server uses these resource lock modes.
Lock modeDescription
Shared (S)Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
Update (U)Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X)Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
IntentUsed to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
SchemaUsed when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU)Used when bulk-copying data into a table and the TABLOCK hint is specified.

Shared Locks
Shared (S) locks allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
Update Locks
Update (U) locks prevent a common form of deadlock. A typical update pattern consists of a transaction reading a record, acquiring a shared (S) lock on the resource (page or row), and then modifying the row, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.
To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.
Exclusive Locks
Exclusive (X) locks prevent access to a resource by concurrent transactions. No other transactions can read or modify data locked with an exclusive (X) lock.
Intent Locks
An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.
Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Lock modeDescription
Intent shared (IS)Indicates the intention of a transaction to read some (but not all) resources lower in the hierarchy by placing S locks on those individual resources.
Intent exclusive (IX)Indicates the intention of a transaction to modify some (but not all) resources lower in the hierarchy by placing X locks on those individual resources. IX is a superset of IS.
Shared with intent exclusive (SIX)Indicates the intention of the transaction to read all of the resources lower in the hierarchy and modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources. Concurrent IS locks at the top-level resource are allowed. For example, an SIX lock on a table places an SIX lock on the table (allowing concurrent IS locks), and IX locks on the pages being modified (and X locks on the modified rows). There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.

Schema Locks
Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.
Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.
Bulk Update Locks
Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is specified or the table lock on bulk load table option is set usingsp_tableoption. Bulk update (BU) locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

Wednesday, 16 July 2014

OUTPUT clause in INSERT/UPDATE/DELETE statements

How do I use an INSERT statement's OUTPUT clause to get the identity value?




update URLDETSAMP SET Name='test'
output  INTO URLDETSAMP(name)

SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table. More details on the restrictions of the OUTPUT clause and usage can be obtained from the SQL Server Books Online documentation.
Let us take a look at a common scenario now and how OUTPUT clause can be used to solve the problem. Use of identity column as primary key in a table is a fairly common practice. Additionally, if you have references to the identity column you need to know the value explicitly before inserting the related row. This scenario gets complicated if multiple identity values are generated as a result of inserting multiple rows. In this case, there is no easy way to determine the values that were inserted without using a trigger to populate a temporary table with the generated identity values from the inserted table for example. To demonstrate this scenario, we can first see a trigger based implementation that works in SQL Server 2000 and 2005:
use tempdb;
create table itest ( i int identity not null primary key, j int not null unique );
create trigger insert_itest on itest after insert
    insert into #new ( i, j )
    select i, j
      from inserted;
create table #new ( i int not null, j int not null );
insert into itest ( j )
select from sysobjects as o;
-- Newly inserted rows and identity values:
select * from #new;

-- #new can be used now to insert into a related table:
drop table #new, itest;

This code can be re-written in SQL Server 2005 using the OUTPUT clause like below:

create table itest ( i int identity not null primary key, j int not null unique )
create table #new ( i int not null, j int not null)
insert into itest (j)
output inserted.i, inserted.j into #new
select o.object_id from sys.objects as o
select * from #new
drop table #new, itest;
Now from this example, you can see the integration of OUTPUT clause with existing DML syntax.
Another common scenario is auditing of data in a table using triggers. In this case, the trigger uses information from the inserted and updated tables to add rows into the audit tables. The example below shows code that uses OUTPUT clause in UPDATE and DELETE statements to insert rows into an audit table.
create table t ( i int not null );
create table t_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
   set i  = i + 1
output deleted.i, inserted.i into t_audit
 where i = 1;
delete from t
output deleted.i, NULL into t_audit
 where i = 2;
select * from t;
select * from t_audit;
drop table t, t_audit;
Hope you have now got an understanding of the OUTPUT clause in SQL Server 2005. It is a powerful feature that enables you to eliminate use of triggers in some cases or send results to client as part of the data modification operation efficiently.

Thursday, 3 July 2014

Components of Data flow?

Components includes -
  1. Data source(s).
  2. Transformations.
  3. Destination(s).
Component 1 -  Data Flow Sources
Data Flow SourcesDescription
OLE DB SourceConnects to OLE DB data source such as SQL Server, Access, Oracle, or DB2.
Excel SourceReceives data from Excel spreadsheets.
Flat File SourceConnects to a delimited or fixed-width file.
Raw File SourceDo not use connection manager. It produces a specialized binary file format for data that is in transit.
XML SourceDo not use connection manager. Retrieves data from an XML document.
ADO.NET SourceThis source is just like the OLE DB Source but only for ADO.NET based sources.
CDC SourceReads data out of a table that has change data capture (CDC) enabled. Used to
retrieve only rows that have changed over duration of time.
ODBC SourceReads data out of table by using an ODBC provider instead of OLE DB.

Transformation CategoriesTransformations
Row TransformationsCharacter Map
Copy Column
Data Column
Derived Column
OLE DB Command
 Rowset Transformations   Aggregate
 Percentage sampling/Row sampling
 Split and Join Transformations     Conditional split
 Look up
 Merge join
 union All
 Business intelligence transformations     Data Mining Query
 Fuzzy Look Up
 Fuzzy Grouping
 Term Extraction
 Term Look up
 Script Transformations Script
 Other Transformations     Audit
Cache Transform
 Export Column
 Import Column
 Row Count
Component 3 Data Flow Destinations
Data Flow DestinationsDescription 
 ADO.NET DestinationExposes data to other external processes such as a .NET application.
 Data Reader DestinationAllows the ADO.NET Data Reader interface to consume data, similar to the ADO.NET Destination.
 OLE DB DestinationOutputs data to an OLE DB data connection like SQL Server, Oracle or Access.
 Excel DestinationOutputs data from the Data Flow to an Excel spreadsheet.
 Flat file DestinationEnables you to write data to a comma-delimited or fixed-width file.
 Raw file DestinationOutputs data in a binary format that can be used later as a Raw File Source. It’s usually used as an intermediate persistence mechanism.
 ODBC DestinationOutputs data to an OLE DB data connection like SQL Server, Oracle or Access.
 Record set DestinationWrites the records to an ADO record set. Once written, to an object variable, it can be looped over a variety of ways in SSIS like a Script Task or a Foreach Loop Container.
 SQL Server DestinationThe destination that you use to write data to SQL Server. This destination has many limitations, such as the ability to only write to the SQL Server where the SSIS package is executing. For example – If you’re running a package to copy data from Server 1 to Server 2, then the package must run on Server 2. This destination is largely for backwards compatibility and should not be used.

Monday, 30 June 2014

Performance Issues Tuning in SQL Server

Performance Issues

Performance Issues    


There are several factors that can degrade SQL Server performance and in this section we will investigate some of the common areas that can effect performance.  We will look at some of the tools that you can use to identify issues as well as review some possible remedies to fix these performance issues.
We will cover the following topics:
  • Blocking
  • Deadlocks
  • I/O
  • CPU
  • Memory
  • Role of statistics
  • Query Tuning Bookmark Lookups
  • Query Tuning Index Scans
Troubleshooting Blocking
(Performance Issues)


In order for SQL Server to maintain data integrity for both reads and writes it uses locks, so that only one process has control of the data at any one time.  There are serveral types of locks that can be used such as Shared, Update, Exclusive, Intent, etc...  and each of these has a different behavior and effect on your data.
When locks are held for a long period of time they cause blocking, which means one process has to wait for the other process to finish with the data and release the lock before the second process can continue.  This is similar to deadlocking where two processes are waiting on the same resource, but unlike deadlocking, blocking is resolved as soon as the first process releases the resource.


As mentioned above, blocking is a result of two processes wanting to access the same data and the second process needs to wait for the first process to release the lock.  This is how SQL Server works all of the time, but usually you do not see blocking because the time that locks are held is usually very small.
It probably makes sense that locks are held when updating data, but locks are also used when reading data.  When data is updated an Update lock is used and when data is read a Shared lock is used.  An Update lock will create an exclusive lock on the data for this process and a Shared lock allows other processes that use a Shared lock to access the data as well and when two processes are trying to access the same data this is where the locking and blocking occurs.
Here are various ways you can identify blocking for your SQL Server instance.


In a query window run this command:
This is the output that is returned.  Here we can see the BlkBy column that shows SPID 60 is blocked by SPID 59.
sp_who2 blocking information in sql server

Activity Monitor

In SSMS, right click on the SQL Server instance name and select Activity Monitor.  In the Processes section you will see information similar to below.  Here we can see similar information as sp_who2, but we can also see the Wait Time, Wait Type and also the resource that SPID 60 is waiting for.
sql server acitvity monitor blocking

Report - All Blocking Transactions

Another option is to use the built in reports in SSMS.  Right click on the SQL Server instance name and select Reports > Standard Reports > Activity - All Block Transactions.
sql server Activity All Block Transactions report

Querying Dynamic Management Views

You can also use the DMVs to get information about blocking.
SELECT session_id, command, blocking_session_id, wait_type, wait_time, wait_resource, t.TEXT
FROM sys.dm_exec_requests 
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50 
AND blocking_session_id > 0
SELECT session_id, '', '', '', '', '', t.TEXT
FROM sys.dm_exec_connections 
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id 
                    FROM sys.dm_exec_requests 
                    WHERE blocking_session_id > 0)
Here is the output and we can see the blocking information along with the TSQL commands that were issued.
sql server blockng using sys.dm_exec_connections

Tracing a SQL Server Deadlock   
(Performance Issues)


A common issue with SQL Server is deadlocks.  A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward.  When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process or processes can move forward.
By default when this occurs, your application may see or handle the error, but there is nothing that is captured in the SQL Server Error Log or the Windows Event Log to let you know this occurred.  The error message that SQL Server sends back to the client is similar to the following:
Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID xx) was deadlocked on {xxx} resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction. 
In this tutorial we cover what steps you can take to capture deadlock information and some steps you can take to resolve the problem.


Deadlock information can be captured in the SQL Server Error Log or by using Profiler / Server Side Trace.

Trace Flags

If you want to capture this information in the SQL Server Error Log you need to enable one or both of these trace flags. 
  • 1204 - this provides information about the nodes involved in the deadlock
  • 1222 - returns deadlock information in an XML format
You can turn on each of these separately or turn them on together.  
To turn these on you can issue the following commands in a query window or you can add these as startup parameters.  If these are turned on from a query window, the next time SQL Server starts these trace flags will not be active, so if you always want to capture this data the startup parameters is the best option.
DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)
Here is sample output for each of the trace flags.

Trace Flag 1222 Output

trace flag 1222 output

Trace Flag 1204 Output

Trace Flag 1204 Output

Profiler / Server Side Trace

Profiler works without the trace flags being turned on and there are three events that can be captured for deadlocks.  Each of these events is in the Locks event class.
  • Deadlock graph - Occurs simultaneously with the Lock:Deadlock event class. The Deadlock Graph event class provides an XML description of the deadlock.
  • Lock: Deadlock - Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources that the other transaction owns.
  • Lock: Deadlock Chain - Is produced for each of the events leading up to the deadlock.

Event Output

In the below image, I have only captured the three events mentioned above.
sql server deadlock events

Deadlock Graph Output

Below is the deadlock graph which is the output for the Deadlock graph event.  We can see on the left side that server process id 62 was selected as the deadlock victim.  Also, if you hover over the oval with the X through it we can see the transaction that was running.
sql server deadlock graph

Finding Objects Involved in Deadlock

In all three outputs, I have highlighted the object IDs for the objects that are in contention.  You can use the following query to find the object, substituting the object ID for the partition_id below.
FROM sys.partitions
WHERE partition_id = 289180401860608;

Saving Deadlock Graph Data in XML File

Since the deadlock graph data is stored in an XML format, you can save the XML events separately.  When configuring the Trace Properties click on the Events Extraction Settings and enable this option as shown below.
store deadlock data in xml files

Index Scans and Table Scans 


There are several things that you can do to improve performance by throwing more hardware at the problem, but usually the place you get the most benefit from is when you tune your queries.  One common problem that exists is the lack of indexes or incorrect indexes and therefore SQL Server has to process more data to find the records that meet the queries criteria.  These issues are known as Index Scans and Table Scans.
In this section will look at how to find these issues and how to resolve them.


An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records.  A scan is the opposite of a seek, where a seek uses the index to pinpoint the records that are needed to satisfy the query.  The reason you would want to find and fix your scans is because they generally require more I/O and also take longer to process.  This is something you will notice with an application that grows over time.  When it is first released performance is great, but over time as more data is added the index scans take longer and longer to complete.
To find these issues you can start by running Profiler or setting up a server side trace and look for statements that have high read values.  Once you have identified the statements then you can look at the query plan to see if there are scans occurring.
Here is a simple query that we can run. First use Ctrl+M to turn on the actual execution plan and then execute the query.
SELECT * FROM Person.Contact
Here we can see that this query is doing a Clustered Index Scan.  Since this table has a clustered index and there is not a WHERE clause SQL Server scans the entire clustered index to return all rows.  So in this example there is nothing that can be done to improve this query.
query plan showing clustered index scan
In this next example I created a new copy of the Person.Contact table without a clustered index and then ran the query.
SELECT * FROM Person.Contact2
Here we can see that this query is doing a Table Scan, so when a table has a Clustered Index it will do a Clustered Index Scan and when the table does not have a clustered index it will do a Table Scan.  Since this table does not have a clustered index and there is not a WHERE clause SQL Server scans the entire table to return all rows.  So again in this example there is nothing that can be done to improve this query.
query plan showing table scan
In this next example we include a WHERE clause for the query.
SELECT * FROM Person.Contact WHERE LastName = 'Russell'
Here we can see that we still get the Clustered Index Scan, but this time SQL Server is letting us know there is a missing index.  If you right click on the query plan and select Missing Index Details you will get a new window with a script to create the missing index.
query plan showing clustered index scan with recommended index
Let's do the same thing for our Person.Contact2 table.
SELECT * FROM Person.Contact2 WHERE LastName = 'Russell'
We can see that we still have the Table Scan, but SQL Server doesn't offer any suggestions on how to fix this.
query plan showing table scan without recommended index
Another thing you could do is use the Database Engine Tuning Advisor to see if it gives you any suggestions. If I select the query in SSMS, right click and select Analyze Query in Database Engine Tuning Advisor the tools starts up and I can select the options and start the analysis.
Below is the suggestion this tool provides and we can see that recommends creating a new index, so you can see that using both tools can be beneficial.
database engine tuning advisor index recommendation

Create New Index

So let's create the recommended index on Person.Contact and run the query again.
USE [AdventureWorks]
ON [Person].[Contact] ([LastName])
SELECT * FROM Person.Contact WHERE LastName = 'Russell'
Here we can see the query plan has changed and instead of a Clustered Index Scan we now have an Index Seek which is much better.  We can also see that there is now a Key Lookup operation which we will talk about in the next section.
new query plan showing index seek


By finding and fixing your Index Scans and Table Scans you can drastically improve performance especially for larger tables.  So take the time to identify where your scans may be occurring and create the necessary indexes to solve the problem.  One thing that you should be aware of is that too many indexes also causes issues, so make sure you keep a balance on how many indexes you create for a particular table.

Eliminating bookmark (key/rid) lookups


When we were looking at the index scan and table scan section we were able to eliminate the scan which was replaced with an index seek, but this also introduced a Key Lookup which is something else you may want to eliminate to improve performance.
A key lookup occurs when data is found in a non-clustered index, but additional data is needed from the clustered index to satisfy the query and therefore a lookup occurs.  If the table does not have a clustered index then a RID Lookupoccurs instead.
In this section we will look at how to find Key/RID Lookups and ways to eliminate them.


The reason you would want to eliminate Key/RID Lookups is because they require an additional operation to find the data and may also require additional I/O.  I/O is one of the biggest performance hits on a server and any way you can eliminate or reduce I/O is a performance gain.
So let's take a look at an example query and the query plan. Before we do this we want to first add the nonclustered index on LastName.
USE [AdventureWorks]
ON [Person].[Contact] ([LastName])
Now we can use Ctrl+M to turn on the actual execution plan and run the select.
SELECT * FROM Person.Contact WHERE LastName = 'Russell'
If we look at the execution plan we can see that we have an Index Seek using the new index, but we also have a Key Lookup on the clustered index.  The reason for this is that the nonclustered index only contains the LastName column, but since we are doing a SELECT * the query has to get the other columns from the clustered index and therefore we have a Key Lookup.  The other operator we have is the Nested Loops this joins the results from the Index Seek and the Key Lookup.
new query plan showing index seek
So if we change the query as follows and run this again you can see that the Key Lookup disappears, because the index includes all of the columns.
SELECT LastName FROM Person.Contact WHERE LastName = 'Russell'
Here we can see that we no longer have a Key Lookup and we also no longer have the Nested Loops operator.
query with index seek
If we run both of these queries at the same time in one batch we can see the improvement by removing these two operators.
SELECT * FROM Person.Contact WHERE LastName = 'Russell'
SELECT LastName FROM Person.Contact WHERE LastName = 'Russell'
Below we can see that the first statement takes 99% of the batch and the second statement takes 1%, so this is a big improvement.
query plan with index seek and key lookup
This should make sense that since the index includes LastName and that is the only column that is being used for both the SELECTed columns and the WHERE clause the index can handle the entire query.  Another thing to be aware of is that if the table has a clustered index we can include the clustered index column or columns as well without doing a Key Lookup.
The Person.Contact table has a clustered index on ContactID, so if we include this column in the query we can still do just an Index Seek.
SELECT ContactID, LastName FROM Person.Contact WHERE LastName = 'Russell'
Here we can see that we only need to do an Index Seek to include both of these columns.
query plan with index seek
So that's great if that is all you need, but what if you need to include other columns such as FirstName. If we change the query as follows then the Key Lookup comes back again.
SELECT FirstName, LastName FROM Person.Contact WHERE LastName = 'Russell'
Luckily there are a few options to handle this.

Creating a Covering Index

A covering index basically does what it implies, it covers the query by including all of the columns that are needed.  So if our need is to always include FirstName and LastName we can modify our index as follows to include both LastName and FirstName.
DROP INDEX [IX_LastName] ON [Person].[Contact]
ON [Person].[Contact] ([LastName], [FirstName])
And if we look at the execution plan we can see that we eliminated the Key Lookup once again.
query plna for covering index

Creating an Index with Included Columns

Another option is to use the included columns feature for an index.  This allows you to include additional columns so they are stored with the index, but are not part of the index tree.  So this allows you to take advantage of the features of a covering index and reduces storage needs within the index tree. Another benefit is that you can include additional data types that can not be part of a covering index.
The syntax for the the index with included columns is as follows:
DROP INDEX [IX_LastName] ON [Person].[Contact]
ON [Person].[Contact] ([LastName]) 
INCLUDE ([FirstName])
Here we can see the exuection plan is the same for both options.
query plan for index with included columns

Discovering Unused Indexes


To ensure that data access can be as fast as possible, SQL Server like other relational database systems utilizes indexing to find data quickly.  SQL Server has different types of indexes that can be created such as clustered indexes, non-clustered indexes, XML indexes and Full Text indexes.
The benefit of having more indexes is that SQL Server can access the data quickly if an appropriate index exists.  The downside to having too many indexes is that SQL Server has to maintain all of these indexes which can slow things down and indexes also require additional storage.  So as you can see indexing can both help and hurt performance.
In this section we will focus on how to identify indexes that exist, but are not being used and therefore can be dropped to improve performance and decrease storage requirements.


When SQL Server 2005 was introduced it added Dynamic Management Views (DMVs) that allow you to get additional insight as to what is going on within SQL Server.  One of these areas is the ability to see how indexes are being used.  There are two DMVs that we will discuss.   Note that these views store cumulative data, so when SQL Server is restated the counters go back to zero, so be aware of this when monitoring your index usage.

DMV - sys.dm_db_index_operational_stats

This DMV allows you to see insert, update and delete information for various aspects for an index.  Basically this shows how much effort was used in maintaining the index based on data changes.
If you query the table and return all columns, the output may be confusing.  So the query below focuses on a few key columns.  To learn more about the output for all columns you can check out Books Online.
       I.[NAME] AS [INDEX NAME], 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
Below we can see the number of Inserts, Updates and Deletes that occurred for each index, so this shows how much work SQL Server had to do to maintain the index.

DMV - sys.dm_db_index_usage_stats

This DMV shows you how many times the index was used for user queries.  Again there are several other columns that are returned if you query all columns and you can refer to Books Online for more information.
       I.[NAME] AS [INDEX NAME], 
       AND S.database_id = DB_ID()
Here we can see seeks, scans, lookups and updates.
  • The seeks refer to how many times an index seek occurred for that index.  A seek is the fastest way to access the data, so this is good.
  • The scans refers to how many times an index scan occurred for that index.  A scan is when multiple rows of data had to be searched to find the data.  Scans are something you want to try to avoid.
  • The lookups refer to how many times the query required data to be pulled from the clustered index or the heap(does not have a clustered index).  Lookups are also something you want to try to avoid.
  • The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.

Identifying Unused Indexes

So based on the output above you should focus on the output from the second query.  If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index.  Remember that the data from these DMVs is reset when SQL Server is restarted, so make sure you have collected data for a long enough period of time to determine which indexes may be good candidates to be dropped.
Investigating I/O bottlenecks


SQL Server is usually a high I/O activity process and in most cases the database is larger than the amount of memory installed on a computer and therefore SQL Server has to pull data from disk to satisfy queries.  In addition, since the data in databases is constantly changing these changes need to be written to disk.  Another process that can consume a lot of I/O is the TempDB database.  The TempDB database is a temporary working area for SQL Server to do such things as sorting and grouping.  The TempDB database also resides on disk and therefore depending on how many temporary objects are created this database could be busier than your user databases.
Since I/O is such an important part of SQL Server performance you need to make sure your disk subsystem is not the bottleneck.  In the old days this was much easier to do, since most servers had local attached storage.  These days most SQL Servers use SAN or NAS storage or to further complicate things more and more SQL Servers are running in a virtualized environment.


There are several different methods that can be used to track I/O performance, but as mentioned above with SAN / NAS storage and virtualized SQL Server environments, this is getting harder and harder to track as well as the rules have changed as far as what should be tracked to determine if there is an I/O bottleneck.  The advantage is that there are several tools available at both the storage level and the virtual level to aid in performance, but we will not cover these here.
There are basically two options that you have to monitor I/O bottlenecks, SQL Server DMVs and Performance Monitor counters.  There are other tools as well, but these are two options that will exist in every SQL Server environment.

DMV - sys.dm_io_virtual_file_stats

This DMV will give you cumulative file stats for each database and each database file including both the data and log files.  Based on this data you can determine which file is the busiest from a read and/or write perspective.
The output also includes I/O stall information for reads, writes and total.  The I/O stall is the total time, in milliseconds, that users waited for I/O to be completed on the file.  By looking at the I/O stall information you can see how much time was waiting for I/O to complete and therefore the users were waiting.
The data that is returned from this DMV is cumulative data, which means that each time you restart SQL Server the counters are reset.  Since the data is cumulative you can run this once and then run the query again in the future and compare the deltas for the two time periods. If the I/O stalls are high compared to the length of the that time period then you may have an I/O bottleneck.
cast(DB_Name(a.database_id) as varchar) as Database_name,
b.physical_name, * 
sys.dm_io_virtual_file_stats(null, null) a 
INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id
ORDER BY Database_Name
Here is partial output from the above command.
DMV sys.dm_io_virtual_file_stats output

Performance Monitor

Performance Monitor is a Windows tool that let's you capture statistics about SQL Server, memory usage, I/O usage, etc...  This tool can be run interactively using the GUI or you can set it up to collected information behind the scenes which can be reviewed at a later time.  This tool is found in the Control Panel under Administrative tools.
There are several counters related to I/O and they are located under Physical Disk and Logical Disk.  The Physical Disk performance object consists of counters that monitor hard or fixed disk drive on a computer.  The Logical Disk performance object consists of counters that monitor logical partitions of a hard or fixed disk drives. For the most part, they both contain the same counters.  In most cases you will probably use the Physical Disk counters.  Here is a partial list of the available counters.
Performance Monitor Physical Disk countersPerformance Monitor Logical Disk counters
Now that storage could be either local, SAN, NAS, etc... these two counters are helpful to see if there is a bottleneck:
  • Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.
  • Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.
The recommendation is that the values for both of these counters be less than 20ms.  When you capture this data the values will be displayed as 0.000, so a value of 0.050 equals 50ms.

Resource Monitor

Another tool that you can use is the Resource Monitor.  This can be launched from Task Manager or from the Control Panel.
Below you can see the Disk tab that shows current processes using disk, the active disk files and storage at the logical and physical level.  The Response Time (ms) is helpful to see how long it is taking to service the I/O request.
windows resource monitor

Additional Information

I/O issues may not always be a problem with your disk subsystem.  Just because you see a slow down or I/O waits occurring there may be other issues that you need to consider such as missing indexes, poorly written queries, fragmentation or out of date statistics.  We will cover these topics as well in this tutorial.