SQL Server 2014 and Ascending Key Problem in Large Tables
Duration: 60 minutes
Track: F1 - The Performance
The SQL Server Query Optimizer relies on distribution statistics to create the query execution plan. But statistics are almost always out-of-date. For columns with ascending keys the most recent rows don’t exist for statistics objects. And common queries are looking for those rows. This can lead to poorly-performing plans, especially for large tables, where for instance a query that returns a few recent orders for a customer takes several seconds, with a few million logical reads.
In this session we’ll show how to identify columns with ascending keys and also query patterns where this problem occurs. Of course we’ll see how to get well performing plans with the queries in SQL Server 2012. According to documentation about new features in SQL Server 2014 the problem seems to be solved in the latest SQL Server version. But is this really so? What is different in SQL Server 2014? Does the problem still occur? As you guess, all those questions will be addressed and answered in this session