December 23, 2013 at 1:14 am
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
December 23, 2013 at 5:01 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply