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 Fragmenation

I'm having trouble with slow performance due to index fragmentation. I'll insert 20000 records into a table and then have index fragmentation of like 67%! The database is so slow query this data back (over 3 minutes!). After rebuilding the index on the primary key (the only index) the same query takes less than 2 seconds. I'm using GUID as my primary key and I have recently switched from using NEWID() to using the new NEWSEQUENTAILID() to generate them. Can anyone suggest why I'm still having such a hard time with fragmentation?Unless you really NEED a GUID because you need an id that HAS to be unique across an entire network, I'd use an integer or bigint. There are a lot of articles out there regarding the performance hit associated with GUID's primarily due to page splits if memory serves. Is your primary key also a clustered index?
|||Yes it is a clustered index. I thought the problem with GUIDs in general is that they are pseudo-random. That is why I went to the newsequentialid() function that generates sequential guids across a machine.|||Index fragementation caused by using GUIDs in indexed columns shouldn't be causing the issues you're seeing.

While it is certainly true that you'll see very high index fragmentation for these columns (our production DB often has 95%+ fragmentation), this isn't necessarily going to kill your performance. We did extensive performance comparisons before decided to go with nearly 100% GUIDs are primary keys. There was certainly a perf difference, but it was negligible overall.

In fact, INSERT performance may actually increase thanks to the fact that disk hot spots are far less common.

I would examine the query plans before/after your defragmentation/rebuild your index. I think something else must be going on here.|||Its very likely that you have out of date statistics on the table that is giving you a bad query plan after the inserts. Rebuilding the index automatically updates statistics. Try just running UPDATE STATISTICS TableName after the INSERT and see if that gives you a better query plan.