Which one is Preferable [ INDEX REBUILD or DROP ]

Introduction

Indexing is the key factor of the performance. Design and architecture of the Index and table structure has changed depends on modification of application on time to time basis. We have to create new Indexes or modify the existing one to improve the performance. Some time we have to remove the index to see the performance effects and sometime we have to update the data also.

In MS SQL Server 2000 and earlier version we have only one option available is DROP Index and RE-CREATE it. But from MS SQL Server 2005 and later version have another option is called DISABLE Index. So question is in mind that, which one is preferable. In this article we are trying to discuss about the DROP INDEX and DISABLE INDEX feature and which one we choose depends on case scenario.

What Happened When Disable Index Occurs

Before understanding what happened when we Disable a index we must understand how to disable Index.

The Syntax of Disable Index is

ALTER ALTER INDEX <Index_Name>

ON <Schema_Name>.<Table_Name> DISABLE;

Now we have to understand the sys.dm_db_index_usage_stats DMV.

MS SQL Server 2005 onwards the most common way to monitor unused indexes is to use sys.dm_db_index_usage_stats DMV. As the name suggests this DMV returns the information that is tracked about index usage from SQL Server cache.

Disabling Non Clustered Index:

When we Disable a Non Clustered Index the Index page is Deleted and page is freed in the database.

Effect of Disable Non Clustered Index is mentioned bellow.

1.    The Query Optimizer cannot use the Index.

2.    If we have a Index hint in the SELECT statement.

For example:

SELECT *

FROM <Table_Name> WITH(INDEX(Index_Name));

is going to be Failed.

Disabling Clustered Index:

When we Disable a Clustered Index the Data in the Table remain Exists but not accessible. Only Drop and Rebuild option can access the data.

Effect of Disable Non Clustered Index is mentioned bellow.

1.    All the Non Clustered Index and Views are not available.

2.    We cannot use any DML action on the Table where Clustered Index is Disable.

To Re-Enable the Index

ALTER INDEX <Index_Name>

ON <Schema_Name>.<Table_Name> REBUID;

What Happened When DROP Index Occurs

To DROP an Index we used

DROP INDEX <Index_Name>

ON <Schema_Name>.<Table_Name> REBUID;

Dropping Index is really a bad idea. When we DROP an Index, the metadata, statistics, and index pages are removed. If we drop a Clustered Index, the table will become a heap.

Effect of Dropping Index is mentioned bellow.

1.    Dropping a Clustered Index make the table a Heap.

2.    After Drop an Index we cannot use REBUILD options. We must recreate the Index again. So we need the Script of Index definition to Re Create it again.

So Which one to select DROP or DISABLE

First of all DROP a Clustered Index is really a bad idea. It generate Heap. By Clustered Index disable caused DML action is not working on Table.

As per my suggestion Clustered Index is created when the table is designed and do not disturb a clustered index is a good idea.

So we have to think about Non Clustered Index. Whether we DROP it or DISABLE it.

DROP a non clustered Index, need Script or Index definition to recreate it. Therefore, rather than having to script out the drop and create statements, we just disable the relevant indexes, and then issue an ALTER INDEX REBUILD for those indexes when we finished with our modifications. Not only is this a great time saver, but I get the added benefit of still saving disk space.

When an index is rebuilt we need enough disk space to store the old and new copy of the index. When an index is dropped and then recreated, the creation can use the disk space originally used for the index. The only additional space needed is for the sorting, which is about 20% of the disk size, and we can avoid using my data file for the sort if I turn on the SORT_IN_TEMPDB option.

CREATE NONCLUSTERED INDEX <Index_Name>

ON <Schema_Name>.<Table_Name>

(

   <Col_Name>

)

WITH

(

  PAD_INDEX = OFF,

  STATISTICS_NORECOMPUTE = OFF,

  SORT_IN_TEMPDB = ON,

  IGNORE_DUP_KEY = OFF,

  ONLINE = OFF,

  ALLOW_ROW_LOCKS = ON,

  ALLOW_PAGE_LOCKS = ON

);

Hope you like it.

Posted by: MR. JOYDEEP DAS

Is there any differences between Table Scan and Index Scan

Introduction

As we all know that the developer prefers the Index Seek in the case of performance of a query and we all know that what is Index Seek and who it improve the performance. In this article we are not going to discuss about Index Seek.

A table scan is performed on a table which does not have an Index upon it (a heap) – it looks at the rows in the table and an Index Scan is performed on an indexed table – the index itself.

Here we are trying discuss about a specified scenario occurs related to Table Scan and Index Scan.

Scenario Description

Suppose we have a table object without any Index on it. The name of the Table Object is tbl_WithoutIndex and another Table Object called tbl_WuthIndex

-- Heap Table Defination

IF OBJECT_ID(N'dbo.tbl_WithoutIndex', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_WithoutIndex];

   END

GO

CREATE TABLE [dbo].[tbl_WithoutIndex]

       (

         EMPID    INT         NOT NULL,

         EMPNAME  VARCHAR(50) NOT NULL

       );

GO

-- Insert Some Records

INSERT INTO  [dbo].[tbl_WithoutIndex]

       (EMPID, EMPNAME)

VALUES (101, 'Joydeep Das'),

       (102, 'Sukamal Jana'),

       (103, 'Ranajit Shinah');

GO      

-- Table with Index (Clustered Index for primary Key)                        

IF OBJECT_ID(N'dbo.tbl_WithIndex', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_WithIndex];

   END

GO

CREATE TABLE [dbo].[tbl_WithIndex]

       (

         EMPID    INT         NOT NULL PRIMARY KEY,

         EMPNAME  VARCHAR(50) NOT NULL

       );

GO

-- Finding Index Name

sp_helpindex tbl_WithIndex;

index_name	index_description	index_keys
PK__tbl_With__14CCD97D3AD6B8E2	clustered, unique, primary key located on PRIMARY	EMPID

-- Insert Some Records

INSERT INTO  [dbo].[tbl_WithIndex]

       (EMPID, EMPNAME)

VALUES (101, 'Joydeep Das'),

       (102, 'Sukamal Jana'),

       (103, 'Ranajit Shinah');

GO

Now we are going to compare the execution plan output

SELECT * FROM [dbo].[tbl_WithoutIndex];      

As we can see in the Execution plan, there is Table Scan. As the table has no Index (Here I mean the clustered Index) the table is a Heap. So when we put the SELECT statement the entire table scanned.

SELECT * FROM [dbo].[tbl_WithIndex];

Here he Table has a PRIMARY KEY, so it has a Clustered Index on it. But here we are not putting the Index columns on the WHERE clause, so the Clustered Index Scan Occurs.

Close Observation of Execution Plan

Please remember that the table has small number of records.

Table Name	Estimated IO Cost
tbl_WithoutIndex	0.0032035
Tbl_WithIndex	0.003125

If we see the Estimated Operation Cost, it would be same for both the Query (0.0032853).

Question in Mind

Here we can see the Estimated Operation cost for both the Query is same. So Question is in the mind that, if a Index Scan occur we can drop the index and use the heap (in our case). So is there any other difference between them.

How they are Differences

Here we understand what the internal difference between Table Scan and Index Scan.

When the table scan occurs MS SQL server reads all the Rows and Columns into memory. When the Index Scan occurs, it's going to read all the Rows and only the Columns in the index.

Effects in Performance

In case of performance the Table Scan and Index Scan both have the same output, if we have use the single table SELECT statement. But it differs in the case of Index Scan when we are going to JOIN table.

Hope you like it.

Posted by: MR. JOYDEEP DAS

DO.. WHILW and REPEAT… UNTIL loop in MS SQL

Introduction

When I am looking for a forum post related to SQL Server, one of the junior professional is asking for DO…WHILE loop is MS SQL Server. Several people wrote their opinion related to it. Every one is saying to use WHILE loop and some of them suggesting with T-SQL structure of CURSOR with WHILE LOOP.

Obviously when a junior professional is learning MS SQL server, the question in mind arise is there DO… WHILE, REPEIT … UNTILL loop present in MS SQL Server Like C or C++ etc.

No one is answering directly on the forum that is we can use DO… WHILE or REPEIT … UNTILL in MS SQL Server or NOT. If yeas how we can implement them.

DO… WHILE in MS SQL Sever

First we look at the algorithm of DO… WHILE.

SET X = 1

DO

  PRINT X

  SET X = X + 1

WHILE X <= 10

Now we try to implement it in MS SQL Server.

DECLARE @X INT=1;

WAY:     --> Here the  DO statement

  PRINT @X;

  SET @X += 1;

IF @X<=10 GOTO WAY; --> Here the WHILE @X<=1

REPEAT… UNTIL

First we look at the algorithm of REPEAT... UNTIL

SET X = 1

REPEAT

  PRINT X

  SET X = X + 1

UNTIL X > 10

Now we try to implement it in MS SQL Server

DECLARE @X INT = 1;

WAY:        -- Here the REPEAT statement

  PRINT @X;

  SET @X += 1;

IF NOT(@X >1 0) GOTO WAY; -- Here the  UNTIL @X>10

So we see that it is possible, but little complicated… So most of the developer prefers the WHILE loop in MS SQL Server.

Posted by: MR. JOYDEEP DAS

Some Common Query related to FILTERED INDEX

Introduction

The Filtered Index is introduced at MS SQL Server 2008. Here in this article we are not directly discuss the Filtered Index, but solve some common query in mind Related to Filter Index.

An Example of Filtered Index

-- Base table

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

     DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

  (EMPID   INT         NOT NULL IDENTITY PRIMARY KEY,

   EMPNAME VARCHAR(50) NOT NULL,

   DOB     DATE        NOT NULL,

   GRADE   CHAR(1)     NOT NULL);

GO

-- Inserting Records

INSERT INTO [dbo].[tbl_EMPLOYEE]

    (EMPNAME, DOB, GRADE)

VALUES('Joydeep Das', '1974-12-17', 'A'),

      ('Jukamal Jana','1974-10-11', 'A'),

      ('Chandan Bannerjee','1973-06-09', 'B'),

      ('Soumen Bhomik', '2008-11-28', 'C');

GO

     

-- Create Filtered Index

CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE

ON [dbo].[tbl_EMPLOYEE](GRADE)

WHERE GRADE = 'A';     

Now we are trying to solve some Query

Query – 1

What Type of Comparison operator used in Filter Criteria in Filtered Index

Comparison Operator

=

!=

<

<=

>

>=

IS NULL

IS NOT NULL

Query – 2

Is BETWEEN Clause is used in Filter Criteria of Filtered Index

NO. The BETWEEN Clause is not used in Filter Criteria of FILTERED Index. We can use >= and <= to get the output like BETWEEN Clause.

CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE

ON [dbo].[tbl_EMPLOYEE](GRADE)

WHERE DOB >= '1974-01-01' AND DOB <= '1974-12-31';

Query – 3

Is it possible to add more data to Filtered Index without dropping and re-creating it

Yes it is possible.

Query – 4

Can we use the SUB QUERY in the Filtered Index WHERE Clause like

WHERE Grate=(SELECT grade FROM tbal_2)

NO it is not Possible. WHERE Clause of Filtered Index always needs a Scalar value.

As per MSDN  

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Under the "WHERE" section of the Arguments heading:

The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

If you have any question in mind related to FILTERED INDEX please make some comments to solve it.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Index Page level Locking is Disable ERROR

Introduction

When we are working with production database, sometime we find a specific message in maintenance plan log.

The index [IndexName] on table [TableName]

cannot be reorganized because page level locking is disabled. 

This will cause our Index Reorganize steps fails. In this article we are trying to explain it and solve it.

Why this Errors Occurs

By default the page level locks should be enabled for Index. The main causes of this error are the Index Reorganize step has hits an Index that have page lock disables. This somehow your Index Page Lock is disabled.

How to Solve it

Finding the Table Name and Index Name where Page Lock is Disable

SELECT T.Name AS [Table Name], I.Name As [Index Name]

FROM   sys.indexes I

       LEFT OUTER JOIN sys.tables T

       ON I.object_id = t.object_id

WHERE  I.allow_page_locks = 0

       AND  T.Name IS NOT NULL;

Now Use Alter Statement to Allow Page Lock On

The syntax is mentioned bellow

ALTER INDEX <Index_Name> ON <Table_Name>

SET (ALLOW_PAGE_LOCKS = ON);

To generate the Alter Script Automatically

SELECT 'ALTER INDEX ' + I.Name + ' ON ' +  T.Name +

       ' SET (ALLOW_PAGE_LOCKS = ON)' As Command

FROM   sys.indexes I

       LEFT OUTER JOIN sys.tables T

       ON I.object_id = t.object_id

WHERE  I.allow_page_locks = 0

       AND T.Name IS NOT NULL;

Hope you like it.

Posted by: MR. JOYDEEP DAS

INDEX not implemented when a Materialized view is created from another

Introduction

There is a limitation of view that we must understand is if a materialized view is created by another materialized view, in this case we cannot configured index on Second Materialized view

Please try to understand the bellow pictorial diagram.

Materialized View 1  à

                              Used to Create  à  

                                                Materialized View 2  ß Cannot Configure Index

This article is related to it.

Example to understand

Step - 1 [ Create the Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

       (

         EMPID    INT         NOT NULL,

         EMPNAME  VARCHAR(50) NOT NULL,

         EMPGRADE CHAR(1)

       );

GO

Step – 2 [ Insert Some Records ]

INSERT INTO  [dbo].[tbl_EMPLOYEE]

      (EMPID, EMPNAME, EMPGRADE)

VALUES(1, 'Joydeep Das', 'A'),

      (2, 'Sukamal Jana', 'A'),

      (3, 'Sangram jit', 'B'),

      (4, 'Souman Bhowmik', 'C');

GO

Step – 3 [ Create First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_1', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_1];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_1]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[tbl_EMPLOYEE];

GO 

Step – 4 [ Create Second VIEW by Using First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_2', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_2];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_2]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[view_EMPLOYEE_1];

GO

Step – 5 [ Creating the UNIQUE CLUSTERED Index on Second VIEW and Error occurs]

CREATE UNIQUE CLUSTERED INDEX IX_view_EMPLOYEE_2

ON [dbo].[view_EMPLOYEE_2](EMPID);

Msg 1937, Level 16, State 1, Line 2

Cannot create index on view 'MATRIXSYSDB.dbo.view_EMPLOYEE_2'

because it references another view 'dbo.view_EMPLOYEE_1'.

Consider expanding referenced view's

definition by hand in indexed view definition.

Reason for That

The reason for this is that another view over a view is difficult to maintain

What to do to solve it

Use the SELECT statement of first view within the second view.

Hope you like it.

Posted by: MR. JOYDEEP DAS