question about fragmented indexes

We have an application that was running slow.  one of their level II guys pointed out how badly the indexes were fragmented on the database the app used.   I ran an reorganize and reindex on the db in question and the stats don't seem any better after i run the query.
do you guys have any suggestions about how i should fix a badly defragged database besides reorganize and reindex

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
jamesmetcalf74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
After you reorg/rebuild your indexes it is a good idea to update the statistics.  Try that and see if it helps.

https://msdn.microsoft.com/en-us/library/ms187348.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamesmetcalf74Author Commented:
I just completed task you suggested and the results seem about the same as beforehand.


the query results in 1641 rows.
about 250 tables seem highly fragmented.
1400 seem fine.

does the above described result seem standard.
I realize it would differentiate between db's but the support rep is telling me this result is bad....

not sure what else I should do to get  a better result
0
Brian CroweDatabase AdministratorCommented:
Run the query below to evaluate your index fragmentation.  Are you still seeing high levels of fragmentation?

SELECT [Schema].[name] as 'Schema', 
	[Table].[name] as 'Table', 
	[Index].[name] as 'Index',
	IStat.avg_fragmentation_in_percent,
	IStat.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS IStat
INNER JOIN sys.tables AS [Table]
	ON [Table].[object_id] = IStat.[object_id]
INNER JOIN sys.schemas AS [Schema]
	ON [Table].[schema_id] = [Schema].[schema_id]
INNER JOIN sys.indexes AS [Index]
	ON [Index].[object_id] = IStat.[object_id]
	AND IStat.index_id = [Index].index_id
WHERE IStat.database_id = DB_ID()
ORDER BY /*IStat.page_count DESC,*/ IStat.avg_fragmentation_in_percent desc

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jerry_JusticeCommented:
Are you sure you are rebuilding each index?  You shouldn't be seeing "highly fragmented" tables immediately after rebuilding.

What method, exactly, are you using to defrag these indexes?
0
jamesmetcalf74Author Commented:
Brian I ran your query and basically got the same result.

Jerry- here is the maintenance task I am running.
I confirmed the right db is selected on all three tasks
see attached
defrag-fix.jpg
0
Brian CroweDatabase AdministratorCommented:
I have never really trusted the built in index defrag maintenance tasks.  I strongly recommend using the Olla Halengren scripts.  They are widely used in enterprise production environments all over the place.  It is very easy to setup and configure.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
0
Scott PletcherSenior DBACommented:
For tables less than 8 pages, SQL will share extents for them, so that each table may store each page in a separate extent, which is very fragmented, but SQL won't combine them even during a rebuild.  Most people just ignore tables than 8 pages.  If you prefer, you can add dummy rows to force the table to be more than 8 pages -- at which point SQL will switch to using extents dedicated to rows only for that table -- then delete the dummy rows.  Do one final rebuild.  (Once SQL makes a table dedicated, it doesn't "de-dedicate" it even if the page count goes below 8).  


>> After you reorg/rebuild your indexes it is a good idea to update the statistics. <<

Actually that's the worst thing you can do for performance, particularly after a rebuild.  When SQL rebuilds an index, its statistics are then based on all the actual data -- a 100% sampling in technical terms.  When you update stats, SQL will typically just sample a much smaller percentage of rows, typically 5-20%.  Obviously stats based on 100% of data will be more accurate than stats based on a much smaller subset of data only.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.