Category Archives: Database ReIndexing

Performance improvement of a query using indexes.


If a Microsoft SQL Server 2005 database has two or more tables that are frequently joined together, then the columns used for the JOINs should have an index configured. If the columns used in the join are “wide”, for example Email varchar(70), then use a surrogate key for the JOINs such as

EmailID INT IDENTITY(1,1) PRIMARY KEY

INT is 4 bytes fixed length column yields extremely good performance in JOINs. Example for index create:

— T-SQL create index – create nonclustered index on contactid column

CREATE NONCLUSTERED INDEX [idxContactPK] ON [Person].[Contact] ([ContactID] ASC)

GO

Regular index maintenance should include rebuilding indexes every weekend. Extremely dynamic OLTP tables should be rebuilt with FILLFACTOR 70-90.

— MSSQL index rebuild with fillfactor

USE AdventureWorks;

GO

ALTER INDEX ALL ON Sales.SalesOrderDetail

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

GO

Covering indexes are good for queries which are executed very frequently, business critical or mission critical. SQL Server 2005 allows the inclusion of nonkey (not indexed) columns in covering indexing. That is, all columns referenced in the query are included in the index as either key or nonkey columns. Composite index can also cover a query (pre-2005). This allows the query optimizer to locate all the required information from an index seek or scan; the table or clustered index data will not be accessed. Example: Category and Subcategory form a composite index key. Frequent query includes Category, Subcategory, Color and Size. Including Color and Size as nonkey columns in the index will speed up the query significantly.

— SQL convering index demo – INCLUDE option in create index

USE AdventureWorks;

GO

IF EXISTS (SELECT name FROM sys.indexes

            WHERE name = N’idxContactCovering’)

    DROP INDEX idxContactCovering ON Person.Contact;

GO

— Create index covers all the columns in the following query

CREATE NONCLUSTERED INDEX idxContactCovering

    ON Person.Contact (ContactID)

    INCLUDE (FirstName, LastName, EmailAddress);

GO

— SQL covered query

SELECT FirstName+’ ‘+ LastName as FullName, EmailAddress, ContactID

FROM Person.Contact

WHERE ContactID BETWEEN N’10000′ and N’11000’

ORDER BY FullName;

GO

/* Partial results

FullName          EmailAddress                        ContactID

Alexandra Allen   alexandra90@adventure-works.com     10115

Alexandra Hall    alexandra89@adventure-works.com     10058

Alexandra Smith   alexandra92@adventure-works.com     10494

Alexandra Young   alexandra91@adventure-works.com     10181

Allison Allen     allison45@adventure-works.com       10160

*/

Covering Indexes , Create , execute in SQL Server


The Microsoft SQL Server T-SQL create index has the INCLUDE option (2005 and on) to include non-key column(s) in the index so that a specific query can be satisfied – covered – from the index without accessing table data. Composite index can also cover a query. Covering index speeds up the target query, however, due to larger size, may have negative impact on other queries.

— MSSQL query with execution timing envelope
— DBCC DROPCLEANBUFFERS forces pages to disk for consistent time measurements
— SELECT INTO table create
USE tempdb;
SELECT * INTO Address
FROM AdventureWorks2008.dbo.Address
 
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
— TABLE SCAN (NonClustered)
— COST 0.275
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE ‘34%’;
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
— logical reads 340
— 76 msec
 
 
— T-SQL create index with non-key columns INCLUDE
— MSSQL SELECT will be covered for City & StateProvinceID (not for other colums)
CREATE INDEX idxPostalCodeCovering
ON dbo.Address (PostalCode)
INCLUDE ( City, StateProvinceID);
GO
— Command(s) completed successfully.
 
 
— SQL Server time query again after index create
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
— INDEX SEEK (NonClustered)
— COST 0.0034
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE ‘34%’;
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
— logical reads 4,
— 10 msec
 
 
DROP INDEX dbo.Address.idxPostalCodeCovering
GO
 
— T-SQL create composite index
— MSSQL SELECT will be covered for City & StateProvinceID (not for other colums)
CREATE INDEX idxPostalCodeCoveringComposite
ON dbo.Address (PostalCode,City, StateProvinceID);
GO
— Command(s) completed successfully.
 
 
— SQL Server time query again after index create
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
— INDEX SEEK (NonClustered)
— COST 0.0034
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE ‘34%’;
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
— logical reads 4,
— 13 msec
 
 
— Cleanup
DROP TABLE dbo.Address

Identify fragmentation for all the indexes in a database


USE AdventureWorks;

SELECT   ‘dbcc showcontig (‘ + convert(VARCHAR,i.[object_id]) + ‘,’ + — table id 

          convert(VARCHAR,i.index_id) + ‘) — ‘ + — index id 

          s.name + — schema name

          ‘.’ + object_name(i.[object_id]) + — table name 

          ‘.’ + i.name — index name

FROM     sys.schemas s

         JOIN sys.objects o

           ON (s.schema_id = o.schema_id)

         JOIN sys.indexes i

           ON (o.[object_id] = i.[object_id])

WHERE    o.TYPE = ‘U’

         AND i.TYPE IN (1,2)

ORDER BY s.name,

         object_name(i.[object_id]),

         i.index_id

GO

/* Partial results

dbcc showcontig (642101328,1) —
— Sales.SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

dbcc showcontig (642101328,2) — Sales.SalesOrderDetail.AK_SalesOrderDetail_rowguid

dbcc showcontig (642101328,3) — Sales.SalesOrderDetail.IX_SalesOrderDetail_ProductID

dbcc showcontig (754101727,1) — Sales.SalesOrderHeader.PK_SalesOrderHeader_SalesOrderID

dbcc showcontig (754101727,2) — Sales.SalesOrderHeader.AK_SalesOrderHeader_rowguid

dbcc showcontig (754101727,3) —
— Sales.SalesOrderHeader.AK_SalesOrderHeader_SalesOrderNumber

*/

Identify tables without Indexes in SQL Server Database


— SQL no clustered index – SQL tables missing clustered index – SQL objectproperty

USE AdventureWorksDW;

SELECT   DISTINCT [Table] = object_name(object_id)

FROM     sys.indexes

WHERE    index_id = 0

         AND objectproperty(object_id,’IsUserTable’) = 1

         AND objectproperty(object_id,’IsMSShipped’) = 0

ORDER BY [Table]

GO

/* Results

Table

AdventureWorksDWBuildVersion

DatabaseLog

FactCurrencyRate

FactFinance

FactInternetSales

FactInternetSalesReason

FactResellerSales

FactSalesQuota

ProspectiveBuyer

*/

Reindex all fragmented indexes in a database


USE AdventureWorks;

GO

— Reindex database – reindex all tables script

— Reindex fragmented indexes – rebuild all fragmented indexes

— DBREINDEX database – REBUILD all database indexes

CREATE PROCEDURE uspDBREINDEXFragmentedTables @FragmentationLimit DECIMAL

AS

BEGIN

CREATE TABLE #IndexFragmentation (

  ObjectName     CHAR(255),

  ObjectId       INT,

  IndexName      CHAR(255),

  IndexId        INT,

  Lvl            INT,

  CountPages     INT,

  CountRows      INT,

  MinRecSize     INT,

  MaxRecSize     INT,

  AvgRecSize     INT,

  ForRecCount    INT,

  Extents        INT,

  ExtentSwitches INT,

  AvgFreeBytes   INT,

  AvgPageDensity INT,

  ScanDensity    DECIMAL,

  BestCount      INT,

  ActualCount    INT,

  LogicalFrag    DECIMAL,

  ExtentFrag     DECIMAL)

INSERT #IndexFragmentation

EXEC( ‘DBCC SHOWCONTIG WITH TABLERESULTS , ALL_INDEXES’)

DELETE #IndexFragmentation

WHERE  left(ObjectName,3) = ‘sys’

UPDATE #IndexFragmentation

SET    ObjectName = s.name + ‘.’ + o.name

FROM   #IndexFragmentation i

       JOIN sys.objects o

         ON i.ObjectID = o.object_id

       JOIN sys.schemas s

         ON o.schema_id = s.schema_id

DECLARE  @Table      SYSNAME,

         @DynamicSQL NVARCHAR(1024)

DECLARE  @objectid INT,

         @indexid  INT

DECLARE  @Fragmentation    DECIMAL

DECLARE curIndexFrag CURSOR  FOR

SELECT   ObjectName,

         LogicalFrag = max(LogicalFrag)

FROM     #IndexFragmentation

WHERE    LogicalFrag >= @FragmentationLimit

         AND indexid != 0

         AND indexid != 255

GROUP BY ObjectName

OPEN curIndexFrag

FETCH NEXT FROM curIndexFrag

INTO @Table,

     @Fragmentation

WHILE @@FETCH_STATUS = 0

  BEGIN

    SELECT @DynamicSQL = ‘DBCC DBREINDEX (”’ + RTRIM(@Table) + ”’, ””, 70)’

    PRINT @DynamicSQL

    — Partial result:

    — DBCC DBREINDEX (‘Sales.SpecialOfferProduct’, ”, 70)

    EXEC sp_executesql @DynamicSQL

    FETCH NEXT FROM curIndexFrag

    INTO @Table,

         @Fragmentation

  END

CLOSE curIndexFrag

DEALLOCATE curIndexFrag

DROP TABLE #IndexFragmentation

END

GO

— Execute database reindexing (INDEX REBUILD) stored procedure

— Reindex indexes with 35% logical fragmentation or higher

EXEC uspDBREINDEXFragmentedTables 35.0

GO

%d bloggers like this: