N Narsimha Reddy

SQL DBA

INDEXES
An index is used to speed up searching the data in a database. Index is a pointer which points
towards existing stored data.

Index is best option for select queries (to read operations), not for DML (write) operations.

Clustered index: clustered index sort and store the data rows in a table based on key values.

Clustered index organized in a binary tree format. In leaf node we have exact data. If table has a
clustered index then that table called as clustered table.

There can be only one clustered index because the data rows can be stored in one order.

If the table has no cluster index then that table called as heap table. Cluster index id is always 1.

Syntax: Create clustered index index-name on table name (column name)

Non-clustered index: non clustered index does not contain the exact data at the leaf nodes. It
contains a pointer or key that points towards actual data.

In single table we can create up to 249 non-clustered indexes and 1 clustered-index (2005), from
2008 onwards we have 999 non-clustered index and 1 clustered index.

Syntax: create index index name on table name (column name)

Note:

1. If you create primary key on a column it will create a cluster index.

2. If you have cluster index and non-cluster index on table then non-cluster index depends
on CI.
3. If we have only non CI on table then it depends on row GUID.

When should we avoid indexes?

 Indexes should not be used on small tables.

 Tables that have frequent, large batch update or insert operations.
 Indexes should not be used on columns that contain a high number of null values.

Covering index: covering index improves the query performance and meets requirements of the
query using non-cluster indexes.

It typically consists of two or more columns, on maximum 16columns we can create covering
index.

Syntax: create index index name on tablename (c1,c2,…c16)

1
N Narsimha Reddy
SQL DBA
Filter index: from 2008 onwards filter index is used to filters the rows in columns that improve
query performance and plan quality. It reduces index maintenance cost and index storage cost.

Syntax: create index index name on table name (column name) where serial number > 2000

Unique index: It will not allow duplicate values. Unique is property of both clustered and
clustered index

Syntax: create unique index index -name on table name (column name)

Partial Index: (2005 feature) It supports in enterprise and developer edition, improves the
performance by distributing the data across the tables.

Other types like full text index, xml index (created on xml data types), column stored index
(2012 new feature), clustered column store index, non-clustered column store index.

Missing index and unused index:

A table have the indexes but not used or used early called as unused index.

Sometimes no index on the column which is frequently being used called as missing index.

Syntax: select * from sys.dm_db_missing_index_details it gives information

about missing index i.e. table name, column name.

Syntax:

Select * from sys.dm_db_missing_index_groups

Select * from sys.dm_db_missing_index_columns(‘1’) it gives specific column index missing

Syntax: select * from sys.dm_db_index_usage_stats based on user seeks, users

scan, user lookup

Syntax: sp_spaceused ‘table name’ ---provides table size and index size

Syntax: sp_helpindex ‘table name ‘ --- provides index information

Syntax: select * from sys.indexes ---- info about indexes

Fill factor:

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled
with data. Its value varies from 1-100 %.

2
N Narsimha Reddy
SQL DBA
100% is not recommended for read-write databases, for read-only databases there is no problem
if have fill factor value 100%.

Example: Specifying value 80% of fill factor means 20% of each leaf level page will be empty
which helps the table for its future growth.

Syntax: create index indexname on table name (COLUMNNAME) with fill factor=70%

Fragmentation:

Whenever indexes are not perform up to the mark called as fragmentation, when the index are
fragmented the data access becomes time consuming or very slow,When pages left empty or not
properly filled with data then fetching the data will take more time.

Types:

Internal fragmentation: when records are stored non-continuously inside the page called
internal fragmentation. Like pages left empty not filled properly inside due to this 4 pages data
occupy 8pages this occurs through the modification of data process (insert, delete, update).In
other words, internal fragmentation is said to occur if there is unused space between records in a
page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE,
and DELETE statements) that are made against the table.

External fragmentation: External fragmentation happens when the logical order of the pages
does not match the physical order of the pages. While reading the individual rows the external
fragmentation will not affect the performance a bit directly go to the page. If storage of pages
and extents are not continuously for example my data earlier store in 1 st extent due to shrinking
or recompilation my required data goes 10 th extent instead of 1st. while searching it has to scan all
10 extent pages this is called external fragmentation.

How to find fragmentation:

Dbcc showcontig (‘table name’)

Select avg_fragmentation_in_percent, * from

sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

Observe column avg_fragmentation_in_percent,: It shows level of fragmentation.

 If 0-5% we can ignore, more than 5% it creates problem.

 If 5-30% we should reorganize the index.
 If more than 30% we should rebuild the index.

Defragmentation: we need to either use rebuild or re-organize process, generally this process
advisable to schedule a job to do this in off production hours as these activities are high cost
resources.
3
N Narsimha Reddy
SQL DBA
Re-organize: All the records will arrange properly inside the index it will make data in order.

Syntax: alter index all on tablename reorganize

Alter index indexname on table name reorganize

Re-build: it is online operation in enterprise edition. It uses a much extra space as it drops old
index completely and create new index.

Syntax: alter index all on table name rebuild

Dbcc rebuild index(indexname)

Parameters:

Sort_in_tempdb: By default this option is off and the process runs sorting operation in
destination file of db. If it is ON our sorting will run in tempdb.

Online: by default online option is off, the old index is not available while rebuilding the
indexes, as the table is locked and not allow read and write operation.

If Online is ON old index is accessible for all operations while creating the new index.

Example: alter index all on table name rebuild with (online=on,

sort_in_tempdb=on, fill_factor=80)

Disable and drop of index:

Alter index index name on table name disable

Drop index index name on table name (column name)

Note: don’t disable CI, drop CI and disable NCI. If CI is disabled table is not accessible because
NCI depends on CI.

STATISTICS

Stats: statistics are database objects it contains information about the tables i.e. how many
records are insert/delete/update. If estimated query plan (ctrl+L) and actual query plan (ctrl+M)
are same then we can say that stats are up to date.

Statistics are used by query optimizer in creating a query plan.

4
N Narsimha Reddy
SQL DBA
1) SQL server maintain stats on index and key columns of all your table.
2) These are used by query optimizer in creating query plans
3) Stats are by default created and updated if you want to create it we can create manually
4) Whenever you create new index on a table automatically new stats will be created
5) If stats are very poor it creates a very poor query plan i.e it is totally different from actual
query plan
6) If you find stats are not up to date as a DBA we need to update.
How to find stats last update date and time

Use databasename
Go
Sp_Autostats (‘Tablename’)

Use databasename
Go
Dbcc Show_Statistics (‘Tablename’,indexname/columnname)

It returns stats name and last update date time. Then we have to update the
statistics.

How to update the stats

Update Statistics Tablename with fullscan

Update Statistics Tablename with sample 70 percent

7) If you perform re-organise on the table (or) Stats are out of date then manually we need
to update the stats
8) If you rebuilt the index no need update the stats.

sp_updatestats:

It updates statistics on all the tables in a database that require updating based on
the counts the total number of inserted, deleted, or updated rows since the last time
statistics were updated for the table and do not update the statistics on unchanged
rows.

Wait types:

5
N Narsimha Reddy
SQL DBA
SQL server will wait for resources to complete the query sometimes, at that time we can see wait
types in SQL server.

This wait types will causes performance issues and becomes a problem .

Find wait types by using dmv:

Sys.dm_db_wait_stats

Identify wait type related issue by using below dmv

Sys.dm_os_waiting_tasks

Query life cycle:

Running: a query which is being executed on cpu is called running. This query is response for
cpu time.

Runnable: A query which is ready to execute and waiting for its turn to run is called runnable
query. But cpu is running other query.

Suspended: A query which is waiting due to any reason (to know the reason we need know wait
stats) and should converted to runnable.

Famous wait-types:

1. Cx_packet wait type: This wait type is involved in parallel query execution and
indicates that the spid is waiting on a parallel process execute to complete or start.
Note: not all cx-packets are best suggestion. To trouble shoot cx-packet wait type, we can
set the max. Degree of parallel to lower number or 1.
Sp_configure ‘max.degree of parallelism ’, 1
Go
Reconfigure with override
2. Asynchronous network IO: It occurs on the network rights when the task is blocked
behind the network.
3. Async-IO-completion wait type: It occurs when a task is waiting for IO to finish.
4. Backup –IO wait type: It occurs when task is blocked as a part of backup process.
5. Resource-semaphore wait type: It occurs if SQL server find the memory pressure or
memory bottle neck on a server.
6. SOS-scheduler-yield wait type: It occurs when SQL server finds cpu bottle neck on a
server.

6
N Narsimha Reddy
SQL DBA