{"id":89427,"date":"2021-01-05T00:13:04","date_gmt":"2021-01-05T00:13:04","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89427"},"modified":"2021-06-03T16:48:14","modified_gmt":"2021-06-03T16:48:14","slug":"heaps-in-sql-server-part-4-pfs-contention","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/heaps-in-sql-server-part-4-pfs-contention\/","title":{"rendered":"Heaps in SQL Server: Part 4 PFS contention"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Heaps in SQL Server: Part 1 The Basics<\/a><\/li>\n
  2. Heaps in SQL Server: Part 2 Optimizing Reads<\/a><\/li>\n
  3. Heaps in SQL Server: Part 3 Nonclustered Indexes<\/a><\/li>\n
  4. \u00a0Heaps in SQL Server: Part 4 PFS contention<\/a><\/li>\n<\/ol>\n\n

    After looking at the internal structures and the selection of data in heaps in the previous articles, the next articles will describe how DML operations can be optimized on a heap.<\/p>\n

    Demo set up<\/h2>\n

    I use data from a demo database for all demos for demonstration purposes in articles and conferences. You can download the database [CustomerOrders] here<\/a>.<\/p>\n

    In this article, I\u2019ll use an additional database, demo_db. Run the following script to create the demo_db database, a heap to test inserts, and a view pointing to [CustomerOrders].<\/p>\n

    CREATE DATABASE demo_db;\r\nGO\r\nUSE demo_db;\r\nGO\r\nCREATE TABLE dbo.Customers\r\n(\r\n\tId     INT          NOT NULL,\r\n\tName   VARCHAR(200) NOT NULL,\r\n\tCCode  CHAR(3)      NOT NULL,\r\n\tState  VARCHAR(200) NOT NULL,\r\n\tZIP    CHAR(10)     NOT NULL,\r\n\tCity   VARCHAR(200) NOT NULL,\r\n\tStreet VARCHAR(200) NOT NULL\r\n);\r\nGO\r\nCREATE VIEW dbo.CustomerAddresses\r\nAS\r\n\tSELECT\tC.Id,\r\n\t\tC.Name,\r\n\t\tA.CCode,\r\n\t\tA.State,\r\n\t\tA.ZIP,\r\n\t\tA.City,\r\n\t\tA.Street\r\n\tFROM\tCustomerOrders.dbo.Customers AS C\r\n\t\tINNER JOIN CustomerOrders.dbo.CustomerAddresses AS CA\r\n\t\tON (C.Id = CA.Customer_Id)\r\n\t\tINNER JOIN CustomerOrders.dbo.Addresses AS A\r\n\t\tON (CA.Address_Id = A.Id)\r\n\tWHERE\tCA.IsDefault = 1;\r\nGO<\/pre>\n
    Standard Procedure – INSERT<\/h2>\n
    When data records are entered in a heap, this process consists of several individual steps that are transparent to the applications. Knowing them leaves room for possible optimization of the process.<\/p>\n
    Update of PFS<\/h3>\n
    If a data row is stored in a heap and there is not enough space available on the data page, a new data page must be created. The data record can only be saved after the new page has been created.<\/p>\n
    In the first demo, insert one row into the formerly created table from the created view.<\/p>\n
    CHECKPOINT;\r\nGO\r\nINSERT INTO dbo.Customers\r\nSELECT\t*\r\nFROM\tdbo.CustomerAddresses\r\nWHERE\tId = 1;\r\nGO<\/pre>\n
    The above example adds a new record from an existing data source to the new table. Since the table was previously empty, the table structure must first be created. The undocumented function sys.fn_dblog () can be used to determine which tasks Microsoft SQL Server had to perform to insert the record into the table. I used CHECKPOINT to eliminate previous operations from appearing in the results below.<\/p>\n
    SELECT\tROW_NUMBER() OVER (ORDER BY [Current LSN])\t[Step #],\r\n\t[Current LSN],\r\n\tOperation,\r\n\tContext,\r\n\tAllocUnitName,\r\n\t[Page ID],\r\n\t[Slot ID]\r\nFROM\tsys.fn_dblog(NULL, NULL)\r\nWHERE\tCONTEXT <> N'LCX_NULL'\r\n\tAND AllocUnitName IS NOT NULL;\r\nGO<\/pre>\n
    <\/p>\n
    Figure 1: Recording from the Transaction Log<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Step(s)<\/p>\n<\/th>\n
    		\n
    Operation and Context<\/p>\n<\/th>\n
    		\n
    Description<\/p>\n<\/th>\n<\/tr>\n<\/thead>\n
    \n
    1 and 2<\/p>\n<\/td>\n
    		\n
    LOP_MODIFY_ROW \/ LCK_PFS<\/p>\n<\/td>\n
    		\n
    Since data pages are first created for the table, each assignment must be \u201cregistered\u201d in the PFS page. A data page and the IAM page are created and registered for the table.<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    LOP_FORMAT_PAGE \/ LCX_IAM<\/p>\n<\/td>\n
    		\n
    Creation of the IAM page for table dbo.Customers<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    LOP_MODIFY_ROW \/ LCX_IAM<\/p>\n<\/td>\n
    		\n
    Registration of the first data page in IAM page<\/p>\n<\/td>\n<\/tr>\n
    \n
    5 and 6<\/p>\n<\/td>\n
    		\n
    LOP_ROOT_CHANGE \/ LCX_CLUSTERED<\/p>\n<\/td>\n
    		\n
    Registration of table metadata in Microsoft SQL Server system tables<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    LOP_FORMAT_PAGE \/ LCX_HEAP<\/p>\n<\/td>\n
    		\n
    Preparation of the data page for the heap for storing the records.<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    LOP_ROOT_CHANGE \/ LCX_CLUSTERED<\/p>\n<\/td>\n
    		\n
    Storage of metadata in Microsoft SQL Server system tables<\/p>\n<\/td>\n<\/tr>\n
    \n
    9<\/p>\n<\/td>\n
    		\n
    LOP_INSERT_ROWS \/ LCX_HEAP<\/p>\n<\/td>\n
    		\n
    Insert row in Heap<\/p>\n<\/td>\n<\/tr>\n
    \n
    10<\/p>\n<\/td>\n
    		\n
    LOP_SET_FREE_SPACE \/ LCX_PFS<\/p>\n<\/td>\n
    		\n
    Update of the filling level of the data page for the PFS page<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Note:\u00a0 I describe the system pages and their functions in detail in the article \u201cHeaps \u2013 The Basics<\/a>\u201d.<\/p>\n
    If further records are entered, the existing data page is filled until it is – in percentage terms – so full that no new records can be saved on it and Microsoft SQL Server has to allocate the next data page in the system.<\/p>\n
    Run this script to add another 10,000 rows.<\/p>\n
    CHECKPOINT;\r\nGO\r\nDECLARE @I INT = 2\r\nWHILE @I <= 10000\r\nBEGIN\r\n\tINSERT INTO dbo.Customers\r\n\tSELECT * FROM dbo.CustomerAddresses\r\n\tWHERE   Id = @I;\r\n \r\n\tSET @I += 1;\r\nEND\r\nGO<\/pre>\n
    Another 10,000 records will be inserted into the table [dbo].[Customers] with the code above. Afterwards, look into the Transaction log to see the single transactional steps.<\/p>\n
    <\/p>\n
    Figure 2: PFS updates<\/strong><\/p>\n
    You can see that Microsoft SQL Server must update the PFS page several times (line 2, 46, 73, …). This is because the PFS page – only in the case of heaps \u2013 needs to be updated every time the next threshold is reached.<\/p>\n
    Bottleneck PFS<\/h3>\n
    The PFS page \u201ccan\u201d become a bottleneck for a heap if many data records are entered in the heap in the shortest possible time. How often the PFS page has to be updated depends mostly on the data record’s size to be saved.<\/p>\n
    This procedure does not apply to clustered indexes since data records in an index must ALWAYS be \u201csorted\u201d into the data volume according to the defined index value. Therefore, the search for a \u201cfree\u201d space is not carried out via the PFS page but via the value of the key attribute!<\/p>\n
    Microsoft SQL Server must explicitly check after each insert process whether the PFS page needs to be updated or not. If the above result is reduced to processes on the PFS page, the process is easy to recognize.<\/p>\n
    <\/p>\n
    Figure 3: Filtered operations from the log for PFS activity<\/strong><\/p>\n
    In total – due to the short data record length – the PFS page had to be updated 14 times in order to enter 10,000 data records in the heap.<\/p>\n
    At first glance, that may not seem like a lot – after all, 10,000 records were entered. However, it can become problematic for the PFS page as soon as more than one process wants to enter data in the table at the same time. To derive – imprecise due to the limitations of my test system! \u2013 a trend, I had the latches recorded on the PFS page with the help of an extended event session and then processed the above (wrapped in a stored proc) in parallel with a different number of clients.<\/p>\n
    CREATE OR ALTER PROC dbo.InsertCustomerData\r\n\t@NumOfRecords INT\r\nAS\r\nBEGIN\r\n\tWHILE @NumOfRecords > 0\r\n\tBEGIN\r\n\t\tINSERT INTO dbo.Customers\r\n\t\tSELECT * FROM dbo.CustomerAddresses\r\n\t\tWHERE   Id = @NumOfRecords;\r\n \r\n\t\tSET @NumOfRecords -= 1;\r\n\tEND\r\nEND\r\nGO\r\n\r\nCREATE EVENT SESSION [track pfs contention]\r\nON SERVER\r\nADD EVENT sqlserver.latch_suspend_end\r\n(\r\n    ACTION(package0.event_sequence)\r\n    WHERE\r\n    (\r\n        sqlserver.database_name = N'demo_db'\r\n        AND sqlserver.is_system = 0\r\n        AND mode >= 0\r\n        AND mode <= 5\r\n    )\r\n    AND class = 28\r\n    AND\r\n    (\r\n        -- only check for PFS, GAM, SGAM\r\n        page_id = 1\r\n        OR page_id = 2\r\n        OR page_id = 3\r\n        OR package0.divides_by_uint64(page_id, 8088)\r\n        OR package0.divides_by_uint64(page_id, 511232)\r\n    )\r\n)\r\nADD TARGET package0.event_file\r\n(\r\n\tSET filename = N'T:\\TraceFiles\\PFS_Contention.xel',\r\n\t\tMAX_FILE_SIZE = 1024,\r\n\t\tMAX_ROLLOVER_FILES = 10\r\n)\r\nWITH\r\n(\r\n    MAX_MEMORY = 4096KB,\r\n    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,\r\n    MAX_DISPATCH_LATENCY = 5 SECONDS,\r\n    MAX_EVENT_SIZE = 0KB,\r\n    MEMORY_PARTITION_MODE = NONE,\r\n    TRACK_CAUSALITY = OFF,\r\n    STARTUP_STATE = OFF\r\n)\r\nGO\r\n\r\n<\/pre>\n
    I carried out each series of tests five times to compensate for possible deviations. After each workload, the recordings from the extended event has been analysed with the following query:<\/p>\n
    SELECT\tCAST(event_Data AS xml) AS StatementData\r\nINTO\t#EventData\r\nFROM\tsys.fn_xe_file_target_read_file\r\n\t('T:\\TraceFiles\\PFS*.xel', NULL, NULL, NULL);\r\nGO\r\nSELECT * FROM #EventData;\r\nGO\r\nWITH XE\r\nAS\r\n(\r\n\tSELECT\tStatementData.value('(event\/@timestamp)[1]','datetime') AS [time],\r\n\t\tStatementData.value('(event\/@name)[1]', 'VARCHAR(128)') AS [Event_name],\r\n\t\tStatementData.value('(event\/data[@name=\"mode\"]\/text)[1]','VARCHAR(10)') AS [mode],\r\n\t\tStatementData.value('(event\/data[@name=\"duration\"]\/value)[1]','int') AS [duration],\r\n\t\tStatementData.value('(event\/data[@name=\"page_type_id\"]\/text)[1]','VARCHAR(64)') AS [page_type]\r\n\tFROM\t#EventData\r\n)\r\nSELECT\tXE.page_type,\r\n\tCOUNT_BIG(*)\t\tAS\tnum_records,\r\n\tSUM(XE.duration)\tAS\tsum_duration,\r\n\tAVG(XE.duration)\tAS\tavg_duration\r\nFROM\tXE\r\nGROUP BY\r\n\tXE.page_type\r\nGO<\/pre>\n\n\n\n\n\n\n\n
    \n
    Processes<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n
    		\n
    16<\/p>\n<\/td>\n
    		\n
    32<\/p>\n<\/td>\n
    		\n
    64<\/p>\n<\/td>\n<\/tr>\n
    \n
    PFS-Contention<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n
    		\n
    16<\/p>\n<\/td>\n
    		\n
    68<\/p>\n<\/td>\n<\/tr>\n
    \n
    avg. duration (\u00b5sec)<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    27<\/p>\n<\/td>\n
    		\n
    305<\/p>\n<\/td>\n
    		\n
    790<\/p>\n<\/td>\n
    		\n
    1.113<\/p>\n<\/td>\n
    		\n
    3.446<\/p>\n<\/td>\n<\/tr>\n
    \n
    Runtime (sec)<\/p>\n<\/td>\n
    		\n
    4,28<\/p>\n<\/td>\n
    		\n
    5,65<\/p>\n<\/td>\n
    		\n
    7,68<\/p>\n<\/td>\n
    		\n
    13,45<\/p>\n<\/td>\n
    		\n
    23,83<\/p>\n<\/td>\n
    		\n
    55,93<\/p>\n<\/td>\n
    		\n
    165,72<\/p>\n<\/td>\n<\/tr>\n
    \n
    avg (\u00b5sec)\/ row<\/p>\n<\/td>\n
    		\n
    428<\/p>\n<\/td>\n
    		\n
    2.825<\/p>\n<\/td>\n
    		\n
    192<\/p>\n<\/td>\n
    		\n
    16.813<\/p>\n<\/td>\n
    		\n
    16.769<\/p>\n<\/td>\n
    		\n
    17.478<\/p>\n<\/td>\n
    		\n
    25.894<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    <\/p>\n
    Figure 4: Dependence on processes to contention(s)<\/strong><\/p>\n
    The tests I carried out are not representative because external influences were not properly isolated. Nevertheless, one can deduce from the values that the potential for contention on the PFS page escalates with an increasing number of simultaneous processes.<\/p>\n
    <\/p>\n
    You know the problem from everyday life; You have to queue longer the more people want to use the same resource (till in the supermarket) at the same time. The bottleneck can be rectified by working with multiple files for the filegroup in which the heap is located – as is also common practice with TEMPDB.<\/p>\n
    <\/p>\n
    Figure 5: A separate database file for each core<\/strong><\/p>\n
    I performed the same workload with 4 database files for the PRIMARY filegroup, and the results have been observed with the Windows Resource Manager:<\/p>\n
    <\/p>\n
    Figure 6: Relatively even distribution of the write load – better throughput<\/strong><\/p>\n
    BTW: Now it is a good time to learn \u201ca few\u201d german words like<\/p>\n
    Datei = File<\/p>\n
    Lesen = read<\/p>\n
    Schreiben = write<\/p>\n
    It was to be expected that this would ease the situation. You can think of it as a situation in a supermarket where only one till is open at first. As soon as there are many customers in the supermarket, it accumulates in front of the till. Several cash registers are opened, and the situation is more relaxed again.<\/p>\n
    <\/p>\n
    Figure 7: Significant relaxation for the PFS pages<\/strong><\/p>\n
    Bottleneck data structure<\/h2>\n
    Anyone working with heaps must take the data structures into account. The biggest difference in the storage of data between an index and a heap is that data in a heap can be stored anywhere, while indexed tables must store the data according to the index attribute’s value. Storing data in a Heap can result in several problems:<\/p>\n
    Waste of storage space due to the calculation of the percentage of available storage space on a data page<\/p>\n
    Waste of memory in the buffer pool, since it is not the data itself that is loaded into the buffer pool, but the data pages on which the data is located<\/p>\n
    Increased contention on the PFS page if data records are too large and the percentage filling level has to be updated quickly.<\/p>\n
    Unused memory on a data page<\/h3>\n
    Memory is expensive and, for Microsoft SQL Server, it\u2019s an important component for fast queries. For this reason, you naturally want to avoid the situation where data pages are not completely filled, and thus RAM cannot be used.<\/p>\n
    To demonstrate this huge discrepancy between a Heap and a Clustered Index, create in the first scenario, a Heap table with a column C2 with a fixed size of 2,000 bytes for the payload. Afterwards, a Stored Procedure inserts 10,000 rows into the Heap table.<\/p>\n
    USE demo_db;\r\nGO\r\nDROP TABLE IF EXISTS dbo.Customers;\r\nGO\r\n-- Create a demo table\r\nCREATE TABLE dbo.Customers\r\n(\r\n   C1 INT\t\tNOT NULL\tIDENTITY (1, 1),\r\n   C2 CHAR(2000)\tNOT NULL\tDEFAULT ('Testdata'),\r\n);\r\nGO\r\n-- Create stored procedure for the INSERT process\r\nCREATE OR ALTER PROC dbo.InsertCustomerData\r\n\t@NumOfRecords INT\r\nAS\r\nBEGIN\r\n\tWHILE @NumOfRecords > 0\r\n\tBEGIN\r\n\t\tINSERT INTO dbo.Customers\r\n\t\t(C2)\r\n\t\tDEFAULT VALUES;\r\n\t\t\r\n\t\tSET @NumOfRecords -= 1;\r\n\tEND\r\nEND\r\nGO\r\n-- Execution of stored procedures for 10,000 rows\r\nEXEC dbo.InsertCustomerData @NumOfRecords = 10000;\r\nGO<\/pre>\n
    The example above creates the table [dbo].[Customers] and a simple Stored Procedure which gets the number of rows to be inserted from a variable. After the insert process, you can get insights into the data distribution with the next query, which retrieves the physical information about the stored data.<\/p>\n
    SELECT\tpage_count,\r\n\trecord_count,\r\n\trecord_count \/ page_count\tAS\tavg_rows_per_page,\r\n\tavg_page_space_used_in_percent\r\nFROM\tsys.dm_db_index_physical_stats\r\n\t(\r\n\tDB_ID(),\r\n\tOBJECT_ID(N'dbo.Customers', N'U'),\r\n\tNULL,\r\n\tNULL,\r\n\tN'DETAILED'\r\n\t)\r\nWHERE\tindex_level = 0;\r\nGO<\/pre>\n
    <\/p>\n
    With the table’s current design, two or three records (avg) can be stored on one data page. This means that a data page is filled with approx. 50 \u2013 75%. If you change the Heap Table to a Clustered Index Table, the results look completely different!<\/p>\n
    DROP TABLE IF EXISTS dbo.Customers;\r\nGO\r\n-- Create a demo table\r\nCREATE TABLE dbo.Customers\r\n(\r\n  C1  INT\t\tNOT NULL\tIDENTITY (1, 1),\r\n  C2  CHAR(2000)\tNOT NULL\tDEFAULT ('Testdata'),\r\n  CONSTRAINT pk_Customers_C1 PRIMARY KEY CLUSTERED (C1)\r\n);\r\nGO\r\n-- Execution of stored procedures for 10,000 rows\r\nEXEC dbo.InsertCustomerData @NumOfRecords = 10000;\r\nGO<\/pre>\n
    <\/p>\n
    The reason for this odd behaviour is that Microsoft SQL Server references ONLY to the PFS page when it comes to the storage of a record in a Heap while a Clustered Index always has to follow the restriction of the Clustered Key and stores the record on the position of the key in the table.<\/p>\n
    A clustered index outperforms \u2013 based on the storage consumption \u2013 the Heap due to the need to store a record based on the key attribute. But keep in mind that \u2013 different from a Heap structure \u2013 the INSERT process requires to follow the B-Tree structure when it must safe a record on a data page.<\/p>\n
    Note<\/p>\n
    Before you go for a Heap structure, perform some tests to understand your data distribution in the data pages!<\/p>\n
    Workload when inserting records<\/h3>\n
    The following demonstration shows the dependencies between the row size and the remaining free space on a data page.<\/p>\n
    IF OBJECT_ID(N'dbo.demo_table', N'U') IS NOT NULL\r\n\tDROP TABLE dbo.demo_table;\r\n\tGO\r\n-- The size of the column C1 will change with every test!\r\nCREATE TABLE dbo.demo_table (C1 CHAR(100) NOT NULL);\r\nGO\r\n-- Clear the log file for the analysis of PFS updates\r\nCHECKPOINT;\r\nGO\r\n-- This script will run for each test loop and insert \r\n-- 10,000 records into the table\r\nBEGIN TRANSACTION InsertRecord;\r\nGO\r\n\tDECLARE @I INT = 1;\r\n\tWHILE @I <= 10000\r\n\tBEGIN\r\n\t    INSERT INTO dbo.demo_table(C1) VALUES ('This is a test');\r\n\t    SET @I += 1;\r\n\tEND\r\n\t-- Afterwards we count the log entries for the PFS updates\r\n\tSELECT\tContext,\r\n\t\tCOUNT_BIG(*)\r\n\tFROM\tsys.fn_dblog(NULL, NULL)\r\n\tWHERE\t[Transaction ID] IN \r\n\t\t(\r\n\t\t\tSELECT [Transaction ID]\r\n\t\t\tFROM sys.fn_dblog(NULL, NULL)\r\n\t\t\tWHERE\t[Transaction Name] = N'InsertRecord'\r\n\t\t\t\tOR Context = N'LCX_PFS'\r\n\t\t)\r\n\tGROUP BY\r\n\t\tContext;\r\n\t-- and have a look to the avg space used in the heap\r\n\tSELECT\tpage_count,\r\n\t\tavg_page_space_used_in_percent\r\n\tFROM\tsys.dm_db_index_physical_stats\r\n\t\t(\r\n\t\t\tDB_ID(),\r\n\t\t\tOBJECT_ID(N'dbo.demo_table', N'U'),\r\n\t\t\t0,\r\n\t\t\tNULL,\r\n\t\t\tN'DETAILED'\r\n\t\t);\r\n\tGO\r\nROLLBACK TRANSACTION;\r\nGO<\/pre>\n
    The above demonstration has been run with different row sizes. The result of the tests with different row sizes gave the following results:<\/p>\n
    While the duration of the transaction runtime changes moderately (157 ms – 1.459 ms), the number of updates of the PFS page increases extremely beginning with a record length of 200 bytes (563 – 16.260). Although the PFS page refresh occurs quite frequently, the number of data pages grows moderately (149-5,000). The average filling level of a data page is between 75% and 100%, depending on the size of the row.<\/p>\n
    The PFS page’s frequent updating is explained by the growing size of a data record since fewer data records fit on one data page and the various thresholds can be reached more quickly.<\/p>\n\n\n\n\n\n\n\n\n\n\n
    \n
    Record Length<\/p>\n<\/td>\n
    		\n
    Time (ms)<\/p>\n<\/td>\n
    		\n
    PFS Update<\/p>\n<\/td>\n
    		\n
    Pages<\/p>\n<\/td>\n
    		\n
    Avg. Used space<\/p>\n<\/td>\n<\/tr>\n
    \n
    100<\/p>\n<\/td>\n
    		\n
    157<\/p>\n<\/td>\n
    		\n
    563<\/p>\n<\/td>\n
    		\n
    149<\/p>\n<\/td>\n
    		\n
    90,36%<\/p>\n<\/td>\n<\/tr>\n
    \n
    200<\/p>\n<\/td>\n
    		\n
    414<\/p>\n<\/td>\n
    		\n
    1,397<\/p>\n<\/td>\n
    		\n
    271<\/p>\n<\/td>\n
    		\n
    95,26%<\/p>\n<\/td>\n<\/tr>\n
    \n
    500<\/p>\n<\/td>\n
    		\n
    441<\/p>\n<\/td>\n
    		\n
    3,199<\/p>\n<\/td>\n
    		\n
    777<\/p>\n<\/td>\n
    		\n
    80,91%<\/p>\n<\/td>\n<\/tr>\n
    \n
    1000<\/p>\n<\/td>\n
    		\n
    595<\/p>\n<\/td>\n
    		\n
    5,916<\/p>\n<\/td>\n
    		\n
    1,436<\/p>\n<\/td>\n
    		\n
    86,79%<\/p>\n<\/td>\n<\/tr>\n
    \n
    2000<\/p>\n<\/td>\n
    		\n
    920<\/p>\n<\/td>\n
    		\n
    10,625<\/p>\n<\/td>\n
    		\n
    3,339<\/p>\n<\/td>\n
    		\n
    74,31%<\/p>\n<\/td>\n<\/tr>\n
    \n
    3000<\/p>\n<\/td>\n
    		\n
    1,138<\/p>\n<\/td>\n
    		\n
    16,069<\/p>\n<\/td>\n
    		\n
    5,004<\/p>\n<\/td>\n
    		\n
    74,27%<\/p>\n<\/td>\n<\/tr>\n
    \n
    4000<\/p>\n<\/td>\n
    		\n
    1,459<\/p>\n<\/td>\n
    		\n
    16,260<\/p>\n<\/td>\n
    		\n
    5,000<\/p>\n<\/td>\n
    		\n
    99,04%<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Let\u2019s do a little maths when data are stored on a data page.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Bytes<\/p>\n<\/th>\n
    		\n
    50%<\/p>\n<\/th>\n
    		\n
    80%<\/p>\n<\/th>\n
    		\n
    95%<\/p>\n<\/th>\n
    		\n
    100%<\/p>\n<\/th>\n<\/tr>\n<\/thead>\n
    \n
    100<\/p>\n<\/td>\n
    		\n
    40<\/strong><\/p>\n<\/td>\n
    		\n
    64<\/strong><\/p>\n<\/td>\n
    		\n
    76<\/strong><\/p>\n<\/td>\n
    		\n
    80<\/strong><\/p>\n<\/td>\n<\/tr>\n
    \n
    200<\/p>\n<\/td>\n
    		\n
    20<\/strong><\/p>\n<\/td>\n
    		\n
    32<\/strong><\/p>\n<\/td>\n
    		\n
    38<\/strong><\/p>\n<\/td>\n
    		\n
    40<\/strong><\/p>\n<\/td>\n<\/tr>\n
    \n
    500<\/p>\n<\/td>\n
    		\n
    8<\/strong><\/p>\n<\/td>\n
    		\n
    12<\/strong><\/p>\n<\/td>\n
    		\n
    15<\/strong><\/p>\n<\/td>\n
    		\n
    16<\/strong><\/p>\n<\/td>\n<\/tr>\n
    \n
    1000<\/p>\n<\/td>\n
    		\n
    4<\/strong><\/p>\n<\/td>\n
    		\n
    6<\/strong><\/p>\n<\/td>\n
    		\n
    7<\/strong><\/p>\n<\/td>\n
    		\n
    8<\/strong><\/p>\n<\/td>\n<\/tr>\n
    \n
    2000<\/p>\n<\/td>\n
    		\n
    2<\/strong><\/p>\n<\/td>\n
    		\n
    3<\/strong><\/p>\n<\/td>\n
    		\n
    3<\/strong><\/p>\n<\/td>\n
    		\n
    4<\/strong><\/p>\n<\/td>\n<\/tr>\n
    \n
    3000<\/p>\n<\/td>\n
    		\n
    1<\/strong><\/p>\n<\/td>\n
    		\n
    2<\/strong><\/p>\n<\/td>\n
    		\n
    2<\/strong><\/p>\n<\/td>\n
    		\n
    2<\/strong><\/p>\n<\/td>\n<\/tr>\n
    \n
    4000<\/p>\n<\/td>\n
    		\n
    1<\/strong><\/p>\n<\/td>\n
    		 <\/td>\n <\/td>\n\n
    2<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The above table shows the maximum records which \u201cshould\u201d fit on ONE data page when the threshold has exceeded. Please note that with a fill level of 95%, only 403 bytes<\/strong> (8.060 * (1-95%)) are mathematically available on the data page.<\/p>\n
    If the row size is 100 Bytes, Microsoft SQL Server can store 40 records on ONE data page before the threshold gets updated to 80%. It takes 24 more records before the next update to 95% will happen.<\/p>\n
    As bigger the row size is as faster will the thresholds be reached. Keep in mind that the row size has an direct impact on the possible contention on the PFS page.<\/p>\n
    Let\u2019s take a row size of 1,000 bytes for a record. With the 5th<\/sup> record, the PFS gets updated to 80%. When the 6th<\/sup> row (1,000 Bytes) must be stored on a data page, it will fit perfectly. From the table above, you can see the green and red values.<\/p>\n
    The green values mean that the records can be stored on the data page while the red ones show the records which will request a new data page!<\/p>\n
    Summary<\/h2>\n
    The aim when inserting new data in a heap is to avoid frequent updates of the PFS pages and to use the available space as max as possible. The next article will show how you can boost the performance when you insert data into a Heap.<\/p>\n","protected":false},"excerpt":{"rendered":"
    When inserting rows into a heap, SQL Server must sometimes update the PFS page which can PFS contention and affect performance. In this article, Uwe Ricken demonstrates how to measure PFS contention.…<\/p>\n","protected":false},"author":205038,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531],"tags":[101935,126398,4206,126397,5842,4151],"coauthors":[19794],"class_list":["post-89427","post","type-post","status-publish","format-standard","hentry","category-featured","category-t-sql-programming-sql-server","tag-heaps","tag-inserting-rows","tag-performance","tag-pfs-contention","tag-sql-monitor","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89427","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/205038"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89427"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89427\/revisions"}],"predecessor-version":[{"id":89454,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89427\/revisions\/89454"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89427"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89427"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}