Efficient SQL indexing model?

[morglum666]

Hello,

We've recently upgraded to a windows platform using Win 2008 and SQL 2008R2. We have a roughly 25 gb business data plus control tables database.

We'd like to do some database indexing but it needs to be relatively fast given some users are always on the system including at night. I also think we need to reflect the use of web client and not necessarily index central objects all the time like when we had fat clients. I haven't worked with the specifics of setting up a SQL index plan and would be relying on our back end team but I would appreciate any comments or tips. I do not want to use a JDE UBE.

I was thinking:

Weekly - Database index of Business Data - Prod, Control Tables - Prod + Serialized object tables (F98999, F989998)
Monthly - Index everything not included in the weekly run.

Does that make sense? Too specific?

Cheers

Malcolm

 

[brother_of_karamazov]

Are you talking about index maintenance?


[ QUOTE ]
Hello,

We've recently upgraded to a windows platform using Win 2008 and SQL 2008R2. We have a roughly 25 gb business data plus control tables database.

We'd like to do some database indexing but it needs to be relatively fast given some users are always on the system including at night. I also think we need to reflect the use of web client and not necessarily index central objects all the time like when we had fat clients. I haven't worked with the specifics of setting up a SQL index plan and would be relying on our back end team but I would appreciate any comments or tips. I do not want to use a JDE UBE.

I was thinking:

Weekly - Database index of Business Data - Prod, Control Tables - Prod + Serialized object tables (F98999, F989998)
Monthly - Index everything not included in the weekly run.

Does that make sense? Too specific?

Cheers

Malcolm

[/ QUOTE ]

 

[Larry_Jones]

OK, I'm a Oracle guy WAY more than I'm a SQL Server guy. But reoganizing / rebuilding indices on a weekly basis?

Defragmenting sounds good, but have you considered the performance impact it has on inserts and updates when page splits happen?
Even assuming you set fill factors less than the default 100% you're still going to CAUSE fragmentation by trying to cram everything back together into fewer leafs and/or pages with no room for expansion.

If you feel you MUST reorg/rebuild your indixes frequently - then do it intelligently -> only on indices that are heavily fragmented.

Frankly - doing some research on this before posting has made me appreciate Oracle DB a whole lot more. Oracle reccomends that you normally DON'T reorg/rebuild indices - they're self maintaining.

One thing of course that you SHOULD do regularly is update statistics - but you're already doing that - right?

 

[BOster]

The world will be a better place when all storage is solid state and fragmentation is not such a big issue.

 

[morglum666]

Hi Larry,

Its interesting you say that - our back end team plugged in a few analysis tools and essentially only a handful (10-30) tables really have fragmentation. I think we'll end up going that route.. just attack the fragmented ones.

It's one of those situations where a couple of years ago we really implemented a very powerful architecture (performance, redundancy, load balancing) and I just don't want it to degrade over time performance wise.

Thanks for the advice all

Malcolm

 

[DBohner-(db)]

I have always understood that fragmentation was not an indexing problem, rather a data location issue. I am not, necessary, saying disk location, rather data and type within a table.

If there are lots of updates and deletes, doing a table defragment or reorganization can improve performance.

I rarely experience that re-indexing improves performance. Evaluating Statistics, on Oracle systems, generally helps.

(db)