Total Pageviews

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

Friday, March 8, 2013

OR vs. UNION ALL – have you thought about it?

Often we write a query which requires retrieving data based on two or more logical blocks of condition. Such as after the WHERE clause a search criterion may be as follows:

SELECT a, b, c FROM sometable
WHERE
((a=2 and b=4) AND c=6)
OR
((a=10 and b=20) AND c=30)

Logically, there is no issue with the construction of this query. It does the job just fine. However, is there any other technique or better way to write this query which is more efficient and cost effective? In respect to SQL Server Query Optimizer, yes we have a more efficient method if we use "UNION ALL". Basically, the “OR” is a squeezed and simplified structure of “UNION” .

A union may look like "Last Supper" - a paining by Leonardo Da Vinci.

In this test we will examine the same query in two different ways and compare the efficiency and cost.

Steps to follow:
1.       Create a database “TestDB”.
2.       Create a table “tblLarge” and populate it with 1 million records.
3.       Create clustered and a non-clustered covering index.
4.       Write two queries; one with using “OR” and another with “UNION ALL”.
5.       We will clear buffer and plan cache before every execution.

Let’s see it:

1.       Create the “TestDB”, create a Table “TblLarge” and populate it with One Million records
CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE tblLarge
    (
      xID INT IDENTITY(1, 1) ,
      sName1 VARCHAR(100) ,
      sName2 VARCHAR(1000) ,
      sName3 VARCHAR(400) ,
      sIdentifier CHAR(10) ,
      dDOB DATETIME ,
      nWage NUMERIC(20, 2) ,
      sLicense VARCHAR(25)
     )
GO

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 1000000

2.       Create clustered and a non-clustered covering index:
ALTER TABLE dbo.tblLarge ADD CONSTRAINT PK_tblLarge PRIMARY KEY CLUSTERED (xID)
GO

CREATE NONCLUSTERED INDEX IX_tblLarge_sIdentifier ON dbo.tblLarge
       (sIdentifier)
INCLUDE (sName1, sName2, sName3) 
       WITH( STATISTICS_NORECOMPUTE = OFF)
GO

3.       Update statistics with FULL SCAN
UPDATE STATISTICS tblLarge WITH FULLSCAN

4.       The query with the OR logic. Use any search argument as your choice.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SELECT  xID ,
        sName1 ,
        sName2 ,
        sName3
FROM    tblLarge
WHERE   ( ( xID BETWEEN 200000 AND 200090 )
          AND sIdentifier = 'BC'
        )
        OR ( ( xID BETWEEN 500000 AND 500050 )
             AND sIdentifier = 'CD'
           )
GO

5.       The query with the UNION ALL. Use any search argument as your choice.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SELECT  xID ,
        sName1 ,
        sName2 ,
        sName3
FROM    tblLarge
WHERE   ( ( xID BETWEEN 200000 AND 200090 )
          AND sIdentifier = 'BC'
        )
UNION ALL
SELECT  xID ,
        sName1 ,
        sName2 ,
        sName3
FROM    tblLarge
WHERE   ( ( xID BETWEEN 500000 AND 500050 )
          AND sIdentifier = 'CD'
        )

Comparing – “OR” vs. “UNION ALL”
Following are the differences we can find easily.

1.       The “OR” version query has two operators (steam aggregation and merge join) whereas “UNION ALL” version has concatenation operator only.
2.       Cardinality estimate is inaccurate for “OR” version query.
3.       The sub-tree cost for “UNION ALL” query is way less than the “OR” version.
4.       Overall, the operational cost for “UNION ALL” query is 30% less than the other one.

Query Result:


Execution Plan:
 

"OR" Query Cost:


“UNION ALL” Query Cost:









Conclusion:
A query can be written in many different ways and it is a good idea to try different methods. There is a saying, “Query writing is an Art” and you are the Artist.

Wednesday, March 6, 2013

Table-Valued Function (TVF) – hidden gotchas and possible alternatives

Both the Table-Valued Function (TVF) and Inline Table-Valued Function (ITVF) are user defined functions that return table data type.

TVF and ITVF can be used to achieve the functionality of parameterized views.  Which means a view does not support parameters in the search conditions specified in the WHERE clause. But user-defined functions do support parameters in the search conditions specified in the WHERE clause.

Inline Table-Valued Function:
To define TVF and ITVF you must follow their own set of rules. Below is a brief description about how an ITVF can be defined.

1.       The RETURNS clause contains only the keyword “table”. There is no need to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
2.       There is no function body delimited by BEGIN and END.
3.       The RETURN clause contains a single SELECT statement in parentheses.
4.       It accepts only constants or @local_variable arguments.

Disadvantages:
It is always preferable to use ITVF over TVF whenever feasible to get the best possible performance which we will see later. Following are some critical disadvantages we observed while using TVF.

1.       Cardinality estimate for TVF will be always wrong, as it produces a dynamic table at execution time thus no statistics.
2.       TVF requires intermediate results which will go into tempdb.
3.       Confusing execution plan and cost estimation.
4.       Consumes more CPU because lack of statistics.
5.       Causes more performance issues when used with LEFT JOIN.

Performance Comparison:
Here, we will be reviewing performance differences among TVF, ITVF and SELECT statement.

1.       Create a new database.
2.       Create a table and populate it with 100,000 records.
3.       Create clustered and covering indexes.
4.       Create a TVF which is “fn_myTVF” and create an ITVF which is “fn_myInlineTVF”.
5.       Write an equivalent query for the above.

In our test we will clear Buffer cache in each run and will collect STATISTICS IO and TIME output.

Let’s get started:

1.       Create a database “TestDB”
CREATE DATABASE TestDB
GO

USE TestDB
GO

2.       Create a table “tblLarge” and populate it with 100,000 records.
IF OBJECT_ID('tblLarge') IS NOT NULL
    DROP TABLE tblLarge
GO

CREATE TABLE tblLarge
    (
      xID INT IDENTITY(1, 1) ,
      sName1 VARCHAR(100) ,
      sName2 VARCHAR(1000) ,
      sName3 VARCHAR(400) ,
      sIdentifier CHAR(10) ,
      dDOB DATETIME ,
      nWage NUMERIC(20, 2) ,
      sLicense VARCHAR(25)
     )
GO

-- insert 100,000 dummy records
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 100000

3.       Create indexes:
-- create a clustered index
ALTER TABLE dbo.tblLarge ADD CONSTRAINT PK_tblLarge PRIMARY KEY CLUSTERED (xID)
GO

CREATE NONCLUSTERED INDEX IX_tblLarge_sIdentifier ON dbo.tblLarge
       (sIdentifier)
INCLUDE (sName1, sName2, sName3) 
       WITH( STATISTICS_NORECOMPUTE = OFF)
GO

4.       Create the Table-Valued Function (TVF) “fn_myTVF”:
CREATE FUNCTION dbo.fn_myTVF
    (
      @sIdentifier VARCHAR(10)
    )
RETURNS @myTbl TABLE
    (
      xID INT NOT NULL ,
      sName1 VARCHAR(100) ,
      sName2 VARCHAR(1000) ,
      sName3 VARCHAR(400)
    )
AS
    BEGIN  
        INSERT  INTO @myTbl
                SELECT  xID ,
                        sName1 ,
                        sName2 ,
                        sName3
                FROM    tblLarge
                WHERE   sIdentifier = @sIdentifier
     
        RETURN 
    END
GO

5.       Create the Inline Table-Valued Function (ITVF) – “fn_myInlineTVF”:
CREATE FUNCTION dbo.fn_myInlineTVF
       (
         @sIdentifier VARCHAR(10)
       )
RETURNS TABLE
AS
RETURN
       ( SELECT xID ,
                sName1 ,
                sName2 ,
                sName3
         FROM   tblLarge
         WHERE  sIdentifier = @sIdentifier
       )
6.       Now, run the following query a few times and examine the Actual Execution Plan (CTRL+M)

SET STATISTICS IO ON
SET STATISTICS TIME ON

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS
SELECT a.xID, a.sName1, a.sName2, a.sName3 FROM dbo.fn_myTVF('AA') a

DBCC DROPCLEANBUFFERS
SELECT a.xID, a.sName1, a.sName2, a.sName3 FROM dbo.fn_myInlineTVF('AA') a

DBCC DROPCLEANBUFFERS
SELECT a.xID, a.sName1, a.sName2, a.sName3 FROM tblLarge a WHERE sIdentifier = 'AA'

Three execution plans:
It looks like the first Execution Plan which is “fn_myTVF” has better efficiency and is less expensive when compared with the other two!


Let’s hover over the mouse on the first node of the plan tree (SELECT Cost 0%), we will see that the estimated number of rows is 1 (one) and for which the estimated sub-tree cost is also low “0.0032863”, though in reality the query return 366 rows in our case.



Thus we can easily conclude that the cardinality estimation is completely wrong and so is the query cost.


STATISTICS TIME and IO output:
From this comparison, we can easily notice that the TVF version is a more expensive operation than the other two.

--- TVF
Table '#267ABA7A'. Scan count 1, logical reads 5, physical reads 0,
                           read-ahead reads 0, lob logical reads 0,
                           lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 32 ms,  elapsed time = 179 ms.

-- ITVF
Table 'tblLarge'. Scan count 1, logical reads 8, physical reads 3,
                           read-ahead reads 5, lob logical reads 0,
                           lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 134 ms.

--SELECT Statement
Table 'tblLarge'. Scan count 1, logical reads 8, physical reads 3,
                           read-ahead reads 5, lob logical reads 0,
                           lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 140 ms.


Conclusion:
If it is possible, Inline table-valued function should be used to avoid hidden performance gotchas. Alternatively, the TVF functionality may be merged into the main Stored Procedure or in the SQL batch.