SQL Server Image http://www.Oostdam.Info General pictureMicrosoft SQL Server is a great product to work with. Especially from the release of version 2005 and now 2008. For me, it is the standard for all kinds of databases. The Object explorer, Tuning Advisor, the Query Analyser and T-SQL statements work great, and are a joy to work with. When it goes wrong somewhere, it is mostly a syntax error, program(bug) error or a user error that causes the problem. Stable as it is, I can recomment the use of it to anyone. In this articles I like to share with you, some errors and "best practices" that came up during my work several times. So, initially set up was for my own references, now I share it with you. Enjoy these articles!! Due to the fact that a big percentage of visitors of this page are international based, this article is in the english language.


Why are searches so slow sometimes? Why is the screen so slow? Why is my report taking so long? These are the questions that are often asked to database administrators. And you better find a solution fast!! This could be very difficult sometimes. First thing to look at, is actually what tables are involved, what maintenance plans are running etc. Now, when you've done that, it might be handy to check if the index fragmentation is high. That causes often a lot of performance issue's. For SQL 2005 and 2008 there has even been build a special function for it.


How to determine all the Database Index Fragmentations:
Underneath you find a script which can be used to determine the fragmentation level of each index used in a SQL database. Replace the name DATABASENAME with the name of the actual database, you're having doubts about.  Here is what you do. Open the SQL Management Studio and create a new manual Query. Type the following.


USE DATABASENAME
GO
SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count, 
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'DATABASENAME'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO


At the SQL Query Result Screen you will find the fragmentation levels for each index.
You can copy/paste them in Excel for nice (colored)reports. Now thats an idea!


Let me explain something about the result colomn's. ( this table overview has been copied from a nice site

Returned ColumnDescription
avg_fragmentation_in_percentIt indicates the amount of external fragmentation you have for the given objects.

The lower the number the better - as this number approaches 100% the more pages you have in the given index that are not properly ordered.

For heaps, this value is actually the percentage of extent fragmentation and not external fragmentation.

avg_page_space_used_in_percentIt indicates how dense the pages in your index are, i.e. on average how full each page in the index is (internal fragmentation).

The higher the number the better speaking in terms of fragmentation and read-performance. To achieve optimal disk space use, this value should be close to 100% for an index that will not have many random inserts. However, an index that has many random inserts and has very full pages will have an increased number of page splits. This causes more fragmentation. Therefore, in order to reduce page splits, the value should be less than 100 percent.

fragment_countA fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. An index has at least one fragment. The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. So the less fragments the more data is stored consecutively.
avg_fragment_size_in_pagesLarger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance.



NOTE: This query can take a long time to run, please consider this before running this query! And good luck debugging!



Printscreen: Not available.


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

Ben OostdamBen Oostdam has been working with Windows systems since 1993. Worked for several companies as a system administrator, and is currently a Senior Support Engineer for Qurius Managed Services in the Netherlands specialized in System Center Solutions.

Disclaimer: The information contained in this website/article is for general information purposes only. The information is provided as is, by Ben Oostdam and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk. In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website. Through this website you are sometimes able to link to other websites which are not under my control. I have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them. Every effort is made to keep the website up and running smoothly. However, I take no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control. All entries in these articles, are my individual opinion, or from co-writers and they don't necessary reflect the opinion of my employer.





 

Tuesday the 15th, October 2019. All rights reserved.. // Oostdam WebDesign