Copy Code code as follows:
--begin Index (index) analysis optimization related SQL
--Returns an index with more than 25% of the current database fragmentation rate
--Running this statement scans a lot of data pages
--Avoid running when the system load is relatively high
--Avoid running when the system load is relatively high
DECLARE @dbid int
Select @dbid = db_id ()
SELECT O.name as tablename,s.* from sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, 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
--Possible missing indexes for the current database
-A very useful SQL statement
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
--Automatically rebuild or rearrange indexes
--easy to use and cautious, especially for online DB
--Ensure a Use <databasename> statement has been executed.
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, NULL, 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;
--is a arbitrary decision point in 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 >= 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 usage of the current database index
--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
--end Index Analysis optimization of related SQL
