0

I am reading Paul Randal's Investigating the proportional fill algorithm it says

The skip targets are recalculated whenever a file is added to or removed from a filegroup, or at least 8192 extent allocations take place in the filegroup.

I am playing in AdventureWorks on a SQL 2014 (SP2) instance. I have added and removed files but that does not cause skip target count to display in the error log. It only shows periodically (like on the 8192 extent allocations count). I have trace flag, 1165 on.

I have a query I run while doing an insert (or delete) to watch how data space on each file is being impacted. I would like to be able to see the 'skip target' value, real time on demand. Either as part of this query or a in a separate query.

--Identifies used space on files, run during a insert, or delete it shows data usage by file.
select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

How can I see the 'skip target' value on demand?

Current displays in error log. I added and removed AdventureWorks2014_Data_A3 between attempts and I also added a AdventureWorks2014_Data_X1 that did not make the count show in the error log.

LogDate ProcessInfo Text
2017-05-25 12:44:20.300 spid62  Proportional Fill Recalculation Starting for DB AdventureWorks2014 with m_cAllocs 8192.

2017-05-25 12:44:20.330 spid62  Proportional Fill Recalculation Completed for DB AdventureWorks2014 new m_cAllocs 8192, most free file is file 4.

2017-05-25 12:44:20.330 spid62      File [AdventureWorks2014_Data] (1) has 0 free extents and skip target of 393. 

2017-05-25 12:44:20.330 spid62      File [AdventureWorks2014_Data_A3] (4) has 393 free extents and skip target of 1. 

2017-05-25 12:44:20.330 spid62      File [AdventureWorks2014_Data_A2] (3) has 0 free extents and skip target of 393. 

2017-05-25 13:06:54.510 spid62  Proportional Fill Recalculation Starting for DB AdventureWorks2014 with m_cAllocs 8192.

2017-05-25 13:06:54.520 spid62  Proportional Fill Recalculation Completed for DB AdventureWorks2014 new m_cAllocs 8192, most free file is file 1.

2017-05-25 13:06:54.520 spid62      File [AdventureWorks2014_Data] (1) has 2054 free extents and skip target of 1. 

2017-05-25 13:06:54.520 spid62      File [AdventureWorks2014_Data_A3] (4) has 0 free extents and skip target of 2054. 

2017-05-25 13:06:54.520 spid62      File [AdventureWorks2014_Data_A2] (3) has 230 free extents and skip target of 8. 

2017-05-25 14:16:32.010 spid62  Proportional Fill Recalculation Starting for DB AdventureWorks2014 with m_cAllocs 8192.

2017-05-25 14:16:32.020 spid62  Proportional Fill Recalculation Completed for DB AdventureWorks2014 new m_cAllocs 8192, most free file is file 3.

2017-05-25 14:16:32.020 spid62      File [AdventureWorks2014_Data] (1) has 1517 free extents and skip target of 2. 

2017-05-25 14:16:32.020 spid62      File [AdventureWorks2014_Data_X1] (5) has 427 free extents and skip target of 10. 

2017-05-25 14:16:32.020 spid62      File [AdventureWorks2014_Data_A3] (4) has 4320 free extents and skip target of 1. 

2017-05-25 14:16:32.020 spid62      File [AdventureWorks2014_Data_A2] (3) has 2944 free extents and skip target of 1. 

Edit after some experimenting it seems like this statement by Paul Randal is correct

The skip targets are recalculated whenever a file is added to or removed from a filegroup, or at least 8192 extent allocations take place in the filegroup.

But at least in SQL 2014, the recalculated values are not displayed in the error log for adding or deleting a file, only events for the 8192 extent allocations are displayed. Watching file growth and paying attention to when the 8192 extent allocation recalculation occurs I was able to surmise that most of the article is accurate.

For the following, I saw some behavior that partial supports and partially conflicts with 'skip target' decreasing by one on each non-allocation.

During the round robin, the skip target for a file is examined, and if it’s equal to 1, an allocation takes place. If the skip target is higher than 1, it’s decremented by 1 (to a minimum value of 1), no allocation takes place, and consideration moves to the next file in the filegroup.

It would be very interesting to be able to watch 'skip target' value change as new records are written.

Keeping in mind that 'skip target' values are recalculated with about every 500MB of new allocation; for the most part, Proportional fill is concerned with 10's or 100's of GB's. Details about skip target for a couple of MB of data being written are mostly academic.

1

There are parameters for xp_readerrorlog.

So I think you can create 2 query for recent operations.

  1. exec xp_readerrorlog 0, 1, 'Proportional Fill Recalculation', '', NULL, NULL, N'desc' for Proportional Fill Recalculation logs.
  2. exec xp_readerrorlog 0, 1, 'skip target', '', NULL, NULL, N'desc' for skip target logs.
1
  • Thank you, but not exactly what I am looking for. This only shows the values when/where displayed in the error log. the values change as the round robin decides where to write the next data. You are not seeing the current value you are seeing the last value reported in the error log. Only the first 3 parameters are needed to for to get the results, so this will do it: exec xp_readerrorlog 0, 1, "skip Target" – James Jenkins May 26 '17 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.