I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.

The 2 tables are described as follows:

MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts

MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits

Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.

Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?

Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.

Thoughts? Suggestions? Thanks...

On Mar 23, 12:08 pm, "Zamdrist" <zamdr... (AT) gmail (DOT) com> wrote:
Is this a reporting and analytical system or a transactional system?

On Mar 23, 12:20 pm, "Brad" <Brad.Marsh... (AT) Teksouth (DOT) com> wrote:
Transactional

"Zamdrist" <zamdrist (AT) gmail (DOT) com> wrote

Just so you know, these aren't very large tables.

However, I'd definitely agree you probably want some indexes.

However, the question you're asking is a bit too generic. You probably need
to look at what queries you're doing and optimize for those specifically.

And generally you want to find not necessarily the longest running queries,
but the ones called the most. If you have one query called 10 times a day
that runs for 10 minutes and optimize it 10%, you'll save 10 minutes a day.

If you have one query called 10,000 times a day for a minute and optimize it
10%, you'll save 1000 minutes.




--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:
A million records isn't large? Ok.

An application I didn't write and have NO support for is accessing the
data, reading & writing to these tables. There *are* indexes but only
one each and only on the primary key field.

I have no access to the queries as the application is reading from the
tables, probably using in-line string & code queries. There are no
views or procedures used by the application (well very few and not in
this instance). For all I know it could be using "Select * From
TableName...".

There are only 4 fields in each table, so I suppose the query(ies)
could only so complex as four fields would allow for. I know what one
of the fields ties back into a more widely used table that does have
more indexes, ones that appear to be useful.

Thanks

See inline

Zamdrist wrote:
Don't expect SQL Server 2000 or 2005 to be any faster if you keep the
current table and index structures... The fact you are using SQL Server
7.0 is no reason to have poor performance.

Those queries would most likely benefit from an index on this column.
But it depends on the selectivity of the column. If it is highly
selective (many different values), then an index is probably very
useful.

It would surely be worth a try. Just keep in mind that there is a cost
associated with creating an additional index, because it will require
disk space, memory (for caching) and can affect concurrency because of
added blocking. Blocking could really hurt an OLTP system.

HTH,
Gert-Jan

"Zamdrist" <zamdrist (AT) gmail (DOT) com> wrote

Nah, rather trivial these days. ;-)


If you have no access to these tables, you can't put indexes on it. So
perhaps I misunderstand.


Ack.

In any case, you probably CAN get this information via profiler and looking
at the queries as they come through the machine.

Also, there's some tools (only source I know of is via the SQL Server
Magazine website so they're copyrighted I believe) to follow wait
statistics, which can be VERY powerful to find out where your application is
doing a lot of querying.

You can also try (though generally I don't find it useful) the Index Wizard
in EM.

And any joins.




--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

On Mar 23, 1:59 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:
No I have access to all the objects via Enterprise Manager, I just
don't have access to the application code to see what it is doing,
there are however only a handful of queries/procedures, and over 70+
tables, so I fear the worst.
Thanks Greg.

"Greg D. Moore (Strider)" <mooregr_deleteth1s (AT) greenms (DOT) com> wrote

Does "trivial" mean easy or unimportant?

"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote

No, in this case it means rather small which impacts how you approach
maintainence issues. And to some extent how you solve problems.

For example, for some databases, it may be "simpler" to simply through more
memory at the problem. For a database 10x the size, more memory might not
even make a dent.


--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com