x

database tuning advisor - sql server 2008

Hi, when i tuning my SP using sql server 2008 database tuning advisor, the tuning log shows number of rows for a single mode and so many rows displayed as 'statement does not reference any table'. can you pls. explain the reason? it ok or not? what can i do for avoid this? Example:

EXEC uspGetProduts @VarSpOprMode='OFFERS', 
@VarSpSubOprMode='LATEST-RDS', 
@VarCurrType='USD', 
@IntStartPageVal=1, 
@IntEndPageVal=30, 
@IntStartPrice=0,
@IntEndPrice=0, 
@VarOrderBy='CREATEDATE', 
@VarPrdType='saree'
Thanks in advance by Pratheeskumar
more ▼

asked Nov 16, 2010 at 03:31 AM in Default

prathees gravatar image

prathees
31 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

A couple of suggestions: 1. Does that stored proc do anything with any tables? Maybe it's just doing a calculation using the parameters. Maybe it's empty because the functionality wasn't needed any more but it was left in to avoid changing the application code. 2. Does the query do something that the table metadata makes impossible? For example, I know that if you inner join two tables and only select data from one table and it has has a foreign key to the other table, the query optimizer leaves the other table out of the plan because it can tell there will always be a row on that side of the join. Maybe there are constraints or something that mean these parameter values make it impossible for any rows to be returned.

This example looks like it does a select rather than an update. Try running it and look at the query plan.
more ▼

answered Nov 16, 2010 at 04:26 AM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 33

Check for temp Tables on your SP
Nov 16, 2010 at 04:38 AM Cyborg
(comments are locked)
10|1200 characters needed characters left

Maybe you have some conditional logic in the procedure that in some rare cases ends in statements that doesn't have anything to do with tables, like this:

CREATE PROC dbo.spTest
@a intAS
IF @a=1
Return 0 --Or something else
ELSE
SELECT * FROM tableA

OR why not an exception, like this:

CREATE PROC dbo.spTest2
@b int =0
AS
BEGIN TRY
  DECLARE @a
  SELECT @a=1/@b
END TRY
BEGIN CATCH
  Print ERROR_MESSAGE()
END CATCH
If you post your stored procedure it would be easier to help you.
more ▼

answered Nov 16, 2010 at 07:39 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

Hi Hakan Winther,

I accept your valuable response. Also I checked your Test Stored Prodcedure. But I, "Analyze Query in Database Engine Tuning Advisor", Tuning Log displayed as

Category ID Event Statement Frequency Reason S001 EXEC dbo.uspTestTuning @a=1 1 Statement does not reference any tables S001 EXEC dbo.uspTestTuning @a=1 Return 0 --Or something else 1 Statement does not reference any tables

I want, why the reason shows 'statement does not reference any tables'?, it will affect the performance?

Thanks By Pratheeskumar
Nov 16, 2010 at 11:00 PM prathees
(comments are locked)
10|1200 characters needed characters left

Prathees - as alluded by the others here, please check the contents of dbo.uspTestTuning. This stored procedure is most probably doing some sort of calculation, but doesn't actually select/delete/update any tables at all.

This warning just means that the query tuning advisor cannot help tune the query, as it will be constrained by memory and CPU alone. With a calculation like this, SQL Server cannot offer up better ways of running the query, as no tables are accessed and thus no statistics on data can be used to cost estimate the calculation.
more ▼

answered Nov 17, 2010 at 12:48 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x51

asked: Nov 16, 2010 at 03:31 AM

Seen: 2154 times

Last Updated: Nov 17, 2010 at 12:51 AM