To index or not to index?

Question

To index or not to index?

PompeyTID

SSC Veteran

Points: 269
More actions
April 11, 2005 at 7:57 pm

#109464

I have a poorly designed DB to maintain which was subject to what can only be described as a unique design methodology.
Essentially there are no indexes on it, and it runs like a fat piggy. What I would like to know please if there are some ready reckoners of what make good index candidates and which do not.
Thank you in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

In a case where I'm not familiar with the system and specifically the queries being run against the database, I would recommend running a trace and then feeding the results into the tunig wizard. This should get you the biggest bang for the least amount of effort. Then you can start examining specific problems and determine on a case by case basis what to do.

Generally though you want indexes on columns that are referenced in the ON clause in JOINS and the WHERE clause.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

athurgar SSCrazy Points: 2816 More actions · Answer 2

Have a dig through the sysindexes table for the user database. Ideally each table should have at least a clustered index (indid =1). As a very rough real of thumb I look at the 'WA_Sys' statistical indexes to get a feel for what tables and columns may need an index. Less effort and performance overhead than the tuning wizard - but not as accurate.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Another option might me to utilize a reverse engeneering tool in a database design product like ErWin or PowerDesigner. Products such as these could take a rather large database and then tell you where at least your major issues are i very short order. You should be able to download 'evaluation or developer' versions for these products (I personally prefer PowerDesigner because it deals with just about any DBMS and platform).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Indexing is sometimes more of an art than a science

I would start by using the Index Tuning Wizard and then go from there. Here are some links that might help:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;311826

http://support.microsoft.com/default.aspx?scid=kb;EN-US;814324

http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/itwforsql.asp

http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]