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', 

Thanks in advance by Pratheeskumar

more ▼

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

avatar image

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

avatar image

David Wimbush
10.7k 31 34 44

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

OR why not an exception, like this:

 CREATE PROC dbo.spTest2
 @b int =0
   SELECT @a=1/@b

If you post your stored procedure it would be easier to help you.

more ▼

answered Nov 16, 2010 at 07:39 AM

avatar image

Håkan Winther
16.6k 38 46 58

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

avatar image

26.2k 18 38 48

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 16, 2010 at 03:31 AM

Seen: 2643 times

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

Copyright 2018 Redgate Software. Privacy Policy