SQL Server Index usage and optimization related SQL statements

Copy codeThe Code is as follows:
-- Begin Index analysis optimization related SQL
-- Returns the index with the current database fragmentation rate greater than 25%.
-- Run this statement to scan many data pages
-- Avoid running when the system load is high
-- Avoid running when the system load is high
Declare @ dbid int
Select @ dbid = db_id ()
SELECT o. name as tablename, s. * FROM sys. dm_db_index_physical_stats (@ dbid, NULL) s, sys. objects o
Where avg_fragmentation_in_percent> 25 and o. object_id = s. object_id
Order by avg_fragmentation_in_percent desc
GO
-- Indexes that may be missing in the current database
-- Very useful SQL statements
Select d .*
, S. avg_total_user_cost
, S. avg_user_impact
, S. last_user_seek
, S. unique_compiles
From sys. dm_db_missing_index_group_stats s
, Sys. dm_db_missing_index_groups g
, Sys. dm_db_missing_index_details d
Where s. group_handle = g. index_group_handle
And d. index_handle = g. index_handle
Order by s. avg_user_impact desc
Go
-- Auto re-create or re-organize Indexes
-- Relatively easy to use and use with caution, especially for Online Databases
-- Ensure a USE <databasename> statement has been executed first.
Set nocount on;
DECLARE @ objectid int;
DECLARE @ indexid int;
DECLARE @ partitioncount bigint;
DECLARE @ schemaname nvarchar (130 );
DECLARE @ objectname nvarchar (130 );
DECLARE @ indexname nvarchar (130 );
DECLARE @ partitionnum bigint;
DECLARE @ partitions bigint;
DECLARE @ frag float;
DECLARE @ command nvarchar (4000 );
-- Conditionally select tables and indexes from the sys. dm_db_index_physical_stats function
-- And convert object and index IDs to names.
SELECT
Object_id AS objectid,
Index_id AS indexid,
Partition_number AS partitionnum,
Avg_fragmentation_in_percent AS frag
INTO # work_to_do
FROM sys. dm_db_index_physical_stats (DB_ID (), NULL, 'limited ')
WHERE avg_fragmentation_in_percent> 10.0 AND index_id> 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions cursor for select * FROM # work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @ objectid, @ indexid, @ partitionnum, @ frag;
IF @ FETCH_STATUS <0 BREAK;
SELECT @ objectname = QUOTENAME (o. name), @ schemaname = QUOTENAME (s. name)
FROM sys. objects AS o
JOIN sys. schemas as s ON s. schema_id = o. schema_id
WHERE o. object_id = @ objectid;
SELECT @ indexname = QUOTENAME (name)
FROM sys. indexes
WHERE object_id = @ objectid AND index_id = @ indexid;
SELECT @ partitioncount = count (*)
FROM sys. partitions
WHERE object_id = @ objectid AND index_id = @ indexid;
-- 30 is an arbitrary demo-point at which to switch between reorganizing and rebuilding.
IF @ frag <30.0
SET @ command = N 'alter Index' + @ indexname + N' ON '+ @ schemaname + N'.' + @ objectname + N' REORGANIZE ';
IF @ frag & gt; = 30.0
SET @ command = N 'alter Index' + @ indexname + N' ON '+ @ schemaname + N'.' + @ objectname + N' REBUILD ';
IF @ partitioncount> 1
SET @ command = @ command + N' PARTITION = '+ CAST (@ partitionnum AS nvarchar (10 ));
EXEC (@ command );
Print n 'executed: '+ @ command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
Drop table # work_to_do;
GO

-- View the current database index usage
-- Very useful
SELECT
Object_name (object_id) as table_name,
(
Select name
From sys. indexes
Where object_id = stats. object_id and index_id = stats. index_id
) As index_name,
*
FROM sys. dm_db_index_usage_stats as stats
WHERE database_id = DB_ID ()
Order by table_name

-- Specify the index usage of the table
Declare @ table as nvarchar (100)
Set @ table = 't_ name ';
SELECT
(
Select name
From sys. indexes
Where object_id = stats. object_id and index_id = stats. index_id
) As index_name,
*
FROM sys. dm_db_index_usage_stats as stats
Where object_id = object_id (@ table)
Order by user_seeks, user_scans, user_lookups asc
-- SQL related to End Index Analysis and Optimization