{"id":496,"date":"2008-04-04T09:15:11","date_gmt":"2008-04-04T08:15:11","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=496"},"modified":"2008-04-04T09:15:11","modified_gmt":"2008-04-04T08:15:11","slug":"sql-server-2008-ujdonsagok-31-adatlapok-tomoritett-tarolasa-ii","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/04\/04\/sql-server-2008-ujdonsagok-31-adatlapok-tomoritett-tarolasa-ii\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 31. – Adatlapok t\u00f6m\u00f6r\u00edtett t\u00e1rol\u00e1sa II."},"content":{"rendered":"

N\u00e9zz\u00fcnk konkr\u00e9t p\u00e9ld\u00e1kat, mekkora nyeres\u00e9get \u00e9rhet\u00fcnk el a t\u00f6m\u00f6r\u00edt\u00e9ssel (elm\u00e9let ez el\u0151z\u0151 r\u00e9szben<\/a>).<\/p>\n

N\u00e9zz\u00fck meg egy t\u00e1bla m\u00e9ret\u00e9t, majd l\u00e1ssuk, mi lesz bel\u0151le row \u00e9s page compression ut\u00e1n (legal\u00e1bbis mit j\u00f3solnak)?<\/p>\n

\r\nexec sp_spaceused 'Production.TransactionHistoryArchive'\r\nexec sp_estimate_data_compression_savings 'Production', 'TransactionHistoryArchive', NULL, NULL, 'row'\r\nexec sp_estimate_data_compression_savings 'Production', 'TransactionHistoryArchive', NULL, NULL, 'page'\r\n<\/pre>\n
\r\nname                           rows        reserved           data               index_size         unused\r\n------------------------------ ----------- ------------------ ------------------ ------------------ ------------------\r\nTransactionHistoryArchive      89253       8224 KB            4992 KB            2776 KB            456 KB\r\n\r\n\r\nobject_name                    schema_name                    index_id    partition_number size_with_current_compression_ size_with_requested_compressio sample_size_with_current_compr sample_size_with_requested_com\r\n------------------------------ ------------------------------ ----------- ---------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------\r\nTransactionHistoryArchive      Production                     1           1                5136                           3240                           5008                           3160\r\nTransactionHistoryArchive      Production                     2           1                1144                           968                            1024                           872\r\nTransactionHistoryArchive      Production                     3           1                1488                           1240                           1728                           1448\r\n\r\nobject_name                    schema_name                    index_id    partition_number size_with_current_compression_ size_with_requested_compressio sample_size_with_current_compr sample_size_with_requested_com\r\n------------------------------ ------------------------------ ----------- ---------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------\r\nTransactionHistoryArchive      Production                     1           1                5136                           1680                           5008                           1640\r\nTransactionHistoryArchive      Production                     2           1                1144                           816                            1024                           736\r\nTransactionHistoryArchive      Production                     3           1                1488                           1144                           1728                           1336\r\n\r\n<\/pre>\n
Mit l\u00e1tunk? Az alapban 5.1 meg\u00e1s adatb\u00f3l \u00e9s rajta lev\u0151 2.7 meg\u00e1nyi indexb\u0151l row compressionnel 3.2 \u00e9s 2.2 mega lesz. Az adat kb. a fel\u00e9re megy \u00f6ssze, ami nem rossz, hisz row compressionr\u0151l van sz\u00f3, ami nagyon gyors. Az indexet nem tudta \u00fagy \u00f6sszenyomni, val\u00f3sz\u00edn\u0171leg az indexekben lev\u0151 int adatok jelent\u0151s r\u00e9sze 2 byte hosszan t\u00e1rolhat\u00f3 csak el, \u00edgy csak fel\u00e9re nyomhat\u00f3 \u00f6ssze.<\/p>\n
Lap szint\u0171 t\u00f6m\u00f6r\u00edt\u00e9sn\u00e9l az 5.1 meg\u00e1s adatok csak 1.7 meg\u00e1t foglalnak el, azaz 3x t\u00f6m\u00f6r\u00edt\u00e9st kapunk. Az indexek m\u00e9rete 2.7-r\u0151l 1.95 meg\u00e1ra esik vissza, nem sokkal kisebbre, mint csak sor t\u00f6m\u00f6r\u00edt\u00e9ssel. Sz\u00f3val indexn\u00e9l eset\u00fcnkben nem sokat \u00e9rt egyik m\u00f3dszer sem, az adatok jellege miatt. \u00c9n lehet, hogy indexn\u00e9l sor, adatn\u00e1l pedig lap szint\u0171 t\u00f6m\u00f6r\u00edt\u00e9st haszn\u00e1ln\u00e9k.<\/p>\n
Az adatok t\u00e9nyleges t\u00f6m\u00f6r\u00edt\u00e9se lap szinten:<\/p>\n
\r\nalter table Production.TransactionHistoryArchive \r\nrebuild with (data_compression = page);\r\n<\/pre>\n
N\u00e9zz\u00fck meg, mit nyer\u00fcnk? Tudni kell, att\u00f3l, hogy bekapcsoljuk valamely t\u00f6m\u00f6r\u00edt\u00e9st, m\u00e9g d\u00f6nthet \u00fagy a szerver, hogy bizonyos lapokat nem t\u00f6m\u00f6r\u00edt, mert esetleg nagyobb lenne az eredm\u00e9ny, mint a forr\u00e1s (a zip, rar, stb. programok is \u00edgy tesznek). N\u00e9zz\u00fck meg a p\u00e9ld\u00e1nkban mi a helyzet?<\/p>\n
\r\nselect \r\nindex_id,\r\nindex_level,\r\nindex_type_desc,\r\npage_count,\r\ncompressed_page_count,\r\n(select top 1 name from sys.indexes si where si.object_id = s.object_id and si.index_id = s.index_id) IndexName\r\nfrom \r\nsys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), object_id('Production.TransactionHistoryArchive'), NULL, NULL, 'DETAILED') s\r\norder by s.index_id, s.index_level desc\r\n<\/pre>\n
\r\nindex_id    index_level index_type_desc                                    page_count           compressed_page_count IndexName\r\n----------- ----------- -------------------------------------------------- -------------------- --------------------- --------------------------------------------------\r\n1           1           CLUSTERED INDEX                                    1                    0                     PK_TransactionHistoryArchive_TransactionID\r\n1           0           CLUSTERED INDEX                                    203                  203                   PK_TransactionHistoryArchive_TransactionID\r\n2           1           NONCLUSTERED INDEX                                 1                    0                     IX_TransactionHistoryArchive_ProductID\r\n2           0           NONCLUSTERED INDEX                                 125                  0                     IX_TransactionHistoryArchive_ProductID\r\n3           1           NONCLUSTERED INDEX                                 1                    0                     IX_TransactionHistoryArchive_ReferenceOrderID_Refe\r\n3           0           NONCLUSTERED INDEX                                 168                  0                     IX_TransactionHistoryArchive_ReferenceOrderID_Refe\r\n<\/pre>\n
L\u00e1that\u00f3, hogy a clustered index 1. szintje, az index gy\u00f6k\u00e9r lapja nem page t\u00f6m\u00f6r\u00edtett (kor\u00e1bban \u00edrtam ,mi\u00e9rt), de a 203 lev\u00e9lszint\u0171 lap, azaz az adatlapok mind t\u00f6m\u00f6r\u00edtettek.
\nL\u00e1that\u00f3 az is, hogy az indexek egy\u00e1ltal\u00e1n nincsenek t\u00f6m\u00f6r\u00edtve, az alter table clu index eset\u00e9n csak arra vonatkozik, az nc indexekre nem. Ha azokat is t\u00f6m\u00f6r\u00edteni akarjuk, alter index kell: <\/p>\n
\r\nalter index IX_TransactionHistoryArchive_ProductID \r\non Production.TransactionHistoryArchive\r\nrebuild with (data_compression = page);\r\n<\/pre>\n
Ennek hat\u00e1s\u00e1ra a kor\u00e1bbi lek\u00e9rdez\u00e9s kimenete:<\/p>\n
\r\n2           1           NONCLUSTERED INDEX                                 1                    0                     IX_TransactionHistoryArchive_ProductID\r\n2           0           NONCLUSTERED INDEX                                 90                   89                    IX_TransactionHistoryArchive_ProductID\r\n<\/pre>\n
Szinte minden indexlap page compressed lett, csak 1 maradt row (a gy\u00f6k\u00e9ren k\u00edv\u00fcl).<\/p>\n
Hasonl\u00edtsuk \u00f6ssze a teljes t\u00e1bla kiolvas\u00e1s\u00e1nak IO k\u00f6lts\u00e9g\u00e9t a page t\u00f6m\u00f6r\u00edt\u00e9ssel \u00e9s sim\u00e1n:<\/p>\n
\r\nset statistics io on\r\nselect * from Production.TransactionHistoryArchive\r\n\r\nalter table Production.TransactionHistoryArchive \r\nrebuild with (data_compression = none);\r\n\r\nselect * from Production.TransactionHistoryArchive\r\n\r\nset statistics io off\r\n<\/pre>\n
\r\n(89253 row(s) affected)\r\nTable 'TransactionHistoryArchive'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n(89253 row(s) affected)\r\nTable 'TransactionHistoryArchive'. Scan count 1, logical reads 622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n<\/pre>\n
Harmad\u00e1ra esik vissza az IO, a h\u00e1romszoros t\u00f6m\u00f6r\u00edt\u00e9s miatt.<\/p>\n
A m\u00f3dos\u00edt\u00e1sok k\u00f6lts\u00e9g\u00e9re gyakorolt k\u00e1ros hat\u00e1st HF-k\u00e9nt feladom a kedves olvas\u00f3knak. :)<\/p>\n
\u00d6sszegezve, a t\u00f6m\u00f6r\u00edt\u00e9s egy igen kellemes szolg\u00e1ltat\u00e1s, amely hatalmas t\u00e1bl\u00e1k eset\u00e9n jelent\u0151sen cs\u00f6kkentheti az IO k\u00f6lts\u00e9get, \u00edgy ha az a sz\u0171k keresztmetszet, akkor nem csak spr\u00f3rol a viny\u00f3val, de m\u00e9g gyors\u00edt is.<\/p>\n
Hangs\u00falyozom viszont, hogy OLTP jelleg\u0171 t\u00e1bl\u00e1kn\u00e1l sokat ronthat, pont ez\u00e9rt sz\u00e1nd\u00e9kosan nincs olyan sp a szerverben, ami minden t\u00e1bl\u00e1ra \u00e9s indexre bekapcsoln\u00e1 a t\u00f6m\u00f6r\u00edt\u00e9st, mert sok ember majom m\u00f3dj\u00e1ra zipelne. :) Sajnos persze m\u00e1r van fenn az interneten ilyesmi sp, nem is linkelem be.<\/p>\n
\u00c9s v\u00e9g\u00fcl egy kellemetlen h\u00edr: a f\u00edcs\u00f6r csak az enterprise verzi\u00f3ban lesz benne. Valahogy motiv\u00e1lni kellett az elad\u00e1sokat, ez el\u00e9gg\u00e9 er\u0151s \u00e9rv a \u0171bergalaktikus verzi\u00f3 mellett.<\/p>\n","protected":false},"excerpt":{"rendered":"
N\u00e9zz\u00fcnk konkr\u00e9t p\u00e9ld\u00e1kat, mekkora nyeres\u00e9get \u00e9rhet\u00fcnk el a t\u00f6m\u00f6r\u00edt\u00e9ssel (elm\u00e9let ez el\u0151z\u0151 r\u00e9szben). N\u00e9zz\u00fck meg egy t\u00e1bla m\u00e9ret\u00e9t, majd l\u00e1ssuk, mi lesz…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,58],"tags":[],"class_list":["post-496","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/496","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=496"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/496\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=496"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=496"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}