Início / dba / Perguntas / 167265
Accepted
Andrew Lackenby
Asked: 2017-03-16 07:40:53 +0800 CST

Scripts de índices não clusterizados que são compactados

  • 772

Ninguém conhece uma maneira de encontrar índices não clusterizados que foram compactados usando um script? Eu uso o seguinte para roteirizá-los e estava procurando alterá-lo para adicionar a opção de compactação, agora tenho empresa para brincar.

DECLARE @idxTableName SYSNAME
DECLARE @idxTableID INT
DECLARE @idxname SYSNAME
DECLARE @idxid INT
DECLARE @colCount INT
DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT
DECLARE @ColumnIDInTable INT
DECLARE @ColumnIDInIndex INT
DECLARE @IsIncludedColumn INT
DECLARE @sIncludeCols VARCHAR(4000)
DECLARE @sIndexCols VARCHAR(4000)
DECLARE @sSQL VARCHAR(4000)
DECLARE @rowcnt INT
DECLARE @sParamSQL VARCHAR(4000)
DECLARE @location SYSNAME
DECLARE @fillfactor INT

-- Get all the index info
DECLARE curidx CURSOR
FOR
SELECT object_name(si.object_id)
,si.object_id
,si.NAME
,si.index_id
FROM sys.indexes si
LEFT JOIN information_schema.table_constraints tc ON si.NAME = tc.constraint_name AND object_name(si.object_id) = tc.table_name
WHERE objectproperty(si.object_id, 'IsUserTable') = 1 AND [si].[type] = 2
ORDER BY object_name(si.object_id)
,si.index_id

OPEN curidx

FETCH NEXT
FROM curidx
INTO @idxTableName
,@idxTableID
,@idxname
,@idxid

--loop 
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sSQL = 'IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE name = ''' + @idxname + ''')' + CHAR(13)
SET @sSQL = @sSQL + 'BEGIN' + CHAR(13)
SET @sSQL = @sSQL + 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
SET @sSQL = @sSQL + 'UNIQUE '

-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
SET @sSQL = @sSQL + 'CLUSTERED '
SET @sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [' + @idxTableName + ']' + CHAR(13) + '('
SET @sSQL = @sSQL + CHAR(13)
SET @colCount = 0

SELECT @fillfactor = fill_factor
FROM sys.indexes 
WHERE name = @idxname

IF ISNULL(@fillfactor, 0) = 0
SET @fillfactor = 90

-- Get the number of cols in the index 
SELECT @colCount = COUNT(*)
FROM sys.index_columns ic
INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
WHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

-- Get the file group info 
SELECT @location = f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.object_id = @idxTableID AND i.index_id = @idxid

-- Get all columns of the index
DECLARE curidxcolumn CURSOR
FOR
SELECT sc.column_id AS columnidintable
,sc.NAME
,ic.index_column_id columnidinindex
,ic.is_included_column AS isincludedcolumn
FROM sys.index_columns ic
INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
WHERE ic.object_id = @idxTableID AND index_id = @idxid
ORDER BY ic.index_column_id

SET @IxFirstColumn = 1
SET @sIncludeCols = ''
SET @sIndexCols = ''
SET @rowcnt = 0

OPEN curidxColumn

FETCH NEXT
FROM curidxColumn
INTO @ColumnIDInTable
,@IxColumn
,@ColumnIDInIndex
,@IsIncludedColumn

--loop 
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @IsIncludedColumn = 0
BEGIN
SET @rowcnt = @rowcnt + 1
SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @IxColumn + ']'

-- Check the sort order of the index cols 
IF (INDEXKEY_PROPERTY(@idxTableID, @idxid, @ColumnIDInIndex, 'IsDescending')) = 0
SET @sIndexCols = @sIndexCols + ' ASC '
ELSE
SET @sIndexCols = @sIndexCols + ' DESC '

IF @rowcnt < @colCount
SET @sIndexCols = @sIndexCols + ', '
END
ELSE
BEGIN
-- Check for any include columns
IF len(@sIncludeCols) > 0
SET @sIncludeCols = @sIncludeCols + ','
SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'

END

FETCH NEXT
FROM curidxColumn
INTO @ColumnIDInTable
,@IxColumn
,@ColumnIDInIndex
,@IsIncludedColumn
END

CLOSE curidxColumn

DEALLOCATE curidxColumn

--append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '

-- Build the options
SET @sParamSQL = ' WITH (FILLFACTOR = ' + cast(isnull(@fillfactor, 90) AS VARCHAR(3)) + ', '

--set @sParamSQL = ' WITH ('
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1)
SET @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, '

IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, '

IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = OFF, '

IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF, '

SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = OFF ) '
SET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL + ' ON [' + @location + ']' + CHAR(13) + 'END ' + CHAR(10) + 'GO' + CHAR(13)

PRINT @sIndexCols

FETCH NEXT
FROM curidx
INTO @idxTableName
,@idxTableID
,@idxname
,@idxid
END

CLOSE curidx
DEALLOCATE curidx
sql-server

1 respostas

  1. Best Answer

    Eu uso este script para obter detalhes sobre índices, incluindo índices não clusterizados. Ele fornece um bom nível de detalhes, que usamos para reorganizar/reconstruir índices.

    SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , IndexName = QUOTENAME(i.name)
    , PadIndex = CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END
    , [FillFactor] = 'FILLFACTOR = ' + CONVERT(VARCHAR(50), CASE WHEN i.fill_factor = 0 THEN 100 ELSE i.fill_factor END)
    , [AllowPageLocks] = 'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END
    , [AllowRowLocks] = 'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END
    , [DataSpace] = 'ON ' + ds.name
    , IgnoreDupeKey = 'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END
    , CompressOption = 'DATA_COMPRESSION = ' + p.data_compression_desc
    , PartitionNumber = p.partition_number
    , NumberOfFragments = ips.fragment_count
    , NumberOfPages = ips.page_count
    , AvgFragmentSizeInPages = ips.avg_fragment_size_in_pages
    , AvgFragmentationInPercent = ips.avg_fragmentation_in_percent
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
    , IsPrimary = i.is_primary_key
    , IsPartitioned = CASE WHEN ps.data_space_id IS NULL THEN 0 ELSE 1 END
    , IsClustered = CASE WHEN i.type = 1 THEN 1 ELSE 0 END
FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p ON o.object_id = p.object_id
                                    AND i.index_id = p.index_id
    LEFT JOIN sys.partition_schemes ps ON ds.data_space_id = ps.data_space_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, p.partition_number, 'SAMPLED') ips
WHERE o.is_ms_shipped = 0
    AND NOT (
        o.type = 'TF' -- table valued function
        OR o.type = 'TT' -- table type
        OR o.type = 'SO' -- sequence object
        )
    AND i.index_id > 0
    AND i.is_disabled = 0
    AND i.is_hypothetical = 0
    AND (
        ds.type = 'FG' -- filegroup
        OR ds.type = 'PS' -- partition stream
        ) 
    AND i.type_desc IN 
        ( 
            'CLUSTERED'
            , 'NONCLUSTERED'
            , 'XML'
        )
ORDER BY QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , QUOTENAME(i.name);

    Os detalhes retornados são assim:

    +------------------+------------------------------ ----+-----------------+------------------+------------ ---------------+-----------+----------- --------+----------------------+------------------ -------+-----------------+-------------------+---- -------+------------------------+------------- --------------+-----------+----------+-----------+ ---------------+-------------+
| ObjectName | IndexName | PadIndex | Fator de Preenchimento | AllowPageLocks | AllowRowLocks | DataSpace | IgnoreDupeKey | Comprimir Opção | PartiçãoNúmero | NumberOfFragments | NúmeroDePáginas | AvgFragmentSizeInPages | AvgFragmentationInPercent | Tipo de índice | éÚnico | IsPrimary | EstáParticionado | IsClustered |
+------------------+------------------------------ ----+-----------------+------------------+------------ ---------------+-----------+----------- --------+----------------------+------------------ -------+-----------------+-------------------+---- -------+------------------------+------------- --------------+-----------+----------+-----------+ ---------------+-------------+
| [dbo].[PartTest] | [PK__PartTest__3214EC275AC4DD06] | PAD_INDEX = OFF | FATOR DE PREENCHIMENTO = 100 | ALLOW_PAGE_LOCKS = ATIVADO | ALLOW_ROW_LOCKS = ATIVADO | ON PartSchemeTest | IGNORE_DUP_KEY = OFF | DATA_COMPRESSION = NENHUM | 1 | 0 | 0 | 0 | 0 | AGRUPADO | 1 | 1 | 1 | 1 |
| [dbo].[PartTest] | [PK__PartTest__3214EC275AC4DD06] | PAD_INDEX = OFF | FATOR DE PREENCHIMENTO = 100 | ALLOW_PAGE_LOCKS = ATIVADO | ALLOW_ROW_LOCKS = ATIVADO | ON PartSchemeTest | IGNORE_DUP_KEY = OFF | DATA_COMPRESSION = NENHUM | 2 | 14 | 100 | 7.14285714285714 | 2 | AGRUPADO | 1 | 1 | 1 | 1 |
| [dbo].[PartTest] | [PK__PartTest__3214EC275AC4DD06] | PAD_INDEX = OFF | FATOR DE PREENCHIMENTO = 100 | ALLOW_PAGE_LOCKS = ATIVADO | ALLOW_ROW_LOCKS = ATIVADO | ON PartSchemeTest | IGNORE_DUP_KEY = OFF | DATA_COMPRESSION = NENHUM | 3 | 15 | 100 | 6.66666666666667 | 3 | AGRUPADO | 1 | 1 | 1 | 1 |
| [dbo].[PartTest] | [PK__PartTest__3214EC275AC4DD06] | PAD_INDEX = OFF | FATOR DE PREENCHIMENTO = 100 | ALLOW_PAGE_LOCKS = ATIVADO | ALLOW_ROW_LOCKS = ATIVADO | ON PartSchemeTest | IGNORE_DUP_KEY = OFF | DATA_COMPRESSION = NENHUM | 5 | 1909 | 14880 | 7.79465688842326 | 0,376344086021505 | AGRUPADO | 1 | 1 | 1 | 1 |
| [dbo].[PartTest] | [PK__PartTest__3214EC275AC4DD06] | PAD_INDEX = OFF | FATOR DE PREENCHIMENTO = 100 | ALLOW_PAGE_LOCKS = ATIVADO | ALLOW_ROW_LOCKS = ATIVADO | ON PartSchemeTest | IGNORE_DUP_KEY = OFF | DATA_COMPRESSION = PÁGINA | 4 | 6 | 50 | 8.33333333333333 | 4 | AGRUPADO | 1 | 1 | 1 | 1 |
+------------------+------------------------------ ----+-----------------+------------------+------------ ---------------+-----------+----------- --------+----------------------+------------------ -------+-----------------+-------------------+---- -------+------------------------+------------- --------------+-----------+----------+-----------+ ---------------+-------------+
    • 1

relate perguntas