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