SQL Server Fragmentation| Internal And External Fragmentation Part 2

So yes readproblemwithfragmentation is heavily fragmented and the copyofreadproblemwithfragmentation is not, to test the read latency with fragmentation using the simple queries, the query is designed in that way so it can use index order scan and  it can request the data from storage engine in index order, you can verify this in execution plan data access strategy would be index seek with ordered true property.
Please note in this test we are using DROPCLEANBUFFERS command, it will clear all the Buffer Cache so do not run this in the production server.

CHECKPOINT;--Flush all the Dirty Reads
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; --Clean the buffer Cache
GO
SET STATISTICS TIME ON;
Select COUNT_BIG (*) FROM ReadProblemsWithFragmentation  WHERE Primarykey>0 OPTION (MAXDOP 1)
SET STATISTICS TIME OFF;
GO
CHECKPOINT;--Flush all the Dirty Reads
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; --Clean the buffer Cache
GO
SET STATISTICS TIME ON;
Select COUNT_BIG (*) FROM CopyofReadProblemsWithFragmentation  WHERE Primarykey>0  OPTION (MAXDOP 1)
SET STATISTICS TIME OFF;
GO

You can see in the above GIF both tables consist same amount of data, but CopyofReadProblemsWithFragmentation table executed within 5980 ms whereas Readproblemwithfragmentation took 13051 ms. Keep in mind, we conducted the test in the pretty small table.
In the real world we can have very large fragmented tables and in some scenarios we are required to issue large data scan and that could lead to dramatic performance effect if the table is heavily fragmented.


The performance is heavily depends on so many things, when you test the demo you might find some odd results because it depends on the hardware type you have, for example, I am conducting these tests on traditional rotating hard disk subsystem which is infamous for Its random read latency where the SSD or SSD NVMe or Fusion IO are a much improved version of traditional disk subsystem and  perform way faster than traditional disk subsystem and even some external media, random reads performed better than sequential reads.
Your read milage also depends on timings for example, even if you have an ideal machine still you might find these tests are not as expected because there might be some operating system or some other background system process is running and that can impact the overall performance metrics etc etc.
The laptop I am currently using has traditional rotating hard disk and i captured its performance benchmark using crystal disk mark, and a matrix of my laptop is:

If you are more interested in learning about CrystalDiskMark you ask in the comment section or there is a quite nice article by Brent Ozar you can read it here.

Query Optimizer doesn`t consider external fragmentation

In this section we will try to learn that does the Query Optimizer consider the fragmentation in the generation of execution plan or not. We will use the both above table reeadproblmewithfragmetation and copyofreadproblemwithfragmentation  and check the difference in the execution plan to see the execution you can use shortcut CTRL+M or right click on the SSMS query window and choose "include an actual execution plan" or you can select it from the title bar and run the query (this can be seen in estimated execution plan a well).
Look at the query and execution plan below:

Select COUNT_BIG (*) FROM  ReadProblemsWithFragmentation   WHERE Primarykey >0

Select COUNT_BIG (*) FROM  CopyofReadProblemsWithFragmentation   WHERE Primarykey >0 

you can see in the above execution plan the fist query is costing 60 percent of this batch and bottom query is costing 40 percent of this batch(these costs are arbitrary and doesn`t reflect the actual cost of the query) just because of the number of pages in the both index and if you look at the execution plan more closely you will see the the top plan is mainly costing high because of its Estimated IO Cost on Index Seek  otherwise  everything in the both execution plan is almost same.
But that is because of number of pages not the fragmentation. to prove that we will fake the page count of the top table so the Query Optimzer can generate the execution plan on the basis of those fake numbers.
 Again, this is just for testing purpose and not at all recommended on the production system.
Lets us match the page count of the both tables so we can see does the Query Optimizer take fragmentation into its query plan costing.
The bottom table has 71429 number of pages in the leaf level, so we are faking the pagecount of top table with  71429 using the undocumented update statistics option(again its undocumented command and not recommended in the production server, we presented it for educational purpose only).

Update Statistics ReadProblemsWithFragmentation with pagecount = 71429 -- Number of Leaf/Data level pages in the Index

Now check the execution plan again:

Select COUNT_BIG (*) FROM  ReadProblemsWithFragmentation   WHERE Primarykey>0

Select COUNT_BIG (*) FROM  CopyofReadProblemsWithFragmentation   WHERE Primarykey>0 

Look at the cost of both queries they are identical, only because of the Estimated IO cost of index seek has reduced in the query, but we should not forget the still the top query is highly fragmented and the Query Optimizer is not considering this fact.
So the question arises shouldn`t it consider the fragmentation in the query plan generation?
Well, it doesn`t matter, even if the Query Optimzer stating considering the fragmentation in the query generation plan just the query cost will increase, but the plan quality won`t because after the data have pulled from the secondary storage subsystem, then there is no effect of the fragmentation on the query execution, but yes page count and number of rows matter a lot.

External Fragmentation Doesn`t Matter

External fragmentation has no effect at all, if all the data pages are in the buffer cache and relational engine is pulling all the requested data from the buffer cache because that's how RAM (random access memory) works, if you are working on the pure OLAP environment where you worry about read only and your organization has provided you a lots of buffer  cache, and most of the times your requested data is stays in the memory then its not as worry some to think a lot of about external fragmentation about the fragmentation. Brent Ozar has written a full length article on that you can read it here.
Yes, it's true, but keep in mind here we are only and only considering reads with external fragmentation.
So here we go, let us use the last example of where we saw ReadProblemsWithFragmentation table, took more than 13 seconds and CopyofReadProblemsWithFragmentation table took approx 6 seconds, this time we will pull all the data in the buffer cache by running the query couple of times then measure the performance time to check how much time the table take to perform the query:

SET STATISTICS TIME ON;
Select COUNT_BIG (*) FROM  CopyofReadProblemsWithFragmentation   WHERE Primarykey>0 option (maxdop 1)
GO
Select COUNT_BIG (*) FROM  ReadProblemsWithFragmentation   WHERE Primarykey>0  option (maxdop 1)
SET STATISTICS TIME OFF;
GO

You can see in the above image , the query which was taking 13 seconds to execute from the disk subsystem now taking only 177 ms to execute from the buffer pool, whereas the other table which were taking 5 Sec to execute now taking only 77 seconds to execute.
Once the requested data in the cache it don`t matter, it is fragmented or not.

Variable Length Data Type And External fragmentation

So far till now we have seen only one way which makes the table fragmented, but you might be wondering that in your production server you have static and ever increasing clustered index/primary key and still you find your tables are fragmented right?
So the reason behind is the Storage Engine does not allocate the full size(and that make sense) to the variable data types as they are variable in the nature, so if the data page is full enough and it does not have any extra space in the page and variable data column updates so the page split is bound to happen and fragmentation occurs in the table.
So let us try to understand this with an example, in this example we will use the table from the last part of fragmentation series and see the external fragmentation level in the table.

--BEFORE UPDATE
SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count
FROM sys.dm_db_index_physical_stats
(db_id('TutorialSQLServer'), object_id ('Fragmented' ), null ,null,'DETAILED')
GO

Ok, so we can see in the above image that the Clustered Index is not externally fragmented and page density is near about 100 percent so this table is ideal to test the scenarios we discussed above, so let us update the table`s keycol which has varchar datatype with size 50.

UPDATE Fragmented SET KeyCol = replicate ('A',50) GO

The table is updated so, according to the above theory the table should be fragmented externally, so let us again use the sys.dm_db_index_physical_stats  DMV to find out the level of fragmentation.

--AFTER UPDATE
SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count
FROM sys.dm_db_index_physical_stats
(db_id('TutorialSQLServer'), object_id ('Fragmented' ), null ,null,'DETAILED')
GO

WOhhhhh.. see the above image now the clustered index is highly fragmented so yes variable column plays the major part to fragment the index and usually you can not avoid this situation as you don`t know what length should be provided and can not restrict the user to update the column :)