Index

Question

Index

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
December 4, 2009 at 3:32 am

#140437

Hi All,
How to find all the user defined indexes in a database. And is this possible to find their fragmentation level at a time ?
Thanks in Advance
Thank You.
Regards,
Raghavender Chavva

Viewing 15 posts - 1 through 15 (of 18 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Raghavender (12/4/2009)
Hi All,
How to find all the user defined indexes in a database. And is this possible to find their fragmentation level at a time ?
Thanks in Advance

Raghavender ,

Read the first link added in my signature.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

Hi,

As per gail suggest,I remove the Deprecated objects in my script .I think this ll post with in two days.(After posted u can change it)

FYI,

http://www.sqlservercentral.com/Forums/Topic827173-146-2.aspx#bm828000

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Raghavender (12/4/2009)
How to find all the user defined indexes in a database.

Query sys.indexes

And is this possible to find their fragmentation level at a time ?

Query sys.dm_db_index_physical_stats

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 4

Ok,

Is this possible to find out the indexes on a single talble, not for whole database.

Thanks

Thank You.

Regards,
Raghavender Chavva

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 5

and the query should compatible for both 2005 and 2000.

Thanks and Regards!!

Thank You.

Regards,
Raghavender Chavva

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Sure. Query sys.indexes (sysindexes on SQL 2000) and filter by object_id (id on SQL 2000)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 7

Hi Muthukkumaran,

I have tried your script of index defrag, but unable to defrag a single percentage down in our production environment.

exec USP_DBA_INDEX_DEFRAGMENTATION DBName is the query I have used.

can you please dig into it.

Thanks and Regards!!

Thank You.

Regards,
Raghavender Chavva

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

How big are those indexes? How many pages?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 9

Its around 8000 to 10000 Pages and fragmentation around 50%

And some of the indexes are about 200 to 800 pages, their fragmentation level is around 80% and they are also not able to defrag.

Thanks and Regards!!

Thank You.

Regards,
Raghavender Chavva

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Those are big enough that there should be a reduction in fragmentation after a rebuild. Small ones often don't show much if any change.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 11

do your tables have clustered indexes?

---------------------------------------------------------------------

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 12

Yes, have both clustered and non clustered indexes.

Thank You.

Regards,
Raghavender Chavva

george sibbald SSC Guru Points: 104210 More actions · Answer 13

then you should get improvement. Put debug statements in your code to see what is actually executed.

---------------------------------------------------------------------

NickDBA Hall of Fame Points: 3792 More actions · Answer 14

If the fragmentation is as high as that you may want to consider rebuilding the index.

Although without seeing the script you've been advised to run, im guessing it does that anyway based on that level of fragmentation?