Total Pageviews

Friday, March 29, 2013

Covering Index – A “Adam and Eve” Style

We know that in SQL Server there is a limitation on index key which is 900 bytes. This means that the total length of an index must not exceed 900 bytes. Until SQL 2000, we used to create a covering index by concatenating a number of columns to support a query execution. Starting from SQL Server 2005, Microsoft has added a new feature which is known as the “INCLUDE” column index.

In this article we will be researching on covering indexes, its performance comparison, which is between “multiple-columns index” (also known as composite, or concatenated) and “single column index” with the “include” option.

Covering index:
In a simple way, a covering index is one which can satisfy all requested columns in a query without performing a further lookup into the base table (clustered or heap).  So, we can say a covering index is an index which will satisfy JOIN and WHERE clause as well as all the columns from the SELECT statement.

Example of covering index:
Say we have the following SELECT query from a table “tblLarge”,

SELECT  xID ,              -- Primary and Clustered key
        sName1 ,           -- Key column for search
        sName2 ,           -- non-key column
        sName3 ,           -- non-key column
        sIdentifier        -- key column for search
FROM tblLarge

Based on the above select criteria, we may have the following covering indexes:

(a)    Multiple-column index without include option:
sIdentifier +  sName1 + sName2 + sName3
(b)   Multiple-column index with include option:
sIdentifier +  sName1  INCLUDE ( sName2 , sName3)

Please keep in mind that the clustered key does not need be part of the index.

Index Advantages:
The biggest advantage of a covering index is that it completely offloads the locking overheads from a table to the index. This reduces

1.        I/O operation
2.       Row lookup
3.       CPU usage

The overall benefit is a dramatic improvement of query response time.

Some Terms:
Let’s learn what we mean by Key and non-key column. Usually, a column said to be a key column is used on a JOIN or on a WHERE clause. The columns which are not used in search or in join clause are known as non-key columns.

A fact:
It is recommended that an index key should be narrow as possible- this means that Query Optimizer of SQL Server will always try to utilize the smallest index. SQL Server Query optimize is very picky because it is a cost based optimizer.

While creating an execution plan, Query Optimizer evaluates mainly two things; Selectivity and data retrieval cost. Based on the estimated cost of an index, it may not be selective enough so an index may not be used or if it selects a sub-optimal index, then query performance may hinder rather than improvement. In that case Index Hints can be used to force SQL Server to use a particular index.

Our Testing Approach:
1.       We will use a database called “TestDB”.
2.       A table “tblLarge” with 2,000,000 records.
3.       A clustered index on the Primary key “xID”.
4.       We will create two indexes.
5.       Buffer cache will be cleared before query execution.
6.       We will also collect STATISTICS IO output.

Sample Database, Table and Rows creation:
1.       Create a database “TestDB”

CREATE DATABASE TestDB
GO
USE TestDB
GO
2.        Create a “tblLarge” with default fill factor (which is 0 or 100)

SET NOCOUNT ON
IF OBJECT_ID('tblLarge') IS NOT NULL
    DROP TABLE tblLarge
GO

CREATE TABLE [tblLarge](
       [xID] [int] IDENTITY(1,1) NOT NULL,
       [sName1] [varchar](100) NULL,
       [sName2] [varchar](200) NULL,
       [sName3] [varchar](300) NULL,
       [sIdentifier] [char](10) NULL,
       [dDOB] [datetime] NULL,
       [nWage] [numeric](20, 2) NULL,
       [sLicense] [varchar](25) NULL,
 CONSTRAINT [PK_tblLarge] PRIMARY KEY CLUSTERED ([xID] ASC) )

3.       Populate 2,000,000 rows.

SET NOCOUNT ON
INSERT  INTO tblLarge
        ( sName1 ,
          sName2 ,
          sName3 ,
          sIdentifier ,
          dDOB ,
          nWage ,
          sLicense
        )
VALUES  ( LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*50) ,     -- sName1
          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*60) ,     -- sName2
          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*70) ,     -- sName2
          LEFT(CAST(NEWID() AS VARCHAR(36)), 2) ,             -- sIdentifier      
          DATEADD(dd, -RAND() * 20000, GETDATE()) ,           -- dDOB
          ( RAND() * 1000 ) ,                                 -- nWage
          SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 6, 7)       -- sLicense
         )

GO 2000000

Sample SELECT query:
We will be using the following SELECT query to perform our test.

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON

SELECT  xID ,              -- Primary and Clustered key
        sName1 ,           -- Key column for search
        sName2 ,           -- non-key column
        sIdentifier        -- key column for search
FROM    tblLarge
WHERE   sIdentifier = 'AB'
        AND sName1 LIKE 'ABC%'

Any search string can be used to run the above query.

Covering Index with “INCLUDE” option:
In our first test, let’s create two covering indexes with the “INCLUDE” option as follows.

Ind#1
CREATE INDEX [IX_sIdentifier] ON [tblLarge] ([sIdentifier])
INCLUDE ([sName1], [sName2])

Once the index#1 is created, we run the above query, and it provides the following Execution Plan.
Let’s create a variation of the above index and move the “sName1” column to the index key as follows.

Ind#2
CREATE INDEX [IX_sIdentifier_sName1] ON [tblLarge] ([sIdentifier], [sName1])
INCLUDE ([sName2])

When we run the query, we get the following Execution Plan.


Covering Index without “INCLUDE” option:
In our second test, we will create a multiple-column index without the “INCLUDE” option as follows.

Ind#3
CREATE INDEX [IX_sIdent_sName1_sName2] ON [tblLarge] ([sIdentifier], [sName1], [sName2])

Now if we run our query, it will result in the following Execution Plan.
STATISTICS IO Comparison:
When  #X Index
Scan Count
Read-Ahead Reads
Physical Reads
Logical Reads
Ind#1
1
72
2
75
Ind#2
1
0
0
3
Ind#3
1
0
0
4


Index Properties of each Index:

 

 


Review of Query execution:

1.       When we first created the index “ind#1”, query utilized this index although the index is not completely covered. When the query was first executed, there were no statistics for the column “sName1”, so SQL Server created missing statistics.
2.       In case of “ind#2”, all the predicates are found in this index and as well “sName2” which is included with the “INCLUDE” option. So this index has totally covered the query.
3.       For the “ind#3”, we created this index at the end without deleting the previous two indexes. Though the second index is far better than the third index, SQL Server started using this multiple-column index although it is a little expensive.

As an explanation, when we create multiple-column indexes, a multi-column statistics is also created along with the index which provides better cardinality estimations to evaluate the query. However, if we examine the properties of all the indexes, then you will notice that the depth of “ind#3” is 4, which makes it more complex in nature by adding one more leaf level in the b-tree structure.

Summary:
There is a saying which goes like “Query writing is an Art, index is a Science”. As SQL Server uses indexes, it does not mean that it is efficient too. Reviewing the "Index Depth" is one of the critical factors for a performance boost while creating a covering index for a query.
While creating indexes, we should not forget that there is a cost and penalty to maintain indexes where DML operation happens very frequently, and which often reduces the benefit of having indexes. Also we should not have duplicate and overlapping indexes, unless there is a very specific reason.

Read More:
SQL Server Optimization

Increase fan-out to reduce index depth

Clustered and Nonclustered Indexes Described

Introduction to Indexes

Tuesday, March 19, 2013

SQL I/O Latency and Disk Issue – The missing link

Say for example the application has been written with performance in mind, all queries are optimal, no index issues, databases are fully optimized through a nightly job or manually, and the best I/O subsystem has been deployed to achieve excellent I/O throughput. 

Log, Data, Indexes, tempdb are placed in RAID10, and all the best practices around the SQL Server were put in place. Yet the disk response time is not satisfactory. SQL Server DMV (sys.dm_io_virtual_file_stats, sys.dm_io_pending_io_requests, sys.dm_os_wait_stats) continuously reports I/O stalls or issues during peak hour. Performance counters such as Avg. Disk sec/Read, Avg. Disk sec/Write and Avg. Disk sec/Transfer exceed the accepted threshold (usually read/write > 20 milliseconds and transfer > 15 milliseconds) alarmingly for a long duration of time. Wait Type reports numerous "PAGEIOLATCH_*" significantly.

This is one of the “pulling hair” type situations to figure out the root causes. This article is for DBAs and Sys Admins who are running SQL Server in physical or in a virtual environment and experiencing slow I/O response.

Detecting I/O Issues with SQL DMV:
To check for outstanding IOPs waiting longer than 10ms:
SELECT  *
FROM    sys.dm_io_pending_io_requests
WHERE   io_type = 'disk'
        AND io_pending_ms_ticks > 10

To check the time spent waiting on PAGEIOLATCH_*:
SELECT  SUM(wait_time_ms) total_wait_ms
FROM    sys.dm_os_wait_stats
WHERE   wait_type LIKE 'PAGEIOLATCH%'

What to check?
There are some areas that need to be checked to see whether there is a misconfiguration or not.
1.       NTFS Allocation Unit (formerly known as cluster size)
2.       Volume Alignment (also known as disk, partition or sector alignment)
3.       HBA Disk Queue Depth


HBA Disk Queue Depth:
HBA Disk Queue Depth is another area which is often/always left as default (ranging from 8 to 32). This setting also needs to be reviewed based on workload. Inappropriate settings can significantly impact I/O throughput and out-of-the box values can be insufficient for SQL Server.


Although a testing is required to determine optimal value of HBA Disk Queue Depth for a particular workload; however queues depth of 64 is generally accepted as a good starting point in the absence of any specific vendor recommendations.

NTFS File allocation unit size (cluster size):
Often, the file allocation unit size (cluster size) is not taken into consideration while formatting a SQL Data drive (physical and virtual). It is highly recommended that all SQL Server data drives should be 64KB. "The best practice for SQL Server is to choose 64KB, because this reduces the likelihood of I/O’s that span distinct NTFS allocations, which then might result in split I/O’s." Eventually this introduces significant disk latency.

In SQL Server, each page size is 8KB, and eight (8) physically contiguous pages (8KB X 8 = 64KB) create one Extent. Therefore if the NTFS allocation unit size is 64KB then the Extent creation by SQL Server Engine will be hassle free, meaning it does not require extra pointer calculations overhead -which provides an efficient read and write operation.Use the following command to check drive info.

fsutil fsinfo ntfsinfo [your drive]

How to check Allocation Unit Size?
We can use the following command to check the "allocation unit size" of all logical disks. Open the command window and then type,

cmd> wmic volume GET Caption, BlockSize

The above command will provide the following output:

Volume Alignment (Disk Alignment, Sector Alignment or Partition Alignment):
From my experience, I have seen a number of DBAs, sysadmins and storage admins that have no idea what it is all about and what the impact is on the performance. In SQL Server OLTP environment (the DML operation such as SELECT, UPDATE) proper disk alignment will provide up to a 30% -40% dramatic performance improvement. This is clearly documented and explained in various Microsoft articles.

There are a number of sites where this topic has been discussed very elaborately. I encourage you to read those before preparing a disk for SQL Server data files.

Up to Windows 2003, Sector Alignment was not done automatically while formatting and installing Windows OS, it was all manual task which was usually performed by the server admin. This has been changed starting from Windows 2008 Server and later versions. Windows Server 2008 uses 1024 KB (2048 sectors) as a starting offset for the disk which is larger than 4GB, which will usually work well for almost any array.

How to check Volume Alignment?
There are many different methods and as well as tools are available to check volume alignment. Following is the simplest method:

1.       Type Msinfo32.exe in the search window in start menu.
2.       In the dialog box look for Components > Storage > Disks.
3.       Look for the desired logical drive and find the "Partition Starting Offset".
4.       The number is divisible by 4096 bytes -
(a)    If dividing it by 4096 reveals a whole number then volume alignment is correct.
(b)   If dividing it by 4096 gives decimal then the volume alignment is not correct.

Alternatively following command can used to get the same information.
cmd> wmic partition get Index, Name, StartingOffset


Manually adjust alignment for basic disks: 
We can use diskpart.exe to format the drive with correct alignment while preparing the Disk for SQL Server data files. Please be careful, following steps erase the selected partition- so don’t try it in a live system.

1.       Open a command prompt, then type diskpart
2.       Type list disk to check which disks are available
3.       Select the desired partition to align:
select disk <DiskNumber>
4.       We will Align the partition to 1024KB:
create partition primary align=1024
5.       Assign a drive letter to it:
assign letter=<DriveLetter>
6.       The volume alignment has set correctly. Now we can format the disk as usual.

Following is a demonstration to show you how to use above diskpart commands to correct volume alignment.


Following is freeware tool (Disk Alignment Test v1.0.1) can also be used to check disk alignment.


Recommendations:
1.       When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.
2.       Review and align the sector to 1024KB (or 2048KB max) before formatting the disk
3.       Correcting the Allocation Unit Size and Volume Alignment is necessary for Physical and virtual disks for significant I/O performance.

Further Reading:
Disk Partition Alignment Best Practices for SQL Server

Performance Tuning Guidelines for Windows Server 2008

Recommendations for Aligning VMFS Partitions

Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000

Disk Alignment Test