Sql Server 中怎么優(yōu)化索引

Sql Server 中怎么優(yōu)化索引，針對這個(gè)問題，這篇文章詳細(xì)介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問題的小伙伴找到更簡單易行的方法。

代碼如下: --Begin Index(索引) 分析優(yōu)化的相關(guān) Sql -- 返回當(dāng)前數(shù)據(jù)庫所有碎片率大于25%的索引 -- 運(yùn)行本語句會掃描很多數(shù)據(jù)頁面 -- 避免在系統(tǒng)負(fù)載比較高時(shí)運(yùn)行 -- 避免在系統(tǒng)負(fù)載比較高時(shí)運(yùn)行 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 -- 當(dāng)前數(shù)據(jù)庫可能缺少的索引 -- 非常好用的 Sql 語句 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 -- 自動重建或重新組織索引 -- 比較好用，慎用，特別是對于在線 DB -- 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, 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; -- 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 + 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 -- 查看當(dāng)前數(shù)據(jù)庫索引的使用率 -- 非常的有用 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 -- 指定表的索引使用情況 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 分析優(yōu)化的相關(guān) Sql

關(guān)于Sql Server 中怎么優(yōu)化索引問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識。