{"id":499,"date":"2025-07-16T12:57:46","date_gmt":"2025-07-16T03:57:46","guid":{"rendered":"https:\/\/sejiwon.com\/?p=499"},"modified":"2025-07-16T12:57:46","modified_gmt":"2025-07-16T03:57:46","slug":"row-vs-page-%ec%95%95%ec%b6%95-%eb%b9%84%ea%b5%90-%eb%a6%ac%ed%8f%ac%ed%8a%b8-%ec%83%9d%ec%84%b1-%ec%8a%a4%ed%81%ac%eb%a6%bd%ed%8a%b8","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=499","title":{"rendered":"ROW vs PAGE \uc555\ucd95 \ube44\uad50 \ub9ac\ud3ec\ud2b8 \uc0dd\uc131 \uc2a4\ud06c\ub9bd\ud2b8"},"content":{"rendered":"\n

MSSQL\uc5d0\uc11c \ud14c\uc774\ube14 \ub610\ub294 \uc778\ub371\uc2a4\uc5d0 \ub300\ud574 ROW \uc555\ucd95 vs PAGE \uc555\ucd95 \ud6a8\uacfc\ub97c \ube44\uad50\ud558\ub294 \ub9ac\ud3ec\ud2b8 \uc0dd\uc131 \uc2a4\ud06c\ub9bd\ud2b8<\/strong>\ub294 \uacf5\uc2dd \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800\uc778 sp_estimate_data_compression_savings<\/code><\/a> \uc744 \uae30\ubc18\uc73c\ub85c \uc791\uc131\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n

\n\n\n\n

\u2705 \ud83d\udcca ROW vs PAGE \uc555\ucd95 \ube44\uad50 \ub9ac\ud3ec\ud2b8 \uc2a4\ud06c\ub9bd\ud2b8<\/h2>\n\n\n\n

\uc544\ub798\ub294 \ud14c\uc774\ube14 \ub0b4 \ubaa8\ub4e0 \uc778\ub371\uc2a4 \ubc0f \ud30c\ud2f0\uc158\uc5d0 \ub300\ud574 \uc555\ucd95 \uc804\ud6c4 \uacf5\uac04 \uc808\uc57d\ub7c9<\/strong>\uc744 \uacc4\uc0b0\ud558\uace0, ROW \/ PAGE \uc555\ucd95 \ud6a8\uacfc\ub97c \ube44\uad50<\/strong>\ud558\ub294 T-SQL \uc2a4\ud06c\ub9bd\ud2b8\uc785\ub2c8\ub2e4.<\/p>\n\n\n\n

\ud83d\udd27 \uc804\uccb4 \ud14c\uc774\ube14 \ub300\uc0c1 (\ud30c\ud2f0\uc158 \uc804\uccb4 \ud3ec\ud568):<\/h3>\n\n\n\n
DECLARE @schema_name SYSNAME = 'dbo';\nDECLARE @table_name SYSNAME = 'YourTableName';\n\nIF OBJECT_ID('tempdb..#CompressionResults') IS NOT NULL\n    DROP TABLE #CompressionResults;\n\nCREATE TABLE #CompressionResults (\n    Index_ID INT,\n    Partition_Number INT,\n    Compression_Type VARCHAR(10),\n    Size_Current_KB BIGINT,\n    Size_Estimated_KB BIGINT,\n    Size_Saved_KB AS (Size_Current_KB - Size_Estimated_KB)\n);\n\nDECLARE @index_id INT, @partition_number INT, @sql NVARCHAR(MAX);\n\n-- \uc778\ub371\uc2a4\uc640 \ud30c\ud2f0\uc158 \uc815\ubcf4\ub97c \ub8e8\ud504\nDECLARE cur CURSOR FOR\nSELECT i.index_id, p.partition_number\nFROM sys.indexes i\nJOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id\nWHERE i.object_id = OBJECT_ID(QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name))\nGROUP BY i.index_id, p.partition_number;\n\nOPEN cur;\nFETCH NEXT FROM cur INTO @index_id, @partition_number;\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    -- ROW \uc555\ucd95\n    INSERT INTO #CompressionResults(Index_ID, Partition_Number, Compression_Type, Size_Current_KB, Size_Estimated_KB)\n    EXEC sp_estimate_data_compression_savings\n        @schema_name = @schema_name,\n        @object_name = @table_name,\n        @index_id = @index_id,\n        @partition_number = @partition_number,\n        @data_compression = 'ROW';\n\n    -- PAGE \uc555\ucd95\n    INSERT INTO #CompressionResults(Index_ID, Partition_Number, Compression_Type, Size_Current_KB, Size_Estimated_KB)\n    EXEC sp_estimate_data_compression_savings\n        @schema_name = @schema_name,\n        @object_name = @table_name,\n        @index_id = @index_id,\n        @partition_number = @partition_number,\n        @data_compression = 'PAGE';\n\n    FETCH NEXT FROM cur INTO @index_id, @partition_number;\nEND\n\nCLOSE cur;\nDEALLOCATE cur;\n\n-- \uacb0\uacfc \ub9ac\ud3ec\ud2b8 \ucd9c\ub825\nSELECT \n    Index_ID,\n    Partition_Number,\n    Compression_Type,\n    Size_Current_KB,\n    Size_Estimated_KB,\n    Size_Saved_KB,\n    CAST(100.0 * Size_Saved_KB \/ NULLIF(Size_Current_KB, 0) AS DECIMAL(5,2)) AS Saved_Percent\nFROM #CompressionResults\nORDER BY Partition_Number, Index_ID, Compression_Type;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
\ud83d\udccb \uacb0\uacfc \uc608\uc2dc (\ucd9c\ub825)<\/h2>\n\n\n\n
Index_ID<\/th>Partition_Number<\/th>Compression_Type<\/th>Size_Current_KB<\/th>Size_Estimated_KB<\/th>Size_Saved_KB<\/th>Saved_Percent<\/th><\/tr><\/thead>
1<\/td>1<\/td>ROW<\/td>100000<\/td>85000<\/td>15000<\/td>15.00%<\/td><\/tr>
1<\/td>1<\/td>PAGE<\/td>100000<\/td>60000<\/td>40000<\/td>40.00%<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
\u2705 \ucd94\uac00 \ud301<\/h2>\n\n\n\n