SQL Index question(s) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Index question(s)

Author

Topic

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-12 : 14:18:46

I've been reading a lot about DBCC DBREINDEX, DBCC INDEXDEFRAG etc., but I really need to get a better understanding of indexes in general.

I see there are two types of indexes: clustered and nonclustered. I'm learning the differences and advantages between the two.

A couple of questions.

How can I find the actual index or indexes on a table? Is that possible? It is important to know the actual indexes on tables, yes?

I ask this because I want to fully understand how indexes work, the type of maintenance that is needed, and any other caveats that come with it.

For example, here is a before and after DBCC SHOWCONTIG on a table I ran DBREINDEX on (this is a test DB, so if I FUBARD it, its ok)


DBCC SHOWCONTIG scanning 'S_OPTY_X' table...
Table: 'S_OPTY_X' (5835333); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 2015
- Extents Scanned..............................: 262
- Extent Switches..............................: 1318
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 19.11% [252:1319]
- Logical Scan Fragmentation ..................: 33.60%
- Extent Scan Fragmentation ...................: 88.55%
- Avg. Bytes Free per Page.....................: 3401.3
- Avg. Page Density (full).....................: 57.98%

And after the DBREINDEX


DBCC SHOWCONTIG scanning 'S_OPTY_X' table...
Table: 'S_OPTY_X' (5835333); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 1272
- Extents Scanned..............................: 160
- Extent Switches..............................: 159
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.38% [159:160]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 62.50%
- Avg. Bytes Free per Page.....................: 658.4
- Avg. Page Density (full).....................: 91.87%

Reading BoL, I see you can use fillfactor. What is a fillfactor, and is there a standard on what to use?

I'll stop there for now.

THanks,

TCG

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-12 : 14:21:53

One more question. When looking at the results of a SHOWCONTIG for a table, what are ideal numbers to have?

What are bad numbers?

Thanks.

EDIT: Does rebuilding indexes take a long time? Or is it rather quick?

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-01-13 : 01:56:54

Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

SQL Server 2005 Books Online
Clustered Index Structures
http://msdn2.microsoft.com/en-us/library/ms177443.aspx
Nonclustered Index Structures
http://msdn2.microsoft.com/en-us/library/ms177484.aspx

Table and Index Organization
http://msdn2.microsoft.com/en-us/library/ms189051.aspx

MohammedU

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-15 : 14:03:17

thanks for the links.

I guess, more than anything else, I just need to find the fragemented tables and fix them, correct?

Just need to do some reading up on clustered and non-clustered indexes. I also need to figure out WHAT my tables have. (Is there a way?)

I guess, my challenge is, I have so many tables (3940), how do get them all fixed.

Westley
Posting Yak Master

229 Posts

Posted - 2007-01-18 : 03:01:11

You can always have a script to do them all, as for finding what indexes you have per table, there are scripts to do that as well. Just need to dig in more :)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-18 : 04:00:48

The Index Tuning Wizard will go a long way to fixing the tables for you then after that you just focus on the ones that are causing performance problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources