Donat Was Here

DonatShell
Server IP : 180.180.241.3 / Your IP : 216.73.216.252
Web Server : Microsoft-IIS/7.5
System : Windows NT NETWORK-NHRC 6.1 build 7601 (Windows Server 2008 R2 Standard Edition Service Pack 1) i586
User : IUSR ( 0)
PHP Version : 5.3.28
Disable Function : NONE
MySQL : ON | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF
Directory : /Program Files (x86)/Microsoft SQL Server/110/Tools/Binn/Resources/1033/
Upload File :
[ HOME SHELL ]
Current File : /Program Files (x86)/Microsoft SQL Server/110/Tools/Binn/Resources/1033/DTAENGINE.rll
MZР  ╕@░║┤	═!╕L═!This program cannot be run in DOS mode.

$Uu]╪3Л3Л3ЛэcДЛ3ЛэcЙЛ3ЛRich3ЛPELCу5Oр!

└E]╨Р@─╛┬X<.rsrc─╛└@@8А8АNАPАhА А8А╢PА╡hАААША░А╚АрА°АА(А	@А
XАpАИА
аА╕А╨АшААА0АHА`АxАРА	и	╕	╚	╪	ш	°			(	8	H	X	h	x	И	Ш	и	╕	╚	╪	ш	°			(dьбфP╖КфTAtф╚HDфPфV>ф\[шфDaFфМg0ф╝nЬфXu0	фИ~фФЖф░Н°фиП░фXТ(фАЩоф0афDм0фt│╓фL╡Ёф<╜0	фl╞Dф░╚╕фh═Zф
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 -----------------
'Insufficient arguments for -%s option.
&Argument for -%s option not provided.
jUsage: DTAEngine  -D database-name [,...n] {-i workload-file | -t workload-table-name} -o script-filename
Б[-S instance\server]  [-R instance\server]  {-U username
[-P password] | -E}  [-f tuning-feature-set]  [-a analysis-report-list]
r[-A time-bound-secs]  [-K keep-existing-indexes]  [-B storage-bound]  [-e error-table-name]  [-C max-key-columns]
l[-T table-list-file]  [-m minimum-improvement]  [-F]  [-N]  [-X xml_input_file]  [-O xml_output_file]  [-v]
(Incorrect command line argument near %s
Unknown option %s specified.
$Specify a number for stroage bound.
'Value for storage bound should be > 0.
/Specify a number for maximum columns in index.
?Value for maximum columns in indxe should be between 1 and 16.
-Specify a number for the feature set option.
╒Value for the feature set option should be between 0 and 3.
To combine feature sets append the option values delimited by space character within quotes .
Example -f "2 3" implies keep indexed views and partitions  Specify a number for -K option.
&Value for -K option should be 0 or 1.
 Specify a number for -M option.
/Value for -M option should be between 0 and 2.
 Specify a number for -m option.
5Specify a number for number of queries to tbe tuned.
@Value for number of queries that should be tuned should be > 0.
.Error: Database(s) to be tuned not specified.
/Error: Workload (File or Table) not specified.
'Error: Specify either -o or -O option.
AError: Incorrect format for table list in file %s near line:
 %s
@Invalid file name %s specified in the  table list  file option.
4Specified table [%s].[%s] not found in database %s.
ZUnable to create output script file %s.
 Use -F option to overwrite existing script file.
FLogin failed on tuned server. Check user name (-U) and password (-P).
*Login failed for user %s on tuned server.
#This tuning mode is not supported.
4This tuning mode is not supported in SQL Server 7.0
PTuning indexed views requires Enterprise Edition of Microsoft SQL Server 2000 .
ATuning indexed views is not supported in Microsoft SQL Server 7.0!Error writing to script file %s.
%Error closing output script file %s.
NoneError: setting tables to tune.
 Error: Unable to execute query.
Error: Exception encountered
PError: The events in the workload were ignored. Check if the workload is empty.
3Error: The events in the workload were unparseable
 Error: Invalid input specified.
WError: Table that was referenced during tuning/evaluate doesn't exist in the database.
Error: Insufficient memory.
$Error: Connection to server broken.
5Error: Server returned error when optimizing a query
!Error: DTAEngine internal error.
+/* Created by: Database Tuning Advisor */

/* Date: %s */

/* Time: %s */

/* Server: %s */

/* Database(s) Tuned: [%s] /* Workload file: %s */

/* Workload table: %s */

*/* Time taken for analysis = %lf secs */



USE [%s]

go

лSET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

go

eBEGIN TRANSACTION

DROP INDEX %s.%s

IF (@@error <> 0) ROLLBACK TRANSACTION

COMMIT TRANSACTION

go





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, /

/* Statistics to support recommendations */

CREATE STATISTICS %s ON %s ( %s)

M/* Minimum improvement specified = %7.2lf, Expected improvement = %5.2lf */

G/* No minimum improvement specified, Expected improvement = %5.2lf */

p/* Keep all existing indexes: %s, Feature Set = %s, Number of queries to tune = %d,  Storage bound = %d MBs */

TRUEFALSEUnsupported Server Version8/* Error: Storage Bound is less than Minimum Required */Thorough analysisIndexes and Indexed ViewsIndexes onlyIndexed views onlyb/* Storage for current configuration = %d MBs, Storage for recommended configuration = %d MBs */

Error: Password too long.
 Error: -U option not specified.
%Error: must specify -U or -E option.
)/* No tunable queries in the workload. */A/* Expected improvement does not meet the specified -m option. */K/* Recommendation has reduced storage compared to current configuration. */n/* Expected improvement is negative since user specified reduced storage compared to current configuration. */a/* Current configuration is better or recommendation could not meet the specified constraints. */Invalid argument :Error: Incorrect format specified for -t option argument.
;Error: Owner name, if specified in -t option, must be dbo.
PA1Error: Unable to open input file specified : %s.
GError: Workload in the input table specified : %s could not be opened.
ъThe minimum storage space required for the existing physical design structures (PDS) you have selected to keep is larger than the storage space provided. Choose fewer PDS to keep, or set the storage space to be larger than %1.0lf MBs.KWarning:This option is deprecated.Instead use -A to specify the timebound.
 Specify a number for -k option.
)Error:Failed to open input workload file.-Error: Use either -c or -X option, not both.
Unable to parse input file %s.
Partitions are not supported .
'Error on line %d, position %d in "%s".
Workload2Error: Server name exceeds maximum allowed length
8Error: Test Server name exceeds maximum allowed length 
1Error: File name exceeds maximum allowed length 
*Specify a valid number > 0 for -A option.
3Error: Database name exceeds maximum allowed length0Error: Invalid number of arguments for -f option.Error: Exceeds maximum allowed password length(Error: Exceeds maximum allowed user name0Error: Invalid number of arguments for -a optionaTuning online indexes is not supported in Microsoft SQL Server 7.0 or Microsoft SQL Server 2000.
ZTuning online Indexes requires Enterprise Edition of Microsoft SQL Server 2005 and later.
OWarning:Error message table already exists. Error messages will not be written.QWarning:Could not create error message table. Error messages will not be written.-Arguments passed in are incorrect.Check usage(Error: Could not connect to database %s User aborted sessionSuccess'Error: Out of memory parsing  input XML>Error: Failed to parse input XML. Check schema for correctness%Error: Could not load input XML file.3Error: Did not find opening element (DTAXML) in XMLKError: Failed to parse input XML. Check DTAInput hierarchy for correctness.OError: Failed to parse input XML. Arguments specified in input XML was invalid.СError: Failed to parse input XML. Could not parse Server type specified.In USC make sure that -S option matches the server name specified via XML▒Error: Failed to parse input XML. Could not parse Database type specified.In USC make sure that the database names in the argument and that specified in Configuration type match?Error: Failed to parse input XML. Could not parse Workload type>Error: Failed to parse input XML. Could not parse Options type0Error: Invalid analysis report options specified2Error: Feature set options specified are incorrectSError : Indexed views are not supported when the feature set is online indexes only*Error: Invalid number of queries specified'Error: Time bound specified is invalid.)Error: Storage bound specified is inavlid'Error: Max columns specified is invalidGError: Failed to parse input XML. Could not parse Schema type specifiedMError: Failed to parse input XML. Could not parseConfiguration type specifiedEError: Failed to parse input XML. Could not parseTable type specifiedEError: Failed to parse input XML. Could not parse View type specifiedQError: Failed to parse input XML.  Could not parse Recommendation  type specifiedIError: Failed to parse input XML.  Could not parse Create  type specifiedFError: Failed to parse input XML.  Could not parse Drop type specifiedGError: Failed to parse input XML.  Could not parse Index type specifiedHError: Failed to parse input XML.  Could not parse Column type specifiedLError: Failed to parse input XML.  Could not parse Statistics type specifiedQError: Failed to parse input XML.  Could not parse PartitionScheme type specifiedTError: Failed to parse input XML.  Could not parse PartitionFunction  type specifiedVError: Cannot create shell database. Check tuning log table, if specified, for details%Error: Invalid Drop options specified(Error: Expected text in the node element Error: Failed to parse input XML%Error: Failed to generate output XML.5Error: Failed to create Output element in output XML.4Error: Failed to create Header element in output XML<Error: Failed to create Configuration element in output XML.6Error: Failed to create Summary element in output XML.5Error: Failed to create Detail element in output XML.7Error: Failed to create Database element in output XML.5Error: Failed to create Schema element in output XML.4Error: Failed to create Table element in output XML.3Error: Failed to create View element in output XML.4Error: Failed to create Index element in output XML.9Error: Failed to create Statistics element in output XML.=Error: Failed to create Recoomendation element in output XML.$Error: Server to tune not specified.sError: The tuning option specified is not valid. Make sure that the manageability modes are either Tune or EvaluateBError: This option is supported only in input recommendation mode.(Error: Invalid input file name specified)Error: Invalid output file name specified0Error: Could not load input XML file for parsing@Error: Can not get minimal database information in allotted timegError: An invalid configuration was specified as input. Check if the specifed configuration is correct.EError: Value for minimum improvement option should be less than 100.
DateTimeServerDatabase(s) to tune
Workload fileWorkload tableMaximum tuning timeTime taken for tuning(Minimum improvement percentage specified Estimated percentage improvement%Maximum space for recommendation (MB)Space used currently (MB)!Space used by recommendation (MB)6Error: The specified workload (file or table) is emptyDaysDayHoursHourMinutesMinuteeError: Logged in user does not have enough permissions to tune one or more of the databases specified0Error: Tuning single user database not supportedELogin failed on test server. Check user name (-U) and password (-P).
)Login failed for user %s on test server.
FError: Could not connect to selected default database on tuned server
EError: Could not connect to selected default database on test server
CError: Could not refresh statistics and table sizes on test server
1Error : Could not connect to the specified server$Error : SQL Express is not supportedThe minimum storage space required for the selected physical design structures exceeds the default storage space selected by Database Engine Tuning Advisor. Either keep fewer physical design structures, or increase the default storage space to be larger than at least %1.0lf MB.Number of events in workloadNumber of events tunedNumber of statements tuned!Events specified to be tuned (-n)*Percent SELECT statements in the tuned set*Percent INSERT statements in the tuned set*Percent DELETE statements in the tuned set*Percent UPDATE statements in the tuned set+Number of indexes recommended to be created4Number of indexes on views recommended to be created.Number of statistics recommended to be created7Number of partition functions recommended to be created5Number of partition schemes recommended to be created+Number of indexes recommended to be dropped4Number of indexes on views recommended to be droppedXServer ''%s'' specified in input configuration is invalid. Specify the right server name\Database ''%s'' specified in input configuration is invalid. Specify the right database nameXSchema ''%s'' specified in input configuration is invalid. Specify the right schema namePAVTable ''%s'' specified in input configuration is invalid. Specify the right table nameZNo columns have been specified for index in the configuration. Specify at least one columnoIndex ''%s'' specified in input configuration cannot be dropped because it is a constraint or primary XML indexiIndex ''%s'' specified in input configuration cannot be dropped because it does not exist in the databasesIndex ''%s'' specified in input configuration cannot be created because a duplicate index with the same name existsFTable ''%s'' specified in input configuration was not selected to tuneXColumn ''%s'' specified in input configuration is invalid. Specify the right column name>Failed to create index ''%s'' specified in input configurationEFailed to create indexed view ''%s'' specified in input configurationqView ''%s'' specified in input configuration cannot be created because a duplicate view with the same name existsIFailed to create partition scheme ''%s'' specified in input configurationЙPartition scheme ''%s'' specified in input configuration cannot be created because a duplicate partition scheme with the same name existsKFailed to create partition function ''%s'' specified in input configurationНPartition function ''%s'' specified in input configuration cannot be created because a duplicate partition function with the same name existsEFailed to create statitistics ''%s'' specified in input configuration}Statistics ''%s'' specified in input configuration cannot be created because a duplicate statistics with the same name existsPA]Index ''%s'' specified in input configuration references non existent partition scheme ''%s''bPartition scheme ''%s'' specified in input configuration references non existent file group ''%s''jPartition scheme ''%s'' specified in input configuration references non existent partition function ''%s''WIndex ''%s'' specified in input configuration references non existent file group ''%s''CConfiguration specified in input is invalid. Table ''%s'' is a heapbConfiguration specified in input is invalid. Table ''%s'' has a preexisting clustered index ''%s''Index ''%s'' specified in input is invalid for the selected partitioning strategy because it doesnt meet alignment requirementseView ''%s'' specified in input cannot be materialized because it does not have a base clustered index8View ''%s'' specified in input could not be materialized	ClusteredColumn NameCost DecreasedCost Increased
Cost Range
Database Name
Index Name	No ChangeNumber of referencesNumber of RowsNumber of StatementsTable/View NamePercent Improvement
Percent UsageSchema NameStatement IdStatement StringStatement TypeIndex Size (MB)
Table NameUniqueView DefinitionPAView Id	View NameEvent StringWeightCurrent Statement CostRecommended Statement CostЙFailed to simulate heap for Table ''%s'' specified in input configuration. Simulating heaps for tables requires SQL Server 2005 and laterNumber of statements (Current)"Number of statements (Recommended)FError: Failed to parse input XML.  Could not parse Heap type specified3Error: Failed to create Heap element in output XML.<Failed to create heap''%s'' specified in input configurationInlineQDrop Index ''%s'' is invalid with absolute mode specified in input configuration ЄThe specified workload (file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StmtStarting or SP:StmtCompleted for workload trace file or table╧The specified workload (file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting or RPC:Completed for workload trace file or tablesError: Test production feature is supported only if both test and production server versions are SQL 2005 and laterHeap}Error: The test and production server names specified refer to the same server. Make sure that the servers are not identical.	Unlimited┼Use one of the following methods to increase storage space: (1) If you are using the graphical user interface, enter the required value for Define max. space for recommendations (MB) in the Advanced Options of the Tuning Options tabbed page; (2) If you are using dta.exe, specify the maximum space value for the -B argument; (3) If you are using an XML input file, specify the maximum space value for the <StorageBoundInMB> element under <TuningOptions>вAll the events in the workload were ignored due to syntax errors.The most common reason for this error is that the database to connect has not been set correctly.KTuning indexed views requires SQL Server 2005 and later Enterprise Edition.3Tuning this edition of SQL Server is not permitted.
CategoryIDEvent	Statement	FrequencyReason)Percent MERGE statements in the tuned setVFiltered index ''%s'' specified in input configuration doesnt have a filter definition/Server could not parse filter definition ''%s''%Filtered indexes are not supported .
FilteredFilter DefinitionBFiltered indexes option cannot be selected for this tuning option./Workload could not be extracted from plan cache4Plan cache does not contain any valid events to tune╕4VS_VERSION_INFO╜я■n█<4<4?StringFileInfoЄ040904B02
PlatformNT INTEL X86║ILegalTrademarksMicrosoft SQL Server is a registered trademark of Microsoft Corporation. CommentsSQL&GoldenBitsTrueLCompanyNameMicrosoft CorporationfFileDescriptionDatabase Tuning Advisor Engine|.FileVersion2011.0110.2100.060 ((SQL11_RTM).120210-1846 )4
InternalNameDTAEnginen%LegalCopyrightMicrosoft Corp. All rights reserved.DOriginalFilenameDTAEngine.RLLJProductNameMicrosoft SQL Server>
ProductVersion11.0.2100.60DVarFileInfo$Translation	░<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel level="asInvoker" uiAccess="false"></requestedExecutionLevel>
      </requestedPrivileges>
    </security>
  </trustInfo>
</assembly>PAPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADX<0В<H	*ЖHЖў
аВ<90В<510	+0L
+В7а>0<0
+В70	авА0!0	+ТИ╨╚°И╪F·╗ИGщ
к<ЩаВЄ0Ва0ВИа
aїъj0
	*ЖHЖў
0y10	UUS10U
Washington10URedmond10U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA0
111101223917Z
130201224917Z0БГ10	UUS10U
Washington10URedmond10U
Microsoft Corporation1
0UMOPR10UMicrosoft Corporation0В"0
	*ЖHЖў
В0В
В├й╧|#1%Й▌uy╣╧УKaТ╢ХЄяк%ЭwєKZmIР╠р1╕ж7ЗtпЄма`Ф╕╟N%■З╪X╥ N-ё:e.Т[ж]цХuр4хP!Ь}9Ч╢ОD#fжe р6ыК·Эт╦`№*d╚ЗEё╩═(м║хWY&▄ь┼JlЪц)2?ЮГм%
Д╠▌o╢j╗-ўД:mf1ш6p ДТРў@vЮИ.G╓+ўПрe+╝_я№ўьh╖Eю└░╖╜xkоНфPё5тЮ╥сы[qВa! є╘╔▐╕EeI╛~╡7Жз═ьZ▀╕v №Q№·*Pк┤╨:А╛c╩▄БGгВ0В0U%0
+0Uжхв─qвВ├Пv═,
тЭJ(0U А0U#0АWEt]░Ў╚CрМT-П2з■HЦ0VUO0M0KаIаGЖEhttp://crl.microsoft.com/pki/crl/products/MicCodSigPCA_08-31-2010.crl0Z+N0L0J+0Ж>http://www.microsoft.com/pki/certs/MicCodSigPCA_08-31-2010.crt0
	*ЖHЖў
ВРў°yТi╚ПkК`ю|┴о>Б8RЇ║╧ЩМЁ-=ЇоЎaЁu8NY╞?C∙|╟SфСb╓иvuФЖY▓║┬l?├/~9├╧■T'XФ5aymQW░съcrКfыЯ9*35ЛГ▐
^1╝Яю5E
]╟Ч▀*рЭ:I√уHE2▀6m	nvUUм4┼y]n╒╠ЧЙєиh}ъi{U}М─M0╝bў{еьаТЁ*пWЫ)H═╢!еъя▄	їлb╓
ь5_kЩ╘▒ЁwR∙wщ?lШV└\6бN^ЇМzЧ  1ynr_0WЫ╦|вUїplxьбC оь░5Л0В║0Вва
aЦ0
	*ЖHЖў
0w10	UUS10U
Washington10URedmond10U
Microsoft Corporation1!0UMicrosoft Time-Stamp PCA0
110725204219Z
121025204219Z0Б│10	UUS10U
Washington10URedmond10U
Microsoft Corporation1
0UMOPR1'0%UnCipher DSE ESN:9E78-864B-039D1%0#UMicrosoft Time-Stamp Service0В"0
	*ЖHЖў
В0В
В╙╦;SвЯDгzлMVqSКwк7У~>¤лгSй ╤▒"┼РчunюшБ┼╪a╛аv;
┼аи№CSб╣ТШb8я╘3ў+={янEWЕХ Я╫c4ЬbFО╛ор┘Ю9ЎйU¤╧чwц5я\ш┐HУOCX8КЕlъ╔╨KzВn╦╙`ЙФи[?:╔? }m/X}╩К8╝├┬9h╪-X#э*Z МВеэ╤ЧEТЕ╠├7Г▀
Ш|┼╙╢рЩS╪xїх#!bЖZji¤p∙S4Бў%1ЖЗЛ└UЪ¤кL5¤[щХ╔лSШ)_,ес%d┼Є░'uгВ	0В0Uy#чб
╛U,h)bВg╓єс68Z0U#0А#4°┘RFp
э@√v√│+░├5│0TUM0K0IаGаEЖChttp://crl.microsoft.com/pki/crl/products/MicrosoftTimeStampPCA.crl0X+L0J0H+0Ж<http://www.microsoft.com/pki/certs/MicrosoftTimeStampPCA.crt0U%0
+0
	*ЖHЖў
ВG┬vБ[1▌o╙<ЩЄzl~ЧP,╕Dб╡МЇV╧╜░!┤dma╪юшыїТюCвD├··ян╢\ЧmK╡C√'axT|▒∙пШ
┤t─кш-P─}■}NбЩ╓'(╖о╪▀▒ПГ"О╬єy№am┐ы╠Д░▀г╗[╖ъTїbl'жcK·уб·╗▀ЕЦeiS░╢qШd&f╜┼ё\b╝СЇ=ЬKB
╧8&Z▄*KjЩ8╪▌sЩв╬МP$Юдыї?Кx╠TЩ]%fдA(пI─┤RФь▐Гd■M╘ Kв№)А█ю?л<э3И╩┐"`╨\;░
┤Шm<з:OЪ╤bКAvа0В0Вяа
ah40
	*ЖHЖў
0_10
	Т&ЙУЄ,dcom10
	Т&ЙУЄ,d	microsoft1-0+U$Microsoft Root Certificate Authority0
070403125309Z
210403130309Z0w10	UUS10U
Washington10URedmond10U
Microsoft Corporation1!0UMicrosoft Time-Stamp PCA0В"0
	*ЖHЖў
В0В
ВЯбl▒▀█HТ*|k.с╜ту┼ЩQ#Pн╬▌N$ю╤з╤Lнt0 ы╒TХIФBТоШ\0&┌kш{╜ьЙўШЁЙ╠╦3$ЗЭЄЇg,№{чИъчNгб┴#S╩Н·E╧	╨^п╨░Bв∙жlУg╫(▄FS░Ж╨х(F.'мЖOURф,√jРР0nЗєY0Э·~╓Ч│ш!Ч~°╥є╖SmR┤EРЯHJGf')fиЧф╙БJв∙ДзG	ВЯДэUx■ЪPЕ0Fэ╖▐#F╗─-TЯпxA1w╠Ы▀;ГУбa╡
▒№ўЫ▓╬"KT ∙├┬ гВл0Вз0U 0 0U#4°┘RFp
э@√v√│+░├5│0UЖ0	+В70БШU#БР0БНАмВ`@V'Чх%№*с
SХYфдбcдa0_10
	Т&ЙУЄ,dcom10
	Т&ЙУЄ,d	microsoft1-0+U$Microsoft Root Certificate AuthorityВyнбJаенLsXЇ.e0PUI0G0EаCаAЖ?http://crl.microsoft.com/pki/crl/products/microsoftrootcert.crl0T+H0F0D+0Ж8http://www.microsoft.com/pki/certs/MicrosoftRootCert.crt0U%0
+0
	*ЖHЖў
ВЧК├\D6▌щ┤нw█╬yQM▒.tq[mл╬╛{П╕.╘вМmb╕W╦N PЩ▌z@тW╗пXЪсЖм╗xЄЛ╨ь;ют╛П
╚НHтЁS▌OлТфчНj╒А┴цФЄ/ЕщС*$"p√Ў№фxЩ.
ўтp╝NЭОk
rХ╕б9Ьg-┼Qъb\?ШЛ ?т2∙╠1Jv1=+r╚ъp= Е
▀┬
aОЁ╫╕ыNЛ┼5+^г┐ы╝}B{╘Sr!ю0╩╗xe\[
╥┌Шї<╣fX│-/ч∙ЕЖ╠QVшЭpФlм9L╘Ўy┐кzb)явЫ)4wb╔=mВЁ╟,╗╧C│х∙ь}╡удиt5╕N┼q#&v<RМqZFC╝││░Mg╚ЯB Аy!АЮ0fшB^╚Ю"!╨Cщ+щ╗ЇH╠,╘╪2АL&*H$_ZъVяж▐ЩЭ╩:o╜Б'tюv!┐ЫВ┴'T╢▒j=Йбva┤nбж┐кGЁo¤К2l▓■▀Q╚М#╔f╜ЭЗd=-пYП╕ф!х╡░╩c┤xT╘A.PмФ░еxл│аЦu┘ТЗu"/2иnа[М%┐аяД╩!╓ыO╔ЪюIрўeoЙ}╚i╚цnъзЧ╬1) ┼[\╪Mб╪·/Ю?.Uk╔г¤0ВБ0Вiа
a'0
	*ЖHЖў
0_10
	Т&ЙУЄ,dcom10
	Т&ЙУЄ,d	microsoft1-0+U$Microsoft Root Certificate Authority0
060125232232Z
170125233232Z0y10	UUS10U
Washington10URedmond10U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA0В"0
	*ЖHЖў
В0В
ВЯН▀Е7М▒Vf6N╘
ы6#СплНXщ─╧┴Я:
$───^░ю┼·>Щ3kirЇ|_хЩ%D╫5КР▄(ОРя╒Рaн▐▄p-Г╔oпЕ,°▌╣╣0¤qxГ¤╚й╣Вg
z╕┴	┬╝ЧW?Ш╙i╫0РРРН0╪&╘Ю{И┘т	эЧIEыRБu{╕╡"╕.V╗╫╧йN<
Гви:┼н╢j╧"┤#l╒'ч╖~┴┤╒2нy%BЖтЭЕЇW┌Y/-&Т)┼│╫ЕгЮ`ZФц{Ь{^В4Km ╘ю'╨в▄╤Р╬}|Им4╟└lШДJ╔Dyh(▀"Г
╕К,Haт■гВ#0В0	+В70UWEt]░Ў╚CрМT-П2з■HЦ0U╞0U 0 0БШU#БР0БНАмВ`@V'Чх%№*с
SХYфдбcдa0_10
	Т&ЙУЄ,dcom10
	Т&ЙУЄ,d	microsoft1-0+U$Microsoft Root Certificate AuthorityВyнбJаенLsXЇ.e0PUI0G0EаCаAЖ?http://crl.microsoft.com/pki/crl/products/microsoftrootcert.crl0T+H0F0D+0Ж8http://www.microsoft.com/pki/certs/MicrosoftRootCert.crt0vU o0m0k	+В7/0^0\+0PNCopyright й 2006 Microsoft Corporation.0U%0
+0
	*ЖHЖў
В0╝░ дbазя9&a=▄м╞Щ╨Ж─.їГ2e^">Ы╓Ж°ш╢Я╩Л╓;uбС>L▌dtМIЄИМgl■fF╕:FBЧ╚С7╘$▒чм╙ъAГw╙╔O_гR┐Цд/>V[u╬S├╬Ti┤e╔$ес%╕╣Mх╣PO│ШХю"Yн"╦tE{Yр╪│ыL°`▌┬xЮ╤║Яkўєю~D@╥╜║ЫKnд╛Uфж8гHЇUzК ╤}■С█ФП/┬{kжdЕOгх,4)]┐ї╟8/─ ─V1Ўi╬Е°
Сь╨╓о║8ыnOLЬ)кв№а)
\\V#W┌Ц
╚&6┴D╦хсцoЙчПщШ╡s╙xуэг<tх^╝lЎ!ЙgьЧЭ╡▒¤пжщrе├4с/Я∙Їj9т-В[№╨Ьо|Э∙ц╡St║9J9ы}B│J;7=┬J{'ШчтNЫJT8j#7╛j╖HюъB
I$СО╖.ЧoфчE╗ПM¤`┘╫Y┬╪А╓`4{╖бУ ■Б╕ке\	щК
╛(M~╫шэex╤1_╣f█▀Bч╠Н▀  гEe┤│2Н.СтM┤й╔·╝k▓ЯъЯ▐д╔
ЦЇНў╪ШиЁЕYд ж,ЄRэiхdргл▐╩|aC╕eї6гU+g,M0L1В%▌0В%┘0БЗ0y10	UUS10U
Washington10URedmond10U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA
aїъj0	+аБ▓0	*ЖHЖў
	1
+В70
+В710
+В70#	*ЖHЖў
	1uM╘Ьs╪4.╖Y╘А■╒Ж*9└щ0R
+В71D0Bа АSQL Server 2012бАhttp://www.microsoft.com/sql0
	*ЖHЖў
Вy9ж·еsФПСФкЫеg█5ПНИгцг┌рN┴чы╒Q┌O(=E`╤
8╚НP▌Р-╚%┘FЮpYOWУ<▌р8б▀╣Jн▐0PЄЭmЩў▓odЧ┘И┴cЁЇ∙Уў1░$Чмyx'wРh>DI:f╠иб╟aьXx▌к/п1гvD╔HX>┤┐KУЯ5┘ЎпK╜MuL7U(BС_Й$ЯжЧбЭ<╚╞Nъюг0UыЛ?├[└еТўb$[НВ╩LЦLАц█ъч░*
4Щ,ИёбЁХ]ч`чзMПЧykР╝&[yXJH&(■|■х2║q╤╠Bї#И Л&]4а@Ю#щЯъ	еWбВ#u0В	*ЖHЖў
	1В
0В0БЕ0w10	UUS10U
Washington10URedmond10U
Microsoft Corporation1!0UMicrosoft Time-Stamp PCA
aЦ0+а]0	*ЖHЖў
	1	*ЖHЖў
0	*ЖHЖў
	1
120211165939Z0#	*ЖHЖў
	1вХДчўз╧Б▒зl╚ўїъ1
0
	*ЖHЖў
ВHс!ЬОOsOм╧їNфяиЩO_y{уоя╘7ЧTгЭ╟gЪQ├ё╝JщьЄБв█кS╡_мшu ╟н├ЮжўДG ЙкYХW%хь╝АР┐6╞╓s█mAэ┌╜щюЭ#!яЦQ█iB(хmV╣|Nmu╗^√╔Ч√2^ЖG,
Ў$iН█0°@Mжпсf╘тУУC Ыч╗Е\ш %vh'ч1|Ш╟▄nЯ ЬXЇЎЬ╨щc*u╟9йё*м═■╞?рлe$n└BФЛ;FЄ $Я╖в°КTГW╓▀сT2─╨щ
я(Yч▐╔F╓G╤ШVgоx0В!T
+В71В!D0В!@	*ЖHЖў
аВ!10В!-10
	`ЖHe0\
+В7аN0L0
+В70	авА010
	`ЖHe ;q░c╤▌ф¤м
╓└5╛жQД┼ЄКз╕ю@ЬHДTMИаВ0Вг0ВЛа
aIU0
	*ЖHЖў
0~10	UUS10U
Washington10URedmond10U
Microsoft Corporation1(0&UMicrosoft Code Signing PCA 20100
111010204524Z
130110205524Z0БГ10	UUS10U
Washington10URedmond10U
Microsoft Corporation1
0UMOPR10UMicrosoft Corporation0В"0
	*ЖHЖў
В0В
В╧д01БQЖхU╠4`╢ЦE)_+яд╥╓+є╬░хrCд╠эЫ\;фщ*Y¤Ьqrx ,Ы8БЛ[дbє¤ф╚°}Н▒╘пa║ышDЯ9к╬▓У▄T¤Kр&DOWЁ°я#JjцЇщ╪й6оwн╝Ёїц┌4ЁЄ╩O{░_╘■u╕У9}ПaЭRфЮ╞▓Ж╚є~:╓x╚Ф=╟GИ┐'╞rГП╧ ЮpWф0╕█'c╬a(ЦЦЗЧ╨╗bd@Л"╛+^╣гwrч╙N|U~└└\жй~@ю6▌tdпW$│XЎРkЮUgZ╕Ръ┌╟.у┤iк;╣█%с╓√гВ0В0U%0
+0Ui°▓ў1·╣bГqX√ит┬∙╪xa0U#0Ац№_{╗"XфrN╡Ї!t#2цям0VUO0M0KаIаGЖEhttp://crl.microsoft.com/pki/crl/products/MicCodSigPCA_2010-07-06.crl0Z+N0L0J+0Ж>http://www.microsoft.com/pki/certs/MicCodSigPCA_2010-07-06.crt0U 00
	*ЖHЖў
ВxК[рZm#}u	ЫРА·╩jЭ'кE╙ТЕя╝NЖ╝╝─зe╣▌Е╪J╕жХЁЗSxA
И0д▒ Tб'│Y┤h┼8░.?┐i├L■ГЭШLx
Ц╗P┤:MТЄ?+ХУ╦╚┘R=Mk&Z¤\j@Kоўс╘1(╚|ъTйб╫p?vАY╨┌ў■ЎЯ0Кn└║m¤.$X9MRljф┘▀А[ASН{┴T1
╦┬шдDЕ╟A∙╗~RВДi?c2уK▀W8оИX┬Ч╘ю╤@cя-mk░|+С;№шB║К+┼9╟@Н╥"Qp╕AаЎ
`№В&░╟йc▀╪Е0Вp0ВXа
aRL0
	*ЖHЖў
0БИ10	UUS10U
Washington10URedmond10U
Microsoft Corporation1200U)Microsoft Root Certificate Authority 20100
100706204017Z
250706205017Z0~10	UUS10U
Washington10URedmond10U
Microsoft Corporation1(0&UMicrosoft Code Signing PCA 20100В"0
	*ЖHЖў
В0В
ВщdPyg╡─у¤	LЮФмўVhъD╪╧┼XOйеv|mE║╙9Т┤д∙∙eВф╥П¤DЬшeУ╬,UД┐}у.+иA+╖вKnILk▐╤╥┬ЙqФ═╡K┤п╪╠И╓kФ:У╬&?ьц■4ШW╒]IЎ▓*.╒Е╗Y?°Р┤+Гt╩+│;FуЁFI┴fT╔╜─UbWr°g╣% 4▐]жеХ^л(А═╒▓Юх╡c╙▓╚┴╚К&
Yь эА5Lж╛R[їж┌рЛHw╓ЕG╒╣╞шкюЛj-\`╞┤*[Ь#_Eуo7╦3АjЙMгjfcxУ╒0╧ХгВу0В▀0	+В70Uц№_{╗"XфrN╡Ї!t#2цям0	+В7
SubCA0UЖ0U 0 0U#0А╒ЎV╦Пшв\bh╤=ФР[╫╬Ъ─0VUO0M0KаIаGЖEhttp://crl.microsoft.com/pki/crl/products/MicRooCerAut_2010-06-23.crl0Z+N0L0J+0Ж>http://www.microsoft.com/pki/certs/MicRooCerAut_2010-06-23.crt0БЭU БХ0БТ0БП	+В7.0ББ0=+1http://www.microsoft.com/PKI/docs/CPS/default.htm0@+042 Legal_Policy_Statement. 0
	*ЖHЖў
ВtяWO){─Еx╕P╙"№	ЭмВЧ°4 *,ЧХхф┐╧┐У╚у4й█Б╕▄╛╥5oпхyХwх╘ёы╪═Naв┬Z#ЁМиbQEgу?<У°0Е╚9@ж╫│!)хебiМ"У╠tШчбGCЄSм└0i■╥% moa╙▀╒┘r,iЖv=Q█ж9H╔7am▌S╦з╓a┬┐тГлрkЫХ╓}(Q░ЙJQдЪl╚╖Jiй╫▄┴~╤Ipк╢н╗rGc·ж╓вжЖьиDЫc╢▓iЙ╟FЖz?ш┼!╒{∙#-┼A╦┐L╚я▒Ь№"KIКnужv[╤SyСЕЭ╒╥█=s5є<оT▓RGj└кХ╥О┌Щg^2М√7Е╤▄uЕЬЗ╞ZWЕ┬┐▌
ПМЫ-ы┤ю╧'╙╡^i·дз$gs╧MO╢▐VЧzўщRMЇwOЕ╞╪ёОэB	╤
vу#Vx"&6╛╩▒МnкфЕ┌G3bПд╔С3_q@пШe╔"шB!%К-`┘7ЙAЙ*╫a<Фh`Rя╓GЩаА@юБw>ЬрSP8ХЫf3'9xЗ6╬N├_▓ї=GS╢рх█a=*╫Т,╬7Z>@B1д┬VЬ┐$]QЭjy╥╙┌┴ФО{%qDлjц╘╞▀#ЪЦu┼1ВШ0ВФ0БМ0~10	UUS10U
Washington10URedmond10U
Microsoft Corporation1(0&UMicrosoft Code Signing PCA 2010
aIU0
	`ЖHeаБ╤0
*ЖHЖў
	10	*ЖHЖў
	1
+В70
+В710
+В70/	*ЖHЖў
	1" cuw"├m|& hx┌9Лф vU│+h*ш╩А╙0R
+В71D0Bа АSQL Server 2012бАhttp://www.microsoft.com/sql0
	*ЖHЖў
В┴шЙ╡F2зgщDTр	yX%<╔№мG▌_'─@╖╤╒3╜ЬД}ЯЁЭсfjв╒МN·╝№P5 ;npQ/ф╧Oс_╚ФMЕpЄ╓Wz@а48╓фПЭйЬюu,{vZ¤╥Xй┐Ox9ё ╒ьB>
╣Ї╪Ш}ЗўУпiв▓-I
eХ╘¤$ZИ│5нВыЕЪ1qЦ	*ь\▌нFн<Р@9х%'r▒йЄ╙Лf╬6зУEk▀╬8є(з╓ИmЗОqU╩мЎБ╫╔°ДЭчуfwСШдЙ┌8PцвAGNX[┘ЇХx9nлKДЗ╓ЄР¤nnF█└f}ЯМ╕зqзu∙бВ0В
+В71ВЇ0ВЁ	*ЖHЖў
аВс0В▌10	+0В**ЖHЖў
	аВВ0В
+ДY
0!0	+┴Ї\bз▒їХъя╗ ыZа7+2O1&",V20120211165941.124Z0АЇаБ╣дБ╢0Б│10	UUS10U
Washington10URedmond10U
Microsoft Corporation1
0UMOPR1'0%UnCipher DSE ESN:936C-7AEE-74AE1%0#UMicrosoft Time-Stamp ServiceаВ─0Вq0ВYа
a	Б*0
	*ЖHЖў
0БИ10	UUS10U
Washington10URedmond10U
Microsoft Corporation1200U)Microsoft Root Certificate Authority 20100
100701213655Z
250701214655Z0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 20100В"0
	*ЖHЖў
В0В
Вй
╝wК: ь№Чї·iФktT╒е
ВЕ√э|hK,_┼├хa┬v╖>f+[ЁS'1AХ╬	О|a0Y▒0Dё`ИДTC╫M╕8│B▌Ум╓s0W&ВгE
╨ъїGБ═┐$`2X`FЄXGЖ2ДtagС_БT▒╧УLТ┴─ж]╤an(╞∙ЖА╗▀a№F┴'$gr!КпKdЙPb▒]¤w=ЁWuм╜КBM@Q╤Ь>gїf└ЦD~я╨K¤nхЪ╩▒иЄz*
1Ё┌NС╢И5шx░щЩ═<ч/D║зЇ▄d╜д┴ 	Уx═№╝└╔D]^ЬO"MгВц0Вт0	+В70U╒c:\К1РєC{|F┼3hZЕmU0	+В7
SubCA0UЖ0U 0 0U#0А╒ЎV╦Пшв\bh╤=ФР[╫╬Ъ─0VUO0M0KаIаGЖEhttp://crl.microsoft.com/pki/crl/products/MicRooCerAut_2010-06-23.crl0Z+N0L0J+0Ж>http://www.microsoft.com/pki/certs/MicRooCerAut_2010-06-23.crt0БаU  БХ0БТ0БП	+В7.0ББ0=+1http://www.microsoft.com/PKI/docs/CPS/default.htm0@+042 Legal_Policy_Statement. 0
	*ЖHЖў
ВцИQ
т╞рШ?Бq=Эгб!o│ыж╠ї1╛╧тй■·Wm0│┬┼f╔j▀їчЁx╜╟иЮ%у∙╝эkTW+QВD√╣SМ╠Ї`Кv╠@@AЫ▄\ \∙^5Ш$VKtяB╚п┐╞Є7}Z?ЄЩyJСRп8ї/yБe┘й╡kф╟╬Ў╩zoK0D$"<╧эеЦПY)╝╢¤сpЯ2J'¤Uп/■╢хО3╗b_Ъ█W@щё╬ЩfРМ jb▌┼JС&т9ьJqcЭ{!m├Ьгв<·}ЦjРxжm╥сЬ∙№8╪ФЇ╞е
ЦЖд╜ЮоBГ╕╡АЫ"8 ╡%хdьўЇ┐~cY%z.9WvвqкКЙ║aз╦Ъ╪GЪА┼╨═з╨я}ГЁс;q	▀]tШ"a┌░Po╜ёс▀ч1дУ:ўeGxш°иHлў▐r~akowйБ╦з	м9╗ь╞╦╪В┤r═Ї╕ЕА√Й*T9▓[┌╚
UЩzЗs;цШ-ъНр3.)ї└/T'!ў╚мN┌(╕▒й█Ц▓зBв╔╧AMрЖ∙*Ъгf0╙╗t2K▀c{їЩК/╟!пY╡о▄D<ЧPq╫б╥┼Uуi▐W┴╤▐0└¤╠цM√
┐]OщЭ8/╝╧X.я
аP5┌я	'╒│~5║┌6█╙_П▐tИI0В╤0В╣а
aNк	0
	*ЖHЖў
0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 20100
111215031031Z
130315032031Z0Б│10	UUS10U
Washington10URedmond10U
Microsoft Corporation1
0UMOPR1'0%UnCipher DSE ESN:936C-7AEE-74AE1%0#UMicrosoft Time-Stamp Service0В"0
	*ЖHЖў
В0В
ВЧ╪╛c0HSм<!╞%О5xд°ЩЬ~Bт ╚Ї Б?+#^╖т╦АЭЕ╧├ДRв■ЎШU9U?│╖▒°T<>з╔ї╟ШP5G▓j+с╝ГdжPeўеMs╪╛╠HЄuНэ┤84еYRфЗЙ
ю;G╨Ъ+№	мэёкTHВЬ│ку9ЫЫHkd~ш"М·AHъ▀6ў№/S4д╠J&)IУaБ°Г╩▒╧·X&╥═_Й"ёц
┴ёСФw┌gщж╞uбaў╞уS╜+Х▀╞╢╣wpм/┌%█BЦ:∙∙Ў╚┬2шAОъOTlИСEэ{А@КХPх▄FПW╞АbйгВ0В0UЛ?ЪЙ9Неv5щЗэ#[LвбWр70U#0А╒c:\К1РєC{|F┼3hZЕmU0VUO0M0KаIаGЖEhttp://crl.microsoft.com/pki/crl/products/MicTimStaPCA_2010-07-01.crl0Z+N0L0J+0Ж>http://www.microsoft.com/pki/certs/MicTimStaPCA_2010-07-01.crt0U 00U%0
+0
	*ЖHЖў
ВЩO)жтэKТSQВ\\Е├▀ sЬГяХ*8hыOш╣9╣KC54эU╕Л╫d╕Д" 
ў╜`З╬Г√;@|дD╚ц[┴ОiЕ│уfaЇ?мann!Ь╥▓х╜g╝ЎR*rл╕u1Й╕>QхI╠Р╘И!▒┼kb╥╪и"єpQЁ├ШЯ9мX=pDуШ.═Ў╬mКLЄАКsT&й c═YNOжg~u╨:TeРPБяА┬b╔В%=e№?Пc▓ЫБbд╕╙Й_╔йVK@▓ДЁчTA%ёXЧш,O╛угП)П}WПЮr√xюЛЪnjЪ] УPLбВv0В^0БубБ╣дБ╢0Б│10	UUS10U
Washington10URedmond10U
Microsoft Corporation1
0UMOPR1'0%UnCipher DSE ESN:936C-7AEE-74AE1%0#UMicrosoft Time-Stamp Serviceв%
0	+з^$ъ3¤+Оb сxc
╓БsьаБ┬0Б┐дБ╝0Б╣10	UUS10U
Washington10URedmond10U
Microsoft Corporation1
0UMOPR1'0%UnCipher NTS ESN:B027-C6F8-1D881+0)U"Microsoft Time Source Master Clock0
	*ЖHЖў
╥р╧г0"20120211112939Z20120212112939Z0t0:
+ДY
1,0*0
╥р╧г0╕0Р0
╥т!#06
+ДY
1(0&0
+ДY
а
0б б
0б 0
	*ЖHЖў
В*√Aр▄Ътк}kдpд┘EK)piЗn_fВ\PЪ0Шю╠Їч▄a╙:>╘u╤г{7щц7yМX&2pR"Л╞VА>╥zi]╫s^Ш╖ОFАxбB>КcХ╖М3nхКў*▒╣dч∙QЪ┌ў9╬дMpEнВh	ЬDЫh-√ak8I╬щ65╬вфk╜жЧЬМvЇgЁД};юЖ╕█╟╢█dЙu;OЦ№J¤-(N!обvчmЧ
ЙИc╪┴УPU\dяе╥Ж6Fл!n¤kBDй$єя>r;,Н!▓бD]їфт_H╗О∙7─▒└Р▓!^"╡zФз#|RS5│е]▄1В╙0В╧0БК0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 2010
aNк	0	+аВ0	*ЖHЖў
	1
*ЖHЖў
	0#	*ЖHЖў
	1J
6	S╣вBX0ЦH╘√A■тz0Б┘*ЖHЖў
	1Б╔0Б╞0Б├0Биз^$ъ3¤+Оb сxc
╓Бsь0БП0БАд~0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 2010
aNк	0┴┼Z╟Хй╛Г.н
¤Ч╘д╬@ё0
	*ЖHЖў
В)Ь▄╓╩фP°│╤м<Н+═╥АI├аяwё~л!╚О(и╦▓xРКдgлчу╘PВБ2Й╒FК,ЮD├█┐╥*zX'b.-wEс;ўЄ╫DУ№МEьЭчФЄ▌gж╝z№╡п*╫ф│∙Nы?╣5Ў.┼Lpн'(╡╗{kTпp59┬-▒▌│%шЮ│yу4¤l└|┐gO╤·╪Ї
Е'uш_ўў∙Я9`y╙Ю╛х#и┌╧Р─├кvфTC  ╨ЄЩЭ^2О*L┌
▌ Э7'l∙╬╒5оЦ╪Юо&ИїNЕ SА╝sЪ│╕Ї╡k▒╝OЩВЙл╦PЇ├З╤Р
B╨`5'Йо
Anon7 - 2022
AnonSec Team