SQL statements related to SQL Server Index usage and optimization, which are well documented here. For more information, see
SQL Server Index usage and optimization related SQL statements, very good SQL statements, recorded here, need friends can refer to below
The 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 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
