{"id":369828,"date":"2024-05-21T04:12:04","date_gmt":"2024-05-21T04:12:04","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=369828"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=369828","title":{"rendered":"MSSQL: \u0441\u0440\u0430\u0432\u043d\u0438\u0432\u0430\u0435\u043c data compression \u0438 backup compression<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

MSSQL \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u0442 \u043a\u043e\u043c\u043f\u0440\u0435\u0441\u0441\u0438\u044e \u0431\u044d\u043a\u0430\u043f\u043e\u0432 \u043d\u0430 \u043b\u0435\u0442\u0443 — \u043b\u0435\u0433\u043a\u043e\u0432\u0435\u0441\u043d\u0443\u044e \u0438 \u0431\u044b\u0441\u0442\u0440\u0443\u044e. \u0422\u0430\u043a\u0436\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u043c\u043e\u0436\u043d\u043e \u0443\u043f\u0430\u043a\u043e\u0432\u0430\u0442\u044c \u0432\u043d\u0443\u0442\u0440\u0438 \u0431\u0430\u0437\u044b \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e DATA_COMPRESSION = PAGE \u0438\u043b\u0438 ROW. \u041a\u0430\u043a \u043c\u044b \u043f\u043e\u043c\u043d\u0438\u043c, \u0443\u043f\u0430\u043a\u043e\u0432\u0430\u043d\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u043f\u043b\u043e\u0445\u043e \u043f\u0430\u043a\u0443\u044e\u0442\u0441\u044f. \u041a\u0430\u043a \u0443\u043f\u0430\u043a\u043e\u0432\u043a\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u043f\u043e\u0432\u043b\u0438\u044f\u0435\u0442 \u043d\u0430 \u0440\u0430\u0437\u043c\u0435\u0440 \u0431\u044d\u043a\u0430\u043f\u0430?<\/p>\n

\u0422\u0435\u0441\u0442\u043e\u0432\u0430\u044f \u0431\u0430\u0437\u0430<\/h2>\n

\u0421\u043e\u0437\u0434\u0430\u0435\u043c \u0431\u043e\u043b\u044c\u0448\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0447\u043a\u0443 test \u0432 \u0447\u0438\u0441\u0442\u043e\u0439 \u0431\u0430\u0437\u0435 (\u043d\u0435 \u0437\u0430\u0431\u044b\u0432\u0430\u0435\u043c \u043f\u0435\u0440\u0435\u0432\u0435\u0441\u0442\u0438 \u0435\u0435 \u0432 SIMPLE):<\/p>\n

create table seed (n int) GO set nocount on declare @n int=9999 while @n>=0 begin   insert into seed select @n   set @n=@n-1   end GO create table test (n int identity, a int, b int, str varchar(128)) GO create clustered index PK_n on test (n) GO insert into test (a,b,str)   select L.n,R.n,'seed '+convert(varchar,L.n)+' and '+convert(varchar,R.n)     from seed L, seed R GO create index IXa on test (a) GO create index IXb on test (b) GO create index IXstr on test (str) GO <\/code><\/pre>\n
\u0417\u0430\u043f\u0438\u0448\u0435\u043c \u0440\u0430\u0437\u043c\u0435\u0440\u044b \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0438 \u0431\u044d\u043a\u0430\u043f\u043e\u0432 — \u0443\u043f\u0430\u043a\u043e\u0432\u0430\u043d\u043d\u043e\u0433\u043e \u0438 \u043e\u0431\u044b\u0447\u043d\u043e\u0433\u043e. \u0417\u0430\u0442\u0435\u043c \u043f\u0440\u0438\u043c\u0435\u043d\u0438\u043c \u043a\u043e \u0432\u0441\u0435\u043c \u0438\u043d\u0434\u0435\u043a\u0441\u0430\u043c DATA_COMPRESSION = PAGE<\/p>\n
alter index PK_n on test REBUILD with (data_compression=PAGE) alter index IXa on test REBUILD with (data_compression=PAGE) alter index IXb on test REBUILD with (data_compression=PAGE) alter index IXstr on test REBUILD with (data_compression=PAGE)<\/code><\/pre>\n
\u0438 \u0441\u043d\u043e\u0432\u0430 \u0432\u0441\u0435 \u0437\u0430\u043f\u0438\u0448\u0435\u043c.<\/p>\n
\u041d\u0430\u043a\u043e\u043d\u0435\u0446, \u043f\u0440\u0438\u043c\u0435\u043d\u0438\u043c DATA_COMPRESSION = ROW<\/p>\n
alter index PK_n on test REBUILD with (data_compression=ROW) alter index IXa on test REBUILD with (data_compression=ROW) alter index IXb on test REBUILD with (data_compression=ROW) alter index IXstr on test REBUILD with (data_compression=ROW)<\/code><\/pre>\n
\u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u044b<\/h2>\n
\u0412\u0441\u0435 \u0440\u0430\u0437\u043c\u0435\u0440\u044b \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u044b \u0432 Mb<\/p>\n
\n
\n\n\n\n\n\n\n\n
\n
Index<\/p>\n<\/td>\n
\n
no compression<\/p>\n<\/td>\n
\n
compression PAGE<\/p>\n<\/td>\n
\n
compression ROW<\/p>\n<\/td>\n<\/tr>\n
\n
PK_n<\/p>\n<\/td>\n
\n
4355<\/p>\n<\/td>\n
\n
1992<\/p>\n<\/td>\n
\n
3646<\/p>\n<\/td>\n<\/tr>\n
\n
IXa<\/p>\n<\/td>\n
\n
1355<\/p>\n<\/td>\n
\n
854<\/p>\n<\/td>\n
\n
1152<\/p>\n<\/td>\n<\/tr>\n
\n
IXb<\/p>\n<\/td>\n
\n
1355<\/p>\n<\/td>\n
\n
963<\/p>\n<\/td>\n
\n
1152<\/p>\n<\/td>\n<\/tr>\n
\n
IXstr<\/p>\n<\/td>\n
\n
3091<\/p>\n<\/td>\n
\n
1239<\/p>\n<\/td>\n
\n
3174<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
\u0422\u0435\u043f\u0435\u0440\u044c \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0438\u043c \u0440\u0430\u0437\u043c\u0435\u0440 full backups, \u0442\u043e\u0436\u0435 \u0432 \u043c\u0435\u0433\u0430\u0431\u0430\u0439\u0442\u0430\u0445:<\/p>\n
\n
\n\n\n\n\n\n
\n
Backup<\/p>\n<\/td>\n
\n
no compression<\/p>\n<\/td>\n
\n
compression PAGE<\/p>\n<\/td>\n
\n
compression ROW<\/p>\n<\/td>\n<\/tr>\n
\n
no compression<\/p>\n<\/td>\n
\n
10417<\/p>\n<\/td>\n
\n
5189<\/p>\n<\/td>\n
\n
9364<\/p>\n<\/td>\n<\/tr>\n
\n
compression<\/p>\n<\/td>\n
\n
1934<\/p>\n<\/td>\n
\n
2331<\/p>\n<\/td>\n
\n
2285<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
\u0412\u044b\u0432\u043e\u0434\u044b<\/h2>\n
\u0410\u043c\u0435\u0440\u0438\u043a\u0443 \u044f \u043d\u0435 \u043e\u0442\u043a\u0440\u044b\u043b, \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u044b \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u043e\u0436\u0438\u0434\u0430\u0435\u043c\u044b\u0435, \u043d\u043e \u0445\u043e\u0442\u0435\u043b\u043e\u0441\u044c \u0443\u0431\u0435\u0434\u0438\u0442\u044c\u0441\u044f \u0435\u0449\u0435 \u0440\u0430\u0437<\/p>\n