Total Pageviews

Tuesday, April 9, 2013

Non-clustered indexes per table – how many indexes are too many?


There is a strong recommendation that “Don’t create too many indexes, because it degrades performance due to overhead of DML operations”. Having indexes on a table is necessary and on the other hand it may have a tradeoff – performance gain versus overhead of maintaining. All indexes need optimization (rebuild, reorganize or statistics update) on a regular basis. So if the overall performance gain is worth more than the cost of maintaining indexes we receive from the queries that uses the index, then having indexes is fully justified.

MSDN Says “As with most performance optimization techniques, there are tradeoffs. For example, with more indexes, SELECT queries will potentially run faster. However, DML (INSERT, UPDATE, DELETE and MERGE) operations will slow down significantly because more indexes must be maintained with each operation. Therefore, if queries are mostly SELECT statements, more indexes can be helpful. If the application performs many DML operations, we should be conservative with the number of indexes we create.”

Non-clustered indexes per table:
Starting from SQL Server 2008, a table can have 999 non-clustered indexes where as in SQL Server 2005, a table can have 249 non-clustered indexes. So from SQL 2008 there is a significant enhancement done in Database Engine level. In SQL 2012, one more new feature has been added– columnstore index, though it is not useful in OLTP implementation (sorry folks columnstore index is read only).

Index column limitation:
(a)    An index key (length of all index columns) can’t exceed 900 bytes and there will be a maximum of 16 columns in an index key.
(b)   From SQL Server 2005, we can include non-key columns in a non-clustered index to avoid the limitation of a maximum of 16 key columns and 900 bytes limitation.
(c)    If the table contains one or more XML indexes, the clustering key of a table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index.
(d)   LOB data type (image/Text) can’t be part of an index key.

Good practices while creating indexes:
Creating useful indexes is one of the most important ways to achieve better query performance. Thoughtful indexes help find data with fewer disk I/O operations, less CPU cycles, small memory footprint and less system resource usage. The following are a few good practices around indexes:

(a)    Create a separate file group for indexes and place the file in a faster disk such as RAID10.
(b)   Based on DML operation, adjust Fill Factor from 80 to 90 for example. Low Fill Factor value introduces memory pressure, extensive I/O operation while High Fill Factor value causes page split thus high fragmentation.
(c)    Multi-column indexes are very useful; it creates multi-column statistics, but watch for “Index Depth”, more than 3 of this value decreases search efficiency.
(d)   Never add the clustered key with a non-clustered index or with the INCLUDE option.
(e)   While creating multi-column indexes, keep most unique value columns (high selective) to the left and try not to create a wider column index.
(f)     Be very careful about duplicate indexes and drop it without any hesitation.
(g)    Overlapping indexes - try to consolidate multiple indexes into few such as one or two
(h)   Review index usage on a regular basis and adjust (or drop) non-optimal indexes.

Index Rebuild/Reorganize know-how: 
1.       Always use DROP_EXISING=ON while rebuilding/adjusting an existing index.
2.       Examine fragmentation overtime; and
If the “avg_fragmentation_in_percent” value derived from “sys.dm_db_index_physical_stats” then rebuild/reorganize as follows:
(a) 5% and < = 30% - ALTER INDEX REORGANIZE
(b) > 30% - ALTER INDEX REBUILD
3.       Indexes with less than128 extents (1,024 pages =8mb) may not be good candidates for index rebuild.
4.       Clustered index cannot rebuild ONLINE when the underlying table contains LOB data type.
5.       A non-clustered index can rebuild ONLINE if it does not contain columns of LOB data type even though the underlying table does.
6.       Reorganizing (REORGANIZE) an index is always executed online and lesser resources are used.
7.       Reorganize does not update statistics but only compacts the index pages.
8.       Rebuilding a unique or non-unique clustered index does not rebuild the non-clustered indexes.
9.       When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

Non-clustered indexes per Table impact:
In the following test we will be reviewing the impact of having a number of indexes in a table. We will be performing this test:

1.       On a Heap without any index.
2.       On a clustered index with couple of indexes.

We will use the following work flow to perform a simple test:

1.       We will run SQL Profiler Trace.
2.       Create a table (which is still a heap).
3.       Insert 10,000 records.
4.       DROP the table and recreate it with clustered index, create five non-clustered indexes.
5.       Compare the profiler and DMV output about duration, page split and fragmentation.

The above test can also be performed using Microsoft “OSTress.exe” by creating multiple concurrent connections by executing smaller DML statements to simulate production like behavior. Either way, the final results will be same.

DMV Query:

To check table and index level changes:
SELECT  OBJECT_NAME(ios.object_id, ios.database_id) as table_name,
              ios.index_id ,
        si.name AS index_name,
        ios.leaf_insert_count +
        ios.leaf_update_count +
        ios.leaf_delete_count AS leaf_changes,
        ios.leaf_allocation_count AS leaf_page_splits,
        ios.nonleaf_insert_count +
        ios.nonleaf_update_count +
        ios.nonleaf_delete_count AS nonleaf_changes,
        ios.nonleaf_allocation_count AS nonleaf_page_splits,
        (ios.range_scan_count + ios.leaf_insert_count
            + ios.leaf_delete_count + ios.leaf_update_count
            + ios.leaf_page_merge_count + ios.singleton_lookup_count
           ) total_changes
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
        JOIN sys.objects so ON so.object_id = ios.object_id
        JOIN sys.indexes si ON si.object_id = ios.object_id
                               AND si.index_id = ios.index_id
        JOIN sys.schemas ss ON so.schema_id = ss.schema_id
WHERE   OBJECTPROPERTY(ios.object_id, 'IsUserTable') = 1
ORDER BY leaf_changes DESC

To check index fragmentation:
SELECT  a.index_id ,
        b.name AS [object_name],
        CONVERT(NUMERIC(5,2),a.avg_fragmentation_in_percent) pct_avg_fragmentation
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id
                                 AND a.index_id = b.index_id;


Let’s get started:

1.       Create the database and table:

CREATE DATABASE TestDB
GO
USE [TestDB]
GO

CREATE TABLE [tblLarge](
       [xID] [int] IDENTITY(1,1) NOT NULL,
       [sName1] [varchar](10) NULL,
       [sName2] [varchar](13) NULL,
       [sName3] [varchar](36) NULL,
       [sIdentifier] [char](2) NULL,
       [dDOB] [date] NULL,
       [nWage] [numeric](12, 2) NULL,
       [sLicense] [char](7) NULL,
       [bGender] [bit] NULL
) ON [PRIMARY]
GO


2.       Start the SQL Profiler Trace and we can filter the trace for the SPID which we are using to execute query.
3.       Run the following INSERT statement in a loop to insert 10,000 records.

DECLARE @n INT = 0
BEGIN TRAN
WHILE @n <= 10000
      BEGIN
            INSERT  INTO tblLarge
                    ( sName1 ,
                      sName2 ,
                      sName3 ,
                      sIdentifier ,
                      dDOB ,
                      nWage ,
                      sLicense ,
                      bGender
                    )
            VALUES  ( LEFT(CAST(NEWID() AS VARCHAR(36)), 8) ,
                      LEFT(CAST(NEWID() AS VARCHAR(36)), 13) ,
                      CAST(NEWID() AS VARCHAR(36)) ,
                      LEFT(CAST(NEWID() AS VARCHAR(36)), 2) ,
                      DATEADD(dd, -RAND() * 20000, GETDATE()) ,
                      ( RAND() * 1000 ) ,
                      SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 6, 7) ,
                      COALESCE(ISNUMERIC(LEFT(CAST(NEWID() AS VARCHAR(36)),1)),0)
                    )
            SET @n = @n + 1
      END
COMMIT TRAN

4.       Drop the existing table. Then create the same table, along with a clustered index and five non-clustered indexes.

ALTER TABLE tblLarge ADD CONSTRAINT PK_tblLarge
PRIMARY KEY CLUSTERED (xID) WITH (FILLFACTOR=90)

CREATE NONCLUSTERED INDEX IX_tblLarge_sIdentifier ON tblLarge (sIdentifier)
INCLUDE (sName1, sName2 ) WITH (FILLFACTOR=90)

CREATE NONCLUSTERED INDEX IX_tblLarge_sName1 ON tblLarge (sName1)
INCLUDE (sIdentifier, sName2) WITH (FILLFACTOR=90)

CREATE NONCLUSTERED INDEX IX_tblLarge_sName2 ON tblLarge (sName2)
INCLUDE (dDOB, nWage ) WITH (FILLFACTOR=90)

CREATE NONCLUSTERED INDEX IX_tblLarge_sName3 ON tblLarge (sName3)
INCLUDE (sIdentifier, nWage, sLicense) WITH (FILLFACTOR=90)

CREATE NONCLUSTERED INDEX IX_tblLarge_dDOB ON tblLarge (dDOB)
INCLUDE (sIdentifier, sName1, sName2, sName3 ) WITH (FILLFACTOR=90)

5.       Run the INSERT statement again and collect DMV and SQL Profiler output. Below are all the captured outputs:

Figure #1: Inserting 10000 records in a Heap
  
Figure #2: DMV output when inserting 10,000 records in a clustered table and when there are five non-clustered indexes
 
Figure #3: SQL Profiler traces output


Points from test:
From our above simple test we may conclude the following:
1.       When a table is HEAP, INSERT is fast.
2.       The INSERT statement is much more expensive when having indexes in terms of CPU, Duration, Read and Writes.
3.       When we have clustered and non-clustered indexes, due to INSERT, page splits occurs in leaf and non-leaf level.
4.       Fragmentation on all non-clustered indexes increases rapidly. However, fragmentations on clustered indexes are low.

Summary:
As we saw from our test that having indexes does have operational costs, but it does not make sense to stop creating useful indexes. If indexes are necessary then we should create it judiciously while ensuring all the best practices.   

How many indexes are too many?  As many as required.

References:
Learn from Guru: Paul S. Randal

Expert Performance Indexing for SQL Server 2012 (aprèss book)
Jason Strate and Ted Krueger

Specify Fill Factor for an Index

ALTER INDEX (Transact-SQL)


Saturday, April 6, 2013

SQL Server 2012 AlwaysON: "Automatic Page Repair" - how does it work?

SQL Server 2012 brought us a new HADR (high availability disaster recovery) feature and declares that database mirroring will be deprecated in future versions. Like database mirroring, AlwaysON has a similar feature: “Automatic Page Repair”. In the “AlwaysON Availability Groups”, if a participating database in any replica became a victim of page level issue (logical consistency) then that page will be automatically repaired by a non- affected page from another replica. A background service does this repair task once SQL Server Database Engine encounters an error similar to “SQL Server detected a logical consistency-based I/O error”.

In this article, we will be testing “Automatic Page Repair” functionality of SQL Server 2012 in the Windows 2012 environment. To test this feature we have the following:

1.       The entire environment is running inside VMware ESXi 5.0
2.       We have two Nodes Windows 2012 Datacenter Failover Cluster (WSFC)
3.       Two nodes SQL Server 2012 instance participating in WSFC.
4.       One is read-write "primary replica" and another is readable "secondary replica".
5.       Failover mode is “Automatic” and data synchronization mode is “synchronous”

In our testing, we will manually corrupt a primary read-write database to see how “Automatic Page Repair” functionality helps us to recover our database. To corrupt our test database we will be using a HexEditor XVI32 which can be downloaded from http://www.chmaas.handshake.de/.

A brief about our AlwaysON:

1.         ”BlogHAGroup” is the AlwaysON Availability Groups.
2.         SQL12K1 is the primary replica.
3.         Our “TestDB” is the only database participating in AlwaysON Availability Groups and resides in SQL12K1.
4.         SQL12K2 is the secondary replica and where the “TestDB” is read only.

Screenshots of our AlwaysON Setup:


Windows 2012 Failover Cluser with AlwaysON Availability Groups:

AlwaysON Availability Groups in SSMS 2012:



Let’s Get Started:

1.       Let’s create a table “Country” in the primary replica and insert some data into this table.

CREATE TABLE [Country]
    (
      [xID] [int] IDENTITY(1, 1)
                  NOT NULL ,
      [sCountry] [varchar](50) NOT NULL ,
      [sAbbrev] [varchar](5) NOT NULL
    )

INSERT  INTO Country
        ( sCountry, sAbbrev )
VALUES  ( 'Canada', 'CA' ),
        ( 'United States', 'US' ),
        ( 'Afghanistan', 'AF' ),
        ( 'Albania', 'AL' ),
        ( 'Algeria', 'DZ' ),
        ( 'Andorra', 'AD' ),
        ( 'Angolia', 'AO' )


2.       Query the primary and secondary replica. Both table schema and the data have been synchronized (figure #1).

3.       To simulate a database disaster and  to corrupt the database we need to do the following:
(a)    We stop SQL Server Service in SQL12K1. Please note that in AlwaysON mode, a participating database in an “availability group” can’t be offline.
(b)   Go to the database folder and provide the appropriate OS permission. And then use HexEditor to open the data file (figure #2).
(c)    Let’s search the string “Andorra” and on the left hand side, type any number and then save the changes.
(d)   Start SQL Server Service on SQL12K1 which is our primary read-write replica.

4.       Execute a SELECT statement on primary replica (SQL12K1) and we will receive the following error:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x2f2c9f8a; actual: 0xb9b4fc2b). It occurred during a read of page (1:231) in database ID 5 at offset 0x000000001ce000 in file 'H:\HADB\TestDB.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

5.       Query the following table and DMV, and then we will see the following page related issue recorded in the table (figure #3).
     select * from msdb..suspect_pages
     select * from sys.dm_hadr_auto_page_repair
 
6.       Wait for a few seconds to let the SQL Server Engine repair the corrupt page then execute the SELECT statement against “country” table again. The corrupted page has been rectified!

Figure # 1: Table and data


Figure # 2: Manually damaging a page with HexEditor

Figure # 3: Suspect pages
 
Summary:
As we saw the AlwaysON service is an excellent feature in SQL Server 2012 which provides maximum benefits for “High Availability” than ever before and “Automatic Page Repair” protects the database in instantly.

Tuesday, April 2, 2013

DBCC CHECKDB – “After death comes the doctor”

It is a common practice for a lot of us to run DBCC CHECKDB before backing up the database. Doing so on a small database performance impact may be negligible. However, for a large and high-end OLTP system, impact is noticeable and significant.

The point is why do we run DBCC CHECKDB and what does it achieve? Generally DBCC CHECKDB is considered as a maintenance command (corruption detection and repairing) to make sure that a database is in a consistent state. it can be used to repair a suspect or corrupt database with or without losing data. Losing a single byte of production data is not desirable and running DBCC CHECKDB isn’t the only way to make sure if the database is in good shape.

In this article we will be focusing on some other options and a easy to follow checklist which will help to prevent a possible database corruption.

What is database corruption?
When one or more pages cannot be read by the database engine, the database will be marked as Suspect or Restore pending. Generally there are two types of page corruptions that can happen– logical and physical.

(a)    Physical error: 823
A Windows read or write request has failed. In the read case, SQL Server will have already retried the read request four times. This error is often the result of a hardware error, but may be caused by the device driver. This is known as CRC (cyclic redundancy check) failure.
(b)   Logical error: 824
This error indicates that Windows reports that the page is successfully read from disk, but SQL Server has discovered something wrong with the page. This error is similar to error 823 except that Windows did not detect the error. This usually indicates a problem in the I/O subsystem, such as a failing disk drive, disk firmware problems, faulty device driver, and so on. This is known as torn page detection.
(c)    Restore Pending: 829
A page has been marked as restore pending.

Why do database corruptions happen?
SQL Server Engine highly cares about database corruption and although Microsoft put excellent and tremendous effort to protect user databases from every angle, database corruption still may happen due to the following identified reasons:

(a)    Faulty disk and controller system
(b)   Outdated bios firmware
(c)    Disk Caching issue
(d)   inappropriate system driver
(e)   Power supply failure

How to prevent database corruption:
There is no precise answer to this. Generally, database corruption happens due to faulty hardware and it is more or less random in nature. Therefore proactive monitoring of various logged events and taking action against suspicious occurrences is the key to prevent database corruption.

Starting from SQL 2005, there are features in Database Engine which reduces the likelihood of page level corruption, one is "Checksum" mechanism and another is "automatic page repair".

(a)    PAGE_VERIFY  CHECKSUM (SQL 2005, and up): This is a per database setting.  When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header.

However, there is a glitch about this mechanism - the checksum are computed on the pages that are written only after the database has been enabled for checksum and re-computed when such pages are read back. Since the database was not initially created with checksum enabled, there will be pages in the database that have no checksums.

(b)   Database Mirroring (SQL 2005 SP1, and up): Automatic page repair, this feature is based on the fact that the principal and mirror databases are exactly the same. So, if a page becomes corrupt on the principal, SQL Server should be able to read the page from the mirror and use it to fix the principal. Similarly, if a page becomes corrupt on the mirror, the page can be read from the principal to fix the mirror.

(c)    AlwaysON (SQL 2012): Automatic page repair is supported by AlwaysON Availability Groups. After certain types of errors corrupt a page, making it unreadable, an availability replica (primary or secondary) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner or from another replica. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error.

Performance Impact:
Running DBCC CHECKDB against large database will incur significant performance impact. DBCC CHECKDB statement typically must read each allocated page from disk into memory so that it can be checked. By design this command will check allocation and structural integrity of all tables, indexes, indexed views and as well as text and image objects. Below are three major impacted areas.

(a)    Significant memory usage
(b)   Extensive I/O activities
(c)    Heavy spooling to tempdb

DBCC CHECKDB does not create any blocking and it works on a snapshot taken internally. Please note that executing DBCC CHECKDB automatically executes DBCC CHECKTABLE for each table in the database, as well as DBCC CHECKALLOC and DBCC CHECKCATALOG, eliminating the need to run them separately.

What to monitor?
Along with others routine checks, we should include the following areas in order to understand the system and database health.

(a)    SQL Server ERROR LOG for ERROR 823, 824
(b)   Windows System Events (disk, iSCSI, ftdisk, etc)
(c)    Querying msdb for possible events: SELECT * FROM msdb..suspect_pages
(d)   AlwaysON Availability Groups: SELECT * FROM sys.dm_hadr_auto_page_repair
(e)   Database mirroring: SELECT * FROM sys.dm_db_mirroring_auto_page_repair

Last ran of DBCC CHECKDB against all databases:
/***********************************************************
** Last ran of DBCC CHECKDB against all databases
***********************************************************/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @databases TABLE
        (
          Id INT IDENTITY(1, 1)
                 PRIMARY KEY ,
          ParentObject VARCHAR(255) ,
          Object VARCHAR(255) ,
          Field VARCHAR(255) ,
          Value VARCHAR(255)
        )

INSERT  INTO @databases
        ( ParentObject ,
          Object ,
          Field ,
          Value
        )
        EXEC sp_msforeachdb N'DBCC DBINFO(''?'') WITH TABLERESULTS;';

INSERT  INTO @databases
        ( ParentObject ,
          Object ,
          Field ,
          Value
        )

        SELECT  'Final Record' ,
                'Final Record' ,
                'dbi_dbname' ,
                'Final Record';

WITH    database_name ( Id, Field, Value, DBID )
          AS ( SELECT   Id ,
                        Field ,
                        Value ,
                        ROW_NUMBER() OVER ( PARTITION BY Field ORDER BY ID )
               FROM     @databases
               WHERE    Field = 'dbi_dbname'
             ),
        Last_DBCC_CHECKDB ( Id, Field, Value )
          AS ( SELECT   Id ,
                        Field ,
                        Value
               FROM     @databases
               WHERE    Field = 'dbi_dbccLastKnownGood'
             )

     SELECT DISTINCT
            db1.Value [database_name] ,
            ldc.Value [last_DBCC_CHECKDB]
     FROM   Last_DBCC_CHECKDB ldc
            INNER JOIN database_name db1 ON ldc.Id > db1.Id
            INNER JOIN database_name db2 ON ldc.Id < db2.Id
                                            AND db2.DBID = db1.DBID + 1
     ORDER BY db1.Value;

Automated Monitoring and SQL Alert Feature:
DBCC CHEKDB is a more reactive and corrective kind of command which tells us the sad story of a database’s consistency after it corrupted. It will be too late to take action when the corruption is discovered. 

In order to get early notifications regarding database issues, we can easily implement a notification service task which will let us know immediately when an issue occurred in a database.

How to enable Alert System: 
1.       Configure Database Mail and Create a mail profile
2.       Create operator who will be notified in the event of issue
3.       In SQL Server Agent attach the mail profile to the Alert System
4.        SQL Agent Server needs to be restarted to take effect of above changes
5.       Expand SQL Agent tree nodes and choose Alert and create New Alert
6.       In the Alert Dialog box – choose “SQL Server Event Alert”
7.       For “Severity” level there are couples of options, select “Fatal” type such as 023, 024, 025, etc.

Below is a Notification Configuration screenshot for alerting a database corruption error in SQL Server Instance level for all databases.


We will receive the following alert message in case of any integrity issue.

DESCRIPTION:   SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x94a8f51f; actual: 0x0275a963). It occurred during a read of page (1:0) in database ID 7 at offset 0000000000000000 in file 'C:\Temp\TestDB.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Database Corruption related message can be found in SQL Server ERROR LOG:

2013-04-02 11:30:15.920 spid57 Setting database option ONLINE to ON for database 'TestDB'.
2013-04-02 11:30:15.920 spid57 Starting up database 'TestDB'.
2013-04-02 11:30:15.940 spid57 Error: 824, Severity: 24, State: 6.
2013-04-02 11:30:15.940 spid57 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x94a8f51f; actual: 0x0275a963). It occurred during a read of page (1:0) in database ID 7 at offset 0000000000000000 in file 'C:\Temp\TestDB.mdf'. Additional messages


Result of querying msdb for all possible recorded events happened in SQL Server instance level:

 
Recommendations:
Preventing database corruptions are one of the critical tasks of any DBA’s or SysAdmin’s job responsibilities. Along with the regular database backups and system checks described above, Automatic Page Repair features of database mirroring or AlwaysON can be used to prevent page level corruption.

Summary:
Honestly, no matter whatever we do, regular database and frequent transaction log backup is the only safeguard against database corruption or any disastrous situation. And even though, if the backup has not been restored successfully somewhere and periodically, nobody can guarantee that the backup is good enough to restore the database– we are not prepared for a database disaster.

So “After corruption comes the DBCC CHECKDB” is similar to “After death comes the Doctor”.

Learn from Guru: 
SQL Server DBCC CHECKDB
http://www.sqlskills.com/blogs/paul/category/checkdb-from-every-angle/

Description of using disk drive caches with SQL Server that every database administrator should know