SQL Script: How To Find Index Fragmentation

What Is Index Fragmentation?

Over time, as records are inserted, updated, and deleted, your tables and indexes become fragmented.  This fragmentation can lead to poor performance of not only your SELECT queries, but also your INSERT, UPDATE, and DELETE operations.

How Do I Find Index Fragmentation?

Index fragmentation can be found by querying the built in sys.dm_db_index_physical_stats DMV. To get readable, useful information you’ll also need to join your query to other DMVs such as sys.indexes and sys.tables.  Below is a simple query that will provide a list of indexes, fragmentation percentage, and record counts for each table in a database.

USE YourDatabase GO SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, indexstats.page_count, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC

EXECUTION PLAN

 What is execution plan and explain it? 

Execution plan graphically displays the data retrieval methods chosen by SQL Server. It represents the execution cost of specific statements and quires in SQL Server. This graphical approach is very useful for understanding the performance of the query. 

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by SQL Server Query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure science the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the query drop-down menu). If this option is tuned on it will display query execution plan in separate window query is ran again.

Execution plan displays:

1. Physical operations

2. Logical operations

3. Actual Number rows

4. Estimated I/O cost

5. Estimated CPU cost

6. Number of Executions

7. Estimated Number of Executions

8. Estimated Operator cost

9. Estimated Subtree cost

10. Estimated Number of Rows

11. Estimated Row Size

12. Actual rebinds

13. Actual rewinds

14. Key lookup

15. Nested look up

16. Index seek

17. Index scan

TEMPDB ARCHITECTURE

 What is Stored in Tempdb?

Tempdb is used to store three different categories of temporary data:

– User Objects

– Internal Objects

– Version Stores

User Objects:

• Local and global temporary tables and indexes

• User-defined tables and indexes

• Table variables

• Tables returned in table-valued functions

Note: These lists are not designed to be all inclusive.

Internal Objects:

• Work tables for DBCC CHECKDB and DBCC CHECKTABLE.

• Work tables for hash operations, such as joins and aggregations.

• Work tables for processing static or keyset cursors.

• Work tables for processing Service Broker objects.

• Work files needed for many GROUP BY, ORDER BY, UNION, and SELECT DISTINCT operations.

• Work files for sorts that result from creating or rebuilding indexes (SORT_IN_TEMPDB).

• Storing temporary large objects (LOBs) as variables or parameters (if they won’t fit into memory).

Version Stores:

• The version store is a collection of pages used to store row level versioning of data.

• There are two types of version stores:

1. Common Version Store: Used when:

– Building the inserted and deleted tables in after triggers.

– When DML is executed against a database using snapshot transactions or read-committed row versioning isolation levels.

– When multiple active result sets (MARS) are used.

2. Online-Index-Build Version Store: Used for online index builds or rebuilds. EE edition only.

Tempdb doesn’t act as an other databases:

• Tempdb only uses simply recovery model.

• Manydb options not be able to change.

• Tempdb may not be dropped, attached or detached.

• Tempdb may not backed up, restore, can’t implement any HA options.

Types of Tempdb Problems:

• Generally, there are three major problems you run into with Tempdb:

1. Tempdb is experiencing an I/O bottleneck, hurting server performance.

2. Tempdb is experiencing DDL and/or allocation contention on various global allocation structures (metadata pages) as temporary objects are being created, populated, and dropped. E.G. Any space-changing operation (such as INSERT) acquires a latch on PFS, SGAM or GAM pages to update space allocation metadata. A large number of such operations can cause excessive waits while latches are acquired, creating a bottleneck, and hurting performance.

3. Tempdb has run out of space.

SOLUTION:

Use performance Monitor:

And also DMV’S are useful what is going on Tempdb

• sys.dm_db_file_space_usage: Returns one row for each data file in Tempdb showing space usage.

• sys.dm_db_task_space_usage: Returns one row for each active task and shows the space allocated and deallocated by the task.

• sys.dm_db_session_space_usage: Returns one row for each session, with cumulative values for space allocated and deallocated by the session.

Monitoring Tempdb Space:

Performance Counters:

• SQL Server: Database: Data File(s) Size (KB): tempdb

• SQL Server: Database: Log File(s) Used Size (KB): tempdb

• SQL Server: Transactions: Free Space in tempdb (KB)

DMV

• sys.dm_db_file_space_usage

Errors in tempdb running slow check in error logs:

• Check the SQL Server error log for these errors:

– 1101 or 1105: A session has to allocate more space in tempdb in order to continue

– 3959: The version store is full.

– 3967: The version store has been forced to shrink because tempdb is full.

– 3958 or 3966: A transaction is unable to find a required version record in tempdb.

Note: Be sure auto growth is turned on for tempdb, and ensure that you have enough available free disk space.

Operations that cannot be performed on the tempdb database:

> Adding filegroups.

> Backing up or restoring the database.

> Changing collation. The default collation is the server collation.

> Changing the database owner. Tempdb is owned by dbo.

> Creating a database snapshot.

> Dropping the database.

> Dropping the guest user from the database.

> Participating in database mirroring.

> Removing the primary filegroup, primary data file, or log file.

> Renaming the database or primary filegroup.

> Running DBCC CHECKALLOC.

> Running DBCC CHECKCATALOG.

> Setting the database to OFFLINE.

> Setting the database or primary filegroup to READ_ONLY.