For anyone who is having this issue and is using a sys admin account, I
ran the following script to adding the missing objects to MSDB. It is
posted across 6 posts since there is a limit to the post size:
-- BEGIN SCRIPT 4 of 6
/****** Object: StoredProcedure
[dbo].[sp_DTA_query_costrange_helper_relational] Script Date: 12/31/2008
10:55:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_costrange_helper_relational]
@SessionID int
as
begin
declare @maxCost float
declare @minCost float
declare @maxCurrentCost float
declare @minCurrentCost float
declare @maxRecommendedCost float
declare @minRecommendedCost float
set nocount on
select @minCurrentCost = min(CurrentCost*Weight),@maxCurrentCost =
max(CurrentCost*Weight),
@minRecommendedCost = min(RecommendedCost*Weight),
@maxRecommendedCost = max(RecommendedCost*Weight)
from [msdb].[dbo].[DTA_reports_query]
where SessionID = @SessionID
-- Set the bucket boundaries
if @maxCurrentCost > @maxRecommendedCost
set @maxCost = @maxCurrentCost
else
set @maxCost = @maxRecommendedCost
if @minCurrentCost < @minRecommendedCost
set @minCost = @minCurrentCost
else
set @minCost = @minRecommendedCost
create table #stringmap(OutputString nvarchar(30),num int)
insert into #stringmap values(N'0% - 10%',0)
insert into #stringmap values(N'11% - 20%',1)
insert into #stringmap values(N'21% - 30%',2)
insert into #stringmap values(N'31% - 40%',3)
insert into #stringmap values(N'41% - 50%',4)
insert into #stringmap values(N'51% - 60%',5)
insert into #stringmap values(N'61% - 70%',6)
insert into #stringmap values(N'71% - 80%',7)
insert into #stringmap values(N'81% - 90%',8)
insert into #stringmap values(N'91% - 100%',9)
select num,count(*) as cnt
into #c
from
(
select case
when (@maxCost=@minCost) then 9
when (CurrentCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9
else
convert(int,floor(10*(CurrentCost*Weight-@minCost)/(@maxCost-@minCost)))
end as num
from
[msdb].[dbo].[DTA_reports_query]
where CurrentCost*Weight >= @minCost and
CurrentCost*Weight <= @maxCost
and SessionID = @SessionID
) t
group by num
select num,count(*) as cnt
into #r
from
( select case
when (@maxCost=@minCost) then 9
when (RecommendedCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9
else
convert(int,floor(10*(RecommendedCost*Weight-@minCost)/(@maxCost-@minCost)))
end as num
from
[msdb].[dbo].[DTA_reports_query]
where RecommendedCost*Weight >= @minCost and
RecommendedCost*Weight <= @maxCost
and SessionID = @SessionID
) t
group by num select 'Cost Range' =OutputString, 'Number of statements
(Current)' = ISNULL(c.cnt,0) , 'Number of statements (Recommended)' =
ISNULL(r.cnt,0) from
(
select #stringmap.num, #r.cnt
from #stringmap LEFT OUTER JOIN #r
ON #stringmap.num = #r.num
) r,
(
select #stringmap.num, #c.cnt
from #stringmap LEFT OUTER JOIN #c
ON #stringmap.num = #c.num
) c,
#stringmap
where #stringmap.num = r.num and
#stringmap.num = c.num
drop table #r
drop table #c
drop table #stringmap
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_query_cost_helper_xml]
Script Date: 12/31/2008 10:55:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_cost_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [StatementCostReport!1!!element],
NULL as [Statement!2!StatementID!ELEMENT],
NULL as [Statement!2!StatementString!ELEMENT] ,
NULL as [Statement!2!PercentImprovement!ELEMENT],
NULL as [Statement!2!Type!ELEMENT]
union all
select 2 as Tag,
1 as Parent,
NULL as [StatementCostReport!1!!element],
QueryID as [Statement!2!StatementID!ELEMENT],
StatementString as [Statement!2!StatementString!ELEMENT] ,
CASE
WHEN CurrentCost = 0 THEN 0.00
WHEN CurrentCost <> 0 THEN
CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal
(20,2))
end as [Statement!2!PercentImprovement!ELEMENT],
CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end as [Statement!2!Type!ELEMENT]
from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID
order by Tag,[Statement!2!PercentImprovement!ELEMENT] desc
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_query_cost_helper_relational]
Script Date: 12/31/2008 10:55:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_cost_helper_relational]
@SessionID int
as
begin select 'Statement Id' = QueryID, 'Statement String' =
StatementString, 'Percent Improvement' =
CASE
WHEN CurrentCost = 0 THEN 0.00
WHEN CurrentCost <> 0 THEN
CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal
(20,2))
end , 'Statement Type' = CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID
order by 'Percent Improvement' desc end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_event_weight_helper_xml]
Script Date: 12/31/2008 10:53:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_event_weight_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [EventWeightReport!1!!element],
NULL as [EventDetails!2!EventString!ELEMENT] ,
NULL as [EventDetails!2!Weight!ELEMENT]
union all
select 2 as Tag,
1 as Parent,
NULL as [QueryCost!1!!element],
EventString as [EventDetails!2!EventString!ELEMENT] ,
CAST(EventWeight as decimal(38,2)) as [EventDetails!2!Weight!ELEMENT]
from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID and EventWeight>0
order by Tag,[EventDetails!2!Weight!ELEMENT] desc
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_event_weight_helper_relational] Script Date: 12/31/2008
10:53:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_event_weight_helper_relational]
@SessionID int
as
begin select 'Event String'= EventString, 'Weight' = CAST(EventWeight as
decimal(38,2)) from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID and EventWeight>0
order by EventWeight desc end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_query_detail_helper_xml]
Script Date: 12/31/2008 10:55:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_detail_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [StatementDetailReport!1!!element],
NULL as [Statement!2!StatementID!ELEMENT] ,
NULL as [Statement!2!StatementString!ELEMENT] ,
NULL as [Statement!2!Type!ELEMENT],
NULL as [Statement!2!CurrentCost!ELEMENT],
NULL as [Statement!2!RecommendedCost!ELEMENT],
NULL as [Statement!2!EventString!ELEMENT]
union all
select 2 as Tag,
1 as Parent,
NULL as [QueryCost!1!!element],
QueryID as [Statement!2!StatementID!ELEMENT],
StatementString as [Statement!2!StatementString!ELEMENT] ,
CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end as [Statement!2!Type!ELEMENT!element],
CAST(CurrentCost as decimal(38,7)) as [Statement!2!CurrentCost!ELEMENT],
CAST(RecommendedCost as decimal(38,7)) as
[Statement!2!RecommendedCost!ELEMENT],
EventString as [Statement!2!EventString!ELEMENT]
from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_query_detail_helper_relational] Script Date: 12/31/2008
10:55:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_detail_helper_relational]
@SessionID int
as
begin select 'Statement Id' =QueryID, 'Statement String'
=StatementString, 'Statement Type' = CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end,'Current Statement Cost' =CAST(CurrentCost as decimal(38,7)),
'Recommended Statement Cost' =CAST(RecommendedCost as decimal(38,7)), 'Event
String' =EventString from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID order by QueryID ASC end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_tuninglog] Script
Date: 12/31/2008 10:54:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_tuninglog]
@SessionID int,
@XML int = 0,
@LastRowRetrieved int = 0,
@GetFrequencyForRowIDOnly int = 0
as
begin
set nocount on
declare @retval int
declare @LogTableName nvarchar(1280)
declare @DefaultTableName nvarchar(128)
declare @SQLString nvarchar(2048)
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 @LogTableName = @DefaultTableName
begin
if (@GetFrequencyForRowIDOnly = 0)
begin
select CategoryID,Event,Statement,Frequency,Reason from
[msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
order by RowID
end
else
begin
select Frequency from [msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
order by RowID
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 + ' order by RowID'
exec (@SQLString)
end
else
begin
if @LogTableName = @DefaultTableName
begin
if (@GetFrequencyForRowIDOnly = 0)
begin
select CategoryID,Event,Statement,Frequency,Reason from
[msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
FOR XML RAW
end
else
begin
select Frequency from [msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
FOR XML RAW
end
return(0)
end
if (@GetFrequencyForRowIDOnly = 0)
begin
set @SQLString = N' select CategoryID,Event,Statement,Frequency,Reason
from '
end
else
begin
set @SQLString = N' select Frequency from '
end
set @SQLString = @SQLString + @LogTableName
set @SQLString = @SQLString + N' where SessionID = '
set @SQLString = @SQLString + CONVERT(nvarchar(10),@SessionID)
set @SQLString = @SQLString + N' and RowID > '
set @SQLString = @SQLString + CONVERT(nvarchar(10),@LastRowRetrieved)
set @SQLString = @SQLString + 'FOR XML RAW'
exec (@SQLString)
end
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_add_session] Script Date:
12/31/2008 10:53:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_add_session]
@SessionName sysname,
@TuningOptions ntext,
@SessionID int OUTPUT
as
declare @UserName as nvarchar(256)
declare @x_SessionName sysname
declare @ErrorString nvarchar(500)
declare @XmlDocumentHandle int
declare @retval int
set nocount on
begin transaction
-- Check for duplicate session name
select @x_SessionName = @SessionName
from msdb.dbo.DTA_input
where SessionName = @SessionName
if (@x_SessionName IS NOT NULL)
begin
rollback transaction
set @ErrorString = 'The session ' + '"' + LTRIM(RTRIM(@SessionName)) +
'"' +' already exists. Please use a different session name.'
raiserror (31001, -1,-1,@SessionName)
return(1)
end
-- Create new session
insert into msdb.dbo.DTA_input (SessionName,TuningOptions)
values (@SessionName,@TuningOptions)
select @SessionID = @@identity
if @@error <> 0
begin
rollback transaction
return @@error
end
if @@error <> 0
begin
rollback transaction
return @@error
end
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @TuningOptions,
'<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:x="http://schemas.microsoft.com/sqlserver/2004/07/dta"/>'
if @@error <> 0
begin
rollback transaction
return @@error
end
-- Execute a SELECT statement using OPENXML rowset provider.
insert into DTA_reports_database
SELECT @SessionID,[x:Name],1
FROM OPENXML (@XmlDocumentHandle,
'/x
TAXML/x
TAInput/x:Server//x
atabase',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
-- Insert progress record
insert into [msdb].[dbo].[DTA_progress]
(SessionID,WorkloadConsumption,EstImprovement,Tuni ngStage,ConsumingWorkLoadMessage,PerformingAnalysi sMessage,GeneratingReportsMessage)
values(@SessionID,0,0,0,N'',N'',N'')
if @@error <> 0
begin
rollback transaction
return @@error
end
-- Commit if input/progress records are updated
commit transaction
return 0
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_delete_session] Script
Date: 12/31/2008 10:53:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_delete_session]
@SessionID int
as
begin
declare @retval int
set nocount on
exec @retval = sp_DTA_check_permission @SessionID
if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
delete from msdb.dbo.DTA_input where SessionID=@SessionID
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_help_session] Script Date:
12/31/2008 10:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_help_session]
@SessionID int = 0,
@IncludeTuningOptions int = 0
as
begin
declare @tuning_owner nvarchar(256)
declare @retval int
declare @InteractiveStatus tinyint
declare @delta int
declare @cursessionID int
declare @dbname nvarchar(128)
declare @dbid int
declare @retcode int
declare @sql nvarchar(256)
set nocount on
-- List all Sessions mode
if @SessionID = 0
begin
-- If sysadmin role then rowset has all the rows in the table
-- Return everything
if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
begin
if (@IncludeTuningOptions = 0)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
order by
I.SessionID desc
end
else if (@IncludeTuningOptions = 1)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime,
O.StopTime,I.TuningOptions,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output as O
on I.SessionID = O.SessionID
order by
I.SessionID desc
end
end
else
begin
-- Temporary table to store sessionid and databases passed in by user
create table #allDistinctDbIds (DatabaseID int)
-- Init variables
set @dbid = 0
set @retcode = 1
-- Get all database names passed in by user (IsDatabaseSelectedToTune =1)
declare db_cursor cursor for
select distinct(DatabaseName) from DTA_reports_database
where IsDatabaseSelectedToTune = 1
-- Open cursor
open db_cursor
-- Fetch first session id and db name
fetch next from db_cursor
into @dbname
-- loop and get all the databases selected to tune
while @@fetch_status = 0
-- Loop
begin
-- set @retcode = 1 in the beginning to indicate success
set @retcode = 1
-- Get database id
select @dbid = DB_ID(@dbname)
-- In Yukon this masks the error messages.If not owner dont return
-- error message in SP
set @sql = N'begin try
dbcc autopilot(5,@dbid) WITH NO_INFOMSGS
end try
begin catch
set @dbid = 0
set @retcode = 0
end catch'
execute sp_executesql @sql
, N'@dbid int output, @retcode int OUTPUT'
, @dbid output
, @retcode output
-- dbid is 0 if user doesnt have permission to do dbcc call
insert into #allDistinctDbIds(DatabaseID) values
(@dbid)
-- fetch next
fetch from db_cursor into @dbname
-- end the cursor loop
end
-- clean up cursor
close db_cursor
deallocate db_cursor
select SessionID
into #allValidSessionIds
from DTA_input as I
where
((select count(*) from
#allDistinctDbIds ,DTA_reports_database as D
where #allDistinctDbIds.DatabaseID = DB_ID(D.DatabaseName)
and I.SessionID = D.SessionID
group by D.SessionID ) =
(select count(*) from DTA_reports_database as D
where I.SessionID = D.SessionID
and D.IsDatabaseSelectedToTune = 1
group by D.SessionID )
)
group by I.SessionID
-- Return only sessions with matching user name
-- If count of rows with DatabaseID = 0 is > 0 then permission denied
if ( @IncludeTuningOptions = 0 )
begin
select I.SessionID , I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
inner join #allValidSessionIds S
on I.SessionID = S.SessionID
order by
I.SessionID desc
end
else if (@IncludeTuningOptions = 1)
begin
select I.SessionID , I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime,
O.StopTime,I.TuningOptions,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
inner join #allValidSessionIds S
on I.SessionID = S.SessionID
order by
I.SessionID desc
end
drop table #allDistinctDbIds
drop table #allValidSessionIds
end
end
else
begin
exec @retval = sp_DTA_check_permission @SessionID
if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
if ( @IncludeTuningOptions = 0)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
where I.SessionID = @SessionID
end
else if (@IncludeTuningOptions = 1)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime,
O.StopTime,I.TuningOptions,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
where I.SessionID = @SessionID
end
-- Second rowset returned for DTA to process progress information
select ProgressEventID,TuningStage,WorkloadConsumption,Es tImprovement,
ProgressEventTime
,ConsumingWorkLoadMessage,PerformingAnalysisMessag e,GeneratingReportsMessage
from msdb.dbo.DTA_progress
where SessionID=@SessionID
order by ProgressEventID
-- Set interactive status to 6 if a time of 5 mins has elapsed
-- Next time help session is called DTA will exit
select @InteractiveStatus=InteractiveStatus
from msdb.dbo.DTA_input
where SessionID = @SessionID
if (@InteractiveStatus IS NOT NULL and( @InteractiveStatus <> 4 and
@InteractiveStatus <> 6))
begin
select @delta=DATEDIFF(minute ,ProgressEventTime,getdate())
from msdb.dbo.DTA_progress
where SessionID =@SessionID
order by TuningStage ASC
if(@delta > 30)
begin
update [msdb].[dbo].[DTA_input] set InteractiveStatus = 6
where SessionID = @SessionID
end
end
end
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_set_interactivestatus]
Script Date: 12/31/2008 10:55:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_set_interactivestatus]
@InterActiveStatus int,
@SessionID int
as
begin
declare @retval int
set nocount on
exec @retval = sp_DTA_check_permission @SessionID
if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
update [msdb].[dbo].[DTA_input] set InteractiveStatus = @InterActiveStatus
where SessionID = @SessionID
end
GO
-- END SCRIPT 4 of 6
RE: Database Engine Tuning Advisor -
12-31-2008
, 11:33 AM
Linear Mode
