MZ@ !L!This program cannot be run in DOS mode. $-TٽL:L:L:FL:BL:RichL:PEL5zK! pO@nr`.rsrcpp@@0H`0H`x  8 P h (@Xp 0@P`p A EHJNDRU|Xl[v^fLb\e\fXgiktdoFqPrvy0zh DTA_SPROCS DTA_TABLES/**************************************************************************** // 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 /***************************************************************/ /* 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 PA3------------------- DTAEngine Done --------------- 6------------------- DTAEngine Start ----------------- -%s yvSpe N0 *gcO -%s yvSpe0 A(ul: DTAEngine -D penc^ Ty [,...n] {-i ]\OweN | -t ]\Owh T} -o ,geN T I[-S [O\ gRhV] [-R [O\ gRhV] {-U (u7b T [-P [x] | -E} [-f OSRƖ] [-a RgbJTRh] =[-A eP6R(y)] [-K OYus g"}_] [-B X[PP6R] [-e h Ty] [-C g'Y.Rpe] G[-T hRheN] [-m g\cؚ 0) ROLLBACK TRANSACTION COMMIT TRANSACTION go  PA>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,  /* ^@bOncv~Oo` */ CREATE STATISTICS %s ON %s ( %s) )/* c[vg\cؚ

OST:g"}_ Microsoft SQL Server 2005 Enterprise Edition NSfؚHr,g0 fJT: mo`h]X[(W0\ NQeQmo`0fJT: elR^mo`h0\ NQeQmo`0 OvSpe Ncknx0hg(ul: elޏc0Rpenc^ %s (u7b]-NbkO݋bR: RgeQ XML eQX[ N: elRgeQ XML0hggg/f&Tcknx: elR}eQ XML eN0: (W XML -N~b N0RwYCQ }(DTAXML)%: elRgeQ XML0hg DTAInput B\!k~g/f&Tcknx0: elRgeQ XML0eQ XML -Nc[vSpeeHe0@: elRgeQ XML0elRgc[v gRhV{|W0(W USC -N nxO -S yNǏ XML c[v gRhV Tyv9SMA: elRgeQ XML0elRgc[vpenc^{|W0(W USC -N nxOSpe-Nvpenc^ TyNMn{|W-Nc[vpenc^ Tyv9SM: elRgeQ XML0elRg]\Ow{|W: elRgeQ XML0elRg y{|W: c[vRgbJT yeHe: c[vRƖ y Ncknx: S_RƖN:NT:g"}_e N/ec"}_ƉV : c[vgpeeHe: c[veP6ReHe0 : c[vX[PP6ReHe : c[vg'YRpeeHe: elRgeQ XML0elRgc[vgg{|W: elRgeQ XML0elRgc[vMn{|W: elRgeQ XML0elRgc[vh{|WPA: elRgeQ XML0elRgc[vƉV{|W: elRgeQ XML0elRgc[v^{|W: elRgeQ XML0elRgc[vR^{|W: elRgeQ XML0elRgc[v Rd{|W: elRgeQ XML0elRgc[v"}_{|W: elRgeQ XML0elRgc[vR{|W: elRgeQ XML0elRgc[v~Oo`{|W): elRgeQ XML0elRgc[v PartitionScheme {|W+: elRgeQ XML0elRgc[v PartitionFunction {|W): elR^ Shell penc^0 gsQ~Oo` g wOSe_h(Yg]c[) : c[v Rd yeHe: pCQ }-N^S+Te,g: elRgeQ XML: elubQ XML0: el(WQ XML -NR^ Output CQ }0: el(WQ XML -NR^ Header CQ }PA#: el(WQ XML -NR^ Configuration CQ }0: el(WQ XML -NR^ Summary CQ }0: el(WQ XML -NR^ Detail CQ }0: el(WQ XML -NR^ Database CQ }0: el(WQ XML -NR^ Schema CQ }0: el(WQ XML -NR^ Table CQ }0: el(WQ XML -NR^ View CQ }0: el(WQ XML -NR^ Index CQ }0 : el(WQ XML -NR^ Statistics CQ }0$: el(WQ XML -NR^ Recoomendation CQ }0: *gc[OSv gRhV0!: c[vOS yeHe0nxOS{t'`!j_/f OS b ċ0O : N(WeQ^!j_-N/ecdk y0: c[veQeN TeHe: c[vQeN TeHe: elR}RgveQ XML eN: (WRMveQelSgNOP^vpenc^Oo`: c[:NeQvMneHe0hgc[vMn/f&Tcknx0: g\cؚ yv NvCQ } <StorageBoundInMB>11uNQsl ]\Ow-Nv@b gNNGW]_eu0[dkvg8^SV/f*gcknxnޏcvpenc^03OS"}_ƉV SQL Server 2005 TfؚHr,gv Enterprise Edition0 NAQOS SQL Server vdkHr,g0{|+R IDNNSsSVOSƖQv MERGE S~vRkeQMn-Nc[v[{ "}_ %s l g[{ hV[IN gRhVelRg[{ hV[IN %s N/ec[{ v"}_0 ][{ [{ hV[INel:NdkOS y b[{ v"}_ y0h4VS_VERSION_INFOd@2 @?StringFileInfo080404B02 PlatformNT INTEL X864LegalTrademarksMicrosoft SQL Server /f Microsoft Corporation vlQFUh0 CommentsSQL&GoldenBitsTrueLCompanyNameMicrosoft CorporationfFileDescriptionDatabase Tuning Advisor Enginet*FileVersion2009.0100.1600.01 ((KJ_RTM).100402-1540 )4 InternalNameDTAEngineTLegalCopyrightMicrosoft Corp. OYu@b gCg)R0DOriginalFilenameDTAEngine.RLLJProductNameMicrosoft SQL Server> ProductVersion10.50.1600.1DVarFileInfo$TranslationPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGX`0S *H D0@1 0 +0h +7Z0X03 +70% <<<Obsolete>>>0!0 +aVG\^ ;d#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 a- 0  *H 0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Timestamping PCA0 080725190217Z 130725191217Z01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR1'0%U nCipher DSE ESN:7A82-688A-9F921%0#UMicrosoft Time-Stamp Service0"0  *H 0  BQzٕ܇7(vMPv*GVi~MquۻCloq+|Δź~a-0&-v?F؉7[hd֋=vP'`]),<ϥZ5O8a?=6Vw;]7'c,HUl+~w$*p*$Ţ"KLYwTrC>dݓXI 63~Ly2Y)0 +0 *H  1  +70 +7 10  +70# *H  1VA*L)N$f0X +7 1J0H&$SQL Server 2008 R2http://www.microsoft.com/sql0  *H Ql{L 6uwzA3^]ģe^x[ARbUo寤PdTWs~Dј|nӄ]Yl&^Ll4+/ZF+^av|9%L\F=[Yvj`2˶wG2.c3=JG;sDlݛR"^J|VʚwHeH J͖|swìL)Hdus@E.嫊$Z⡂0 *H  1 000y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Timestamping PCA a- 0+]0 *H  1  *H 0 *H  1 100403181021Z0# *H  12M uΰ0  *H PN{>8DTW?nݖn3 DRۊ"|4P/Fdq}/&װsCKM|P[=p~#y̹r`R^ܚ0ҏ'"E]׏5~Lr 33w;쁤6;h ` YH==^{!9pH}r<:"YdNe>'{hrN;DŽs^L) @:u?HҨs