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.

DMV’S [DYNAMIC MANAGEMENT VIEWS]

 > This concept is introduced in SQL Server 2005 version.

> The main purpose we can monitor SQL Server without consuming hardware resources like DBCC queries.

> The DMV’S newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine.

> These Values will help the administrator to diagnose problems and tune the server for optimal performance.

> The DMV’S in SQL Server are designed to give you a window into what’s going on inside SQL Server

> They can provide information on what’s currently happening inside the server as well as the objects it’s strong. They are designed to be used instead of system tables and various functions.

> DMV’S are stored in sys schema and they start with dm_in the name

To know list of DMV’S:

SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name

Output:

List of DMV'S

2005 version of Sql -89….. 2008 version of sql-176……2012 version of sql-900+

There are two types of dynamic management views and functions:

Server-scoped dynamic management views and functions: These require VIEW SERVER STATE permission on the server.

Database-scoped dynamic management views and functions: These require VIEW DATABASE STATE permission on the database.

There are multiple categories close to 17... In which these views and functions have been organized

We have 85 of these views and functions. To give a further split, 76 of these are views and 9 of them are functions... Below are the 4 types of DMV which can be used frequently.

1. SQL Server Related [Hardware Resources] DMV’S

2. Database Related DMV’S

3. Index Related DMV’S

4. Execution Related DMV’S

5. Replication Related DMV’S

6. Query notifications Related DMV’S

7. SQL Operating System Related DMV’S

8. I/O Related DMV’S

9. Transaction Related DMV’S

1. SQL Server related [Hardware Resources] DMV’S:

> This section contains the dynamic management views that are associated with the SQL Server Operating System (SQLOS). The SQLOS is responsible for managing operating system resources that are specific to SQL Server.

Locks:

Select * from Sys.dm_tran_locks:

Returns information about locks

Blockings:

Select * from sys.dm_os_waiting_tasks:

> Returns information about the wait queue of tasks that are waiting on some resource.

Sys.dm_os_wait_stats:

> Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

2. Database Related DMV’S:

Mirroring:

1. Sys.dm_db_mirroring_auto_page_repair:

1. Returns a row for every automatic page-repair attempt on any mirrored database on the server instance.

2. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100 rows per database.

2. Sys.dm_db_mirroring_connections:

Returns a row for each connection established for database mirroring.

3. INDEX related DMV’S

Fragmentation: DMV's to find 2005 onwards

1. Select * from sys.dm_db_index_physical_stats:

To find Column to verify the fragmentation value:

Avg_fragementaion_in_pernt:

Missing Index:

Select * from sys.dm_db_missing_index_details:

Returns detailed information about missing indexes, excluding spatial indexes.

sys.dm_db_index_usage_stats:

Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.

4. Execution related DMV’S:

Sys.dm_exec_cached_plans:

> Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Sys.dm_exec_connections:

> Returns information about the connections established to this instance of SQL Server and the details of each connection.

Sys.dm_exec_sessions:

> shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

Sys.dm_exec_cursors:

> Returns information about the cursors that are open in various databases.

5. Replication related DMV’S:

Sys.dm_repl_articles:

> Returns information about database objects published as articles in a replication topology.

Sys.dm_repl_tranhash:

> Returns information about transactions being replicated in a transactional publication.

Sys.dm_repl_schemas:

> Returns information about table columns published by replication.

Sys.dm_repl_traninfo:

> Returns information on each replicated or change data capture transaction.

STORED PROCEDURES IN SQL SERVER

 > A stored procedure is a group of Sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

In SQL we are having different types of stored procedures are there

a) System Stored Procedures

b) User Defined Stored procedures

c) Extended Stored Procedures

System Stored Procedures:

System stored procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be used to perform variety of tasks to support Sql server functions for external application calls in the system tables and use to perform many administrative and informational activities.

Ex: sp_helptext [StoredProcedure_Name]

User Defined Stored Procedures:

User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database.

Stored procedure are modules or routines that encapsulate code for reuse. A stored procedures can take input parameters, return tabular or scalar results and messages to the client.

Extended Stored Procedures:

Extended stored procedures are the procedures that call functions from DLL files that an instance of Microsoft SQL Server can dynamically load and run. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.

Why use Stored Procedures?

> Rewriting inline SQL statements as Stored Procedures

> Compilation and storing of the query execution plan

> Enabling of conditional and procedural logic

> Centralized repository for DML and DDL code enabling code reuse

> Protection from SQL Injection attacks

> Enabling of strict security model

> Readability

Sample of creating Stored Procedure

USE AdventureWorks2008R2;

GO

CREATE PROCEDURE dbo.sp_who

AS

SELECT FirstName, LastName FROM Person.Person;

GO

EXEC sp_who;

EXEC dbo.sp_who;

GO

DROP PROCEDURE dbo.sp_who;

GO

Advantages of using stored procedures

a) Stored procedure allows modular programming.

You can create the procedure once, store it in the database, and call it any number of times in your program.

b) Stored Procedure allows faster execution.

If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

c) Stored Procedure can reduce network traffic.

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

d) Stored procedures provide better security to your data

Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.