Archive for the ‘Index’ Category

Should you update statistics after index rebuild?

Pop Quiz Hot Shot: Should you update statistics after index rebuild?

The short answer is NO! This is because index rebuild will update the statistics with a full scan. If you update the statistics manually after the index rebuild, you will be updating it using the default, which is a sampled scan. This is a common mistake!

When not explained correctly, this concept can even throw the very seasoned or senior DBAs into loops of confusions.  Here, I will redirect you to one of the most trusted sources and authorities for SQL Server, Paul Randal. In his posting “Rebuilding Indexes and Updating Statistics,” Paul writes:

The problem I’ve been seeing is people rebuilding indexes and then updating statistics. So why is this a problem? Well, it depends :-)

  • If your default for updating statistics is to do a sampled scan, then having rebuild update the statistics with a full scan (as a side effect), and then proceeding to manually update them again with the default sampled scan, means that after both operations you’re left with sampled statistics. You’ve wasted resources doing the sampled scan AND you’ve lost the ‘free’ full-scan statistics that the index rebuild did for you.
  • If your default is to do a full scan, then you don’t lose out on having the best statistics, but you do waste resources by unnecessarily updating statistics a second time.

So what’s the solution?

The simple answer is not to update statistics on indexes that have just been rebuilt.

The more complicated answer is to:

  1. Have a list of indexes (F) that you know will cause workload performance problems if they get fragmented
  2. Have a list of indexes (S) that you know will cause workload performance problems if the statistics don’t get regularly updated
  3. Evaluate fragmentation for list F, and choose to reorganize, rebuild, or do nothing
  4. For all indexes in list S that were not rebuilt in step 3, update statistics

What can you do? Well, you can start writing your customized script for index and update statistics maintenance or you can take a look at Ola Hallengren’s “SQL Server Index and Statistics Maintenance” script. I would opt for the latter. Why reinvent the wheels or sacrifice the precious time you could spent on other also very interesting topics of SQL Server such as performance tuning or deep dive research into SQL Server Replication?


Rebuilding Indexes and Updating Statistics

SQL Server Index and Statistics Maintenance

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

Today, I have found one of the index maintenance jobs in a SQL Server 2005 instance has failed. The history shows a helpful message:

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

This error is caused by the index reorganization step in the attempt to defragment the index in question but the index has the page level locking option disabled. The job fails and the rest of the indexes will not get worked on. On the next subsequent scheduled run of the job, the same scenario will happen until you enable page locking for this index and the indexes with page level locking disabled. Yes. It is really a royal pain in the behind. You can’t just run away from it and hoping the problem will go away. You have to man up to understand and resolve the problem.

By default, page level locking is enabled for indexes. Usually, the page level locking for the indexes get disabled unintentionally either by third-party tools or by using RTM version of SQL Server 2005 and creating indexes via the GUI in the Object Explorer. In case, you know of any other way the page level locking get disabled for the indexes, please let me know.

To find all the indexes in the database that have page level locking disabled, you can run the following query:

-- indexes that have page level locking disabled
SELECT OBJECT_NAME(i.object_id) as TableName
, name as IndexName
, allow_page_locks
FROM sys.indexes as i

You can then submit a change request to have these indexes modified to enable page level locking.

To generate the code to alter the indexes to enable page level locking for all the required indexes, you can run the following query. This query will omit the internal system tables that do not have the page level locking for the indexes:

-- generate the TSQL Statments to enable page level locking for the required indexes
SELECT 'ALTER INDEX ' +i.Name+ ' ON ['
	+ts.TABLE_CATALOG+ '].[' +ts.TABLE_SCHEMA+ '].[' +ts.TABLE_NAME+']'
	+' SET (ALLOW_PAGE_LOCKS = ON)' as TSQL_Statement
FROM sys.indexes i LEFT OUTER JOIN sys.tables t
		ON i.object_id = t.object_id
WHERE i.allow_page_locks = 0

Save the output and run it on the database in question and then try the index maintenance job again. This will solve the page level locking is disabled error.






What is an Index in SQL Server?

Similar to the index in the back of a book of which enables you to seek for the relevant terms without scanning the entire book, an index in SQL Server allows search operations to find data in a table without scanning all rows in the entire table.  There are two possible types of indexes in SQL Server. They are clustered and non-clustered.

There can be only one clustered index in a table. This similar to a dictionary – usually a big reference book that lists all the terms in alphabetical order with definitions next to the terms. You may be able to find them in museums, libraries, or in your grandparents’ homes. A clustered index is similar to a dictionary because data can be ordered in only one way. Usually, ordered in numeric sequence or in alphabetical order. When you create a primary key for a table which uniquely identifies each record in the table, by default a clustered index is also created. Even though you can override this default behavior, it is usually not recommended.  When a clustered index is created, it establishes an order for the data.

There can be 249 non-clustered indexes in SQL Server 2005 and 999 in SQL Server 2008. This is because you can order the data in many different ways by creating different non-clustered indexes to speed up the data retrieval process. The non-clustered indexes usually don’t include all the columns of the table. They usually contain only a selected number of columns. Non-clustered indexes are separate ordered structures from the base table. They can be stored in different drives and/or filegroups. This enables SQL Server to access the table and the indexes concurrently if needed to speed up querying.

Considering the benefits of the clustered and non-clustered indexes, even though you can only have one clustered index, you may be tempted to create many non-clustered indexes. Similar to everything in life, you will need to find the perfect balance or you will suffer the consequences. This is similar to a hangover after too many beers or glasses of Hennessy Paradis on a great night out in the city or having too many crispy crème donuts. While the indexes speed up data retrieval, they have negative impacts on insert, update, and delete statements. As data rows get inserted, updated, or deleted, so will the indexes. When you add a row to a table, the row will also be added to the index. The same goes for updates and deletes. In addition, more disk space and memory are required. For clustered indexes, the ordering of the rows will also need to be re-adjusted and this will also require the adjustment of the subsequent rows below the rows being affected.  An interesting note is that in order to find the row to update or delete, the indexes will aid in speeding up the process of finding the row.




Table and Index Architecture

How to identify and remove unused indexes (only after carefully review and verification)?

Indexes are duplicates of parts of the data that are already in the tables. They are ordered or filtered in various ways to improve performance.  Indexes can be accessed through seeks, scans, and lookups. You can think of seeks as similar to when you found the page number for a key term you are looking for in the back of the index section of a book and you flip to the corresponding page to read about the key term. Scans are similar to when you found the key term is in a range of pages and you need to scan through the pages to find the relevant information for the key term. Lookups are similar to when you found the page for the key term and then there are additional key terms that are related to the key term that you will also need to look up.

As with everything in life, nothing is perfect. There must be manual interventions with careful balancing acts to achieve desired results or optimum effects.  Indexes improve data selection queries. But, have adverse effects on updates. When the data in a table are INSERTED, UPDATED, and DELETED the indexes will need to be updated as well as indexes are duplicates of parts of the data that are already in the table. This will increase the transactions lengths, query execution duration, locking, blocking, and the number of annoying timeouts. Disk space is also a concern as unnecessary indexes will store unnecessary data. One of the important objectives as a SQL Server DBA is to continuously maintain and improve the indexes maintenance process by selectively creating the indexes that the databases need and remove the unused ones.

The sys.dm_db_index_usage_stats Dynamic Management View (DMV) presents you with the critical and relevant index usage data that you will need to identify the updates, seeks, scans, and lookups since the last SQL Server service restart. If SQL Server reports that there is high number of updates, but minimal or zero for seeks, scans, and lookups, for the index, it is a candidate for removal after careful review and consideration.

The following script is an example of how to identify the unused indexes and generate the TSQL statements to drop them. We will exclude the clustered indexes, foreign keys, unique constraints, and system objects.  Again, review and verify them carefully before dropping.

-- Identify the unused indexes 
-- Algorithm: hight user updates, minimial or zero seeks, scans, and lookups
-- Exclude the clustered indexes, foreign keys, and unique constraints

-- Create the temp table to store the unused indexes
DROP TABLE #UnusedIndexes

CREATE TABLE #UnusedIndexes
ID int identity(1,1)
,DatabaseName VARCHAR(123)
,SchemaName VARCHAR(123)
,TableName VARCHAR(123)
,IndexName VARCHAR(123)
,User_Updates INT
,User_SeeksScansLookups INT
,System_Updates INT
,System_SeeksScansLookups INT  

-- Load the unused indexes into the temp table
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #UnusedIndexes
	DB_NAME() AS DatabaseName
	, SCHEMA_NAME(o.Schema_ID) AS SchemaName
	, OBJECT_NAME(ix_usage_stats.object_id) AS TableName
	, AS IndexName
	, ix_usage_stats.user_updates
	, ix_usage_stats.user_seeks + ix_usage_stats.user_scans + ix_usage_stats.user_lookups
							AS [User SeeksScansLookups]
    , ix_usage_stats.system_updates							
	, ix_usage_stats.system_seeks + ix_usage_stats.system_scans + ix_usage_stats.system_lookups
							AS [System SeeksScansLookups]
FROM sys.dm_db_index_usage_stats ix_usage_stats
INNER JOIN sys.indexes ix ON ix_usage_stats.object_id = ix.object_id
	AND ix_usage_stats.index_id = ix.index_id
INNER JOIN sys.objects o ON ix.object_id = o.object_id
WHERE ix_usage_stats.database_id = DB_ID()
	AND IS NOT NULL -- exclude heaps
	AND ix.type <> 1 -- exclude the clustered indexes
	AND o.is_ms_shipped = 0 -- exclude system objects
	AND o.type NOT IN(''F'', ''UQ'') -- exclude the foreign keys and unique contraints
	AND ix_usage_stats.user_seeks = 0
	AND ix_usage_stats.user_scans = 0
	AND ix_usage_stats.user_lookups = 0

-- Query the top 25 order by the user updates and generate the TSQL statement to drop the unused indexes
SELECT top 25 uix.DatabaseName
,'DROP INDEX [' +uix.IndexName+ '] ON [' +uix.DatabaseName+ '].[' +uix.SchemaName+ '].[' +uix.TableName+ ']' as [DROP INDEX Statement]
FROM #UnusedIndexes uix
ORDER BY uix.User_Updates desc






How to reorganize and rebuild indexes based on fragmentation percentage?

Index fragmentation is not an ideal situation where the data pages are not contiguous. It is caused by page splits. When data pages become full, SQL Server has to redistribute data from a page that’s full to a new data page. New data pages are created all the way back to the end of the line. When searching, instead of going from one page right to the next, SQL Server has to go to a different location to look for what’s being requested. It is similar to going to a popular club with your friends. If your friends are scattered all over the club, you will need to search for them in different floors or corners instead of being able to speaking directly to them if they were right next to you.

As data is inserted, updated, and deleted, fragmentation levels on the underlying indexes will inevitably rise. There are 2 types of fragmentation: external and internal. External fragmentation causes performance issues for requests for ordered results. It occurs when the logical order of the index does not match the physical order of the index. Internal fragmentation occurs when there is too much empty space in the index pages usually caused by insert, update, and delete operations.

Index fragmentations, both external and internal, will degrade SQL Server query performance increasing the data retrieval time. To report on the indexes fragmentation percentage, you can use the Dynamic Management Function (DMF) sys.dm_db_index_physical_stats in addition to joining it to the sys.indexes, sys.tables, and sys.schemas tables. You can then store the indexes fragmentation information into a temp table and then use the alter index statement to reorganize or rebuild indexes based on the fragmentation percentage. You can schedule a SQL Server Agent job to automate and run this index maintenance process on a desired or preferred time interval. In the following script, we will reorganize indexes with fragmentation percent between 10% to 30% and rebuild indexes if fragmentation >= 30% and rebuild online for the Enterprise editions:

-- Rebuild index if fragmentation >= 30%
-- Reorganize if fragmentation between 10% to 30%

DECLARE @dbname VARCHAR(123) -- 123 is the limit for the database length
  ,@tablename VARCHAR(125)
  ,@schemaname VARCHAR(50)
  ,@indexName VARCHAR(125)
  ,@percentfragmentation DECIMAL (10,2)
  ,@id int -- to store the sequence id of the databases
  ,@max int -- to store the number of databases
  ,@cmdfragmentationload VARCHAR(MAX) -- cmd to load the fragmentation data
  ,@cmddefragmentation VARCHAR(MAX) -- cmd to run the defragmentation process
  ,@edition varchar(50) -- sql server edition

-- create a temp table to store the fragmentation information
IF OBJECT_ID('tempdb..#fragmentation') IS NOT NULL
DROP TABLE #fragmentation

CREATE TABLE #fragmentation
id int identity (1,1)
,dbname VARCHAR(123)
,tablename VARCHAR(125)
,schemaname VARCHAR(50)
,indexname VARCHAR(125)
,avgfragmentation DECIMAL (10,2)

-- create a temp table to store the list of databases
IF OBJECT_ID('tempdb..#databases') IS NOT NULL
DROP TABLE #databases
CREATE TABLE #databases
  id int identity (1,1)
  , dbname varchar(128) not null

-- load the database(s) into the temp table
INSERT INTO #databases (dbname)
SELECT '['+name+']'
FROM sys.databases
WHERE name not in ('tempdb') -- databases to exclude
AND state = 0 -- must be online

SELECT @id = 1, @max = max(id)
FROM #databases

-- load the index fragmentation information into the #fragmentation temp table
WHILE (@id <= @max) 
   SELECT @dbname = dbname      
   FROM #databases      
   WHERE id = @id          

   SET @cmdfragmentationload = 'INSERT INTO #fragmentation   
            (dbname, tablename, schemaname, indexname, avgfragmentation)   
            SELECT '''  +@dbname+ ''' AS DBName        
               ,tb.Name AS TableName        
               ,scmas.Name AS SchemaName        
               , AS IndexName        
            FROM ' +@dbname+ '.sys.dm_db_index_physical_stats(DB_ID('''+@dbname+'''), NULL, NULL,                                        NULL, ''DETAILED'') AS ix_physical_stats         
                   JOIN ' +@dbname+ '.sys.indexes ix         
                        ON ix_physical_stats.object_Id = ix.object_id 
                          AND ix_physical_stats.index_id = ix.index_id         
                   JOIN ' +@dbname+ '.sys.tables tb         
                        ON ix.object_id = tb.object_Id         
                   JOIN ' +@dbname+ '.sys.schemas scmas         
                        ON tb.schema_id = scmas.SCHEMA_ID   
            WHERE ix_physical_stats.avg_fragmentation_in_percent > 10
      AND tb.type = ''U''
      AND ix_physical_stats.page_count > 25'

   EXEC(@cmdfragmentationload) -- load data on each iteration

   set @id = @id + 1 -- increase the @id counter by 1

-- defrag the indexes
-- Re-organize: 10% to 30% fragmentation
-- Re-build: >= 30% fragmentation
SELECT @id = 1, @max = MAX(id)
FROM #fragmentation

WHILE (@id <= @max)
  SELECT @dbname = f.dbname
  , @tablename = f.tablename
  , @schemaname = f.schemaname
  , @indexName = f.indexname
  , @percentfragmentation = f.avgfragmentation
  FROM #fragmentation f
  WHERE id = @id

  IF @percentfragmentation < 30 -- fragmentation < 30%, reorganize
    SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REORGANIZE'
    EXEC (@cmddefragmentation)
  ELSE -- fragmentation >= 30%, rebuild
      IF (@edition = 'Ent' or @edition = 'Dev') -- if enterprise version, then rebuild index online
          SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REBUILD WITH(ONLINE = ON)'
           EXEC (@cmddefragmentation)
          SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REBUILD'
          EXEC (@cmddefragmentation)
        SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REORGANIZE'
        EXEC (@cmddefragmentation)
    END CATCH        

  SET @id = @id + 1 -- always remember to increase the counter by 1