Index Tuning Wizard Driving me NUTS 
Author Message
 Index Tuning Wizard Driving me NUTS

We have a SQL 7 server database with approx 200 tables.  I am using
the SQL 2000 client utilities because of the improved Query Analyzer
and the SQL Tuning Wizard.  Which brings me to the point.  I have been
using the profiler to trace system activity each morning this week.  I
use the SQLTuning template which only captures RPC:Complete and
SQL:BatchCompleted events.  The trace files are between 5 and 200mb in
size.  When I run the index tuning wizard against the database, I
connect as SA, I accept the default settings like "keep existing
tables" and "limit queries to 200" etc.  I then select all my tables
and run the trace.  I always get a "workload does not contain any
events or queries" message or it comes up saying that it doesn't make
any recommendations.

I am about to strike the system with immediate and sudden DEATH.  This
is driving me nuts.  I know for an absolute fact that my files DO
contain evens and queries that the analyzer CAN use against the
database.  I've tried different modes, Fast - Medium - Thorough.  I've
tried different tuning paramaters, limiting number of queries to 200,
no limit, limit to 1000, etc.  I've tried selecting various numbers of
tables.  Nothing is working and I can't be this crazy.

Please please please if there is anyone out there who has suffered
through this and has any advice, please give me a hint.  I can't find
any good information on MSDN and none of the posts on this topic in
Google really say anything accept "are you suuuuuuuure you have
RPC:Complete and SQL:BatchComplete in your trace".  YES is the answer.

Anyone?



Tue, 20 Sep 2005 18:16:50 GMT
 Index Tuning Wizard Driving me NUTS

I was listening to a presentation from one of MS's big SQL tuning guys... He
said the index tuning wizard is VERY conservative... They (MS) would prefer
to give NO recommendation rather than risk a BAD recommendation... So
getting the "no recommendataions" (he says) does not always mean we are in
good shape, it could mean there is no clear choice or perhaps, "there is so
much data it gets confused".... He suggests taking a small (short time
period) sample, which increases the likelihood that the wizard might be able
to recognize and make some suggestions for you...

--
Wayne Snyder MCDBA, SQL Server MVP
Computer  Education Services Corp (CESC), C{*filter*}te, NC
(Please respond only to the newsgroups.)

PASS - the definitive, global community
for SQL Server professionals - http://www.***.com/


Quote:
> We have a SQL 7 server database with approx 200 tables.  I am using
> the SQL 2000 client utilities because of the improved Query Analyzer
> and the SQL Tuning Wizard.  Which brings me to the point.  I have been
> using the profiler to trace system activity each morning this week.  I
> use the SQLTuning template which only captures RPC:Complete and
> SQL:BatchCompleted events.  The trace files are between 5 and 200mb in
> size.  When I run the index tuning wizard against the database, I
> connect as SA, I accept the default settings like "keep existing
> tables" and "limit queries to 200" etc.  I then select all my tables
> and run the trace.  I always get a "workload does not contain any
> events or queries" message or it comes up saying that it doesn't make
> any recommendations.

> I am about to strike the system with immediate and sudden DEATH.  This
> is driving me nuts.  I know for an absolute fact that my files DO
> contain evens and queries that the analyzer CAN use against the
> database.  I've tried different modes, Fast - Medium - Thorough.  I've
> tried different tuning paramaters, limiting number of queries to 200,
> no limit, limit to 1000, etc.  I've tried selecting various numbers of
> tables.  Nothing is working and I can't be this crazy.

> Please please please if there is anyone out there who has suffered
> through this and has any advice, please give me a hint.  I can't find
> any good information on MSDN and none of the posts on this topic in
> Google really say anything accept "are you suuuuuuuure you have
> RPC:Complete and SQL:BatchComplete in your trace".  YES is the answer.

> Anyone?



Thu, 22 Sep 2005 20:46:56 GMT
 Index Tuning Wizard Driving me NUTS
Thanks Wayne.  Interesting you say that.  We have a very active system
and a 20 minute slice can easily be over 30MB.  The one consistency I
have noticed is that if I make very small trace files (1-3 MB) I seem
to get recommendations (or at least it doesn't error out on me).  I am
going to trace many very small time slices and see if I can get
something out of it that way.

Will post my progress.

Quote:

> I was listening to a presentation from one of MS's big SQL tuning guys... He
> said the index tuning wizard is VERY conservative... They (MS) would prefer
> to give NO recommendation rather than risk a BAD recommendation... So
> getting the "no recommendataions" (he says) does not always mean we are in
> good shape, it could mean there is no clear choice or perhaps, "there is so
> much data it gets confused".... He suggests taking a small (short time
> period) sample, which increases the likelihood that the wizard might be able
> to recognize and make some suggestions for you...

> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer  Education Services Corp (CESC), C{*filter*}te, NC
> (Please respond only to the newsgroups.)

> PASS - the definitive, global community
> for SQL Server professionals - http://www.***.com/



> > We have a SQL 7 server database with approx 200 tables.  I am using
> > the SQL 2000 client utilities because of the improved Query Analyzer
> > and the SQL Tuning Wizard.  Which brings me to the point.  I have been
> > using the profiler to trace system activity each morning this week.  I
> > use the SQLTuning template which only captures RPC:Complete and
> > SQL:BatchCompleted events.  The trace files are between 5 and 200mb in
> > size.  When I run the index tuning wizard against the database, I
> > connect as SA, I accept the default settings like "keep existing
> > tables" and "limit queries to 200" etc.  I then select all my tables
> > and run the trace.  I always get a "workload does not contain any
> > events or queries" message or it comes up saying that it doesn't make
> > any recommendations.

> > I am about to strike the system with immediate and sudden DEATH.  This
> > is driving me nuts.  I know for an absolute fact that my files DO
> > contain evens and queries that the analyzer CAN use against the
> > database.  I've tried different modes, Fast - Medium - Thorough.  I've
> > tried different tuning paramaters, limiting number of queries to 200,
> > no limit, limit to 1000, etc.  I've tried selecting various numbers of
> > tables.  Nothing is working and I can't be this crazy.

> > Please please please if there is anyone out there who has suffered
> > through this and has any advice, please give me a hint.  I can't find
> > any good information on MSDN and none of the posts on this topic in
> > Google really say anything accept "are you suuuuuuuure you have
> > RPC:Complete and SQL:BatchComplete in your trace".  YES is the answer.

> > Anyone?



Sat, 24 Sep 2005 15:01:38 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. index tuning wizard says drop some indexes

2. Index Tuning Wizard

3. Index Tuning Wizard - error message

4. Can I rely on Index Tuning Wizard ?

5. Index Tuning Wizard and stored procedure

6. Help on Index Tuning Wizard

7. API to Index Tuning Wizard?

8. Index Tuning Wizard and Table Variables

9. Index Tuning Wizard

10. Tuning Indexes w/the Wizard!

11. Index Tuning Wizard

12. Index tuning wizard


 
Powered by phpBB® Forum Software