question about performance issues w/SQL2000 with NO indexes

[pheonix1t]

hello,
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!

Thanks,

Oskar

[Stu]

I can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu

[Mike Epprecht \(SQL MVP\)]

Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <st**************@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

I can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu

[Mike Epprecht \(SQL MVP\)]

Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <st**************@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

I can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu

[Erland Sommarskog]

pheonix1t (ph***************************@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, es****@sommarskog.se

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

[Erland Sommarskog]

pheonix1t (ph***************************@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, es****@sommarskog.se

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

[--CELKO--]

The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.

[--CELKO--]

The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.

[Craig Kelly]

"pheonix1t" wrote:

hello,
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!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig

[Craig Kelly]

"pheonix1t" wrote:

hello,
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!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig