Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)

Question

Hello,

This is the first time I am using this forum. I am stuck and need some help. I received this error message both on SQL 2005 and 2008. I am currently using SQL 2008 DEV for testing purposes and I am experiencing this error message when trying to connect to Database Engine Tuning Advisor. I am using the SA account so I should not have any security issues. Also this is a fresh install of SQL 2008 DEV on a fresh install of Windows XP Pro with all SP. Can some please help.

Error:

"Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)"

===================================

Failed to open a new connection.

===================================

Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
   at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
   at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
   at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
   at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
   at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

 

Answer 1

Try the workaround given in the below link

http://www.lifeasbob.com/2008/05/23/DTAFailedToInitializeMSDBDatabaseForTuning.aspx

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 2

I created the below MSDB SP and Tables. Now the issue is resolved.

http://www.devnewsgroups.net/sqlservertools/t67379-database-engine-tuning-advisor.aspx 

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[sp_DTA_start_xmlprefix]    Script
Date: 12/31/2008 10:56:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_start_xmlprefix]
as
begin
    declare @startTags nvarchar(128)
    set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>'
    select @startTags
end
GO
/****** Object:  Table [dbo].[DTA_input]    Script Date: 12/31/2008 10:56:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_input](
    [SessionName] [sysname] NOT NULL,
    [SessionID] [int] IDENTITY(1,1) NOT NULL,
    [TuningOptions] [ntext] NOT NULL,
    [CreationTime] [datetime] NOT NULL DEFAULT (getdate()),
    [ScheduledStartTime] [datetime] NOT NULL DEFAULT (getdate()),
    [ScheduledJobName] [sysname] NOT NULL DEFAULT (''),
    [InteractiveStatus] [tinyint] NOT NULL DEFAULT ((0)),
    [LogTableName] [nvarchar](1280) NOT NULL DEFAULT (''),
    [GlobalSessionID] [uniqueidentifier] NULL DEFAULT (newid()),
PRIMARY KEY CLUSTERED
(
    [SessionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_progress]    Script Date: 12/31/2008
10:57:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_progress](
    [ProgressEventID] [int] IDENTITY(1,1) NOT NULL,
    [SessionID] [int] NULL,
    [TuningStage] [tinyint] NOT NULL DEFAULT ((0)),
    [WorkloadConsumption] [tinyint] NOT NULL,
    [EstImprovement] [int] NOT NULL DEFAULT ((0)),
    [ProgressEventTime] [datetime] NOT NULL DEFAULT (getdate()),
    [ConsumingWorkLoadMessage] [nvarchar](256) NULL,
    [PerformingAnalysisMessage] [nvarchar](256) NULL,
    [GeneratingReportsMessage] [nvarchar](256) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_output]    Script Date: 12/31/2008
10:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_output](
    [SessionID] [int] NOT NULL,
    [TuningResults] [ntext] NOT NULL,
    [StopTime] [datetime] NOT NULL DEFAULT (getdate()),
    [FinishStatus] [tinyint] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
    [SessionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_tuninglog]    Script Date: 12/31/2008
11:01:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_tuninglog](
    [SessionID] [int] NOT NULL,
    [RowID] [int] NOT NULL,
    [CategoryID] [nvarchar](4) NOT NULL,
    [Event] [ntext] NULL,
    [Statement] [ntext] NULL,
    [Frequency] [int] NOT NULL,
    [Reason] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_database]    Script Date:
12/31/2008 10:57:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_database](
    [DatabaseID] [int] IDENTITY(1,1) NOT NULL,
    [SessionID] [int] NOT NULL,
    [DatabaseName] [sysname] NOT NULL,
    [IsDatabaseSelectedToTune] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [DatabaseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_query]    Script Date: 12/31/2008
10:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_query](
    [QueryID] [int] NOT NULL,
    [SessionID] [int] NOT NULL,
    [StatementType] [smallint] NOT NULL,
    [StatementString] [ntext] NOT NULL,
    [CurrentCost] [float] NOT NULL,
    [RecommendedCost] [float] NOT NULL,
    [Weight] [float] NOT NULL,
    [EventString] [ntext] NULL,
    [EventWeight] [float] NOT NULL,
CONSTRAINT [DTA_reports_query_pk] PRIMARY KEY CLUSTERED
(
    [SessionID] ASC,
    [QueryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_querydatabase]    Script Date:
12/31/2008 11:00:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_querydatabase](
    [QueryID] [int] NOT NULL,
    [SessionID] [int] NOT NULL,
    [DatabaseID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_table]    Script Date: 12/31/2008
11:00:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_table](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseID] [int] NOT NULL,
    [SchemaName] [sysname] NOT NULL,
    [TableName] [sysname] NOT NULL,
    [IsView] [bit] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
    [TableID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_partitionfunction]    Script Date:
12/31/2008 10:58:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_partitionfunction](
    [PartitionFunctionID] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseID] [int] NOT NULL,
    [PartitionFunctionName] [sysname] NOT NULL,
    [PartitionFunctionDefinition] [ntext] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [PartitionFunctionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_partitionscheme]    Script Date:
12/31/2008 10:59:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_partitionscheme](
    [PartitionSchemeID] [int] IDENTITY(1,1) NOT NULL,
    [PartitionFunctionID] [int] NOT NULL,
    [PartitionSchemeName] [sysname] NOT NULL,
    [PartitionSchemeDefinition] [ntext] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [PartitionSchemeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_tableview]    Script Date:
12/31/2008 11:01:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_tableview](
    [TableID] [int] NOT NULL,
    [ViewID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_querytable]    Script Date:
12/31/2008 11:00:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_querytable](
    [QueryID] [int] NOT NULL,
    [SessionID] [int] NOT NULL,
    [TableID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_index]    Script Date: 12/31/2008
10:58:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_index](
    [IndexID] [int] IDENTITY(1,1) NOT NULL,
    [TableID] [int] NOT NULL,
    [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)),
    [Storage] [float] NOT NULL,
    [NumRows] [int] NOT NULL,
    [IsRecommended] [bit] NOT NULL DEFAULT ((0)),
    [RecommendedStorage] [float] NOT NULL,
    [PartitionSchemeID] [int] NULL,
    [SessionUniquefier] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [IndexID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_column]    Script Date: 12/31/2008
10:57:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_column](
    [ColumnID] [int] IDENTITY(1,1) NOT NULL,
    [TableID] [int] NOT NULL,
    [ColumnName] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ColumnID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_queryindex]    Script Date:
12/31/2008 11:00:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_queryindex](
    [QueryID] [int] NOT NULL,
    [SessionID] [int] NOT NULL,
    [IndexID] [int] NOT NULL,
    [IsRecommendedConfiguration] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_querycolumn]    Script Date:
12/31/2008 10:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_querycolumn](
    [QueryID] [int] NOT NULL,
    [SessionID] [int] NOT NULL,
    [ColumnID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DTA_reports_indexcolumn]    Script Date:
12/31/2008 10:58:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTA_reports_indexcolumn](
    [IndexID] [int] NOT NULL,
    [ColumnID] [int] NOT NULL,
    [ColumnOrder] [int] NULL,
    [PartitionColumnOrder] [int] NOT NULL DEFAULT ((0)),
    [IsKeyColumn] [bit] NOT NULL DEFAULT ((1)),
    [IsDescendingColumn] [bit] NOT NULL DEFAULT ((1))
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[sp_DTA_check_permission]    Script
Date: 12/31/2008 10:53:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_check_permission]
                @SessionID int
as
begin
    declare @retcode  int
    declare @dbname nvarchar(128)
    declare @sql nvarchar(256)
    declare @dbid int

    set nocount on

    -- Check if SA
    if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
    begin
        return(0)
    end

    -- if not yukon return
    if (patindex('%9.00.%',@@version) = 0)
    begin
        return (1)
    end
    -- declare and open a cursor and get all the databases specified in the input
    declare db_cursor cursor for
    select DatabaseName from DTA_reports_database
    where SessionID = @SessionID and IsDatabaseSelectedToTune  = 1
    -- open
    open db_cursor
    -- fetch first db name
    fetch next from db_cursor
    into @dbname
    -- loop and get all the databases selected to tune
    while @@fetch_status = 0
    begin
        -- build use db string
        select  @dbid = DB_ID(@dbname)

        -- 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
    return(0)
end
GO
/****** Object:  StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml]   
Script Date: 12/31/2008 10:54:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_index_usage_helper_xml]
    @SessionID        int,
    @IsRecommended    int
as   
begin
select 1            as Tag,
        NULL          as Parent,
        '' as [IndexUsageReport!1!!ELEMENT],
        case when @IsRecommended = 1 then 'false'
        else 'true' end as [IndexUsageReport!1!Current],
        NULL as [Database!2!DatabaseID!hide],
        NULL  as [Database!2!Name!ELEMENT] ,
        NULL  as [Schema!3!Name!ELEMENT] ,
        NULL as [Table!4!TableID!hide],
        NULL as [Table!4!Name!ELEMENT],
        NULL as [Index!5!IndexID!hide],
        NULL as [Index!5!Name!ELEMENT],
        NULL as [Index!5!NumberOfReferences!ELEMENT],
        NULL as [Index!5!PercentUsage!ELEMENT]
    union all
select 2            as Tag,
        1          as Parent,
        NULL as [IndexUsageReport!1!!ELEMENT],
        NULL as [IndexUsageReport!1!Current],
        D.DatabaseID as [Database!2!DatabaseID!hide],
        D.DatabaseName  as [Database!2!Name!ELEMENT] ,
        NULL  as [Schema!3!Name!ELEMENT] ,
        NULL as [Table!4!TableID!hide],
        NULL as [Table!4!Name!ELEMENT],
        NULL as [Index!5!IndexID!hide],
        NULL as [Index!5!Name!ELEMENT],
        NULL as [Index!5!NumberOfReferences!ELEMENT],
        NULL as [Index!5!PercentUsage!ELEMENT]
    from [msdb].[dbo].[DTA_reports_database] as D
    where
    D.SessionID = @SessionID and
    D.DatabaseID in
    (select D.DatabaseID from
            [msdb].[dbo].[DTA_reports_queryindex] as QI,
            [msdb].[dbo].[DTA_reports_index] as I,
            [msdb].[dbo].[DTA_reports_table] as T,
            [msdb].[dbo].[DTA_reports_database] as D
            where
            QI.IndexID = I.IndexID  and
            I.TableID = T.TableID and
            T.DatabaseID = D.DatabaseID and
            D.SessionID = @SessionID and
            QI.IsRecommendedConfiguration = @IsRecommended
            GROUP BY D.DatabaseID)
    union all
select 3            as Tag,
        2          as Parent,
        NULL as [IndexUsageReport!1!!ELEMENT],
        NULL as [IndexUsageReport!1!Current],
        D.DatabaseID as [Database!2!DatabaseID!hide],
        D.DatabaseName  as [Database!2!Name!ELEMENT] ,
        R.SchemaName  as [Schema!3!Name!ELEMENT] ,
        NULL as [Table!4!TableID!hide],
        NULL as [Table!4!Name!ELEMENT],
        NULL as [Index!5!IndexID!hide],
        NULL as [Index!5!Name!ELEMENT],
        NULL as [Index!5!NumberOfReferences!ELEMENT],
        NULL as [Index!5!PercentUsage!ELEMENT]
    from [msdb].[dbo].[DTA_reports_database] as D,
    (
        select D.DatabaseID,T.SchemaName from
        [msdb].[dbo].[DTA_reports_queryindex] as QI,
        [msdb].[dbo].[DTA_reports_index] as I,
        [msdb].[dbo].[DTA_reports_table] as T,
        [msdb].[dbo].[DTA_reports_database] as D
        where
        QI.IndexID = I.IndexID  and
        I.TableID = T.TableID and
        T.DatabaseID = D.DatabaseID and
        QI.IsRecommendedConfiguration = @IsRecommended and
        D.SessionID = @SessionID
        GROUP BY D.DatabaseID,T.SchemaName
    ) R
    where
    D.SessionID = @SessionID and
    D.DatabaseID = R.DatabaseID
union all

select 4            as Tag,
        3          as Parent,
        NULL as [IndexUsageReport!1!!ELEMENT],
        NULL as [IndexUsageReport!1!Current],
        D.DatabaseID as [Database!2!DatabaseID!hide],
        D.DatabaseName as [Database!2!Name!ELEMENT] ,
        R.SchemaName  as [Schema!3!Name!ELEMENT] ,
        R.TableID as [Table!4!TableID!hide],
        T.TableName as [Table!4!Name!ELEMENT],
        NULL as [Index!5!IndexID!hide],
        NULL as [Index!5!Name!ELEMENT],
        NULL as [Index!5!NumberOfReferences!ELEMENT],
        NULL as [Index!5!PercentUsage!ELEMENT]

    from    [msdb].[dbo].[DTA_reports_database] as D,
            [msdb].[dbo].[DTA_reports_table] as T,
    (
        select D.DatabaseID,T.SchemaName,T.TableID from
        [msdb].[dbo].[DTA_reports_queryindex] as QI,
        [msdb].[dbo].[DTA_reports_index] as I,
        [msdb].[dbo].[DTA_reports_table] as T,
        [msdb].[dbo].[DTA_reports_database] as D
        where
        QI.IndexID = I.IndexID  and
        I.TableID = T.TableID and
        T.DatabaseID = D.DatabaseID and
        D.SessionID = @SessionID and
        QI.IsRecommendedConfiguration = @IsRecommended
        GROUP BY D.DatabaseID,T.SchemaName, T.TableID
    ) R
    where
    D.SessionID = @SessionID and
    D.DatabaseID = R.DatabaseID and
    R.TableID = T.TableID and
    T.DatabaseID = D.DatabaseID

union all
select 5            as Tag,
        4          as Parent,
        NULL as [IndexUsageReport!1!!ELEMENT],
        NULL as [IndexUsageReport!1!Current],
        D1.DatabaseID as [Database!2!DatabaseID!hide],
        D1.DatabaseName as [Database!2!Name!ELEMENT] ,
        T1.SchemaName  as [Schema!3!Name!ELEMENT] ,
        T1.TableID as [Table!4!TableID!hide],
        T1.TableName as [Table!4!Name!ELEMENT],
        I1.IndexID as [Index!5!IndexID!hide],
        I1.IndexName as [Index!5!Name!ELEMENT],
        R.Count as [Index!5!NumberOfReferences!ELEMENT],
        CAST(R.Usage as decimal(38,2))  as [Index!5!PercentUsage!ELEMENT]
        from
            [msdb].[dbo].[DTA_reports_database] as D1 ,
            [msdb].[dbo].[DTA_reports_index] as I1,
            [msdb].[dbo].[DTA_reports_table] as T1,
            (
                select D.DatabaseID,T.TableID ,
                        I.IndexID  ,SUM(Q.Weight) as Count,
                        100.0 *  SUM(Q.Weight) /
                        ( 1.0 * (    select    CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)
                                            else 1
                                            end   

                                    from [msdb].[dbo].[DTA_reports_query] as Q
                                    where Q.SessionID = @SessionID ))
            as Usage
        from
            [msdb].[dbo].[DTA_reports_index] as I   
            LEFT OUTER JOIN
            [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID
            LEFT OUTER JOIN
            [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID
            JOIN
            [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID
            JOIN
            [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID
            and Q.SessionID = QI.SessionID and
            QI.IsRecommendedConfiguration = @IsRecommended and
            Q.SessionID = @SessionID
            GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R
            where R.DatabaseID = D1.DatabaseID and
            R.TableID = T1.TableID and
            R.IndexID = I1.IndexID and
            D1.SessionID = @SessionID  and
            R.Count > 0
    order by
[Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],
            [Index!5!NumberOfReferences!ELEMENT] , [Index!5!IndexID!hide]

    FOR XML EXPLICIT
end
GO
/****** Object:  StoredProcedure
[dbo].[sp_DTA_index_usage_helper_relational]    Script Date: 12/31/2008
10:54:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_index_usage_helper_relational]
    @SessionID        int,
    @IsRecommended    int
    as begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as
'Schema Name' ,T1.TableName as 'Table/View Name' ,I1.IndexName as 'Index
Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as
'Percent Usage' from
                DTA_reports_database as D1 ,
                DTA_reports_index as I1,
                DTA_reports_table as T1,
                (
                    select D.DatabaseID,T.TableID ,
                            I.IndexID  ,SUM(Q.Weight) as Count,
                            100.0 *  SUM(Q.Weight) /
                            ( 1.0 * (    select    CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)
                                                else 1
                                                end   

                                        from [msdb].[dbo].[DTA_reports_query] as Q
                                        where Q.SessionID = @SessionID ))
                as Usage
        from
                [msdb].[dbo].[DTA_reports_index] as I   
                LEFT OUTER JOIN
                [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID
                LEFT OUTER JOIN
                [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID
                JOIN
                [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID
                JOIN
                [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID
                and Q.SessionID = QI.SessionID and
                QI.IsRecommendedConfiguration = @IsRecommended and
                Q.SessionID = @SessionID

                GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R
                where R.DatabaseID = D1.DatabaseID and
                R.TableID = T1.TableID and
                R.IndexID = I1.IndexID and
                D1.SessionID = @SessionID  and
                R.Count > 0
                order by R.Count desc end
GO
/****** Object:  StoredProcedure [dbo].[sp_DTA_database_access_helper_xml]  
Script Date: 12/31/2008 10:53:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_database_access_helper_xml]
            @SessionID        int
as
begin
    select 1            as Tag,
            NULL          as Parent,
            '' as [DatabaseAccessReport!1!!ELEMENT],
            NULL  as [Database!2!Name!ELEMENT] ,
            NULL as [Database!2!NumberOfReferences!ELEMENT],
            NULL as [Database!2!PercentUsage!ELEMENT]
        union all


    select 2 as Tag, 1 as Parent,NULL,D1.DatabaseName  ,
    R.Count  ,
    CAST(R.Usage as decimal(38,2))  from
                    [msdb].[dbo].[DTA_reports_database] as D1 ,
                    (
                        select D.DatabaseID,SUM(Q.Weight) as Count,
                                100.0 *  SUM(Q.Weight) /
                                ( 1.0 * (    select    CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)
                                                    else 1
                                                    end   

                                            from [msdb].[dbo].[DTA_reports_query] as Q
                                            where Q.SessionID = @SessionID ))
                    as Usage
            from
                        [msdb].[dbo].[DTA_reports_database] as D
                        LEFT OUTER JOIN
                        [msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID =
D.DatabaseID
                        LEFT OUTER JOIN
                        [msdb].[dbo].[DTA_reports_query] as Q ON QD.QueryID = Q.QueryID
                        and Q.SessionID = QD.SessionID and
                        Q.SessionID = @SessionID       
                        GROUP BY D.DatabaseID
                    ) as R
                    where R.DatabaseID = D1.DatabaseID  and
                    D1.SessionID = @SessionID and
                    R.Count > 0
    order by Tag,[Database!2!NumberOfReferences!ELEMENT] desc
    FOR XML EXPLICIT
end
GO