Database Engine Tuning Wizard: "Failed to initialize MSDB database for tuning (exit code:

Error trying to tune a database:
Database Engine Tuning Wizard: "Failed to initialize MSDB database for
tuning (exit code: -1). (DTAClient)
Steps to Reproduce the Problem
1. Launch Database Engine Tuning Advisor
2. Enter connection info
Server Name: <The Server dns name>
Authentication: SQL Server Authentication
Login: sa
Password: <the sa password>
3. Click "Connect"
Notes:
a. The target server has an MSDB database
b. The server is Windows 2000
c. The server is SQL Server 2000
d. The sa login can connect and see the msdb database
e. My desktop machine has no application compatibility flags set for
explorer.exe
Full error log:
===================================
Failed to open a new connection.
===================================
Failed to initialize MSDB database for tuning (exit code: -1). (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)
===================================
Failed to establish a connection to the server.With regard to the "Database Engine Tuning Wizard: "Failed to initializ
MSDB database for tuning (exit code: -1). (DTAClient)" error, I just ra
into this myself with a fresh install of MS SQL Server 2005 + SP1.
I ran SQL Profiler on another server, that was working, to see what wa
happening when firing up the Database Tuning Advisor, and found that i
was looking for the following in the MSDB database:
Tables:
'DTA_input',
'DTA_progress',
'DTA_output',
'DTA_tuninglog',
'DTA_reports_database',
'DTA_reports_partitionfunction',
'DTA_reports_partitionscheme',
'DTA_reports_table',
'DTA_reports_tableview',
'DTA_reports_query',
'DTA_reports_querytable',
'DTA_reports_querydatabase',
'DTA_reports_index',
'DTA_reports_queryindex',
'DTA_reports_column',
'DTA_reports_indexcolumn',
'DTA_reports_querycolumn'
Stored Procedures:
'sp_DTA_add_session',
'sp_DTA_delete_session',
'sp_DTA_help_session',
'sp_DTA_update_session',
'sp_DTA_get_tuninglog',
'sp_DTA_update_session',
'sp_DTA_get_session_report',
'sp_DTA_get_tuninglog',
'sp_DTA_get_session_tuning_results',
'sp_DTA_set_interactivestatus'
These objects were missing from the server that was failing, so
scripted them from the server that was working. Once I created th
objects in the MSDB database, Database Tuning Advisor now works.
I've attached a zip file with the SQL scripts
+---
|Filename: DTA_Objects.zip
|Download: http://unixadmintalk.com/attachment.php?attachmentid=2
+---
--
bkp_4
----
bkp_42's Profile: http://unixadmintalk.com/77
View this thread: http://unixadmintalk.com/showthread.php?t=18525sql

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Hi all,
I'm having a problem with the DETA in SQL Server 2005. Or at least I
think I am.
I have an asp.net web application that invokes lots of stored
procedures. There is one page that causes a whole load of stored
procedures to be fired off. I'm pretty sure that some of these
procedures could be optimised somehow.
I've done a trace using SQL Profiler. This basically involved me setting
up the trace and fannying about with the page for a few minutes,
performing the sort of actions that users will commonly perform.
In the DETA I set up a new session, selected the workload file and the
workload database and set it off on its merry way. I set it to consider
getting rid of all indexes (there are only indexes on the PK at the moment).
The thing is, I get no suggestions, which I'm surprised about.
The SProcs that are getting executed are search procs that take a few
different parameters. I had a feeling that this procedure, given the
number of times it's executed and the fact that it has no indexes other
than the PK would be ripe for some optimisation.
Can anyone tell me if:
- Perhaps the workload file is to small? I only fannied for a couple of
minutes. Could that make a difference?
- Is there anything else I could do?
Could I just be a god like query writer and not even know it :-)
Thanks to anyone who can advise
Kindest Regards
SimonHi Simon
First of all I'd say that DETA (2005) is much more reliable rather TA
(2000) and make really good suggestions as i have been played with it
Can you show us your store procedure's code?
I did some testing and have found that DETA does make suggestion to create
an additional index on searched column.
CREATE PROC usp_Test
@.s VARCHAR(20)
AS
SELECT ProductDescription FROM Products WHERE ProductDescription LIKE
@.s+'%'
An output about 1500 rows
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O8NYdEFJHHA.1008@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm having a problem with the DETA in SQL Server 2005. Or at least I think
> I am.
> I have an asp.net web application that invokes lots of stored procedures.
> There is one page that causes a whole load of stored procedures to be
> fired off. I'm pretty sure that some of these procedures could be
> optimised somehow.
> I've done a trace using SQL Profiler. This basically involved me setting
> up the trace and fannying about with the page for a few minutes,
> performing the sort of actions that users will commonly perform.
> In the DETA I set up a new session, selected the workload file and the
> workload database and set it off on its merry way. I set it to consider
> getting rid of all indexes (there are only indexes on the PK at the
> moment).
> The thing is, I get no suggestions, which I'm surprised about.
> The SProcs that are getting executed are search procs that take a few
> different parameters. I had a feeling that this procedure, given the
> number of times it's executed and the fact that it has no indexes other
> than the PK would be ripe for some optimisation.
> Can anyone tell me if:
> - Perhaps the workload file is to small? I only fannied for a couple of
> minutes. Could that make a difference?
> - Is there anything else I could do?
> Could I just be a god like query writer and not even know it :-)
> Thanks to anyone who can advise
> Kindest Regards
> Simon|||Hi,
Thanks for your reply. The SPROC is as follows:
Apologies for the formating...I'm hoping it will look better on your
screen than it looks as I'm writing this :-)
Any suggestions on indexes would be very much appreciated. In terms of
context, the procedure is attempting to find properties that dont have
rental bookings in them between the dates passed in. The other
paramaters are optional...
Thanks
Simon
CREATE PROCEDURE [dbo].[SearchAvailability]
@.propertyIDOrAddress VARCHAR(75) = NULL,
@.startDate VARCHAR(50) = NULL,
@.endDate VARCHAR(50) = NULL,
@.propertyTypeID smallint = NULL,
@.noOfBedrooms smallint = NULL,
@.estateID smallint = NULL,
@.noOfResults smallint = 25
AS
SELECT
SharedDataStore.dbo.Properties.[ID],
SharedDataStore.dbo.Properties.[StrapLine],
SharedDataStore.dbo.Properties.[ShortDescription],
SharedDataStore.dbo.Properties.[AdditionalDetails],
SharedDataStore.dbo.Properties.[InternalNotes],
SharedDataStore.dbo.Properties.[PublicNotes],
SharedDataStore.dbo.Properties.[Address1],
SharedDataStore.dbo.Properties.[Address2],
SharedDataStore.dbo.Properties.[Address3],
SharedDataStore.dbo.Properties.[EstateID],
SharedDataStore.dbo.Estates.[Name] AS 'EstateName',
SharedDataStore.dbo.Properties.[City],
SharedDataStore.dbo.Properties.[Region],
SharedDataStore.dbo.Properties.[Country],
SharedDataStore.dbo.Properties.[PostalCode],
SharedDataStore.dbo.Properties.[Tel],
SharedDataStore.dbo.Properties.[IsCommitment],
SharedDataStore.dbo.Properties.[PropertyTypeID],
SharedDataStore.dbo.PropertyTypes.[Type] AS 'PropertyTypeName',
SharedDataStore.dbo.Properties.[Status],
SharedDataStore.dbo.PropertyStatusTypes.[Name] AS 'StatusName',
SharedDataStore.dbo.Properties.[NoOfBedrooms],
SharedDataStore.dbo.Properties.[NoOfBathrooms],
SharedDataStore.dbo.Properties.[SupplierID],
SharedDataStore.dbo.Organisations.[Name] AS 'SupplierName',
SharedDataStore.dbo.Properties.[IsDeleted],
SharedDataStore.dbo.Properties.[EnteredDate],
SharedDataStore.dbo.Properties.[EnteredBy],
SharedDataStore.dbo.Properties.[LastModified],
SharedDataStore.dbo.Properties.[LastModifiedBy]
FROM
SharedDataStore.dbo.Properties LEFT JOIN
SharedDataStore.dbo.PropertyTypes ON
SharedDataStore.dbo.Properties.PropertyTypeID =SharedDataStore.dbo.PropertyTypes.ID
LEFT JOIN SharedDataStore.dbo.Estates ON
SharedDataStore.dbo.Properties.EstateID = SharedDataStore.dbo.Estates.ID
LEFT JOIN SharedDataStore.dbo.Organisations ON
SharedDataStore.dbo.Properties.SupplierID =SharedDataStore.dbo.Organisations.ID
LEFT JOIN SharedDataStore.dbo.PropertyStatusTypes ON
SharedDataStore.dbo.Properties.Status =SharedDataStore.dbo.PropertyStatusTypes.ID
WHERE
[SharedDataStore].[dbo].[Properties].[ID] NOT IN
(
SELECT [Bookings].[PropertyID]
FROM Bookings
WHERE ArriveDate < CONVERT(DATETIME, @.endDate, 101) AND DepartDate >
CONVERT(DATETIME, @.startDate, 101)
AND ([Bookings].[IsDeleted] != 'true')
)
AND ([SharedDataStore].[dbo].[Properties].[IsDeleted] != 'true')
AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE + '%' +
@.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =@.propertyTypeID) OR (@.propertyTypeID IS NULL))
AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfBedrooms)
OR (@.noOfBedrooms IS NULL))
AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID) OR
(@.estateID IS NULL))
AND ([SharedDataStore].[dbo].[Properties].[Status] = 0) -- Property is
active
ORDER BY [SharedDataStore].[dbo].[Properties].[Address1]|||Simon Harvey wrote: [snip]
> AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE + '%' +
> @.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID => @.propertyTypeID) OR (@.propertyTypeID IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfBedrooms)
> OR (@.noOfBedrooms IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID) OR
> (@.estateID IS NULL))
Simon,
For these types of queries hardly any optimizations are possible because
of all optional parameters. So I am not surprised that the DETA could
not come up with any suggestions (there probably aren't any useful index
suggestions).
You could consider using dynamic SQL to form the exact SQL query that is
needed for the particular situation. You might get more compilations,
but such a query can make use of indexes on the search column(s) that
is/are used.
Another method to achieve the same thing is to write a query for each
combination of the optional parameters and use IF/ELSE commands to
execute the right query.
Then you can create indexes on each search column and compound indexes
on the search column combinations that are used most.
HTH,
Gert-Jan|||Hi there,
I was begining to suspect that that was going to be the problem.
I did the SProc that way because it seemed a bit (though not much) neater.
I guess if performance becomes more of a problem I may have to look at
making it dynamic.
Many thanks for your advice
Kindest Regards
Simon

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Hi all,
I'm having a problem with the DETA in SQL Server 2005. Or at least I
think I am.
I have an asp.net web application that invokes lots of stored
procedures. There is one page that causes a whole load of stored
procedures to be fired off. I'm pretty sure that some of these
procedures could be optimised somehow.
I've done a trace using SQL Profiler. This basically involved me setting
up the trace and fannying about with the page for a few minutes,
performing the sort of actions that users will commonly perform.
In the DETA I set up a new session, selected the workload file and the
workload database and set it off on its merry way. I set it to consider
getting rid of all indexes (there are only indexes on the PK at the moment).
The thing is, I get no suggestions, which I'm surprised about.
The SProcs that are getting executed are search procs that take a few
different parameters. I had a feeling that this procedure, given the
number of times it's executed and the fact that it has no indexes other
than the PK would be ripe for some optimisation.
Can anyone tell me if:
- Perhaps the workload file is to small? I only fannied for a couple of
minutes. Could that make a difference?
- Is there anything else I could do?
Could I just be a god like query writer and not even know it :-)
Thanks to anyone who can advise
Kindest Regards
SimonHi Simon
First of all I'd say that DETA (2005) is much more reliable rather TA
(2000) and make really good suggestions as i have been played with it
Can you show us your store procedure's code?
I did some testing and have found that DETA does make suggestion to create
an additional index on searched column.
CREATE PROC usp_Test
@.s VARCHAR(20)
AS
SELECT ProductDescription FROM Products WHERE ProductDescription LIKE
@.s+'%'
An output about 1500 rows
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O8NYdEFJHHA.1008@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm having a problem with the DETA in SQL Server 2005. Or at least I think
> I am.
> I have an asp.net web application that invokes lots of stored procedures.
> There is one page that causes a whole load of stored procedures to be
> fired off. I'm pretty sure that some of these procedures could be
> optimised somehow.
> I've done a trace using SQL Profiler. This basically involved me setting
> up the trace and fannying about with the page for a few minutes,
> performing the sort of actions that users will commonly perform.
> In the DETA I set up a new session, selected the workload file and the
> workload database and set it off on its merry way. I set it to consider
> getting rid of all indexes (there are only indexes on the PK at the
> moment).
> The thing is, I get no suggestions, which I'm surprised about.
> The SProcs that are getting executed are search procs that take a few
> different parameters. I had a feeling that this procedure, given the
> number of times it's executed and the fact that it has no indexes other
> than the PK would be ripe for some optimisation.
> Can anyone tell me if:
> - Perhaps the workload file is to small? I only fannied for a couple of
> minutes. Could that make a difference?
> - Is there anything else I could do?
> Could I just be a god like query writer and not even know it :-)
> Thanks to anyone who can advise
> Kindest Regards
> Simon|||Hi,
Thanks for your reply. The SPROC is as follows:
Apologies for the formating...I'm hoping it will look better on your
screen than it looks as I'm writing this :-)
Any suggestions on indexes would be very much appreciated. In terms of
context, the procedure is attempting to find properties that dont have
rental bookings in them between the dates passed in. The other
paramaters are optional...
Thanks
Simon
CREATE PROCEDURE [dbo].[SearchAvailability]
@.propertyIDOrAddress VARCHAR(75) = NULL,
@.startDate VARCHAR(50) = NULL,
@.endDate VARCHAR(50) = NULL,
@.propertyTypeID smallint = NULL,
@.noOfBedrooms smallint = NULL,
@.estateID smallint = NULL,
@.noOfResults smallint = 25
AS
SELECT
SharedDataStore.dbo.Properties.[ID],
SharedDataStore.dbo.Properties.[StrapLine],
SharedDataStore.dbo.Properties.[ShortDescription],
SharedDataStore.dbo.Properties.[AdditionalDetails],
SharedDataStore.dbo.Properties.[InternalNotes],
SharedDataStore.dbo.Properties.[PublicNotes],
SharedDataStore.dbo.Properties.[Address1],
SharedDataStore.dbo.Properties.[Address2],
SharedDataStore.dbo.Properties.[Address3],
SharedDataStore.dbo.Properties.[EstateID],
SharedDataStore.dbo.Estates.[Name] AS 'EstateName',
SharedDataStore.dbo.Properties.[City],
SharedDataStore.dbo.Properties.[Region],
SharedDataStore.dbo.Properties.[Country],
SharedDataStore.dbo.Properties.[PostalCode],
SharedDataStore.dbo.Properties.[Tel],
SharedDataStore.dbo.Properties.[IsCommitment],
SharedDataStore.dbo.Properties.[PropertyTypeID],
SharedDataStore.dbo.PropertyTypes.[Type] AS 'PropertyTypeName',
SharedDataStore.dbo.Properties.[Status],
SharedDataStore.dbo.PropertyStatusTypes.[Name] AS 'StatusName',
SharedDataStore.dbo.Properties.[NoOfBedrooms],
SharedDataStore.dbo.Properties.[NoOfBathrooms],
SharedDataStore.dbo.Properties.[SupplierID],
SharedDataStore.dbo.Organisations.[Name] AS 'SupplierName',
SharedDataStore.dbo.Properties.[IsDeleted],
SharedDataStore.dbo.Properties.[EnteredDate],
SharedDataStore.dbo.Properties.[EnteredBy],
SharedDataStore.dbo.Properties.[LastModified],
SharedDataStore.dbo.Properties.[LastModifiedBy]
FROM
SharedDataStore.dbo.Properties LEFT JOIN
SharedDataStore.dbo.PropertyTypes ON
SharedDataStore.dbo.Properties.PropertyTypeID =
SharedDataStore.dbo.PropertyTypes.ID
LEFT JOIN SharedDataStore.dbo.Estates ON
SharedDataStore.dbo.Properties.EstateID = SharedDataStore.dbo.Estates.ID
LEFT JOIN SharedDataStore.dbo.Organisations ON
SharedDataStore.dbo.Properties.SupplierID =
SharedDataStore.dbo.Organisations.ID
LEFT JOIN SharedDataStore.dbo.PropertyStatusTypes ON
SharedDataStore.dbo.Properties.Status =
SharedDataStore.dbo.PropertyStatusTypes.ID
WHERE
[SharedDataStore].[dbo].[Properties].[ID] NOT IN
(
SELECT [Bookings].[PropertyID]
FROM Bookings
WHERE ArriveDate < CONVERT(DATETIME, @.endDate, 101) AND DepartDate >
CONVERT(DATETIME, @.startDate, 101)
AND ([Bookings].[IsDeleted] != 'true')
)
AND ([SharedDataStore].[dbo].[Properties].[IsDeleted] != 'tr
ue')
AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE +
'%' +
@.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =
@.propertyTypeID) OR (@.propertyTypeID IS NULL))
AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfB
edrooms)
OR (@.noOfBedrooms IS NULL))
AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID)
OR
(@.estateID IS NULL))
AND ([SharedDataStore].[dbo].[Properties].[Status] = 0) -- P
roperty is
active
ORDER BY [SharedDataStore].[dbo].[Properties].[Address1]|||Simon Harvey wrote: [snip]
> AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE
+ '%' +
> @.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =
> @.propertyTypeID) OR (@.propertyTypeID IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noO
fBedrooms)
> OR (@.noOfBedrooms IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateI
D) OR
> (@.estateID IS NULL))
Simon,
For these types of queries hardly any optimizations are possible because
of all optional parameters. So I am not surprised that the DETA could
not come up with any suggestions (there probably aren't any useful index
suggestions).
You could consider using dynamic SQL to form the exact SQL query that is
needed for the particular situation. You might get more compilations,
but such a query can make use of indexes on the search column(s) that
is/are used.
Another method to achieve the same thing is to write a query for each
combination of the optional parameters and use IF/ELSE commands to
execute the right query.
Then you can create indexes on each search column and compound indexes
on the search column combinations that are used most.
HTH,
Gert-Jan|||Hi there,
I was begining to suspect that that was going to be the problem.
I did the SProc that way because it seemed a bit (though not much) neater.
I guess if performance becomes more of a problem I may have to look at
making it dynamic.
Many thanks for your advice
Kindest Regards
Simon

Database Engine Tuning Advisor gives non existent errors in SQL 2005

Hello,

I am sure you have heard of Community server - if not you are just using it ;)

I decided to try to optimise the performance of my site, run a trace and then DETA.

And I am getting errors like these:

---

E000 exec dbo.cs_user_Get @.UserName=N'jded',@.UserID=0,@.IsOnline=0,@.LastAction=N'',@.SettingsID=1000 122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @.ThreadID=5969,@.UserID=28236,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @.p4 bit
set @.p4=0
exec dbo.cs_thread_IsTracked @.ThreadID=414,@.UserID=1001,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @.SettingsID=1000,@.PostID=44641 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.

---

The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!

A couple of hints.
The database was an upgrade from 2000.:
- I changed the compatibility level to 2005 but no luck there. I tried with a brand new database, and the errors keep cropping up.
B. The errors were observed in a kit that comprises of a 32bit IIS and 64bit SQL2005 and thought that it had to do with the connectivity of those two.
- I run the traces in one (32bit) server that hosts both IIS and SQL and I am getting the same errors.

Any help will be greatly appreciated.

Thank you.

Which catelog are these stored proc resides? What is the catalog that you are using?|||

Hi!

The database name is CommunityServer - the sprocs reside in it.

It is referenced like this in the web.config:
<add key="SiteSqlServer" value="uid=userXXX;server=SUXXX;pwd=XXXXX;database=CommunityServer;Min Pool Size=5;Max Pool Size=5000;Connect Timeout=20;" />

Does this help you to help me?

Thank you,

Dimitris

|||

The issue has to do with the fact that we use ADO.NET to access the data whereas the tuning advisor uses SQL Native Client and thus it compares the actions to SQLNCLI's syntax.

The team of the tuning advisor should something about it.

|||

Can you please use the -d option (dta.exe) or "Database for Workload Analysis" option in UI to set the database context to the database that contains the stored procedures.

Thanks

Sanjay

|||What if the "Database for Workload Analysis" dropdown is way way way too small to see the entire database name? If I have 50 databases there and they all begin with the same first 20 characters, that's all I can see. How could I possible choose the correct database name in the dropdown?

Database Engine Tuning Advisor gives non existent errors in SQL 2005

Hello,

I am sure you have heard of Community server - if not you are just using it ;)

I decided to try to optimise the performance of my site, run a trace and then DETA.

And I am getting errors like these:

---

E000 exec dbo.cs_user_Get @.UserName=N'jded',@.UserID=0,@.IsOnline=0,@.LastAction=N'',@.SettingsID=1000 122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @.ThreadID=5969,@.UserID=28236,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @.p4 bit
set @.p4=0
exec dbo.cs_thread_IsTracked @.ThreadID=414,@.UserID=1001,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @.SettingsID=1000,@.PostID=44641 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.

---

The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!

A couple of hints.
The database was an upgrade from 2000.:
- I changed the compatibility level to 2005 but no luck there. I tried with a brand new database, and the errors keep cropping up.
B. The errors were observed in a kit that comprises of a 32bit IIS and 64bit SQL2005 and thought that it had to do with the connectivity of those two.
- I run the traces in one (32bit) server that hosts both IIS and SQL and I am getting the same errors.

Any help will be greatly appreciated.

Thank you.

Which catelog are these stored proc resides? What is the catalog that you are using?|||

Hi!

The database name is CommunityServer - the sprocs reside in it.

It is referenced like this in the web.config:
<add key="SiteSqlServer" value="uid=userXXX;server=SUXXX;pwd=XXXXX;database=CommunityServer;Min Pool Size=5;Max Pool Size=5000;Connect Timeout=20;" />

Does this help you to help me?

Thank you,

Dimitris

|||

The issue has to do with the fact that we use ADO.NET to access the data whereas the tuning advisor uses SQL Native Client and thus it compares the actions to SQLNCLI's syntax.

The team of the tuning advisor should something about it.

|||

Can you please use the -d option (dta.exe) or "Database for Workload Analysis" option in UI to set the database context to the database that contains the stored procedures.

Thanks

Sanjay

|||What if the "Database for Workload Analysis" dropdown is way way way too small to see the entire database name? If I have 50 databases there and they all begin with the same first 20 characters, that's all I can see. How could I possible choose the correct database name in the dropdown?

Database Engine Tuning Advisor Error

I have a very irritating error with Database Engine Tuning Advisor. While processing traces from Sql Profiler I keep getting trace log full of syntax error of most of queries - some of them very simple and ALL of them perfectly correct ( I check them separatly in QueryAnaliser ) - program gives recomendtation when I enter queries one by one - in that case the syntax is correct for Database Engine Tuning Advisor, but I just can't imagine why quries given to analysis in the set aren't correct? I dont' want to enter trace query by query (while I have thousends of them) !

It all seem like a Database Engine Tuning Advisor bug, any ideas about this problem?

Joanna

try and do one of the following:

1. make your trace little bit smaller by filtering on databaseid number.

2. make your trace for 1 hour + or so.

3. may b you have 2 many records there and its inufficient for the advisor.

tomer

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
SimonIt would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
Simon
It would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon
sql

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
SimonIt would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?
Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>
|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>
|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>
|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:[vbcol=seagreen]
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?Hi Howard,
I've yet to work with SQL Server 2005, but our local user group has had
a few presentations on installing SQL Server 2005 and upgrading SQL
2000. It's my understanding that the Database Engine Tuning Advisor
assumes some of the responsibility of the Index Tuning Wizard. They
are not the same, but the Database Engine Advisor will give suggestions
on indexes.
Hth, for what it's worth. Maybe others can give you their insight.
Stu|||Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>> Do bad indexes hinder performance? if so will the Database Engine Tuning
>> Advisor detect them and recommend their removal?
>|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> > What do you mean by a bad index? an extra index that's not beign used? a
> > redundent index? an index with fragmentation issues?
> >
> > -Paul Nielsen
> > SQL Server MVP
> > www.SQLServerBible.com
> >
> >
> > "Howard" <howdy0909@.yahoo.com> wrote in message
> > news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> >> Do bad indexes hinder performance? if so will the Database Engine Tuning
> >> Advisor detect them and recommend their removal?
> >>
> >
> >

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:[vbcol=seagreen]
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...sql