Problems with Index Tuning

24 Nov 2005 3:21 PM

Alessandro Zucchi

Hi all,

I'm trying to use SQL Profile and Index tuning to tune performance of my
database.
My Web application use only Stored Procedures.
During the "SQL Profile session" I traced "Stored Procedure RPC:Completed"
as event. Follow and example of trace:

exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @P1, @P2, @P3, @P4, @P5,
@P6, @P7, @P8 ', N'@P1 int ,@P2 tinyint ,@P3 tinyint ,@P4 int ,@P5 tinyint
,@P6 tinyint ,@P7 int ,@P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239

exec sp_executesql N'EXEC SP_CARICAQUADRO_A @P1, @P2 ', N'@P1 int ,@P2
tinyint ', 59774, 3

....

The trace contains several thousands of above commands. The various Stored
Procedure add, modify, delete records on tables that have not indexes.

The second step is to use the registered trace as workload in "Index tuning
wizard".
At the end of the wizard the responce is:
"No index racciomandation for the workload and choosen parameters."

Unfortunately this is false because the database is not indexed and the
Stored Procedure contained in the workload need of indexes.

Anyone can Help ME ???
Best Regards

26 Nov 2005 11:07 PM

Erland Sommarskog

Alessandro Zucchi (AlessandroZuc***@discussions.microsoft.com) writes:
Show quote

> I'm trying to use SQL Profile and Index tuning to tune performance of my
> database.
> My Web application use only Stored Procedures. During the "SQL Profile
> session" I traced "Stored Procedure RPC:Completed" as event. Follow and
> example of trace:
>
> exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @P1, @P2, @P3, @P4, @P5,
> @P6, @P7, @P8 ', N'@P1 int ,@P2 tinyint ,@P3 tinyint ,@P4 int ,@P5 tinyint
> ,@P6 tinyint ,@P7 int ,@P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
>
> exec sp_executesql N'EXEC SP_CARICAQUADRO_A @P1, @P2 ', N'@P1 int ,@P2
> tinyint ', 59774, 3
>
> ...
>
> The trace contains several thousands of above commands. The various Stored
> Procedure add, modify, delete records on tables that have not indexes.
>
> The second step is to use the registered trace as workload in "Index
> tuning wizard".
> At the end of the wizard the responce is:
> "No index racciomandation for the workload and choosen parameters."
>
> Unfortunately this is false because the database is not indexed and the
> Stored Procedure contained in the workload need of indexes.

I have never used ITW, but obviously the event RPC:Completed is not
enough to trace. I would expect SP:StmtCompleted to be required, as well
as some of the performance events, and possibly some of the Object:Scan
events. I suggest that you study the documenation for the Index Tuning
Wizard.

By the way, the sp_ prefix is reserved for system stored procedures, and
you should not use it for your own objects, as SQL Server first looks
for these in master.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

27 Nov 2005 6:53 AM

Greg Linwood

Hi Alessandro

Unfortunately, the Index Tuning Wizard is over-simplistic in its
capabilities. You'll probably have to do this work manually by identifying
which stored procedure (or few worst stored procedures) is actually the
worst performing procedure & then tuning that stored procedure/s.

This, however is far easier to say than to do, as aggregating the
information you collect from your profiler traces is no trivial task.

I wrote an application SQLBenchmarkPro, which has this capability & it's
available for free to test at the moment from www.gajsoftware.com . It's
really designed for streamlining on-going SQL Server benchmark work, but it
also has an analysis feature whiich will capture profiler events, aggregate
them & tell you which are the worst performing stored procs to help you
focus your efforts. This is the easiest way I know of to perform this work
when the Index Tuning Wizard isn't up to the job.

HTH

Regards,
Greg Linwood
SQL Server MVP

Show quote

"Alessandro Zucchi" <AlessandroZuc***@discussions.microsoft.com> wrote in
message news:0D740F5D-78B3-4417-A53C-3311E01C7F76@microsoft.com...
> Hi all,
>
> I'm trying to use SQL Profile and Index tuning to tune performance of my
> database.
> My Web application use only Stored Procedures.
> During the "SQL Profile session" I traced "Stored Procedure
> RPC:Completed"
> as event. Follow and example of trace:
>
> exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @P1, @P2, @P3, @P4, @P5,
> @P6, @P7, @P8 ', N'@P1 int ,@P2 tinyint ,@P3 tinyint ,@P4 int ,@P5 tinyint
> ,@P6 tinyint ,@P7 int ,@P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
>
> exec sp_executesql N'EXEC SP_CARICAQUADRO_A @P1, @P2 ', N'@P1 int ,@P2
> tinyint ', 59774, 3
>
> ...
>
> The trace contains several thousands of above commands. The various Stored
> Procedure add, modify, delete records on tables that have not indexes.
>
> The second step is to use the registered trace as workload in "Index
> tuning
> wizard".
> At the end of the wizard the responce is:
> "No index racciomandation for the workload and choosen parameters."
>
> Unfortunately this is false because the database is not indexed and the
> Stored Procedure contained in the workload need of indexes.
>
> Anyone can Help ME ???
> Best Regards