Category Archives: Indexes

Analyze Indexes – with or without them?

SQL Indexes – what a great subject to discuss on! I just love them. It is incredible to find out each time, what a proper index can do to your database…

As far as I can think of there are 3 issues related to Indexes:

  1. Fragmentation
  2. Query Optimization
  3. Storage problems – I already blogged about it and if you follow the link you will see the whole story.

I. Fragmentation

Inside each DBA’s maintenance tasks, should be included a step which will collect fragmentation on all Production most critical databases.
You can schedule the Index part once per month, or as frequent as you consider necessary.

The SQL query is rather simple:

  1. Create a table with structure:
     

    use tempdb
    go
    CREATE TABLE #indexes (
       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)
    
  2. Open a cursor that contains 2 parameters: schema name and table name selected from INFORMATION_SCHEMA.TABLES and execute for each:
     

     
     INSERT INTO #indexes
     EXEC ('DBCC SHOWCONTIG ('schemaname.tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    
  3. The execution output will be stored in a table with same structure as the output’s columns, used for future analysis.
    I only focus on Indexes that have a fragmentation higher than 5% and have more than 10000 pages. In theory, there is no point in rebuilding an index that occupies less than 24 pages. In practice, it’s generally recommended not to do anything for indexes with less than 10000 pages.
    Next step:
     

    delete from #indexes where LogicalFrag <= 5 or CountPages <= 10000
    

The list resulted can be split in two action strategies:

  • Reorganize Indexes that have a fragmentation rate > 5% and < 30%
  • Rebuild Indexes that have a fragmentation rate > 30%

 
Check this also for more info on fill factor importance and Alter Index syntaxes:

Managing SQL Server Database Fragmentation
How to check Fragmentation on SQL Server 2005


II. Query Optimization

I have been asked so many times, questions like: "Why this procedure is running so slowly?" or "why so many blockages?", "I have a new script and it takes ages until it is completed!" and I can go on. Almost every time, in a bigger or a smaller part - a lack or surplus of one/many indexes is involved.

How to know if you have unused indexes

Indexes in surplus - causes SQL to do as many index updates as many indexes are there on a table that include the same column which is modified in a single statement.
I saw many cases with tables having tens of indexes. Some of them were doubled and some of them were created just following SQL hints from SQL Query Optimizer. There is more damage than good, when you follow literary SQL's hints. First of all, multiple updates on those many indexes which slow down the query, secondarily index disk usage which can get substantial.

To understand which indexes are really unused and should be dropped at next maintenance window, SQL Server must not be restarted for some time (1 month - 6 months), considering that it will have exhausted all possible recurrent user queries, recurrent stored procedures, application transactions that run on it. This is because SQL will hold the statistics until next reboot. In order to have valuable statistics the SQL should have run for an enough period of time.

  1. Look at existing Indexes in your database:

    The script will avoid Primary Keys, Unique keys and already Disabled indexes.

    use your_database
    go
    select i.name index_name, o.name [object_name], i.type_desc, o.type_desc , i.is_disabled, i.is_unique
    from sys.indexes i inner join sys.objects o on 
    i.[object_id] = o.[object_id] where o.type_desc = 'USER_TABLE' and i.name is not null 
    and i.is_primary_key = 0 and i.is_disabled = 0 and i.is_unique = 0
    order by o.name
    
  2. Check how many seeks/scans are done on above extracted indexes. You can filter the query by specifying one or more object names and index names. This select will display also each table's row count.

    Example 1: you see a number of 1867777 scans on a 2000000 rows table and close to 0 seeks. Big problem. It means the index is not well designed. It is used but not as it should be.
    Example 2: you see a number close to 0 both for seeks and scans columns. Why? The index has never been used since SQL was restarted.

    In conclusion, seeks column should filled with numbers. If both seeks and scans have numbers, the indexes should be left there. user lookups are used mostly by primary keys or clustered indexes, so it is unlikely you will something there.

    use your_database 
    go
    IF (object_id('tempdb..#temp') IS NOT Null)
       DROP TABLE #temp   
    
    CREATE TABLE #temp (
    table_name sysname ,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50))
    SET NOCOUNT ON
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    
    
    SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],S.[OBJECT_ID],
             I.[NAME] AS [INDEX NAME],
             USER_SEEKS,
             USER_SCANS,
             USER_LOOKUPS,
             USER_UPDATES, t1.row_count
    FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
             INNER JOIN SYS.INDEXES AS I
               ON I.[OBJECT_ID] = S.[OBJECT_ID]
                  AND I.INDEX_ID = S.INDEX_ID
                  inner join #temp t1 on S.object_id = object_id (t1.table_name)
                  
    WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1  
    and is_disabled = 0 and is_unique = 0 and i.name is not null -- and user_seeks =0
    and  OBJECT_NAME(s.[OBJECT_ID]) in ('object1') 
    and I.[name] IN ('index1', 'index2','index3')
    order by  OBJECT_NAME(s.[OBJECT_ID]) ,i.name
    
  3. Check leaf updates, inserts and deletes on indexes identified in previous step with zero scans/seeks.
    This is the ultimate proof that an index which is not used in optimization, is used to be updated at insert/update/delete operations --> bad performance.

    use your_database 
    go
    SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
           I.[NAME] AS [INDEX NAME],
           A.LEAF_INSERT_COUNT,
           A.LEAF_UPDATE_COUNT,
           A.LEAF_DELETE_COUNT
    FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
           INNER JOIN SYS.INDEXES AS I
             ON I.[OBJECT_ID] = A.[OBJECT_ID]
                AND I.INDEX_ID = A.INDEX_ID
    WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1 
    and OBJECT_NAME(A.[OBJECT_ID]) in ('object1') 
    
    order by  I.[NAME]
    
  4. At last, identify Indexes that don't do any harm, but overcrowd the Indexes list and use space, these are indexes completely useless and should be dropped.
     

    DECLARE @dbid INT
    SELECT @dbid = DB_ID(DB_NAME())
    SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID, i.object_id
    FROM SYS.INDEXES I
    JOIN SYS.OBJECTS O
    ON I.OBJECT_ID = O.OBJECT_ID
    WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
    AND I.INDEX_ID NOT IN (
    SELECT S.INDEX_ID
    FROM SYS.DM_DB_INDEX_USAGE_STATS S
    WHERE S.OBJECT_ID = I.OBJECT_ID
    AND I.INDEX_ID = S.INDEX_ID
    AND DATABASE_ID = @dbid) and I.NAME is not null 
    and is_unique =0 and is_disabled = 0
    ORDER BY OBJECTNAME,
    I.INDEX_ID,
    INDEXNAME ASC
    GO
    

How to force SQL to use ad-hoc created Indexes on #temp tables

Inside a stored procedure, one simple and common way of querying/modifying some production data and avoiding unnecessary locks on production tables is to use temporary tables. These tables can be queried/modified and joined with base tables without creating blockages; resulting information can be manipulated easier and faster.

There is a problem though, when working with temporary tables. I am speaking about tables created with # in front which are removed at each session’s closure and only those that are part of a stored procedure code.

If you use a certain temporary table in a join operation with another table, and you have many rows implicated – than you would probably see a performance issue, since you don’t have an index covering both joining columns.
This behavior is also available in order by statements or filter clauses (where involved).

You will see table scans on #(temporary) table and the performance will be very poor. The next logical step is to create the proper indexes right after the table creation.

You are absolutely right to do so, but SQL will ignore this index and exclude it from the Execution Plan, because the Execution Plan was already created when the index didn’t exist. When a stored procedure is called, it is first compiled and Query Optimizer creates an Execution Plan for it. It does not take into consideration the indexes created on temporary tables.

In order to achieve this you can do this by following one of the below methods:

  • Create Primary/Unique keys when creating the temporary table. – this is only efficient if you have unique columns involved in order by or filtering options.
  • Specify the table hint: with (index(name_of_the_index)) right after mentioning the table in a select statement.
     

    Example: Select name from #table with (index (index_id)) where id>1000

RangeX-X Key-Range Locks in SERIALIZABLE isolation level (part 3)

I will continue in this part describing other types of Key – Range locks: RangeX-X combined with RangeS-U locks.

As a reference I will use the same table created in part 1 – range_lock.

RangeX-X Locks

RangeX-X locks are exclusive resource locks acquired on Index key values from a specific range when the transaction modifies a key value from the Index. The locks are exclusive locks meaning, no other type of transaction (Select/Update/Insert/Delete) can be executed until the first transaction is completed/commited on the range key values modified.

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
begin transaction
go

update range_lock set rname ='ana' where rname = 'anna' 


Analyze the Query Execution Plan

RangeX-X Execution Plan

RangeX-X Execution Plan

 
 
 
 
 
 
You will see a Clustered Index Update, and an Index seek on ix_rname (rname column) – this defines the range of the key values to seek in.
At first look, the execution plan looks exactly as it did on RangeS-U locks – with a slight difference on the operator cost (%): 75% on RangeS-U and 86% on RangeX-X. Why? If I mouse over the Clustered Index Update operation I see 2 indexes listed for Update operation: Primary Key and ix_rname index.

RangeX-X Execution plan - highlight Index Update operation

RangeX-X Execution plan - highlight Index Update operation


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Both Indexes are planned for update because:

  • PK_range_locks – in this case is a Clustered Index that orders the data rows in the table based on its (Primary Key) key values. The update is made on another column than the defined Primary key column. All data rows from a table are called the leaf level of the index. The Primary Key values contain pointers to all data rows from the table. Any update on another column than the primary key will result to an update on the leaf level of the Clustered Index.
  • ix_rname – Update is made on a key value from the Index, so the index should be updated with a different value.

Check Held Locks

Execute:

sp_lock 55

Here is what you would get:

RangeX-X for equality operation

RangeX-X for equality operation

I see that one Exclusive lock (X) is held on IndId=1 (Primary Key) which is needed for the Index update, and I see that RangeX-X lock is acquired on IndId = 2 (ix_rname) on one key value. Also RangeS-U is acquired on IndId = 2 (ix_rname) on one key value. If I go back in part 1, and take a look on table rows and their order, I will see that after “anna” is “antony” row. I can take a smal guess that the RangeS-U lock is on the next key value after “anna” – “antony”. How can I prove that?
 
 
 
Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

begin transaction 

select rname from range_lock where 	
rname = 'antony' 

This statement will work fine. RangeS-U locks allows Select statements to be executed. Rollback this transaction.

Inside the same session execute:

begin transaction 

update range_lock set rname = 'antoni' where
rname = 'antony' 

You will see that this transaction will be blocked by the first one.

Session 58 is blocked by session 55

Session 58 is blocked by session 55


 
 
 
 

Check Locked resources on both transactions

To figure out why the update operation on rname column for the “antony” row is blocked by the first update on rname column for the “anna” rows check this out:

Execute:

sp_lock 55 
go
sp_lock 58
go
RangeX-X for equality operation

RangeX-X for equality operation

I see one WAIT type of lock for the second transaction on same resource the first transaction has put RangeS-U lock. It is clear that we are speaking about same row: “antony”.

Microsoft explains WAIT locks as:

WAIT – Lock is blocked by another process.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
The conclusion I get is: RangeX-X locks will only be acquired on those columns that are actually modified from the range. Even if the range contains the “next” value from the table list of rows, RangeS-U locks will be held on that value (s), in order to maintain the integrity of data until the transaction is commited.

 

RangeS-U – Key-Range Locks in SERIALIZABLE isolation level – (part 2)

I will continue in this part describing other types of Key – Range locks.

As a reference I will use the same table created in part 1 – range_lock.

RangeS-U Locks

RangeS-U locks are locks acquired on Index key values from a specific range when the following conditions are met:

  • The transaction contains an UPDATE statement
  • WHERE clause is used to specify a condition that limit the rows that are updated. The condition will include at least one column from the Index. This will define the range of key value(s) from the Index. In this case, locks are needed on defining key value(s) that refer to the rows updated.
  • The UPDATE modifies a column(s) which is(are) not contained in the Index definition or:
  • The UPDATE modifies a column(s) which is(are) contained in the Index definition.

I hope from the below example the explanation will more straightforward. I will use a larger range of values in this example, meaning I will use an inequality operation. The equality operation will have the same types of locks.

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
begin transaction
go

update range_lock set sname ='surname' where rname between 'anna' and 'arlen'

Analyze the Query Execution Plan

RangesS-U - Execution Plan

RangesS-U - Execution Plan

 
 
 
 
 
 
You will see a Clustered Index Update, and an Index seek on ix_rname (rname column) – this defines the range of the key values to seek in.

The update on PK_range_locks happens because in this case the PK is a Clustered Index that orders the data rows in the table based on its (Primary Key) key values. The update is made on another column than the defined Primary key column. All data rows from a table are called the leaf level of the index. The Primary Key values contain pointers to all data rows from the table. Any update on another column than the primary key will result to an update on the leaf level of the Clustered Index.

Check Held Locks

Execute:

sp_lock 55

Here is what you would get:

RangeS-U for inequality operation

RangeS-U for inequality operation

I see 3 Exclusive locks (X) are held on IndId=1 (Primary Key) which are needed for the Clustered Index update, and I see that RangeS-U locks are acquired on IndId = 2 (ix_rname) on four key values. If I go back in part 1, and take a look on table rows and their order, I will see that between “anna” and “arlen” are 3 rows, not 4. The fourth lock is on the next key value after “arlen” – “barry”.
 
 
 
 
 
 
 
 
As the name suggests Shared – Update locks mean that:

  • Other transactions that need to Select the same rows inside the range will be permitted.
  • Other transactions that perform an update on any of the key values from the range – will be blocked by the first one until the first one is commited or rollbacked.

Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

begin transaction 

select rname from range_lock where 	
rname = 'barry' 

This statement will work fine. Rollback this transaction.

Inside the same session execute:

begin transaction 

update range_lock set rname = 'barri' where
rname = 'barry'  

You will see that this transaction will be blocked by the first one.

Session 58 is blocked by session 55

Session 58 is blocked by session 55


 
 
 
 

Check Locked resources on both transactions

To figure out why the update operation on rname column for the “barry” row is blocked by the first update on sname column for the rows between “anna” and “arlen” check this out:

Execute:

sp_lock 55 
go
sp_lock 58
go
RangeS-U for inequality operation

RangeS-U for inequality operation

I see one WAIT type of lock for the second transaction on same resource the first transaction has put RangeS-U lock. It is clear that we are speaking about same row: “barry”.

Microsoft explains WAIT locks as:

WAIT – Lock is blocked by another process.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
This is all about RangeS-U locks and further in part 3, I will detail RangeX-X locks and RangeX-X combined with Range S-U locks.

RangeS-S – Key-Range Locks in SERIALIZABLE isolation level (part 1)

Overview
 

Locking mechanism in SQL is by far one of the most complex mechanism and hard to explain. Blockages and Locks are a DBA’s daily problems when confronting with concurrent processes which access same records of the same table. In order to have a high – performance system, a very important aspect to consider is isolation of concurrent operations. In high-activity systems like web-sites, ERP’s or online shops there is a huge amount of concurrent transactions that select data from same tables, modify data and insert new data. In order to isolate some processes, the isolation level of transactions must be changed. The Transaction Isolation Level controls the default transaction locking behavior for all SQL statements issued by a connection.

READ COMMITTED is the default level of isolation which is also the most recommended for most processes. But this level is not always enough in order to have an ACID transaction because the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. In order to isolate completely a Transaction which has a BEGIN TRANSACTION in front – you can SET TRANSACTION ISOLATION LEVEL to SERIALIZABLE inside a stored procedure. This does not affect other transactions and their isolation level.
Also, If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control, the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

SERIALIZABLE isolation level is equivalent HOLDLOCK hint and has the same behavior when dealing with locks.

When SERIALIZABLE isolation level is set – a range lock is placed on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. And also… it is the less explained on WWW. This is mainly the reason I want to write about the Locking types inside SERIALIZABLE transaction.

Key-Range Lock Modes
 

In serializable isolation level Key – Range locks are used. Serializability implies that any query executed inside a transaction must obtain the same set of data from the beginning until the end of the transaction. The set of data locked inside the serializable transaction is based on a range of key values of the index that covers the rows extracted. It is assured that the values from the range of values locked will not be modified or new values will not be inserted for the same range of values by other concurrent transactions. Any attempt of a second transaction to insert, update, or delete any row within the same range as the first one requires a modification to the index. Thus, the second transaction is blocked until the first transaction completes because key-range locks cover the index entries.

There are 4 types of locks:

  1. RangeS-S – Shared range, shared resource lock; serializable range scan.
  2. RangeS-U – Shared range, update resource lock; serializable update scan.
  3. RangeX-X – Exclusive range, exclusive resource lock; used when updating a key in a range.
  4. RangeI-N – Insert range, null resource lock; used to test ranges before inserting a new key into an index.

There are couple of basic things to know about key-range locking which are not mentioned in MSDN documentation:

  1. In equality operations (“=”, “IN”) – Key-Range locks are not held on key values that are found inside an Unique Index even if the transaction is issued in Serializable isolation level.

    Example:
    If the ix_range index created on range_lock table would be Unique, the following statement would not require Key-Range locks.

    Shared locks will be enough.

    select rname from range_locks where rname = 'anna' 
    
  2. In equality operations (“=”, “IN”) – Key-Range locks are held on “next” key values that are not found inside an Unique or Non-Unique Index.

    Example:
    Whether ix_range index created on range_lock table is Unique or Non-Unique, the following statement will hold Key-Range locks on the “next” value of the key value from the equality operation that was not found inside the index.

    In this example “annabella” field does not exist so, Key-Range locks will be put on next value from the list: “antony”.

    select rname from range_locks where rname = 'annabella' 
    
  3. In in-equality operations (“>”, “<", BETWEEN, LIKE, "<>“) – Key-Range locks are held on all key values (found) from the range specified and the “next” value. This is true for both Unique or Non-Unique Indexes.

    Example:
    Whether ix_range index created on range_lock table is Unique or Non-Unique, the following statement will hold Key – Range locks on all key values in the range when using “BETWEEN” and on the “next” value of the key value that is outside the range.

    In below example RangeS-S locks will be acquired on all values between “antony” and “barry”.
    “annabella” is missing – so the starting value of the range will be considered the next value in alphabetical order after “annabella”. Also an additional RangeS-S lock will be put on the value after “barry”: “benedict”.

    select rname from range_locks where rname between 'annabella' and 'barry'
    

RangeS-S Locks
 

I will explain through examples each type of locking. In this part I will go through Key – Range Shared – Shared locks. In order to not repeat on following parts I will write only in this part the prerequisites for my tests.
I need to create a table and insert few rows into it. The table will have a Primary Clustered Key and an Index Non-Clustered on one of the three columns.

Here you go:

create table range_lock 
(rid int NOT NULL IDENTITY (1, 1),
rname nvarchar (20),
sname nvarchar (20))

ALTER TABLE range_lock ADD PRIMARY KEY (rid);

CREATE NONCLUSTERED INDEX [ix_rname] ON [dbo].[range_lock] 
(
[rname] ASC
)

Populate the table with names. The rows will be displayed in alphabetical order due to the ix_rname index.

INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('anna')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('antony')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('angel')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('ARLEN')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BARRY')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BENEDICT')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BILL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BRYCE')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CAROL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CEDRIC')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CLINT')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('DARELL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('DAVID')

A simple select in the range_lock table will show the inserted values ordered by rname column ascending.

Rows ordered in ascending order by the rname column.


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
Begin Transaction
go
select rname from range_lock where rname = 'anna'

Analyze the Query Execution Plan

RangeS-S locks - Execution Plan

RangeS-S locks - Execution Plan


 
 
 
 
 
 
 
 
You will see an Index Seek on ix_rname index because the ix_rname contains the rname column needed in the SELECT. Index Seek only touches only the rows that qualify.

IMPORTANT!!!

If I would issue a SELECT on all table columns, then the Query Optimizer will perform an Index Scan on the Primary Key in order to retrieve rid and sname values position from the Clustered Index. This is not optimal and must be avoided. So to avoid this, the rid and sname columns should be added as included columns inside ix_rname index.

Check Held Locks

Execute:

sp_lock 55

Here is what you would get:

RangeS-S for equality operation - Non-Unique Index

It is obvious that RangeS-S locks are acquired on IndId = 2 (ix_rname) for two key values.

Remember what I wrote earlier about equality operations and Unique Indexes – that Range locks are not held? Well, here we deal with a Non-unique Index so, it is quite normal that RangeS-S locks are held. The question that pops in, is why two RangeS-S locks are acquired when the statement only retrieves data about “anna”?
That is because Range locks are acquired on the key value from the “=” operator and on the next value. This is to ensure that no row can be inserted updated or deleted between the requested key and the one after that. It means that “anna” and “antony” rows would become locked if some other transactions tried to modify the fields. Let’s see that.

If you look above in the range_lock table content you will see that after “anna” comes “antony”. Further I will try to delete the “antony” row inside another transaction from the default isolation level and see what happens.

Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

begin transaction
go
delete from range_lock where rname ='antony'

The second transaction will wait for the first one to be committed.
 
Execute sp_who2 active to make sure session 58 is blocked by session 55.

58 blocked by 55


 
 
 
 

Check Locked resources on both transactions

To figure out why the delete operation on “antony” row is blocked by the first select on “anna” row check this out:

Execute:

sp_lock 55 
go
sp_lock 58
go

RangeS-S and CNVT locks


 

I see one CNVT type of lock for the second transaction on same resource the first transaction has put RangeS-S lock. It is clear that we are speaking about same row: “antony”.

Microsoft explains CNVT locks as:

CNVT – Lock is being converted to another lock. A lock being converted to another lock is held in one mode but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to a WAIT.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
This is all about RangeS-S locks and further in part 2 and 3, I will detail more complex locks such as RangeS-U and RangeX-X.

Check Tables & Indexes size for your Database

Before accepting that your database is big because it needs all the data inside it and buy extra storage to overcome the potential space problem, you can find out if the existing space can be reduced by removing unused objects, indexes.

First find out what are the biggest tables:

 


DECLARE @query varchar(100)
    DECLARE @schema as sysname
    DECLARE @objectname as sysname
    
    IF (object_id('tempdb..#Table_Size') IS NOT Null)
    drop table [dbo].[#Table_Size]
    CREATE TABLE #Table_Size 
      ( TableName varchar(50),
        [RowCount] int,
        Table_Size varchar(50),
        Data_Space_Used varchar(50),
        Index_Space_Used varchar(50),
        Unused_Space varchar(50)
     )
    
    DECLARE Object_Cursor CURSOR FOR
    
    SELECT s.name, o.name  FROM sysobjects o inner join sys.schemas s ON o.uid = s.schema_id
    WHERE xtype='U' 
    
    OPEN Object_Cursor
    FETCH NEXT FROM Object_Cursor 
    INTO  @schema, @objectname
    
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    
    set @query = 'sp_spaceused ''[' + @schema + '].[' + @objectname + ']'''
    
    INSERT INTO #Table_Size EXEC(@query)
    
    FETCH NEXT FROM Object_Cursor 
    INTO   @schema, @objectname
    
    END
    
    CLOSE Object_Cursor
    
    DEALLOCATE Object_Cursor
    
    select TableName,[RowCount],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2))/1024 [Table_Size (MB)],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Data_Space_Used)),len(ltrim(rtrim(Data_Space_Used)))-2))/1024 [Data_Space_Used (MB)],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Index_Space_Used)),len(ltrim(rtrim(Index_Space_Used)))-2))/1024 [Index_Space_Used (MB)],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Unused_Space)),len(ltrim(rtrim(Unused_Space)))-2))/1024 [Unused_Space (MB)]
    
    from #Table_Size
    
    order by convert(int ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2))  desc

After you have a list of biggest tables, check their Indexes. During my DBA work I have found many times unused indexes, doubled with same definition which not only occupied the disk space for nothing but also made worse the performance of any DML query as these should have been updated. So for a single Insert imagine updating 4 indexes. Sometimes these can be re-designed.

 

DECLARE @query varchar(100)
    DECLARE @index_id INT
    DECLARE @index_size BIGINT SET @index_size = 0
    DECLARE @name as NVARCHAR(500)
    DECLARE @indextype as NVARCHAR(256)
    DECLARE @TableName AS NVARCHAR(256)
    
    SET @TableName = 'TABLENAME'
    
    
    IF (object_id('tempdb..#Index_Table') IS NOT Null)
    drop table [dbo].[#Index_Table]
    
    CREATE TABLE #Index_Table 
     (	Table_Name		 varchar(250),
    	Index_ID		 varchar(250),
     	Index_Name		 varchar(250),
     	IndexType		 varchar(250),	
     	Index_Size_MB	 INT
     )
    
    DECLARE Index_Table CURSOR FOR
    
    SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) 
    
    OPEN Index_Table
    FETCH NEXT FROM Index_Table 
    INTO  @index_id, @name
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    
    SELECT @index_size = @index_size + (avg_record_size_in_bytes * record_count)
    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')
    
    set @index_size = @index_size / 1048576
    select @indextype = (index_type_desc) 
    from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')
    
    insert into #Index_Table values (@TableName,  @index_id,  @name, @indextype, convert(int ,@index_size))
    
    FETCH NEXT FROM Index_Table
    INTO   @index_id, @name
    END
    CLOSE Index_Table
    
    DEALLOCATE Index_Table
    
    select * from  #Index_Table order by Index_Size_MB desc

Reindex on Mirrored Databases

The basic Index Rebuild or DBCC Reindex has a different impact on mirrored databases and can cause you more problems than you think.

Before starting the maintenance on Indexes check if:

  • Mirroring is set to high – safety or high – performance.
  • Space available on both servers (Principal/Mirror) for Log Files disk.
  • Space available on both servers (Principal/Mirror) for Data Files disk.
  • Space available on both servers (Principal/Mirror) for tempdb database.

1. Change Mirror Operating mode:
 
Now, if your databases are set in High – Safety operationg mode, change the setting in High – Performance. It will allow Principal server to perform the Reindex and not consider the answer from Mirror. Your goal is to minimize the downtime on Principal server. The Mirror server will catch up with Log restoration when the transaction will be sent to Mirror server not before committing on Principal.
 
2. Online vs. Offline Rebuild:
 
If you perform ALTER INDEX REBUILD with ONLINE option make sure you have the size of your indexes planned for Rebuild available on Data and Log Files disk, because the SQL will first create the NEW index allowing other queries to be run against the table using OLD index. Until the switch is done and OLD index dropped, 2 indexes will co-exist.

OFFLINE Rebuild will last 1,2 times less and use less disk space because the OLD index is dropped first and re-created from scratch. All activity on the table is exclusively locked.

Check the indexes size for a specific database:SELECT * INTO master.dbo.TEMPINDEXES FROM sys.dm_db_index_physical_stats (DB_ID(),null, null, NULL, ‘DETAILED’) SELECT t.database_id, s.name, s.type,t.object_id, t.index_type_desc, t.avg_fragmentation_in_percent, t.page_count, avg_record_size_in_bytes * record_count/1048576 as index_size, * FROM master.dbo.TEMPINDEXES t inner join sysobjects s on t.[OBJECT_ID]¬† = s.id order by t.avg_fragmentation_in_percent desc

 
3. Remove Snapshots:
 
During Index  Rebuild existing Database snapshots will get very big due to the complexity of the whole process. Also performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
For big Indexes (< 2 GB) I strongly recommend removing the snapshots, postpone the processes which use them (usually used in reporting by DW) and perform the Rebuild.

If you follow these steps, you will not have any problems at re-index procedure on mirrored databases.