Posted by Erland Sommarskog on 10/01/03 11:18

pheonix1t (pheonix1tAThoustonDOTrrDOTcom@com.com) writes:
> I've been assigned to do performance tuning on an SQL2000 database
> (around 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
>
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
>
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
>
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
>
> Before I start investing more time on this, I'd really like to learn
> about why you wouldn't want to use indexes - especially on such a large
> database!

Seems like you have an easy job. Run Profiler to catch a day's workload,
run the Index Tuning Wizard over the result, create indexes. If the
system does not really have any indexes and is still standing up on
that hardware, it's pointless to improve it.

And least of all, the RAID. The only way that system can survive is
because it's able to hold the data in cache.

Then again, I would suspect that if you run this query:

select *
from sysindexes
where indid >= 1 and indid < 255
and indexproperty(id, name, 'IsStatistics') = 0
and indexproperty(id, name, 'IsHypothetical') = 0

That a couple of indexes will show up.

Yet, then again, just because there are indexes, does not mean that
they are the right indexes, so Profiler and Index Tuning Wizard may
still be what you should look at.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]