Shulou(Shulou.com)05/31 Report--

How to optimize the index in Sql Server, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

The code is as follows:-- Begin Index (index) analyzes the relevant Sql optimized-- returns all indexes with fragmentation rates greater than 25% in the current database-- runs this statement to scan many data pages-- avoid running when the system load is high-- avoid running declare @ dbid int select @ dbid = db_id () SELECT o.name as tablename when the system load is high. S.* FROM sys.dm_db_index_physical_stats (@ dbid, NULL, NULL) sdirection sys.objects o where avg_fragmentation_in_percent > 25 and o.object_id = s.object_id order by avg_fragmentation_in_percent desc GO-- an index 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-- automatically rebuild or reorganize the index-- is easier to use Use with caution, especially for online DB-Ensure a USE statement has been executed first. SET NOCOUNT ON; DECLARE @ objectid int; DECLARE @ indexid int; DECLARE @ partitioncount bigint; DECLARE @ schemaname nvarchar; DECLARE @ objectname nvarchar; DECLARE @ indexname nvarchar; 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 (1x 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 decision 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 >

= 30.0 SET @ command = N'ALTER INDEX'+ @ indexname + N'ON'+ @ schemaname + Numb.' + @ 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-- look at the 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 = 'tweak 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-- related Sql optimized by End Index analysis

This is the answer to the question about how to optimize the index in Sql Server. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.