Archive for the ‘SQL Server Internals’ Category

SQL Server Index Fragmentation and Its Resolution

January 9, 2011 Leave a comment

It’s really easy to answer the interviewer question “How to know index fragmentation?” and “what you will do to resolve the issue?”

It’s always a situation with Production DBA’s how to find index fragmentation and in case they are lucky enough to find out they are confused its internal fragmentation or external fragmentation. What to do to reduce   index fragmentation it should be Index Organize or Rebuild is a big challenge.

DBA don’t put much effort that rebuilding the indexes can be more expensive in an OLTP environment and affect the system inadequately. Fragmentation can cause serious performance issues and it’s vital to understand how it affects the performance.

Fragmentation can cause queries run slower due to more disk IO because of data page splitting

If the table size is too large and you decide to rebuild the index (online or offline), it can be a expensive decision as rebuilding index will make the system more slow while rebuilding of index is in progress.

In SQL Server there are two types of index fragmentation internal fragmentation and external fragmentation.

Internal Fragmentation: Occurs if the percentage of free space in index pages is below 75%.
This doesn’t mean that index which have fill factor or more then 80% doesn’t come in this category.

External Fragmentation: External fragmentation occurs when an index leaf page is not in logical order. When an index is created, the index keys are placed in a logical order on a set of index pages. As new data is inserted into the index, it is possible for the new keys to be inserted in between existing keys. This may cause new index pages to be created to accommodate any existing keys that were moved so that the new keys can be inserted in correct order.

First we will figure out the indexes on tables or on all objects of a database.

SELECT id,indid,dpages,OrigFillFactor,name Ind_Name FROM sys.sysindexes



id indid dpages OrigFillFactor Ind_Name
518565927 1 0 0 Colid_Clu
518565927 2 0 0 NonClustered_CheckFrag

At the movement there are no records associated with the table. So, there will not be any fragmentation.



ObjectName varchar(MAX),

ObjectIndex varchar(max),

Avg_fragmentation_in_percent decimal,

avg_page_space_used_in_percent decimal





SELECT object_name(dt.object_id)ObjectName, IndexName,




SELECT    object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’) where index_id<>0


sys.indexes si ON si.object_id=dt.object_id and si.index_id=dt.index_id

Order By dt.avg_fragmentation_in_percent asc

SELECT * FROM @IndexFrag where ObjectName=’__DROPRO’ order by ObjectIndex


As u can see the empty table doesn’t have fragmentation and no data pages associated with it.

I have done few hundreds of transaction on the table with insert, update and delete.

Checking fragmentation after the transactions

SELECT id,indid,dpages,OrigFillFactor,name Ind_Name FROM sys.sysindexes


id Indid dpages OrigFillFactor Ind_Name
518565927 1 666 0 Colid_Clu
518565927 2 554 0 NonClustered_CheckFrag


DBCC SHOWCONTIG scanning ‘__DROPRO’ table…

Table: ‘__DROPRO’ (518565927); index ID: 1, database ID: 7

TABLE level scan performed.

– Pages Scanned…………………………..: 666

– Extents Scanned…………………………: 89

– Extent Switches…………………………: 347

– Avg. Pages per Extent……………………: 7.5

– Scan Density [Best Count:Actual Count]…….: 24.14% [84:348]

– Logical Scan Fragmentation ………………: 99.85%

– Extent Scan Fragmentation ……………….: 66.29%

– Avg. Bytes Free per Page…………………: 2336.0

– Avg. Page Density (full)…………………: 71.14%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Avg_fragmentation_in_percent has increased above 15 %( A value between 5-30% indicates moderate fragmentation, while any value over 30% indicates high fragmentation) it means the physical order of the index pages is not matching with the logical order.

The Avg_page_space_used_in_percent is another value that it is worth to look closely. This value represents the amount of spaced used in the indexes. A value below 75% is usually associated to internal fragmentation.

Reference Values (in %) Action SQL statement
Avg_fragmentation_in_percent > 5 AND < 30 Reorganize Index ALTER INDEX REORGANIZE
Avg_fragmentation_in_percent > 30 Rebuild Index ALTER INDEX REBUILD

As we can see the Avg_fragmentation_in_percentage is more than 30% we will go for Rebuilding of indexes

USE [vcvcxvcx]









Categories: Index Fragmentation

Temp Database Issues Resolution (Move Tempdb)

August 25, 2010 Leave a comment


SQL Server Services is not starting because temp database can’t grow further as it reached to its maximum size (All Disk Space is utilized). We can’t delete the temp database file from the disk because of disk issues.

What can be done to resolve the issue?


To resolve the issue DAC should be enabled.

What basically we will do is moving the temp database on to some other drive.

Steps to do

Start SQL Server Service with minimal configuration


Start the command prompt with Administrative Priviliges

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -sInstancename -f

In case instancename is default  then u don’t provide the instance name

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -f

Start another instance of command prompt with administrative priviliges.


sqlcmd -A -dmaster -E -SNL03-DF271\instance name

In case instancename is default  then u don’t provide the instance name

 sqlcmd -A -dmaster -E -SNL03-DF271\instance name

Move the temp database file to some other location

USE master;
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQLLog\templog.ldf’);

Once file get moved;Restart the SQL Server Services

Press Ctrl+C on the command Prompt in the first step

Restart the SQL Server Service.

Query Plan -Part I

June 8, 2010 Leave a comment

This post is about how to see or get compiled query plans. I thought of writing this blog to help  DBA’s who run around to seek help how to get compiled query plans and see which one is being used for their query.

SQL Server follows some basis rules to generate the Query Plan.
1. Complier generates Complied plans for your Query.
2. Next is it creates an Execution Plans based on Compiled Query Plans.
3. Query Execution Happens, While Query Execution starts the optimizer look for indexes or Stats for best use).
To get all Query Plans in XML Format .

SELECT convert(xml,query_plan)    FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

To get Query Plan in XML Format for a Specific Query or StoredProc

In case u have an OLTP Environment where hundreds of request are flowing  and u want to get the Query Pan for a specific query , in the where conduction of the below u can give some specific sort of text which is written in the query.

 SELECT convert(xml,query_plan)    FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

To get Query Plan for a Specific Query or StoredProc  or all Complied Plans

    SELECT text,query_plan,plan_handle    FROM sys.dm_exec_query_stats AS qs
             CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp

SELECT text,query_plan,plan_handle  FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

In case u have different plans for the same query then u need to look into the Query Execution Plan for the reason.

Will write about the reason why different query plans are generated for the same query and how it affects the performance of the Database and System.

Hope this post will be helpful.

Capture Query Execution Plans using SQL Server Profiler

May 6, 2010 Leave a comment

It’s always like a nightmare for DBA to capture Query execution Plans in text format and study it in case of performance issues.

Here I will explore how SQL Server Profiler can be helpful in capturing Query Execution Plan in text format.

Note:  Always run SQL Server Profiler on server which is not production as it consumes server resources.

 Open SQL Server Profiler can connect to the SQL Server Instance from which u want to capture the Query Execution Plan.

In the Event Selection Tab uncheck all the default selected Events, Expand the [+] Performance Event and select Show Plan Statistics Profile.

Click on the column filter  and  filter the capture trace using database id (Production Database id) and Login name (connect using id which no one is using else it will be little difficult to figure out for which query the exercise is being done.

Click on the Run and start capturing the Plans,once u execute the query SQL Profilor will start caturing the plans as below.

It’s always good if u start reading the plan from bottom to top.It will help u understand how really execution plan work and the top most plan will be the result (total number of rows) which is the total number of rows as output so the query.

How to make Dedicated Admin Connection to SQL Server in Single User Mode

May 3, 2010 Leave a comment

Some time there is a need to start SQL Server Service in DAC mode (Dedicated Administrator Connection) (Emergency Mode). 

Enable Remote Admin Connection using sp_configure so a dedicated connection can be made when SQL Server Start in single user mode. 

sp_configure ‘remote admin connections’ ,1 
reconfigure with override





C:\Program Files\Microsoft SQL Server\MSSQL10.BI\MSSQL\Binn>sqlservr.exe -sBI(instance name) -m 

Note : Before stopping SQL Server Service Stop the SQL Server Reporting Service as it will make the first connection (Connection Pooling) as SQL Server Service starts in Single User Mode. 

 To create a DAC Connection open a new commond prompt and connect to sqlcmd 



Stop SQL Server Service and start again using option -m from command Prompt.






Categories: DAC

Start SQL Server Service in Single-User Mode

May 1, 2010 Leave a comment

Under certain circumstances, you may have to start an instance of Microsoft SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

When you start an instance of SQL Server in single-user mode, note the following:

  • Only one user can connect to the server.
  • The CHECKPOINT process is not executed. By default, it is executed automatically at startup.


Updating Index Statistics With Full Table Scan

March 9, 2010 Leave a comment

How to Update Index Statistics of a table with Full Table Scan.


The following example updates the table statistics group (collection) in the given table, forces a full scan of all rows in the given table, and re-enables automatic statistical updating on the table.

In case u does not want to enable automatic statistical updating use NORECOMPUTE option to disable the same.