June 5, 2019 at 5:11 pm
Greetings,
I have inherited a 2008 R2 SQL VM with SAP databases. I created a new 2016 VM and backed up/restored the databases and upgraded them to 2016. On the new server the TempDB data files grow to 150GB filling up the drive when we run the SAP Dataservices dataflow. If I restore the same databases and leave them in 2008 compatibility mode TempDB doesn't grow and stays under 10GB. Is there a known issue that would cause this?
Thanks,
AF
June 5, 2019 at 7:22 pm
When running on the SQL Server 2016 database, you are using the new cardinality estimator, and there may be a query as part of the SAP dataflow that performs differently, requiring worktables or some type of spillover into tempdb. Leaving the database in 2008 combatibility mode forces the SQL Server engine to use the old cardinality estimator and would thus use an execution plan more similar to the old system.
June 6, 2019 at 10:49 am
Thank you for your response. I appreciate it.
If I leave that database in 2008 compatibility mode will I still be able to utilize all of the 2016 functionality such as columnstore indexing?
June 6, 2019 at 4:04 pm
No, new features would not be available if the entire database is in compatibility mode. If you are able to examine the execution plans and figure out which queries are causing the TEMPDB usage, then there is a query hint available in 2016 that you can use on those individual queries to force just those to use the old cardinality estimator:
USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
June 6, 2019 at 5:09 pm
Thanks again, you have been most helpful. I have identified the query causing the issue and I will try using the hint to see if that resolves the issue.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply