setting Index fill factors and clustered vs non-clustered

[Eyespi20]

I'm trying to reindex/reogranize my indexes to improve performance on the database and have a couple of questions regarding the options for setting indexes.

Example 1:
I have a very small table that very seldom gets anything inserted in it (like maybe once a year if we reorganize the department it refers to), and there is a primary key index on it and a clustered index on it both on the primary key field. It's a tiny table of 8 columns and 90 rows. The absolute BEST scan density I've been able to achieve is 50% after reindexing with a fill factor of 95. I could probably do it with a fill factor of 100 and be OK, but will that improve the scan density in any significant way? Is the fact that there are two indexes on it referring to the same column interfering with the scan in any way? Is the clustered index superfluous?

Example 2:
This table is HUGE and has no primary key field. It is constantly having records inserted and updated and is VERY heavily queried, but very few records ever get deleted (only when I archive). It currently has 6,127,607 rows and 20 columns. It has 4 indexes a primary key index (although how it can have one is beyond me, because there IS no uniquely identified column in it -- this index is, however on two columns, that when taken together would be unique, so I'm assuming that's how it has the designation of PK), a clustered index and two other non-clusterd indexes. Before I ran dbcc reindex on it last night, it had a scan density of 47% afterwards, it had a scan density of 53%. Obviously, since this table has a lot of activity, I want the fill factor set well below 100%, but how far below? When I reindexed, I set it to 70 -- should it be even lower?

Thanks in advance!

Margaret

 

[druer]

Margaret,

Example 1: I would use a fill factor of 100% since virtually no activity on inserts/updates. Idea being I would want to store all of my data in as few data pages as possible. Likelihood is if the table is hit much the whole thing is probably cached since it is so small anyway.

Example 2: Clustered indexes are best when the first field being indexed in them is going to be going in a forward direction. In your case if the table is constantly getting data inserted/update you may be better off using a clustered index. Idea being the inserts/updates likely end up causing many page splits and "re-do's", then the other indexes also have to be updated.

My guess is some of the stragneness you are seeing in the Scan Density might have to do with your rowsizes or something. I seldom see anything under 90+ % once I completely rebuild my indexes (using 90% fill factor.) (Just a guess though.)

 

[Eyespi20]

Thanks druer. I'll reindex the one table at 100%.

The other table is a real issue since it's hit on so much. I HAVE to do something to get that going better.

Thanks for the information.

Margaret

 

[druer]

Do you have any "backup" server or "development database on same server" where you can do some testing? Idea would be to copy table and indexes as they are, then capture the longest duration queries you have for the table and run them on the "test" version to gain a benchmark. Then make the index change and see if help things or hurt them.

What type of disk(s) are you using for your production database? Is it a system with multiple drives? With no other changes you might be able to create a new filegroup on a seperate drive just to keep this heavily accessed (poorly designed) table in (and perhaps a handful of others in.)

 

[Eyespi20]

It's a raid 5 dual processor and we're getting a new server with more room in it to move the db to. Currently we are running SQL7 on NT and are upgrading the OS to Windows 2000 server. Can't upgrade the SQL version since the application is ancient (1998!) and doesn't like SQL 2000 (or SQL 2000 doesn't like it).

Thanks

Margaret

 

[druer]

Application doesn't like SQL 2000. There were a couple of system table column name changes that hosed up certain things for many applications because of commands that were executed to "find out system" information. Your's isn't the only one.

 

[Eyespi20]

Well - I'm certainly glad to hear that!

I was holding out that the nay-sayers were just too lazy to figure out the bugs and that with diligent searching and tweaking, we could get it to work on SQL2k some day. Thanks, druer -- you made my day.

Margaret

 

[druer]

Oh they can definitely be worked out (Actually quite easily). As long as someone has access to the code. If it weren't 5 years ago when I went through it, I'd probably remember the names of the system columns that changed and be able to hand the solution to you.

 

[Eyespi20]

Isn't there a setting you can set on 2k to mimic 7.0?

Margaret

 

[SQLDenis]

>>Isn't there a setting you can set on 2k to mimic 7.0?
You can set the DB compatibility level to 70 but I dn't know if that will help you in this case

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Eyespi20]

Thanks, Dennis -- guess I'll have to install 2k on the old server once we have the new one up and running and play around with it.

Thanks!

Margaret