{"id":492,"date":"2025-07-16T12:47:42","date_gmt":"2025-07-16T03:47:42","guid":{"rendered":"https:\/\/sejiwon.com\/?p=492"},"modified":"2025-07-16T12:48:16","modified_gmt":"2025-07-16T03:48:16","slug":"mssql-%ec%95%95%ec%b6%95-%ec%8a%a4%ed%81%ac%eb%a6%bd%ed%8a%b8%ec%99%80-%ec%98%88%ec%a0%9c-%eb%b0%8f-%ed%8c%8c%ed%8b%b0%ec%85%98%eb%b3%84-%ec%95%95%ec%b6%95-%ec%9e%90%eb%8f%99%ed%99%94-%eb%b0%a9","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=492","title":{"rendered":"MSSQL \uc555\ucd95 \uc2a4\ud06c\ub9bd\ud2b8\uc640 \uc608\uc81c \ubc0f \ud30c\ud2f0\uc158\ubcc4 \uc555\ucd95 \uc790\ub3d9\ud654 \ubc29\ubc95"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">\u2705 1. \ud30c\ud2f0\uc158\ubcc4 \uc555\ucd95 \uae30\ubcf8 \uc2a4\ud06c\ub9bd\ud2b8 \uc608\uc81c<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83c\udfaf \ubaa9\ud45c: \ud2b9\uc815 \ud30c\ud2f0\uc158\ub9cc PAGE \uc555\ucd95\ud558\uae30<\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>ALTER TABLE dbo.YourTableName\nREBUILD PARTITION = 5  -- 5\ubc88 \ud30c\ud2f0\uc158\nWITH (DATA_COMPRESSION = PAGE);\n<\/code><\/pre>\n\n\n\n<p>\ud83d\udccc \ud30c\ud2f0\uc158 \ubc88\ud638\ub294 \ud30c\ud2f0\uc158 \ud568\uc218\uc758 \uc815\uc758\uc5d0 \ub530\ub77c \ub2e4\ub984. \uc544\ub798\uc5d0\uc11c \ud655\uc778\ud558\ub294 \ubc29\ubc95 \uc124\uba85\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 2. \ud30c\ud2f0\uc158 \uc815\ubcf4 \uc870\ud68c<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u25b6 \ud30c\ud2f0\uc158 \ubc88\ud638\uc640 \ubc94\uc704 \ud655\uc778 \uc2a4\ud06c\ub9bd\ud2b8:<\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    ps.partition_number,\n    prv.value AS RangeValue,\n    p.rows AS [RowCount]\nFROM \n    sys.partitions p\n    JOIN sys.objects o ON p.object_id = o.object_id\n    JOIN sys.indexes i ON i.object_id = o.object_id AND p.index_id = i.index_id\n    JOIN sys.partition_schemes pscheme ON i.data_space_id = pscheme.data_space_id\n    JOIN sys.partition_functions pf ON pscheme.function_id = pf.function_id\n    LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id\n    JOIN sys.partition_stats ps ON ps.object_id = p.object_id AND ps.index_id = p.index_id AND ps.partition_number = p.partition_number\nWHERE \n    o.name = 'YourTableName'\n    AND i.index_id IN (0,1)  -- Heap \ub610\ub294 \ud074\ub7ec\uc2a4\ud130\ub4dc \uc778\ub371\uc2a4\nORDER BY \n    ps.partition_number;\n<\/code><\/pre>\n\n\n\n<p>\ud83d\udd0e <code>partition_number<\/code>\ub97c \ud655\uc778 \ud6c4, \ud574\ub2f9 \ubc88\ud638\uc5d0 \ub300\ud574 \uc555\ucd95\uc744 \uc218\ud589\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 3. \ud2b9\uc815 \uae30\uac04(\uc608: \uc9c0\ub09c\ub2ec) \ud30c\ud2f0\uc158\ub9cc \uc555\ucd95\ud558\uae30<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u25b6 \uc608: \ud30c\ud2f0\uc158\uc774 \uc6d4 \ub2e8\uc704\uc77c \ub54c, \uc9c0\ub09c\ub2ec \ub370\uc774\ud130 \uc555\ucd95<\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>DECLARE @partition_number INT = 5;  -- \uc608: 5\ubc88 \ud30c\ud2f0\uc158\uc774 \uc9c0\ub09c\ub2ec\n\nALTER TABLE dbo.YourTableName\nREBUILD PARTITION = @partition_number\nWITH (DATA_COMPRESSION = PAGE);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 4. \uc790\ub3d9\ud654: \ud30c\ud2f0\uc158\ubcc4 \uc555\ucd95 \uc2a4\ud06c\ub9bd\ud2b8 \ub3d9\uc801 \uc0dd\uc131 \ubc0f \uc2e4\ud589<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u25b6 \ubaa8\ub4e0 \ud30c\ud2f0\uc158 \ub300\uc0c1 \uc555\ucd95 \uc2a4\ud06c\ub9bd\ud2b8 \uc790\ub3d9 \uc0dd\uc131<\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>DECLARE @TableName NVARCHAR(128) = 'YourTableName';\nDECLARE @SQL NVARCHAR(MAX) = '';\n\nSELECT \n    @SQL += '\n    ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '\n    REBUILD PARTITION = ' + CAST(p.partition_number AS VARCHAR) + '\n    WITH (DATA_COMPRESSION = PAGE);\n    '\nFROM \n    sys.partitions p\n    JOIN sys.objects o ON p.object_id = o.object_id\n    JOIN sys.schemas s ON o.schema_id = s.schema_id\nWHERE \n    o.name = @TableName\n    AND p.index_id IN (0,1)\nGROUP BY \n    s.name, o.name, p.partition_number;\n\n-- \ucd9c\ub825 \ub610\ub294 \uc2e4\ud589\nPRINT @SQL;\n-- EXEC sp_executesql @SQL; -- \uc2e4\uc81c \uc801\uc6a9 \uc2dc \uc0ac\uc6a9\n<\/code><\/pre>\n\n\n\n<p>\ud83d\udd04 **\uc2a4\ucf00\uc904\ub7ec(SQL Agent)**\ub97c \ud1b5\ud574 \uc815\uae30\uc801\uc73c\ub85c \uc774 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc2e4\ud589\ud558\uba74 \uc790\ub3d9\ud654 \uac00\ub2a5<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 5. \uc790\ub3d9\ud654 \uc804\ub7b5 \uc81c\uc548 (\uc2e4\ubb34\uc801 \uc811\uadfc)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udccc \ud30c\ud2f0\uc158 \uc2a4\uc704\uce6d + \uc555\ucd95 (\uc608: \uc6d4\ub9d0 \uc790\ub3d9 \uc791\uc5c5)<\/h3>\n\n\n\n<ol>\n<li>\uc2e0\uaddc \ub370\uc774\ud130 \u2192 \uc555\ucd95 \uc5c6\ub294 \ud30c\ud2f0\uc158 \ud14c\uc774\ube14 (\uc2a4\ud14c\uc774\uc9d5 \ud14c\uc774\ube14) \uc5d0 \uc800\uc7a5<\/li>\n\n\n\n<li>\uc6d4\ub9d0 \uc2a4\ucf00\uc904\ub7ec:\n<ul>\n<li>\ud574\ub2f9 \uc6d4 \ud30c\ud2f0\uc158 \uc555\ucd95<\/li>\n\n\n\n<li>\ud30c\ud2f0\uc158 \uc2a4\uc704\uce6d\ud558\uc5ec \uba54\uc778 \ud14c\uc774\ube14\ub85c \uc774\ub3d9<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p>\uc608\uc81c:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- 1. \uc555\ucd95\ub41c \ud14c\uc774\ube14 \uc900\ube44 (\uad6c\uc870 \ub3d9\uc77c, \uc555\ucd95 \uc801\uc6a9)\nCREATE TABLE dbo.ArchivePartition (\n    ...\n) ON ps_MonthlyPartitionScheme(MonthColumn);\n\n-- \uc555\ucd95\nALTER TABLE dbo.ArchivePartition \nREBUILD PARTITION = 5 WITH (DATA_COMPRESSION = PAGE);\n\n-- 2. \uc2a4\uc704\uce6d\nALTER TABLE dbo.YourTableName SWITCH PARTITION 5 \nTO dbo.ArchivePartition PARTITION 5;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u26a0\ufe0f \uc8fc\uc758\uc0ac\ud56d<\/h2>\n\n\n\n<ul>\n<li>\uc555\ucd95\uc740 <strong>CPU \ube44\uc6a9\uc774 \ud07c<\/strong>: \uac00\ub2a5\ud558\uba74 \uc5c5\ubb34 \uc2dc\uac04 \uc678 \uc2e4\ud589<\/li>\n\n\n\n<li>\ud074\ub7ec\uc2a4\ud130\ub4dc \uc778\ub371\uc2a4\uac00 \uc788\uc5b4\uc57c <code>REBUILD PARTITION<\/code> \uac00\ub2a5<\/li>\n\n\n\n<li><code>sp_estimate_data_compression_savings<\/code>\ub85c \uc555\ucd95 \ud6a8\uacfc \ubbf8\ub9ac \ubd84\uc11d \uad8c\uc7a5<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83e\uddea \uc555\ucd95 \ud6a8\uacfc \ubbf8\ub9ac \ubcf4\uae30<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>EXEC sp_estimate_data_compression_savings  \n    @schema_name = 'dbo',\n    @object_name = 'YourTableName',  \n    @index_id = 1,  -- \ud074\ub7ec\uc2a4\ud130\ub4dc \uc778\ub371\uc2a4 ID\n    @partition_number = 5,  \n    @data_compression = 'PAGE';  \n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n","protected":false},"excerpt":{"rendered":"<p>\u2705 1. \ud30c\ud2f0\uc158\ubcc4 \uc555\ucd95 \uae30\ubcf8 \uc2a4\ud06c\ub9bd\ud2b8 \uc608\uc81c \ud83c\udfaf \ubaa9\ud45c: \ud2b9\uc815 \ud30c\ud2f0\uc158\ub9cc PAGE \uc555\ucd95\ud558\uae30 ALTER TABLE dbo.YourTableName REBUILD PARTITION = 5 &#8212; 5\ubc88 \ud30c\ud2f0\uc158 WITH (DATA_COMPRESSION = PAGE); \ud83d\udccc \ud30c\ud2f0\uc158 \ubc88\ud638\ub294 \ud30c\ud2f0\uc158 \ud568\uc218\uc758 \uc815\uc758\uc5d0 \ub530\ub77c \ub2e4\ub984. \uc544\ub798\uc5d0\uc11c \ud655\uc778\ud558\ub294 \ubc29\ubc95 \uc124\uba85\ud569\ub2c8\ub2e4. \u2705 2. \ud30c\ud2f0\uc158 \uc815\ubcf4 \uc870\ud68c \u25b6 \ud30c\ud2f0\uc158 \ubc88\ud638\uc640 \ubc94\uc704 \ud655\uc778 \uc2a4\ud06c\ub9bd\ud2b8: SELECT ps.partition_number, prv.value AS RangeValue,\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=492\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11],"tags":[],"_links":{"self":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/492"}],"collection":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=492"}],"version-history":[{"count":2,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/492\/revisions"}],"predecessor-version":[{"id":495,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/492\/revisions\/495"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}