Recently, I experienced an interesting issue where I found SQL Server wait type PageLatch_UP went up drastically after migration. Let’s walk through to understand the problem and its solution.
Problem
After migrating SQL Server 2005 instances to higher versions – SQL 2008/SQL 2008 R2/SQL 2012/SQL 2014 and SQL 2016, I found the most of (not all) the migrated SQL Server instances suddenly started experience high wait type PAGELATCH_UP and PAGELATCH_SH.
Findings
The most surprising were when I found a SQL Server instance where we did an in-place upgrade from SQL Server 2005 to SQL Server 2012. Basically, we didn’t change anything at infra side except upgrading SQL Server Engine. The SQL Server instance also started experiencing high wait type after the upgrade. It gave a clue that the problem was not at infra side as the SQL instances were migrating to better hardware in-terms of CPU, RAM, and Storage.
- After analyzing the workload of the instances which were experiencing high wait type, I found that the SQL Server instances which were dealing with XML type of workload only those were experiencing high wait type “PAGELATCH_UP”.
- The wait type “PAGELATCH_UP” was happening at SQL Server Tempdb database. I quickly checked the tempdb contention issue but could not find contention on tempdb allocation bitmap pages.
Robert Davis wrote a fantastic blog on Breaking Down TempDB Contention
- Figured out all the queries which were generating the wait type PAGELATCH_UP and found the “insert into table variable select from OPENXML” statement was generating the wait type.
- To simulate this, you can copy the below script
DECLARE @command TABLE (id INT,
parentid INT,
notetype INT,
localname VARCHAR (50),
prefix VARCHAR(50),
namespaceuri VARCHAR(50),
datatype VARCHAR(50),
prev VARCHAR(50),
test VARCHAR(50)
)
DECLARE @idoc INT, @doc VARCHAR (1000);
SET @doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT statement that uses the OPENXML rowset provider.
insert into @command
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc;
--select * from @command- Open the SQL trace file, run the above query and check the number of pages allocation and the number of pages read in all the version of SQL Server;
SQL Server 2005
Let’s run the above query and see how many reads have done by SQL Server 2005
Verify the number of pages has been allocated to temp variable table
Verify the “Remote Scan” operator’s estimated number of rows
After removing the “Insert into @command”, the number of reads
SQL Server 2008
The number of reads has taken place in SQL Server 2008
Verify the number of pages has been allocated to temp variable table
Verify the “Remote Scan” operator’s estimated number of rows
After removing the “Insert into @command”, the number of reads
SQL Server 2012
The number of reads has done by SQL Server 2012
How many pages has been allocated to temp variable table
Remote Scan operator scan estimated number of rows
After removing the “Insert into @command”, the number of reads
SQL Server 2016
The number of reads has done by SQL Server 2016
How many pages has been allocated to temp variable table
Remote Scan operator scan estimated number of rows
After removing the “Insert into @command”, the number of reads
Observation/Conclusion
- From SQL Server 2008 onwards, SQL Server engine allocates more pages to temp table variable than SQL Server 2005.
- The change in page allocation because of “Insert into – select from”. Another thing to keep in mind that the targeted table is a heap table.
- Allocating more pages makes SQL Server read more and make a query to run slower than SQL Serve 2005.
- I suspect there is a change in the optimizer rule of the SQL Engine where it started allocating more pages to temp variable table from SQL Server 2008. For sure, it is NOT a bug. Otherwise, Microsoft may have released a fix for it.
- Remote Scan iterator has estimated the number of row 10,000 across all the version of SQL Server (2005-2016).
Solutions
There are two ways to fix the issue.
Fix it by enabling the trace flag 8692
- Enable the trace flag 8692 at the statement level, and it will be reduce the number of reads. I am running this testing on SQL server 2008 and it is applicable from 2008 to 2016.
Note: The trace flag 8692 is an undocumented trace flag.
- Let’s see how many pages has been allocated to temp variable table after enabling the trace flag 8692 at statement level
- After enabling the trace, the execution plan changed and the table spool iterator forced after the remote scan. The remote scan estimated row still is 10000, but the target temp table/table variable preallocate is based on actual row cardinality provided by the table spool iterator:
Fix it by changing the code
If you find, there are many times only row is being inserted into the temp table variable. Kindly change your script to follow below conditions
- When the XML parameter only parses as 1 row, we could select that result into several variables instead of the table variable
- When the XML parses as multiple rows, you can use the table variable
Reference
- Follow the link to get the OPENXML command – https://msdn.microsoft.com/en-us/library/ms186918.aspx
- To know more about the Remote Scan Operator default cardinality estimations : https://www.simple-talk.com/sql/performance/sql-server-statistics-problems-and-solutions/
I hope you enjoyed learning!
The post After migrating SQL Server from 2005 to higher version, the waits type PAGELATCH_UP increased appeared first on .
