Re: Problems with Index Tuning

Alessandro Zucchi (AlessandroZucchi@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> 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, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: User and Login auditing
    ... I am running SQL 7. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • Re: Tracking user activities
    ... >> I'd like these settings to persist permanently and the event records to ... > In SQL 2000, this is not very simple to do. ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... select Script Trace. ...
    (comp.databases.ms-sqlserver)
  • Re: How to look at parameter string
    ... I am tracing, but the problem I can't trace (at least, I haven't figured out ... string it is putting together to send to Sql. ... >> The problem is not coming from Sql Server but from my page. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: C2 SQL auditing
    ... Andrew J. Kelly SQL MVP ... Do you suggest any third party tools for SOX compliance? ... I would like to off load the server side trace to the SOX team ... when I stop SQL server then my trace file gets populated. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL logins logged where
    ... you can't trace directly to a table but can use the> fn_trace_gettable function to load the trace file into a> table. ... If you are using SQL Server 7, use> xp_trace_setqueuedestination to set the destination to a> table. ... Security Tab does not give too many ...
    (microsoft.public.sqlserver.security)