MZ@ !L!This program cannot be run in DOS mode. $-TٽL:L:L:FL:BL:RichL:PEL5zK! O@x`.rsrcx@@0H`0H`x  8 P h (@X p             0 @ P ` p       ( Et@MDTc>inF8u0h|0  ((ص 0Z0 ~A DTA_TABLES DTA_SPROCS/***************************************************************/ /* Make sure we are int the right database */ /***************************************************************/ use msdb go PRINT '----------------------------------' PRINT 'Starting execution of DTA_tables.SQL' PRINT '----------------------------------' go /**************************************************************/ /* DTA_input */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_input') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_input...' CREATE TABLE dbo.DTA_input ( SessionName sysname not null, SessionID int identity primary key, TuningOptions ntext not null, CreationTime datetime not null default GetDate(), ScheduledStartTime datetime not null default GetDate(), ScheduledJobName sysname not null default '', InteractiveStatus tinyint not null default 0, LogTableName nvarchar(1280) not null default '', GlobalSessionID uniqueidentifier default NEWID() ) END go /**************************************************************/ /* DTA_progress */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_progress') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_progress...' CREATE TABLE dbo.DTA_progress ( ProgressEventID int identity, SessionID int references DTA_input(SessionID) on delete cascade, TuningStage tinyint not null default 0, WorkloadConsumption tinyint not null check (WorkloadConsumption>=0 and WorkloadConsumption<=100), EstImprovement int not null default 0, ProgressEventTime datetime not null default GetDate(), ConsumingWorkLoadMessage nvarchar(256), PerformingAnalysisMessage nvarchar(256), GeneratingReportsMessage nvarchar(256) ) CREATE CLUSTERED INDEX DTA_progress_index on DTA_progress(SessionID) END go /**************************************************************/ /* DTA_output */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_output') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_output...' CREATE TABLE dbo.DTA_output ( SessionID int not null primary key references DTA_input(SessionID) on delete cascade, TuningResults ntext not null, StopTime datetime not null default GetDate(), FinishStatus tinyint not null default 0 ) END go /**************************************************************/ /* DTA_tuninglog */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_tuninglog') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_tuninglog...' CREATE TABLE dbo.DTA_tuninglog ( SessionID int not null references DTA_input(SessionID) on delete cascade, RowID int not null, CategoryID nvarchar(4) not null, Event ntext null, Statement ntext null, Frequency int not null, Reason ntext null ) CREATE CLUSTERED INDEX DTA_tuninglog_index on DTA_tuninglog(SessionID,RowID) END go /**************************************************************/ /* DTA_reports_database */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_database') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_database...' CREATE TABLE dbo.DTA_reports_database ( DatabaseID int identity primary key, SessionID int not null references DTA_input(SessionID) on delete cascade, DatabaseName sysname not null, IsDatabaseSelectedToTune int ) CREATE INDEX DTA_reports_database_index on DTA_reports_database(SessionID) END go /**************************************************************/ /* DTA_reports_partitionfunction */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_partitionfunction') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_partitionfunction...' CREATE TABLE dbo.DTA_reports_partitionfunction ( PartitionFunctionID int identity primary key, DatabaseID int not null references DTA_reports_database(DatabaseID) on delete cascade, PartitionFunctionName sysname not null, PartitionFunctionDefinition ntext not null ) CREATE INDEX DTA_reports_partitionfunction_index on DTA_reports_partitionfunction(DatabaseID) END go /**************************************************************/ /* DTA_reports_partitionscheme */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_partitionscheme') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_partitionscheme...' CREATE TABLE dbo.DTA_reports_partitionscheme ( PartitionSchemeID int identity primary key, PartitionFunctionID int not null references DTA_reports_partitionfunction(PartitionFunctionID) on delete cascade, PartitionSchemeName sysname not null, PartitionSchemeDefinition ntext not null ) CREATE INDEX DTA_reports_partitionscheme_index on DTA_reports_partitionscheme(PartitionFunctionID) END go /**************************************************************/ /* DTA_reports_table */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_table') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_table...' CREATE TABLE dbo.DTA_reports_table ( TableID int identity primary key, DatabaseID int not null references DTA_reports_database(DatabaseID) on delete cascade, SchemaName sysname not null, TableName sysname not null, IsView bit not null default 0 ) CREATE INDEX DTA_reports_table_index on DTA_reports_table(DatabaseID) END go /**************************************************************/ /* DTA_reports_tableview */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_tableview') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_tableview...' CREATE TABLE dbo.DTA_reports_tableview ( TableID int not null references DTA_reports_table(TableID) on delete cascade, ViewID int not null references DTA_reports_table(TableID) ) CREATE CLUSTERED INDEX DTA_reports_tableview_index on DTA_reports_tableview(TableID) CREATE INDEX DTA_reports_tableview_index2 on DTA_reports_tableview(ViewID) END go /**************************************************************/ /* DTA_reports_query */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_query') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_query...' CREATE TABLE dbo.DTA_reports_query ( QueryID int not null, SessionID int not null references DTA_input(SessionID) on delete cascade, StatementType smallint not null, StatementString ntext not null, CurrentCost float not null, RecommendedCost float not null, Weight float not null, EventString ntext, EventWeight float not null ) ALTER TABLE DTA_reports_query ADD CONSTRAINT DTA_reports_query_pk PRIMARY KEY (SessionID,QueryID) END go /**************************************************************/ /* DTA_reports_querytable */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_querytable') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_querytable...' CREATE TABLE dbo.DTA_reports_querytable ( QueryID int not null, SessionID int not null, TableID int not null references DTA_reports_table(TableID) ) ALTER TABLE DTA_reports_querytable ADD CONSTRAINT DTA_reports_querytable_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE CREATE CLUSTERED INDEX DTA_reports_querytable_index on DTA_reports_querytable(SessionID,QueryID) CREATE INDEX DTA_reports_querytable_index2 on DTA_reports_querytable(TableID) END go /**************************************************************/ /* DTA_reports_querydatabase */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_querydatabase') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_querydatabase...' CREATE TABLE dbo.DTA_reports_querydatabase ( QueryID int not null, SessionID int not null, DatabaseID int not null references DTA_reports_database(DatabaseID) ) ALTER TABLE DTA_reports_querydatabase ADD CONSTRAINT DTA_reports_querydatabase_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE CREATE CLUSTERED INDEX DTA_reports_querydatabase_index on DTA_reports_querydatabase(SessionID,QueryID) CREATE INDEX DTA_reports_querydatabase_index2 on DTA_reports_querydatabase(DatabaseID) END go /**************************************************************/ /* DTA_reports_index */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_index') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_index...' CREATE TABLE dbo.DTA_reports_index ( IndexID int identity primary key, TableID int not null references DTA_reports_table(TableID) on delete cascade, IndexName sysname not null, IsClustered bit not null default 0, IsUnique bit not null default 0, IsHeap bit not null default 1, IsExisting bit not null default 1, IsFiltered bit not null default 0, Storage float not null, NumRows bigint not null, IsRecommended bit not null default 0, RecommendedStorage float not null, PartitionSchemeID int, SessionUniquefier int, FilterDefinition nvarchar(1024) not null ) CREATE INDEX DTA_reports_indexindex on DTA_reports_index(TableID) CREATE INDEX DTA_reports_indexindex2 on DTA_reports_index(PartitionSchemeID) END go /**************************************************************/ /* DTA_reports_queryindex */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_queryindex') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_queryindex...' CREATE TABLE dbo.DTA_reports_queryindex ( QueryID int not null, SessionID int not null, IndexID int not null references DTA_reports_index(IndexID), IsRecommendedConfiguration bit not null ) ALTER TABLE DTA_reports_queryindex ADD CONSTRAINT DTA_reports_queryindex_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE CREATE CLUSTERED INDEX DTA_reports_queryindex_index on DTA_reports_queryindex(SessionID,QueryID) CREATE INDEX DTA_reports_queryindex_index2 on DTA_reports_queryindex(IndexID) END go /**************************************************************/ /* DTA_reports_column */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_column') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_column...' CREATE TABLE dbo.DTA_reports_column ( ColumnID int identity primary key, TableID int not null references DTA_reports_table(TableID) on delete cascade, ColumnName sysname not null ) CREATE INDEX DTA_reports_column_index on DTA_reports_column(TableID) END go /**************************************************************/ /* DTA_reports_indexcolumn */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_indexcolumn') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_indexcolumn...' CREATE TABLE dbo.DTA_reports_indexcolumn ( IndexID int not null references DTA_reports_index(IndexID) on delete cascade, ColumnID int not null, ColumnOrder int, PartitionColumnOrder int not null default 0, IsKeyColumn bit not null default 1, IsDescendingColumn bit not null default 1 ) CREATE CLUSTERED INDEX DTA_reports_indexcolumn_index on DTA_reports_indexcolumn(IndexID) CREATE INDEX DTA_reports_indexcolumn_index2 on DTA_reports_indexcolumn(ColumnID) END go /**************************************************************/ /* DTA_reports_querycolumn */ /* */ /**************************************************************/ IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'DTA_reports_querycolumn') AND (type = 'U') AND (uid = USER_ID('dbo')))) BEGIN PRINT '' PRINT 'Creating table DTA_reports_querycolumn...' CREATE TABLE dbo.DTA_reports_querycolumn ( QueryID int not null, SessionID int not null, ColumnID int not null references DTA_reports_column(ColumnID) ) ALTER TABLE DTA_reports_querycolumn ADD CONSTRAINT DTA_reports_querycolumn_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE CREATE CLUSTERED INDEX DTA_reports_querycolumn_index on DTA_reports_querycolumn(SessionID,QueryID) CREATE INDEX DTA_reports_querycolumn_index2 on DTA_reports_querycolumn(ColumnID) END go /**************************************************************************** // Copyright (c) Microsoft Corporation. // // @File: DTA_sprocs.sql // // Purpose: // Script for DTA/DTAEngine90 communication // // Notes: // // History: // // // @EndHeader@ *****************************************************************************/ use msdb go PRINT '----------------------------------' PRINT 'Starting execution of DTA_sprocs.SQL' PRINT '----------------------------------' go /***************************************************************************** sp_DTA_check_permission @SessionID, - ID of a session to check Checks if caller has enough permissions If caller in SA role then returns 0 (allow) If caller in DB role and if caller owns session then returns 0 (allow) Always called as a prolog to any external SP's *****************************************************************************/ print '' print 'Creating procedure sp_DTA_check_permission...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_check_permission' and type = 'P') drop procedure sp_DTA_check_permission go create procedure sp_DTA_check_permission @SessionID int as begin declare @retcode int declare @dbname nvarchar(128) declare @sql nvarchar(256) declare @dbid int declare @ServVersion nvarchar(128) set nocount on set @retcode = 1 -- Check if SA if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) begin return(0) end -- if SQL Server 2000 return select @ServVersion=CONVERT(nvarchar(128), serverproperty(N'ProductVersion')) if (patindex('8.%',@ServVersion) > 0) begin return (1) end -- declare and open a cursor and get all the databases specified in the input declare db_cursor cursor for select DatabaseName from DTA_reports_database where SessionID = @SessionID and IsDatabaseSelectedToTune = 1 -- open open db_cursor -- fetch first db name fetch next from db_cursor into @dbname -- loop and get all the databases selected to tune while @@fetch_status = 0 begin -- build use db string select @dbid = DB_ID(@dbname) -- set @retcode to OK. Will be set to 1 in case of issues set @retcode = 0 -- In Yukon this masks the error messages set @sql = N'begin try dbcc autopilot(5,@dbid) WITH NO_INFOMSGS end try begin catch set @retcode = 1 end catch' execute sp_executesql @sql , N'@dbid int output, @retcode int OUTPUT' , @dbid output , @retcode output -- if caller is not member of dbo if (@retcode = 1) begin -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor return(1) end fetch from db_cursor into @dbname end -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor -- if caller is not member of dbo if (@retcode = 1) begin return(1) end return(0) end go grant exec on sp_DTA_check_permission to public go /***************************************************************************** sp_DTA_add_session @SessionName, - name of the session nvarchar[30] @TuningOptions, - input xml @SessionID OUTPUT  return value, id of the new session. *****************************************************************************/ print '' print 'Creating procedure sp_DTA_add_session...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_add_session' and type = 'P') drop procedure sp_DTA_add_session go create procedure sp_DTA_add_session @SessionName sysname, @TuningOptions ntext, @SessionID int OUTPUT as declare @UserName as nvarchar(256) declare @x_SessionName sysname declare @ErrorString nvarchar(500) declare @XmlDocumentHandle int declare @retval int declare @dbcount int set nocount on begin transaction -- Check for duplicate session name select @x_SessionName = @SessionName from msdb.dbo.DTA_input where SessionName = @SessionName if (@x_SessionName IS NOT NULL) begin rollback transaction set @ErrorString = 'The session ' + '"' + LTRIM(RTRIM(@SessionName)) + '"' +' already exists. Please use a different session name.' raiserror (31001, -1,-1,@SessionName) return(1) end -- Create new session insert into msdb.dbo.DTA_input (SessionName,TuningOptions) values (@SessionName,@TuningOptions) select @SessionID = @@identity if @@error <> 0 begin rollback transaction return @@error end if @@error <> 0 begin rollback transaction return @@error end -- Create an internal representation of the XML document. EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @TuningOptions, '<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://schemas.microsoft.com/sqlserver/2004/07/dta"/>' if @@error <> 0 begin rollback transaction return @@error end -- Execute a SELECT statement using OPENXML rowset provider. insert into DTA_reports_database SELECT @SessionID,msdb.dbo.fn_DTA_unquote_dbname([x:Name]),1 FROM OPENXML (@XmlDocumentHandle, '/x:DTAXML/x:DTAInput/x:Server//x:Database',2) WITH ([x:Name] nvarchar(128) ) if @@error <> 0 begin rollback transaction return @@error end EXEC sp_xml_removedocument @XmlDocumentHandle if @@error <> 0 begin rollback transaction return @@error end -- Check if allowed to add session exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31003,-1,-1) rollback transaction return (1) end select @dbcount = count(*) from DTA_reports_database where SessionID = @SessionID if @dbcount = 0 begin rollback transaction return (1) end -- Insert progress record insert into [msdb].[dbo].[DTA_progress] (SessionID,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage) values(@SessionID,0,0,0,N'',N'',N'') if @@error <> 0 begin rollback transaction return @@error end -- Commit if input/progress records are updated commit transaction return 0 go grant exec on sp_DTA_add_session to public go /***************************************************************************** sp_DTA_delete_session @SessionID - ID of a session to delete Deletes a session with a given ID and removes references to that session from all tables. *****************************************************************************/ print '' print 'Creating procedure sp_DTA_delete_session...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_delete_session' and type = 'P') drop procedure sp_DTA_delete_session go create procedure sp_DTA_delete_session @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end delete from msdb.dbo.DTA_input where SessionID=@SessionID end go grant exec on sp_DTA_delete_session to public go /***************************************************************************** sp_DTA_get_session_tuning_results @SessionID - ID of a session requested Returns the following rowset: TuningResults, FinishStatus, StopTime *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_session_tuning_results...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_session_tuning_results' and type = 'P') drop procedure sp_DTA_get_session_tuning_results go create procedure sp_DTA_get_session_tuning_results @SessionID int as begin set nocount on declare @retval int exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select FinishStatus,TuningResults from msdb.dbo.DTA_output where SessionID=@SessionID end go grant exec on sp_DTA_get_session_tuning_results to public go /***************************************************************************** sp_DTA_set_interactivestatus @InterActiveStatus - Interactive status in MSDB @SessionID - ID of a session requested Sets the interactive status in input table *****************************************************************************/ print '' print 'Creating procedure sp_DTA_set_interactivestatus...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_interactivestatus' and type = 'P') drop procedure sp_DTA_set_interactivestatus go create procedure sp_DTA_set_interactivestatus @InterActiveStatus int, @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end update [msdb].[dbo].[DTA_input] set InteractiveStatus = @InterActiveStatus where SessionID = @SessionID end go grant exec on sp_DTA_set_interactivestatus to public go /***************************************************************************** sp_DTA_help_session @SessionID - ID of a session to list, optional @IncludeTuningOptions - Flag that indicates that input XML Should be included in returned rowset. Help Session reports different book keeping info to DTA related to one session or all sessions if sa *****************************************************************************/ print '' print 'Creating procedure sp_DTA_help_session...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_help_session' and type = 'P') drop procedure sp_DTA_help_session go create procedure sp_DTA_help_session @SessionID int = 0, @IncludeTuningOptions int = 0 as begin declare @tuning_owner nvarchar(256) declare @retval int declare @InteractiveStatus tinyint declare @delta int declare @cursessionID int declare @dbname nvarchar(128) declare @dbid int declare @retcode int declare @sql nvarchar(256) set nocount on -- List all Sessions mode if @SessionID = 0 begin -- If sysadmin role then rowset has all the rows in the table -- Return everything if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) begin if (@IncludeTuningOptions = 0) begin select I.SessionID, I.SessionName, I.InteractiveStatus, I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O on I.SessionID = O.SessionID order by I.SessionID desc end else if (@IncludeTuningOptions = 1) begin select I.SessionID, I.SessionName, I.InteractiveStatus, I.CreationTime, I.ScheduledStartTime, O.StopTime,I.TuningOptions,I.GlobalSessionID from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output as O on I.SessionID = O.SessionID order by I.SessionID desc end end else begin -- Temporary table to store sessionid and databases passed in by user create table #allDistinctDbIds (DatabaseID int) -- Init variables set @dbid = 0 set @retcode = 1 -- Get all database names passed in by user (IsDatabaseSelectedToTune =1) declare db_cursor cursor for select distinct(DatabaseName) from DTA_reports_database where IsDatabaseSelectedToTune = 1 -- Open cursor open db_cursor -- Fetch first session id and db name fetch next from db_cursor into @dbname -- loop and get all the databases selected to tune while @@fetch_status = 0 -- Loop begin -- set @retcode = 1 in the beginning to indicate success set @retcode = 1 -- Get database id select @dbid = DB_ID(@dbname) -- In Yukon this masks the error messages.If not owner dont return -- error message in SP set @sql = N'begin try dbcc autopilot(5,@dbid) WITH NO_INFOMSGS end try begin catch set @dbid = 0 set @retcode = 0 end catch' execute sp_executesql @sql , N'@dbid int output, @retcode int OUTPUT' , @dbid output , @retcode output -- dbid is 0 if user doesnt have permission to do dbcc call insert into #allDistinctDbIds(DatabaseID) values (@dbid) -- fetch next fetch from db_cursor into @dbname -- end the cursor loop end -- clean up cursor close db_cursor deallocate db_cursor select SessionID into #allValidSessionIds from DTA_input as I where ((select count(*) from #allDistinctDbIds ,DTA_reports_database as D where #allDistinctDbIds.DatabaseID = DB_ID(D.DatabaseName) and I.SessionID = D.SessionID group by D.SessionID ) = (select count(*) from DTA_reports_database as D where I.SessionID = D.SessionID and D.IsDatabaseSelectedToTune = 1 group by D.SessionID ) ) group by I.SessionID -- Return only sessions with matching user name -- If count of rows with DatabaseID = 0 is > 0 then permission denied if ( @IncludeTuningOptions = 0 ) begin select I.SessionID , I.SessionName, I.InteractiveStatus, I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O on I.SessionID = O.SessionID inner join #allValidSessionIds S on I.SessionID = S.SessionID order by I.SessionID desc end else if (@IncludeTuningOptions = 1) begin select I.SessionID , I.SessionName, I.InteractiveStatus, I.CreationTime, I.ScheduledStartTime, O.StopTime,I.TuningOptions,I.GlobalSessionID from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O on I.SessionID = O.SessionID inner join #allValidSessionIds S on I.SessionID = S.SessionID order by I.SessionID desc end drop table #allDistinctDbIds drop table #allValidSessionIds end end else begin exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end if ( @IncludeTuningOptions = 0) begin select I.SessionID, I.SessionName, I.InteractiveStatus, I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O on I.SessionID = O.SessionID where I.SessionID = @SessionID end else if (@IncludeTuningOptions = 1) begin select I.SessionID, I.SessionName, I.InteractiveStatus, I.CreationTime, I.ScheduledStartTime, O.StopTime,I.TuningOptions,I.GlobalSessionID from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O on I.SessionID = O.SessionID where I.SessionID = @SessionID end -- Second rowset returned for DTA to process progress information select ProgressEventID,TuningStage,WorkloadConsumption,EstImprovement, ProgressEventTime ,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage from msdb.dbo.DTA_progress where SessionID=@SessionID order by ProgressEventID -- Set interactive status to 6 if a time of 5 mins has elapsed -- Next time help session is called DTA will exit select @InteractiveStatus=InteractiveStatus from msdb.dbo.DTA_input where SessionID = @SessionID if (@InteractiveStatus IS NOT NULL and( @InteractiveStatus <> 4 and @InteractiveStatus <> 6)) begin select @delta=DATEDIFF(minute ,ProgressEventTime,getdate()) from msdb.dbo.DTA_progress where SessionID =@SessionID order by TuningStage ASC if(@delta > 30) begin update [msdb].[dbo].[DTA_input] set InteractiveStatus = 6 where SessionID = @SessionID end end end end go grant exec on sp_DTA_help_session to public go /***************************************************************************** sp_dta_update_session @SessionID, - ID of a session to update [@SessionName,] - New session name (optional) [@Status] - New session status (optional) Possible values: 0  stop session 1  cancel session 2  start session (currently not used, reserved) At least one of the optional parameters must be provided. *****************************************************************************/ print '' print 'Creating procedure sp_DTA_update_session...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_update_session' and type = 'P') drop procedure sp_DTA_update_session go create procedure sp_DTA_update_session @SessionID int, @SessionName sysname = NULL, @InteractiveStatus tinyint = NULL as begin declare @x_SessionName sysname declare @x_InteractiveStatus tinyint declare @retval int declare @ErrorString nvarchar(500) set nocount on select @SessionName = LTRIM(RTRIM(@SessionName)) declare @dup_SessionName sysname if @SessionName IS NOT NULL begin select @dup_SessionName = @SessionName from msdb.dbo.DTA_input where SessionName = @SessionName if (@dup_SessionName IS NOT NULL) begin set @ErrorString = 'The session ' + '"' + LTRIM(RTRIM(@SessionName)) + '"' +' already exists. Please use a different session name.' raiserror (31001, -1,-1,@SessionName) return(1) end end exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end if ((@SessionName IS NOT NULL) OR (@InteractiveStatus IS NOT NULL) ) begin select @x_SessionName = SessionName, @x_InteractiveStatus = InteractiveStatus from msdb.dbo.DTA_input where SessionID = @SessionID if (@SessionName IS NULL) select @SessionName = @x_SessionName if (@InteractiveStatus IS NULL) select @InteractiveStatus = @x_InteractiveStatus update msdb.dbo.DTA_input set SessionName = @SessionName, InteractiveStatus = @InteractiveStatus where SessionID = @SessionID end end go grant exec on sp_DTA_update_session to public go /***************************************************************************** sp_DTA_get_tuninglog @SessionID - ID of a session requested Returns the following rowset: RowID,CategoryID,Event,Statement,Frequency,Reason *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_tuninglog...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tuninglog' and type = 'P') drop procedure sp_DTA_get_tuninglog go create procedure sp_DTA_get_tuninglog @SessionID int, @XML int = 0, @LastRowRetrieved int = 0, @GetFrequencyForRowIDOnly int = 0 as begin set nocount on declare @retval int declare @LogTableName nvarchar(1280) declare @DefaultTableName nvarchar(128) declare @SQLString nvarchar(2048) --CategoryID,Event,Statement,Frequency,Reason declare @localized_string_CategoryID nvarchar(128) declare @localized_string_Event nvarchar(128) declare @localized_string_Statement nvarchar(128) declare @localized_string_Frequency nvarchar(128) declare @localized_string_Reason nvarchar(128) set @localized_string_CategoryID = N'"CategoryID"' set @localized_string_Event = N'"Event"' set @localized_string_Statement = N'"Statement"' set @localized_string_Frequency = N'"Frequency"' set @localized_string_Reason = N'"Reason"' exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end set @DefaultTableName = '[msdb].[dbo].[DTA_tuninglog]' set @LogTableName = ' ' select top 1 @LogTableName = LogTableName from DTA_input where SessionID = @SessionID if (@LogTableName = ' ') return (0) if @XML = 0 begin if (@GetFrequencyForRowIDOnly = 0) begin set @SQLString ='select CategoryID as ' + @localized_string_CategoryID + ' ,Event as ' + @localized_string_Event + ' ,Statement as ' + @localized_string_Statement + ' ,Frequency as ' + @localized_string_Frequency + ' ,Reason as ' + @localized_string_Reason + ' from ' end else begin set @SQLString = N' select Frequency from ' end set @SQLString = @SQLString + @LogTableName set @SQLString = @SQLString + N' where SessionID = ' set @SQLString = @SQLString + CONVERT(nvarchar(10),@SessionID) set @SQLString = @SQLString + N' and RowID > ' set @SQLString = @SQLString + CONVERT(nvarchar(10),@LastRowRetrieved) set @SQLString = @SQLString + ' order by RowID' exec (@SQLString) end else begin if @LogTableName = @DefaultTableName begin if (@GetFrequencyForRowIDOnly = 0) begin select CategoryID,Event,Statement,Frequency,Reason from [msdb].[dbo].[DTA_tuninglog] where SessionID = @SessionID and RowID > @LastRowRetrieved FOR XML RAW end else begin select Frequency from [msdb].[dbo].[DTA_tuninglog] where SessionID = @SessionID and RowID > @LastRowRetrieved FOR XML RAW end return(0) end if (@GetFrequencyForRowIDOnly = 0) begin set @SQLString = N' select CategoryID,Event,Statement,Frequency,Reason from ' end else begin set @SQLString = N' select Frequency from ' end set @SQLString = @SQLString + @LogTableName set @SQLString = @SQLString + N' where SessionID = ' set @SQLString = @SQLString + CONVERT(nvarchar(10),@SessionID) set @SQLString = @SQLString + N' and RowID > ' set @SQLString = @SQLString + CONVERT(nvarchar(10),@LastRowRetrieved) set @SQLString = @SQLString + 'FOR XML RAW' exec (@SQLString) end end go grant exec on sp_DTA_get_tuninglog to public go /************************************************************** Following stored procs are helpers for different analysis reports generated by DTAEngine90. Depending on user input DTAEngine90 can generate both XML and relational (rowset) reports. The relational report is used by DTAShell to show reports in grids. XML reports are generated by FOR XML EXPLICIT. The schema is in DTASchema.xsd **************************************************************/ /**************************************************************/ /* Index Usage Helper XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_index_usage_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_usage_helper_xml' and type = 'P') drop procedure sp_DTA_index_usage_helper_xml go create procedure sp_DTA_index_usage_helper_xml @SessionID int, @IsRecommended int as begin select 1 as Tag, NULL as Parent, '' as [IndexUsageReport!1!!ELEMENT], case when @IsRecommended = 1 then 'false' else 'true' end as [IndexUsageReport!1!Current], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @IsRecommended and D.SessionID = @SessionID GROUP BY D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID union all select 4 as Tag, 3 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , R.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_table] as T, ( select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID,T.SchemaName, T.TableID ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID and R.TableID = T.TableID and T.DatabaseID = D.DatabaseID union all select 5 as Tag, 4 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D1.DatabaseID as [Database!2!DatabaseID!hide], D1.DatabaseName as [Database!2!Name!ELEMENT] , T1.SchemaName as [Schema!3!Name!ELEMENT] , T1.TableID as [Table!4!TableID!hide], T1.TableName as [Table!4!Name!ELEMENT], I1.IndexID as [Index!5!IndexID!hide], I1.IndexName as [Index!5!Name!ELEMENT], R.Count as [Index!5!NumberOfReferences!ELEMENT], CAST(R.Usage as decimal(38,2)) as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D1 , [msdb].[dbo].[DTA_reports_index] as I1, [msdb].[dbo].[DTA_reports_table] as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide], [Index!5!NumberOfReferences!ELEMENT] , [Index!5!IndexID!hide] FOR XML EXPLICIT end go /**************************************************************/ /* Index Usage Helper Relational */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_index_usage_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_usage_helper_relational' and type = 'P') drop procedure sp_DTA_index_usage_helper_relational go declare @localized_string_databaseName01 nvarchar(128) declare @localized_string_schemaName01 nvarchar(128) declare @localized_string_tableName01 nvarchar(128) declare @localized_string_indexName01 nvarchar(128) declare @localized_string_numReferences01 nvarchar(128) declare @localized_string_percentUse01 nvarchar(128) declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) set @localized_string_databaseName01 = N'"Database Name"' set @localized_string_schemaName01 = N'"Schema Name"' set @localized_string_tableName01 =N'"Table/View Name"' set @localized_string_indexName01 =N'"Index Name"' set @localized_string_numReferences01 = N'"Number of references"' set @localized_string_percentUse01 =N'"Percent Usage"' set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName01 + ' ,T1.SchemaName as ' + @localized_string_schemaName01 + ' ,T1.TableName as ' + @localized_string_tableName01 + ' ,I1.IndexName as ' + @localized_string_indexName01 + ' ,R.Count as '+ @localized_string_numReferences01 + ' ,CAST(R.Usage as decimal(38,2)) as '+ @localized_string_percentUse01 set @sql_from =' from DTA_reports_database as D1 , DTA_reports_index as I1, DTA_reports_table as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by R.Count desc' set @sql ='create procedure sp_DTA_index_usage_helper_relational @SessionID int, @IsRecommended int as begin ' + @sql_select + @sql_from +' end' execute(@sql) go /**************************************************************/ /* Database Access Helper XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_database_access_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_database_access_helper_xml' and type = 'P') drop procedure sp_DTA_database_access_helper_xml go create procedure sp_DTA_database_access_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [DatabaseAccessReport!1!!ELEMENT], NULL as [Database!2!Name!ELEMENT] , NULL as [Database!2!NumberOfReferences!ELEMENT], NULL as [Database!2!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent,NULL,D1.DatabaseName , R.Count , CAST(R.Usage as decimal(38,2)) from [msdb].[dbo].[DTA_reports_database] as D1 , ( select D.DatabaseID,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_database] as D LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QD.QueryID = Q.QueryID and Q.SessionID = QD.SessionID and Q.SessionID = @SessionID GROUP BY D.DatabaseID ) as R where R.DatabaseID = D1.DatabaseID and D1.SessionID = @SessionID and R.Count > 0 order by Tag,[Database!2!NumberOfReferences!ELEMENT] desc FOR XML EXPLICIT end go /**************************************************************/ /* Database Access Helper Relational */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_database_access_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_database_access_helper_relational' and type = 'P') drop procedure sp_DTA_database_access_helper_relational go declare @localized_string_databaseName02 nvarchar(128) declare @localized_string_numReferences02 nvarchar(128) declare @localized_string_percentUse02 nvarchar(128) declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) set @localized_string_databaseName02 = N'"Database Name"' set @localized_string_numReferences02 = N'"Number of references"' set @localized_string_percentUse02 =N'"Percentage usage"' set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName02 + ' ,R.Count as '+ @localized_string_numReferences02 + ' ,CAST(R.Usage as decimal(38,2)) as '+ @localized_string_percentUse02 set @sql_from =' from [msdb].[dbo].[DTA_reports_database] as D1 , ( select D.DatabaseID,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_database] as D LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID LEFT OUTER JOIN DTA_reports_query as Q ON QD.QueryID = Q.QueryID and Q.SessionID = QD.SessionID and Q.SessionID = @SessionID GROUP BY D.DatabaseID ) as R where R.DatabaseID = D1.DatabaseID and D1.SessionID = @SessionID and R.Count > 0 order by R.Count desc ' set @sql =' create procedure sp_DTA_database_access_helper_relational @SessionID int as begin ' + @sql_select + @sql_from + ' end' execute(@sql) go /**************************************************************/ /* Table Access Helper XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_table_access_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_table_access_helper_xml' and type = 'P') drop procedure sp_DTA_table_access_helper_xml go create procedure sp_DTA_table_access_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [TableAccessReport!1!!ELEMENT], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Table!4!NumberOfReferences!ELEMENT], NULL as [Table!4!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [TableAccessReport!1!!ELEMENT], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Table!4!NumberOfReferences!ELEMENT], NULL as [Table!4!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_querytable] as QT, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QT.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID group by D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL as [TableAccessReport!1!!ELEMENT], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Table!4!NumberOfReferences!ELEMENT], NULL as [Table!4!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_querytable] as QT, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QT.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID group by D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID union all select 4 as Tag, 3 as Parent, NULL as [TableAccessReport!1!!ELEMENT], D1.DatabaseID as [Database!2!DatabaseID!hide], D1.DatabaseName as [Database!2!Name!ELEMENT] , T1.SchemaName as [Schema!3!Name!ELEMENT] , T1.TableID as [Table!4!TableID!hide], T1.TableName as [Table!4!Name!ELEMENT], R.Count as [Table!4!NumberOfReferences!ELEMENT], CAST(R.Usage as decimal(38,2)) as [Table!4!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D1 , [msdb].[dbo].[DTA_reports_table] as T1, ( select D.DatabaseID,T.TableID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_table] as T LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querytable] as QT ON QT.TableID = T.TableID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QT.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QT.SessionID and Q.SessionID = @SessionID GROUP BY T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and D1.SessionID = @SessionID and R.Count > 0 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Table!4!NumberOfReferences!ELEMENT] FOR XML EXPLICIT end go /**************************************************************/ /* Table Access Helper Relational */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_table_access_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_table_access_helper_relational' and type = 'P') drop procedure sp_DTA_table_access_helper_relational go declare @localized_string_databaseName03 nvarchar(128) declare @localized_string_schemaName02 nvarchar(128) declare @localized_string_tableName02 nvarchar(128) declare @localized_string_numReferences03 nvarchar(128) declare @localized_string_percentUse03 nvarchar(128) declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) set @localized_string_databaseName03 = N'"Database Name"' set @localized_string_schemaName02 = N'"Schema Name"' set @localized_string_tableName02 =N'"Table Name"' set @localized_string_numReferences03 = N'"Number of references"' set @localized_string_percentUse03 =N'"Percent Usage"' set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName03 + ' ,T1.SchemaName as ' + @localized_string_schemaName02 + ' ,T1.TableName as ' + @localized_string_tableName02 + ' ,R.Count as '+ @localized_string_numReferences03 + ' ,CAST(R.Usage as decimal(38,2)) as '+ @localized_string_percentUse03 set @sql_from =' from [msdb].[dbo].[DTA_reports_database] as D1 , [msdb].[dbo].[DTA_reports_table] as T1, ( select D.DatabaseID,T.TableID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_table] as T LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querytable] as QT ON QT.TableID = T.TableID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QT.QueryID = Q.QueryID JOIN DTA_reports_database as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QT.SessionID and Q.SessionID = @SessionID GROUP BY T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and D1.SessionID = @SessionID and R.Count > 0 order by R.Count desc ' set @sql =' create procedure sp_DTA_table_access_helper_relational @SessionID int as begin ' + @sql_select + @sql_from + ' end ' execute(@sql) go print '' print 'Creating procedure sp_DTA_column_access_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_column_access_helper_xml' and type = 'P') drop procedure sp_DTA_column_access_helper_xml go create procedure sp_DTA_column_access_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [ColumnAccessReport!1!!ELEMENT], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Column!5!ColumnID!hide], NULL as [Column!5!Name!ELEMENT], NULL as [Column!5!NumberOfReferences!ELEMENT], NULL as [Column!5!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL, D.DatabaseID,D.DatabaseName, NULL,NULL,NULL,NULL,NULL,NULL,NULL from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_querycolumn] as QC, [msdb].[dbo].[DTA_reports_column] as C, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QC.ColumnID = C.ColumnID and C.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID group by D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL, R.DatabaseID,D.DatabaseName, R.SchemaName,NULL,NULL,NULL,NULL,NULL,NULL from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_querycolumn] as QC, [msdb].[dbo].[DTA_reports_column] as C, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QC.ColumnID = C.ColumnID and C.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID group by D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID union all select 4 as Tag, 3 as Parent, NULL, R.DatabaseID,D.DatabaseName, R.SchemaName,R.TableID,T.TableName,NULL,NULL,NULL,NULL from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_table] as T, ( select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_querycolumn] as QC, [msdb].[dbo].[DTA_reports_column] as C, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QC.ColumnID = C.ColumnID and C.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID group by D.DatabaseID,T.SchemaName,T.TableID ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID and R.TableID = T.TableID and T.DatabaseID = D.DatabaseID union all select 5 as Tag, 4 as Parent, NULL, D1.DatabaseID,D1.DatabaseName, T1.SchemaName,T1.TableID,T1.TableName,C1.ColumnID,C1.ColumnName, R.Count, CAST(R.Usage as decimal(38,2)) from [msdb].[dbo].[DTA_reports_database]as D1 , [msdb].[dbo].[DTA_reports_table] as T1, [msdb].[dbo].[DTA_reports_column] as C1, ( select D.DatabaseID,T.TableID,C.ColumnID, SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_column] as C LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querycolumn] as QC ON QC.ColumnID = C.ColumnID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QC.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON C.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QC.SessionID and Q.SessionID = @SessionID GROUP BY C.ColumnID,T.TableID,D.DatabaseID ) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.ColumnID = C1.ColumnID and D1.SessionID = @SessionID and R.Count > 0 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Column!5!NumberOfReferences!ELEMENT] , [Column!5!ColumnID!hide] FOR XML EXPLICIT end go print '' print 'Creating procedure sp_DTA_column_access_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_column_access_helper_relational' and type = 'P') drop procedure sp_DTA_column_access_helper_relational go declare @localized_string_databaseName04 nvarchar(128) declare @localized_string_schemaName03 nvarchar(128) declare @localized_string_tableName03 nvarchar(128) declare @localized_string_columnName nvarchar(128) declare @localized_string_numReferences04 nvarchar(128) declare @localized_string_percentUse04 nvarchar(128) declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) set @localized_string_databaseName04 = N'"Database Name"' set @localized_string_schemaName03 = N'"Schema Name"' set @localized_string_tableName03 =N'"Table/View Name"' set @localized_string_columnName =N'"Column Name"' set @localized_string_numReferences04 = N'"Number of references"' set @localized_string_percentUse04 =N'"Percent Usage"' set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName04 + ' ,T1.SchemaName as ' + @localized_string_schemaName03 + ' ,T1.TableName as ' + @localized_string_tableName03 + ' ,C1.ColumnName as ' + @localized_string_columnName + ' ,R.Count as '+ @localized_string_numReferences04 + ' ,CAST(R.Usage as decimal(38,2)) as '+ @localized_string_percentUse04 set @sql_from =' from [msdb].[dbo].[DTA_reports_database] as D1 , [msdb].[dbo].[DTA_reports_table] as T1, [msdb].[dbo].[DTA_reports_column] as C1, ( select D.DatabaseID,T.TableID,C.ColumnID, SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_column] as C LEFT OUTER JOIN DTA_reports_querycolumn as QC ON QC.ColumnID = C.ColumnID LEFT OUTER JOIN DTA_reports_query as Q ON QC.QueryID = Q.QueryID JOIN DTA_reports_table as T ON C.TableID = T.TableID JOIN DTA_reports_database as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QC.SessionID and Q.SessionID = @SessionID GROUP BY C.ColumnID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.ColumnID = C1.ColumnID and D1.SessionID = @SessionID and R.Count > 0 order by R.Count desc' set @sql =' create procedure sp_DTA_column_access_helper_relational @SessionID int as begin ' + @sql_select + @sql_from + ' end ' execute(@sql) go print '' print 'Creating procedure sp_DTA_query_costrange_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_costrange_helper_xml' and type = 'P') drop procedure sp_DTA_query_costrange_helper_xml go create procedure sp_DTA_query_costrange_helper_xml @SessionID int as begin declare @maxCost float declare @minCost float declare @maxCurrentCost float declare @minCurrentCost float declare @maxRecommendedCost float declare @minRecommendedCost float set nocount on select @minCurrentCost = min(CurrentCost*Weight),@maxCurrentCost = max(CurrentCost*Weight), @minRecommendedCost = min(RecommendedCost*Weight), @maxRecommendedCost = max(RecommendedCost*Weight) from [msdb].[dbo].[DTA_reports_query] where SessionID = @SessionID -- Set the bucket boundaries if @maxCurrentCost > @maxRecommendedCost set @maxCost = @maxCurrentCost else set @maxCost = @maxRecommendedCost if @minCurrentCost < @minRecommendedCost set @minCost = @minCurrentCost else set @minCost = @minRecommendedCost create table #stringmap(OutputString nvarchar(30),num int) insert into #stringmap values(N'0% - 10%',0) insert into #stringmap values(N'11% - 20%',1) insert into #stringmap values(N'21% - 30%',2) insert into #stringmap values(N'31% - 40%',3) insert into #stringmap values(N'41% - 50%',4) insert into #stringmap values(N'51% - 60%',5) insert into #stringmap values(N'61% - 70%',6) insert into #stringmap values(N'71% - 80%',7) insert into #stringmap values(N'81% - 90%',8) insert into #stringmap values(N'91% - 100%',9) select num,count(*) as cnt into #c from ( select case when (@maxCost=@minCost) then 9 when (CurrentCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9 else convert(int,floor(10*(CurrentCost*Weight-@minCost)/(@maxCost-@minCost))) end as num from [msdb].[dbo].[DTA_reports_query] where CurrentCost*Weight >= @minCost and CurrentCost*Weight <= @maxCost and SessionID = @SessionID ) t group by num select num,count(*) as cnt into #r from ( select case when (@maxCost=@minCost) then 9 when (RecommendedCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9 else convert(int,floor(10*(RecommendedCost*Weight-@minCost)/(@maxCost-@minCost))) end as num from [msdb].[dbo].[DTA_reports_query] where RecommendedCost*Weight >= @minCost and RecommendedCost*Weight <= @maxCost and SessionID = @SessionID ) t group by num select 1 as Tag, NULL as Parent, '' as [StatementCostRangeReport!1!!ELEMENT], NULL as [CostRange!2!Percent] , NULL as [CostRange!2!NumStatementsCurrent!ELEMENT], NULL as [CostRange!2!NumStatementsRecommended!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [StatementCostRangeReport!1!!ELEMENT], OutputString as [CostRange!2!ELEMENT] , ISNULL(c.cnt,0) as [CostRange!2!NumStatementsCurrent!ELEMENT], ISNULL(r.cnt,0) as [CostRange!2!NumStatementsRecommended!ELEMENT] from ( select #stringmap.num, #r.cnt from #stringmap LEFT OUTER JOIN #r ON #stringmap.num = #r.num ) r, ( select #stringmap.num, #c.cnt from #stringmap LEFT OUTER JOIN #c ON #stringmap.num = #c.num ) c, #stringmap where #stringmap.num = r.num and #stringmap.num = c.num FOR XML EXPLICIT drop table #r drop table #c drop table #stringmap end go print '' print 'Creating procedure sp_DTA_query_costrange_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_costrange_helper_relational' and type = 'P') drop procedure sp_DTA_query_costrange_helper_relational go declare @localized_string_costRange nvarchar(128) declare @localized_string_numCurrStmts nvarchar(128) declare @localized_string_numRecStmts nvarchar(128) declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) set @localized_string_costRange = N'"Cost Range"' set @localized_string_numCurrStmts = N'"Number of statements (Current) "' set @localized_string_numRecStmts = N'"Number of statements (Recommended) "' set @sql_select =' select ' + @localized_string_costRange + ' =OutputString, ' + @localized_string_numCurrStmts + ' = ISNULL(c.cnt,0) , ' + @localized_string_numRecStmts + ' = ISNULL(r.cnt,0) ' set @sql_from = ' from ( select #stringmap.num, #r.cnt from #stringmap LEFT OUTER JOIN #r ON #stringmap.num = #r.num ) r, ( select #stringmap.num, #c.cnt from #stringmap LEFT OUTER JOIN #c ON #stringmap.num = #c.num ) c, #stringmap where #stringmap.num = r.num and #stringmap.num = c.num drop table #r drop table #c drop table #stringmap ' set @sql ='create procedure sp_DTA_query_costrange_helper_relational @SessionID int as begin declare @maxCost float declare @minCost float declare @maxCurrentCost float declare @minCurrentCost float declare @maxRecommendedCost float declare @minRecommendedCost float set nocount on select @minCurrentCost = min(CurrentCost*Weight),@maxCurrentCost = max(CurrentCost*Weight), @minRecommendedCost = min(RecommendedCost*Weight), @maxRecommendedCost = max(RecommendedCost*Weight) from [msdb].[dbo].[DTA_reports_query] where SessionID = @SessionID -- Set the bucket boundaries if @maxCurrentCost > @maxRecommendedCost set @maxCost = @maxCurrentCost else set @maxCost = @maxRecommendedCost if @minCurrentCost < @minRecommendedCost set @minCost = @minCurrentCost else set @minCost = @minRecommendedCost create table #stringmap(OutputString nvarchar(30),num int) insert into #stringmap values(N''0% - 10%'',0) insert into #stringmap values(N''11% - 20%'',1) insert into #stringmap values(N''21% - 30%'',2) insert into #stringmap values(N''31% - 40%'',3) insert into #stringmap values(N''41% - 50%'',4) insert into #stringmap values(N''51% - 60%'',5) insert into #stringmap values(N''61% - 70%'',6) insert into #stringmap values(N''71% - 80%'',7) insert into #stringmap values(N''81% - 90%'',8) insert into #stringmap values(N''91% - 100%'',9) select num,count(*) as cnt into #c from ( select case when (@maxCost=@minCost) then 9 when (CurrentCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9 else convert(int,floor(10*(CurrentCost*Weight-@minCost)/(@maxCost-@minCost))) end as num from [msdb].[dbo].[DTA_reports_query] where CurrentCost*Weight >= @minCost and CurrentCost*Weight <= @maxCost and SessionID = @SessionID ) t group by num select num,count(*) as cnt into #r from ( select case when (@maxCost=@minCost) then 9 when (RecommendedCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9 else convert(int,floor(10*(RecommendedCost*Weight-@minCost)/(@maxCost-@minCost))) end as num from [msdb].[dbo].[DTA_reports_query] where RecommendedCost*Weight >= @minCost and RecommendedCost*Weight <= @maxCost and SessionID = @SessionID ) t group by num ' + @sql_select + 'from ( select #stringmap.num, #r.cnt from #stringmap LEFT OUTER JOIN #r ON #stringmap.num = #r.num ) r, ( select #stringmap.num, #c.cnt from #stringmap LEFT OUTER JOIN #c ON #stringmap.num = #c.num ) c, #stringmap where #stringmap.num = r.num and #stringmap.num = c.num drop table #r drop table #c drop table #stringmap end ' exec (@sql) go /**************************************************************/ /* Query Cost Report XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_query_cost_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_cost_helper_xml' and type = 'P') drop procedure sp_DTA_query_cost_helper_xml go create procedure sp_DTA_query_cost_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [StatementCostReport!1!!element], NULL as [Statement!2!StatementID!ELEMENT], NULL as [Statement!2!StatementString!ELEMENT] , NULL as [Statement!2!PercentImprovement!ELEMENT], NULL as [Statement!2!Type!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [StatementCostReport!1!!element], QueryID as [Statement!2!StatementID!ELEMENT], StatementString as [Statement!2!StatementString!ELEMENT] , CASE WHEN CurrentCost = 0 THEN 0.00 WHEN CurrentCost <> 0 THEN CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal (20,2)) end as [Statement!2!PercentImprovement!ELEMENT], CASE WHEN StatementType = 0 THEN 'Select' WHEN StatementType = 1 THEN 'Update' WHEN StatementType = 2 THEN 'Insert' WHEN StatementType = 3 THEN 'Delete' WHEN StatementType = 4 THEN 'Merge' end as [Statement!2!Type!ELEMENT] from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID order by Tag,[Statement!2!PercentImprovement!ELEMENT] desc FOR XML EXPLICIT end go print '' print 'Creating procedure sp_DTA_query_cost_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_cost_helper_relational' and type = 'P') drop procedure sp_DTA_query_cost_helper_relational go /**************************************************************/ /* Query Cost Report Relational */ /**************************************************************/ declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_stmtId01 nvarchar(128) declare @localized_string_stmtString01 nvarchar(128) declare @localized_string_stmtType01 nvarchar(128) declare @localized_string_percentImprovement nvarchar(128) set @localized_string_stmtId01 = N'"Statement Id"' set @localized_string_stmtString01 = N'"Statement String"' set @localized_string_percentImprovement = N'"Percent Improvement"' set @localized_string_stmtType01 = N'"Statement Type"' set @sql_select =' select ' + @localized_string_stmtId01 + ' = QueryID, ' + @localized_string_stmtString01 + ' = StatementString, ' + + @localized_string_percentImprovement + ' = CASE WHEN CurrentCost = 0 THEN 0.00 WHEN CurrentCost <> 0 THEN CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal (20,2)) end , ' + @localized_string_stmtType01 + ' = CASE WHEN StatementType = 0 THEN ''Select'' WHEN StatementType = 1 THEN ''Update'' WHEN StatementType = 2 THEN ''Insert'' WHEN StatementType = 3 THEN ''Delete'' WHEN StatementType = 4 THEN ''Merge'' end ' set @sql_from = ' from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID order by ' + @localized_string_percentImprovement + ' desc ' set @sql =' create procedure sp_DTA_query_cost_helper_relational @SessionID int as begin ' + @sql_select + @sql_from + ' end ' execute(@sql) go /**************************************************************/ /* Event Frequency Report XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_event_weight_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_event_weight_helper_xml' and type = 'P') drop procedure sp_DTA_event_weight_helper_xml go create procedure sp_DTA_event_weight_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [EventWeightReport!1!!element], NULL as [EventDetails!2!EventString!ELEMENT] , NULL as [EventDetails!2!Weight!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [QueryCost!1!!element], EventString as [EventDetails!2!EventString!ELEMENT] , CAST(EventWeight as decimal(38,2)) as [EventDetails!2!Weight!ELEMENT] from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID and EventWeight>0 order by Tag,[EventDetails!2!Weight!ELEMENT] desc FOR XML EXPLICIT end go print '' print 'Creating procedure sp_DTA_event_weight_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_event_weight_helper_relational' and type = 'P') drop procedure sp_DTA_event_weight_helper_relational go /**************************************************************/ /* Event Frequency Report Relational */ /**************************************************************/ declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_eventString nvarchar(128) declare @localized_string_weight nvarchar(128) set @localized_string_eventString =N'"Event String"' set @localized_string_weight =N'"Weight"' set @sql_select = ' select ' + @localized_string_eventString + '= EventString, ' +@localized_string_weight +' = CAST(EventWeight as decimal(38,2)) ' set @sql_from = ' from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID and EventWeight>0 order by EventWeight desc ' set @sql =' create procedure sp_DTA_event_weight_helper_relational @SessionID int as begin' + @sql_select + @sql_from + ' end ' execute(@sql) go /**************************************************************/ /* Query Detail Report XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_query_detail_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_detail_helper_xml' and type = 'P') drop procedure sp_DTA_query_detail_helper_xml go create procedure sp_DTA_query_detail_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [StatementDetailReport!1!!element], NULL as [Statement!2!StatementID!ELEMENT] , NULL as [Statement!2!StatementString!ELEMENT] , NULL as [Statement!2!Type!ELEMENT], NULL as [Statement!2!CurrentCost!ELEMENT], NULL as [Statement!2!RecommendedCost!ELEMENT], NULL as [Statement!2!EventString!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [QueryCost!1!!element], QueryID as [Statement!2!StatementID!ELEMENT], StatementString as [Statement!2!StatementString!ELEMENT] , CASE WHEN StatementType = 0 THEN 'Select' WHEN StatementType = 1 THEN 'Update' WHEN StatementType = 2 THEN 'Insert' WHEN StatementType = 3 THEN 'Delete' WHEN StatementType = 4 THEN 'Merge' end as [Statement!2!Type!ELEMENT!element], CAST(CurrentCost as decimal(38,7)) as [Statement!2!CurrentCost!ELEMENT], CAST(RecommendedCost as decimal(38,7)) as [Statement!2!RecommendedCost!ELEMENT], EventString as [Statement!2!EventString!ELEMENT] from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID FOR XML EXPLICIT end go print '' print 'Creating procedure sp_DTA_query_detail_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_detail_helper_relational' and type = 'P') drop procedure sp_DTA_query_detail_helper_relational go /**************************************************************/ /* Query Detail Report Relational */ /**************************************************************/ declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_stmtString02 nvarchar(128) declare @localized_string_stmtType02 nvarchar(128) declare @localized_string_stmtCostCurr nvarchar(128) declare @localized_string_stmtCostRec nvarchar(128) declare @localized_string_stmtID03 nvarchar(128) declare @localized_string_event01 nvarchar(128) set @localized_string_stmtString02 =N'"Statement String"' set @localized_string_stmtType02 =N'"Statement Type"' set @localized_string_stmtCostCurr =N'"Current Statement Cost"' set @localized_string_stmtCostRec =N'"Recommended Statement Cost"' set @localized_string_stmtID03 =N'"Statement ID"' set @localized_string_event01 =N'"Event String"' set @sql_select = ' select ' + @localized_string_stmtID03 + ' =QueryID, ' + @localized_string_stmtString02 + ' =StatementString, ' + @localized_string_stmtType02 + ' = CASE WHEN StatementType = 0 THEN ''Select'' WHEN StatementType = 1 THEN ''Update'' WHEN StatementType = 2 THEN ''Insert'' WHEN StatementType = 3 THEN ''Delete'' WHEN StatementType = 4 THEN ''Merge'' end,' + @localized_string_stmtCostCurr + ' =CAST(CurrentCost as decimal(38,7)), ' + @localized_string_stmtCostRec + ' =CAST(RecommendedCost as decimal(38,7)), ' + @localized_string_event01 + ' =EventString' set @sql_from = ' from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID order by QueryID ASC' set @sql =' create procedure sp_DTA_query_detail_helper_relational @SessionID int as begin' + @sql_select + @sql_from + ' end ' execute(@sql) go /**************************************************************/ /* Query Index Relations Report XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_query_indexrelations_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_indexrelations_helper_xml' and type = 'P') drop procedure sp_DTA_query_indexrelations_helper_xml go create procedure sp_DTA_query_indexrelations_helper_xml @SessionID int , @Recommended int as begin select 1 as Tag, NULL as Parent, '' as [StatementIndexReport!1!!ELEMENT], case when @Recommended = 1 then 'false' else'true' end as [StatementIndexReport!1!Current], NULL as [StatementIndexDetail!2!stmtID!hide], NULL as [StatementIndexDetail!2!StatementString!ELEMENT] , NULL as [Database!3!DatabaseID!hide], NULL as [Database!3!Name!ELEMENT] , NULL as [Schema!4!Name!ELEMENT] , NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT], NULL as [Index!6!IndexID!hide], NULL as [Index!6!Name!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [StatementIndexReport!1!!ELEMENT], NULL as [StatementIndexReport!1!Current], Q.QueryID as [StatementIndexDetail!2!stmtID!hide], Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] , NULL as [Database!3!DatabaseID!hide], NULL as [Database!3!Name!ELEMENT] , NULL as [Schema!4!Name!ELEMENT] , NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT], NULL as [Index!6!IndexID!hide], NULL as [Index!6!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_query] Q, ( select Q.QueryID,D.DatabaseID from [msdb].[dbo].[DTA_reports_query] Q, [msdb].[dbo].[DTA_reports_queryindex] QI, [msdb].[dbo].[DTA_reports_index] I, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_database] D where Q.SessionID=QI.SessionID and Q.QueryID=QI.QueryID and QI.IndexID=I.IndexID and I.TableID=T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @Recommended and Q.SessionID=@SessionID group by Q.QueryID,D.DatabaseID) as R where R.QueryID = Q.QueryID and R.DatabaseID = D.DatabaseID and Q.SessionID = @SessionID and R.DatabaseID IS NOT NULL union all select 3 as Tag, 2 as Parent, NULL as [StatementIndexReport!1!!ELEMENT], NULL as [StatementIndexReport!1!Current], Q.QueryID as [StatementIndexDetail!2!stmtID!hide], Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] , D.DatabaseID as [Database!3!DatabaseID!hide], D.DatabaseName as [Database!3!Name!ELEMENT] , NULL as [Schema!4!Name!ELEMENT] , NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT], NULL as [Index!6!IndexID!hide], NULL as [Index!6!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_query] Q, ( select Q.QueryID,D.DatabaseID from [msdb].[dbo].[DTA_reports_query] Q, [msdb].[dbo].[DTA_reports_queryindex] QI, [msdb].[dbo].[DTA_reports_index] I, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_database] D where Q.SessionID=QI.SessionID and Q.QueryID=QI.QueryID and QI.IndexID=I.IndexID and I.TableID=T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @Recommended and Q.SessionID=@SessionID group by Q.QueryID,D.DatabaseID) as R where R.QueryID = Q.QueryID and R.DatabaseID = D.DatabaseID and Q.SessionID = @SessionID union all select 4 as Tag, 3 as Parent, NULL as [StatementIndexReport!1!!ELEMENT], NULL as [StatementIndexReport!1!Current], Q.QueryID as [StatementIndexDetail!2!stmtID!hide], Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] , D.DatabaseID as [Database!3!DatabaseID!hide], D.DatabaseName as [Database!3!Name!ELEMENT] , R.SchemaName as [Schema!4!Name!ELEMENT] , NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT], NULL as [Index!6!IndexID!hide], NULL as [Index!6!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_query] Q, ( select Q.QueryID,D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_query] Q, [msdb].[dbo].[DTA_reports_queryindex] QI, [msdb].[dbo].[DTA_reports_index] I, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_database] D where Q.SessionID=QI.SessionID and Q.QueryID=QI.QueryID and QI.IndexID=I.IndexID and I.TableID=T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @Recommended and Q.SessionID=@SessionID group by Q.QueryID,D.DatabaseID,T.SchemaName) as R where R.QueryID = Q.QueryID and R.DatabaseID = D.DatabaseID and Q.SessionID = @SessionID union all select 5 as Tag, 4 as Parent, NULL as [StatementIndexReport!1!!ELEMENT], NULL as [StatementIndexReport!1!Current], Q.QueryID as [StatementIndexDetail!2!stmtID!hide], Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] , D.DatabaseID as [Database!3!DatabaseID!hide], D.DatabaseName as [Database!3!Name!ELEMENT] , R.SchemaName as [Schema!4!Name!ELEMENT] , R.TableID as [Table!5!TableID!hide], T.TableName as [Table!5!Name!ELEMENT], NULL as [Index!6!IndexID!hide], NULL as [Index!6!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_query] Q, [msdb].[dbo].[DTA_reports_table] T, ( select Q.QueryID,D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_query] Q, [msdb].[dbo].[DTA_reports_queryindex] QI, [msdb].[dbo].[DTA_reports_index] I, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_database] D where Q.SessionID=QI.SessionID and Q.QueryID=QI.QueryID and QI.IndexID=I.IndexID and I.TableID=T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @Recommended and Q.SessionID=@SessionID group by Q.QueryID,D.DatabaseID,T.SchemaName,T.TableID) as R where R.QueryID = Q.QueryID and R.DatabaseID = D.DatabaseID and Q.SessionID = @SessionID and R.TableID = T.TableID union all select 6 as Tag, 5 as Parent, NULL as [StatementIndexReport!1!!ELEMENT], NULL as [StatementIndexReport!1!Current], Q.QueryID as [StatementIndexDetail!2!stmtID!hide], Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] , D.DatabaseID as [Database!3!DatabaseID!hide], D.DatabaseName as [Database!3!Name!ELEMENT] , T.SchemaName as [Schema!4!Name!ELEMENT] , T.TableID as [Table!5!TableID!hide], T.TableName as [Table!5!Name!ELEMENT], I.IndexID as [Index!6!IndexID!hide], I.IndexName as [Index!6!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_query] Q, [msdb].[dbo].[DTA_reports_queryindex] QI, [msdb].[dbo].[DTA_reports_index] I, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_database] D where Q.SessionID=QI.SessionID and Q.QueryID=QI.QueryID and QI.IndexID=I.IndexID and I.TableID=T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @Recommended and Q.SessionID=@SessionID order by [StatementIndexDetail!2!stmtID!hide],[Database!3!DatabaseID!hide], [Schema!4!Name!ELEMENT],[Table!5!TableID!hide],[Index!6!IndexID!hide] FOR XML EXPLICIT end go print '' print 'Creating procedure sp_DTA_query_indexrelations_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_indexrelations_helper_relational' and type = 'P') drop procedure sp_DTA_query_indexrelations_helper_relational go /**************************************************************/ /* Query Index Relations Report Relational */ /**************************************************************/ declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_stmtId02 nvarchar(128) declare @localized_string_stmtString03 nvarchar(128) declare @localized_string_databaseName05 nvarchar(128) declare @localized_string_schemaName04 nvarchar(128) declare @localized_string_objectName01 nvarchar(128) declare @localized_string_indexName02 nvarchar(128) declare @localized_string_clustered01 nvarchar(128) declare @localized_string_unique01 nvarchar(128) set @localized_string_stmtId02 = N'"Statement Id"' set @localized_string_stmtString03 = N'"Statement String"' set @localized_string_databaseName05 = N'"Database Name"' set @localized_string_schemaName04 = N'"Schema Name"' set @localized_string_objectName01 = N'"Table/View Name"' set @localized_string_indexName02 = N'"Index Name"' set @localized_string_clustered01 = N'"Clustered"' set @localized_string_unique01 = N'"Unique"' set @sql_select = ' select ' + @localized_string_stmtId02 + ' =Q.QueryID, ' + @localized_string_stmtString03 + ' =Q.StatementString,' + @localized_string_databaseName05 + ' =D.DatabaseName, ' + @localized_string_schemaName04 + ' =T.SchemaName, ' + @localized_string_objectName01 +' =T.TableName, ' + @localized_string_indexName02 + ' =I.IndexName ' set @sql_from = ' from [msdb].[dbo].[DTA_reports_query] Q, [msdb].[dbo].[DTA_reports_queryindex] QI, [msdb].[dbo].[DTA_reports_index] I, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_database] D where Q.SessionID=QI.SessionID and Q.QueryID=QI.QueryID and QI.IndexID=I.IndexID and I.TableID=T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @Recommended and Q.SessionID=@SessionID order by Q.QueryID ' set @sql = ' create procedure sp_DTA_query_indexrelations_helper_relational @SessionID int, @Recommended int as begin ' + @sql_select + @sql_from + ' end ' execute(@sql) go /**************************************************************/ /* Index Detail Report For Current XML */ /**************************************************************/ go print '' print 'Creating procedure sp_DTA_index_current_detail_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_current_detail_helper_xml' and type = 'P') drop procedure sp_DTA_index_current_detail_helper_xml go create procedure sp_DTA_index_current_detail_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [IndexDetailReport!1!!ELEMENT], 'true' as [IndexDetailReport!1!Current], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] union all select 2 as Tag, 1 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsExisting = 1 group by D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsExisting = 1 group by D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID union all select 4 as Tag, 3 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , R.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_table] as T, ( select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsExisting = 1 group by D.DatabaseID,T.SchemaName,T.TableID ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID and R.TableID = T.TableID and T.DatabaseID = D.DatabaseID union all select 5 as Tag, 4 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , T.SchemaName as [Schema!3!Name!ELEMENT] , T.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], I.IndexID as [Index!5!IndexID!hide], I.IndexName as [Index!5!Name!ELEMENT], CASE WHEN I.IsClustered = 1 THEN 'true' WHEN I.IsClustered = 0 THEN 'false' end as [Index!5!Clustered], CASE WHEN I.IsUnique = 1 THEN 'true' WHEN I.IsUnique = 0 THEN 'false' end as [Index!5!Unique], CASE WHEN I.IsHeap = 1 THEN 'true' WHEN I.IsHeap = 0 THEN 'false' end as [Index!5!Heap], CASE WHEN I.IsFiltered = 1 THEN 'true' WHEN I.IsFiltered = 0 THEN 'false' end as [Index!5!IsFiltered], CAST(I.Storage as decimal(38,2)) as [Index!5!IndexSizeInMB], I.NumRows as [Index!5!NumberOfRows], I.FilterDefinition as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] D, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsExisting = 1 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Index!5!IndexID!hide] FOR XML EXPLICIT end go /**************************************************************/ /* Index Detail Report For Recommended XML */ /**************************************************************/ go print '' print 'Creating procedure sp_DTA_index_recommended_detail_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_recommended_detail_helper_xml' and type = 'P') drop procedure sp_DTA_index_recommended_detail_helper_xml go create procedure sp_DTA_index_recommended_detail_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [IndexDetailReport!1!!ELEMENT], 'false' as [IndexDetailReport!1!Current], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] union all select 2 as Tag, 1 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and IsRecommended = 1 group by D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and IsRecommended = 1 group by D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID union all select 4 as Tag, 3 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , R.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!Clustered], NULL as [Index!5!Unique], NULL as [Index!5!Heap], NULL as [Index!5!FilteredIndex], NULL as [Index!5!IndexSizeInMB], NULL as [Index!5!NumberOfRows], NULL as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_table] as T, ( select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsRecommended = 1 group by D.DatabaseID,T.SchemaName,T.TableID ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID and R.TableID = T.TableID and T.DatabaseID = D.DatabaseID union all select 5 as Tag, 4 as Parent, NULL as [IndexDetailReport!1!!ELEMENT], NULL as [IndexDetailReport!1!Recommended], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , T.SchemaName as [Schema!3!Name!ELEMENT] , T.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], I.IndexID as [Index!5!IndexID!hide], I.IndexName as [Index!5!Name!ELEMENT], CASE WHEN I.IsClustered = 1 THEN 'true' WHEN I.IsClustered = 0 THEN 'false' end as [Index!5!Clustered], CASE WHEN I.IsUnique = 1 THEN 'true' WHEN I.IsUnique = 0 THEN 'false' end as [Index!5!Unique], CASE WHEN I.IsHeap = 1 THEN 'true' WHEN I.IsHeap = 0 THEN 'false' end as [Index!5!Heap], CASE WHEN I.IsFiltered = 1 THEN 'true' WHEN I.IsFiltered = 0 THEN 'false' end as [Index!5!FilteredIndex], CAST(I.RecommendedStorage as decimal(38,2)) as [Index!5!IndexSizeInMB], I.NumRows as [Index!5!NumberOfRows], I.FilterDefinition as [Index!5!FilterDefinition] from [msdb].[dbo].[DTA_reports_database] D, [msdb].[dbo].[DTA_reports_table] T, [msdb].[dbo].[DTA_reports_index] as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsRecommended = 1 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Index!5!IndexID!hide] FOR XML EXPLICIT end go /**************************************************************/ /* Index Detail Report For Current Relational */ /**************************************************************/ go print '' print 'Creating procedure sp_DTA_index_detail_current_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_detail_current_helper_relational' and type = 'P') drop procedure sp_DTA_index_detail_current_helper_relational go declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_databaseName06 nvarchar(128) declare @localized_string_schemaName05 nvarchar(128) declare @localized_string_objectName02 nvarchar(128) declare @localized_string_indexName03 nvarchar(128) declare @localized_string_clustered02 nvarchar(128) declare @localized_string_unique02 nvarchar(128) declare @localized_string_storage nvarchar(128) declare @localized_string_rows nvarchar(128) declare @localized_string_heap nvarchar(128) declare @localized_string_filter nvarchar(128) declare @localized_string_filtdef nvarchar(128) set @localized_string_databaseName06 = N'"Database Name"' set @localized_string_schemaName05 = N'"Schema Name"' set @localized_string_objectName02 = N'"Table/View Name"' set @localized_string_indexName03 = N'"Index Name"' set @localized_string_clustered02 = N'"Clustered"' set @localized_string_unique02 = N'"Unique"' set @localized_string_heap = N'"Heap"' set @localized_string_storage = N'"Index Size (MB)"' set @localized_string_rows = N'"Number of Rows"' set @localized_string_filter = N'"Filtered"' set @localized_string_filtdef = N'"Filter Definition"' set @sql_select = ' select ' + @localized_string_databaseName06 + ' = D.DatabaseName, ' + @localized_string_schemaName05 + ' = T.SchemaName, ' + @localized_string_objectName02 + ' = T.TableName, ' + @localized_string_indexName03 + ' = I.IndexName, ' + @localized_string_clustered02 + ' = CASE WHEN I.IsClustered = 1 THEN ''Yes'' WHEN I.IsClustered = 0 THEN ''No'' end, ' + @localized_string_unique02 + ' = CASE WHEN I.IsUnique = 1 THEN ''Yes'' WHEN I.IsUnique = 0 THEN ''No'' end , ' + @localized_string_heap + ' = CASE WHEN I.IsHeap = 1 THEN ''Yes'' WHEN I.IsHeap = 0 THEN ''No'' end , ' + @localized_string_filter + ' = CASE WHEN I.IsFiltered = 1 THEN ''Yes'' WHEN I.IsFiltered = 0 THEN ''No'' end , ' + @localized_string_storage + '= CAST(I.Storage as decimal(38,2)) , ' + @localized_string_rows + '= NumRows , ' + @localized_string_filtdef + '= I.FilterDefinition ' set @sql_from = ' from DTA_reports_database D, DTA_reports_table T, DTA_reports_index as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsExisting = 1 ' set @sql = ' create procedure sp_DTA_index_detail_current_helper_relational @SessionID int as begin' + @sql_select + @sql_from + ' end ' execute(@sql) go /**************************************************************/ /* Index Detail Report For Recommended Relational */ /**************************************************************/ go print '' print 'Creating procedure sp_DTA_index_detail_recommended_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_detail_recommended_helper_relational' and type = 'P') drop procedure sp_DTA_index_detail_recommended_helper_relational go declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_databaseName06 nvarchar(128) declare @localized_string_schemaName05 nvarchar(128) declare @localized_string_objectName02 nvarchar(128) declare @localized_string_indexName03 nvarchar(128) declare @localized_string_clustered02 nvarchar(128) declare @localized_string_unique02 nvarchar(128) declare @localized_string_storage nvarchar(128) declare @localized_string_rows nvarchar(128) declare @localized_string_heap nvarchar(128) declare @localized_string_filter nvarchar(128) declare @localized_string_filtdef nvarchar(128) set @localized_string_databaseName06 = N'"Database Name"' set @localized_string_schemaName05 = N'"Schema Name"' set @localized_string_objectName02 = N'"Table/View Name"' set @localized_string_indexName03 = N'"Index Name"' set @localized_string_clustered02 = N'"Clustered"' set @localized_string_unique02 = N'"Unique"' set @localized_string_heap = N'"Heap"' set @localized_string_storage = N'"Index Size (MB)"' set @localized_string_rows = N'"Number of Rows"' set @localized_string_filter = N'"Filtered"' set @localized_string_filtdef = N'"Filter Definition"' set @sql_select = ' select ' + @localized_string_databaseName06 + ' = D.DatabaseName, ' + @localized_string_schemaName05 + ' = T.SchemaName, ' + @localized_string_objectName02 + ' = T.TableName, ' + @localized_string_indexName03 + ' = I.IndexName, ' + @localized_string_clustered02 + ' = CASE WHEN I.IsClustered = 1 THEN ''Yes'' WHEN I.IsClustered = 0 THEN ''No'' end, ' + @localized_string_unique02 + ' = CASE WHEN I.IsUnique = 1 THEN ''Yes'' WHEN I.IsUnique = 0 THEN ''No'' end , ' + @localized_string_heap + ' = CASE WHEN I.IsHeap = 1 THEN ''Yes'' WHEN I.IsHeap = 0 THEN ''No'' end , ' + @localized_string_filter + ' = CASE WHEN I.IsFiltered = 1 THEN ''Yes'' WHEN I.IsFiltered = 0 THEN ''No'' end , ' + @localized_string_storage + '= CAST(I.RecommendedStorage as decimal(38,2)) , ' + @localized_string_rows + '= NumRows , '+ @localized_string_filtdef + '= I.FilterDefinition ' set @sql_from = ' from DTA_reports_database D, DTA_reports_table T, DTA_reports_index as I where D.SessionID = @SessionID and D.DatabaseID = T.DatabaseID and T.TableID = I.TableID and I.IsRecommended = 1 ' set @sql = ' create procedure sp_DTA_index_detail_recommended_helper_relational @SessionID int as begin' + @sql_select + @sql_from + ' end ' execute(@sql) go /**************************************************************/ /* View Table Relations XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_view_table_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_view_table_helper_xml' and type = 'P') drop procedure sp_DTA_view_table_helper_xml go create procedure sp_DTA_view_table_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [ViewTableReport!1!!ELEMENT], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [View!4!ViewID!hide], NULL as [View!4!Name!ELEMENT], NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [ViewTableReport!1!!ELEMENT], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [View!4!ViewID!hide], NULL as [View!4!Name!ELEMENT], NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in ( select D.DatabaseID from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where T.IsView = 1 and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID GROUP BY D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL as [ViewTableReport!1!!ELEMENT], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [View!4!ViewID!hide], NULL as [View!4!Name!ELEMENT], NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, (select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where T.IsView = 1 and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID GROUP BY D.DatabaseID,T.SchemaName ) R where R.DatabaseID = D.DatabaseID and D.SessionID = @SessionID union all select 4 as Tag, 3 as Parent, NULL as [ViewTableReport!1!!ELEMENT], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , T.TableID as [View!4!ViewID!hide], T.TableName as [View!4!Name!ELEMENT], NULL as [Table!5!TableID!hide], NULL as [Table!5!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D, (select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where T.IsView = 1 and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID GROUP BY D.DatabaseID,T.SchemaName,T.TableID ) R where R.DatabaseID = D.DatabaseID and T.TableID = R.TableID and D.SessionID = @SessionID union all select 5 as Tag, 4 as Parent, NULL as [ViewTableReport!1!!ELEMENT], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , T2.SchemaName as [Schema!3!Name!ELEMENT] , T2.TableID as [View!4!ViewID!hide], T2.TableName as [View!4!Name!ELEMENT], T1.TableID as [Table!5!TableID!hide], T1.TableName as [Table!5!Name!ELEMENT] from [msdb].[dbo].[DTA_reports_database] D, [msdb].[dbo].[DTA_reports_tableview] TV, [msdb].[dbo].[DTA_reports_table] T1, [msdb].[dbo].[DTA_reports_table] T2 where D.DatabaseID=T1.DatabaseID and D.DatabaseID=T2.DatabaseID and T1.TableID=TV.TableID and T2.TableID=TV.ViewID and D.SessionID = @SessionID order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[View!4!ViewID!hide],[Table!5!TableID!hide] FOR XML EXPLICIT end go /**************************************************************/ /* View Table Relations Relational */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_view_table_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_view_table_helper_relational' and type = 'P') drop procedure sp_DTA_view_table_helper_relational go declare @sql nvarchar(4000) declare @sql_select_relational nvarchar(4000) declare @sql_select_xml nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_viewID02 nvarchar(128) declare @localized_string_databaseName07 nvarchar(128) declare @localized_string_schemaName06 nvarchar(128) declare @localized_string_viewName nvarchar(128) declare @localized_string_tableName04 nvarchar(128) set @localized_string_viewID02 =N'"View Id"' set @localized_string_databaseName07 = N'"Database Name"' set @localized_string_schemaName06 = N'"Schema Name"' set @localized_string_viewName = N'"View Name"' set @localized_string_tableName04 = N'"Table Name"' set @sql_select_relational = ' select ' + @localized_string_viewID02 + ' =T2.TableID, ' + @localized_string_databaseName07 + ' =D.DatabaseName, ' + @localized_string_schemaName06 + ' =T2.SchemaName, ' + @localized_string_viewName + ' =T2.TableName, ' + @localized_string_databaseName07 + ' =D.DatabaseName, ' + @localized_string_schemaName06 + ' =T1.SchemaName, ' + @localized_string_tableName04 + ' =T1.TableName ' set @sql_select_xml = ' select T2.TableID,D.DatabaseName, T2.SchemaName, T2.TableName as ''View'',T1.TableName as ''Table''' set @sql_from = ' from [msdb].[dbo].[DTA_reports_database] D, [msdb].[dbo].[DTA_reports_tableview] TV, [msdb].[dbo].[DTA_reports_table] T1, [msdb].[dbo].[DTA_reports_table] T2 where D.DatabaseID=T1.DatabaseID and D.DatabaseID=T2.DatabaseID and T1.TableID=TV.TableID and T2.TableID=TV.ViewID and D.SessionID=@SessionID order by TV.ViewID ' set @sql = ' create procedure sp_DTA_view_table_helper_relational @SessionID int as begin ' + @sql_select_relational + @sql_from + ' end ' execute(@sql) go /**************************************************************/ /* Workload Analysis Report XML */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_wkld_analysis_helper_xml...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_wkld_analysis_helper_xml' and type = 'P') drop procedure sp_DTA_wkld_analysis_helper_xml go create procedure sp_DTA_wkld_analysis_helper_xml @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [WorkloadAnalysisReport!1!!ELEMENT], NULL as [Statements!2!Type!ELEMENT] , NULL as [Statements!2!NumberOfStatements!ELEMENT], NULL as [Statements!2!CostDecreased!ELEMENT], NULL as [Statements!2!CostIncreased!ELEMENT], NULL as [Statements!2!CostSame!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [WorkloadAnalysis!1!!ELEMENT], CASE WHEN StatementType = 0 THEN 'Select' WHEN StatementType = 1 THEN 'Update' WHEN StatementType = 2 THEN 'Insert' WHEN StatementType = 3 THEN 'Delete' WHEN StatementType = 4 THEN 'Merge' end as [Statements!2!Type!ELEMENT] , COUNT(QueryID) as [Statements!2!NumberOfStatements!ELEMENT], SUM(CASE WHEN RecommendedCost<CurrentCost THEN 1 else 0 end) as [Statements!2!CostDecreased!ELEMENT], SUM(CASE WHEN RecommendedCost>CurrentCost THEN 1 else 0 end) as [Statements!2!CostIncreased!ELEMENT], SUM(CASE WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) as [Statements!2!CostSame!ELEMENT] from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID group by StatementType FOR XML EXPLICIT end go /**************************************************************/ /* Workload Analysis Report */ /**************************************************************/ print '' print 'Creating procedure sp_DTA_wkld_analysis_helper_relational...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_wkld_analysis_helper_relational' and type = 'P') drop procedure sp_DTA_wkld_analysis_helper_relational go declare @sql nvarchar(4000) declare @sql_select nvarchar(4000) declare @sql_from nvarchar(4000) declare @localized_string_stmtType03 nvarchar(128) declare @localized_string_numStmts02 nvarchar(128) declare @localized_string_costDecreased nvarchar(128) declare @localized_string_costIncreased nvarchar(128) declare @localized_string_costSame nvarchar(128) set @localized_string_stmtType03 = N'"Statement Type"' set @localized_string_numStmts02 = N'"Number Of Statements"' set @localized_string_costDecreased = N'"Cost Decreased"' set @localized_string_costIncreased = N'"Cost Increased"' set @localized_string_costSame = N'"No Change"' set @sql_select = ' select ' + @localized_string_stmtType03 + ' = CASE WHEN StatementType = 0 THEN ''Select'' WHEN StatementType = 1 THEN ''Update'' WHEN StatementType = 2 THEN ''Insert'' WHEN StatementType = 3 THEN ''Delete'' WHEN StatementType = 4 THEN ''Merge'' end, ' + @localized_string_numStmts02 + ' =COUNT(QueryID), ' + @localized_string_costDecreased + ' =SUM(CASE WHEN RecommendedCost<CurrentCost THEN 1 else 0 end), ' + @localized_string_costIncreased +' =SUM(CASE WHEN RecommendedCost>CurrentCost THEN 1 else 0 end), ' + @localized_string_costSame + ' =SUM(CASE WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) ' set @sql_from = ' from [msdb].[dbo].[DTA_reports_query] where SessionID=@SessionID group by StatementType ' set @sql = ' create procedure sp_DTA_wkld_analysis_helper_relational @SessionID int as begin' + @sql_select + @sql_from + ' end ' execute(@sql) go /***************************************************************************** sp_dta_get_session_report @SessionID, - ID of a session requested @ReportID, - ID of a report requested @ReportType - report format: 0  rowset 1 - XML This is the main sp called by DTA/Shell to generate various reports This in turn calls the various helper functions in script *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_session_report...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_session_report' and type = 'P') drop procedure sp_DTA_get_session_report go create procedure sp_DTA_get_session_report @SessionID int, @ReportID int, @ReportType int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end if @ReportType = 0 begin /**************************************************************/ /* Query Cost Report */ /**************************************************************/ if @ReportID = 2 begin exec sp_DTA_query_cost_helper_relational @SessionID end /**************************************************************/ /* Event Frequency Report */ /**************************************************************/ else if @ReportID = 3 begin exec sp_DTA_event_weight_helper_relational @SessionID end /**************************************************************/ /* Query Detail Report */ /**************************************************************/ else if @ReportID = 4 begin exec sp_DTA_query_detail_helper_relational @SessionID end /**************************************************************/ /* Current Query Index Relations Report */ /**************************************************************/ else if @ReportID = 5 begin exec sp_DTA_query_indexrelations_helper_relational @SessionID,0 end /**************************************************************/ /* Recommended Query Index Relations Report */ /**************************************************************/ else if @ReportID = 6 begin exec sp_DTA_query_indexrelations_helper_relational @SessionID,1 end /**************************************************************/ /* Current Query Cost Range */ /**************************************************************/ else if @ReportID = 7 begin exec sp_DTA_query_costrange_helper_relational @SessionID end /**************************************************************/ /* Recommended Query Cost Range */ /**************************************************************/ else if @ReportID = 8 begin exec sp_DTA_query_costrange_helper_relational @SessionID end /**************************************************************/ /* Current Query Index Usage Report */ /**************************************************************/ else if @ReportID = 9 begin exec sp_DTA_index_usage_helper_relational @SessionID,0 end /**************************************************************/ /* Recommended Query Index Usage Report */ /**************************************************************/ else if @ReportID = 10 begin exec sp_DTA_index_usage_helper_relational @SessionID,1 end /**************************************************************/ /* Current Index Detail Report */ /**************************************************************/ else if @ReportID = 11 begin exec sp_DTA_index_detail_current_helper_relational @SessionID end /**************************************************************/ /* Recommended Index Detail Report */ /**************************************************************/ else if @ReportID = 12 begin exec sp_DTA_index_detail_recommended_helper_relational @SessionID end /**************************************************************/ /* View Table Relations Report */ /**************************************************************/ else if @ReportID = 13 begin exec sp_DTA_view_table_helper_relational @SessionID end /**************************************************************/ /* Workload Analysis Report */ /**************************************************************/ else if @ReportID = 14 begin exec sp_DTA_wkld_analysis_helper_relational @SessionID end /**************************************************************/ /* All object access reports */ /**************************************************************/ else if @ReportID = 15 begin exec sp_DTA_database_access_helper_relational @SessionID end else if @ReportID = 16 begin exec sp_DTA_table_access_helper_relational @SessionID end else if @ReportID = 17 begin exec sp_DTA_column_access_helper_relational @SessionID end end -- XML Reports else if @ReportType = 1 begin /**************************************************************/ /* Query Cost Report */ /**************************************************************/ if @ReportID = 2 begin exec sp_DTA_query_cost_helper_xml @SessionID end /**************************************************************/ /* Event Frequency Report */ /**************************************************************/ else if @ReportID = 3 begin exec sp_DTA_event_weight_helper_xml @SessionID end /**************************************************************/ /* Query Detail Report */ /**************************************************************/ else if @ReportID = 4 begin exec sp_DTA_query_detail_helper_xml @SessionID end /**************************************************************/ /* Current Query Index Relations Report */ /**************************************************************/ else if @ReportID = 5 begin exec sp_DTA_query_indexrelations_helper_xml @SessionID,0 end /**************************************************************/ /* Recommended Query Index Relations Report */ /**************************************************************/ else if @ReportID = 6 begin exec sp_DTA_query_indexrelations_helper_xml @SessionID,1 end /**************************************************************/ /* Current Query Cost Range */ /**************************************************************/ else if @ReportID = 7 begin exec sp_DTA_query_costrange_helper_xml @SessionID end /**************************************************************/ /* Recommended Query Cost Range */ /**************************************************************/ else if @ReportID = 8 begin exec sp_DTA_query_costrange_helper_xml @SessionID end /**************************************************************/ /* Current Query Index Usage Report */ /**************************************************************/ else if @ReportID = 9 begin exec sp_DTA_index_usage_helper_xml @SessionID,0 end /**************************************************************/ /* Recommended Query Index Usage Report */ /**************************************************************/ else if @ReportID = 10 begin exec sp_DTA_index_usage_helper_xml @SessionID,1 end /**************************************************************/ /* Current Index Detail Report */ /**************************************************************/ else if @ReportID = 11 begin exec sp_DTA_index_current_detail_helper_xml @SessionID end /**************************************************************/ /* Recommended Index Detail Report */ /**************************************************************/ else if @ReportID = 12 begin exec sp_DTA_index_recommended_detail_helper_xml @SessionID end /**************************************************************/ /* View Table Relations Report */ /**************************************************************/ else if @ReportID = 13 begin exec sp_DTA_view_table_helper_xml @SessionID end /**************************************************************/ /* Workload Analysis Report */ /**************************************************************/ else if @ReportID = 14 begin exec sp_DTA_wkld_analysis_helper_xml @SessionID end /**************************************************************/ /* All object access reports */ /**************************************************************/ else if @ReportID = 15 begin exec sp_DTA_database_access_helper_xml @SessionID end else if @ReportID = 16 begin exec sp_DTA_table_access_helper_xml @SessionID end else if @ReportID = 17 begin exec sp_DTA_column_access_helper_xml @SessionID end end end go grant exec on sp_DTA_get_session_report to public go /***************************************************************************** Start of stored procs used by DTAEngine90 DTAEngine90 also calls SP's internally *****************************************************************************/ /***************************************************************************** sp_DTA_set_tuninglogtablename @LogTableName - Tuning Log table name @SessionID, - ID of a session requested Sets the tuning log table name passed by DTA *****************************************************************************/ print '' print 'Creating procedure sp_DTA_set_tuninglogtablename...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_tuninglogtablename' and type = 'P') drop procedure sp_DTA_set_tuninglogtablename go create procedure sp_DTA_set_tuninglogtablename @LogTableName nvarchar(1280), @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end update [msdb].[dbo].[DTA_input] set LogTableName = @LogTableName where SessionID = @SessionID end go grant exec on sp_DTA_set_tuninglogtablename to public go /***************************************************************************** sp_DTA_get_tuningoptions @SessionID - ID of a session requested Gets the tuning options from [msdb].[dbo].[DTA_input] for this session *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_tuningoptions...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tuningoptions' and type = 'P') drop procedure sp_DTA_get_tuningoptions go create procedure sp_DTA_get_tuningoptions @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select TuningOptions from [msdb].[dbo].[DTA_input] where SessionID = @SessionID end go grant exec on sp_DTA_get_tuningoptions to public go /***************************************************************************** sp_DTA_get_interactivestatus @SessionID - ID of a session requested Gets the interactivestatus bit from DTA_input *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_interactivestatus...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_interactivestatus' and type = 'P') drop procedure sp_DTA_get_interactivestatus go create procedure sp_DTA_get_interactivestatus @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select InteractiveStatus from [msdb].[dbo].[DTA_input] where SessionID = @SessionID end go grant exec on sp_DTA_get_interactivestatus to public go /***************************************************************************** sp_DTA_insert_progressinformation @SessionID - ID of a session requested Inserts the progress information in DTA_Progress table *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_progressinformation...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_progressinformation' and type = 'P') drop procedure sp_DTA_insert_progressinformation go create procedure sp_DTA_insert_progressinformation @SessionID int, @TuningStage int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end INSERT into [msdb].[dbo].[DTA_progress] (SessionID,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage) values(@SessionID,0,0,@TuningStage,N'',N'',N'') end go grant exec on sp_DTA_insert_progressinformation to public go /***************************************************************************** sp_DTA_set_progressinformation @SessionID - ID of a session requested Sets the progress information in DTA_Progress table *****************************************************************************/ print '' print 'Creating procedure sp_DTA_set_progressinformation...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_progressinformation' and type = 'P') drop procedure sp_DTA_set_progressinformation go create procedure sp_DTA_set_progressinformation @SessionID int, @WorkloadConsumption int, @TuningStage int, @EstImprovement int, @ConsumingWorkLoadMessage nvarchar(256) = N'', @PerformingAnalysisMessage nvarchar(256)= N'', @GeneratingReportsMessage nvarchar(256)= N'' as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end update [msdb].[dbo].[DTA_progress] set WorkloadConsumption = @WorkloadConsumption, EstImprovement = @EstImprovement, ProgressEventTime = GetDate(), ConsumingWorkLoadMessage = @ConsumingWorkLoadMessage , PerformingAnalysisMessage = @PerformingAnalysisMessage, GeneratingReportsMessage = @GeneratingReportsMessage where SessionID=@SessionID and TuningStage = @TuningStage end go grant exec on sp_DTA_set_progressinformation to public go /***************************************************************************** sp_DTA_set_outputinformation @SessionID - ID of a session requested @TuningResults - Tuning results @FinishStatus - Finish status Sets the output information *****************************************************************************/ print '' print 'Creating procedure sp_DTA_set_outputinformation...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_outputinformation' and type = 'P') drop procedure sp_DTA_set_outputinformation go create procedure sp_DTA_set_outputinformation @SessionID int, @TuningResults ntext, @FinishStatus tinyint as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end Insert into [msdb].[dbo].[DTA_output]([SessionID], [TuningResults],[FinishStatus]) values(@SessionID,@TuningResults,@FinishStatus) end go grant exec on sp_DTA_set_outputinformation to public go /***************************************************************************** Table Inserts called by DTAEngine90 since DBO's dont have bulkadmin privileges *****************************************************************************/ /***************************************************************************** sp_DTA_insert_reports_database @SessionID int @DatabaseName sysname *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_database...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_database' and type = 'P') drop procedure sp_DTA_insert_reports_database go create procedure sp_DTA_insert_reports_database @SessionID int, @DatabaseName sysname, @IsDatabaseSelectedToTune int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end Insert into [msdb].[dbo].[DTA_reports_database]([SessionID],[DatabaseName],[IsDatabaseSelectedToTune]) values(@SessionID,@DatabaseName,@IsDatabaseSelectedToTune) end go grant exec on sp_DTA_insert_reports_database to public go /***************************************************************************** sp_DTA_insert_reports_partitionscheme @SessionID int @PartitionFunctionID int @PartitionSchemeName sysname @PartitionSchemeDefinition ntext *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_partitionscheme...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_partitionscheme' and type = 'P') drop procedure sp_DTA_insert_reports_partitionscheme go create procedure sp_DTA_insert_reports_partitionscheme @SessionID int, @PartitionFunctionID int, @PartitionSchemeName sysname, @PartitionSchemeDefinition ntext as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end Insert into [msdb].[dbo].[DTA_reports_partitionscheme]( [PartitionFunctionID],[PartitionSchemeName],[PartitionSchemeDefinition]) values(@PartitionFunctionID,@PartitionSchemeName,@PartitionSchemeDefinition) end go grant exec on sp_DTA_insert_reports_partitionscheme to public go /***************************************************************************** sp_DTA_insert_reports_partitionfunction SessionID int DatabaseID int PartitionFunctionName sysname PartitionFunctionDefinition ntext *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_partitionfunction...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_partitionfunction' and type = 'P') drop procedure sp_DTA_insert_reports_partitionfunction go create procedure sp_DTA_insert_reports_partitionfunction @SessionID int, @DatabaseID int, @PartitionFunctionName sysname, @PartitionFunctionDefinition ntext as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end Insert into [msdb].[dbo].[DTA_reports_partitionfunction]([DatabaseID],[PartitionFunctionName],[PartitionFunctionDefinition]) values(@DatabaseID,@PartitionFunctionName,@PartitionFunctionDefinition) end go grant exec on sp_DTA_insert_reports_partitionfunction to public go /***************************************************************************** sp_DTA_insert_reports_column @SessionID int, @TableID int @ColumnName sysname */ print '' print 'Creating procedure sp_DTA_insert_reports_column...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_column' and type = 'P') drop procedure sp_DTA_insert_reports_column go create procedure sp_DTA_insert_reports_column @SessionID int, @TableID int, @ColumnName sysname as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_column]([TableID], [ColumnName]) values( @TableID ,@ColumnName) end go grant exec on sp_DTA_insert_reports_column to public go /***************************************************************************** sp_DTA_insert_reports_tableview @SessionID int, @TableID int, @ViewID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_tableview...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_tableview' and type = 'P') drop procedure sp_DTA_insert_reports_tableview go create procedure sp_DTA_insert_reports_tableview @SessionID int, @TableID int, @ViewID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_tableview]([TableID], [ViewID]) values(@TableID,@ViewID) end go grant exec on sp_DTA_insert_reports_tableview to public go /***************************************************************************** sp_DTA_insert_reports_query @SessionID int @QueryID int @StatementType smallint @StatementString ntext @CurrentCost float @RecommendedCost float @Weight float @EventString ntext @EventWeight float *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_query...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_query' and type = 'P') drop procedure sp_DTA_insert_reports_query go create procedure sp_DTA_insert_reports_query @SessionID int, @QueryID int, @StatementType smallint, @StatementString ntext, @CurrentCost float, @RecommendedCost float, @Weight float, @EventString ntext, @EventWeight float as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_query]([SessionID],[QueryID], [StatementType], [StatementString], [CurrentCost], [RecommendedCost], [Weight], [EventString], [EventWeight]) values(@SessionID,@QueryID,@StatementType,@StatementString,@CurrentCost,@RecommendedCost,@Weight,@EventString,@EventWeight) end go grant exec on sp_DTA_insert_reports_query to public go /***************************************************************************** sp_DTA_insert_reports_index @SessionID int @TableID int @IndexName sysname @IsClustered bit @IsUnique bit @IsHeap bit @IsExisting bit @IsFiltered bit @Storage int @NumRows bigint @IsRecommended bit @RecommendedStorage int @PartitionSchemeID int @SessionUniquefier int @FilterDefinition nvarchar(1024) *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_index...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_index' and type = 'P') drop procedure sp_DTA_insert_reports_index go create procedure sp_DTA_insert_reports_index @SessionID int, @TableID int, @IndexName sysname, @IsClustered bit, @IsUnique bit, @IsHeap bit, @IsExisting bit, @IsFiltered bit, @Storage int, @NumRows bigint, @IsRecommended bit, @RecommendedStorage int, @PartitionSchemeID int, @SessionUniquefier int, @FilterDefinition nvarchar(1024) as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_index]([TableID], [IndexName], [IsClustered], [IsUnique], [IsHeap],[IsExisting], [IsFiltered],[Storage], [NumRows], [IsRecommended], [RecommendedStorage], [PartitionSchemeID],[SessionUniquefier],[FilterDefinition]) values(@TableID,@IndexName,@IsClustered,@IsUnique,@IsHeap,@IsExisting,@IsFiltered,@Storage,@NumRows,@IsRecommended,@RecommendedStorage,@PartitionSchemeID,@SessionUniquefier,@FilterDefinition) end go grant exec on sp_DTA_insert_reports_index to public go /***************************************************************************** sp_DTA_insert_reports_table @SessionID int @DatabaseID int @SchemaName sysname @TableName sysname @IsView bit *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_table...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_table' and type = 'P') drop procedure sp_DTA_insert_reports_table GO create procedure sp_DTA_insert_reports_table @SessionID int, @DatabaseID int, @SchemaName sysname, @TableName sysname, @IsView bit as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_table]([DatabaseID], [SchemaName], [TableName], [IsView]) values(@DatabaseID,@SchemaName,@TableName,@IsView) end GO grant exec on sp_DTA_insert_reports_table to public GO /***************************************************************************** sp_DTA_insert_reports_queryindex @SessionID int @QueryID int @IndexID int @IsRecommendedConfiguration bit @Cost float *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_queryindex...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_queryindex' and type = 'P') drop procedure sp_DTA_insert_reports_queryindex go create procedure sp_DTA_insert_reports_queryindex @SessionID int, @QueryID int, @IndexID int, @IsRecommendedConfiguration bit as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_queryindex]([SessionID],[QueryID],[IndexID], [IsRecommendedConfiguration]) values(@SessionID,@QueryID,@IndexID,@IsRecommendedConfiguration) end go grant exec on sp_DTA_insert_reports_queryindex to public go /***************************************************************************** sp_DTA_insert_reports_indexcolumn @SessionID int @IndexID int @ColumnID int @ColumnOrder int @PartitionColumnOrder int @IsKeyColumn bit @IsDescendingColumn bit *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_indexcolumn...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_indexcolumn' and type = 'P') drop procedure sp_DTA_insert_reports_indexcolumn go create procedure sp_DTA_insert_reports_indexcolumn @SessionID int, @IndexID int, @ColumnID int, @ColumnOrder int, @PartitionColumnOrder int, @IsKeyColumn bit, @IsDescendingColumn bit as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_indexcolumn]([IndexID], [ColumnID], [ColumnOrder], [PartitionColumnOrder], [IsKeyColumn], [IsDescendingColumn]) values(@IndexID,@ColumnID,@ColumnOrder,@PartitionColumnOrder,@IsKeyColumn,@IsDescendingColumn) end go grant exec on sp_DTA_insert_reports_indexcolumn to public go /***************************************************************************** sp_DTA_insert_reports_querytable @SessionID int @QueryID int @TableID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_querytable...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querytable' and type = 'P') drop procedure sp_DTA_insert_reports_querytable go create procedure sp_DTA_insert_reports_querytable @SessionID int, @QueryID int, @TableID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_querytable]([SessionID], [QueryID],[TableID]) values(@SessionID,@QueryID,@TableID) end go grant exec on sp_DTA_insert_reports_querytable to public go /***************************************************************************** sp_DTA_insert_reports_querydatabase @SessionID int @QueryID int @DatabaseID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_querydatabase...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querydatabase' and type = 'P') drop procedure sp_DTA_insert_reports_querydatabase go create procedure sp_DTA_insert_reports_querydatabase @SessionID int, @QueryID int, @DatabaseID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_querydatabase]([SessionID], [QueryID],[DatabaseID]) values(@SessionID,@QueryID,@DatabaseID) end go grant exec on sp_DTA_insert_reports_querydatabase to public go /***************************************************************************** sp_DTA_insert_reports_querycolumn @SessionID int @QueryID int @ColumnID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_reports_querycolumn...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querycolumn' and type = 'P') drop procedure sp_DTA_insert_reports_querycolumn go create procedure sp_DTA_insert_reports_querycolumn @SessionID int, @QueryID int, @ColumnID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_reports_querycolumn]([QueryID], [ColumnID],[SessionID]) values(@QueryID,@ColumnID,@SessionID ) end go grant exec on sp_DTA_insert_reports_querycolumn to public go /***************************************************************************** sp_DTA_insert_DTA_tuninglog @SessionID int @RowID int @CategoryID char(4) @Event ntext @Statement ntext @Frequency int @Reason ntext *****************************************************************************/ print '' print 'Creating procedure sp_DTA_insert_DTA_tuninglog...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_DTA_tuninglog' and type = 'P') drop procedure sp_DTA_insert_DTA_tuninglog go create procedure sp_DTA_insert_DTA_tuninglog @SessionID int, @RowID int, @CategoryID char(4), @Event ntext, @Statement ntext, @Frequency int, @Reason ntext as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end insert into [msdb].[dbo].[DTA_tuninglog]([SessionID], [RowID], [CategoryID], [Event], [Statement], [Frequency], [Reason]) values(@SessionID, @RowID, @CategoryID, @Event, @Statement, @Frequency, @Reason) end go grant exec on sp_DTA_insert_DTA_tuninglog to public go /***************************************************************************** sp_DTA_get_databasetableids @SessionID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_databasetableids...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_databasetableids' and type = 'P') drop procedure sp_DTA_get_databasetableids go create procedure sp_DTA_get_databasetableids @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select DatabaseID,DatabaseName from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID end go grant exec on sp_DTA_get_databasetableids to public go /***************************************************************************** sp_DTA_get_pftableids @SessionID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_pftableids...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_pftableids' and type = 'P') drop procedure sp_DTA_get_pftableids go create procedure sp_DTA_get_pftableids @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select PartitionFunctionID ,DatabaseName ,PartitionFunctionName from [msdb].[dbo].[DTA_reports_partitionfunction] as PF, [msdb].[dbo].[DTA_reports_database] as D where PF.DatabaseID = D.DatabaseID and D.SessionID = @SessionID end go grant exec on sp_DTA_get_pftableids to public go /***************************************************************************** sp_DTA_get_pstableids @SessionID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_pstableids...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_pstableids' and type = 'P') drop procedure sp_DTA_get_pstableids go create procedure sp_DTA_get_pstableids @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select PartitionSchemeID,DatabaseName,PartitionSchemeName from [msdb].[dbo].[DTA_reports_partitionfunction] as PF, [msdb].[dbo].[DTA_reports_partitionscheme] as PS, [msdb].[dbo].[DTA_reports_database] as D where PS.PartitionFunctionID = PF.PartitionFunctionID and PF.DatabaseID = D.DatabaseID and D.SessionID = @SessionID end go grant exec on sp_DTA_get_pstableids to public go /***************************************************************************** sp_DTA_get_tableids @SessionID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_tableids...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tableids' and type = 'P') drop procedure sp_DTA_get_tableids go create procedure sp_DTA_get_tableids @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select TableID,DatabaseName,SchemaName,TableName from [msdb].[dbo].[DTA_reports_table] as T,[msdb].[dbo].[DTA_reports_database] as D where T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID end go grant exec on sp_DTA_get_tableids to public go /***************************************************************************** sp_DTA_get_columntableids @SessionID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_columntableids...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_columntableids' and type = 'P') drop procedure sp_DTA_get_columntableids go create procedure sp_DTA_get_columntableids @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select ColumnID,DatabaseName,SchemaName,TableName,ColumnName from [msdb].[dbo].[DTA_reports_column] as C, [msdb].[dbo].[DTA_reports_table] as T,[msdb].[dbo].[DTA_reports_database] as D where C.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID end go grant exec on sp_DTA_get_columntableids to public go /***************************************************************************** sp_DTA_get_indexableids @SessionID int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_get_indexableids...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_indexableids' and type = 'P') drop procedure sp_DTA_get_indexableids go create procedure sp_DTA_get_indexableids @SessionID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end select IndexID,DatabaseName,SchemaName,TableName,IndexName,SessionUniquefier from [msdb].[dbo].[DTA_reports_index] as I,[msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID end go grant exec on sp_DTA_get_indexableids to public go /***************************************************************************** sp_DTA_update_tuninglog_errorfrequency @SessionID int @RowID int @Frequency int *****************************************************************************/ print '' print 'Creating procedure sp_DTA_update_tuninglog_errorfrequency...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_update_tuninglog_errorfrequency' and type = 'P') drop procedure sp_DTA_update_tuninglog_errorfrequency go create procedure sp_DTA_update_tuninglog_errorfrequency @SessionID int, @Frequency int, @RowID int as begin declare @retval int set nocount on exec @retval = sp_DTA_check_permission @SessionID if @retval = 1 begin raiserror(31002,-1,-1) return(1) end update [msdb].[dbo].[DTA_tuninglog] set [Frequency]=@Frequency where [RowID]=@RowID and [SessionID] = @SessionID end go grant exec on sp_DTA_update_tuninglog_errorfrequency to public go /***************************************************************************** sp_DTA_start_xmlprefix Doesnt take any arguments Add prefix for XML reports *****************************************************************************/ print '' print 'Creating procedure sp_DTA_start_xmlprefix...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_start_xmlprefix' and type = 'P') drop procedure sp_DTA_start_xmlprefix go create procedure sp_DTA_start_xmlprefix as begin declare @startTags nvarchar(128) set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>' select @startTags end go /***************************************************************************** sp_DTA_end_xmlprefix Doesnt take any arguments Add postfix tags for XML reports *****************************************************************************/ print '' print 'Creating procedure sp_DTA_end_xmlprefix...' go if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_end_xmlprefix' and type = 'P') drop procedure sp_DTA_end_xmlprefix go create procedure sp_DTA_end_xmlprefix as begin declare @endTags nvarchar(128) set @endTags = N'</AnalysisReport></DTAOutput></DTAXML>' select @endTags end go /***************************************************************************** fn_DTA_unquote_dbname DTA client passes in quoted db names because of a bug fix. The dbnames are stored unquoted. master and [master] map to master. Hence unquote dbname *****************************************************************************/ print '' print 'Creating function fn_DTA_unquote_dbname...' go if object_id('fn_DTA_unquote_dbname') is not null drop function fn_DTA_unquote_dbname go create function fn_DTA_unquote_dbname(@dbname nvarchar(258) ) returns sysname as begin declare @unquote nvarchar(258) set @unquote = @dbname if(patindex(N'[[]%',@unquote) > 0) select @unquote = right(@unquote, LEN(@unquote)-1) if(patindex(N'%]',@unquote) > 0) select @unquote = left(@unquote, LEN(@unquote)-1) select @unquote =REPLACE (@unquote,N']]',N']') return @unquote end go 4VS_VERSION_INFOd@2 @?StringFileInfo040904B02 PlatformNT INTEL X86ILegalTrademarksMicrosoft SQL Server is a registered trademark of Microsoft Corporation. CommentsSQL&GoldenBitsTrueLCompanyNameMicrosoft CorporationfFileDescriptionDatabase Tuning Advisor Enginet*FileVersion2009.0100.1600.01 ((KJ_RTM).100402-1540 )4 InternalNameDTAEnginen%LegalCopyrightMicrosoft Corp. All rights reserved.DOriginalFilenameDTAEngine.RLLJProductNameMicrosoft SQL Server> ProductVersion10.50.1600.1DVarFileInfo$Translation 3------------------- DTAEngine Done --------------- 6------------------- DTAEngine Start ----------------- 'Insufficient arguments for -%s option. &Argument for -%s option not provided. jUsage: DTAEngine -D database-name [,...n] {-i workload-file | -t workload-table-name} -o script-filename [-S instance\server] [-R instance\server] {-U username [-P password] | -E} [-f tuning-feature-set] [-a analysis-report-list] r[-A time-bound-secs] [-K keep-existing-indexes] [-B storage-bound] [-e error-table-name] [-C max-key-columns] l[-T table-list-file] [-m minimum-improvement] [-F] [-N] [-X xml_input_file] [-O xml_output_file] [-v] (Incorrect command line argument near %s Unknown option %s specified. $Specify a number for stroage bound. 'Value for storage bound should be > 0. /Specify a number for maximum columns in index. ?Value for maximum columns in indxe should be between 1 and 16. -Specify a number for the feature set option. Value for the feature set option should be between 0 and 3. To combine feature sets append the option values delimited by space character within quotes . Example -f "2 3" implies keep indexed views and partitions Specify a number for -K option. &Value for -K option should be 0 or 1. Specify a number for -M option. /Value for -M option should be between 0 and 2. Specify a number for -m option. 5Specify a number for number of queries to tbe tuned. @Value for number of queries that should be tuned should be > 0. .Error: Database(s) to be tuned not specified. /Error: Workload (File or Table) not specified. 'Error: Specify either -o or -O option. AError: Incorrect format for table list in file %s near line: %s @Invalid file name %s specified in the table list file option. 4Specified table [%s].[%s] not found in database %s. ZUnable to create output script file %s. Use -F option to overwrite existing script file. FLogin failed on tuned server. Check user name (-U) and password (-P). *Login failed for user %s on tuned server. #This tuning mode is not supported. 4This tuning mode is not supported in SQL Server 7.0 PTuning indexed views requires Enterprise Edition of Microsoft SQL Server 2000 . ATuning indexed views is not supported in Microsoft SQL Server 7.0!Error writing to script file %s. %Error closing output script file %s. NoneError: setting tables to tune. Error: Unable to execute query. Error: Exception encountered PError: The events in the workload were ignored. Check if the workload is empty. 3Error: The events in the workload were unparseable Error: Invalid input specified. WError: Table that was referenced during tuning/evaluate doesn't exist in the database. sError: Test production feature is supported only if both test and production server versions are SQL 2005 and laterHeap}Error: The test and production server names specified refer to the same server. Make sure that the servers are not identical. UnlimitedUse one of the following methods to increase storage space: (1) If you are using the graphical user interface, enter the required value for Define max. space for recommendations (MB) in the Advanced Options of the Tuning Options tabbed page; (2) If you are using dta.exe, specify the maximum space value for the -B argument; (3) If you are using an XML input file, specify the maximum space value for the <StorageBoundInMB> element under <TuningOptions>All the events in the workload were ignored due to syntax errors.The most common reason for this error is that the database to connect has not been set correctly.KTuning indexed views requires SQL Server 2005 and later Enterprise Edition.3Tuning this edition of SQL Server is not permitted. CategoryIDEvent Statement FrequencyReason)Percent MERGE statements in the tuned setVFiltered index ''%s'' specified in input configuration doesnt have a filter definitionError: Insufficient memory. $Error: Connection to server broken. 5Error: Server returned error when optimizing a query !Error: DTAEngine internal error. +/* Created by: Database Tuning Advisor */ /* Date: %s */ /* Time: %s */ /* Server: %s */ /* Database(s) Tuned: [%s] /* Workload file: %s */ /* Workload table: %s */ */* Time taken for analysis = %lf secs */  USE [%s] go SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF go eBEGIN TRANSACTION DROP INDEX %s.%s IF (@@error <> 0) ROLLBACK TRANSACTION COMMIT TRANSACTION go  >BEGIN TRANSACTION CREATE CLUSTERED INDEX [%s] ON [%s].[%s] ( 4BEGIN TRANSACTION CREATE INDEX [%s] ON [%s].[%s] ( 4CREATE VIEW [%s].[%s] WITH SCHEMABINDING AS %s go EBEGIN TRANSACTION CREATE UNIQUE CLUSTERED INDEX [%s] ON [%s].[%s] ( E%s) IF (@@error <> 0) ROLLBACK TRANSACTION COMMIT TRANSACTION go %s, / /* Statistics to support recommendations */ CREATE STATISTICS %s ON %s ( %s) M/* Minimum improvement specified = %7.2lf, Expected improvement = %5.2lf */ G/* No minimum improvement specified, Expected improvement = %5.2lf */ p/* Keep all existing indexes: %s, Feature Set = %s, Number of queries to tune = %d, Storage bound = %d MBs */ TRUEFALSEUnsupported Server Version8/* Error: Storage Bound is less than Minimum Required */Thorough analysisIndexes and Indexed Views Indexes onlyIndexed views onlyb/* Storage for current configuration = %d MBs, Storage for recommended configuration = %d MBs */ Error: Password too long. Error: -U option not specified. %Error: must specify -U or -E option. )/* No tunable queries in the workload. */A/* Expected improvement does not meet the specified -m option. */K/* Recommendation has reduced storage compared to current configuration. */n/* Expected improvement is negative since user specified reduced storage compared to current configuration. */a/* Current configuration is better or recommendation could not meet the specified constraints. */Invalid argument :Error: Incorrect format specified for -t option argument. ;Error: Owner name, if specified in -t option, must be dbo. 1Error: Unable to open input file specified : %s. GError: Workload in the input table specified : %s could not be opened. The minimum storage space required for the existing physical design structures (PDS) you have selected to keep is larger than the storage space provided. Choose fewer PDS to keep, or set the storage space to be larger than %1.0lf MBs.KWarning:This option is deprecated.Instead use -A to specify the timebound. Specify a number for -k option. )Error:Failed to open input workload file.-Error: Use either -c or -X option, not both. Unable to parse input file %s. Partitions are not supported . 'Error on line %d, position %d in "%s". Workload2Error: Server name exceeds maximum allowed length 8Error: Test Server name exceeds maximum allowed length 1Error: File name exceeds maximum allowed length *Specify a valid number > 0 for -A option. 3Error: Database name exceeds maximum allowed length0Error: Invalid number of arguments for -f option.Error: Exceeds maximum allowed password length(Error: Exceeds maximum allowed user name0Error: Invalid number of arguments for -a optionaTuning online indexes is not supported in Microsoft SQL Server 7.0 or Microsoft SQL Server 2000. ZTuning online Indexes requires Enterprise Edition of Microsoft SQL Server 2005 and later. OWarning:Error message table already exists. Error messages will not be written.QWarning:Could not create error message table. Error messages will not be written.-Arguments passed in are incorrect.Check usage(Error: Could not connect to database %s User aborted sessionSuccess'Error: Out of memory parsing input XML>Error: Failed to parse input XML. Check schema for correctness%Error: Could not load input XML file.3Error: Did not find opening element (DTAXML) in XMLDateTimeServerDatabase(s) to tune Workload fileWorkload tableMaximum tuning timeTime taken for tuning(Minimum improvement percentage specified Estimated percentage improvement%Maximum space for recommendation (MB)Space used currently (MB)!Space used by recommendation (MB)6Error: The specified workload (file or table) is emptyDaysDayKError: Failed to parse input XML. Check DTAInput hierarchy for correctness.OError: Failed to parse input XML. Arguments specified in input XML was invalid.Error: Failed to parse input XML. Could not parse Server type specified.In USC make sure that -S option matches the server name specified via XMLError: Failed to parse input XML. Could not parse Database type specified.In USC make sure that the database names in the argument and that specified in Configuration type match?Error: Failed to parse input XML. Could not parse Workload type>Error: Failed to parse input XML. Could not parse Options type0Error: Invalid analysis report options specified2Error: Feature set options specified are incorrectSError : Indexed views are not supported when the feature set is online indexes only*Error: Invalid number of queries specified'Error: Time bound specified is invalid.)Error: Storage bound specified is inavlid'Error: Max columns specified is invalidGError: Failed to parse input XML. Could not parse Schema type specifiedMError: Failed to parse input XML. Could not parseConfiguration type specifiedEError: Failed to parse input XML. Could not parseTable type specifiedEError: Failed to parse input XML. Could not parse View type specifiedQError: Failed to parse input XML. Could not parse Recommendation type specifiedIError: Failed to parse input XML. Could not parse Create type specifiedFError: Failed to parse input XML. Could not parse Drop type specifiedGError: Failed to parse input XML. Could not parse Index type specifiedHError: Failed to parse input XML. Could not parse Column type specifiedLError: Failed to parse input XML. Could not parse Statistics type specifiedQError: Failed to parse input XML. Could not parse PartitionScheme type specifiedTError: Failed to parse input XML. Could not parse PartitionFunction type specifiedVError: Cannot create shell database. Check tuning log table, if specified, for details%Error: Invalid Drop options specified(Error: Expected text in the node element Error: Failed to parse input XML%Error: Failed to generate output XML.5Error: Failed to create Output element in output XML.4Error: Failed to create Header element in output XMLView Id View Name Event StringWeightCurrent Statement CostRecommended Statement CostFailed to simulate heap for Table ''%s'' specified in input configuration. Simulating heaps for tables requires SQL Server 2005 and laterNumber of statements (Current)"Number of statements (Recommended)FError: Failed to parse input XML. Could not parse Heap type specified3Error: Failed to create Heap element in output XML.<Failed to create heap''%s'' specified in input configurationInlineQDrop Index ''%s'' is invalid with absolute mode specified in input configuration The specified workload (file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StmtStarting or SP:StmtCompleted for workload trace file or tableThe specified workload (file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting or RPC:Completed for workload trace file or table<Error: Failed to create Configuration element in output XML.6Error: Failed to create Summary element in output XML.5Error: Failed to create Detail element in output XML.7Error: Failed to create Database element in output XML.5Error: Failed to create Schema element in output XML.4Error: Failed to create Table element in output XML.3Error: Failed to create View element in output XML.4Error: Failed to create Index element in output XML.9Error: Failed to create Statistics element in output XML.=Error: Failed to create Recoomendation element in output XML.$Error: Server to tune not specified.sError: The tuning option specified is not valid. Make sure that the manageability modes are either Tune or EvaluateBError: This option is supported only in input recommendation mode.(Error: Invalid input file name specified)Error: Invalid output file name specified0Error: Could not load input XML file for parsing@Error: Can not get minimal database information in allotted timegError: An invalid configuration was specified as input. Check if the specifed configuration is correct.EError: Value for minimum improvement option should be less than 100. HoursHourMinutesMinuteeError: Logged in user does not have enough permissions to tune one or more of the databases specified0Error: Tuning single user database not supportedELogin failed on test server. Check user name (-U) and password (-P). )Login failed for user %s on test server. FError: Could not connect to selected default database on tuned server EError: Could not connect to selected default database on test server CError: Could not refresh statistics and table sizes on test server 1Error : Could not connect to the specified server$Error : SQL Express is not supportedThe minimum storage space required for the selected physical design structures exceeds the default storage space selected by Database Engine Tuning Advisor. Either keep fewer physical design structures, or increase the default storage space to be larger than at least %1.0lf MB.Number of events in workloadNumber of events tunedNumber of statements tuned!Events specified to be tuned (-n)*Percent SELECT statements in the tuned set*Percent INSERT statements in the tuned set*Percent DELETE statements in the tuned set*Percent UPDATE statements in the tuned set+Number of indexes recommended to be created4Number of indexes on views recommended to be created.Number of statistics recommended to be created7Number of partition functions recommended to be created5Number of partition schemes recommended to be created+Number of indexes recommended to be dropped4Number of indexes on views recommended to be droppedXServer ''%s'' specified in input configuration is invalid. Specify the right server name\Database ''%s'' specified in input configuration is invalid. Specify the right database nameXSchema ''%s'' specified in input configuration is invalid. Specify the right schema nameVTable ''%s'' specified in input configuration is invalid. Specify the right table nameZNo columns have been specified for index in the configuration. Specify at least one columnoIndex ''%s'' specified in input configuration cannot be dropped because it is a constraint or primary XML indexiIndex ''%s'' specified in input configuration cannot be dropped because it does not exist in the databasesIndex ''%s'' specified in input configuration cannot be created because a duplicate index with the same name existsFTable ''%s'' specified in input configuration was not selected to tuneXColumn ''%s'' specified in input configuration is invalid. Specify the right column name>Failed to create index ''%s'' specified in input configurationEFailed to create indexed view ''%s'' specified in input configurationqView ''%s'' specified in input configuration cannot be created because a duplicate view with the same name existsIFailed to create partition scheme ''%s'' specified in input configurationPartition scheme ''%s'' specified in input configuration cannot be created because a duplicate partition scheme with the same name existsKFailed to create partition function ''%s'' specified in input configurationPartition function ''%s'' specified in input configuration cannot be created because a duplicate partition function with the same name existsEFailed to create statitistics ''%s'' specified in input configuration}Statistics ''%s'' specified in input configuration cannot be created because a duplicate statistics with the same name exists]Index ''%s'' specified in input configuration references non existent partition scheme ''%s''bPartition scheme ''%s'' specified in input configuration references non existent file group ''%s''jPartition scheme ''%s'' specified in input configuration references non existent partition function ''%s''WIndex ''%s'' specified in input configuration references non existent file group ''%s''CConfiguration specified in input is invalid. Table ''%s'' is a heapbConfiguration specified in input is invalid. Table ''%s'' has a preexisting clustered index ''%s''Index ''%s'' specified in input is invalid for the selected partitioning strategy because it doesnt meet alignment requirementseView ''%s'' specified in input cannot be materialized because it does not have a base clustered index8View ''%s'' specified in input could not be materialized Clustered Column NameCost DecreasedCost Increased Cost Range Database Name/Server could not parse filter definition ''%s''%Filtered indexes are not supported . FilteredFilter DefinitionBFiltered indexes option cannot be selected for this tuning option. Index Name No ChangeNumber of referencesNumber of RowsNumber of StatementsTable/View NamePercent Improvement Percent Usage Schema Name Statement IdStatement StringStatement TypeIndex Size (MB) Table NameUniqueView Definition`0S *H D0@1 0 +0h +7Z0X03 +70% <<<Obsolete>>>0!0 +Z.kDdd$/ 10`0L .P\0 +0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority0 070822223102Z 120825070000Z0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Code Signing PCA0"0  *H 0 y}]E941%5IwEqFԌkLRbMIl/$>e# HuEP%+ #A$bEJͳ/"J-|o;99ݽ)f;-2'Hlc\򸔣8P'N0==l9.4. }bxfs Oc,2EJ;PSfQyV>Pn5{$Rf=N+~3nGўJnS00U% 0 +0U0[pir#Q~Mˡr0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority<<>c@0U00Uvp[NQD.Dc0 U0 +{~J&μNtX't*uLxMi|CʇSŸVocDDȚ  )}s9=j8m҈#i 4|.)Bk(q8 ]hͽAkf4|zB{ p֒O8|-=4 b7j#\cZ9`U3; _,˫ 0  *RA^0(ip΀Brv0z0b a>0  *H 0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Code Signing PCA0 091207224029Z 110307224029Z01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR10UMicrosoft Corporation0"0  *H 0 0ErSkO#=Y@8s&S<B8auM\F^i[s)DdY/]nǫ9eħuꇗ&&v89&+ZT!򗍇)I,?}͖ q B5׸?ݎE }K[vrw#}5]PKGmUTN؁B1U SI0奈|^zZ7i2$00U% 0 +0U8xs2_Uƙt0U0U#0vp[NQD.Dc0DU=0;09753http://crl.microsoft.com/pki/crl/products/CSPCA.crl0H+<0:08+0,http://www.microsoft.com/pki/certs/CSPCA.crt0  *H ( oBvCB$e4uq0r7:pQ q(ps{V4ZrN {DO aun+Z 9`jT;1_,nM*v{Ƈֱ*rTn*ȞobKk=/zw9gj{cI&Bޕ" _s2r6)$ROfH&9iPNl.r!m$H@|t8JO00j O%EXzg0  *H 0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority0 060916010447Z 190915070000Z0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Timestamping PCA0"0  *H 0 7nBJqH>S,2ORȃ>3I1(dPKuǨծipfx'f趷 Y")/@VvmdmJT޿ǀL7VhGv\/}%V[jc|<%M9wt]\؆7,u9 |vlnz>q_*Ob`2҃N+"\hE/Pl%ׅvs6ƕz`3[AXn,HoCj&k(0$0U% 0 +0U0[pir#Q~Mˡr0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority<<>c@0 +70UoN?4K;AC0 +7  SubCA0 U0U00  *H M1|PapEsT? -QS9V ތ;ɷQ!oi~k"Flm|"Fӄ6~p]Eݎ*|ɮ2Շc6!v;s!شTeJ(&`;exHϭ:ObX099!dcC/{FeJtn ̝(a|H!8Ŗ2@S=f7"̰wTQ:rD#00 a00  *H 0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Timestamping PCA0 080725190115Z 130725191115Z01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR1'0%U nCipher DSE ESN:85D3-305C-5BCF1%0#UMicrosoft Time-Stamp Service0"0  *H 0 -g%&bK08e46$h{b,FKQ4lZ;G=.5kE<fiJ',gbT'9هN`G#{'@ 15 ӋxaŵA>6X':ڽ]L0 +0 *H  1  +70 +7 10  +70# *H  1Z&;O@nT0X +7 1J0H&$SQL Server 2008 R2http://www.microsoft.com/sql0  *H D|ct"@kIwKb1Ǩ˰\;R*HKrpHcpI~Xb>`, xiVT+LK8S:yDuݎ1x"6|j#,_'OswHB=vP[I V%CQ(n8V \5p@tҸ=X+ B'1I2fa<)0 *H  1 000y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Timestamping PCA a00+]0 *H  1  *H 0 *H  1 100403180704Z0# *H  1qNb օj `g0  *H ssE (W YnL%K$jWMf;rP VmQ'R"#ғ#Tf:]_=-1~ݒߜchB}nUV.Z`t¤d3]Pt3cVlGeۺ[D=Իc7G9jPټ8 |'83Mq^"x.#&5J|!F,/~vB/6PDo0%+m9Gy