DynamicSQL & the Index Tuning wizard.

Some facts first:
1. Large application running on SQL Server 2000.
2. Stored Procedures use a lot of dynamic SQL.
3. Performance is a major issue.
4. Focussing on the database:
4.1 Start up SQL Profiler.
4.2 Run the application for a period of time (normal customer usage) to
generate the workload file.
4.3 Run the ITW on this workload file.
Questions:
(a) What kind of output can I expect from the ITW since the t-sql code is
heavily using dynamic SQL?
(b) Will the ITW be able to properly analyze the workload file in this
scenario?
(c) Are there any other issues that I should be aware of when using the SQL
Profiler and ITW for dynamic SQL code analysis?
TIA
Cheers!
SQLCatZ
SQLCatz,
Yes, the ITW will work fine with dynamic SQL. You might also want to
look at the profiler trace yourself and run some queries against it. I
look for:
1) CPU intensive queries (CPU column)
2) IO intensive queries (reads, writes columns)
3) Long running queries (duration column)
If you get the top 10 culprits from each of those categories, you will
have eliminated 90% of your poor performing queries. You want to get the
biggest bangs for your buck, and not waste time on things that don't
really matter.
Use the ITW as a guide to making decisions about which indexes to apply,
in most cases you won't want to blindly implement what it suggests.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
SQLCatz wrote:
> Some facts first:
> 1. Large application running on SQL Server 2000.
> 2. Stored Procedures use a lot of dynamic SQL.
> 3. Performance is a major issue.
> 4. Focussing on the database:
> 4.1 Start up SQL Profiler.
> 4.2 Run the application for a period of time (normal customer usage) to
> generate the workload file.
> 4.3 Run the ITW on this workload file.
> Questions:
> (a) What kind of output can I expect from the ITW since the t-sql code is
> heavily using dynamic SQL?
> (b) Will the ITW be able to properly analyze the workload file in this
> scenario?
> (c) Are there any other issues that I should be aware of when using the SQL
> Profiler and ITW for dynamic SQL code analysis?
> TIA
> Cheers!
> SQLCatZ
>
sql