Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which Event Classes i should use for finding good indexs ,statistics and etc to optimise queries and SPs. Expand / Collapse
Author
Message
Posted Monday, December 23, 2013 1:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:33 PM
Points: 47, Visits: 143
Dear all,

I am trying to use pro filer to create a trace,so that it can be used as workload in
"Database Engine Tuning Advisor" for optimization of one stored procedure.
latter i am planning to use the tamplet to capture the trace on production server, so that i can get the idea
of exact situation.

Please tel me about the Event classes which i should use in trace.

The stored proc contains three insert queries which insert data into a table variable,

Finally a select query is used on same table variable with one union of the same table variable, to generate a sequence for records based on certain condition of few columns.


There are three cases where i am using the above structure of the SP, so there are three SPS out of three , i want to chose one of them based on their performance.

There are 200 to 450 cols in tables, which i am planning to normalize.
but my question is specific to profiler tamplet in following conditions.
I found there is "Tuning" template avialable ( i will use this one also), but i want to make my own for following conditions and for testing and production server.


1) There is only one table with three inserts which gets into a table variable with a final sequence creation block.

2) There are 15 tables with 45 inserts , which gets into a tabel variable with a final

sequence creation block.

3)

There are 3 tables with 9 inserts , which gets into a table variable with a final
sequence creation block.

In all the above case number of record will be around 5 lacks.

Purpose is optimization of queries in SP

like which Event Classes i should use for finding good indexs ,statistics and ect to optimise queries and SPs.

yours sincerely




Post #1525428
Posted Monday, December 23, 2013 5:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 42,806, Visits: 35,927
From books online:

To build a recommendation of the optimal set of indexes, indexed views, or partitions for your databases, Database Engine Tuning Advisor requires a workload. A workload consists of a Transact-SQL script or a SQL Server Profiler trace saved to a file or table. If you are using a SQL Server Profiler trace file or table, they must contain Transact-SQL batch or remote procedure call (RPC) event classes, and the Event Class and Text data columns.


That's the only requirements. More columns or events will just bloat the trace file without providing any benefit.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1525471
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse