Microsoft introduced in-memory OLTP in SQL Server 2014, advertising that queries can be up to 100 times faster. In this blog series I will explain the design principles of the in-memory OLTP engine to explain why it has the potential to be significantly faster than the traditional on-disk engine by skipping the complexity of handling the data pages in the buffer pool and totally eliminates logical and physical reads which can result in much faster query execution and less CPU impact.
This will be my last blog about the limitations of the missing index feature. This time I will demonstrate that the missing index feature will not suggest filtered indexes. Filtered indexes reduce the index size and thus the storage allocation in the data file is reduced. Rebuilding or reorganizing these indexes also requires fewer resources. So it's worth creating filtered indexes when possible. The following example will prove that the missing index feature will not consider creating filtered indexes.
The limitations of the missing index feature – It suggests creating indexes on imprecise and not persisted columns
This will be my penultimate blog about the limitations of the missing index feature and this one will be short. Before we start, let us clarify some terms so you can better understand the table we are going to create. The data types real and float are imprecise data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Non deterministic columns are computed columns which may return different results each time they are called.
In the last post you saw that the missing index feature does not consider the order for columns to be used in an index. In this blog I will explain another limitation of the missing index feature - it reports only include columns for some queries. SQL Server supports two kind of row store indexes - the clustered index and then non-clustered index. It is important to understand the difference between these two indexes in order to implement the right index type to improve reading performance.
The limitations of the missing index feature – It does not specify an order for columns to be used in an index
In the last post you saw that the missing index feature does not consider trivial plans. In this blog I will explain another limitation of the missing index feature - it does not specify an order for columns to be used in an index. SQL Server can only benefit from a row store index if the key columns are in the correct order. The order of the columns of an index determines whether SQL Server chooses an index scan or seek. Scanning an index simply means that the storage engine of SQL Server will read all index pages and filter the desired records afterwards (residual predicate)