Datawarehouse i SQL server – komprimering av index

Om du har en datawarehouse lösning i SQL server 2008 Enterprise edition så tjänar du oftast både diskyta och prestanda genom att aktivera page compression på dina index. Genom komprimering så sparar du diskyta genom att fler poster får plats per “sida” och därmed tar varje tabell upp färre “sidor”. Prestandan ökar vid läsning genom att du behöver läsa upp färre sidor och du får plats med fler poster i minnet vilket gör att mer data stannar i cachen och mindre data behöver läsas upp på nytt efter att ha tömts ur cachen.

Vill du veta hur mycket diskyta du skulle spara genom att aktivera komprimering så finns det möjlighet att estimera detta genom SSMS eller genom en procedur. Om du använder SSMS så blir det ganska mycket manuellt jobb för att estimera alla index på alla tabeller i din databas. För att underlätta estimeringen så kan du skapa ett skript som listar alla okomprimerade index och sql koden för att anropa proceduren som estimerar komprimeringsgraden.

Här ser du ett exempel på hur du kan använda dig av sys.partitions och sys.indexes för att lista alla aktiva index och dess partitioner med mer än 1000 poster som inte är komprimerade. SELECT OBJECT_NAME(P.OBJECT_ID) TableName, I.NAME IndexName, p.[rows] , ‘EXEC sp_estimate_data_compression_savings ”’ + OBJECT_SCHEMA_NAME(P.OBJECT_ID) + ”’, ”’ + OBJECT_NAME(P.OBJECT_ID) + ”’ , ‘ + CONVERT(VARCHAR(10),P.[index_id]) + ‘,’ + CONVERT(VARCHAR(5),p.[partition_number]) +’, ”PAGE”’ FROM sys.[partitions] AS P INNER JOIN sys.indexes i ON [P].[object_id] = [i].[object_id] AND [P].[index_id] = [i].[index_id] WHERE [P].[data_compression]=0 AND OBJECT_SCHEMA_NAME(P.OBJECT_ID) <>’SYS’ AND p.[rows] >1000 AND i.[is_disabled]=0

I den sista kolumnen i resultatet så finns kod för att anropa sp_estimate_data_compression_savings för respektive index och partition. Den koden kan man kopiera in i ett nytt fönster och exekvera för att lista hur mycket man skulle kunna spara per index. Vill man automatisera ytterligare så skulle man kunna loopa resultatet ifrån mitt skript och exekvera koden med sp_executeSQL så slipper du även att kopiera koden. Resultatet kan du spara i en tabell så att du kan lista den totala besparingen för samtliga index.

Om du har några frågor om SQL server så är du välkommen att kontakta någon av våra SQL server konsulter så hjälper vi dig.