SQL 2008 - Index Defrag Problems...

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 Administration

SQL 2008 - Index Defrag Problems...

Author

Topic

Westside
Starting Member

9 Posts

Posted - 2010-03-09 : 23:41:40

Hi,

I am using SQL 2008 and I'm trying to learn and actually defrag my indexes on a few databases.

I'm using this query to see which databases need their indexes rebuilt

SELECT s.name, st.*
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) st
INNER JOIN sys.databases s ON s.database_id = st.database_id
WHERE avg_fragmentation_in_percent > 30
ORDER BY avg_fragmentation_in_percent desc

I then run the script found here:

http://sqlfool.com/2009/06/index-defrag-script-v30/

I don't get any error but the values in the "avg_fragmentation_in_percent" column don't change. I'm unclear why.

Any help appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-10 : 14:38:02

Here's my custom code: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspx

The fragmentation may not change if the tables are small. How many data pages are in the indexes that are having this "issue"? At about 1000 pages and under, indexes can't be defragmented.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources