ÿþUSE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_add_session]    Script Date: 05/23/2008 14:48:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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,[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_check_permission]    Script Date: 05/23/2008 14:48:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_check_permission] 

				@SessionID int

as 

begin

	declare @retcode  int

	declare @dbname nvarchar(128)

	declare @sql nvarchar(256)

	declare @dbid int



	set nocount on

	set @retcode = 1



	-- Check if SA

	if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)

	begin

		return(0) 

	end



	-- if not yukon return

	if (patindex('%9.00.%',@@version) = 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

/****** Object:  StoredProcedure [dbo].[sp_DTA_column_access_helper_relational]    Script Date: 05/23/2008 14:48:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_column_access_helper_relational]

			@SessionID		int

			as

			begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,C1.ColumnName as 'Column Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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 end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_column_access_helper_xml]    Script Date: 05/23/2008 14:48:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_database_access_helper_relational]    Script Date: 05/23/2008 14:48:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_database_access_helper_relational]

			@SessionID		int

			as

			begin select D1.DatabaseName as 'Database Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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  end

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_database_access_helper_xml]    Script Date: 05/23/2008 14:48:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_delete_session]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_end_xmlprefix]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_end_xmlprefix]

as

begin

	declare @endTags nvarchar(128)

	set @endTags = N'</AnalysisReport></DTAOutput></DTAXML>'

	select @endTags

end

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_event_weight_helper_relational]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_event_weight_helper_relational]

			@SessionID		int

			as

			begin	select 'Event String'= EventString, 'Weight' = CAST(EventWeight as decimal(38,2)) 	from [msdb].[dbo].[DTA_reports_query]

					where SessionID=@SessionID and EventWeight>0

					order by EventWeight desc  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_event_weight_helper_xml]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_columntableids]    Script Date: 05/23/2008 14:48:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_databasetableids]    Script Date: 05/23/2008 14:48:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_indexableids]    Script Date: 05/23/2008 14:48:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_interactivestatus]    Script Date: 05/23/2008 14:48:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_pftableids]    Script Date: 05/23/2008 14:48:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_pstableids]    Script Date: 05/23/2008 14:48:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_session_report]    Script Date: 05/23/2008 14:48:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_session_tuning_results]    Script Date: 05/23/2008 14:48:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_tableids]    Script Date: 05/23/2008 14:48:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_tuninglog]    Script Date: 05/23/2008 14:48:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_tuningoptions]    Script Date: 05/23/2008 14:48:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_help_session]    Script Date: 05/23/2008 14:48:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_current_detail_helper_xml]    Script Date: 05/23/2008 14:48:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

		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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

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

		CAST(I.Storage as decimal(38,2)) as [Index!5!IndexSizeInMB],

		I.NumRows as [Index!5!NumberOfRows]

		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

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_detail_current_helper_relational]    Script Date: 05/23/2008 14:48:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_index_detail_current_helper_relational]

						@SessionID		int

						as

						begin	select 'Database Name' = D.DatabaseName, 'Schema Name' = T.SchemaName, 'Table/View Name' = T.TableName, 'Index Name' = I.IndexName, 'Clustered' =	CASE

						WHEN I.IsClustered = 1 THEN 'Yes'	

						WHEN I.IsClustered = 0 THEN 'No'

						end, 'Unique' =	CASE

						WHEN I.IsUnique = 1 THEN 'Yes'		

						WHEN I.IsUnique = 0 THEN 'No'

						end	, 'Heap' =	CASE

						WHEN I.IsHeap = 1 THEN 'Yes'		

						WHEN I.IsHeap = 0 THEN 'No'

						end	, 'Index Size (MB)'= CAST(I.Storage as decimal(38,2)) , 'Number of Rows'= NumRows 	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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_detail_recommended_helper_relational]    Script Date: 05/23/2008 14:48:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_index_detail_recommended_helper_relational]

						@SessionID		int

						as

						begin	select 'Database Name' = D.DatabaseName, 'Schema Name' = T.SchemaName, 'Table/View Name' = T.TableName, 'Index Name' = I.IndexName, 'Clustered' =	CASE

						WHEN I.IsClustered = 1 THEN 'Yes'	

						WHEN I.IsClustered = 0 THEN 'No'

						end, 'Unique' =	CASE

						WHEN I.IsUnique = 1 THEN 'Yes'		

						WHEN I.IsUnique = 0 THEN 'No'

						end	, 'Heap' =	CASE

						WHEN I.IsHeap = 1 THEN 'Yes'		

						WHEN I.IsHeap = 0 THEN 'No'

						end	, 'Index Size (MB)'= CAST(I.RecommendedStorage as decimal(38,2)) , 'Number of Rows'= NumRows 	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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_recommended_detail_helper_xml]    Script Date: 05/23/2008 14:48:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

		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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

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

		CAST(I.RecommendedStorage as decimal(38,2)) as [Index!5!IndexSizeInMB],

		I.NumRows as [Index!5!NumberOfRows]

		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

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_usage_helper_relational]    Script Date: 05/23/2008 14:48:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_index_usage_helper_relational]

	@SessionID		int,

	@IsRecommended	int

	as begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,I1.IndexName as 'Index Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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 end

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml]    Script Date: 05/23/2008 14:48:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_DTA_tuninglog]    Script Date: 05/23/2008 14:48:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_progressinformation]    Script Date: 05/23/2008 14:48:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_column]    Script Date: 05/23/2008 14:48:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_database]    Script Date: 05/23/2008 14:48:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_index]    Script Date: 05/23/2008 14:48:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_insert_reports_index]

	@SessionID			int,

	@TableID			int,

	@IndexName			sysname,

	@IsClustered		bit,

	@IsUnique			bit,

	@IsHeap				bit,

	@IsExisting			bit,

	@Storage			int,

	@NumRows			int,

	@IsRecommended		bit,

	@RecommendedStorage int,

	@PartitionSchemeID	int,

	@SessionUniquefier  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_index]([TableID], [IndexName], [IsClustered], [IsUnique], [IsHeap],[IsExisting], [Storage], [NumRows], [IsRecommended], [RecommendedStorage], [PartitionSchemeID],[SessionUniquefier])	

	values(@TableID,@IndexName,@IsClustered,@IsUnique,@IsHeap,@IsExisting,@Storage,@NumRows,@IsRecommended,@RecommendedStorage,@PartitionSchemeID,@SessionUniquefier)

end	



GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_indexcolumn]    Script Date: 05/23/2008 14:48:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_partitionfunction]    Script Date: 05/23/2008 14:48:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_partitionscheme]    Script Date: 05/23/2008 14:48:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_query]    Script Date: 05/23/2008 14:48:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_querycolumn]    Script Date: 05/23/2008 14:48:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_querydatabase]    Script Date: 05/23/2008 14:48:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_queryindex]    Script Date: 05/23/2008 14:48:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_querytable]    Script Date: 05/23/2008 14:48:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_table]    Script Date: 05/23/2008 14:48:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_tableview]    Script Date: 05/23/2008 14:48:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_cost_helper_relational]    Script Date: 05/23/2008 14:48:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_query_cost_helper_relational]

			@SessionID		int

			as

			begin 	select 'Statement Id' = QueryID, 'Statement String' = StatementString, 'Percent Improvement' = 	

					CASE

						WHEN CurrentCost = 0 THEN 0.00

						WHEN CurrentCost <> 0 THEN

						CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal (20,2))

					end , 'Statement Type' = CASE 

							WHEN StatementType = 0 THEN 'Select'

							WHEN StatementType = 1 THEN 'Update'

							WHEN StatementType = 2 THEN 'Insert'

							WHEN StatementType = 3 THEN 'Delete'

							end 	from [msdb].[dbo].[DTA_reports_query]

					where SessionID=@SessionID

					order by 'Percent Improvement' desc  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_cost_helper_xml]    Script Date: 05/23/2008 14:48:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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'

			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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_costrange_helper_relational]    Script Date: 05/23/2008 14:48:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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  select 'Cost Range' =OutputString, 'Number of statements (Current)' = ISNULL(c.cnt,0) , 'Number of statements (Recommended)' = ISNULL(r.cnt,0)  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

			

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_costrange_helper_xml]    Script Date: 05/23/2008 14:49:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_detail_helper_relational]    Script Date: 05/23/2008 14:49:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_query_detail_helper_relational]

			@SessionID		int

			as

			begin select 'Statement Id' =QueryID, 'Statement String' =StatementString, 'Statement Type' = CASE 

					WHEN StatementType = 0 THEN 'Select'

					WHEN StatementType = 1 THEN 'Update'

					WHEN StatementType = 2 THEN 'Insert'

					WHEN StatementType = 3 THEN 'Delete'

					end,'Current Statement Cost' =CAST(CurrentCost as decimal(38,7)), 'Recommended Statement Cost' =CAST(RecommendedCost as decimal(38,7)), 'Event String' =EventString	from [msdb].[dbo].[DTA_reports_query]

						where SessionID=@SessionID  order by QueryID ASC end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_detail_helper_xml]    Script Date: 05/23/2008 14:49:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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'

			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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_indexrelations_helper_relational]    Script Date: 05/23/2008 14:49:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_query_indexrelations_helper_relational]

							@SessionID		int,

							@Recommended	int

							as

							begin 	select 'Statement Id' =Q.QueryID, 'Statement String' =Q.StatementString,'Database Name' =D.DatabaseName, 'Schema Name' =T.SchemaName, 'Table/View Name' =T.TableName, 'Index Name' =I.IndexName 	 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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_indexrelations_helper_xml]    Script Date: 05/23/2008 14:49:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_interactivestatus]    Script Date: 05/23/2008 14:49:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_outputinformation]    Script Date: 05/23/2008 14:49:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_progressinformation]    Script Date: 05/23/2008 14:49:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_tuninglogtablename]    Script Date: 05/23/2008 14:49:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_start_xmlprefix]    Script Date: 05/23/2008 14:49:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_start_xmlprefix]

as

begin

	declare @startTags nvarchar(128)

	set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>'

	select @startTags

end



GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_table_access_helper_relational]    Script Date: 05/23/2008 14:49:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_table_access_helper_relational]

			@SessionID		int

			as

			begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_table_access_helper_xml]    Script Date: 05/23/2008 14:49:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_update_session]    Script Date: 05/23/2008 14:49:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_update_tuninglog_errorfrequency]    Script Date: 05/23/2008 14:49:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_view_table_helper_relational]    Script Date: 05/23/2008 14:49:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_view_table_helper_relational]

						@SessionID		int

						as

						begin  select 'View Id' =T2.TableID, 'Database Name' =D.DatabaseName, 'Schema Name' =T2.SchemaName, 'View Name' =T2.TableName, 'Database Name' =D.DatabaseName, 'Schema Name' =T1.SchemaName, 'Table Name' =T1.TableName 	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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_view_table_helper_xml]    Script Date: 05/23/2008 14:49:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_wkld_analysis_helper_relational]    Script Date: 05/23/2008 14:49:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_wkld_analysis_helper_relational]

						@SessionID		int

						as

						begin	select 'Statement Type' = CASE 

						WHEN StatementType = 0 THEN 'Select'

						WHEN StatementType = 1 THEN 'Update'

						WHEN StatementType = 2 THEN 'Insert'

						WHEN StatementType = 3 THEN 'Delete'

						end, 'Number of Statements' =COUNT(QueryID), 'Cost Decreased' =SUM(CASE

															WHEN RecommendedCost<CurrentCost THEN 1 else 0 end), 'Cost Increased' =SUM(CASE

						WHEN RecommendedCost>CurrentCost THEN 1 else 0 end), 'No Change' =SUM(CASE

						WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) 	from 

						[msdb].[dbo].[DTA_reports_query]

						where 

						SessionID=@SessionID group by StatementType  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_wkld_analysis_helper_xml]    Script Date: 05/23/2008 14:49:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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'

			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						



USE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_add_session]    Script Date: 05/23/2008 14:48:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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,[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_check_permission]    Script Date: 05/23/2008 14:48:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_check_permission] 

				@SessionID int

as 

begin

	declare @retcode  int

	declare @dbname nvarchar(128)

	declare @sql nvarchar(256)

	declare @dbid int



	set nocount on

	set @retcode = 1



	-- Check if SA

	if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)

	begin

		return(0) 

	end



	-- if not yukon return

	if (patindex('%9.00.%',@@version) = 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

/****** Object:  StoredProcedure [dbo].[sp_DTA_column_access_helper_relational]    Script Date: 05/23/2008 14:48:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_column_access_helper_relational]

			@SessionID		int

			as

			begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,C1.ColumnName as 'Column Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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 end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_column_access_helper_xml]    Script Date: 05/23/2008 14:48:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_database_access_helper_relational]    Script Date: 05/23/2008 14:48:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_database_access_helper_relational]

			@SessionID		int

			as

			begin select D1.DatabaseName as 'Database Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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  end

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_database_access_helper_xml]    Script Date: 05/23/2008 14:48:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_delete_session]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_end_xmlprefix]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_end_xmlprefix]

as

begin

	declare @endTags nvarchar(128)

	set @endTags = N'</AnalysisReport></DTAOutput></DTAXML>'

	select @endTags

end

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_event_weight_helper_relational]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_event_weight_helper_relational]

			@SessionID		int

			as

			begin	select 'Event String'= EventString, 'Weight' = CAST(EventWeight as decimal(38,2)) 	from [msdb].[dbo].[DTA_reports_query]

					where SessionID=@SessionID and EventWeight>0

					order by EventWeight desc  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_event_weight_helper_xml]    Script Date: 05/23/2008 14:48:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_columntableids]    Script Date: 05/23/2008 14:48:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_databasetableids]    Script Date: 05/23/2008 14:48:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_indexableids]    Script Date: 05/23/2008 14:48:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_interactivestatus]    Script Date: 05/23/2008 14:48:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_pftableids]    Script Date: 05/23/2008 14:48:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_pstableids]    Script Date: 05/23/2008 14:48:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_session_report]    Script Date: 05/23/2008 14:48:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_session_tuning_results]    Script Date: 05/23/2008 14:48:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_tableids]    Script Date: 05/23/2008 14:48:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_tuninglog]    Script Date: 05/23/2008 14:48:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_get_tuningoptions]    Script Date: 05/23/2008 14:48:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_help_session]    Script Date: 05/23/2008 14:48:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_current_detail_helper_xml]    Script Date: 05/23/2008 14:48:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

		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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

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

		CAST(I.Storage as decimal(38,2)) as [Index!5!IndexSizeInMB],

		I.NumRows as [Index!5!NumberOfRows]

		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

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_detail_current_helper_relational]    Script Date: 05/23/2008 14:48:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_index_detail_current_helper_relational]

						@SessionID		int

						as

						begin	select 'Database Name' = D.DatabaseName, 'Schema Name' = T.SchemaName, 'Table/View Name' = T.TableName, 'Index Name' = I.IndexName, 'Clustered' =	CASE

						WHEN I.IsClustered = 1 THEN 'Yes'	

						WHEN I.IsClustered = 0 THEN 'No'

						end, 'Unique' =	CASE

						WHEN I.IsUnique = 1 THEN 'Yes'		

						WHEN I.IsUnique = 0 THEN 'No'

						end	, 'Heap' =	CASE

						WHEN I.IsHeap = 1 THEN 'Yes'		

						WHEN I.IsHeap = 0 THEN 'No'

						end	, 'Index Size (MB)'= CAST(I.Storage as decimal(38,2)) , 'Number of Rows'= NumRows 	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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_detail_recommended_helper_relational]    Script Date: 05/23/2008 14:48:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_index_detail_recommended_helper_relational]

						@SessionID		int

						as

						begin	select 'Database Name' = D.DatabaseName, 'Schema Name' = T.SchemaName, 'Table/View Name' = T.TableName, 'Index Name' = I.IndexName, 'Clustered' =	CASE

						WHEN I.IsClustered = 1 THEN 'Yes'	

						WHEN I.IsClustered = 0 THEN 'No'

						end, 'Unique' =	CASE

						WHEN I.IsUnique = 1 THEN 'Yes'		

						WHEN I.IsUnique = 0 THEN 'No'

						end	, 'Heap' =	CASE

						WHEN I.IsHeap = 1 THEN 'Yes'		

						WHEN I.IsHeap = 0 THEN 'No'

						end	, 'Index Size (MB)'= CAST(I.RecommendedStorage as decimal(38,2)) , 'Number of Rows'= NumRows 	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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_recommended_detail_helper_xml]    Script Date: 05/23/2008 14:48:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

	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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

		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!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows]

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

		CAST(I.RecommendedStorage as decimal(38,2)) as [Index!5!IndexSizeInMB],

		I.NumRows as [Index!5!NumberOfRows]

		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

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_usage_helper_relational]    Script Date: 05/23/2008 14:48:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_index_usage_helper_relational]

	@SessionID		int,

	@IsRecommended	int

	as begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,I1.IndexName as 'Index Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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 end

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml]    Script Date: 05/23/2008 14:48:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_DTA_tuninglog]    Script Date: 05/23/2008 14:48:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_progressinformation]    Script Date: 05/23/2008 14:48:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_column]    Script Date: 05/23/2008 14:48:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_database]    Script Date: 05/23/2008 14:48:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_index]    Script Date: 05/23/2008 14:48:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_insert_reports_index]

	@SessionID			int,

	@TableID			int,

	@IndexName			sysname,

	@IsClustered		bit,

	@IsUnique			bit,

	@IsHeap				bit,

	@IsExisting			bit,

	@Storage			int,

	@NumRows			int,

	@IsRecommended		bit,

	@RecommendedStorage int,

	@PartitionSchemeID	int,

	@SessionUniquefier  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_index]([TableID], [IndexName], [IsClustered], [IsUnique], [IsHeap],[IsExisting], [Storage], [NumRows], [IsRecommended], [RecommendedStorage], [PartitionSchemeID],[SessionUniquefier])	

	values(@TableID,@IndexName,@IsClustered,@IsUnique,@IsHeap,@IsExisting,@Storage,@NumRows,@IsRecommended,@RecommendedStorage,@PartitionSchemeID,@SessionUniquefier)

end	



GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_indexcolumn]    Script Date: 05/23/2008 14:48:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_partitionfunction]    Script Date: 05/23/2008 14:48:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_partitionscheme]    Script Date: 05/23/2008 14:48:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_query]    Script Date: 05/23/2008 14:48:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_querycolumn]    Script Date: 05/23/2008 14:48:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_querydatabase]    Script Date: 05/23/2008 14:48:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_queryindex]    Script Date: 05/23/2008 14:48:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_querytable]    Script Date: 05/23/2008 14:48:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_table]    Script Date: 05/23/2008 14:48:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_insert_reports_tableview]    Script Date: 05/23/2008 14:48:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_cost_helper_relational]    Script Date: 05/23/2008 14:48:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_query_cost_helper_relational]

			@SessionID		int

			as

			begin 	select 'Statement Id' = QueryID, 'Statement String' = StatementString, 'Percent Improvement' = 	

					CASE

						WHEN CurrentCost = 0 THEN 0.00

						WHEN CurrentCost <> 0 THEN

						CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal (20,2))

					end , 'Statement Type' = CASE 

							WHEN StatementType = 0 THEN 'Select'

							WHEN StatementType = 1 THEN 'Update'

							WHEN StatementType = 2 THEN 'Insert'

							WHEN StatementType = 3 THEN 'Delete'

							end 	from [msdb].[dbo].[DTA_reports_query]

					where SessionID=@SessionID

					order by 'Percent Improvement' desc  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_cost_helper_xml]    Script Date: 05/23/2008 14:48:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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'

			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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_costrange_helper_relational]    Script Date: 05/23/2008 14:48:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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  select 'Cost Range' =OutputString, 'Number of statements (Current)' = ISNULL(c.cnt,0) , 'Number of statements (Recommended)' = ISNULL(r.cnt,0)  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

			

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_costrange_helper_xml]    Script Date: 05/23/2008 14:49:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_detail_helper_relational]    Script Date: 05/23/2008 14:49:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_query_detail_helper_relational]

			@SessionID		int

			as

			begin select 'Statement Id' =QueryID, 'Statement String' =StatementString, 'Statement Type' = CASE 

					WHEN StatementType = 0 THEN 'Select'

					WHEN StatementType = 1 THEN 'Update'

					WHEN StatementType = 2 THEN 'Insert'

					WHEN StatementType = 3 THEN 'Delete'

					end,'Current Statement Cost' =CAST(CurrentCost as decimal(38,7)), 'Recommended Statement Cost' =CAST(RecommendedCost as decimal(38,7)), 'Event String' =EventString	from [msdb].[dbo].[DTA_reports_query]

						where SessionID=@SessionID  order by QueryID ASC end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_detail_helper_xml]    Script Date: 05/23/2008 14:49:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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'

			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

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_indexrelations_helper_relational]    Script Date: 05/23/2008 14:49:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_query_indexrelations_helper_relational]

							@SessionID		int,

							@Recommended	int

							as

							begin 	select 'Statement Id' =Q.QueryID, 'Statement String' =Q.StatementString,'Database Name' =D.DatabaseName, 'Schema Name' =T.SchemaName, 'Table/View Name' =T.TableName, 'Index Name' =I.IndexName 	 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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_query_indexrelations_helper_xml]    Script Date: 05/23/2008 14:49:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_interactivestatus]    Script Date: 05/23/2008 14:49:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_outputinformation]    Script Date: 05/23/2008 14:49:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_progressinformation]    Script Date: 05/23/2008 14:49:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_set_tuninglogtablename]    Script Date: 05/23/2008 14:49:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_start_xmlprefix]    Script Date: 05/23/2008 14:49:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_DTA_start_xmlprefix]

as

begin

	declare @startTags nvarchar(128)

	set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>'

	select @startTags

end



GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_table_access_helper_relational]    Script Date: 05/23/2008 14:49:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 create procedure [dbo].[sp_DTA_table_access_helper_relational]

			@SessionID		int

			as

			begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_table_access_helper_xml]    Script Date: 05/23/2008 14:49:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_update_session]    Script Date: 05/23/2008 14:49:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_update_tuninglog_errorfrequency]    Script Date: 05/23/2008 14:49:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_view_table_helper_relational]    Script Date: 05/23/2008 14:49:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_view_table_helper_relational]

						@SessionID		int

						as

						begin  select 'View Id' =T2.TableID, 'Database Name' =D.DatabaseName, 'Schema Name' =T2.SchemaName, 'View Name' =T2.TableName, 'Database Name' =D.DatabaseName, 'Schema Name' =T1.SchemaName, 'Table Name' =T1.TableName 	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  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_view_table_helper_xml]    Script Date: 05/23/2008 14:49:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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

/****** Object:  StoredProcedure [dbo].[sp_DTA_wkld_analysis_helper_relational]    Script Date: 05/23/2008 14:49:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

	create procedure [dbo].[sp_DTA_wkld_analysis_helper_relational]

						@SessionID		int

						as

						begin	select 'Statement Type' = CASE 

						WHEN StatementType = 0 THEN 'Select'

						WHEN StatementType = 1 THEN 'Update'

						WHEN StatementType = 2 THEN 'Insert'

						WHEN StatementType = 3 THEN 'Delete'

						end, 'Number of Statements' =COUNT(QueryID), 'Cost Decreased' =SUM(CASE

															WHEN RecommendedCost<CurrentCost THEN 1 else 0 end), 'Cost Increased' =SUM(CASE

						WHEN RecommendedCost>CurrentCost THEN 1 else 0 end), 'No Change' =SUM(CASE

						WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) 	from 

						[msdb].[dbo].[DTA_reports_query]

						where 

						SessionID=@SessionID group by StatementType  end 

GO

/****** Object:  StoredProcedure [dbo].[sp_DTA_wkld_analysis_helper_xml]    Script Date: 05/23/2008 14:49:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[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'

			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