database tuning advisor - sql server 2008

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.

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.

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.