403WebShell

403Webshell
Server IP : 209.209.40.120 / Your IP : 216.73.217.112
Web Server : Microsoft-IIS/10.0
System : Windows NT NEWWWW 10.0 build 17763 (Windows Server 2019) i586
User : NEWWWW$ ( 0)
PHP Version : 8.3.30
Disable Function : NONE
MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF
Directory : /Program Files (x86)/Microsoft SQL Server Management Studio 18/Common7/Resources/1033/
Upload File :
[ Back ]
Current File : /Program Files (x86)/Microsoft SQL Server Management Studio 18/Common7/Resources/1033/DTAENGINE.rll
MZ����@���	�!�L�!This program cannot be run in DOS mode.

$�����ծ��ծ��ծ���Q��ծ���9��ծ������ծ�Rich�ծ�PEL�]�"!�@�Ӵ@ X��8?T.rdataP@@.rsrcX� � @@�]�TT�]���]
T��RSDS�ׯ$D���P˘d:\dbs\sh\s19s\0924_133349\cmd\a\obj\x86retail\sql\ntdbms\dta\dtaengine\resource\dtaengineresource.vcxproj\DTAEngine.pdbGCTLT.rdataT�.rdata$zzzdbg �.rsrc$01�&��.rsrc$02��H���`�X�x�n�����x������o�����n��� �8�P�h�������	��
�����
(�@�X�p�������������0�H�`�	x	�	�	�	�	�	�	�	�			(	8	H	X	h	x	�	�	�	�	�	�	�	�			(	8	H������.g�&&���2�"tH*D�1�@>F��KF@R0pY��b0	�k�{��`��(0�����0`��t��70	(�28�T��� �2X����}
DTA_TABLES
DTA_TABLES_PG
DTA_SPROCS
DTA_SPROCS_PG��/***************************************************************/

/* Make sure we are int the right database                     */

/***************************************************************/

use msdb

go

PRINT '----------------------------------'

PRINT 'Starting execution of DTA_tables.SQL'

PRINT '----------------------------------'

go





/**************************************************************/

/* DTA_input                                                   */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_input')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_input...'



   CREATE TABLE dbo.DTA_input

   (

   SessionName sysname not null,

   SessionID int identity primary key,

   TuningOptions nvarchar(max) not null,

   CreationTime datetime not null default GetDate(),

   ScheduledStartTime datetime not null default GetDate(),

   ScheduledJobName sysname not null default '',

   InteractiveStatus tinyint not null default 0,

   LogTableName nvarchar(1280) not null default '',

   GlobalSessionID uniqueidentifier default  NEWID()

   )

END

go



/**************************************************************/

/* DTA_progress                                                */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_progress')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_progress...'

   

   CREATE TABLE dbo.DTA_progress

   (

   ProgressEventID int identity,

   SessionID int references DTA_input(SessionID) on delete cascade,

   TuningStage tinyint not null default 0,

   WorkloadConsumption tinyint not null check (WorkloadConsumption>=0 and WorkloadConsumption<=100),

   EstImprovement int not null default 0,

   ProgressEventTime datetime not null default GetDate(),

   ConsumingWorkLoadMessage nvarchar(256),

   PerformingAnalysisMessage nvarchar(256),

   GeneratingReportsMessage nvarchar(256)

   )



   CREATE CLUSTERED INDEX DTA_progress_index on DTA_progress(SessionID)

END

go



/**************************************************************/

/* DTA_output                                                  */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_output')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_output...'



   CREATE TABLE dbo.DTA_output

   (

   SessionID int not null primary key references DTA_input(SessionID) on delete cascade,

   TuningResults nvarchar(max) not null,

   StopTime datetime not null default GetDate(),

   FinishStatus tinyint not null default 0

   )



END

go



/**************************************************************/

/* DTA_tuningresults                                          */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_tuningresults')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_tuningresults...'



   CREATE TABLE dbo.DTA_tuningresults

   (

   SessionID int not null primary key references DTA_input(SessionID) on delete cascade,

   StopTime datetime not null default GetDate(),

   FinishStatus tinyint not null default 0,

   LastPartNumber int

   )



END

go



/**************************************************************/

/* DTA_tuningresults_part                                     */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_tuningresults_part')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_tuningresults_part...'



   CREATE TABLE dbo.DTA_tuningresults_part

   (

   SessionID int not null references DTA_input(SessionID) on delete cascade,

   PartNumber int not null,

   Content nvarchar(3500) not null,

   primary key(SessionID, PartNumber)

   )



END

go



/**************************************************************/

/* DTA_tuninglog                                              */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_tuninglog')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_tuninglog...'

   

   CREATE TABLE dbo.DTA_tuninglog

   (

   SessionID int not null references DTA_input(SessionID) on delete cascade,

   RowID int not null,

   CategoryID nvarchar(4) not null,

   Event nvarchar(max) null,

   Statement nvarchar(max) null,

   Frequency int not null,

   Reason nvarchar(max) null

   )

   

   CREATE CLUSTERED INDEX DTA_tuninglog_index on DTA_tuninglog(SessionID,RowID)

   

END

go





/**************************************************************/

/* DTA_reports_database                                        */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_database')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_reports_database...'

   

   CREATE TABLE dbo.DTA_reports_database

   (

   DatabaseID int identity primary key,

   SessionID int not null references DTA_input(SessionID) on delete cascade, 

   DatabaseName sysname not null,

   IsDatabaseSelectedToTune int

   )



   CREATE INDEX DTA_reports_database_index on DTA_reports_database(SessionID)

END

go



/**************************************************************/

/* DTA_reports_partitionfunction                               */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_partitionfunction')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_reports_partitionfunction...'

   

   CREATE TABLE dbo.DTA_reports_partitionfunction

   (

   PartitionFunctionID int identity primary key, 

   DatabaseID int not null references DTA_reports_database(DatabaseID) on delete cascade, 

   PartitionFunctionName sysname not null,

   PartitionFunctionDefinition nvarchar(max) not null

   )



   CREATE INDEX DTA_reports_partitionfunction_index on DTA_reports_partitionfunction(DatabaseID)

END

go



/**************************************************************/

/* DTA_reports_partitionscheme                                 */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_partitionscheme')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_reports_partitionscheme...'

   

   CREATE TABLE dbo.DTA_reports_partitionscheme

   (

   PartitionSchemeID int identity primary key,

   PartitionFunctionID int not null references DTA_reports_partitionfunction(PartitionFunctionID) on delete cascade,

   PartitionSchemeName sysname not null,

   PartitionSchemeDefinition nvarchar(max) not null

   )



   CREATE INDEX DTA_reports_partitionscheme_index on DTA_reports_partitionscheme(PartitionFunctionID)

END

go



/**************************************************************/

/* DTA_reports_table                                           */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_table')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_reports_table...'



   CREATE TABLE dbo.DTA_reports_table

   (

   TableID int identity primary key, 

   DatabaseID int not null references DTA_reports_database(DatabaseID) on delete cascade, 

   SchemaName sysname not null,

   TableName sysname not null,

   IsView bit not null default 0

   )



   CREATE INDEX DTA_reports_table_index on DTA_reports_table(DatabaseID)

END

go



/**************************************************************/

/* DTA_reports_tableview                                       */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_tableview')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_reports_tableview...'



   CREATE TABLE dbo.DTA_reports_tableview

   (

   TableID int not null references DTA_reports_table(TableID) on delete cascade,

   ViewID int not null references DTA_reports_table(TableID)

   )



   CREATE CLUSTERED INDEX DTA_reports_tableview_index on DTA_reports_tableview(TableID)



   CREATE INDEX DTA_reports_tableview_index2 on DTA_reports_tableview(ViewID)

END

go



/**************************************************************/

/* DTA_reports_query                                           */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_query')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_reports_query...'



   CREATE TABLE dbo.DTA_reports_query

   (

   QueryID int not null,

   SessionID int not null references DTA_input(SessionID) on delete cascade, 

   StatementType smallint not null, 

   StatementString nvarchar(max) not null,

   CurrentCost float not null, 

   RecommendedCost float not null,  

   Weight float not null, 

   EventString nvarchar(max),

   EventWeight float not null

   )



   ALTER TABLE DTA_reports_query ADD CONSTRAINT DTA_reports_query_pk PRIMARY KEY (SessionID,QueryID)

END

go



/**************************************************************/

/* DTA_reports_querytable                                      */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_querytable')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

   	PRINT ''

	PRINT 'Creating table DTA_reports_querytable...'



   CREATE TABLE dbo.DTA_reports_querytable

   (

   QueryID int not null,

   SessionID int not null,

   TableID int not null references DTA_reports_table(TableID)

   )

   ALTER TABLE DTA_reports_querytable ADD CONSTRAINT DTA_reports_querytable_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE



   CREATE CLUSTERED INDEX DTA_reports_querytable_index on DTA_reports_querytable(SessionID,QueryID)



   CREATE INDEX DTA_reports_querytable_index2 on DTA_reports_querytable(TableID)

END

go

/**************************************************************/

/* DTA_reports_querydatabase                                      */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_querydatabase')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_reports_querydatabase...'

	CREATE TABLE dbo.DTA_reports_querydatabase

   (

   QueryID int not null,

   SessionID int not null,

   DatabaseID int not null references DTA_reports_database(DatabaseID)

   )

   ALTER TABLE DTA_reports_querydatabase ADD CONSTRAINT DTA_reports_querydatabase_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE



   CREATE CLUSTERED INDEX DTA_reports_querydatabase_index on DTA_reports_querydatabase(SessionID,QueryID)



   CREATE INDEX DTA_reports_querydatabase_index2 on DTA_reports_querydatabase(DatabaseID)

END

go

/**************************************************************/

/* DTA_reports_index                                           */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_index')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_reports_index...'

   CREATE TABLE dbo.DTA_reports_index

   (

   IndexID int identity primary key,

   TableID int not null references DTA_reports_table(TableID) on delete cascade, 

   IndexName sysname not null,

   IsClustered  bit not null default 0, 

   IsUnique bit not null default 0,

   IsHeap   bit not null default 1,

   IsExisting bit not null default 1, 

   IsFiltered bit not null default 0, 

   Storage float not null,

   NumRows bigint not null,

   IsRecommended  bit not null default 0,

   RecommendedStorage float not null,

   PartitionSchemeID int,

   SessionUniquefier int,

   FilterDefinition nvarchar(1024) not null

   )



   CREATE INDEX DTA_reports_indexindex on DTA_reports_index(TableID)



   CREATE INDEX DTA_reports_indexindex2 on DTA_reports_index(PartitionSchemeID)

END

go



/**************************************************************/

/* DTA_reports_queryindex                                      */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_queryindex')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_reports_queryindex...'

   CREATE TABLE dbo.DTA_reports_queryindex

   (

   QueryID int not null, 

   SessionID int not null,

   IndexID int not null references DTA_reports_index(IndexID),

   IsRecommendedConfiguration bit not null 

   )

   ALTER TABLE DTA_reports_queryindex ADD CONSTRAINT DTA_reports_queryindex_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE



   CREATE CLUSTERED INDEX DTA_reports_queryindex_index on DTA_reports_queryindex(SessionID,QueryID)



   CREATE INDEX DTA_reports_queryindex_index2 on DTA_reports_queryindex(IndexID)

END

go



/**************************************************************/

/* DTA_reports_column                                          */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_column')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_reports_column...'   

   CREATE TABLE dbo.DTA_reports_column

   (

   ColumnID int identity primary key,

   TableID int not null references DTA_reports_table(TableID) on delete cascade, 

   ColumnName sysname not null

   )



   CREATE INDEX DTA_reports_column_index on DTA_reports_column(TableID)

END

go



/**************************************************************/

/* DTA_reports_indexcolumn                                      */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_indexcolumn')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_reports_indexcolumn...'   

   CREATE TABLE dbo.DTA_reports_indexcolumn

   (

   IndexID int not null references DTA_reports_index(IndexID) on delete cascade,

   ColumnID int not null,

   ColumnOrder int,

   PartitionColumnOrder int not null default 0,

   IsKeyColumn bit not null default 1,

   IsDescendingColumn bit not null default 1 

   )



   CREATE CLUSTERED INDEX DTA_reports_indexcolumn_index on DTA_reports_indexcolumn(IndexID)



   CREATE INDEX DTA_reports_indexcolumn_index2 on DTA_reports_indexcolumn(ColumnID)

   END

go



/**************************************************************/

/* DTA_reports_querycolumn                                     */

/*                                                            */

/**************************************************************/

IF (NOT EXISTS (SELECT *

                FROM msdb.dbo.sysobjects

                WHERE (name = N'DTA_reports_querycolumn')

                  AND (type = 'U')

                  AND (uid = USER_ID('dbo'))))

BEGIN

	PRINT ''

	PRINT 'Creating table DTA_reports_querycolumn...'   

   CREATE TABLE dbo.DTA_reports_querycolumn

   (

   QueryID int not null, 

   SessionID int not null,

   ColumnID int not null references DTA_reports_column(ColumnID)

    )

   ALTER TABLE DTA_reports_querycolumn ADD CONSTRAINT DTA_reports_querycolumn_fk FOREIGN KEY (SessionID,QueryID) REFERENCES DTA_reports_query(SessionID,QueryID) on DELETE CASCADE



   CREATE CLUSTERED INDEX DTA_reports_querycolumn_index on DTA_reports_querycolumn(SessionID,QueryID)



   CREATE INDEX DTA_reports_querycolumn_index2 on DTA_reports_querycolumn(ColumnID)

END

go





��/****************************************************************************

//		Copyright (c) Microsoft Corporation.

//

// @File: DTA_tables_PG.sql

//

//

// @EndHeader@

*****************************************************************************/



CREATE EXTENSION IF NOT EXISTS pgcrypto;



CREATE SCHEMA IF NOT EXISTS dta;



/**************************************************************/

/* DTA_input                                                  */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_input

(

SessionName varchar(128) not null,

SessionID serial primary key,

TuningOptions text not null,

CreationTime timestamp not null default now(),

ScheduledStartTime timestamp not null default now(),

ScheduledJobName varchar(128) not null default '',

InteractiveStatus smallint not null default 0,

LogTableName varchar(1280) not null default '',

GlobalSessionID UUID default gen_random_uuid()

);



CREATE INDEX IF NOT EXISTS DTA_input_index_globalsessionid on dta.DTA_input(GlobalSessionID);



/**************************************************************/

/* DTA_progress                                               */

/*                                                            */

/**************************************************************/

  

CREATE TABLE IF NOT EXISTS dta.DTA_progress

(

ProgressEventID serial,

SessionID int references dta.DTA_input(SessionID) on delete cascade,

TuningStage smallint not null default 0,

WorkloadConsumption smallint not null check (WorkloadConsumption>=0 and WorkloadConsumption<=100),

EstImprovement int not null default 0,

ProgressEventTime timestamp not null default now(),

ConsumingWorkLoadMessage varchar(256),

PerformingAnalysisMessage varchar(256),

GeneratingReportsMessage varchar(256)

);



CREATE INDEX IF NOT EXISTS DTA_progress_index on dta.DTA_progress(SessionID);



/**************************************************************/

/* DTA_output                                                 */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_output

(

SessionID int not null primary key references dta.DTA_input(SessionID) on delete cascade,

TuningResults text not null,

StopTime timestamp not null default now(),

FinishStatus smallint not null default 0

);



/**************************************************************/

/* DTA_tuningresults                                          */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_tuningresults

(

SessionID int not null primary key references dta.DTA_input(SessionID) on delete cascade,

StopTime timestamp not null default now(),

FinishStatus smallint not null default 0,

LastPartNumber int

);



/**************************************************************/

/* DTA_tuningresults_part                                     */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_tuningresults_part

(

SessionID int not null references dta.DTA_input(SessionID) on delete cascade,

PartNumber int not null,

Content text not null,

primary key(SessionID, PartNumber)

);



/**************************************************************/

/* DTA_tuninglog                                              */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_tuninglog

(

SessionID int not null references dta.DTA_input(SessionID) on delete cascade,

RowID int not null,

CategoryID varchar(4) not null,

Event text null,

Statement text null,

Frequency int not null,

Reason text null

);



CREATE INDEX IF NOT EXISTS DTA_tuninglog_index on dta.DTA_tuninglog(SessionID,RowID);





/**************************************************************/

/* DTA_reports_database                                       */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_reports_database

(

DatabaseID serial primary key,

SessionID int not null references dta.DTA_input(SessionID) on delete cascade, 

DatabaseName varchar(128) not null,

IsDatabaseSelectedToTune int

);





CREATE INDEX IF NOT EXISTS DTA_reports_database_index on dta.DTA_reports_database(SessionID);



/**************************************************************/

/* DTA_rec                                                    */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_rec

(

SessionID int not null primary key references dta.DTA_input(SessionID) on delete cascade,

GlobalSessionID UUID not null,

DatabaseName varchar(128) not null,

StartTime timestamp not null,

StopTime timestamp not null,

FinishStatus smallint not null,

EstPctImprovement real,

EventWorkloadCount int,

EventTunedCount int,

StatementTunedCount int,

IdxRecCount int,

Message text

);



/**************************************************************/

/* DTA_rec_idx                                                */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_rec_idx

(

SessionID int not null references dta.DTA_input(SessionID) on delete cascade,

IdxID int not null,

RecType int not null,

SchemaName varchar(128) not null,

TableName varchar(128) not null,

ColumnList varchar(128)[] not null,

Command text not null,

Benefit double precision,

IndexSizeInMB double precision,

primary key(SessionID, IdxID)

);



/**************************************************************/

/* DTA_key_val                                                */

/*                                                            */

/**************************************************************/

CREATE TABLE IF NOT EXISTS dta.DTA_key_val

(

key varchar(128) primary key not null,

intval int,

txtval text

); ��/****************************************************************************

//		Copyright (c) Microsoft Corporation.

//

// @File: DTA_sprocs.sql

//

// Purpose:

//		Script for DTA/DTAEngine90 communication

//

// Notes:

//	

// History:

//

//

// @EndHeader@

*****************************************************************************/

use msdb

go

PRINT '----------------------------------'

PRINT 'Starting execution of DTA_sprocs.SQL'

PRINT '----------------------------------'

go

/*****************************************************************************

	sp_DTA_check_permission

	@SessionID,		- ID of a session to check

	

	Checks if caller has enough permissions

	If caller in SA role then returns 0 (allow)

	If caller in DB role and if caller owns session then returns 0 (allow)

	Always called as a prolog to any external SP's

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_check_permission...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_check_permission' and type = 'P')

   drop procedure sp_DTA_check_permission 

go

create procedure sp_DTA_check_permission 

				@SessionID int

as 

begin

	declare @retcode  int

	declare @dbname nvarchar(128)	

	declare @sql nvarchar(256)

	declare @dbid int

	declare @ServVersion nvarchar(128)

	

	set nocount on

	set @retcode = 1



	-- Check if SA

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

	begin

		return(0) 

	end

	

	-- if SQL Server 2000 return

	select @ServVersion=CONVERT(nvarchar(128), serverproperty(N'ProductVersion'))

	if (patindex('8.%',@ServVersion) > 0)

	begin

		return (1)

	end

	

	-- declare and open a cursor and get all the databases specified in the input

	declare db_cursor cursor for

	select DatabaseName from DTA_reports_database

	where SessionID = @SessionID and IsDatabaseSelectedToTune  = 1

	-- open

	open db_cursor

	-- fetch first db name

	fetch next from db_cursor

	into @dbname

	-- loop and get all the databases selected to tune

	while @@fetch_status = 0

	begin

		-- build use db string

		select  @dbid = DB_ID(@dbname)

	

		-- set @retcode to OK. Will be set to 1 in case of issues

		set @retcode = 0



		-- In Yukon this masks the error messages

		set @sql = N'begin try

			dbcc autopilot(5,@dbid) WITH NO_INFOMSGS 

		end try

		begin catch

			set @retcode = 1

		end catch'



		execute sp_executesql @sql

			, N'@dbid int output, @retcode int OUTPUT' 

			, @dbid output 

			, @retcode output

	

		-- if caller is not member of dbo

		if (@retcode = 1)

		begin

			-- close and reset cursor,switch context to current

			-- database and return 1

			close db_cursor

			deallocate db_cursor

			return(1)

		end

		fetch from db_cursor into @dbname

	end

	-- close and reset cursor,switch context to current

	-- database and return 1

	close db_cursor

	deallocate db_cursor

	-- if caller is not member of dbo

	if (@retcode = 1)

	begin

		return(1)

	end

	return(0) 

end		

go

grant exec on sp_DTA_check_permission to public

go

/*****************************************************************************

	sp_DTA_add_session 

		@SessionName, 	- name of the session nvarchar[30]

		@TuningOptions, 	- input xml

		@SessionID OUTPUT   return value, id of the new session.

*****************************************************************************/



print ''

print 'Creating procedure sp_DTA_add_session...'

go

if  exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_add_session' and type = 'P')

   drop procedure sp_DTA_add_session 

go

create procedure sp_DTA_add_session 

   @SessionName sysname, 

   @TuningOptions nvarchar(max),

   @SessionID int OUTPUT,

   @GlobalSessionID uniqueidentifier = NULL

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

				

		if (@GlobalSessionID IS NOT NULL)

			begin

				insert into msdb.dbo.DTA_input (SessionName,TuningOptions, GlobalSessionID) 

				values (@SessionName,@TuningOptions,@GlobalSessionID) 

			end

		else

			begin

				insert into msdb.dbo.DTA_input (SessionName,TuningOptions) 

				values (@SessionName,@TuningOptions) 

			end



		select @SessionID = @@identity	



		

		if @@error <> 0

			begin

				rollback transaction

				return @@error

			end				





		if @@error <> 0

			begin

				rollback transaction

				return @@error

			end				



				-- Create an internal representation of the XML document.

				EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @TuningOptions,

				'<DTAXML  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://schemas.microsoft.com/sqlserver/2004/07/dta"/>'

				

				if @@error <> 0

				begin

					rollback transaction

					return @@error

				end		

				-- Execute a SELECT statement using OPENXML rowset provider.



				insert into DTA_reports_database

				SELECT    @SessionID,msdb.dbo.fn_DTA_unquote_dbname([x:Name]),1

				FROM      OPENXML (@XmlDocumentHandle, 

									'/x:DTAXML/x:DTAInput/x:Server//x:Database',2)

						WITH ([x:Name]  nvarchar(128) ) 

				

				if @@error <> 0

				begin

					rollback transaction

					return @@error

				end		

			

				EXEC sp_xml_removedocument @XmlDocumentHandle



				if @@error <> 0

				begin

					rollback transaction

					return @@error

				end		

				

				



		

			-- Check if allowed to add session

			exec @retval =  sp_DTA_check_permission @SessionID



			if @retval = 1

			begin

				raiserror(31003,-1,-1)

				rollback transaction

				return (1)

			end	



			select @dbcount = count(*) from DTA_reports_database

			where SessionID = @SessionID			

			if @dbcount = 0 

			begin

				rollback transaction

				return (1)

			end



		-- Insert progress record

		insert into [msdb].[dbo].[DTA_progress]

		(SessionID,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage)

		values(@SessionID,0,0,0,N'',N'',N'')



		if @@error <> 0

		begin

			rollback transaction

			return @@error

		end		



		

	-- Commit if input/progress records are updated

	commit transaction

	return 0

go

grant exec on sp_DTA_add_session to public

go

/*****************************************************************************

	sp_DTA_delete_session

			@SessionID		- ID of a session to delete

			

	Deletes a session with a given ID and removes references to that session

	from all tables. 

*****************************************************************************/

				

print ''

print 'Creating procedure sp_DTA_delete_session...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_delete_session' and type = 'P')

   drop procedure sp_DTA_delete_session 

go



create procedure sp_DTA_delete_session 

	@SessionID int 

as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	delete from msdb.dbo.DTA_input where SessionID=@SessionID

end

go

grant exec on sp_DTA_delete_session to public

go

/*****************************************************************************

	sp_DTA_get_session_tuning_results

			@SessionID			- ID of a session requested



	Returns the following rowset:

	TuningResults, FinishStatus, StopTime 

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_session_tuning_results...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_session_tuning_results' and type = 'P')

   drop procedure sp_DTA_get_session_tuning_results 

go

create procedure sp_DTA_get_session_tuning_results 

	@SessionID int 

as 

begin

	set nocount on

	declare @retval  int							

	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	select	FinishStatus,TuningResults 

	from	msdb.dbo.DTA_output 

	where	SessionID=@SessionID

end	

go

grant exec on sp_DTA_get_session_tuning_results to public

go

/*****************************************************************************

	sp_DTA_set_interactivestatus

	@InterActiveStatus - Interactive status in MSDB

	@SessionID	- ID of a session requested

	

	Sets the interactive status in input table

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_set_interactivestatus...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_set_interactivestatus' and type = 'P')

   drop procedure sp_DTA_set_interactivestatus

go

create procedure sp_DTA_set_interactivestatus

	@InterActiveStatus int,

	@SessionID int 



as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	update [msdb].[dbo].[DTA_input] set InteractiveStatus = @InterActiveStatus where SessionID = @SessionID



end	

go

grant exec on sp_DTA_set_interactivestatus to public

go

/*****************************************************************************

	sp_DTA_help_session

		@SessionID				-	ID of a session to list, optional

		@IncludeTuningOptions	-	Flag that indicates that input XML

									Should be included in returned rowset.



		Help Session reports different book keeping info to DTA related to

		one session or all sessions if sa								

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_help_session...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_help_session' and type = 'P')

   drop procedure sp_DTA_help_session 

go



create procedure sp_DTA_help_session 

	@SessionID int = 0,

	@IncludeTuningOptions int = 0

as 

begin

	declare @tuning_owner nvarchar(256)

	declare @retval  int

	declare @InteractiveStatus tinyint

	declare @delta int



	declare @cursessionID int

	declare @dbname nvarchar(128)

	declare @dbid int

	declare @retcode int

	declare @sql nvarchar(256)

	

	set nocount on



	-- List all Sessions mode

	if @SessionID = 0

	begin

		-- If sysadmin role then rowset has all the rows in the table

		-- Return everything

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

		begin

		

			if (@IncludeTuningOptions = 0) 

			begin

				select	I.SessionID, I.SessionName, I.InteractiveStatus,

						I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID

						from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output  O

						on I.SessionID = O.SessionID	

				order by

						I.SessionID	desc					

			end

			

			else if (@IncludeTuningOptions = 1)

			begin

				select	I.SessionID, I.SessionName, I.InteractiveStatus,

						I.CreationTime, I.ScheduledStartTime, O.StopTime,I.TuningOptions,I.GlobalSessionID

						from msdb.dbo.DTA_input  I left outer join msdb.dbo.DTA_output as O

						on I.SessionID = O.SessionID

				order by

						I.SessionID	desc

			end									

	

		end

		

		else 

		begin

			-- Temporary table to store sessionid and databases passed in by user

			create table #allDistinctDbIds (DatabaseID int)

			-- Init variables			

			set @dbid = 0

			set @retcode = 1

			-- Get all database names passed in by user (IsDatabaseSelectedToTune =1)

			declare db_cursor cursor for

			select distinct(DatabaseName) from DTA_reports_database

			where  IsDatabaseSelectedToTune  = 1

			-- Open cursor

			open db_cursor

			-- Fetch first session id and db name

			fetch next from db_cursor

			into @dbname

			

			-- loop and get all the databases selected to tune

			while @@fetch_status = 0

			-- Loop

			begin

				-- set @retcode = 1 in the beginning to indicate success

				set @retcode = 1

				-- Get database id

				select  @dbid = DB_ID(@dbname)

				-- In Yukon this masks the error messages.If not owner dont return

				-- error message in SP

				set @sql = N'begin try

					dbcc autopilot(5,@dbid) WITH NO_INFOMSGS 

				end try

				begin catch

					set @dbid = 0

					set @retcode = 0

				end catch'

				execute sp_executesql @sql

					, N'@dbid int output, @retcode int OUTPUT' 

					, @dbid output 

					, @retcode output

		

				-- dbid is 0 if user doesnt have permission to do dbcc call

				insert into #allDistinctDbIds(DatabaseID) values

								(@dbid)

				-- fetch next								

				fetch from db_cursor into @dbname		

			-- end the cursor loop				

			end		

			-- clean up cursor

			close db_cursor

			deallocate db_cursor





			select SessionID 

			into #allValidSessionIds

			from DTA_input as I

			where

				((select count(*) from

				#allDistinctDbIds ,DTA_reports_database as D

				where #allDistinctDbIds.DatabaseID = DB_ID(D.DatabaseName)

				and I.SessionID = D.SessionID

				group by D.SessionID ) = 

				(select count(*) from DTA_reports_database as D

				where I.SessionID = D.SessionID

				and D.IsDatabaseSelectedToTune = 1

				group by D.SessionID )

				) 

			group by I.SessionID

			



			-- Return only sessions with matching user name

			-- If count of rows with DatabaseID = 0 is > 0 then permission denied

			if ( @IncludeTuningOptions = 0 )

			begin

				select	I.SessionID , I.SessionName, I.InteractiveStatus,

						I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID

						from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output  O 

						on  I.SessionID = O.SessionID

						inner  join #allValidSessionIds S

						on	I.SessionID = S.SessionID

								

						

				order by

						I.SessionID	desc					

			end

			

			else if (@IncludeTuningOptions = 1)

			begin

				select	I.SessionID , I.SessionName, I.InteractiveStatus,

						I.CreationTime, I.ScheduledStartTime, O.StopTime,I.TuningOptions,I.GlobalSessionID

						from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O 

						on  I.SessionID = O.SessionID

						inner  join #allValidSessionIds S

						on	I.SessionID = S.SessionID



						

				order by

						I.SessionID	desc					

										

			end

			drop table #allDistinctDbIds

			drop table #allValidSessionIds

		end

	end



	else

	begin

		exec @retval =  sp_DTA_check_permission @SessionID

		if @retval = 1

		begin

			raiserror(31002,-1,-1)

			return(1)

		end	

	

		if ( @IncludeTuningOptions = 0) 

		begin

			select	I.SessionID, I.SessionName, I.InteractiveStatus,

					I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID

			from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O

			on  I.SessionID = O.SessionID

			where I.SessionID = @SessionID	

		end

		else if (@IncludeTuningOptions = 1)

		begin

			select	I.SessionID, I.SessionName, I.InteractiveStatus,

					I.CreationTime, I.ScheduledStartTime, O.StopTime,I.TuningOptions,I.GlobalSessionID

			from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O

			on  I.SessionID = O.SessionID

			where	I.SessionID = @SessionID				

		end

	

		-- Second rowset returned for DTA to process progress information

		select	ProgressEventID,TuningStage,WorkloadConsumption,EstImprovement,

				ProgressEventTime ,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage

		from	msdb.dbo.DTA_progress 

		where	SessionID=@SessionID

		order by ProgressEventID

				



		-- Set interactive status to 6 if a time of 5 mins has elapsed

		-- Next time help session is called DTA will exit

		

		select	 @InteractiveStatus=InteractiveStatus

		from msdb.dbo.DTA_input

		where SessionID = @SessionID	



		if (@InteractiveStatus IS NOT NULL and( @InteractiveStatus <> 4 and  @InteractiveStatus <> 6)) 

		begin

			select @delta=DATEDIFF(minute ,ProgressEventTime,getdate())

			from msdb.dbo.DTA_progress 

			where  SessionID =@SessionID	

			order by TuningStage ASC

			

			if(@delta > 30)

			begin

				update [msdb].[dbo].[DTA_input] set InteractiveStatus = 6

				where SessionID = @SessionID

			end

		end



		

	end

end								

go

grant exec on sp_DTA_help_session to public

go

/*****************************************************************************

	sp_dta_update_session

		@SessionID,		- ID of a session to update

		[@SessionName,]	- New session name (optional)

		[@Status]		- New session status (optional)

		

		Possible values:

		0   stop session

		1   cancel session

		2   start session (currently not used, reserved)

		At least one of the optional parameters must be provided.

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_update_session...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_update_session' and type = 'P')

   drop procedure sp_DTA_update_session 

go

create procedure sp_DTA_update_session 

	@SessionID int, 

	@SessionName sysname = NULL, 

	@InteractiveStatus tinyint = NULL

as 

begin  

	declare	@x_SessionName sysname

	declare @x_InteractiveStatus tinyint

	declare @retval  int

	declare @ErrorString nvarchar(500)





	set nocount on

	select @SessionName = LTRIM(RTRIM(@SessionName))

	

	

	declare	@dup_SessionName sysname



	if @SessionName IS NOT NULL

	begin

		select @dup_SessionName = @SessionName

		from msdb.dbo.DTA_input

		where SessionName = @SessionName

	

		if (@dup_SessionName IS NOT NULL)

			begin

				set @ErrorString = 'The session ' + '"' + LTRIM(RTRIM(@SessionName)) + '"' +' already exists. Please use a different session name.'

				raiserror (31001, -1,-1,@SessionName)

				return(1)

			end				

	end

	

	exec @retval =  sp_DTA_check_permission @SessionID

	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end

	

	if	((@SessionName IS NOT NULL) OR

		(@InteractiveStatus IS NOT NULL)

		)

	begin

		select	@x_SessionName = SessionName,

				@x_InteractiveStatus = InteractiveStatus

		from msdb.dbo.DTA_input

		where SessionID = @SessionID



		if (@SessionName IS NULL) select @SessionName = @x_SessionName

		if (@InteractiveStatus IS NULL) select @InteractiveStatus = @x_InteractiveStatus



		update msdb.dbo.DTA_input

		set SessionName = @SessionName,

			InteractiveStatus = @InteractiveStatus

		where SessionID = @SessionID

	end		



end

go

grant exec on sp_DTA_update_session to public

go	

/*****************************************************************************

	sp_DTA_get_tuninglog

			@SessionID			- ID of a session requested



	Returns the following rowset:

	RowID,CategoryID,Event,Statement,Frequency,Reason 

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_tuninglog...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_tuninglog' and type = 'P')

	drop procedure sp_DTA_get_tuninglog

go



create procedure sp_DTA_get_tuninglog

	@SessionID int,

	@XML int = 0,

    @LastRowRetrieved int = 0,

    @GetFrequencyForRowIDOnly int = 0



as

begin

	set nocount on

	declare @retval  int

	declare	@LogTableName nvarchar(1280)

	declare	@DefaultTableName nvarchar(128)

	declare @SQLString nvarchar(2048)

	--CategoryID,Event,Statement,Frequency,Reason

	declare @localized_string_CategoryID nvarchar(128)

	declare @localized_string_Event nvarchar(128)

	declare @localized_string_Statement nvarchar(128)

	declare @localized_string_Frequency nvarchar(128)

	declare @localized_string_Reason nvarchar(128)



	set @localized_string_CategoryID = N'"CategoryID"'

	set @localized_string_Event = N'"Event"'

	set @localized_string_Statement = N'"Statement"'

	set @localized_string_Frequency = N'"Frequency"'

	set @localized_string_Reason = N'"Reason"'





	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end



	set @DefaultTableName = '[msdb].[dbo].[DTA_tuninglog]'

	set @LogTableName = ' '

	select top 1 @LogTableName = LogTableName from DTA_input where SessionID = @SessionID



	if (@LogTableName = ' ')

		return (0)





	if @XML = 0

	begin

		if (@GetFrequencyForRowIDOnly = 0)

		begin

			set @SQLString ='select CategoryID as ' + @localized_string_CategoryID + 

							' ,Event as ' +  @localized_string_Event +

							' ,Statement as ' + @localized_string_Statement +

							' ,Frequency as ' + @localized_string_Frequency +

							' ,Reason as ' + @localized_string_Reason +			

							' from '

		end

		else

		begin

			set @SQLString = N' select Frequency from '

		end

		set @SQLString = @SQLString + @LogTableName

		set @SQLString = @SQLString + N' where SessionID = '

		set @SQLString = @SQLString + CONVERT(nvarchar(10),@SessionID)

        set @SQLString = @SQLString + N' and RowID > '

        set @SQLString = @SQLString + CONVERT(nvarchar(10),@LastRowRetrieved)

        set @SQLString = @SQLString + ' order by RowID'



		exec (@SQLString)

	end

	else

	begin

		if @LogTableName = 	@DefaultTableName

		begin

			if (@GetFrequencyForRowIDOnly = 0)

			begin

				select CategoryID,Event,Statement,Frequency,Reason from [msdb].[dbo].[DTA_tuninglog]

				where SessionID = @SessionID and RowID > @LastRowRetrieved

				FOR XML RAW

			end

			else

			begin

				select Frequency from [msdb].[dbo].[DTA_tuninglog]

				where SessionID = @SessionID and RowID > @LastRowRetrieved

				FOR XML RAW

			end

			return(0)

		end



		if (@GetFrequencyForRowIDOnly = 0)

		begin

			set @SQLString = N' select CategoryID,Event,Statement,Frequency,Reason from '

		end

		else

		begin

			set @SQLString = N' select Frequency from '

		end

		set @SQLString =  @SQLString + @LogTableName

		set @SQLString = @SQLString + N' where SessionID = '

		set @SQLString = @SQLString + CONVERT(nvarchar(10),@SessionID)

        set @SQLString = @SQLString + N' and RowID > '

        set @SQLString = @SQLString + CONVERT(nvarchar(10),@LastRowRetrieved)

		set @SQLString = @SQLString + 'FOR XML RAW'



		exec (@SQLString)



	end

end

go

grant exec on sp_DTA_get_tuninglog to public

go

/**************************************************************

	Following stored procs are helpers for different analysis

	reports generated by DTAEngine90.

	Depending on user input DTAEngine90 can generate both XML

	and relational (rowset) reports. The relational report is 

	used by DTAShell to show reports in grids.	XML reports 

	are generated by FOR XML EXPLICIT. The schema is in 

	DTASchema.xsd

	                              

**************************************************************/



/**************************************************************/

/* Index Usage Helper XML		                              */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_index_usage_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_index_usage_helper_xml' and type = 'P')

   drop procedure sp_DTA_index_usage_helper_xml 

go

create procedure sp_DTA_index_usage_helper_xml

	@SessionID		int,

	@IsRecommended	int

as	

begin

select 1            as Tag, 

		NULL          as Parent,

		'' as [IndexUsageReport!1!!ELEMENT],

		case when @IsRecommended = 1 then 'false'

		else 'true' end as [IndexUsageReport!1!Current],

		NULL as [Database!2!DatabaseID!hide],

		NULL  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!NumberOfReferences!ELEMENT],

		NULL as [Index!5!PercentUsage!ELEMENT]

	union all

select 2            as Tag, 

		1          as Parent,

		NULL as [IndexUsageReport!1!!ELEMENT],

		NULL as [IndexUsageReport!1!Current],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!NumberOfReferences!ELEMENT],

		NULL as [Index!5!PercentUsage!ELEMENT]

	from [msdb].[dbo].[DTA_reports_database] as D

	where

	D.SessionID = @SessionID and

	D.DatabaseID in

	(select D.DatabaseID from

			[msdb].[dbo].[DTA_reports_queryindex] as QI,

			[msdb].[dbo].[DTA_reports_index] as I,

			[msdb].[dbo].[DTA_reports_table] as T,

			[msdb].[dbo].[DTA_reports_database] as D

			where

			QI.IndexID = I.IndexID  and

			I.TableID = T.TableID and

			T.DatabaseID = D.DatabaseID and

			D.SessionID = @SessionID and

			QI.IsRecommendedConfiguration = @IsRecommended

			GROUP BY D.DatabaseID)

	union all

select 3            as Tag, 

		2          as Parent,

		NULL as [IndexUsageReport!1!!ELEMENT],

		NULL as [IndexUsageReport!1!Current],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		R.SchemaName  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!NumberOfReferences!ELEMENT],

		NULL as [Index!5!PercentUsage!ELEMENT]

	from [msdb].[dbo].[DTA_reports_database] as D,

	(

		select D.DatabaseID,T.SchemaName from

		[msdb].[dbo].[DTA_reports_queryindex] as QI,

		[msdb].[dbo].[DTA_reports_index] as I,

		[msdb].[dbo].[DTA_reports_table] as T,

		[msdb].[dbo].[DTA_reports_database] as D

		where

		QI.IndexID = I.IndexID  and

		I.TableID = T.TableID and

		T.DatabaseID = D.DatabaseID and

		QI.IsRecommendedConfiguration = @IsRecommended and

		D.SessionID = @SessionID

		GROUP BY D.DatabaseID,T.SchemaName

	) R

	where

	D.SessionID = @SessionID and

	D.DatabaseID = R.DatabaseID

union all



select 4            as Tag, 

		3          as Parent,

		NULL as [IndexUsageReport!1!!ELEMENT],

		NULL as [IndexUsageReport!1!Current],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName as [Database!2!Name!ELEMENT] ,

		R.SchemaName  as [Schema!3!Name!ELEMENT] ,

		R.TableID as [Table!4!TableID!hide],

		T.TableName as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!NumberOfReferences!ELEMENT],

		NULL as [Index!5!PercentUsage!ELEMENT]



	from	 [msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_table] as T,

	(

		select D.DatabaseID,T.SchemaName,T.TableID from

		[msdb].[dbo].[DTA_reports_queryindex] as QI,

		[msdb].[dbo].[DTA_reports_index] as I,

		[msdb].[dbo].[DTA_reports_table] as T,

		[msdb].[dbo].[DTA_reports_database] as D

		where

		QI.IndexID = I.IndexID  and

		I.TableID = T.TableID and

		T.DatabaseID = D.DatabaseID and

		D.SessionID = @SessionID and

		QI.IsRecommendedConfiguration = @IsRecommended

		GROUP BY D.DatabaseID,T.SchemaName, T.TableID

	) R

	where

	D.SessionID = @SessionID and

	D.DatabaseID = R.DatabaseID and

	R.TableID = T.TableID and

	T.DatabaseID = D.DatabaseID



union all

select 5            as Tag, 

		4          as Parent,

		NULL as [IndexUsageReport!1!!ELEMENT],

		NULL as [IndexUsageReport!1!Current],

		D1.DatabaseID as [Database!2!DatabaseID!hide],

		D1.DatabaseName as [Database!2!Name!ELEMENT] ,

		T1.SchemaName  as [Schema!3!Name!ELEMENT] ,

		T1.TableID as [Table!4!TableID!hide],

		T1.TableName as [Table!4!Name!ELEMENT],

		I1.IndexID as [Index!5!IndexID!hide],

		I1.IndexName as [Index!5!Name!ELEMENT],

		R.Count as [Index!5!NumberOfReferences!ELEMENT],

		CAST(R.Usage as decimal(38,2))  as [Index!5!PercentUsage!ELEMENT]

		from

			[msdb].[dbo].[DTA_reports_database] as D1 ,

			[msdb].[dbo].[DTA_reports_index] as I1,

			[msdb].[dbo].[DTA_reports_table] as T1,

			(

				select D.DatabaseID,T.TableID ,

						I.IndexID  ,SUM(Q.Weight) as Count,

						100.0 *  SUM(Q.Weight) / 

						( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

											else 1

											end	

								

									from [msdb].[dbo].[DTA_reports_query] as Q

									where Q.SessionID = @SessionID ))

			as Usage

		from 

			[msdb].[dbo].[DTA_reports_index] as I	

			LEFT OUTER JOIN

			[msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID

			LEFT OUTER JOIN

			[msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID

			JOIN

			[msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID

			JOIN

			[msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID

			and Q.SessionID = QI.SessionID and 

			QI.IsRecommendedConfiguration = @IsRecommended and

			Q.SessionID = @SessionID

			GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R

			where R.DatabaseID = D1.DatabaseID and

			R.TableID = T1.TableID and

			R.IndexID = I1.IndexID and

			D1.SessionID = @SessionID  and

			R.Count > 0

	order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],

			[Index!5!NumberOfReferences!ELEMENT] , [Index!5!IndexID!hide] 



	FOR XML EXPLICIT

end	

go

/**************************************************************/

/* Index Usage Helper Relational                              */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_index_usage_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_index_usage_helper_relational' and type = 'P')

   drop procedure sp_DTA_index_usage_helper_relational 

go

declare @localized_string_databaseName01 nvarchar(128)

declare @localized_string_schemaName01 nvarchar(128)

declare @localized_string_tableName01 nvarchar(128)

declare @localized_string_indexName01 nvarchar(128)

declare @localized_string_numReferences01 nvarchar(128)

declare @localized_string_percentUse01 nvarchar(128)



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



set @localized_string_databaseName01 = N'"Database Name"'

set @localized_string_schemaName01 = N'"Schema Name"'

set @localized_string_tableName01 =N'"Table/View Name"'

set @localized_string_indexName01 =N'"Index Name"'

set @localized_string_numReferences01 = N'"Number of references"'

set @localized_string_percentUse01 =N'"Percent Usage"'



set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName01 +

		' ,T1.SchemaName as ' +  @localized_string_schemaName01 + 

		' ,T1.TableName as ' + @localized_string_tableName01 +

		' ,I1.IndexName as ' +  @localized_string_indexName01 +

		' ,R.Count as '+ @localized_string_numReferences01 +

		' ,CAST(R.Usage as decimal(38,2)) as '+  @localized_string_percentUse01 



set @sql_from =' from 

				DTA_reports_database as D1 ,

				DTA_reports_index as I1,

				DTA_reports_table as T1,

				(

					select D.DatabaseID,T.TableID ,

							I.IndexID  ,SUM(Q.Weight) as Count,

							100.0 *  SUM(Q.Weight) / 

							( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

												else 1

												end	

									

										from [msdb].[dbo].[DTA_reports_query] as Q

										where Q.SessionID = @SessionID ))

				as Usage

		from 

				[msdb].[dbo].[DTA_reports_index] as I	

				LEFT OUTER JOIN

				[msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID

				LEFT OUTER JOIN

				[msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID

				JOIN

				[msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID

				JOIN

				[msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID

				and Q.SessionID = QI.SessionID and 

				QI.IsRecommendedConfiguration = @IsRecommended and

				Q.SessionID = @SessionID

				

				GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R

				where R.DatabaseID = D1.DatabaseID and

				R.TableID = T1.TableID and

				R.IndexID = I1.IndexID and

				D1.SessionID = @SessionID  and

				R.Count > 0

				order by R.Count desc'

		

set @sql ='create procedure sp_DTA_index_usage_helper_relational

	@SessionID		int,

	@IsRecommended	int

	as begin '  +

	@sql_select + @sql_from  +' end'

execute(@sql)	

go

/**************************************************************/

/* Database Access Helper XML		                          */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_database_access_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_database_access_helper_xml' and type = 'P')

   drop procedure sp_DTA_database_access_helper_xml 

go

create procedure sp_DTA_database_access_helper_xml

			@SessionID		int

as

begin

	select 1            as Tag, 

			NULL          as Parent,

			'' as [DatabaseAccessReport!1!!ELEMENT],

			NULL  as [Database!2!Name!ELEMENT] ,

			NULL as [Database!2!NumberOfReferences!ELEMENT],

			NULL as [Database!2!PercentUsage!ELEMENT]

		union all





	select 2 as Tag, 1 as Parent,NULL,D1.DatabaseName  ,

	R.Count  ,

	CAST(R.Usage as decimal(38,2))  from 

					[msdb].[dbo].[DTA_reports_database] as D1 ,

					(

						select D.DatabaseID,SUM(Q.Weight) as Count,

								100.0 *  SUM(Q.Weight) / 

								( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

													else 1

													end	

										

											from [msdb].[dbo].[DTA_reports_query] as Q

											where Q.SessionID = @SessionID ))

					as Usage

			from 

						[msdb].[dbo].[DTA_reports_database] as D

						LEFT OUTER JOIN

						[msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID

						LEFT OUTER JOIN

						[msdb].[dbo].[DTA_reports_query] as Q ON QD.QueryID = Q.QueryID

						and Q.SessionID = QD.SessionID and 

						Q.SessionID = @SessionID		

						GROUP BY D.DatabaseID

					) as R

					where R.DatabaseID = D1.DatabaseID  and

					D1.SessionID = @SessionID and

					R.Count > 0

	order by Tag,[Database!2!NumberOfReferences!ELEMENT] desc

	FOR XML EXPLICIT

end

go

/**************************************************************/

/* Database Access Helper Relational                          */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_database_access_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_database_access_helper_relational' and type = 'P')

   drop procedure sp_DTA_database_access_helper_relational 

go

declare @localized_string_databaseName02 nvarchar(128)

declare @localized_string_numReferences02 nvarchar(128)

declare @localized_string_percentUse02 nvarchar(128)



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



set @localized_string_databaseName02 = N'"Database Name"'

set @localized_string_numReferences02 = N'"Number of references"'

set @localized_string_percentUse02 =N'"Percentage usage"'



set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName02  +

		' ,R.Count as '+ @localized_string_numReferences02 +

		' ,CAST(R.Usage as decimal(38,2)) as '+  @localized_string_percentUse02



set @sql_from =' from 

				[msdb].[dbo].[DTA_reports_database] as D1 ,

				(

					select D.DatabaseID,SUM(Q.Weight) as Count,

							100.0 *  SUM(Q.Weight) / 

							( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

												else 1

												end	

									

										from [msdb].[dbo].[DTA_reports_query] as Q

										where Q.SessionID = @SessionID ))

				as Usage

		from 

					[msdb].[dbo].[DTA_reports_database] as D

					LEFT OUTER JOIN

					[msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID

					LEFT OUTER JOIN

					DTA_reports_query as Q ON QD.QueryID = Q.QueryID

					and Q.SessionID = QD.SessionID and 

					Q.SessionID = @SessionID		

					GROUP BY D.DatabaseID

				) as R

				where R.DatabaseID = D1.DatabaseID  and

				D1.SessionID = @SessionID and

				R.Count > 0

				order by R.Count desc '



		

set @sql =' create procedure sp_DTA_database_access_helper_relational

			@SessionID		int

			as

			begin '  + 	@sql_select + @sql_from + ' end'

execute(@sql)	

go

/**************************************************************/

/* Table Access Helper XML					                  */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_table_access_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_table_access_helper_xml' and type = 'P')

   drop procedure sp_DTA_table_access_helper_xml

go

create procedure sp_DTA_table_access_helper_xml

			@SessionID		int

as

begin

select 1            as Tag, 

		NULL          as Parent,

		'' as [TableAccessReport!1!!ELEMENT],

		NULL as [Database!2!DatabaseID!hide],

		NULL  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Table!4!NumberOfReferences!ELEMENT],

		NULL as [Table!4!PercentUsage!ELEMENT]

	union all

	select 

		2            as Tag, 

		1          as Parent,

		NULL as [TableAccessReport!1!!ELEMENT],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Table!4!NumberOfReferences!ELEMENT],

		NULL as [Table!4!PercentUsage!ELEMENT]

	from [msdb].[dbo].[DTA_reports_database] as D

	where

	D.SessionID = @SessionID and

	D.DatabaseID in

	(select D.DatabaseID from

			[msdb].[dbo].[DTA_reports_querytable] as QT,

			[msdb].[dbo].[DTA_reports_table] as T,

			[msdb].[dbo].[DTA_reports_database] as D

			where

			QT.TableID = T.TableID  and

			T.DatabaseID = D.DatabaseID and

			D.SessionID = @SessionID

			group by D.DatabaseID)

	



union all



	select 3 as Tag, 2 as Parent, 

		NULL as [TableAccessReport!1!!ELEMENT],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		R.SchemaName  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Table!4!NumberOfReferences!ELEMENT],

		NULL as [Table!4!PercentUsage!ELEMENT]



	from [msdb].[dbo].[DTA_reports_database] as D,

	(

		select D.DatabaseID,T.SchemaName from

		[msdb].[dbo].[DTA_reports_querytable] as QT,

		[msdb].[dbo].[DTA_reports_table] as T,

		[msdb].[dbo].[DTA_reports_database] as D

		where

		QT.TableID = T.TableID  and

		T.DatabaseID = D.DatabaseID and

		D.SessionID = @SessionID

		group by D.DatabaseID,T.SchemaName

) R



	where

	D.SessionID = @SessionID and

	D.DatabaseID = R.DatabaseID

	union all

	

	select 4 as Tag, 3 as Parent,



		NULL as [TableAccessReport!1!!ELEMENT],

		D1.DatabaseID as [Database!2!DatabaseID!hide],

		D1.DatabaseName  as [Database!2!Name!ELEMENT] ,

		T1.SchemaName  as [Schema!3!Name!ELEMENT] ,

		T1.TableID as [Table!4!TableID!hide],

		T1.TableName as [Table!4!Name!ELEMENT],

		R.Count as [Table!4!NumberOfReferences!ELEMENT],

		CAST(R.Usage as decimal(38,2)) as [Table!4!PercentUsage!ELEMENT]



from 

				[msdb].[dbo].[DTA_reports_database] as D1 ,

				[msdb].[dbo].[DTA_reports_table] as T1,

				(

					select D.DatabaseID,T.TableID 

							,SUM(Q.Weight) as Count,

							100.0 *  SUM(Q.Weight) / 

							( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

												else 1

												end	

									

										from [msdb].[dbo].[DTA_reports_query] as Q

										where Q.SessionID = @SessionID ))

				as Usage

		from 

				[msdb].[dbo].[DTA_reports_table] as T

				LEFT OUTER JOIN

				[msdb].[dbo].[DTA_reports_querytable] as QT ON QT.TableID = T.TableID

				LEFT OUTER JOIN

				[msdb].[dbo].[DTA_reports_query] as Q ON QT.QueryID = Q.QueryID

				JOIN

				[msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID

				and Q.SessionID = QT.SessionID and 

				Q.SessionID = @SessionID		

				GROUP BY T.TableID,D.DatabaseID) as R

				where R.DatabaseID = D1.DatabaseID and

				R.TableID = T1.TableID and

				D1.SessionID = @SessionID and

				R.Count > 0



	order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Table!4!NumberOfReferences!ELEMENT] 



	FOR XML EXPLICIT

end

go

/**************************************************************/

/* Table Access Helper Relational			                  */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_table_access_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_table_access_helper_relational' and type = 'P')

   drop procedure sp_DTA_table_access_helper_relational 

go

declare @localized_string_databaseName03 nvarchar(128)

declare @localized_string_schemaName02 nvarchar(128)

declare @localized_string_tableName02 nvarchar(128)

declare @localized_string_numReferences03 nvarchar(128)

declare @localized_string_percentUse03 nvarchar(128)



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



set @localized_string_databaseName03 = N'"Database Name"'

set @localized_string_schemaName02 = N'"Schema Name"'

set @localized_string_tableName02 =N'"Table Name"'

set @localized_string_numReferences03 = N'"Number of references"'

set @localized_string_percentUse03 =N'"Percent Usage"'



set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName03 +

		' ,T1.SchemaName as ' +  @localized_string_schemaName02 + 

		' ,T1.TableName as ' + @localized_string_tableName02 +

		' ,R.Count as '+ @localized_string_numReferences03 +

		' ,CAST(R.Usage as decimal(38,2)) as '+  @localized_string_percentUse03





set @sql_from =' from 

				[msdb].[dbo].[DTA_reports_database] as D1 ,

				[msdb].[dbo].[DTA_reports_table] as T1,

				(

					select D.DatabaseID,T.TableID 

							,SUM(Q.Weight) as Count,

							100.0 *  SUM(Q.Weight) / 

							( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

												else 1

												end	

									

										from [msdb].[dbo].[DTA_reports_query] as Q

										where Q.SessionID = @SessionID ))

				as Usage

		from 

				[msdb].[dbo].[DTA_reports_table] as T

				LEFT OUTER JOIN

				[msdb].[dbo].[DTA_reports_querytable] as QT ON QT.TableID = T.TableID

				LEFT OUTER JOIN

				[msdb].[dbo].[DTA_reports_query] as Q ON QT.QueryID = Q.QueryID

				JOIN

				DTA_reports_database as D ON T.DatabaseID = D.DatabaseID

				and Q.SessionID = QT.SessionID and 

				Q.SessionID = @SessionID		

				GROUP BY T.TableID,D.DatabaseID) as R

				where R.DatabaseID = D1.DatabaseID and

				R.TableID = T1.TableID and

				D1.SessionID = @SessionID and

				R.Count > 0

				order by R.Count desc '



		

set @sql =' create procedure sp_DTA_table_access_helper_relational

			@SessionID		int

			as

			begin '  + 	@sql_select + @sql_from + ' end '

execute(@sql)	

go

print ''

print 'Creating procedure sp_DTA_column_access_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_column_access_helper_xml' and type = 'P')

   drop procedure sp_DTA_column_access_helper_xml 

go

create procedure sp_DTA_column_access_helper_xml

			@SessionID		int

as

begin

		select 1            as Tag, 

		NULL          as Parent,

		'' as [ColumnAccessReport!1!!ELEMENT],

		NULL as [Database!2!DatabaseID!hide],

		NULL  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Column!5!ColumnID!hide],

		NULL as [Column!5!Name!ELEMENT],

		NULL as [Column!5!NumberOfReferences!ELEMENT],

		NULL as [Column!5!PercentUsage!ELEMENT]

	union all

	select 2 as Tag, 1 as Parent, NULL,

			D.DatabaseID,D.DatabaseName,

			NULL,NULL,NULL,NULL,NULL,NULL,NULL

	from [msdb].[dbo].[DTA_reports_database] as D

	where

	D.SessionID = @SessionID and

	D.DatabaseID in

	(select D.DatabaseID from

			[msdb].[dbo].[DTA_reports_querycolumn] as QC,

			[msdb].[dbo].[DTA_reports_column] as C,

			[msdb].[dbo].[DTA_reports_table] as T,

			[msdb].[dbo].[DTA_reports_database] as D

			where

			QC.ColumnID = C.ColumnID  and

			C.TableID = T.TableID and

			T.DatabaseID = D.DatabaseID and

			D.SessionID = @SessionID

			group by D.DatabaseID)

	union all

	select 3 as Tag, 2 as Parent, NULL,

			R.DatabaseID,D.DatabaseName,

			R.SchemaName,NULL,NULL,NULL,NULL,NULL,NULL

	from [msdb].[dbo].[DTA_reports_database] as D,

	(

		select D.DatabaseID,T.SchemaName from

		[msdb].[dbo].[DTA_reports_querycolumn] as QC,

		[msdb].[dbo].[DTA_reports_column] as C,

		[msdb].[dbo].[DTA_reports_table] as T,

		[msdb].[dbo].[DTA_reports_database] as D

		where

		QC.ColumnID = C.ColumnID  and

		C.TableID = T.TableID and

		T.DatabaseID = D.DatabaseID and

		D.SessionID = @SessionID

		group by D.DatabaseID,T.SchemaName

) R



	where

	D.SessionID = @SessionID and

	D.DatabaseID = R.DatabaseID

	union all

	select 4 as Tag, 3 as Parent, NULL,

			R.DatabaseID,D.DatabaseName,

			R.SchemaName,R.TableID,T.TableName,NULL,NULL,NULL,NULL

	from [msdb].[dbo].[DTA_reports_database] as D,

	 [msdb].[dbo].[DTA_reports_table] as T,

	(

		select D.DatabaseID,T.SchemaName,T.TableID from

		[msdb].[dbo].[DTA_reports_querycolumn] as QC,

		[msdb].[dbo].[DTA_reports_column] as C,

		[msdb].[dbo].[DTA_reports_table] as T,

		[msdb].[dbo].[DTA_reports_database] as D

		where

		QC.ColumnID = C.ColumnID  and

		C.TableID = T.TableID and

		T.DatabaseID = D.DatabaseID and

		D.SessionID = @SessionID

		group by D.DatabaseID,T.SchemaName,T.TableID

) R



	where

	D.SessionID = @SessionID and

	D.DatabaseID = R.DatabaseID and

	R.TableID = T.TableID and

	T.DatabaseID = D.DatabaseID



	union all

	select 5 as Tag, 4 as Parent, NULL,

			D1.DatabaseID,D1.DatabaseName,

			T1.SchemaName,T1.TableID,T1.TableName,C1.ColumnID,C1.ColumnName,

			R.Count,

			CAST(R.Usage as decimal(38,2))

	from 

	[msdb].[dbo].[DTA_reports_database]as D1 ,

	[msdb].[dbo].[DTA_reports_table] as T1,

	[msdb].[dbo].[DTA_reports_column] as C1,

	(

		select 	D.DatabaseID,T.TableID,C.ColumnID,

			SUM(Q.Weight) as Count,

			100.0 *  SUM(Q.Weight) / 

			( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

					else 1

					end	

					from [msdb].[dbo].[DTA_reports_query] as Q

					where Q.SessionID = @SessionID )) as Usage

		from 

			[msdb].[dbo].[DTA_reports_column] as C

			LEFT OUTER JOIN

			[msdb].[dbo].[DTA_reports_querycolumn] as QC ON QC.ColumnID = C.ColumnID

			LEFT OUTER JOIN

			[msdb].[dbo].[DTA_reports_query] as Q ON QC.QueryID = Q.QueryID

			JOIN

			[msdb].[dbo].[DTA_reports_table] as T ON C.TableID = T.TableID

			JOIN

			[msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID

			and Q.SessionID = QC.SessionID and 

			Q.SessionID = @SessionID		

			GROUP BY C.ColumnID,T.TableID,D.DatabaseID ) as R



			where R.DatabaseID = D1.DatabaseID and

			R.TableID = T1.TableID and

			R.ColumnID = C1.ColumnID and

			D1.SessionID = @SessionID and

			R.Count > 0



	order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Column!5!NumberOfReferences!ELEMENT] , [Column!5!ColumnID!hide] 

	FOR XML EXPLICIT

end

go

print ''

print 'Creating procedure sp_DTA_column_access_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_column_access_helper_relational' and type = 'P')

   drop procedure sp_DTA_column_access_helper_relational 

go   

declare @localized_string_databaseName04 nvarchar(128)

declare @localized_string_schemaName03 nvarchar(128)

declare @localized_string_tableName03 nvarchar(128)

declare @localized_string_columnName nvarchar(128)

declare @localized_string_numReferences04 nvarchar(128)

declare @localized_string_percentUse04 nvarchar(128)



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



set @localized_string_databaseName04 = N'"Database Name"'

set @localized_string_schemaName03 = N'"Schema Name"'

set @localized_string_tableName03 =N'"Table/View Name"'

set @localized_string_columnName =N'"Column Name"'

set @localized_string_numReferences04 = N'"Number of references"'

set @localized_string_percentUse04 =N'"Percent Usage"'



set @sql_select ='select D1.DatabaseName as ' + @localized_string_databaseName04 +

		' ,T1.SchemaName as ' +  @localized_string_schemaName03 + 

		' ,T1.TableName as ' + @localized_string_tableName03 +

		' ,C1.ColumnName as ' +  @localized_string_columnName +

		' ,R.Count as '+ @localized_string_numReferences04 +

		' ,CAST(R.Usage as decimal(38,2)) as '+  @localized_string_percentUse04 



set @sql_from =' from 

				[msdb].[dbo].[DTA_reports_database] as D1 ,

				[msdb].[dbo].[DTA_reports_table] as T1,

				[msdb].[dbo].[DTA_reports_column] as C1,

			

				(

					select D.DatabaseID,T.TableID,C.ColumnID,

							SUM(Q.Weight) as Count,

							100.0 *  SUM(Q.Weight) / 

							( 1.0 * (	select	CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)

												else 1

												end	

									

										from [msdb].[dbo].[DTA_reports_query] as Q

										where Q.SessionID = @SessionID ))

				as Usage

		from 

				[msdb].[dbo].[DTA_reports_column] as C

				LEFT OUTER JOIN

				DTA_reports_querycolumn as QC ON QC.ColumnID = C.ColumnID

				LEFT OUTER JOIN

				DTA_reports_query as Q ON QC.QueryID = Q.QueryID

				JOIN

				DTA_reports_table as T ON C.TableID = T.TableID

				JOIN

				DTA_reports_database as D ON T.DatabaseID = D.DatabaseID

				and Q.SessionID = QC.SessionID and 

				Q.SessionID = @SessionID		

				GROUP BY C.ColumnID,T.TableID,D.DatabaseID) as R

				where R.DatabaseID = D1.DatabaseID and

				R.TableID = T1.TableID and

				R.ColumnID = C1.ColumnID and

				D1.SessionID = @SessionID and

				R.Count > 0

				order by R.Count desc'

		

set @sql =' create procedure sp_DTA_column_access_helper_relational

			@SessionID		int

			as

			begin '  +  @sql_select + @sql_from + ' end '

execute(@sql)	

go

print ''

print 'Creating procedure sp_DTA_query_costrange_helper_xml...'

go

if  exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_costrange_helper_xml' and type = 'P')

   drop procedure sp_DTA_query_costrange_helper_xml 

go

create procedure sp_DTA_query_costrange_helper_xml

			@SessionID	int

			as

begin

	declare @maxCost float

	declare @minCost float

	declare @maxCurrentCost float

	declare @minCurrentCost float

	declare @maxRecommendedCost float

	declare @minRecommendedCost float

	set nocount on

	select @minCurrentCost = min(CurrentCost*Weight),@maxCurrentCost = max(CurrentCost*Weight),

			@minRecommendedCost = min(RecommendedCost*Weight),

			@maxRecommendedCost = max(RecommendedCost*Weight)

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

	where SessionID = @SessionID

	-- Set the bucket boundaries

	if @maxCurrentCost > @maxRecommendedCost

		set @maxCost =  @maxCurrentCost

	else

		set @maxCost =  @maxRecommendedCost



	if @minCurrentCost < @minRecommendedCost

		set @minCost =  @minCurrentCost

	else

		set @minCost =  @minRecommendedCost



	create table #stringmap(OutputString nvarchar(30),num int)

	insert into #stringmap values(N'0% - 10%',0)

	insert into #stringmap values(N'11% - 20%',1)

	insert into #stringmap values(N'21% - 30%',2)

	insert into #stringmap values(N'31% - 40%',3)

	insert into #stringmap values(N'41% - 50%',4)

	insert into #stringmap values(N'51% - 60%',5)

	insert into #stringmap values(N'61% - 70%',6)

	insert into #stringmap values(N'71% - 80%',7)

	insert into #stringmap values(N'81% - 90%',8)

	insert into #stringmap values(N'91% - 100%',9)







	select num,count(*) as cnt

	into #c

	from

	(	select case 

		when (@maxCost=@minCost) then 9 

		when (CurrentCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9

		else convert(int,floor(10*(CurrentCost*Weight-@minCost)/(@maxCost-@minCost)))

		end as num

		from

		[msdb].[dbo].[DTA_reports_query]

		where CurrentCost*Weight >= @minCost and 

		CurrentCost*Weight <= @maxCost

		and SessionID = @SessionID

	) t

	group by num



	select num,count(*) as cnt

	into #r

	from

	(	select case 

		when (@maxCost=@minCost) then 9 

		when (RecommendedCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9

		else convert(int,floor(10*(RecommendedCost*Weight-@minCost)/(@maxCost-@minCost)))

		end as num

		from

		[msdb].[dbo].[DTA_reports_query]

		where RecommendedCost*Weight >= @minCost and 

		RecommendedCost*Weight <= @maxCost

		and SessionID = @SessionID

	) t

	group by num



	select 1            as Tag, 

			NULL          as Parent,

			'' as [StatementCostRangeReport!1!!ELEMENT],

			NULL as [CostRange!2!Percent] ,

			NULL as [CostRange!2!NumStatementsCurrent!ELEMENT],

			NULL as [CostRange!2!NumStatementsRecommended!ELEMENT]

	union all

	select 2            as Tag, 

			1          as Parent,

			NULL as [StatementCostRangeReport!1!!ELEMENT],

			OutputString as [CostRange!2!ELEMENT] ,

			ISNULL(c.cnt,0) as [CostRange!2!NumStatementsCurrent!ELEMENT],

			ISNULL(r.cnt,0) as [CostRange!2!NumStatementsRecommended!ELEMENT]

	from

	(

	select #stringmap.num, #r.cnt

	from #stringmap LEFT OUTER JOIN #r

	ON #stringmap.num = #r.num

	) r,

	(

	select #stringmap.num, #c.cnt

	from #stringmap LEFT OUTER JOIN #c

	ON #stringmap.num = #c.num

	) c,

	#stringmap

	where #stringmap.num = r.num and

	#stringmap.num = c.num



	FOR XML EXPLICIT



	drop table #r

	drop table #c

	drop table #stringmap

end

go

print ''

print 'Creating procedure sp_DTA_query_costrange_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_costrange_helper_relational' and type = 'P')

   drop procedure sp_DTA_query_costrange_helper_relational 

go

declare @localized_string_costRange nvarchar(128)

declare @localized_string_numCurrStmts nvarchar(128)

declare @localized_string_numRecStmts nvarchar(128)



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)





set @localized_string_costRange = N'"Cost Range"'

set @localized_string_numCurrStmts = N'"Number of statements (Current) "'

set @localized_string_numRecStmts = N'"Number of statements (Recommended) "'



set @sql_select =' select ' + @localized_string_costRange + 

							' =OutputString, ' + 

							@localized_string_numCurrStmts +

							' = ISNULL(c.cnt,0) , ' +

							@localized_string_numRecStmts +

							' = ISNULL(r.cnt,0)  ' 

							

set @sql_from		 = '	from

								(

									select #stringmap.num, #r.cnt

									from #stringmap LEFT OUTER JOIN #r

									ON #stringmap.num = #r.num

								) r,

								(

									select #stringmap.num, #c.cnt

									from #stringmap LEFT OUTER JOIN #c

									ON #stringmap.num = #c.num

								) c,

								#stringmap

								where #stringmap.num = r.num and

								#stringmap.num = c.num



								drop table #r

								drop table #c

								drop table #stringmap '

						

set @sql ='create procedure sp_DTA_query_costrange_helper_relational

			@SessionID	int

			as

			begin

			declare @maxCost float

			declare @minCost float

			declare @maxCurrentCost float

			declare @minCurrentCost float

			declare @maxRecommendedCost float

			declare @minRecommendedCost float



			set nocount on

			select @minCurrentCost = min(CurrentCost*Weight),@maxCurrentCost = max(CurrentCost*Weight),

					@minRecommendedCost = min(RecommendedCost*Weight),

					@maxRecommendedCost = max(RecommendedCost*Weight)

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

			where SessionID = @SessionID



			-- Set the bucket boundaries

			if @maxCurrentCost > @maxRecommendedCost

				set @maxCost =  @maxCurrentCost

			else

				set @maxCost =  @maxRecommendedCost



			if @minCurrentCost < @minRecommendedCost

				set @minCost =  @minCurrentCost

			else

				set @minCost =  @minRecommendedCost



			create table #stringmap(OutputString nvarchar(30),num int)

				insert into #stringmap values(N''0% - 10%'',0)

				insert into #stringmap values(N''11% - 20%'',1)

				insert into #stringmap values(N''21% - 30%'',2)

				insert into #stringmap values(N''31% - 40%'',3)

				insert into #stringmap values(N''41% - 50%'',4)

				insert into #stringmap values(N''51% - 60%'',5)

				insert into #stringmap values(N''61% - 70%'',6)

				insert into #stringmap values(N''71% - 80%'',7)

				insert into #stringmap values(N''81% - 90%'',8)

				insert into #stringmap values(N''91% - 100%'',9)







			select num,count(*) as cnt

			into #c

			from

			(	

				select case 

				when (@maxCost=@minCost) then 9 

				when (CurrentCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9

				else convert(int,floor(10*(CurrentCost*Weight-@minCost)/(@maxCost-@minCost)))

				end as num

				from

				[msdb].[dbo].[DTA_reports_query]

				where CurrentCost*Weight >= @minCost and 

				CurrentCost*Weight <= @maxCost

				and SessionID = @SessionID

			) t

			group by num



			select num,count(*) as cnt

			into #r

			from

			(	select case 

				when (@maxCost=@minCost) then 9 

				when (RecommendedCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9

				else convert(int,floor(10*(RecommendedCost*Weight-@minCost)/(@maxCost-@minCost)))

				end as num

				from

				[msdb].[dbo].[DTA_reports_query]

				where RecommendedCost*Weight >= @minCost and 

				RecommendedCost*Weight <= @maxCost

				and SessionID = @SessionID

			) t

			group by num '  + 

			@sql_select + 

			'from

			(

			select #stringmap.num, #r.cnt

			from #stringmap LEFT OUTER JOIN #r

			ON #stringmap.num = #r.num

			) r,

			(

			select #stringmap.num, #c.cnt

			from #stringmap LEFT OUTER JOIN #c

			ON #stringmap.num = #c.num

			) c,

			#stringmap

			where #stringmap.num = r.num and

			#stringmap.num = c.num

			drop table #r

			drop table #c

			drop table #stringmap

			end

			'

exec (@sql)

go



/**************************************************************/

/* Query Cost Report XML                                      */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_query_cost_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_cost_helper_xml' and type = 'P')

   drop procedure sp_DTA_query_cost_helper_xml 

go

create procedure sp_DTA_query_cost_helper_xml 

	@SessionID int 

as 

begin

	select 1            as Tag, 

			NULL          as Parent,

			'' as [StatementCostReport!1!!element],

			NULL as [Statement!2!StatementID!ELEMENT],

			NULL as [Statement!2!StatementString!ELEMENT] ,

			NULL as [Statement!2!PercentImprovement!ELEMENT],

			NULL as [Statement!2!Type!ELEMENT]

	union all



	select 2            as Tag, 

			1          as Parent,

			NULL as [StatementCostReport!1!!element],

			QueryID as [Statement!2!StatementID!ELEMENT],

			StatementString as [Statement!2!StatementString!ELEMENT] ,

			CASE

				WHEN CurrentCost = 0 THEN 0.00

				WHEN CurrentCost <> 0 THEN

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

			end as [Statement!2!PercentImprovement!ELEMENT],

			CASE 

				WHEN StatementType = 0 THEN 'Select'

				WHEN StatementType = 1 THEN 'Update'

				WHEN StatementType = 2 THEN 'Insert'

				WHEN StatementType = 3 THEN 'Delete'

				WHEN StatementType = 4 THEN 'Merge'

			end  as  [Statement!2!Type!ELEMENT]



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

	where SessionID=@SessionID

	order by Tag,[Statement!2!PercentImprovement!ELEMENT] desc

	FOR XML EXPLICIT

end

go   

print ''

print 'Creating procedure sp_DTA_query_cost_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_cost_helper_relational' and type = 'P')

   drop procedure sp_DTA_query_cost_helper_relational 

go

/**************************************************************/

/* Query Cost Report Relational                               */

/**************************************************************/

declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



declare @localized_string_stmtId01 nvarchar(128)

declare @localized_string_stmtString01 nvarchar(128)

declare @localized_string_stmtType01 nvarchar(128)

declare @localized_string_percentImprovement nvarchar(128)



set @localized_string_stmtId01 = N'"Statement Id"'

set @localized_string_stmtString01 = N'"Statement String"'

set @localized_string_percentImprovement = N'"Percent Improvement"'

set @localized_string_stmtType01 = N'"Statement Type"'



set @sql_select ='	select ' + 	 @localized_string_stmtId01 + ' = QueryID, ' +

					@localized_string_stmtString01 + ' = StatementString, ' +

					+ @localized_string_percentImprovement + ' = 	

					CASE

						WHEN CurrentCost = 0 THEN 0.00

						WHEN CurrentCost <> 0 THEN

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

					end , ' +

					@localized_string_stmtType01 + ' = CASE 

							WHEN StatementType = 0 THEN ''Select''

							WHEN StatementType = 1 THEN ''Update''

							WHEN StatementType = 2 THEN ''Insert''

							WHEN StatementType = 3 THEN ''Delete''

							WHEN StatementType = 4 THEN ''Merge''

							end '

set @sql_from = '	from [msdb].[dbo].[DTA_reports_query]

					where SessionID=@SessionID

					order by ' + @localized_string_percentImprovement + ' desc '



set @sql =' create procedure sp_DTA_query_cost_helper_relational

			@SessionID		int

			as

			begin '  + @sql_select + @sql_from + ' end '

execute(@sql)	

go

/**************************************************************/

/*  Event Frequency Report  XML                               */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_event_weight_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_event_weight_helper_xml' and type = 'P')

   drop procedure sp_DTA_event_weight_helper_xml 

go

create procedure sp_DTA_event_weight_helper_xml 

	@SessionID int 

as 

begin

	select 1            as Tag, 

			NULL          as Parent,

			'' as [EventWeightReport!1!!element],

			NULL as [EventDetails!2!EventString!ELEMENT] ,

			NULL as [EventDetails!2!Weight!ELEMENT]

	union all



	select 2         as Tag, 

			1         as Parent,

			NULL as [QueryCost!1!!element],

			EventString as [EventDetails!2!EventString!ELEMENT] ,

			CAST(EventWeight as decimal(38,2)) as [EventDetails!2!Weight!ELEMENT]

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

			where SessionID=@SessionID and EventWeight>0

	order by Tag,[EventDetails!2!Weight!ELEMENT] desc  

	FOR XML EXPLICIT	

end

go



print ''

print 'Creating procedure sp_DTA_event_weight_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_event_weight_helper_relational' and type = 'P')

   drop procedure sp_DTA_event_weight_helper_relational 

go



/**************************************************************/

/* Event Frequency Report Relational                          */

/**************************************************************/



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)

declare @localized_string_eventString nvarchar(128)

declare @localized_string_weight nvarchar(128)



set	@localized_string_eventString =N'"Event String"'

set	@localized_string_weight =N'"Weight"'



set @sql_select = '	select ' +

					@localized_string_eventString + '= EventString, ' 

					+@localized_string_weight +' = CAST(EventWeight as decimal(38,2)) '

set @sql_from = '	from [msdb].[dbo].[DTA_reports_query]

					where SessionID=@SessionID and EventWeight>0

					order by EventWeight desc '

set @sql =' create procedure sp_DTA_event_weight_helper_relational

			@SessionID		int

			as

			begin'  +	@sql_select + @sql_from + ' end '

execute(@sql)	

go

/**************************************************************/

/*  Query Detail Report XML                                   */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_query_detail_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_detail_helper_xml' and type = 'P')

   drop procedure sp_DTA_query_detail_helper_xml 

go

create procedure sp_DTA_query_detail_helper_xml 

	@SessionID int 

as 

begin

	select 1            as Tag, 

			NULL          as Parent,

			'' as [StatementDetailReport!1!!element],

			NULL as [Statement!2!StatementID!ELEMENT] ,

			NULL as [Statement!2!StatementString!ELEMENT] ,

			NULL as [Statement!2!Type!ELEMENT],

			NULL as [Statement!2!CurrentCost!ELEMENT],

			NULL as [Statement!2!RecommendedCost!ELEMENT],

			NULL as [Statement!2!EventString!ELEMENT]

	union all



	select 2            as Tag, 

			1          as Parent,

			NULL as [QueryCost!1!!element],

			QueryID as [Statement!2!StatementID!ELEMENT],

			StatementString as [Statement!2!StatementString!ELEMENT] ,

			CASE 

				WHEN StatementType = 0 THEN 'Select'

				WHEN StatementType = 1 THEN 'Update'

				WHEN StatementType = 2 THEN 'Insert'

				WHEN StatementType = 3 THEN 'Delete'

				WHEN StatementType = 4 THEN 'Merge'

			end  as  [Statement!2!Type!ELEMENT!element],

			CAST(CurrentCost as decimal(38,7)) as	 [Statement!2!CurrentCost!ELEMENT],

			CAST(RecommendedCost as decimal(38,7)) as  [Statement!2!RecommendedCost!ELEMENT],

			EventString as [Statement!2!EventString!ELEMENT]

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

	where SessionID=@SessionID

	FOR XML EXPLICIT

end

go

print ''

print 'Creating procedure sp_DTA_query_detail_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_detail_helper_relational' and type = 'P')

   drop procedure sp_DTA_query_detail_helper_relational 

go



/**************************************************************/

/* Query Detail Report Relational                             */

/**************************************************************/



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)

declare @localized_string_stmtString02 nvarchar(128)

declare @localized_string_stmtType02 nvarchar(128)

declare @localized_string_stmtCostCurr nvarchar(128)

declare @localized_string_stmtCostRec nvarchar(128)

declare @localized_string_stmtID03 nvarchar(128)

declare @localized_string_event01 nvarchar(128)



set	@localized_string_stmtString02 =N'"Statement String"'

set	@localized_string_stmtType02 =N'"Statement Type"'

set	@localized_string_stmtCostCurr =N'"Current Statement Cost"'

set	@localized_string_stmtCostRec =N'"Recommended Statement Cost"'

set	@localized_string_stmtID03 =N'"Statement ID"'

set	@localized_string_event01 =N'"Event String"'





set @sql_select =	' select ' +  @localized_string_stmtID03 + ' =QueryID, ' +

					@localized_string_stmtString02 + ' =StatementString, ' +

					@localized_string_stmtType02 + ' = CASE 

					WHEN StatementType = 0 THEN ''Select''

					WHEN StatementType = 1 THEN ''Update''

					WHEN StatementType = 2 THEN ''Insert''

					WHEN StatementType = 3 THEN ''Delete''

					WHEN StatementType = 4 THEN ''Merge''

					end,' + @localized_string_stmtCostCurr + 

					' =CAST(CurrentCost as decimal(38,7)), ' +

					@localized_string_stmtCostRec + ' =CAST(RecommendedCost as decimal(38,7)), ' +

					@localized_string_event01 + ' =EventString'

					

set @sql_from =		'	from [msdb].[dbo].[DTA_reports_query]

						where SessionID=@SessionID  order by QueryID ASC'

					



set @sql =' create procedure sp_DTA_query_detail_helper_relational

			@SessionID		int

			as

			begin'  + @sql_select + @sql_from + ' end '

execute(@sql)	

go

/**************************************************************/

/*  Query Index Relations Report XML                          */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_query_indexrelations_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_indexrelations_helper_xml' and type = 'P')

   drop procedure sp_DTA_query_indexrelations_helper_xml 

go

create procedure sp_DTA_query_indexrelations_helper_xml 

	@SessionID int ,

	@Recommended	int

as 

begin



	select 1            as Tag, 

			NULL          as Parent,

			'' as [StatementIndexReport!1!!ELEMENT],

			case when @Recommended = 1 then 'false'

			else'true' end

			as [StatementIndexReport!1!Current],	

			NULL as [StatementIndexDetail!2!stmtID!hide],

			NULL  as [StatementIndexDetail!2!StatementString!ELEMENT] ,

			NULL as [Database!3!DatabaseID!hide],

			NULL  as [Database!3!Name!ELEMENT] ,

			NULL  as [Schema!4!Name!ELEMENT] ,

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT],

			NULL as [Index!6!IndexID!hide],

			NULL as [Index!6!Name!ELEMENT]

	union all

	select 2            as Tag, 

			1          as Parent,

			NULL as [StatementIndexReport!1!!ELEMENT],

			NULL as [StatementIndexReport!1!Current],

			Q.QueryID as [StatementIndexDetail!2!stmtID!hide],

			Q.StatementString  as [StatementIndexDetail!2!StatementString!ELEMENT] ,

			NULL as [Database!3!DatabaseID!hide],

			NULL  as [Database!3!Name!ELEMENT] ,

			NULL  as [Schema!4!Name!ELEMENT] ,

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT],

			NULL as [Index!6!IndexID!hide],

			NULL as [Index!6!Name!ELEMENT]

			from [msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_query] Q,

			(	select Q.QueryID,D.DatabaseID

				from

				[msdb].[dbo].[DTA_reports_query] Q, 

				[msdb].[dbo].[DTA_reports_queryindex] QI, 

				[msdb].[dbo].[DTA_reports_index] I, 

				[msdb].[dbo].[DTA_reports_table] T,

				[msdb].[dbo].[DTA_reports_database] D

				where 

				Q.SessionID=QI.SessionID and 

				Q.QueryID=QI.QueryID and 

				QI.IndexID=I.IndexID and 

				I.TableID=T.TableID and 

				T.DatabaseID = D.DatabaseID and

				QI.IsRecommendedConfiguration = @Recommended and

				Q.SessionID=@SessionID

				group by Q.QueryID,D.DatabaseID) as R

				where

				R.QueryID = Q.QueryID and

				R.DatabaseID = D.DatabaseID and

				Q.SessionID = @SessionID

				and R.DatabaseID IS NOT NULL

	union all

	select 3            as Tag, 

			2          as Parent,

			NULL as [StatementIndexReport!1!!ELEMENT],

			NULL as [StatementIndexReport!1!Current],

			Q.QueryID as [StatementIndexDetail!2!stmtID!hide],

			Q.StatementString  as [StatementIndexDetail!2!StatementString!ELEMENT] ,

			D.DatabaseID as [Database!3!DatabaseID!hide],

			D.DatabaseName  as [Database!3!Name!ELEMENT] ,

			NULL  as [Schema!4!Name!ELEMENT] ,

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT],

			NULL as [Index!6!IndexID!hide],

			NULL as [Index!6!Name!ELEMENT]

			from [msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_query] Q,

			(	select Q.QueryID,D.DatabaseID

				from

				[msdb].[dbo].[DTA_reports_query] Q, 

				[msdb].[dbo].[DTA_reports_queryindex] QI, 

				[msdb].[dbo].[DTA_reports_index] I, 

				[msdb].[dbo].[DTA_reports_table] T,

				[msdb].[dbo].[DTA_reports_database] D

				where 

				Q.SessionID=QI.SessionID and 

				Q.QueryID=QI.QueryID and 

				QI.IndexID=I.IndexID and 

				I.TableID=T.TableID and 

				T.DatabaseID = D.DatabaseID and

				QI.IsRecommendedConfiguration = @Recommended and

				Q.SessionID=@SessionID

				group by Q.QueryID,D.DatabaseID) as R

				where

				R.QueryID = Q.QueryID and

				R.DatabaseID = D.DatabaseID and

				Q.SessionID = @SessionID

	union all

	select 4            as Tag, 

			3          as Parent,

			NULL as [StatementIndexReport!1!!ELEMENT],

			NULL as [StatementIndexReport!1!Current],

			Q.QueryID as [StatementIndexDetail!2!stmtID!hide],

			Q.StatementString  as [StatementIndexDetail!2!StatementString!ELEMENT] ,

			D.DatabaseID as [Database!3!DatabaseID!hide],

			D.DatabaseName  as [Database!3!Name!ELEMENT] ,

			R.SchemaName  as [Schema!4!Name!ELEMENT] ,

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT],

			NULL as [Index!6!IndexID!hide],

			NULL as [Index!6!Name!ELEMENT]

			from [msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_query] Q,

			(	select Q.QueryID,D.DatabaseID,T.SchemaName

				from

				[msdb].[dbo].[DTA_reports_query] Q, 

				[msdb].[dbo].[DTA_reports_queryindex] QI, 

				[msdb].[dbo].[DTA_reports_index] I, 

				[msdb].[dbo].[DTA_reports_table] T,

				[msdb].[dbo].[DTA_reports_database] D

				where 

				Q.SessionID=QI.SessionID and 

				Q.QueryID=QI.QueryID and 

				QI.IndexID=I.IndexID and 

				I.TableID=T.TableID and 

				T.DatabaseID = D.DatabaseID and

				QI.IsRecommendedConfiguration = @Recommended and

				Q.SessionID=@SessionID

				group by Q.QueryID,D.DatabaseID,T.SchemaName) as R

				where

				R.QueryID = Q.QueryID and

				R.DatabaseID = D.DatabaseID and

				Q.SessionID = @SessionID



	union all

	select 5            as Tag, 

			4          as Parent,

			NULL as [StatementIndexReport!1!!ELEMENT],

			NULL as [StatementIndexReport!1!Current],

			Q.QueryID as [StatementIndexDetail!2!stmtID!hide],

			Q.StatementString  as [StatementIndexDetail!2!StatementString!ELEMENT] ,

			D.DatabaseID as [Database!3!DatabaseID!hide],

			D.DatabaseName  as [Database!3!Name!ELEMENT] ,

			R.SchemaName  as [Schema!4!Name!ELEMENT] ,

			R.TableID as [Table!5!TableID!hide],

			T.TableName as [Table!5!Name!ELEMENT],

			NULL as [Index!6!IndexID!hide],

			NULL as [Index!6!Name!ELEMENT]

			from [msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_query] Q,

			[msdb].[dbo].[DTA_reports_table] T,

			(	select Q.QueryID,D.DatabaseID,T.SchemaName,T.TableID

				from

				[msdb].[dbo].[DTA_reports_query] Q, 

				[msdb].[dbo].[DTA_reports_queryindex] QI, 

				[msdb].[dbo].[DTA_reports_index] I, 

				[msdb].[dbo].[DTA_reports_table] T,

				[msdb].[dbo].[DTA_reports_database] D

				where 

				Q.SessionID=QI.SessionID and 

				Q.QueryID=QI.QueryID and 

				QI.IndexID=I.IndexID and 

				I.TableID=T.TableID and 

				T.DatabaseID = D.DatabaseID and

				QI.IsRecommendedConfiguration = @Recommended and

				Q.SessionID=@SessionID

				group by Q.QueryID,D.DatabaseID,T.SchemaName,T.TableID) as R

				where

				R.QueryID = Q.QueryID and

				R.DatabaseID = D.DatabaseID and

				Q.SessionID = @SessionID and

				R.TableID = T.TableID

	union all

	select 6            as Tag, 

			5          as Parent,

			NULL as [StatementIndexReport!1!!ELEMENT],

			NULL as [StatementIndexReport!1!Current],

			Q.QueryID as [StatementIndexDetail!2!stmtID!hide],

			Q.StatementString  as [StatementIndexDetail!2!StatementString!ELEMENT] ,

			D.DatabaseID as [Database!3!DatabaseID!hide],

			D.DatabaseName  as [Database!3!Name!ELEMENT] ,

			T.SchemaName  as [Schema!4!Name!ELEMENT] ,

			T.TableID as [Table!5!TableID!hide],

			T.TableName as [Table!5!Name!ELEMENT],

			I.IndexID as [Index!6!IndexID!hide],

			I.IndexName as [Index!6!Name!ELEMENT]

			from 		

				[msdb].[dbo].[DTA_reports_query] Q, 

				[msdb].[dbo].[DTA_reports_queryindex] QI, 

				[msdb].[dbo].[DTA_reports_index] I, 

				[msdb].[dbo].[DTA_reports_table] T,

				[msdb].[dbo].[DTA_reports_database] D

				where 

				Q.SessionID=QI.SessionID and 

				Q.QueryID=QI.QueryID and 

				QI.IndexID=I.IndexID and 

				I.TableID=T.TableID and 

				T.DatabaseID = D.DatabaseID and

				QI.IsRecommendedConfiguration = @Recommended and

				Q.SessionID=@SessionID

	order by [StatementIndexDetail!2!stmtID!hide],[Database!3!DatabaseID!hide],

			[Schema!4!Name!ELEMENT],[Table!5!TableID!hide],[Index!6!IndexID!hide]

	FOR XML EXPLICIT

end

go

print ''

print 'Creating procedure sp_DTA_query_indexrelations_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_query_indexrelations_helper_relational' and type = 'P')

   drop procedure sp_DTA_query_indexrelations_helper_relational 

go



/**************************************************************/

/* Query Index Relations Report Relational                     */

/**************************************************************/



declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



declare @localized_string_stmtId02 nvarchar(128)

declare @localized_string_stmtString03 nvarchar(128)

declare @localized_string_databaseName05 nvarchar(128)

declare @localized_string_schemaName04 nvarchar(128)

declare @localized_string_objectName01 nvarchar(128)

declare @localized_string_indexName02 nvarchar(128)

declare @localized_string_clustered01 nvarchar(128)

declare @localized_string_unique01 nvarchar(128)



set @localized_string_stmtId02 = N'"Statement Id"'

set @localized_string_stmtString03 = N'"Statement String"'

set @localized_string_databaseName05 = N'"Database Name"'

set @localized_string_schemaName04 = N'"Schema Name"'

set @localized_string_objectName01 = N'"Table/View Name"'

set @localized_string_indexName02 = N'"Index Name"'

set @localized_string_clustered01 = N'"Clustered"'

set @localized_string_unique01 = N'"Unique"'



set @sql_select =	'	select ' + @localized_string_stmtId02 + ' =Q.QueryID, ' +

						@localized_string_stmtString03 + ' =Q.StatementString,' +

						@localized_string_databaseName05  + ' =D.DatabaseName, ' +

						@localized_string_schemaName04 + ' =T.SchemaName, ' +

						@localized_string_objectName01 +' =T.TableName, ' +

						@localized_string_indexName02  + ' =I.IndexName ' 



set @sql_from =		'	 from 

						[msdb].[dbo].[DTA_reports_query] Q, 

						[msdb].[dbo].[DTA_reports_queryindex] QI, 

						[msdb].[dbo].[DTA_reports_index] I, 

						[msdb].[dbo].[DTA_reports_table] T,

						[msdb].[dbo].[DTA_reports_database] D

						where 

						Q.SessionID=QI.SessionID and 

						Q.QueryID=QI.QueryID and 

						QI.IndexID=I.IndexID and 

						I.TableID=T.TableID and 

						T.DatabaseID = D.DatabaseID and

						QI.IsRecommendedConfiguration = @Recommended and

						Q.SessionID=@SessionID order by Q.QueryID '

					



set @sql =				'	create procedure sp_DTA_query_indexrelations_helper_relational

							@SessionID		int,

							@Recommended	int

							as

							begin '  + @sql_select + @sql_from + ' end '

execute(@sql)	

go

/**************************************************************/

/* Index Detail Report For Current XML                        */

/**************************************************************/

go

print ''

print 'Creating procedure sp_DTA_index_current_detail_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_index_current_detail_helper_xml' and type = 'P')

   drop procedure sp_DTA_index_current_detail_helper_xml

go

create procedure sp_DTA_index_current_detail_helper_xml

						@SessionID		int

as						

begin

select 1            as Tag, 

		NULL          as Parent,

		'' as [IndexDetailReport!1!!ELEMENT],

		'true' as [IndexDetailReport!1!Current],

		NULL as [Database!2!DatabaseID!hide],

		NULL  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]

	union all

	select 2            as Tag, 

		1          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]

	from [msdb].[dbo].[DTA_reports_database] as D

	where

	D.SessionID = @SessionID and

	D.DatabaseID in

	(select D.DatabaseID from

			[msdb].[dbo].[DTA_reports_table] as T,

			[msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_index] as I

			where

			D.SessionID = @SessionID and

			D.DatabaseID = T.DatabaseID and

			T.TableID = I.TableID and

			I.IsExisting = 1

			group by D.DatabaseID)

union all

	select 3            as Tag, 

		2          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		R.SchemaName  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],	

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]

		from [msdb].[dbo].[DTA_reports_database] as D,

		(

			select D.DatabaseID,T.SchemaName 

			from

			[msdb].[dbo].[DTA_reports_table] as T,

			[msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_index] as I

			where

			D.SessionID = @SessionID and

			D.DatabaseID = T.DatabaseID and

			T.TableID = I.TableID and

			I.IsExisting = 1

			group by D.DatabaseID,T.SchemaName

		) R

	where

	D.SessionID = @SessionID and

	D.DatabaseID = R.DatabaseID

union all

	select 4            as Tag, 

		3          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		R.SchemaName  as [Schema!3!Name!ELEMENT] ,

		R.TableID as [Table!4!TableID!hide],

		T.TableName  as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],	

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]

		from [msdb].[dbo].[DTA_reports_database] as D,

		 [msdb].[dbo].[DTA_reports_table] as T,

		(

			select D.DatabaseID,T.SchemaName,T.TableID

			from

				[msdb].[dbo].[DTA_reports_table] as T,

				[msdb].[dbo].[DTA_reports_database] as D,

				[msdb].[dbo].[DTA_reports_index] as I

			where

				D.SessionID = @SessionID and

				D.DatabaseID = T.DatabaseID and

				T.TableID = I.TableID and

				I.IsExisting = 1

			group by D.DatabaseID,T.SchemaName,T.TableID

		) R

		where

		D.SessionID = @SessionID and

		D.DatabaseID = R.DatabaseID and

		R.TableID = T.TableID and

		T.DatabaseID = D.DatabaseID

union all



	select 5            as Tag, 

		4          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		T.SchemaName  as [Schema!3!Name!ELEMENT] ,

		T.TableID as [Table!4!TableID!hide],

		T.TableName  as [Table!4!Name!ELEMENT],

		I.IndexID as [Index!5!IndexID!hide],

		I.IndexName  as [Index!5!Name!ELEMENT],

		CASE

			WHEN I.IsClustered = 1 THEN 'true'	

			WHEN I.IsClustered = 0 THEN 'false'

		end

		as [Index!5!Clustered],

		CASE

			WHEN I.IsUnique = 1 THEN 'true'		

			WHEN I.IsUnique = 0 THEN 'false'

		end

		as [Index!5!Unique],	

		CASE

			WHEN I.IsHeap = 1 THEN 'true'		

			WHEN I.IsHeap = 0 THEN 'false'

		end

		as [Index!5!Heap],

		CASE

			WHEN I.IsFiltered = 1 THEN 'true'		

			WHEN I.IsFiltered = 0 THEN 'false'

		end

		as [Index!5!IsFiltered],				

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

		I.NumRows as [Index!5!NumberOfRows],

		I.FilterDefinition as [Index!5!FilterDefinition]

		from

		[msdb].[dbo].[DTA_reports_database]  D,

		[msdb].[dbo].[DTA_reports_table] T,

		[msdb].[dbo].[DTA_reports_index] as I

		where

		D.SessionID = @SessionID and

		D.DatabaseID = T.DatabaseID and

		T.TableID = I.TableID and

		I.IsExisting = 1

		order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Index!5!IndexID!hide] 

	FOR XML EXPLICIT



end						

go

/**************************************************************/

/* Index Detail Report For Recommended XML                    */

/**************************************************************/

go

print ''

print 'Creating procedure sp_DTA_index_recommended_detail_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_index_recommended_detail_helper_xml' and type = 'P')

   drop procedure sp_DTA_index_recommended_detail_helper_xml

go

create procedure sp_DTA_index_recommended_detail_helper_xml

						@SessionID		int

as						

begin

select 1            as Tag, 

		NULL          as Parent,

		'' as [IndexDetailReport!1!!ELEMENT],

		'false' as [IndexDetailReport!1!Current],

		NULL as [Database!2!DatabaseID!hide],

		NULL  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]		

	union all

	select 2            as Tag, 

		1          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		NULL  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],		

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]

	from [msdb].[dbo].[DTA_reports_database] as D

	where

	D.SessionID = @SessionID and

	D.DatabaseID in

	(select D.DatabaseID from

			[msdb].[dbo].[DTA_reports_table] as T,

			[msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_index] as I

			where

			D.SessionID = @SessionID and

			D.DatabaseID = T.DatabaseID and

			T.TableID = I.TableID and

			IsRecommended = 1

			group by D.DatabaseID)

union all

	select 3            as Tag, 

		2          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		R.SchemaName  as [Schema!3!Name!ELEMENT] ,

		NULL as [Table!4!TableID!hide],

		NULL as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],		

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]

		from [msdb].[dbo].[DTA_reports_database] as D,

		(

			select D.DatabaseID,T.SchemaName 

			from

			[msdb].[dbo].[DTA_reports_table] as T,

			[msdb].[dbo].[DTA_reports_database] as D,

			[msdb].[dbo].[DTA_reports_index] as I

			where

			D.SessionID = @SessionID and

			D.DatabaseID = T.DatabaseID and

			T.TableID = I.TableID and

			IsRecommended = 1

			group by D.DatabaseID,T.SchemaName

		) R

	where

	D.SessionID = @SessionID and

	D.DatabaseID = R.DatabaseID

union all

	select 4            as Tag, 

		3          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		R.SchemaName  as [Schema!3!Name!ELEMENT] ,

		R.TableID as [Table!4!TableID!hide],

		T.TableName  as [Table!4!Name!ELEMENT],

		NULL as [Index!5!IndexID!hide],

		NULL as [Index!5!Name!ELEMENT],

		NULL as [Index!5!Clustered],

		NULL as [Index!5!Unique],

		NULL as [Index!5!Heap],

		NULL as [Index!5!FilteredIndex],		

		NULL as [Index!5!IndexSizeInMB],

		NULL as [Index!5!NumberOfRows],

		NULL as [Index!5!FilterDefinition]

		from [msdb].[dbo].[DTA_reports_database] as D,

		 [msdb].[dbo].[DTA_reports_table] as T,

		(

			select D.DatabaseID,T.SchemaName,T.TableID

			from

				[msdb].[dbo].[DTA_reports_table] as T,

				[msdb].[dbo].[DTA_reports_database] as D,

				[msdb].[dbo].[DTA_reports_index] as I

			where

				D.SessionID = @SessionID and

				D.DatabaseID = T.DatabaseID and

				T.TableID = I.TableID and

				I.IsRecommended = 1

			group by D.DatabaseID,T.SchemaName,T.TableID

		) R

		where

		D.SessionID = @SessionID and

		D.DatabaseID = R.DatabaseID and

		R.TableID = T.TableID and

		T.DatabaseID = D.DatabaseID

union all



	select 5            as Tag, 

		4          as Parent,

		NULL as [IndexDetailReport!1!!ELEMENT],

		NULL as [IndexDetailReport!1!Recommended],

		D.DatabaseID as [Database!2!DatabaseID!hide],

		D.DatabaseName  as [Database!2!Name!ELEMENT] ,

		T.SchemaName  as [Schema!3!Name!ELEMENT] ,

		T.TableID as [Table!4!TableID!hide],

		T.TableName  as [Table!4!Name!ELEMENT],

		I.IndexID as [Index!5!IndexID!hide],

		I.IndexName  as [Index!5!Name!ELEMENT],

		CASE

			WHEN I.IsClustered = 1 THEN 'true'	

			WHEN I.IsClustered = 0 THEN 'false'

		end

		as [Index!5!Clustered],

		CASE

			WHEN I.IsUnique = 1 THEN 'true'		

			WHEN I.IsUnique = 0 THEN 'false'

		end

		as [Index!5!Unique],	

		CASE

			WHEN I.IsHeap = 1 THEN 'true'		

			WHEN I.IsHeap = 0 THEN 'false'

		end

		as [Index!5!Heap],

		CASE

			WHEN I.IsFiltered = 1 THEN 'true'		

			WHEN I.IsFiltered = 0 THEN 'false'

		end

		as [Index!5!FilteredIndex],				

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

		I.NumRows as [Index!5!NumberOfRows],

		I.FilterDefinition as [Index!5!FilterDefinition]

		from

		[msdb].[dbo].[DTA_reports_database]  D,

		[msdb].[dbo].[DTA_reports_table] T,

		[msdb].[dbo].[DTA_reports_index] as I

		where

		D.SessionID = @SessionID and

		D.DatabaseID = T.DatabaseID and

		T.TableID = I.TableID and

		I.IsRecommended = 1

		order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Index!5!IndexID!hide] 

	FOR XML EXPLICIT



end						

go

/**************************************************************/

/* Index Detail Report For Current Relational                 */

/**************************************************************/

go

print ''

print 'Creating procedure sp_DTA_index_detail_current_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_index_detail_current_helper_relational' and type = 'P')

   drop procedure sp_DTA_index_detail_current_helper_relational 

go

declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



declare @localized_string_databaseName06 nvarchar(128)

declare @localized_string_schemaName05 nvarchar(128)

declare @localized_string_objectName02 nvarchar(128)

declare @localized_string_indexName03 nvarchar(128)

declare @localized_string_clustered02 nvarchar(128)

declare @localized_string_unique02 nvarchar(128)

declare @localized_string_storage nvarchar(128)

declare @localized_string_rows nvarchar(128)

declare @localized_string_heap nvarchar(128)

declare @localized_string_filter nvarchar(128)

declare @localized_string_filtdef nvarchar(128)



set @localized_string_databaseName06 = N'"Database Name"'

set @localized_string_schemaName05 = N'"Schema Name"'

set @localized_string_objectName02 = N'"Table/View Name"'

set @localized_string_indexName03 = N'"Index Name"'

set @localized_string_clustered02 = N'"Clustered"'

set @localized_string_unique02 = N'"Unique"'

set @localized_string_heap = N'"Heap"'

set @localized_string_storage = N'"Index Size (MB)"'

set @localized_string_rows = N'"Number of Rows"'

set @localized_string_filter = N'"Filtered"'

set @localized_string_filtdef = N'"Filter Definition"'





set @sql_select =	'	select ' + @localized_string_databaseName06 + 

					' = D.DatabaseName, ' + @localized_string_schemaName05  +

					' = T.SchemaName, ' +  @localized_string_objectName02 + 

					' = T.TableName, ' +  @localized_string_indexName03 + 

					' = I.IndexName, ' + @localized_string_clustered02  +

					' =	CASE

						WHEN I.IsClustered = 1 THEN ''Yes''	

						WHEN I.IsClustered = 0 THEN ''No''

						end, ' +  @localized_string_unique02 +

					' =	CASE

						WHEN I.IsUnique = 1 THEN ''Yes''		

						WHEN I.IsUnique = 0 THEN ''No''

						end	, ' +  @localized_string_heap + 

					' =	CASE

						WHEN I.IsHeap = 1 THEN ''Yes''		

						WHEN I.IsHeap = 0 THEN ''No''

						end	, '	+ @localized_string_filter +

					' =	CASE

						WHEN I.IsFiltered = 1 THEN ''Yes''		

						WHEN I.IsFiltered = 0 THEN ''No''

						end	, '						

						+ @localized_string_storage  + 

						'= CAST(I.Storage as decimal(38,2)) , '  +  @localized_string_rows +

						'= NumRows ,  ' + @localized_string_filtdef + '= I.FilterDefinition ' 

						



set @sql_from = '	from 

					DTA_reports_database  D,

					DTA_reports_table T,

					DTA_reports_index as I

					where

					D.SessionID = @SessionID and

					D.DatabaseID = T.DatabaseID and

					T.TableID = I.TableID and

					I.IsExisting = 1 '



set @sql =			'	create procedure sp_DTA_index_detail_current_helper_relational

						@SessionID		int

						as

						begin'  +  @sql_select + @sql_from + ' end '

execute(@sql)	



go

/**************************************************************/

/* Index Detail Report For Recommended Relational             */

/**************************************************************/

go

print ''

print 'Creating procedure sp_DTA_index_detail_recommended_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_index_detail_recommended_helper_relational' and type = 'P')

   drop procedure sp_DTA_index_detail_recommended_helper_relational 

go

declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



declare @localized_string_databaseName06 nvarchar(128)

declare @localized_string_schemaName05 nvarchar(128)

declare @localized_string_objectName02 nvarchar(128)

declare @localized_string_indexName03 nvarchar(128)

declare @localized_string_clustered02 nvarchar(128)

declare @localized_string_unique02 nvarchar(128)

declare @localized_string_storage nvarchar(128)

declare @localized_string_rows nvarchar(128)

declare @localized_string_heap nvarchar(128)

declare @localized_string_filter nvarchar(128)

declare @localized_string_filtdef nvarchar(128)



set @localized_string_databaseName06 = N'"Database Name"'

set @localized_string_schemaName05 = N'"Schema Name"'

set @localized_string_objectName02 = N'"Table/View Name"'

set @localized_string_indexName03 = N'"Index Name"'

set @localized_string_clustered02 = N'"Clustered"'

set @localized_string_unique02 = N'"Unique"'

set @localized_string_heap = N'"Heap"'

set @localized_string_storage = N'"Index Size (MB)"'

set @localized_string_rows = N'"Number of Rows"'

set @localized_string_filter = N'"Filtered"'

set @localized_string_filtdef = N'"Filter Definition"'



set @sql_select =	'	select ' + @localized_string_databaseName06 + 

					' = D.DatabaseName, ' + @localized_string_schemaName05  +

					' = T.SchemaName, ' +  @localized_string_objectName02 + 

					' = T.TableName, ' +  @localized_string_indexName03 + 

					' = I.IndexName, ' + @localized_string_clustered02  +

					' =	CASE

						WHEN I.IsClustered = 1 THEN ''Yes''	

						WHEN I.IsClustered = 0 THEN ''No''

						end, ' +  @localized_string_unique02 +

					' =	CASE

						WHEN I.IsUnique = 1 THEN ''Yes''		

						WHEN I.IsUnique = 0 THEN ''No''

						end	, ' +  @localized_string_heap + 

					' =	CASE

						WHEN I.IsHeap = 1 THEN ''Yes''		

						WHEN I.IsHeap = 0 THEN ''No''

						end	, '	+ @localized_string_filter +

					' =	CASE

						WHEN I.IsFiltered = 1 THEN ''Yes''		

						WHEN I.IsFiltered = 0 THEN ''No''

						end	, '						

						+ 						

						 @localized_string_storage  + 

						'= CAST(I.RecommendedStorage as decimal(38,2)) , '  +  @localized_string_rows +

						'= NumRows , '+  @localized_string_filtdef + '= I.FilterDefinition ' 

						



set @sql_from = '	from 

					DTA_reports_database  D,

					DTA_reports_table T,

					DTA_reports_index as I

					where

					D.SessionID = @SessionID and

					D.DatabaseID = T.DatabaseID and

					T.TableID = I.TableID and

					I.IsRecommended = 1 '



set @sql =			'	create procedure sp_DTA_index_detail_recommended_helper_relational

						@SessionID		int

						as

						begin'  +  @sql_select + @sql_from + ' end '

execute(@sql)	



go

/**************************************************************/

/* View Table Relations XML			                          */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_view_table_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_view_table_helper_xml' and type = 'P')

   drop procedure sp_DTA_view_table_helper_xml

go 

create procedure sp_DTA_view_table_helper_xml

						@SessionID		int

as

begin

	select 1            as Tag, 

			NULL          as Parent,

			'' as [ViewTableReport!1!!ELEMENT],

			NULL as [Database!2!DatabaseID!hide],

			NULL  as [Database!2!Name!ELEMENT] ,

			NULL  as [Schema!3!Name!ELEMENT] ,

			NULL as [View!4!ViewID!hide],

			NULL as [View!4!Name!ELEMENT],

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT]

	union all

	select 2            as Tag, 

			1          as Parent,

			NULL as [ViewTableReport!1!!ELEMENT],

			D.DatabaseID as [Database!2!DatabaseID!hide],

			D.DatabaseName  as [Database!2!Name!ELEMENT] ,

			NULL  as [Schema!3!Name!ELEMENT] ,

			NULL as [View!4!ViewID!hide],

			NULL as [View!4!Name!ELEMENT],

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT]

			from	[msdb].[dbo].[DTA_reports_database] as D

			where

				D.SessionID = @SessionID and

				D.DatabaseID in 

				(

					select D.DatabaseID

					from [msdb].[dbo].[DTA_reports_table] as T,

						[msdb].[dbo].[DTA_reports_database] as D

						where

						T.IsView = 1 and

						T.DatabaseID = D.DatabaseID and

						D.SessionID = @SessionID

						GROUP BY D.DatabaseID)

	union all

	select 3            as Tag, 

			2          as Parent,

			NULL as [ViewTableReport!1!!ELEMENT],

			D.DatabaseID as [Database!2!DatabaseID!hide],

			D.DatabaseName  as [Database!2!Name!ELEMENT] ,

			R.SchemaName  as [Schema!3!Name!ELEMENT] ,

			NULL as [View!4!ViewID!hide],

			NULL as [View!4!Name!ELEMENT],

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT]

			from	[msdb].[dbo].[DTA_reports_database] as D,

					(select D.DatabaseID,T.SchemaName

							from [msdb].[dbo].[DTA_reports_table] as T,

							[msdb].[dbo].[DTA_reports_database] as D

							where

							T.IsView = 1 and

							T.DatabaseID = D.DatabaseID and

							D.SessionID = @SessionID

							GROUP BY D.DatabaseID,T.SchemaName

					) R

			where

				R.DatabaseID = D.DatabaseID and

				D.SessionID = @SessionID



	union all

	select 4            as Tag, 

			3          as Parent,

			NULL as [ViewTableReport!1!!ELEMENT],

			D.DatabaseID as [Database!2!DatabaseID!hide],

			D.DatabaseName  as [Database!2!Name!ELEMENT] ,

			R.SchemaName  as [Schema!3!Name!ELEMENT] ,

			T.TableID as [View!4!ViewID!hide],

			T.TableName as [View!4!Name!ELEMENT],

			NULL as [Table!5!TableID!hide],

			NULL as [Table!5!Name!ELEMENT]

			from	[msdb].[dbo].[DTA_reports_table] as T,

					[msdb].[dbo].[DTA_reports_database] as D,

					(select D.DatabaseID,T.SchemaName,T.TableID

							from [msdb].[dbo].[DTA_reports_table] as T,

							[msdb].[dbo].[DTA_reports_database] as D

							where

							T.IsView = 1 and

							T.DatabaseID = D.DatabaseID and

							D.SessionID = @SessionID

							GROUP BY D.DatabaseID,T.SchemaName,T.TableID

					) R

			where

				R.DatabaseID = D.DatabaseID and

				T.TableID = R.TableID and

				D.SessionID = @SessionID

	union all

	select 5            as Tag, 

			4          as Parent,

			NULL as [ViewTableReport!1!!ELEMENT],

			D.DatabaseID as [Database!2!DatabaseID!hide],

			D.DatabaseName  as [Database!2!Name!ELEMENT] ,

			T2.SchemaName  as [Schema!3!Name!ELEMENT] ,

			T2.TableID as [View!4!ViewID!hide],

			T2.TableName as [View!4!Name!ELEMENT],

			T1.TableID as [Table!5!TableID!hide],

			T1.TableName as [Table!5!Name!ELEMENT]

			from

			[msdb].[dbo].[DTA_reports_database] D, 

			[msdb].[dbo].[DTA_reports_tableview] TV, 

			[msdb].[dbo].[DTA_reports_table] T1,

			[msdb].[dbo].[DTA_reports_table] T2

		where 

			D.DatabaseID=T1.DatabaseID and 

			D.DatabaseID=T2.DatabaseID and

			T1.TableID=TV.TableID and 

			T2.TableID=TV.ViewID and

			D.SessionID = @SessionID



	order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[View!4!ViewID!hide],[Table!5!TableID!hide]

	FOR XML EXPLICIT

end						

go

/**************************************************************/

/* View Table Relations Relational                            */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_view_table_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_view_table_helper_relational' and type = 'P')

   drop procedure sp_DTA_view_table_helper_relational 

go

declare	@sql nvarchar(4000)

declare	@sql_select_relational nvarchar(4000)

declare	@sql_select_xml nvarchar(4000)

declare	@sql_from nvarchar(4000)



declare @localized_string_viewID02 nvarchar(128)

declare @localized_string_databaseName07 nvarchar(128)

declare @localized_string_schemaName06 nvarchar(128)

declare @localized_string_viewName nvarchar(128)

declare @localized_string_tableName04 nvarchar(128)





set @localized_string_viewID02 =N'"View Id"'

set @localized_string_databaseName07 = N'"Database Name"'

set @localized_string_schemaName06 = N'"Schema Name"'

set @localized_string_viewName = N'"View Name"'

set @localized_string_tableName04 = N'"Table Name"'



set @sql_select_relational =	' select ' + @localized_string_viewID02 + 

					' =T2.TableID, ' + @localized_string_databaseName07 +

					' =D.DatabaseName, ' + @localized_string_schemaName06 +

					' =T2.SchemaName, ' + @localized_string_viewName +

					' =T2.TableName, ' +  @localized_string_databaseName07 +

					' =D.DatabaseName, ' + @localized_string_schemaName06 + 

					' =T1.SchemaName, ' +  @localized_string_tableName04  + 

					' =T1.TableName ' 

					

set @sql_select_xml	= ' select T2.TableID,D.DatabaseName, T2.SchemaName,

					 T2.TableName as ''View'',T1.TableName as ''Table'''



set @sql_from = '	from 

					[msdb].[dbo].[DTA_reports_database] D, 

					[msdb].[dbo].[DTA_reports_tableview] TV, 

					[msdb].[dbo].[DTA_reports_table] T1,

					[msdb].[dbo].[DTA_reports_table] T2

					where 

						D.DatabaseID=T1.DatabaseID and 

						D.DatabaseID=T2.DatabaseID and

						T1.TableID=TV.TableID and 

						T2.TableID=TV.ViewID and

						D.SessionID=@SessionID

						order by TV.ViewID '

					

set @sql =			'	create procedure sp_DTA_view_table_helper_relational

						@SessionID		int

						as

						begin '  +  @sql_select_relational + @sql_from + ' end '

execute(@sql)	

					

go

/**************************************************************/

/* Workload Analysis Report XML                                */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_wkld_analysis_helper_xml...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_wkld_analysis_helper_xml' and type = 'P')

   drop procedure sp_DTA_wkld_analysis_helper_xml 

go

create procedure sp_DTA_wkld_analysis_helper_xml

						@SessionID		int

as

begin

	select 1            as Tag, 

			NULL          as Parent,

			'' as [WorkloadAnalysisReport!1!!ELEMENT],

			NULL  as [Statements!2!Type!ELEMENT] ,

			NULL as [Statements!2!NumberOfStatements!ELEMENT],

			NULL as [Statements!2!CostDecreased!ELEMENT],

			NULL as [Statements!2!CostIncreased!ELEMENT],

			NULL as [Statements!2!CostSame!ELEMENT]

		union all

	select 2            as Tag, 

			1          as Parent,

			NULL as [WorkloadAnalysis!1!!ELEMENT],

			CASE 

							WHEN StatementType = 0 THEN 'Select'

							WHEN StatementType = 1 THEN 'Update'

							WHEN StatementType = 2 THEN 'Insert'

							WHEN StatementType = 3 THEN 'Delete'

							WHEN StatementType = 4 THEN 'Merge'

			end  as [Statements!2!Type!ELEMENT] ,

			COUNT(QueryID) as [Statements!2!NumberOfStatements!ELEMENT],

			SUM(CASE WHEN RecommendedCost<CurrentCost THEN 1 else 0 end) as [Statements!2!CostDecreased!ELEMENT],

			SUM(CASE WHEN RecommendedCost>CurrentCost THEN 1 else 0 end) as [Statements!2!CostIncreased!ELEMENT],

			SUM(CASE WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) as [Statements!2!CostSame!ELEMENT]

			from 

			[msdb].[dbo].[DTA_reports_query]

			where 

			SessionID=@SessionID

			group by StatementType

			FOR XML EXPLICIT

end						

go						

/**************************************************************/

/* Workload Analysis Report                                   */

/**************************************************************/

print ''

print 'Creating procedure sp_DTA_wkld_analysis_helper_relational...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_wkld_analysis_helper_relational' and type = 'P')

   drop procedure sp_DTA_wkld_analysis_helper_relational 

go

declare	@sql nvarchar(4000)

declare	@sql_select nvarchar(4000)

declare	@sql_from nvarchar(4000)



declare @localized_string_stmtType03 nvarchar(128)

declare @localized_string_numStmts02 nvarchar(128)

declare @localized_string_costDecreased nvarchar(128)

declare @localized_string_costIncreased nvarchar(128)

declare @localized_string_costSame nvarchar(128)



set @localized_string_stmtType03 = N'"Statement Type"'

set @localized_string_numStmts02 = N'"Number Of Statements"'

set @localized_string_costDecreased = N'"Cost Decreased"'

set @localized_string_costIncreased = N'"Cost Increased"'

set @localized_string_costSame = N'"No Change"'





set @sql_select =	'	select ' +  @localized_string_stmtType03 +

						' = CASE 

						WHEN StatementType = 0 THEN ''Select''

						WHEN StatementType = 1 THEN ''Update''

						WHEN StatementType = 2 THEN ''Insert''

						WHEN StatementType = 3 THEN ''Delete''

						WHEN StatementType = 4 THEN ''Merge''

						end, ' + 

						@localized_string_numStmts02 +  ' =COUNT(QueryID), ' +

						@localized_string_costDecreased +  ' =SUM(CASE

															WHEN RecommendedCost<CurrentCost THEN 1 else 0 end), ' +

						@localized_string_costIncreased +' =SUM(CASE

						WHEN RecommendedCost>CurrentCost THEN 1 else 0 end), ' +

						 @localized_string_costSame + ' =SUM(CASE

						WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) '

						

set @sql_from =		 '	from 

						[msdb].[dbo].[DTA_reports_query]

						where 

						SessionID=@SessionID group by StatementType '

					

set @sql =			'	create procedure sp_DTA_wkld_analysis_helper_relational

						@SessionID		int

						as

						begin'  + @sql_select + @sql_from + ' end '

execute(@sql)	

go

/*****************************************************************************

	sp_dta_get_session_report

	@SessionID,		- ID of a session requested

	@ReportID,		- ID of a report requested

	@ReportType		- report format:

					0   rowset

					1 - XML 



	This is the main sp called by DTA/Shell to generate various reports

	This in turn calls the various helper functions in script					

*****************************************************************************/



print ''

print 'Creating procedure sp_DTA_get_session_report...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_session_report' and type = 'P')

   drop procedure sp_DTA_get_session_report 

go

create procedure sp_DTA_get_session_report 

	@SessionID int, 

	@ReportID int,

	@ReportType int

as 

begin



	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	

	if @ReportType = 0

	begin

		/**************************************************************/

		/* Query Cost Report                                          */

		/**************************************************************/

		if @ReportID = 2

		begin

			exec sp_DTA_query_cost_helper_relational @SessionID	

		end

		/**************************************************************/

		/* Event Frequency Report                                     */

		/**************************************************************/

		else if @ReportID = 3

		begin

			exec sp_DTA_event_weight_helper_relational @SessionID								

		end	

		/**************************************************************/

		/* Query Detail Report                                        */

		/**************************************************************/

		else if @ReportID = 4

		begin

			exec sp_DTA_query_detail_helper_relational  @SessionID	

		end	

		/**************************************************************/

		/* Current Query Index Relations Report                        */

		/**************************************************************/

		else if @ReportID = 5

		begin

			exec sp_DTA_query_indexrelations_helper_relational  @SessionID,0

		end	

		/**************************************************************/

		/* Recommended Query Index Relations Report                   */

		/**************************************************************/

		else if @ReportID = 6

		begin

			exec sp_DTA_query_indexrelations_helper_relational  @SessionID,1

		end	

		/**************************************************************/

		/* Current Query Cost Range		                             */

		/**************************************************************/

		else if @ReportID = 7

		begin

			exec sp_DTA_query_costrange_helper_relational @SessionID

		end

		/**************************************************************/

		/* Recommended Query Cost Range		                            */

		/**************************************************************/

		else if @ReportID = 8

		begin

			exec sp_DTA_query_costrange_helper_relational @SessionID

		end

		/**************************************************************/

		/* Current Query Index Usage Report		                       */

		/**************************************************************/

		else if @ReportID = 9

		begin

			exec sp_DTA_index_usage_helper_relational @SessionID,0

		end

		/**************************************************************/

		/* Recommended Query Index Usage Report		                   */

		/**************************************************************/

		else if @ReportID = 10

		begin

			exec sp_DTA_index_usage_helper_relational @SessionID,1

		end

		/**************************************************************/

		/* Current Index Detail Report                                */

		/**************************************************************/

		else if @ReportID = 11

		begin

			exec sp_DTA_index_detail_current_helper_relational  @SessionID

		end	

		/**************************************************************/

		/* Recommended Index Detail Report                                */

		/**************************************************************/

		else if @ReportID = 12

		begin

			exec sp_DTA_index_detail_recommended_helper_relational  @SessionID

		end	

		/**************************************************************/

		/* View Table Relations Report                                */

		/**************************************************************/

		else if @ReportID = 13

		begin

			exec sp_DTA_view_table_helper_relational  @SessionID

		end

		/**************************************************************/

		/* Workload Analysis Report                                   */

		/**************************************************************/

		else if @ReportID = 14

		begin

			exec sp_DTA_wkld_analysis_helper_relational @SessionID

		end	

		/**************************************************************/

		/* All object access reports                                   */

		/**************************************************************/

		else if @ReportID = 15

		begin

			exec sp_DTA_database_access_helper_relational @SessionID

		end

		else if @ReportID = 16

		begin

			exec sp_DTA_table_access_helper_relational @SessionID

		end

		else if @ReportID = 17

		begin

			exec sp_DTA_column_access_helper_relational @SessionID

		end

	end

	-- XML Reports

	else if @ReportType = 1

	begin

		/**************************************************************/

		/* Query Cost Report                                          */

		/**************************************************************/

		if @ReportID = 2

		begin

			exec sp_DTA_query_cost_helper_xml @SessionID	

		end

		/**************************************************************/

		/* Event Frequency Report                                     */

		/**************************************************************/

		else if @ReportID = 3

		begin

			exec sp_DTA_event_weight_helper_xml @SessionID								

		end	

		/**************************************************************/

		/* Query Detail Report                                        */

		/**************************************************************/

		else if @ReportID = 4

		begin

			exec sp_DTA_query_detail_helper_xml  @SessionID	

		end	

		/**************************************************************/

		/* Current Query Index Relations Report                        */

		/**************************************************************/

		else if @ReportID = 5

		begin

			exec sp_DTA_query_indexrelations_helper_xml  @SessionID,0

		end	

		/**************************************************************/

		/* Recommended Query Index Relations Report                   */

		/**************************************************************/

		else if @ReportID = 6

		begin

			exec sp_DTA_query_indexrelations_helper_xml  @SessionID,1

		end	

		/**************************************************************/

		/* Current Query Cost Range		                             */

		/**************************************************************/

		else if @ReportID = 7

		begin

			exec sp_DTA_query_costrange_helper_xml @SessionID

		end

		/**************************************************************/

		/* Recommended Query Cost Range		                            */

		/**************************************************************/

		else if @ReportID = 8

		begin

			exec sp_DTA_query_costrange_helper_xml @SessionID

		end

		/**************************************************************/

		/* Current Query Index Usage Report		                       */

		/**************************************************************/

		else if @ReportID = 9

		begin

			exec sp_DTA_index_usage_helper_xml @SessionID,0

		end

		/**************************************************************/

		/* Recommended Query Index Usage Report		                   */

		/**************************************************************/

		else if @ReportID = 10

		begin

			exec sp_DTA_index_usage_helper_xml @SessionID,1

		end

		/**************************************************************/

		/* Current Index Detail Report                                */

		/**************************************************************/

		else if @ReportID = 11

		begin

			exec sp_DTA_index_current_detail_helper_xml  @SessionID

		end	

		/**************************************************************/

		/* Recommended Index Detail Report                                */

		/**************************************************************/

		else if @ReportID = 12

		begin

			exec sp_DTA_index_recommended_detail_helper_xml  @SessionID

		end	

		/**************************************************************/

		/* View Table Relations Report                                */

		/**************************************************************/

		else if @ReportID = 13

		begin

			exec sp_DTA_view_table_helper_xml  @SessionID

		end

		/**************************************************************/

		/* Workload Analysis Report                                   */

		/**************************************************************/

		else if @ReportID = 14

		begin

			exec sp_DTA_wkld_analysis_helper_xml @SessionID

		end	

		/**************************************************************/

		/* All object access reports                                   */

		/**************************************************************/

		else if @ReportID = 15

		begin

			exec sp_DTA_database_access_helper_xml @SessionID

		end

		else if @ReportID = 16

		begin

			exec sp_DTA_table_access_helper_xml @SessionID

		end

		else if @ReportID = 17

		begin

			exec sp_DTA_column_access_helper_xml @SessionID

		end

	end		

end	

go

grant exec on sp_DTA_get_session_report to public

go

/*****************************************************************************

	Start of stored procs used by DTAEngine90

	DTAEngine90 also calls SP's internally

*****************************************************************************/

	



/*****************************************************************************

	sp_DTA_set_tuninglogtablename

	@LogTableName - Tuning Log table name 

	@SessionID,		- ID of a session requested

	

	Sets the tuning log table name passed by DTA

*****************************************************************************/





print ''

print 'Creating procedure sp_DTA_set_tuninglogtablename...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_set_tuninglogtablename' and type = 'P')

   drop procedure sp_DTA_set_tuninglogtablename 

go

create procedure sp_DTA_set_tuninglogtablename

	@LogTableName nvarchar(1280), 

	@SessionID int 



as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	update [msdb].[dbo].[DTA_input] set LogTableName = @LogTableName where SessionID = @SessionID

	



end	

go

grant exec on sp_DTA_set_tuninglogtablename to public

go





/*****************************************************************************

	sp_DTA_get_tuningoptions

	@SessionID	- ID of a session requested

	Gets the tuning options from [msdb].[dbo].[DTA_input] for this session

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_tuningoptions...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_tuningoptions' and type = 'P')

   drop procedure sp_DTA_get_tuningoptions 

go

create procedure sp_DTA_get_tuningoptions

	@SessionID int 



as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	select TuningOptions from [msdb].[dbo].[DTA_input] where SessionID = @SessionID	



end	

go

grant exec on sp_DTA_get_tuningoptions to public

go





/*****************************************************************************

	sp_DTA_get_interactivestatus

	@SessionID	- ID of a session requested

	Gets the interactivestatus bit from DTA_input

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_interactivestatus...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_interactivestatus' and type = 'P')

   drop procedure sp_DTA_get_interactivestatus 

go

create procedure sp_DTA_get_interactivestatus

	@SessionID int 



as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	select InteractiveStatus from [msdb].[dbo].[DTA_input] where SessionID = @SessionID

end	

go

grant exec on sp_DTA_get_interactivestatus to public

go



/*****************************************************************************

	sp_DTA_insert_progressinformation

	@SessionID	- ID of a session requested

	Inserts the progress information in DTA_Progress table

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_progressinformation...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_progressinformation' and type = 'P')

   drop procedure sp_DTA_insert_progressinformation

go

create procedure sp_DTA_insert_progressinformation

	@SessionID int,

	@TuningStage int

as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	INSERT into [msdb].[dbo].[DTA_progress]

		(SessionID,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage)

	values(@SessionID,0,0,@TuningStage,N'',N'',N'')

	

end	

go

grant exec on sp_DTA_insert_progressinformation to public

go







/*****************************************************************************

	sp_DTA_set_progressinformation

	@SessionID	- ID of a session requested

	Sets the progress information in DTA_Progress table

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_set_progressinformation...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_set_progressinformation' and type = 'P')

   drop procedure sp_DTA_set_progressinformation

go

create procedure sp_DTA_set_progressinformation

	@SessionID int,

	@WorkloadConsumption int,

	@TuningStage int,

	@EstImprovement int,

	@ConsumingWorkLoadMessage nvarchar(256) = N'',

	@PerformingAnalysisMessage nvarchar(256)= N'',

	@GeneratingReportsMessage nvarchar(256)= N''





as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	update [msdb].[dbo].[DTA_progress]

	set WorkloadConsumption = @WorkloadConsumption,

	EstImprovement = @EstImprovement,

	ProgressEventTime = GetDate(),

	ConsumingWorkLoadMessage =	@ConsumingWorkLoadMessage ,

	PerformingAnalysisMessage =	@PerformingAnalysisMessage,

	GeneratingReportsMessage =	@GeneratingReportsMessage

	where SessionID=@SessionID

	and TuningStage = @TuningStage

end	

go

grant exec on sp_DTA_set_progressinformation to public

go



/*****************************************************************************

	sp_DTA_set_outputinformation

	@SessionID	- ID of a session requested

	@TuningResults - Tuning results 

	@FinishStatus - Finish status

	Sets the output information

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_set_outputinformation...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_set_outputinformation' and type = 'P')

   drop procedure sp_DTA_set_outputinformation

go

create procedure sp_DTA_set_outputinformation

	@SessionID int,

	@TuningResults nvarchar(max),

	@FinishStatus tinyint

as 

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	

	Insert into [msdb].[dbo].[DTA_output]([SessionID], [TuningResults],[FinishStatus]) values(@SessionID,@TuningResults,@FinishStatus)

end	

go

grant exec on sp_DTA_set_outputinformation to public

go

/*****************************************************************************

   For Azure DB currently we are using dmv collector to pull tuning results into mds table. nvarchar(max) tuningresults 

   will be truncated in this process due to mds table limit on size of row. Therefore we are breaking tuningresults into parts

   and reassemble later in WAFL. Two tables are used.



   1) DTA_tuningresults_part

      This table stores parts of tuningresults for each tuning session

   2) DTA_tuningresults

      This table stores last part number of tuningresults



   To assemble the parts, client will wait for all parts of tuningresults for a session are received.

   Last part number is used to indicate the boundary (end of parts).

   

	sp_DTA_set_tuningresults_part

	@SessionID	- ID of a session requested

	@Content	- Result content	

	@PartNumber - Tuning results part number

	Sets the output information

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_set_tuningresults_part...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_set_tuningresults_part' and type = 'P')

   drop procedure sp_DTA_set_tuningresults_part

go

create procedure sp_DTA_set_tuningresults_part

	@SessionID int,	

	@Content nvarchar(3500),

	@PartNumber 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_tuningresults_part]([SessionID], [PartNumber],[Content]) values(@SessionID,@PartNumber,@Content)

end	

go

grant exec on sp_DTA_set_tuningresults_part to public

go

/*****************************************************************************

	sp_DTA_set_tuningresults

	@SessionID	- ID of a session requested

	@FinishStatus - Finish status

	@LastPartNumber	- Last part number

	Sets the output information

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_set_tuningresults...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_set_tuningresults' and type = 'P')

   drop procedure sp_DTA_set_tuningresults

go

create procedure sp_DTA_set_tuningresults

	@SessionID int,	

	@FinishStatus tinyint,

	@LastPartNumber 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_tuningresults]([SessionID], [LastPartNumber],[FinishStatus]) values(@SessionID,@LastPartNumber,@FinishStatus)

end	

go

grant exec on sp_DTA_set_tuningresults to public

go

/*****************************************************************************

	Table Inserts called by DTAEngine90 since DBO's dont have bulkadmin 

	privileges

*****************************************************************************/



/*****************************************************************************

	sp_DTA_insert_reports_database

	@SessionID	int

	@DatabaseName sysname

*****************************************************************************/



print ''

print 'Creating procedure sp_DTA_insert_reports_database...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_database' and type = 'P')

   drop procedure sp_DTA_insert_reports_database

go

create procedure sp_DTA_insert_reports_database

	@SessionID	int,

	@DatabaseName sysname,

	@IsDatabaseSelectedToTune int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	

	Insert into [msdb].[dbo].[DTA_reports_database]([SessionID],[DatabaseName],[IsDatabaseSelectedToTune]) values(@SessionID,@DatabaseName,@IsDatabaseSelectedToTune)

end	

go

grant exec on sp_DTA_insert_reports_database to public

go





/*****************************************************************************

sp_DTA_insert_reports_partitionscheme

   @SessionID	int

   @PartitionFunctionID int

   @PartitionSchemeName sysname

   @PartitionSchemeDefinition nvarchar(max)

*****************************************************************************/



print ''

print 'Creating procedure sp_DTA_insert_reports_partitionscheme...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_partitionscheme' and type = 'P')

   drop procedure sp_DTA_insert_reports_partitionscheme

go

create procedure sp_DTA_insert_reports_partitionscheme

	@SessionID	int,   

	@PartitionFunctionID int,

	@PartitionSchemeName sysname,

	@PartitionSchemeDefinition nvarchar(max)

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	

	Insert into [msdb].[dbo].[DTA_reports_partitionscheme]( [PartitionFunctionID],[PartitionSchemeName],[PartitionSchemeDefinition]) values(@PartitionFunctionID,@PartitionSchemeName,@PartitionSchemeDefinition)

end	

go

grant exec on sp_DTA_insert_reports_partitionscheme to public

go





/*****************************************************************************

sp_DTA_insert_reports_partitionfunction

	SessionID	int

	DatabaseID int

	PartitionFunctionName sysname

	PartitionFunctionDefinition nvarchar(max)

*****************************************************************************/



print ''

print 'Creating procedure sp_DTA_insert_reports_partitionfunction...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_partitionfunction' and type = 'P')

   drop procedure sp_DTA_insert_reports_partitionfunction

go

create procedure sp_DTA_insert_reports_partitionfunction

	@SessionID	int,

	@DatabaseID int,

	@PartitionFunctionName sysname,

	@PartitionFunctionDefinition nvarchar(max)

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	

	Insert into [msdb].[dbo].[DTA_reports_partitionfunction]([DatabaseID],[PartitionFunctionName],[PartitionFunctionDefinition]) 

	values(@DatabaseID,@PartitionFunctionName,@PartitionFunctionDefinition)

end	

go

grant exec on sp_DTA_insert_reports_partitionfunction to public

go





/*****************************************************************************

sp_DTA_insert_reports_column

	@SessionID	int,

	@TableID int

	@ColumnName sysname

*/



print ''

print 'Creating procedure sp_DTA_insert_reports_column...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_column' and type = 'P')

   drop procedure sp_DTA_insert_reports_column

go

create procedure sp_DTA_insert_reports_column

	@SessionID	int,

	@TableID int,

	@ColumnName sysname

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	

	insert into [msdb].[dbo].[DTA_reports_column]([TableID], [ColumnName])

		values(	@TableID ,@ColumnName)



end	

go

grant exec on sp_DTA_insert_reports_column to public

go



/*****************************************************************************

sp_DTA_insert_reports_tableview

	@SessionID	int,	

	@TableID	int,

	@ViewID		int

*****************************************************************************/



print ''

print 'Creating procedure sp_DTA_insert_reports_tableview...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_tableview' and type = 'P')

   drop procedure sp_DTA_insert_reports_tableview

go

create procedure sp_DTA_insert_reports_tableview

	@SessionID	int,

	@TableID	int,

	@ViewID		int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	insert into [msdb].[dbo].[DTA_reports_tableview]([TableID], [ViewID])

	values(@TableID,@ViewID)

	



end	

go

grant exec on sp_DTA_insert_reports_tableview to public

go



/*****************************************************************************

sp_DTA_insert_reports_query

	@SessionID			int

	@QueryID			int

	@StatementType		smallint

	@StatementString	nvarchar(max)

	@CurrentCost		float

	@RecommendedCost	float

	@Weight				float

	@EventString		nvarchar(max)

	@EventWeight		float

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_query...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_query' and type = 'P')

   drop procedure sp_DTA_insert_reports_query

go

create procedure sp_DTA_insert_reports_query

	@SessionID			int,

	@QueryID			int,

	@StatementType		smallint,

	@StatementString	nvarchar(max),

	@CurrentCost		float,

	@RecommendedCost	float,

	@Weight				float,

	@EventString		nvarchar(max),

	@EventWeight		float

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	insert into [msdb].[dbo].[DTA_reports_query]([SessionID],[QueryID], [StatementType], [StatementString], [CurrentCost], [RecommendedCost], [Weight], [EventString], [EventWeight])

	values(@SessionID,@QueryID,@StatementType,@StatementString,@CurrentCost,@RecommendedCost,@Weight,@EventString,@EventWeight)

	



end	

go

grant exec on sp_DTA_insert_reports_query to public

go



/*****************************************************************************

sp_DTA_insert_reports_index

	@SessionID			int

	@TableID			int

	@IndexName			sysname

	@IsClustered		bit

	@IsUnique			bit

	@IsHeap				bit

	@IsExisting			bit

	@IsFiltered			bit

	@Storage			int

	@NumRows			bigint

	@IsRecommended		bit

	@RecommendedStorage int

	@PartitionSchemeID	int

	@SessionUniquefier  int

	@FilterDefinition	nvarchar(1024)

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_index...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_index' and type = 'P')

   drop procedure sp_DTA_insert_reports_index

go

create procedure sp_DTA_insert_reports_index

	@SessionID			int,

	@TableID			int,

	@IndexName			sysname,

	@IsClustered		bit,

	@IsUnique			bit,

	@IsHeap				bit,

	@IsExisting			bit,

	@IsFiltered			bit,

	@Storage			int,

	@NumRows			bigint,

	@IsRecommended		bit,

	@RecommendedStorage int,

	@PartitionSchemeID	int,

	@SessionUniquefier  int,

	@FilterDefinition	nvarchar(1024)

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_reports_index]([TableID], [IndexName], [IsClustered], [IsUnique], [IsHeap],[IsExisting], [IsFiltered],[Storage], [NumRows], [IsRecommended], [RecommendedStorage], [PartitionSchemeID],[SessionUniquefier],[FilterDefinition])	

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

end	

go

grant exec on sp_DTA_insert_reports_index to public

go





/*****************************************************************************

sp_DTA_insert_reports_table

	@SessionID	int

	@DatabaseID	int

	@SchemaName	sysname

	@TableName	sysname

	@IsView		bit

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_table...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_table' and type = 'P')

   drop procedure sp_DTA_insert_reports_table

GO

create procedure sp_DTA_insert_reports_table

	@SessionID	int,

	@DatabaseID	int,

	@SchemaName	sysname,

	@TableName	sysname,

	@IsView		bit

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_reports_table]([DatabaseID], [SchemaName], [TableName], [IsView])

	values(@DatabaseID,@SchemaName,@TableName,@IsView)

end	

GO

grant exec on sp_DTA_insert_reports_table to public

GO





/*****************************************************************************

sp_DTA_insert_reports_queryindex

	@SessionID	int

	@QueryID	int

	@IndexID	int

	@IsRecommendedConfiguration	bit

	@Cost		float

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_queryindex...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_queryindex' and type = 'P')

   drop procedure sp_DTA_insert_reports_queryindex

go

create procedure sp_DTA_insert_reports_queryindex

	@SessionID	int,

	@QueryID	int,

	@IndexID	int,

	@IsRecommendedConfiguration	bit

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_reports_queryindex]([SessionID],[QueryID],[IndexID], [IsRecommendedConfiguration])

	values(@SessionID,@QueryID,@IndexID,@IsRecommendedConfiguration)

	

end	

go

grant exec on sp_DTA_insert_reports_queryindex to public

go





/*****************************************************************************

sp_DTA_insert_reports_indexcolumn

	@SessionID		int

	@IndexID		int

	@ColumnID		int

	@ColumnOrder	int

	@PartitionColumnOrder	int

	@IsKeyColumn	bit

	@IsDescendingColumn	bit

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_indexcolumn...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_indexcolumn' and type = 'P')

   drop procedure sp_DTA_insert_reports_indexcolumn

go

create procedure sp_DTA_insert_reports_indexcolumn

	@SessionID		int,

	@IndexID		int,

	@ColumnID		int,

	@ColumnOrder	int,

	@PartitionColumnOrder	int,

	@IsKeyColumn	bit,

	@IsDescendingColumn	bit

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_reports_indexcolumn]([IndexID], [ColumnID], [ColumnOrder], [PartitionColumnOrder], [IsKeyColumn], [IsDescendingColumn])

	values(@IndexID,@ColumnID,@ColumnOrder,@PartitionColumnOrder,@IsKeyColumn,@IsDescendingColumn)

end	

go

grant exec on sp_DTA_insert_reports_indexcolumn to public

go





/*****************************************************************************

sp_DTA_insert_reports_querytable

	@SessionID		int

	@QueryID		int

	@TableID		int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_querytable...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_querytable' and type = 'P')

   drop procedure sp_DTA_insert_reports_querytable

go

create procedure sp_DTA_insert_reports_querytable

	@SessionID		int,

	@QueryID		int,

	@TableID		int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_reports_querytable]([SessionID], [QueryID],[TableID])

	values(@SessionID,@QueryID,@TableID)

end	

go

grant exec on sp_DTA_insert_reports_querytable to public

go

/*****************************************************************************

sp_DTA_insert_reports_querydatabase

	@SessionID		int

	@QueryID		int

	@DatabaseID		int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_querydatabase...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_querydatabase' and type = 'P')

   drop procedure sp_DTA_insert_reports_querydatabase

go

create procedure sp_DTA_insert_reports_querydatabase

	@SessionID		int,

	@QueryID		int,

	@DatabaseID		int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_reports_querydatabase]([SessionID], [QueryID],[DatabaseID])

	values(@SessionID,@QueryID,@DatabaseID)

end	

go

grant exec on sp_DTA_insert_reports_querydatabase to public

go



/*****************************************************************************

sp_DTA_insert_reports_querycolumn

	@SessionID		int

	@QueryID		int

	@ColumnID		int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_reports_querycolumn...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_reports_querycolumn' and type = 'P')

   drop procedure sp_DTA_insert_reports_querycolumn

go

create procedure sp_DTA_insert_reports_querycolumn

	@SessionID		int,

	@QueryID		int,

	@ColumnID		int

as	

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_reports_querycolumn]([QueryID], [ColumnID],[SessionID])

	values(@QueryID,@ColumnID,@SessionID )

end	

go

grant exec on sp_DTA_insert_reports_querycolumn to public

go





/*****************************************************************************

sp_DTA_insert_DTA_tuninglog

	@SessionID int

	@RowID int

	@CategoryID char(4)

	@Event nvarchar(max)

	@Statement nvarchar(max)

	@Frequency int

	@Reason nvarchar(max)

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_insert_DTA_tuninglog...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_insert_DTA_tuninglog' and type = 'P')

   drop procedure sp_DTA_insert_DTA_tuninglog

go

create procedure sp_DTA_insert_DTA_tuninglog

	@SessionID int,

	@RowID int,

	@CategoryID char(4),

	@Event nvarchar(max),

	@Statement nvarchar(max),

	@Frequency int,

	@Reason nvarchar(max)

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	

	insert into [msdb].[dbo].[DTA_tuninglog]([SessionID], [RowID], [CategoryID], [Event], [Statement], [Frequency], [Reason])

	values(@SessionID, @RowID, @CategoryID, @Event, @Statement, @Frequency, @Reason)

end	

go

grant exec on sp_DTA_insert_DTA_tuninglog to public

go







/*****************************************************************************

 sp_DTA_get_databasetableids

	@SessionID	int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_databasetableids...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_databasetableids' and type = 'P')

   drop procedure sp_DTA_get_databasetableids

go

create procedure sp_DTA_get_databasetableids

	@SessionID	int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	select DatabaseID,DatabaseName 

	from [msdb].[dbo].[DTA_reports_database] as D 

	where D.SessionID = @SessionID



end	

go

grant exec on sp_DTA_get_databasetableids to public

go





/*****************************************************************************

sp_DTA_get_pftableids

	@SessionID	int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_pftableids...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_pftableids' and type = 'P')

   drop procedure sp_DTA_get_pftableids

go

create procedure sp_DTA_get_pftableids

	@SessionID	int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	select PartitionFunctionID ,DatabaseName ,PartitionFunctionName  

	from [msdb].[dbo].[DTA_reports_partitionfunction]  as PF,

	[msdb].[dbo].[DTA_reports_database] as D 

	where PF.DatabaseID = D.DatabaseID 

	and D.SessionID = @SessionID

	

end	

go

grant exec on sp_DTA_get_pftableids to public

go







/*****************************************************************************

sp_DTA_get_pstableids

	@SessionID	int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_pstableids...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_pstableids' and type = 'P')

   drop procedure sp_DTA_get_pstableids

go

create procedure sp_DTA_get_pstableids

	@SessionID	int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	select PartitionSchemeID,DatabaseName,PartitionSchemeName   

	from [msdb].[dbo].[DTA_reports_partitionfunction]  as PF, 

	[msdb].[dbo].[DTA_reports_partitionscheme]  as PS, 

	[msdb].[dbo].[DTA_reports_database] as D 

	where PS.PartitionFunctionID  = PF.PartitionFunctionID and 

	PF.DatabaseID = D.DatabaseID and D.SessionID = @SessionID	

	

end	

go

grant exec on sp_DTA_get_pstableids to public

go







/*****************************************************************************

sp_DTA_get_tableids

	@SessionID	int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_tableids...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_tableids' and type = 'P')

   drop procedure sp_DTA_get_tableids

go

create procedure sp_DTA_get_tableids

	@SessionID	int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	select TableID,DatabaseName,SchemaName,TableName 

	from [msdb].[dbo].[DTA_reports_table] as T,[msdb].[dbo].[DTA_reports_database] as D 

	where T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID





end	

go

grant exec on sp_DTA_get_tableids to public

go



/*****************************************************************************

sp_DTA_get_columntableids

	@SessionID	int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_columntableids...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_columntableids' and type = 'P')

   drop procedure sp_DTA_get_columntableids

go

create procedure sp_DTA_get_columntableids

	@SessionID	int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	select ColumnID,DatabaseName,SchemaName,TableName,ColumnName 

	from [msdb].[dbo].[DTA_reports_column] as C,

	[msdb].[dbo].[DTA_reports_table] as T,[msdb].[dbo].[DTA_reports_database] as D 

	where C.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID





end	

go

grant exec on sp_DTA_get_columntableids to public

go





/*****************************************************************************

sp_DTA_get_indexableids

	@SessionID	int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_get_indexableids...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_get_indexableids' and type = 'P')

   drop procedure sp_DTA_get_indexableids

go

create procedure sp_DTA_get_indexableids

	@SessionID	int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	select IndexID,DatabaseName,SchemaName,TableName,IndexName,SessionUniquefier 

	from [msdb].[dbo].[DTA_reports_index] as I,[msdb].[dbo].[DTA_reports_table] as T,

	[msdb].[dbo].[DTA_reports_database] as D 

	where I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID 



end	

go

grant exec on sp_DTA_get_indexableids to public

go



/*****************************************************************************

	sp_DTA_update_tuninglog_errorfrequency

	

	@SessionID	int

	@RowID		int

	@Frequency int

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_update_tuninglog_errorfrequency...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_update_tuninglog_errorfrequency' and type = 'P')

   drop procedure sp_DTA_update_tuninglog_errorfrequency

go

create procedure sp_DTA_update_tuninglog_errorfrequency

	@SessionID	int,

	@Frequency	int,

	@RowID		int

as

begin

	declare @retval  int							

	set nocount on



	exec @retval =  sp_DTA_check_permission @SessionID



	if @retval = 1

	begin

		raiserror(31002,-1,-1)

		return(1)

	end	



	update [msdb].[dbo].[DTA_tuninglog]

	set [Frequency]=@Frequency

	where [RowID]=@RowID and [SessionID] = @SessionID



end	

go

grant exec on sp_DTA_update_tuninglog_errorfrequency to public

go

/*****************************************************************************

	sp_DTA_start_xmlprefix



	Doesnt take any arguments

	Add prefix for XML reports

	

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_start_xmlprefix...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_start_xmlprefix' and type = 'P')

   drop procedure sp_DTA_start_xmlprefix

go

create procedure sp_DTA_start_xmlprefix

as

begin

	declare @startTags nvarchar(128)

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

	select @startTags

end

go

/*****************************************************************************

	sp_DTA_end_xmlprefix



	Doesnt take any arguments

	Add postfix tags for XML reports

	

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_end_xmlprefix...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_end_xmlprefix' and type = 'P')

   drop procedure sp_DTA_end_xmlprefix

go

create procedure sp_DTA_end_xmlprefix

as

begin

	declare @endTags nvarchar(128)

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

	select @endTags

end

go

/*****************************************************************************

	sp_DTA_cleanup_hypothetical_metadata

		@DatabaseName, 	- name of the database

	

	Clean up hypothetical indexes and statistics created by DTA

	

*****************************************************************************/

print ''

print 'Creating procedure sp_DTA_cleanup_hypothetical_metadata...'

go

if exists (select name from msdb.dbo.sysobjects 

         where name = 'sp_DTA_cleanup_hypothetical_metadata' and type = 'P')

   drop procedure sp_DTA_cleanup_hypothetical_metadata

go

create procedure sp_DTA_cleanup_hypothetical_metadata

	@DatabaseName sysname

as

begin



	declare @SQL nvarchar(max) 



	select @SQL = N'

	declare @strSQL nvarchar(max) 

	declare @objid int 

	declare @indid int 

	declare dta_indexes cursor for select object_id, index_id from sys.indexes where name LIKE ''%dta%'' order by name 

	open dta_indexes 

	fetch NEXT from dta_indexes into @objid, @indid 

	while (@@fetch_status != -1) 

	begin 

	select @strSQL = (select case when INDEXPROPERTY(i.object_id, i.name, ''IsStatistics'') = 1 then ''drop statistics ['' else ''drop index ['' end + schema_name(s.schema_id) + ''].['' + object_name(i.object_id) + ''].['' + i.name + '']'' 

	from sys.indexes i join sys.objects o on i.object_id = o.object_id join sys.schemas s on o.schema_id = s.schema_id

	where i.object_id = @objid and i.index_id = @indid and 

	(INDEXPROPERTY(i.object_id, i.name, ''IsHypothetical'') = 1 or

	(INDEXPROPERTY(i.object_id, i.name, ''IsStatistics'') = 1 and 

	INDEXPROPERTY(i.object_id, i.name, ''IsAutoStatistics'') = 0))) 

	EXEC(@strSQL) 

	fetch NEXT from dta_indexes into @objid, @indid

	end

	close dta_indexes 

	deallocate dta_indexes

	'



	if CHARINDEX('[', @DatabaseName) = 0

	begin

		select @DatabaseName = '[' + @DatabaseName + ']'

	end

	

	select @SQL = 'exec ' + @DatabaseName + '..sp_executesql N''' + replace(@SQL,'''','''''') + ''''

	exec (@SQL)



	return @@error

end

go

/*****************************************************************************

	fn_DTA_unquote_dbname



	DTA client passes in quoted db names because of a bug fix. The dbnames are

	stored unquoted. master and [master] map to master. Hence unquote dbname

	

*****************************************************************************/

print ''

print 'Creating function fn_DTA_unquote_dbname...'

go

if object_id('fn_DTA_unquote_dbname') is not null

   drop function fn_DTA_unquote_dbname

go

create function fn_DTA_unquote_dbname(@dbname nvarchar(258) )

returns sysname

as

begin

	declare @unquote nvarchar(258) 

	set @unquote = @dbname

	if(patindex(N'[[]%',@unquote) > 0)

		  select @unquote = right(@unquote, LEN(@unquote)-1)

	if(patindex(N'%]',@unquote)  > 0)

		  select @unquote = left(@unquote, LEN(@unquote)-1)

	select @unquote =REPLACE (@unquote,N']]',N']')

	return @unquote

end

go 

��/****************************************************************************

//		Copyright (c) Microsoft Corporation.

//

// @File: DTA_sprocs_PG.sql

//

//

// @EndHeader@

*****************************************************************************/



create or replace function dta.sp_DTA_check_permission(

	SessionID int)

returns int as

'select case when usesuper then 0 else 1 end from pg_user where usename = CURRENT_USER;'

LANGUAGE SQL

IMMUTABLE;



create or replace function dta.sp_DTA_add_session(

   SessionNameIn varchar(128),

   TuningOptionsIn text,

   GlobalSessionIDIn UUID = NULL,

   OverwriteIn boolean = false)

returns int language plpgsql

as $$ 

declare 

	SessionIdIn int;

	ErrorString varchar(500);

	retval int;

	i int;

	dbid int;

	dbcount int;

	TuningOptionsXml xml;

	dbnames varchar(128)[];

begin

	-- Check for duplicate session name

	perform 1

	from dta.DTA_input

	where SessionName = SessionNameIn;

	if found then

		if OverwriteIn then

			delete from dta.DTA_input cascade where SessionName = SessionNameIn;

		else

			raise 'Session with id % already exists.', SessionNameIn;   

		end if;

	end if;



	-- Create new session

	if GlobalSessionIDIn is not null then

		insert into dta.DTA_input (SessionName,TuningOptions, GlobalSessionID) 

		values (SessionNameIn,TuningOptionsIn,GlobalSessionIDIn);

	else

		insert into dta.DTA_input (SessionName,TuningOptions) 

		values (SessionNameIn,TuningOptionsIn);

	end if;

	

	select currval('dta.DTA_input_SessionID_seq') into strict SessionIdIn;

	

	TuningOptionsIn = replace(TuningOptionsIn, 'utf-16', 'utf-8');

	TuningOptionsXml = XMLPARSE(DOCUMENT TuningOptionsIn);



	select xpath('/x:DTAXML/x:DTAInput/x:Server//x:Database/x:Name/text()', TuningOptionsXml, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}') into dbnames;

	select count(*) into strict dbcount from unnest(dbnames);

	

	if dbcount <> 1 then

		raise 'Tuning Options must specify exactly one DB to tune';

	end if;

		

	select pg_database.oid into strict dbid from pg_database where dbnames[1] = pg_database.datname;



	if OverwriteIn then

		delete from dta.dta_input where SessionId in

		(select SessionId from dta.dta_reports_database where DatabaseId = dbid);

	end if;

	

	insert into dta.dta_reports_database values (dbid, SessionIdIn, dbnames[1], 1);

	

	insert into dta.DTA_progress

	(SessionId,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage)

	values(SessionIdIn,0,0,0,'','','');

	

	return(SessionIdIn);

end $$;



create or replace function dta.sp_DTA_delete_session (

   SessionIdIn int) returns void as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	delete from dta.DTA_input where SessionID=SessionIDIn;

end $$ language plpgsql;



create or replace function dta.sp_DTA_get_session_tuning_results (

   SessionIdIn int) returns table(FinishStatus smallint, RuningResults text) as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	return query

	select dta.DTA_output.FinishStatus, dta.DTA_output.TuningResults

	from dta.DTA_output

	where SessionID=SessionIdIn;

end $$ language plpgsql;



create or replace function dta.sp_DTA_set_interactivestatus (

   InterActiveStatusIn int,

   SessionIdIn int) returns void as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	update dta.DTA_input set InteractiveStatus = InterActiveStatusIn where SessionID = SessionIDIn;

end $$ language plpgsql;



create or replace function dta.sp_DTA_set_tuninglogtablename( 

   LogTableNameIn text,

   SessionIDIn int)

returns int 

as $$ begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	update dta.DTA_input set LogTableName = LogTableNameIn where SessionID = SessionIDIn;



	return(0);

end $$ language plpgsql;



create or replace function dta.sp_DTA_insert_DTA_tuninglog (

	SessionIdIn int,

	RowIdIn int,

	CategoryIDIn char(4),

	EventIn text,

	StatementIn text,

	FrequencyIn int,

	ReasonIn text

) returns void as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	insert into dta.DTA_tuninglog(SessionID, RowID, CategoryID, Event, Statement, Frequency, Reason)

	values(SessionIDIn, RowIDIn, CategoryIDIn, EventIn, StatementIn, FrequencyIn, ReasonIn);

end $$ language plpgsql;



create or replace function dta.sp_DTA_update_tuninglog_errorfrequency (

	SessionIdIn int,

	FrequencyIn int,

	RowIdIn int

) returns void as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	update dta.DTA_tuninglog

	set Frequency=FrequencyIn

	where RowId=RowIdIn and SessionId = SessionIdIn;



end $$ language plpgsql;



create or replace function dta.sp_DTA_get_tuningoptions (

   SessionIdIn int) returns text as

$$ 

declare

	TuningOptionsOut text;

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	select TuningOptions into TuningOptionsOut from dta.DTA_input where SessionID=SessionIDIn;



	if TuningOptionsOut is null then

		raise 'Tuning options not found!';

	end if;



	return(TuningOptionsOut);

end $$ language plpgsql;



create or replace function dta.sp_DTA_get_interactivestatus( 

   SessionIDIn int)

returns int 

as $$ declare

	InteractiveStatusOut int;

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	select InteractiveStatus into InteractiveStatusOut from dta.DTA_input where SessionID = SessionIDIn;



	return(InteractiveStatusOut);

end $$ language plpgsql;



create or replace function dta.sp_DTA_insert_progressinformation( 

   SessionIDIn int, 

   TuningStageIn int)

returns int 

as $$ begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	insert into dta.DTA_progress

		(SessionID,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage)

	values(SessionIDIn,0,0,TuningStageIn,'','','');



	return(0);

end $$ language plpgsql;



create or replace function dta.sp_DTA_set_progressinformation( 

	SessionIDIn int,

	WorkloadConsumptionIn int,

	TuningStageIn int,

	EstImprovementIn int,

	ConsumingWorkLoadMessageIn varchar(256) = '',

	PerformingAnalysisMessageIn varchar(256)= '',

	GeneratingReportsMessageIn varchar(256)= '')

returns void 

as $$ begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	update dta.DTA_progress

	set WorkloadConsumption = WorkloadConsumptionIn,

	EstImprovement = EstImprovementIn,

	ProgressEventTime = now(),

	ConsumingWorkLoadMessage =	ConsumingWorkLoadMessageIn ,

	PerformingAnalysisMessage =	PerformingAnalysisMessageIn,

	GeneratingReportsMessage =	GeneratingReportsMessageIn

	where SessionID=SessionIDIn

	and TuningStage = TuningStageIn;



end $$ language plpgsql;



create or replace function dta.sp_DTA_set_outputinformation (

   SessionIdIn int,

   TuningResultsIn text,

   FinishStatusIn smallint) returns void as

$$ 

declare 

	TuningResultsInXml xml;

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	insert into dta.DTA_output(SessionID, TuningResults,FinishStatus) values (SessionIDIn,TuningResultsIn,FinishStatusIn);



	if FinishStatusIn = 0 then

		insert into dta.DTA_rec(SessionID, GlobalSessionID, DatabaseName, StartTime, StopTime, FinishStatus, Message)

		select din.SessionID

			 , din.GlobalSessionID

			 , ddb.DatabaseName

			 , din.ScheduledStartTime as StartTime

			 , dout.StopTime

			 , dout.FinishStatus

			 , dout.TuningResults

		from dta.DTA_input din

		join dta.DTA_reports_database ddb on ddb.SessionID = din.SessionID

		join dta.DTA_output dout on dout.SessionID = din.SessionID

		where din.SessionId = SessionIdIn;



	else

		TuningResultsIn = replace(TuningResultsIn, 'UTF-16', 'UTF-8');

		TuningResultsInXml = XMLPARSE(DOCUMENT TuningResultsIn);



		insert into dta.DTA_rec(SessionID, GlobalSessionID, DatabaseName, StartTime, StopTime, FinishStatus, EstPctImprovement,

								EventWorkloadCount, EventTunedCount, StatementTunedCount, IdxRecCount)

		select din.SessionID

			 , din.GlobalSessionID

			 , ddb.DatabaseName

			 , din.ScheduledStartTime as StartTime

			 , dout.StopTime

			 , dout.FinishStatus

			 , ((xpath('/x:DTAXML/x:DTAOutput/x:TuningSummary/x:ReportEntry[contains(x:Name,''Estimated percentage improvement'')]/x:Value/text()', TuningResultsInXml, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text::real              

			 , ((xpath('/x:DTAXML/x:DTAOutput/x:TuningSummary/x:ReportEntry[contains(x:Name,''Number of events in workload'')]/x:Value/text()', TuningResultsInXml, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text::int 

			 , ((xpath('/x:DTAXML/x:DTAOutput/x:TuningSummary/x:ReportEntry[contains(x:Name,''Number of events tuned'')]/x:Value/text()', TuningResultsInXml, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text::int

			 , ((xpath('/x:DTAXML/x:DTAOutput/x:TuningSummary/x:ReportEntry[contains(x:Name,''Number of statements tuned'')]/x:Value/text()', TuningResultsInXml, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text::int 

			 , coalesce(((xpath('/x:DTAXML/x:DTAOutput/x:TuningSummary/x:ReportEntry[contains(x:Name,''Number of indexes recommended to be created'')]/x:Value/text()', TuningResultsInXml, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text::int, 0)

		from dta.DTA_input din

		join dta.DTA_reports_database ddb on ddb.SessionID = din.SessionID

		join dta.DTA_output dout on dout.SessionID = din.SessionID

		where din.SessionId = SessionIdIn;



		with tbl(xmldoc) as (

			select TuningResultsInXml)

		, schtbl(SchemaName, SchemaDef) as (

			select unnest(xpath('//*/x:Schema/x:Name/text()', xmldoc, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))::varchar(128) as SchemaName

				 , unnest(xpath('//*/x:Schema', xmldoc, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}')) as SchemaDef

			from tbl)

		, tabtbl(SchemaName, TableName, TableDef) as (

			select SchemaName

				  , unnest(xpath('//*/x:Table/x:Name/text()', SchemaDef, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))::varchar(128) as TableName

				  , unnest(xpath('//*/x:Table', SchemaDef, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}')) as TableDef

			from schtbl)

		, createtbl(SchemaName, TableName, CreateDef) as (

			select SchemaName

				  , TableName

				  , unnest(xpath('//*/x:Create', TableDef, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}')) as CreateDef

			from tabtbl)

		insert into dta.DTA_rec_idx(SessionID, IdxID, RecType, SchemaName, TableName, ColumnList, Command, Benefit, IndexSizeInMB)

		select SessionIdIn

			 , row_number() over ()

			 , 0

			 , SchemaName

			 , TableName

			 , ((xpath('//*/x:Index/x:Column/x:Name/text()', CreateDef, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}')))::varchar(128)[] as ColumnList

			 , ((xpath('//*/x:Index/x:Name/text()', CreateDef, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text as Command

			 , ((xpath('//*/x:Index/@Benefit', CreateDef, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text::double precision as Benefit

			 , ((xpath('//*/x:Index/@IndexSizeInMB', CreateDef, '{{x,http://schemas.microsoft.com/sqlserver/2004/07/dta}}'))[1])::text::double precision as IndexSizeInMB

		from createtbl;

	end if;



end $$ language plpgsql;



create or replace function dta.sp_DTA_set_tuningresults_part (

   SessionIdIn int, ContentIn text, PartNumberIn int) returns void as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	insert into dta.DTA_tuningresults_part(SessionID, PartNumber,Content) values(SessionIDIn,PartNumberIn,ContentIn);

end $$ language plpgsql;



create or replace function dta.sp_DTA_set_tuningresults (

   SessionIdIn int, FinishStatusIn smallint, LastPartNumberIn int) returns void as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	insert into dta.DTA_tuningresults(SessionID, LastPartNumber,FinishStatus) values(SessionIDIn, LastPartNumberIn,FinishStatusIn);

end $$ language plpgsql;



create or replace function dta.sp_DTA_get_databasetableids (

   SessionIdIn int) returns table(DatabaseID int, DatabaseName varchar(128)) as

$$ 

begin

	if dta.sp_DTA_check_permission (SessionIdIn) = 1 then

		raise 'Permission denied!';

	end if;



	return query

	select dta.DatabaseID, dta.DatabaseName

	from dta.DTA_reports_database dta

	where SessionID = SessionIdIn;

end $$ language plpgsql;



INSERT INTO dta.dta_key_val (key, intval)

VALUES ('schema_version', 1); 

3------------------- DTAEngine Done ---------------
6------------------- DTAEngine Start -----------------
'Insufficient arguments for -%s option.
&Argument for -%s option not provided.
jUsage: DTAEngine  -D database-name [,...n] {-i workload-file | -t workload-table-name} -o script-filename
�[-S instance\server]  [-R instance\server]  {-U username
[-P password] | -E}  [-f tuning-feature-set]  [-a analysis-report-list]
r[-A time-bound-secs]  [-K keep-existing-indexes]  [-B storage-bound]  [-e error-table-name]  [-C max-key-columns]
l[-T table-list-file]  [-m minimum-improvement]  [-F]  [-N]  [-X xml_input_file]  [-O xml_output_file]  [-v]
(Incorrect command line argument near %s
Unknown option %s specified.
$Specify a number for stroage bound.
'Value for storage bound should be > 0.
/Specify a number for maximum columns in index.
?Value for maximum columns in indxe should be between 1 and 16.
-Specify a number for the feature set option.
�Value for the feature set option should be between 0 and 3.
To combine feature sets append the option values delimited by space character within quotes .
Example -f "2 3" implies keep indexed views and partitions  Specify a number for -K option.
&Value for -K option should be 0 or 1.
 Specify a number for -M option.
/Value for -M option should be between 0 and 2.
 Specify a number for -m option.
5Specify a number for number of queries to tbe tuned.
@Value for number of queries that should be tuned should be > 0.
.Error: Database(s) to be tuned not specified.
/Error: Workload (File or Table) not specified.
'Error: Specify either -o or -O option.
AError: Incorrect format for table list in file %s near line:
 %s
@Invalid file name %s specified in the  table list  file option.
4Specified table [%s].[%s] not found in database %s.
ZUnable to create output script file %s.
 Use -F option to overwrite existing script file.
FLogin failed on tuned server. Check user name (-U) and password (-P).
*Login failed for user %s on tuned server.
#This tuning mode is not supported.
4This tuning mode is not supported in SQL Server 7.0
PTuning indexed views requires Enterprise Edition of Microsoft SQL Server 2000 .
ATuning indexed views is not supported in Microsoft SQL Server 7.0!Error writing to script file %s.
%Error closing output script file %s.
NoneError: setting tables to tune.
 Error: Unable to execute query.
Error: Exception encountered
PError: The events in the workload were ignored. Check if the workload is empty.
3Error: The events in the workload were unparseable
 Error: Invalid input specified.
WError: Table that was referenced during tuning/evaluate doesn't exist in the database.
sError: Test production feature is supported only if both test and production server versions are SQL 2005 and laterHeap}Error: The test and production server names specified refer to the same server. Make sure that the servers are not identical.	Unlimited�Use one of the following methods to increase storage space: (1) If you are using the graphical user interface, enter the required value for Define max. space for recommendations (MB) in the Advanced Options of the Tuning Options tabbed page; (2) If you are using dta.exe, specify the maximum space value for the -B argument; (3) If you are using an XML input file, specify the maximum space value for the <StorageBoundInMB> element under <TuningOptions>�All the events in the workload were ignored due to syntax errors.The most common reason for this error is that the database to connect has not been set correctly.KTuning indexed views requires SQL Server 2005 and later Enterprise Edition.3Tuning this edition of SQL Server is not permitted.
CategoryIDEvent	Statement	FrequencyReason)Percent MERGE statements in the tuned setVFiltered index ''%s'' specified in input configuration doesnt have a filter definitionError: Insufficient memory.
$Error: Connection to server broken.
5Error: Server returned error when optimizing a query
!Error: DTAEngine internal error.
+/* Created by: Database Tuning Advisor */

/* Date: %s */

/* Time: %s */

/* Server: %s */

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

/* Workload table: %s */

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



USE [%s]

go

�SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

go

eBEGIN TRANSACTION

DROP INDEX %s.%s

IF (@@error <> 0) ROLLBACK TRANSACTION

COMMIT TRANSACTION

go





>BEGIN TRANSACTION

CREATE CLUSTERED INDEX [%s] ON [%s].[%s] ( 4BEGIN TRANSACTION

CREATE INDEX [%s] ON [%s].[%s] ( 4CREATE VIEW [%s].[%s] WITH SCHEMABINDING AS %s

go

EBEGIN TRANSACTION

CREATE UNIQUE CLUSTERED INDEX [%s] ON [%s].[%s] ( E%s)

IF (@@error <> 0) ROLLBACK TRANSACTION

COMMIT TRANSACTION

go

%s, /

/* Statistics to support recommendations */

CREATE STATISTICS %s ON %s ( %s)

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

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

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

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

Error: Password too long.
 Error: -U option not specified.
%Error: must specify -U or -E option.
)/* No tunable queries in the workload. */A/* Expected improvement does not meet the specified -m option. */K/* Recommendation has reduced storage compared to current configuration. */n/* Expected improvement is negative since user specified reduced storage compared to current configuration. */a/* Current configuration is better or recommendation could not meet the specified constraints. */Invalid argument :Error: Incorrect format specified for -t option argument.
;Error: Owner name, if specified in -t option, must be dbo.
1Error: Unable to open input file specified : %s.
GError: Workload in the input table specified : %s could not be opened.
�The minimum storage space required for the existing physical design structures (PDS) you have selected to keep is larger than the storage space provided. Choose fewer PDS to keep, or set the storage space to be larger than %1.0lf MBs.KWarning:This option is deprecated.Instead use -A to specify the timebound.
 Specify a number for -k option.
)Error:Failed to open input workload file.-Error: Use either -c or -X option, not both.
Unable to parse input file %s.
Partitions are not supported .
'Error on line %d, position %d in "%s".
Workload2Error: Server name exceeds maximum allowed length
8Error: Test Server name exceeds maximum allowed length 
1Error: File name exceeds maximum allowed length 
*Specify a valid number > 0 for -A option.
3Error: Database name exceeds maximum allowed length0Error: Invalid number of arguments for -f option.Error: Exceeds maximum allowed password length(Error: Exceeds maximum allowed user name0Error: Invalid number of arguments for -a optionaTuning online indexes is not supported in Microsoft SQL Server 7.0 or Microsoft SQL Server 2000.
ZTuning online Indexes requires Enterprise Edition of Microsoft SQL Server 2005 and later.
OWarning:Error message table already exists. Error messages will not be written.QWarning:Could not create error message table. Error messages will not be written.-Arguments passed in are incorrect.Check usage(Error: Could not connect to database %s User aborted sessionSuccess'Error: Out of memory parsing  input XML>Error: Failed to parse input XML. Check schema for correctness%Error: Could not load input XML file.3Error: Did not find opening element (DTAXML) in XMLDateTimeServerDatabase(s) to tune
Workload fileWorkload tableMaximum tuning timeTime taken for tuning(Minimum improvement percentage specified Estimated percentage improvement%Maximum space for recommendation (MB)Space used currently (MB)!Space used by recommendation (MB)6Error: The specified workload (file or table) is emptyDaysDayKError: Failed to parse input XML. Check DTAInput hierarchy for correctness.OError: Failed to parse input XML. Arguments specified in input XML was invalid.�Error: Failed to parse input XML. Could not parse Server type specified.In USC make sure that -S option matches the server name specified via XML�Error: Failed to parse input XML. Could not parse Database type specified.In USC make sure that the database names in the argument and that specified in Configuration type match?Error: Failed to parse input XML. Could not parse Workload type>Error: Failed to parse input XML. Could not parse Options type0Error: Invalid analysis report options specified2Error: Feature set options specified are incorrectSError : Indexed views are not supported when the feature set is online indexes only*Error: Invalid number of queries specified'Error: Time bound specified is invalid.)Error: Storage bound specified is inavlid'Error: Max columns specified is invalidGError: Failed to parse input XML. Could not parse Schema type specifiedMError: Failed to parse input XML. Could not parseConfiguration type specifiedEError: Failed to parse input XML. Could not parseTable type specifiedEError: Failed to parse input XML. Could not parse View type specifiedQError: Failed to parse input XML.  Could not parse Recommendation  type specifiedIError: Failed to parse input XML.  Could not parse Create  type specifiedFError: Failed to parse input XML.  Could not parse Drop type specifiedGError: Failed to parse input XML.  Could not parse Index type specifiedHError: Failed to parse input XML.  Could not parse Column type specifiedLError: Failed to parse input XML.  Could not parse Statistics type specifiedQError: Failed to parse input XML.  Could not parse PartitionScheme type specifiedTError: Failed to parse input XML.  Could not parse PartitionFunction  type specifiedVError: Cannot create shell database. Check tuning log table, if specified, for details%Error: Invalid Drop options specified(Error: Expected text in the node element Error: Failed to parse input XML%Error: Failed to generate output XML.5Error: Failed to create Output element in output XML.4Error: Failed to create Header element in output XMLView Id	View NameEvent StringWeightCurrent Statement CostRecommended Statement Cost�Failed to simulate heap for Table ''%s'' specified in input configuration. Simulating heaps for tables requires SQL Server 2005 and laterNumber of statements (Current)"Number of statements (Recommended)FError: Failed to parse input XML.  Could not parse Heap type specified3Error: Failed to create Heap element in output XML.<Failed to create heap''%s'' specified in input configurationInlineQDrop Index ''%s'' is invalid with absolute mode specified in input configuration �The specified workload (file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StmtStarting or SP:StmtCompleted for workload trace file or table�The specified workload (file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting or RPC:Completed for workload trace file or table<Error: Failed to create Configuration element in output XML.6Error: Failed to create Summary element in output XML.5Error: Failed to create Detail element in output XML.7Error: Failed to create Database element in output XML.5Error: Failed to create Schema element in output XML.4Error: Failed to create Table element in output XML.3Error: Failed to create View element in output XML.4Error: Failed to create Index element in output XML.9Error: Failed to create Statistics element in output XML.=Error: Failed to create Recoomendation element in output XML.$Error: Server to tune not specified.sError: The tuning option specified is not valid. Make sure that the manageability modes are either Tune or EvaluateBError: This option is supported only in input recommendation mode.(Error: Invalid input file name specified)Error: Invalid output file name specified0Error: Could not load input XML file for parsing@Error: Can not get minimal database information in allotted timegError: An invalid configuration was specified as input. Check if the specifed configuration is correct.EError: Value for minimum improvement option should be less than 100.
HoursHourMinutesMinuteeError: Logged in user does not have enough permissions to tune one or more of the databases specified0Error: Tuning single user database not supportedELogin failed on test server. Check user name (-U) and password (-P).
)Login failed for user %s on test server.
FError: Could not connect to selected default database on tuned server
EError: Could not connect to selected default database on test server
CError: Could not refresh statistics and table sizes on test server
1Error : Could not connect to the specified server$Error : SQL Express is not supportedThe minimum storage space required for the selected physical design structures exceeds the default storage space selected by Database Engine Tuning Advisor. Either keep fewer physical design structures, or increase the default storage space to be larger than at least %1.0lf MB.Number of events in workloadNumber of events tunedNumber of statements tuned!Events specified to be tuned (-n)*Percent SELECT statements in the tuned set*Percent INSERT statements in the tuned set*Percent DELETE statements in the tuned set*Percent UPDATE statements in the tuned set+Number of indexes recommended to be created4Number of indexes on views recommended to be created.Number of statistics recommended to be created7Number of partition functions recommended to be created5Number of partition schemes recommended to be created+Number of indexes recommended to be dropped4Number of indexes on views recommended to be droppedXServer ''%s'' specified in input configuration is invalid. Specify the right server name\Database ''%s'' specified in input configuration is invalid. Specify the right database nameXSchema ''%s'' specified in input configuration is invalid. Specify the right schema nameVTable ''%s'' specified in input configuration is invalid. Specify the right table nameZNo columns have been specified for index in the configuration. Specify at least one columnoIndex ''%s'' specified in input configuration cannot be dropped because it is a constraint or primary XML indexiIndex ''%s'' specified in input configuration cannot be dropped because it does not exist in the databasesIndex ''%s'' specified in input configuration cannot be created because a duplicate index with the same name existsFTable ''%s'' specified in input configuration was not selected to tuneXColumn ''%s'' specified in input configuration is invalid. Specify the right column name>Failed to create index ''%s'' specified in input configurationEFailed to create indexed view ''%s'' specified in input configurationqView ''%s'' specified in input configuration cannot be created because a duplicate view with the same name existsIFailed to create partition scheme ''%s'' specified in input configuration�Partition scheme ''%s'' specified in input configuration cannot be created because a duplicate partition scheme with the same name existsKFailed to create partition function ''%s'' specified in input configuration�Partition function ''%s'' specified in input configuration cannot be created because a duplicate partition function with the same name existsEFailed to create statitistics ''%s'' specified in input configuration}Statistics ''%s'' specified in input configuration cannot be created because a duplicate statistics with the same name exists]Index ''%s'' specified in input configuration references non existent partition scheme ''%s''bPartition scheme ''%s'' specified in input configuration references non existent file group ''%s''jPartition scheme ''%s'' specified in input configuration references non existent partition function ''%s''WIndex ''%s'' specified in input configuration references non existent file group ''%s''CConfiguration specified in input is invalid. Table ''%s'' is a heapbConfiguration specified in input is invalid. Table ''%s'' has a preexisting clustered index ''%s''Index ''%s'' specified in input is invalid for the selected partitioning strategy because it doesnt meet alignment requirementseView ''%s'' specified in input cannot be materialized because it does not have a base clustered index8View ''%s'' specified in input could not be materialized	ClusteredColumn NameCost DecreasedCost Increased
Cost Range
Database Name/Server could not parse filter definition ''%s''%Filtered indexes are not supported .
FilteredFilter DefinitionBFiltered indexes option cannot be selected for this tuning option./Workload could not be extracted from plan cache4Plan cache does not contain any valid events to tune_Error: Could not start DTA tuning. A DTA tuning session is already running on the SQL instance..Error: The specified unit test does not exist.&Error: The specified unit test failed.KError: Columnstore index not supported for server versions before SQL 2016.NError: Recommend columnstore option not compatible with another option passed.+Error: Unable to create wizard.result file.GError: Invalid value set for total number of indexes in recommendation.9Error: Invalid value set for number of indexes per table.
Index Name	No ChangeNumber of referencesNumber of RowsNumber of StatementsTable/View NamePercent Improvement
Percent UsageSchema NameStatement IdStatement StringStatement TypeIndex Size (MB)
Table NameUniqueView DefinitionDError: Invalid value set on minimum improvement threshold per index.8Error: Invalid value set on number of processors to use.,Error: Data package directory not specified.#Error: Log directory not specified.#Error: Unable to initialize XEvent.<Error: Invalid value set on last N hours of queries to tune.BError: Unable to tune multiple databases for query store workload.;Error: Query store is not enabled for the database to tune.&Error: Invalid Cleanup mode specified..Error: Invalid Cleanup mode timeout specified.3Error: Invalid Cleanup mode max timeouts specified.<Error: Invalid Cleanup abort after timeout option specified.8Error: Workload could not be extracted from query store.RError: Recommend Aligned Indexes option not compatible with another option passed.UError: UsePlanCacheQueryForQDSError option can only be used for query store workload.QError: DisableRewriteBulkInsert option can only be used for query store workload.?Error: Invalid value set on last N hours of stats to aggregate.:Error: Invalid value set on minimum query execution count.UError: LastNHoursOfStatsToAggregate option can only be used for query store workload.OError: MinQueryExecutionCount option can only be used for query store workload.JError: NoHintForQDSQuery option can only be used for query store workload.LError: JoinHintForQDSQuery option can only be used for query store workload.cError: Could not start DTA tuning. A DTA tuning session is already running on the logical database.^Error: DisableProcessTableAndScalarFunctions option can only be used for query store workload.HError: Invalid value set on interval in seconds for resource monitoring.QError: Invalid value set on interval in seconds for querying job resource limits.FError: Invalid value set for maximum width of multi-column statistics.DError: Invalid value set for UseRemoteScanRelOpForBulkInsert option.XError: UseRemoteScanRelOpForBulkInsert option can only be used for query store workload.IError: Invalid value set for UseMissingIndexForAutopilotableQuery option.BError: Command line argument only available for tuning PostgreSQL.AError: Connection option argument exceeds maximum allowed length.)Error: Unable to enable HypoPG extension.FError: Filtered indexes currently not supported for tuning PostgreSQL.TError: Specified partitioning options currently not supported for tuning PostgreSQL.?Error: Columnstore indexes not supported for tuning PostgreSQL.KError: Specified 'feature set' options not supported for tuning PostgreSQL.MError: Specified 'keep existing' options not supported for tuning PostgreSQL.7Error: Auto-index mode mandatory for tuning PostgreSQL.5Error: Tuning of specified server type not supported.�4VS_VERSION_INFO������?�StringFileInfo�040904B0PlatformNT�ILegalTrademarksMicrosoft SQL Server is a registered trademark of Microsoft Corporation. CommentsSQL&GoldenBitsTrueLCompanyNameMicrosoft CorporationXFileDescriptiondtaengine Resource Filex,FileVersion2019.0150.2000.05 ((SQLServer).190924-2033)4
InternalNamedtaengined LegalCopyrightMicrosoft. All rights reserved.DOriginalFilenameDTAEngine.rllJProductNameMicrosoft SQL Server<ProductVersion15.0.2000.5DVarFileInfo$Translation	�<?xml version='1.0' encoding='UTF-8' standalone='yes'?>
<assembly xmlns='urn:schemas-microsoft-com:asm.v1' manifestVersion='1.0'>
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel level='asInvoker' uiAccess='false' />
      </requestedPrivileges>
    </security>
  </trustInfo>
</assembly>
8?0�?+	*�H��
��?0�?10	+0L
+�7�>0<0
+�70	���0!0	+�g����05c�g�9ZvH
���0��0� 3æ��;\�0
	*�H��
0w10	UUS10U
Washington10URedmond10U
Microsoft Corporation1!0UMicrosoft Time-Stamp PCA0
181024210734Z
200110210734Z0��10	UUS10U
Washington10URedmond10U
Microsoft Corporation1%0#UMicrosoft America Operations1&0$UThales TSS ESN:12BC-E3AE-74EB1%0#UMicrosoft Time-Stamp Service0�"0
	*�H��
�0�
����+��)���w�ی�ؠSM��g	@*�!L�Ҁe�ι>y�~�3ڭ 
���
����~���>x�UtE�
y���Z���x:�Iq$|�݁�����x�������T���|��/e����h�_��f��<%Oζ:�ʨ,�h��[J���r���^�	�C�i�e��Y�D�WK�e��7�ިʥ���2e,J�('CyU��ɖ|�E�s�ϭ�(/9�&q�>�P�}B�F G˰K��	0�0U<O|�oW��
^n����|�N�0U#0�#4��RFp
�@�v��+��5�0TUM0K0I�G�E�Chttp://crl.microsoft.com/pki/crl/products/MicrosoftTimeStampPCA.crl0X+L0J0H+0�<http://www.microsoft.com/pki/certs/MicrosoftTimeStampPCA.crt0U%0
+0
	*�H��
�2Q�ӹ���ʴv�7(ȷ��p
�Ӎ�i����U4�������Z&���"�I�f��ƍ��W0�6�FTv� �$���� ��^z������f�	`ѱ����9�/���0�b��<%m�ɤ~0�����W���s7��{��=�L	Ws\9� ��r��Le/i�fDѼ�-噯;{h�:NfP���B��澡Oea�~����T�{۞��`��s�L�&���
����/�X<�BNh����$0��0�ߠ3�|��`����0
	*�H��
0y10	UUS10U
Washington10URedmond10U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA0
190502212033Z
200502212033Z0t10	UUS10U
Washington10URedmond10U
Microsoft Corporation10UMicrosoft Corporation0�"0
	*�H��
�0�
����k*)�i퐭��՗+��IJ���E �Z:p�v�@�'�����Z3qʱ����^(��D�P��sY�Ep;��o�\ �(1���䱚GM<�J��f�,�U�muј^�;�F́X\��N��W���
%�%W�2�J~A��u}�ߋ�Nf
����L��,���i�HqxWw��� ����m.������:d�4���̑����yϼ̕}|�@�n���L`�!mW5�(ӭ
�܉����{0�w0U%0
+0Up���mu���*:'YG�0TUM0K�I0G1-0+U$Microsoft Ireland Operations Limited10U
229803+4541060U#0��p�R4���'���ؽ�mGN0bU[0Y0W�U�S�Qhttp://crl.microsoft.com/pki/crl/products/Microsoft%20Code%20Signing%20PCA(2).crl0f+Z0X0V+0�Jhttp://www.microsoft.com/pki/certs/Microsoft%20Code%20Signing%20PCA(2).crt0
	*�H��
�N��{�b~�=�G@������Q��Q��F�!e^bm�5�Z�꼣�h����9qp�g�&��}ZJI�δ7�909��yD�oTRIn>����X�~�G�L"x���3'��
L8*��D�e��h��JY+{p���X�6����-'����ۦ����b�شx�/.�9��5t��"5>��'���`��$yN`҄�u��J
E�:|�M�:�g�Q��#'$غ�Ȳ�1��
���A\e�0��0���
a5E?0
	*�H��
0_10
	�&���,dcom10
	�&���,d	microsoft1-0+U$Microsoft Root Certificate Authority0
180920174200Z
210509232813Z0y10	UUS10U
Washington10URedmond10U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA0�"0
	*�H��
�0�
��������F��fɧ]
~�=���1��ԅ���2����A��7��|W����Z`�1
a�"�|V4b�^Z�.�@*�=�aD؎�6Y�c���������_+�P_�	Ґ頷,DA�eR7,�����,���c�Q����8^|�����)@ߔv*�e��m�'�§���j�Ƨ�f�hw�^��	�R�sQ�*�Q#�I�����P^�Oc?�/���M+q�Y('M��^0�Z0U�0�0U�p�R4���'���ؽ�mGN0U�0	+�70#	+�7<����Up�iB���8��W0	+�7
SubCA0U#0���`@V'��%�*�
S�Y�0PUI0G0E�C�A�?http://crl.microsoft.com/pki/crl/products/microsoftrootcert.crl0T+H0F0D+0�8http://www.microsoft.com/pki/certs/MicrosoftRootCert.crt0
	*�H��
�={�.�6�B�UO�����9
������"˅yC�����5�˅�->M�Q�$����c������
m.U5�,yaƶM�V&��e��.�<He��D���4
ީ�c����d�U�����e~���0���;��u�]π��q�B��ғ��T�|!�싼�~�)	P�+:�=��W��8��ߚ8Ё�'��y;4���"�e�La<��.2h�)���v�߆)�
㴳�L�gZT�Y'��HBn1L�L��h�#J�T㔕l����(�YD���򨅳=�*�7��=7�8�*G�gY�-�z�w��JI*t7��/��C-�2���LI�����G辞�1d������uI{db��~��g\AG�N6-�7�k�?R����"%�9[�Ez��������d�΋��}��mW[*{ol~�C�
�]�gԋ���Nz���߽4����gn�d��L�3��QpU�\}�ݧU��ǣ�Fz��D��c�}�"u0�0��
ah40
	*�H��
0_10
	�&���,dcom10
	�&���,d	microsoft1-0+U$Microsoft Root Certificate Authority0
070403125309Z
210403130309Z0w10	UUS10U
Washington10URedmond10U
Microsoft Corporation1!0UMicrosoft Time-Stamp PCA0�"0
	*�H��
�0�
���l���H�*|k.���řQ#P���N$�ѧ�L�t0 ��T�I�B���\0&�k�{�������3$����g,�{���N���#Sʍ�E�	�^�аB���l�g�(�FS����(F.'��OUR�,�j��0n��Y0��~֗��!�~����SmR�E��HJGf')f�����J����G	����Ux��P�0F��#F��-T��xA1w̛�;���a�
������"KT���� ���0��0U�0�0U#4��RFp
�@�v��+��5�0U�0	+�70��U#��0�����`@V'��%�*�
S�Y䤡c�a0_10
	�&���,dcom10
	�&���,d	microsoft1-0+U$Microsoft Root Certificate Authority�y��J���LsX�.e0PUI0G0E�C�A�?http://crl.microsoft.com/pki/crl/products/microsoftrootcert.crl0T+H0F0D+0�8http://www.microsoft.com/pki/certs/MicrosoftRootCert.crt0U%0
+0
	*�H��
����\D6�鴭w��yQM�.tq[m�ξ{��.���mb�W�N P��z@�W��X�����x���;�⾏
ȍH��S�O����jՀ���/��*$"p����x�.
��p�N��k
r���9�g-�Q�b\?�� ?�2��1Jv1=+r��p=��
��
a��׸�N��5+^���}B{�Sr!�0ʻxe\[
����<�fX�-/���QV�p�l�9L�y��zb))4wb�=m���,��C���}�㤨t5�N�q#&v<R�qZFC����MgȟB��y!��0f�B^Ȟ"!�C�+��H�,��2�L&*H$_Z�V�ޙ��:o��'t�v!����'T��j=��va�n����G�o��2l���QȌ#�f���d=-�Y���!嵰�c�xT�A.P����x����uْ�u"/2�n�[�%����!��Oɚ�I�eo�}�i��n꧗�1)��[\�M��/�?.Uk���1�)0�)
0��0y10	UUS10U
Washington10URedmond10U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA3�|��`����0	+���0	*�H��
	1
+�70
+�710
+�70#	*�H��
	1c�E~���Ј�	6��0N
+�71@0>� �SQL Server 2019��http://www.microsoft.com0
	*�H��
��:`b���VTב�w�P��d��k�zڒ$4]
Io;b�};P�/x4�V*��Y�2�n��l=��[1�1��b�;08�ʣ���;h�b
��k]��&-�A�ۘi��}\�a�h�O�*�ow:Ϙ_X�V�eRW�1���&�B*ɦE
�Z��9�)%�^;���_�0U�iHs҅ߡ��+|�\��cI����#~C���:0�e`�ŘdS��.��5�I"�%=@Ar��c+�������n�3���&�0�$	*�H��
	1�0�0��0w10	UUS10U
Washington10URedmond10U
Microsoft Corporation1!0UMicrosoft Time-Stamp PCA3æ��;\�0	+�]0	*�H��
	1	*�H��
0	*�H��
	1
190924204021Z0#	*�H��
	1.+k�*G4On���_z�d�m0
	*�H��
�B���?;.�]9��Z����7~�Q�?�b{�,.�"��9���}�?���?�D2B�����f�
d�W��4Y��|���w����i��,�I��7��8�33�����o��j�C;j��)W.8|9�(���7wްq}�� q�/��j�R�?���Qqh��i�ڭ�����4�2���q�E�%��'�mwycI����ͤ�R�)�۝��1	�|N���Y�ϓ
ٖ��x�_�h0�$u
+�71�$e0�$a	*�H��
��$R0�$N10
	`�He0\
+�7�N0L0
+�70	���010
	`�He 9���S����K�b �v��mk�N��k�?��
�0�0��3R�@�PV�u�R0
	*�H��
0~10	UUS10U
Washington10URedmond10U
Microsoft Corporation1(0&UMicrosoft Code Signing PCA 20110
190502213746Z
200502213746Z0t10	UUS10U
Washington10URedmond10U
Microsoft Corporation10UMicrosoft Corporation0�"0
	*�H��
�0�
���������t�����ǔߖ�6�+�xL���5���oK��w�U��buAN��[�V7V�p�Cn�3(A6��~��/�k����>��\�6��ɳ����mJ�
�X�%y�D�����*��Or�}Y�Gڗ}tP����M�4dc�睘�*�!�U��}�W����P��e���.����lA ��+4�Ih��z%�x�tr�
����������c� �����i��Zai^���76q�3ހ����0�~0U%0
+�7L+0U��[�e�'f��5kE�0TUM0K�I0G1-0+U$Microsoft Ireland Operations Limited10U
230012+4541360U#0�Hnd�Pӂ�77"�m��u�0TUM0K0I�G�E�Chttp://www.microsoft.com/pkiops/crl/MicCodSigPCA2011_2011-07-08.crl0a+U0S0Q+0�Ehttp://www.microsoft.com/pkiops/certs/MicCodSigPCA2011_2011-07-08.crt0U�00
	*�H��
���D懾��,V�(�-����6@��m�z� 65�W��Q`[�F�y8��t�ھ$�&��C��=�d�R�m���(ߜ����S�]��!��h2]g����"���S��E��v���5@��7�8��t�=0|3e�?�O��%�vS�Y�������ޭ��b�����+O�yDT
�ͅC�8xQ#�+7&�T��",ۮ8��P���h�(�T@�`���T�l������T����G6~�ܷ�f��D�k�IB���	��m������r����aݽ��G>ƚ읶��2>�Ҁ\�����]��r!���K��Q!��(�V�&sLDt�>��QZ�M����W5X��)l�}F�G�M3�s4��O��8v�vT���:�e:����*Ɲ�N�
���hP���z?别2��fwǴ��#cU�b���ӧx�\Ȇ޽�e,��Ul*!����43=+�
�m����
 ��ސ��n���Iy/钒>
�0�z0�b�
a��0
	*�H��
0��10	UUS10U
Washington10URedmond10U
Microsoft Corporation1200U)Microsoft Root Certificate Authority 20110
110708205909Z
260708210909Z0~10	UUS10U
Washington10URedmond10U
Microsoft Corporation1(0&UMicrosoft Code Signing PCA 20110�"0
	*�H��
�0�
���r.��n��M4��X!�B*k�ZP��8I���7k���8��9�1BӉ
yd�~�`$l��I��h^�ߛS�
,ï٩+�z	�זY�`�fv�2R&/�PϳD�]��.u�h��m�:\��F��8d�nd5x��c-�@����
�\y�I)�
��w��=��+ZMV(�zr�:�I^���C���{�{��>��]�O��/Y�;-�3Xŷ>�2ӳ=����8~ҝ�,�NV�!5%�9ndS���#������_��R������!t%_(��'(8%�9J6�|���#��f�aj�(I�_��%]�!K>RĵW?$��z[/�#�p]QFw��Ἤ�_����UK�9��#I��D|E��rz�r�$߿F����Wۃ��MI0�����[���ݰf��{���K�I)�(��}g��bx_�/��W�\�w((��m�(�,�@7O��D��	L�ԥC/t��n�x X,]`��>O3�ڰ�ޞN�F�l���ֈ����0��0	+�70UHnd�Pӂ�77"�m��u�0	+�7
SubCA0U�0U�0�0U#0�r-:1�C�N���1�#�40ZUS0Q0O�M�K�Ihttp://crl.microsoft.com/pki/crl/products/MicRooCerAut2011_2011_03_22.crl0^+R0P0N+0�Bhttp://www.microsoft.com/pki/certs/MicRooCerAut2011_2011_03_22.crt0��U ��0��0��	+�7.0��0?+3http://www.microsoft.com/pkiops/docs/primarycps.htm0@+042 Legal_policy_statement. 0
	*�H��
�g򆥘�Ty.��tg"���c��B�}�y��e_.,>��r�m���?�
�;�G���i�c�"5��]e��}�FPU��|�K�<B�Rа�k�>.)��=�Եw�9��d�D=xz#�}��t����&F*�����h�h.��*?*kXIc	inZ�����F+�;н5�n%��'������(��M=����2���-�[��I9
�
�ƭ�����QEX82q'��'��,: i��Y�hno��t�@�*(>�?Mf��M��o}E�v�@*e��]Rb��c6��������'�ͥ���]cA�[��>w��u�aa������A�(��e/��\��֡F�Y�	KH���)ű��?Ew5�Ң��zz"�H���G#�����k�K���ux��7�yK�ֳ#hu������i;��0�L���umc�=ѝVNO��W"�x2!z�A
�?��̤]����WqVNHE�Bɛv[
�Hk�y���mmj�Rs
zP�1�O0�K0��0~10	UUS10U
Washington10URedmond10U
Microsoft Corporation1(0&UMicrosoft Code Signing PCA 20113R�@�PV�u�R0
	`�He���0
*�H��
	10	*�H��
	1
+�70
+�710
+�70/	*�H��
	1" L�s?]�&<�r��=ηPƈ�x	�����ӻ�0N
+�71@0>� �SQL Server 2019��http://www.microsoft.com0
	*�H��
�7��xix��'��F#�-��PvH~���@N_��C(%�����w�N�A�{�
��i*[��N,�-��n���d�~P�I0��=�;9�n���~�;l�/͘�m��k6���SS<V�5[��x�f���|^ث V��
cQU���vc�G�s���s��f{���r/(��f�q/k��$�Ә2� ,&��z{>t�>NB�Α6o����C�f��5�׆U=��k����k4�o��0��
+�71��0��	*�H��
���0��10
	`�He0�X*�H��
	��G�C0�?
+�Y
010
	`�He ��3��^���V��k�+�$Dt�\�&�)��g]��"��20190924204023.278Z0��Ԥ��0��10	UUS10U
Washington10URedmond10U
Microsoft Corporation1)0'U Microsoft Operations Puerto Rico1&0$UThales TSS ESN:B1B7-F67F-FEC21%0#UMicrosoft Time-Stamp Service��"0��0�ݠ3�N+x�*'�0
	*�H��
0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 20100
190906204117Z
201204204117Z0��10	UUS10U
Washington10URedmond10U
Microsoft Corporation1)0'U Microsoft Operations Puerto Rico1&0$UThales TSS ESN:B1B7-F67F-FEC21%0#UMicrosoft Time-Stamp Service0�"0
	*�H��
�0�
���Wޮ�P>��}
�ż�[v;,�%P�X��}m���@��k%��]�u�ο�%{�GR;�k�=rr=���9m
�q�N�`�܁Q���.<|�G[��;8�
���u	�gB�Mֱ�
�v�q��d [�~k�}��cJ��v$�i�=R�2D�qOX����v���K�[��%������{�qJX��]e��0^Q��ނ/O��#�{�6.1�R�z�Q�W+u�
BS�'C����,'g U�v�Z�6$K��0�0U�6-������e&�<��oZ0U#0��c:\�1��C{|F�3hZ�mU0VUO0M0K�I�G�Ehttp://crl.microsoft.com/pki/crl/products/MicTimStaPCA_2010-07-01.crl0Z+N0L0J+0�>http://www.microsoft.com/pki/certs/MicTimStaPCA_2010-07-01.crt0U�00U%0
+0
	*�H��
�|5��技e�!Hl�ًښ�U��q�S�L�3܂�*�N]�F������	]]q^|}<�7m�Kr�/�WWf�4r<�b�&�,qK�8��h�p��
��fY��Cz�`V�}[��.%k{`�|��-�(��S���y���Z�)�?&�l�O�tD<+㟆�*[X!f5
g�I��=��k�v�"	��m��SH(�ƈ�����z��%�������2t�4�耛A��v��@n�;K=�fAIm�0�q0�Y�
a	�*0
	*�H��
0��10	UUS10U
Washington10URedmond10U
Microsoft Corporation1200U)Microsoft Root Certificate Authority 20100
100701213655Z
250701214655Z0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 20100�"0
	*�H��
�0�
��
�w�: ����i�ktTե
����|hK,_���a�v�>f+[�S'1A��	�|a0Y�0D�`��TC�M�8�Bݓ��s0W&��E
��G�Ϳ$`2X`F�XG�2�tag�_�T�ϓL��Ħ]�an(������a�F�'$gr!��Kd�Pb�]�w=�Wu���BM@Q��>g�f��D~��K�n�ʱ��z*
1��N���5�x���<�/D����d��� 	�x����D]^�O"M���0��0	+�70U�c:\�1��C{|F�3hZ�mU0	+�7
SubCA0U�0U�0�0U#0��Vˏ�\bh�=��[�Κ�0VUO0M0K�I�G�Ehttp://crl.microsoft.com/pki/crl/products/MicRooCerAut_2010-06-23.crl0Z+N0L0J+0�>http://www.microsoft.com/pki/certs/MicRooCerAut_2010-06-23.crt0��U ���0��0��	+�7.0��0=+1http://www.microsoft.com/PKI/docs/CPS/default.htm0@+042 Legal_Policy_Statement. 0
	*�H��
��Q
���?�q=���!o���1����Wm0���f�j���x�Ǩ�%��kTW+Q�D��S���`�v�@@A��\�\�^5�$VKt�Bȯ���7}Z?�yJ�R�8�/y�e٩�k����zoK0D$"<�����Y)����p�2J'�U�/���3�b_��W@��Ιf���jb��J�&�9�Jqc�{!mÜ��<�}�j�x�m���8ؔ�ƥ
������B�����"8 �%�d��~cY%z.9Wv�q����a�˚�G����ͧ��}���;q	�]t�"aڰPo�����1��:�eGx�H���r~akow��˧	�9����؂�r�����*T9�[��
U�z�s;�-��3.)��/T'!�ȬN�(���ۖ��B���AM�*��f0ӻt2K�c{���/�!�Y���D<�Pqס��U�i�W���0����M�
�]O�8/��X.�
�P5��	'ճ~5��6��_��t�I���0��0����Ԥ��0��10	UUS10U
Washington10URedmond10U
Microsoft Corporation1)0'U Microsoft Operations Puerto Rico1&0$UThales TSS ESN:B1B7-F67F-FEC21%0#UMicrosoft Time-Stamp Service�%
0	+k\:6x:��dIFU�V�ڨlڳ���0�ۤ��0��10	UUS10U
Washington10URedmond10U
Microsoft Corporation1)0'U Microsoft Operations Puerto Rico1'0%UnCipher NTS ESN:4DE9-0C5E-3E091+0)U"Microsoft Time Source Master Clock0
	*�H��
�4�10"20190925005617Z20190926005617Z0w0=
+�Y
1/0-0
�4�10
r�0�0
�6�06
+�Y
1(0&0
+�Y
�
0�`�
0� 0
	*�H��
�aK��U���,e����\�0**��r\վ���r��خ��Ϲ�*:Y6m7��Nv�:T5�����LAWI����_�*�k�(��2������@҆�E^�=4�(���9���?o����u�ȇ�|`�s�9�o����H�E�����Zm2/	���J�?��X�Z�
��c����К�mC��E
�=g���r��L`aƪ,]�O����2�T�ѣ�5hv�W�f�R]�WP���S��Z�9�I��1��0��0��0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 20103�N+x�*'�0
	`�He��20	*�H��
	1
*�H��
	0/	*�H��
	1" x<C��!��a�*ɪ�#�֜�4<�9z�u��T�0��*�H��
	1��0��0��0��k\:6x:��dIFU�V�ڨlڳ0��0���~0|10	UUS10U
Washington10URedmond10U
Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 20103�N+x�*'�0P�Z�|�\��s���90
	*�H��
�z���z�`1<IB�4G���v�S%�>̻�C[W����He�	U�#e9�6���P��<r�b�M�z�RŮP
��7�>����T+3��_�%�|:��_���n�G��E���������9�
�?�@��`l@%bU���K��&F9` U"����X}wk.;�>�6��?�_M���ڥ���2���t�[����YI3ufV�B�c��3��u��yY�t���%�}B-JMd��1�]#R�_
Youez - 2016 - github.com/yon3zu
LinuXploit