Requête de toutes les données de la table et de l'indice de compression

Quelqu'un arrive d'avoir un générique instruction SQL qui vous liste toutes les tables et les index dans une base de données, avec leur paramètre de compression, pour chaque partition?

Grâce.

EDIT: voilà Ce que j'ai reçu dans ma tentative de tables de requêtes, mais je ne suis pas sûr que la jointure est correct (je suis en présence de doublons, ce qui semble être causé par la présence d'index)

SELECT [t].[name], [p].[partition_number], [p].[data_compression_desc]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
Vous pourriez avoir écrit dans le temps que vous avez attendu une réponse. 🙂 Vous savez à propos de sys.partitions, sys.index, etc. droit?
Je ne sais pas, je suis un fast typer. J'ai eu un aller, mais tbh je ne suis pas convaincu de la relations entre les tables que vous avez mentionné.
Vous devez inclure ce que vous avez essayé dans votre question; il montre aux autres que vous avez mis dans un certain effort et ne sont pas juste de demander de code (ou de demander à quelqu'un pour écrire votre code pour vous).

OriginalL'auteur Barguast | 2013-06-07

  1. 26

    J'ai pensé que je devais partager ma dernière question. Cette vais vous donner deux ensembles de résultats, le premier de la table de compression de données, et le second index de la table de compression.

    SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

SELECT [t].[name] AS [Table], [i].[name] AS [Index],  
    [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] > 1
    Pour une bonne explication de cette réponse voir cette réponse: stackoverflow.com/a/21262509/61989
    Il ne liste pas PK pour moi? Est-ce normal?

    OriginalL'auteur Barguast

  2. 7

    Alors que je pense alors que le final de requêtes posté par Barguast de travail, il y a toujours un problème avec eux, quelque chose qui n'est pas expliqué assez bien.

    Fondamentalement un index_id de 0 est un tas, 1 est un index cluster et 2 est tout le reste (index non cluster).

    Le problème avec les requêtes, c'est que la requête pour les données ne fonctionnera pas si la table est un tas (même si il y a des données dans la table). Aussi la requête de l'index fonctionne parce que vous spécifiez le index_Id = 2 et il y a des dupes en raison de ne pas se joindre à la index_id entre sys.indexes et sys.partitions. Si vous vous joignez à ceux alors il n'y aura pas de doublons dans le jeu de résultats et vous pouvez faire beaucoup plus compréhensible index_id not in (0,1).

    De toute façon fixe les requêtes sont ci-dessous. J'ai également ajouté le nom de l'index à la première requête (notez que ce champ est null si la table est un segment de mémoire). Notez également que vous n'avez pas à spécifier le rejoindre pour index_id sur la première requête, parce que le where spécifie (0,1) et il ne peut qu'être l'un de ceux (en d'autres mots vous pouvez ajouter si vous le souhaitez, mais il ne fait pas de différence).

    -- Data (table) compression (heap or clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

-- Index compression (non-clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],  
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id] AND i.index_id = p.index_id
WHERE [p].[index_id] not in (0,1)

    OriginalL'auteur ElementZero

  3. 6

    Ces réponses sont toutes décent et de travail. Depuis que j'ai embelli un peu pour mon travail, j'ai pensé que c'était l'heure pour contribuer un peu. Cette requête ajoute le schéma à partir de la réponse de Jason (dont j'avais besoin). et il trie également certains de la jointure des questions et regroupe les résultats dans un assez simple résumé.

    -- Returns user tables and indexes in a DB and their Compression state
select s.name [Schema], t.name [Table], i.name [Index], p.data_compression_desc Compression
     , case when p.index_id in (0, 1) then 'Table' else 'Index' end CompressionObject
  from sys.tables t
  join sys.schemas s on t.schema_id = s.schema_id
  join sys.indexes i on t.object_id = i.object_id
  join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id)
where t.type = 'U'
order by 1, 2, p.index_id, 3

    J'ai utilisé cela comme une "liste des travaux" pour générer des scripts pour compresser tout depuis que je viens de soulever de décalage de la db dans un Azure VM et de vouloir réduire les IOPS pour améliorer les perf. Espérons que cela aide quelqu'un là-bas.

    OriginalL'auteur Trevor Carnahan

  4. 2

    Cela devrait faire le travail, de le tester pour un petit sous-ensemble pour être sûr qu'il vous donne ce dont vous avez besoin

    SELECT DISTINCT s.name [Schema], t.name [Table], i.name [Index Name], p.partition_number, p.data_compression_desc
-- uncommenting the below line will give you dupes
--, p.index_id
FROM sys.schemas s
INNER JOIN sys.tables t
    ON s.schema_id = t.schema_id
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    INNER JOIN sys.partitions p
        ON t.object_id = p.object_id
ORDER BY s.name, t.name

    La raison pour laquelle vous êtes probablement obtenir des dupes est parce que vous avez plusieurs partition d'enregistrements par table, par exemple, plusieurs index_id, voir ce MSDN article pour obtenir des précisions sur ce que l'index_id est-à-dire. L'ajout d'un DISTINCTES devrait résoudre le problème des dupes

    OriginalL'auteur Jason