Sql Server索引脚本

DECLARE @tablename sysname;
 
DECLARE @strsql NCHAR(500);
 
DECLARE tableNameCursor CURSOR FOR
SELECT b.name
FROM sysobjects b
WHERE xtype = 'U'
      AND b.name NOT IN (
                            SELECT OBJECT_NAME(a.parent_obj) FROM sysobjects a WHERE xtype = 'PK'
                        );
 
OPEN tableNameCursor;
 
FETCH NEXT FROM tableNameCursor
INTO @tablename;
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
    PRINT @tablename;
 
    SET @strsql = 'alter table ' + @tablename + ' add primary key (id) ';
 
    PRINT @strsql;
 
    EXEC (@strsql);
 
    FETCH NEXT FROM tableNameCursor
    INTO @tablename;
 
END;
 
CLOSE tableNameCursor;
 
DEALLOCATE tableNameCursor;

SELECT TOP 20
       qs.creation_time,
       last_execution_time,
       total_physical_reads,
       total_logical_reads,
       total_logical_writes,
       DB_NAME(dbid) dbname,
       execution_count,
       --, total_worker_time
       last_worker_time,
       --,substring(convert(char(23),DATEADD(ms,total_worker_time/1000,0),121),12,23) total_worker_time
       --, total_elapsed_time
       last_elapsed_time,
       SUBSTRING(CONVERT(CHAR(23), DATEADD(ms, total_elapsed_time / 1000, 0), 121), 12, 23) total_elapsed_time,
       --, total_elapsed_time / execution_count avg_elapsed_time
       SUBSTRING(CONVERT(CHAR(23), DATEADD(ms, (total_elapsed_time / execution_count) / 1000, 0), 121), 12, 23) avg_elapsed_time,
       SUBSTRING(   st.text,
                    (qs.statement_start_offset / 2) + 1,
                    ((CASE statement_end_offset
                          WHEN -1 THEN
                              DATALENGTH(st.text)
                          ELSE
                              qs.statement_end_offset
                      END - qs.statement_start_offset
                     ) / 2
                    ) + 1
                ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
--ORDER BY execution_count desc 计划执行总次数
ORDER BY total_elapsed_time; --计划执行总时长
--order by last_worker_time desc;--最近一次执行cpu 占用时长

SELECT [statement] 表名称,
       'CREATE INDEX [IX_' + SO.name + '_'
       + REPLACE(
                    REPLACE(REPLACE(REPLACE(ISNULL(equality_columns, included_columns), '[', ''), ']', ''), ',', '_'),
                    ' ',
                    ''
                ) + '] ON ' + SO.name + '(' + ISNULL(equality_columns, '')
       + CASE
             WHEN equality_columns IS NOT NULL
                  AND inequality_columns IS NOT NULL THEN
                 ','
             ELSE
                 ''
         END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '')
       + ' WITH(FILLFACTOR = 90, PAD_INDEX = ON)' AS 索引创建脚本,
       avg_user_impact 收益百分比,
       avg_total_user_cost 减少成本,
       avg_total_user_cost * avg_user_impact * (user_scans + user_seeks) AS 综合收益
FROM sys.dm_db_missing_index_groups AS G
    INNER JOIN sys.dm_db_missing_index_group_stats AS GS
        ON G.index_group_handle = GS.group_handle
    INNER JOIN sys.dm_db_missing_index_details AS D
        ON G.index_handle = D.index_handle
    INNER JOIN sysobjects AS SO
        ON SO.id = D.object_id
ORDER BY 综合收益 DESC;

SELECT o.name AS table_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       c.name AS index_column_name,
       i.index_id,
       ic.index_column_id
FROM sys.objects o
    INNER JOIN sys.indexes i
        ON o.object_id = i.object_id
    INNER JOIN sys.index_columns ic
        ON i.object_id = ic.object_id
           AND i.index_id = ic.index_id
    INNER JOIN sys.columns c
        ON o.object_id = c.object_id
           AND ic.column_id = c.column_id
WHERE o.name = 'PJPY_CollegeNumOrMoneyLimit'
ORDER BY i.index_id,
         ic.index_column_id;

SELECT t.name 表名称, 
        s.record_count 数据量,
        i.name 索引名称,
        s.avg_fragmentation_in_percent 碎片百分比,
        CASE
            WHEN s.avg_fragmentation_in_percent > 30 THEN
                'alter index ' + i.name + ' on dbo.' + t.name + ' rebuild'
            WHEN s.avg_fragmentation_in_percent > 5 THEN
                'alter index ' + i.name + ' on dbo.' + t.name + ' reorganize'
            ELSE
                '无需整理'
        END 碎片整理脚本
 FROM sys.tables t
     JOIN sys.indexes i
         ON i.object_id = t.object_id
            AND i.name IS NOT NULL
     INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), object_id('tablename'), NULL, NULL, 'sampled') s
         ON s.object_id = i.object_id
            AND s.index_id = i.index_id
            AND s.alloc_unit_type_desc = 'IN_ROW_DATA'
            WHERE  s.avg_fragmentation_in_percent>5
 ORDER BY 碎片百分比 DESC; 

SET NOCOUNT ON;
--R_T层游标取出当前数据库所有表 
DECLARE R_T CURSOR FOR SELECT name FROM sys.tables;
DECLARE @T VARCHAR(50);
OPEN R_T;
FETCH NEXT FROM R_T
INTO @T;
WHILE @@fetch_status = 0
BEGIN
    --R_index游标判断指定表索引碎片情况并优化 
    DECLARE R_Index CURSOR FOR
    SELECT t.name,
           i.name,
           s.avg_fragmentation_in_percent
    FROM sys.tables t
        JOIN sys.indexes i
            ON i.object_id = t.object_id
        JOIN sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@T), NULL, NULL, 'limited') s
            ON s.object_id = i.object_id
               AND s.index_id = i.index_id;
    DECLARE @TName VARCHAR(50),
            @IName VARCHAR(50),
            @avg INT,
            @str VARCHAR(500);
    OPEN R_Index;
    FETCH NEXT FROM R_Index
    INTO @TName,
         @IName,
         @avg;
    WHILE @@fetch_status = 0
    BEGIN
        IF @avg >= 30 --如果碎片大于30,重建索引 
        BEGIN
            SET @str = 'alter index ' + RTRIM(@IName) + ' on dbo.' + QUOTENAME(RTRIM(@TName)) + ' rebuild';
        END;
        ELSE --如果碎片小于30,重新组织索引 
        BEGIN
            SET @str = 'alter index ' + RTRIM(@IName) + ' on dbo.' + QUOTENAME(RTRIM(@TName)) + ' reorganize';
        END;
        PRINT @str;
        EXEC (@str); --执行 
        FETCH NEXT FROM R_Index
        INTO @TName,
             @IName,
             @avg;
    END;
    --结束r_index游标 
    CLOSE R_Index;
    DEALLOCATE R_Index;
    FETCH NEXT FROM R_T
    INTO @T;
END;
--结束R_T游标 
CLOSE R_T;
DEALLOCATE R_T;
SET NOCOUNT OFF;

 

Sql Server无用索引查询