안녕하세요? 쓸만한게없네 윤선식입니다.
이전 아티클에서 “Database Engine Tuning Advisor”에 대한 소개를 했는데요, 이후 작업에 대해 설명하려고 합니다.
이 DTA 참 좋긴 한데, 한 가지 문제가 있습니다. 바로 MSDB에 모든 기록이 남는다는 것이죠.
1. 다음과 같은 후유증이 있습니다.
가. MSDB에 DTA 관련 테이블이 그대로 존재합니다. 특히 DTA_tuninglog 테이블등의 Row수는 튜닝대상 쿼리가 많으면 많을수록 사이즈가 클 수밖에 없습니다.
나. 백업파일의 사이즈가 커집니다. 그림과 같이 갑자기 사이즈가 커진 것을 알 수 있습니다.
다. 그렇다면 MSDB의 사이즈는??? 사실 Job을 그리 많이 돌리는 Database가 아니기 때문에 이렇게까지 클 이유가 없죠.
2. 굳이 이를 보관할 필요가 있다면 별도의 DB에 보관하시고, 만약 보관의 필요가 없다면 다음 링크에 있는 명령어를 실행해 봅니다.
http://support.microsoft.com/kb/899634
|
/* Purpose of the Script This script cleans up objects created by DTA client on the target server (server being tuned). DTA creates support tables and stored procedures on the target server. The schema of the DTA tables and the DTA SP interfaces changed from Beta 2. When to use it If a Beta 2 DTA client was used to tune/evaluate against the target server then this script needs to be executed (against the target server) for later versions of DTA to function properly. Impact Previous session details are lost. */ go use msdb go -- Drop DTA msdb Tables if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_indexcolumn') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_indexcolumn if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querycolumn') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_querycolumn if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querytable') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_querytable if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_tableview') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_tableview if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querydatabase') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_querydatabase if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_queryindex') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_queryindex if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_column') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_column if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_index') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_index if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_table') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_table if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_query') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_query if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_partitionscheme') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_partitionscheme if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_partitionfunction') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_partitionfunction if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_tuninglog') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_tuninglog if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_database') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_database if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_progress') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_progress if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_output')and (type = 'U')and (uid = user_id('dbo')))) drop table dbo.DTA_output if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_input') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_input -- Drop DTA msdb SP's if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_check_permission' and type = 'P') drop procedure dbo.sp_DTA_check_permission if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_add_session' and type = 'P') drop procedure dbo.sp_DTA_add_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_delete_session' and type = 'P') drop procedure dbo.sp_DTA_delete_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_session_tuning_results' and type = 'P') drop procedure dbo.sp_DTA_get_session_tuning_results if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_interactivestatus' and type = 'P') drop procedure dbo.sp_DTA_set_interactivestatus if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_help_session' and type = 'P') drop procedure dbo.sp_DTA_help_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_update_session' and type = 'P') drop procedure dbo.sp_DTA_update_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tuninglog' and type = 'P') drop procedure dbo.sp_DTA_get_tuninglog if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_usage_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_index_usage_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_usage_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_index_usage_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_database_access_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_database_access_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_database_access_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_database_access_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_table_access_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_table_access_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_table_access_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_table_access_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_column_access_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_column_access_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_column_access_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_column_access_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_costrange_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_costrange_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_costrange_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_costrange_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_cost_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_cost_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_cost_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_cost_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_event_weight_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_event_weight_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_event_weight_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_event_weight_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_detail_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_detail_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_detail_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_detail_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_indexrelations_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_indexrelations_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_indexrelations_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_indexrelations_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_current_detail_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_index_current_detail_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_recommended_detail_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_index_recommended_detail_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_detail_current_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_index_detail_current_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_detail_recommended_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_index_detail_recommended_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_view_table_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_view_table_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_view_table_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_view_table_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_wkld_analysis_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_wkld_analysis_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_wkld_analysis_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_wkld_analysis_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_session_report' and type = 'P') drop procedure dbo.sp_DTA_get_session_report if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_tuninglogtablename' and type = 'P') drop procedure dbo.sp_DTA_set_tuninglogtablename if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tuningoptions' and type = 'P') drop procedure dbo.sp_DTA_get_tuningoptions if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_interactivestatus' and type = 'P') drop procedure dbo.sp_DTA_get_interactivestatus if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_progressinformation' and type = 'P') drop procedure dbo.sp_DTA_insert_progressinformation if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_progressinformation' and type = 'P') drop procedure dbo.sp_DTA_set_progressinformation if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_outputinformation' and type = 'P') drop procedure dbo.sp_DTA_set_outputinformation if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_database' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_database if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_partitionscheme' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_partitionscheme if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_partitionfunction' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_partitionfunction if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_column' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_column if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_tableview' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_tableview if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_query' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_query if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_index' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_index if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_table' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_table if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_queryindex' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_queryindex if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_indexcolumn' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_indexcolumn if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querytable' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_querytable if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querydatabase' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_querydatabase if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querycolumn' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_querycolumn if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_DTA_tuninglog' and type = 'P') drop procedure dbo.sp_DTA_insert_DTA_tuninglog if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_databasetableids' and type = 'P') drop procedure dbo.sp_DTA_get_databasetableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_pftableids' and type = 'P') drop procedure dbo.sp_DTA_get_pftableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_pstableids' and type = 'P') drop procedure dbo.sp_DTA_get_pstableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tableids' and type = 'P') drop procedure dbo.sp_DTA_get_tableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_columntableids' and type = 'P') drop procedure dbo.sp_DTA_get_columntableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_indexableids' and type = 'P') drop procedure dbo.sp_DTA_get_indexableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_update_tuninglog_errorfrequency' and type = 'P') drop procedure dbo.sp_DTA_update_tuninglog_errorfrequency -- Drop unused SP's if they exist. if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_columnaccesshelper' and type = 'P') drop procedure dbo.sp_DTA_columnaccesshelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_databaseaccesshelper' and type = 'P') drop procedure dbo.sp_DTA_databaseaccesshelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_indexusagehelper' and type = 'P') drop procedure dbo.sp_DTA_indexusagehelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_queryCRhelper' and type = 'P') drop procedure dbo.sp_DTA_queryCRhelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_tableaccesshelper' and type = 'P') drop procedure dbo.sp_DTA_tableaccesshelper |
3. 실행 후엔 어떤 변화가 있을까요?
가. 구문을 보면 아시겠지만, DTA 관련 테이블을 모두 DROP합니다. 하여 아무런 User Table도 남아있지 않게 됩니다.
나. MSDB 사이즈는 어떻게 될까요? 사용 가능한 공간이 924MB 중 892MB나 되네요.
다. 이제 Database Shrink(축소)를 해 보도록 합니다.
라. 짜잔… 다음과 같이 22.5MB로 사이즈가 줄게 됩니다.
DTA를 사용할 때 반드시 알아두어야 할 것 같네요.