Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Fragmentation Presentation Follow Up

  • 12 November 2013
  • Author: Kathi Kellenberger
  • Number of views: 9615

Thanks to all of you who attended my presentation on fragmentation. If you would like to view the video, you can find it here. You can also download the code and slides if you would like.

As always, there were several questions during the session. If there is something else you would like to ask, please leave a comment.

Q: What is the command to get the fragmentation statistics?
A: It is a dynamic management function sys.dm_db_index_physical_stats

Q: If I delete and rebuild all clustered in a database will the database be totally de-fragmented?
A: Doing this will remove the fragmentation from all clustered and non-clustered indexes. This is a brute force approach that is not really recommended.

Q: If the MDF and LDF are on a fast SSD is fragmentation as much of a problem?
A: It is not as much of a problem except that the indexes will take up more space when they are fragmented, wasting space on your expensive SSD. Also, fragmented indexes can cause more pages to be read into data cache that may not be needed.

Q: Do we need to rebuild indexes on the replicated database?
A: Replication and fragmentation guru SQLFool says that you do need to defragment replicated tables:

Q: How often should to do the REBUILD or REORG for OLTP db?  Is it affect to performance?
A: This is one of those it depends questions. I would say it is typically done once per week. Yes, rebuild can affect performance during the process and is generally an offline operation.

Q: Are indexes fragmented on SAN Disks?
A: Yes

Q: Hi, Will the recording be available for future viewing?
A: Yes

Q: My understanding of 3par is SAN split files into equal sized chunks which are distributed by SAN around the disks.  Fragmented by design.  Kathi mentioned that she was not sure about SANs.
A: So, I guess you are saying the files and extents will be fragmented by design. I would think you still need to be concerned about physical and logical fragmentation in the indexes and forwarding pointers in the heaps.

Q: sp_updatestats?
A: We didn’t talk about statistics at all during this presentation. During a REBUILD operation, the statistics are updated. You can have the auto update statistics enabled and do a periodic statistics update.

Q: What does the Scan Count represent?
A: The Scan Count is part of the STATISTICS IO information. It is the number of times the table was scanned.

Q: So can i do fragmentation on tables with large data which is used for lots of retrreivals
A: I’m not sure what you are asking. If you see this, please leave a comment with more information.

Q: Rule of thumb, how many pages should you be  worried about if the fragmentation is high?
A: 1000

Q: Can we get copies of the scripts?  Thanks...
A: Yes, they are posted here

Q: I hear all the time that shrinking is bad, but someitmes it is necessary to shrink due to millions of rows of data being removed from a database.  Optimizing after a shrink is a best practice I guess.
A: Right, and there are some options like creating a new file group and moving the tables. Delete the original file group later.

Q: what is  good tool for monitor the index
A: Use the scripts from Ola Hallengren  or SQL Fool. There are third party tools that you can buy. I haven’t used any of them.


Categories: SQL Server
Rate this article:
No rating

Please login or register to post comments.