Index Fragmentation - 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

General SQL Server Forums

New to SQL Server Programming

Index Fragmentation

Author

Topic

Goldmember
Starting Member

9 Posts

Posted - 2008-06-22 : 18:59:45

What functions of tools do you use for managing index fragmentation?

DBCC?

I am working through MS Press SQL 2005 book and it mentions the
sys.dm_db_index_physical_stats function? It then give an example of code which is very involved.

Does anybody use this function?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-22 : 19:24:14

You can use it or 'dbcc showcontig' to check index fragmentation.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-06-23 : 01:43:09

The idea is that "sys.dm_db_index_physical_stats" will replace DBCC SHOWCONTIG .

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Ola Hallengren
Starting Member

33 Posts

Posted - 2008-06-23 : 13:27:16

I have a stored procedure that is using sys.dm_db_index_physical_stats to decide if an index needs to be rebuilt or reorganized.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren
http://ola.hallengren.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 13:29:49

Here's my script: http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx

And here's all of my database maintenance routines: http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 13:30:41

quote:
Originally posted by rmiao

You can use it or 'dbcc showcontig' to check index fragmentation.

From BOL:

quote:

Important:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_db_index_physical_stats instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffdavid
Starting Member

8 Posts

Posted - 2008-06-24 : 10:14:59

hi, new to SQL Server. How do you determine whether to rebuild or reorganize indexes?

Ola Hallengren
Starting Member

33 Posts

Posted - 2008-06-24 : 10:41:27

Read this article in Books Online.
http://msdn.microsoft.com/en-us/library/ms189858.aspx

You could use a script like Tara's or mine to do this dynamically.

Ola Hallengren
http://ola.hallengren.com

jeffdavid
Starting Member

8 Posts

Posted - 2008-06-24 : 11:07:32

thanks!

jeffdavid
Starting Member

8 Posts

Posted - 2008-06-27 : 10:04:21

Ola, Thank you so much. I read that article and it helped a lot. I was able to reorganize quite a few indexes. I reorganized the example query on that page to work for my database. However, upon running it, the results came back with some highly fragmented indexes, but null values in the name column. Is there another way to find out which indexes these are?

Ola Hallengren
Starting Member

33 Posts

Posted - 2008-06-27 : 13:53:09

Index_id = 0 means that it's a heap (not an index)
Heaps have a NULL value in the name column in sys.indexes.

You can read about heaps here.
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/19/761437.aspx

Ola Hallengren
http://ola.hallengren.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources