Advice on Indexing Tables for Performance Imporovement
-
Hello,
We are a small team of developers with a lot of experience in VB, C#, etc, but no DBAs. We have an application that has a DB with about 250 tables. None of our tables have clustered indexes. Few of these tables have any kind of index other than a non-clustered primary key. It would not be easy to recreate the database with tables with clustered primary keys becuse every thing would need to be scripted since the application is installed at about 800 locations.
A typical table has DDL something like this...
CREATE TABLE Foo(
FooID int IDENTITY,
FooTooID int NOT NULL,
FooDT datetime NULL,
FooValue int NULL,
FooComment varchar(255) NULL
)
go
ALTER TABLE Foo ADD PRIMARY KEY NONCLUSTERED (FooID)
go
Should we create, at a minimum, indexes on the primary keys on all tables?
If so, should these be clustered or non-clustered.
Does it matter that some of these tables have millions of rows and some have only a few rows?
Thanks in advance for any help in this area.
---Sam
[font="Verdana"]Samjazz[/font]
-
The primary key is an index, always. Whether it's better as clustered or nonclustered is hard to say without analysing the system. The queries that run against the tables determine the indexes. Without seeing the queries, it's near impossible to advise meaningfully on indexes.
If I may suggest, ask management about getting a consultant in to help you with indexing and performance tuning. There's no better way to learn than from someone who knows what they are doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for your quick response. I was afraid that that would be the answer. I was hoping there were some general rules that could be applied. Is there any good book or source for such principals. I don't think our company can afford to hire a consultant at this time so the job is falling on me.
[font="Verdana"]Samjazz[/font]
-
The book Steve recommended is a good one.
The thing is, perf tuning's a huge area and one that's not easy to learn by yourself. I got a start by learning from and working with someone who really knew what he was doing. It's as much an art as a science.
This may help a little:
p.s. You don't necessarily have to get someone in for a long engagement. As an example, I do perf tuning for 3 clients at the moment, just 2 days a month.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Blanket statements are usually worthless, so with that in mind...
I'd suggest, at a minimum, making the PK's clustered instead of non-clustered. Probably they're the best access path to the data in most of the queries you have. That would help performance some. But, not knowing all the details of your situation, that could be weak advice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you all for the good advice. I will be ordering that book, reading the given links, and etc...
Is there a negative imact to adding a clustered index (as below to table Foo) that is the same as the non-clustered primary key? Or would appropriate clustered indexes have a performance gain significant enough to justify adding a second (clustered) index?
CREATE UNIQUE CLUSTERED INDEX IXFooID ON Foo(FooID)go
If having the two indexes on the same PK column is unnecessary and degrades performance, I will have to recreate the PKs to change them to clustered.
Is there a script that would generate the DDL to remove all RI, recreate the PKs (as clustered) and reapply the RI to all tables in a DB?
[font="Verdana"]Samjazz[/font]
-
SamJazz (9/14/2010)
Is there a negative imact to adding a clustered index (as below to table Foo) that is the same as the non-clustered primary key?Yes. Slower insert/update/delete as both indexes will have to be updated. Wastes space on disk, in backups. Longer backup time, longer index maintenance
Is there a script that would generate the DDL to remove all RI, recreate the PKs (as clustered) and reapply the RI to all tables in a DB?
I don't have one. Maybe Redgate's SQL Refactor will help (don't know offhand). Otherwise such a script is not impossible to generate, just difficult.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It just so happens that today is T-SQL Tuesday and the theme is indexes, you may want to look here. The book that Steve recommended was written by Grant and is very good, I am working my way through it at the moment 😀
Use Grant's book and suppliment with BOL, the #SQLHelp tag on twitter is very good for instant responses to problems. There are also a plethora of good advice in the form of blog entries over at SQLSkills.com which are really deep dives!
Hope this helps,
Rich[p]
[/p] -
Thank you so much! 🙂 I guess I will get crackin on that script.
[font="Verdana"]Samjazz[/font]
-
Hi
Its very impotent that what type of query U will fire against the table 'Foo', on the basis of query type U may decide the which index is best for,
Ali
MCTS SQL Server2k8
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply