Index tuning wizard

I'm having trouble getting the above to do what I want. I pulled a trace
table on a 2000 server. There were a couple of thousand of (mostly) stored
proc runs pulled into it. I selected all tables but when it ran it failed
with a rather cryptic "An error occurred trying to run a sql statement".
I'm wondering if the wizard can handle stored procs? Especially ones that
are executed strings which the developers rely on heavily here?CLM,
Try to reduce the size and scope of the workload file. I've had individual
statements cause the ITW to fail in the past.
This may also help in general:
http://www.sql-server-performance.com/index_tuning_wizard_tips.asp
and
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:5D0F2A95-C89D-4558-83CF-D2F540B87CA0@.microsoft.com...
> I'm having trouble getting the above to do what I want. I pulled a trace
> table on a 2000 server. There were a couple of thousand of (mostly)
> stored
> proc runs pulled into it. I selected all tables but when it ran it failed
> with a rather cryptic "An error occurred trying to run a sql statement".
> I'm wondering if the wizard can handle stored procs? Especially ones that
> are executed strings which the developers rely on heavily here?|||But if it can't handle stored procs that execute strings, then I'm not sure
it's worth it? I'd have to build these by hand and by the time I do that I
can probably analyze it manually.
Thx for the help...
"Jerry Spivey" wrote:
> CLM,
> Try to reduce the size and scope of the workload file. I've had individual
> statements cause the ITW to fail in the past.
> This may also help in general:
> http://www.sql-server-performance.com/index_tuning_wizard_tips.asp
> and
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx
> HTH
> Jerry
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:5D0F2A95-C89D-4558-83CF-D2F540B87CA0@.microsoft.com...
> > I'm having trouble getting the above to do what I want. I pulled a trace
> > table on a 2000 server. There were a couple of thousand of (mostly)
> > stored
> > proc runs pulled into it. I selected all tables but when it ran it failed
> > with a rather cryptic "An error occurred trying to run a sql statement".
> > I'm wondering if the wizard can handle stored procs? Especially ones that
> > are executed strings which the developers rely on heavily here?
>
>|||On Fri, 26 May 2006 11:10:01 -0700, CLM wrote:
>But if it can't handle stored procs that execute strings, then I'm not sure
>it's worth it? I'd have to build these by hand and by the time I do that I
>can probably analyze it manually.
>Thx for the help...
Hi CLM,
You can set up a profiler trace, run some of your SQL, then let the ITW
analyze the output from the trace.
Check BOL to find out what events you have to include in your trace for
the ITW.
--
Hugo Kornelis, SQL Server MVPsql

Index tuning wizard

Hello!
Is ist necessary to put the database, i want to run the index tuning
wizard on, into the single user mode?
Many thanks in advance.
ChristophNo, just run as normal.
"xoph" wrote:
> Hello!
> Is ist necessary to put the database, i want to run the index tuning
> wizard on, into the single user mode?
> Many thanks in advance.
> Christoph
>

index tuning wizard

I have a query that looks something like this:
select fields from tableA
select fields from tableB
select fields from tableC
...
I want to try to run the Index Tuning Wizard on all of the queries in the
window. However, whenever I try, the ITW tells me "There are no events in
the workload. Either the trace file contains no sql batch or RPC events or
the sql script contained no sql queries." If I select one line and run the
ITW, it works fine - no matter which line I run. I tried adding GO between
each line but that didn't help.
Any ideas how I can get this to work?
Thanks, AndreTurns out it was because there was a syntax error in one of the queries.
Even though the parse completed successfully, when i ran all 84 of the
queries, one of them bombed out. I fixed it, and the ITW is working it's
little self away.
Thanks for the help.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u0PefPTVDHA.2248@.TK2MSFTNGP10.phx.gbl...
> What version and service pack are you on ? The following works fine for me
> on SQL2000 SP3
> use Northwind
> go
> select * from Customers where Country = 'Mexico'
> select * from Customers where City = 'México D.F.'
> select * from Customers where CustomerID = 'ALFKI'
> If I highlight the above in QA and invoke the ITW it generates
> recommendations
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Andre" <AndreGetsEnoughSPAM@.nospam.com> wrote in message
> news:O5mb2LSVDHA.2364@.TK2MSFTNGP09.phx.gbl...
> I have a query that looks something like this:
> select fields from tableA
> select fields from tableB
> select fields from tableC
> ...
> I want to try to run the Index Tuning Wizard on all of the queries in the
> window. However, whenever I try, the ITW tells me "There are no events in
> the workload. Either the trace file contains no sql batch or RPC events or
> the sql script contained no sql queries." If I select one line and run
the
> ITW, it works fine - no matter which line I run. I tried adding GO
between
> each line but that didn't help.
> Any ideas how I can get this to work?
> Thanks, Andre
>
>

Index Tuning Wizard

Hi all,
I'd like to use Index Tuning Wizard. I created a workload trace file with
SQL Profiler, then I start the wizard but it ends up with message that there
are no events and queries that can be tuned against current database.
I used trace template 'SQLProfilerTuning.tdf', so there are only
RPC:Completed and SQL:BatchCompleted events, which should be OK, but may be
it is a problem that we use stored procedures for data manipulation, in fact
there is no SQL query called directly from our application, we always call
SP and pass parameters to it. Is Index Tuning Wizard tool limitid only for
queries? Or may I achieve it to work using events for Stored Procedures?
Any advice appretiated.
eXavierI havent used the Index Tuning Wizard much myself, but I
would say that well normalised tables (using the correct
fields for and with the correct indexes) and well written
procs would be more fruitful than using the Wizard.
>--Original Message--
>Hi all,
>I'd like to use Index Tuning Wizard. I created a workload
trace file with
>SQL Profiler, then I start the wizard but it ends up with
message that there
>are no events and queries that can be tuned against
current database.
>I used trace template 'SQLProfilerTuning.tdf', so there
are only
>RPC:Completed and SQL:BatchCompleted events, which should
be OK, but may be
>it is a problem that we use stored procedures for data
manipulation, in fact
>there is no SQL query called directly from our
application, we always call
>SP and pass parameters to it. Is Index Tuning Wizard tool
limitid only for
>queries? Or may I achieve it to work using events for
Stored Procedures?
>Any advice appretiated.
>eXavier
>
>.
>

Index Tuning Wizard

Hi

I tried running ITW against one of our databases, and it came up with a
number of suggestions, but also filled the Application Log with messages
like:-

The description for Event ID ( 4 ) in Source ( ITW ) could not be found. It
contains the following insertion string(s):
Error in Parsing Event:
declare @.P1 nvarchar(1)
declare @.P2 nvarchar(1)
declare @.P3 nvarchar(1)
declare @.P4 nvarchar(1)
EXEC "dbo"."sp_get_RAO_indexW2K2" @.P1 , @.P2 , @.P3 , @.P4 .

Does anyone know if this is serious (or indeed what it means at all)?

There were no apparent errors in the ITW run.

Chloe CrowderHi

This is not a stored procedure I recognise or can find. Are you running ITW
from a script or profile? If yes then does it exist there?

Also, if you are using procedure names "sp_" SQLServer will be expecting
them to be in the master database, and will check it first, before the
current database for your stored procedure. By calling your procedure
something other than "sp_" you should improve performance.

John

"Chloe Crowder" <chloe.crowder@.bl.uk> wrote in message
news:bfrc2o$abe$1@.south.jnrs.ja.net...
> Hi
> I tried running ITW against one of our databases, and it came up with a
> number of suggestions, but also filled the Application Log with messages
> like:-
> The description for Event ID ( 4 ) in Source ( ITW ) could not be found.
It
> contains the following insertion string(s):
> Error in Parsing Event:
> declare @.P1 nvarchar(1)
> declare @.P2 nvarchar(1)
> declare @.P3 nvarchar(1)
> declare @.P4 nvarchar(1)
> EXEC "dbo"."sp_get_RAO_indexW2K2" @.P1 , @.P2 , @.P3 , @.P4 .
> Does anyone know if this is serious (or indeed what it means at all)?
> There were no apparent errors in the ITW run.
> Chloe Crowder

Index Tuning Wiz - recommendations page differs from script

Hi, When I look at the script generated from the ITW it often doesn't
have all the recomendations I see on the recommendations page. Is
this normal? The doc on the wiz seems a little thin...Johnny,
Check out:
http://www.microsoft.com/technet/pr...in/tunesql.mspx
and
http://www.sql-server-performance.c...wizard_tips.asp
and
http://msdn.microsoft.com/library/d...>
_05_0cbo.asp
HTH
Jerry
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1128634228.482567.300780@.z14g2000cwz.googlegroups.com...
> Hi, When I look at the script generated from the ITW it often doesn't
> have all the recomendations I see on the recommendations page. Is
> this normal? The doc on the wiz seems a little thin...
>|||Quite Frankly the 'brain' behind the ITW is quite this too.
I recommend you do your own analysis, set a benchmark, evaluate indices and
speed, factor in processors, competing resources, SQL cache etc etc.
You're more likely to get a better efficiency should you tackle the design
yourself (albeit you could use the ITW as a guide but it's scope is very
limited) and a better understanding of your data and query requirements.sql

Index tuning wiward, 98% improvement

When I used the index tuning wizard to analysis MSDE. It came up with a
recommendation says that:
If you choose to implement the recommended changes, you should see the
following improvement: 98% improvement in performance based on the sampled
workload!
What is the meaning of 98%? it means I will get 2% (100-98) better
performance, or I really twice better performance' Should I implement it?
regards,
Guoqi Zheng
http://www.ureader.com> What is the meaning of 98%? it means I will get 2% (100-98) better
> performance, or I really twice better performance'
It means based upon the sample you gave it, implementing these changes will
result on a performance increase of
>Should I implement it?
Officially yes of course you should no questions asked, yes, yes, yes and
yes again.
Unoffically and I am not writing this my evil twin brother is, if the speed
of the system has not been commented upon then you may want to delay it until
it is commented on, that way you have some working room :D
Peter
"guoqi zheng" wrote:
> When I used the index tuning wizard to analysis MSDE. It came up with a
> recommendation says that:
> If you choose to implement the recommended changes, you should see the
> following improvement: 98% improvement in performance based on the sampled
> workload!
> What is the meaning of 98%? it means I will get 2% (100-98) better
> performance, or I really twice better performance' Should I implement it?
> regards,
> Guoqi Zheng
> http://www.ureader.com
>