The TL;DR of this is that you can use filtered indexes to keep all of your “hot data” in a separate physical structure, and even on separate underlying hardware (you may have a fast SSD or PCIe drive available, but it can’t hold the whole table).
Using filtered indexes is an interesting way of solving issues that partitioning normally would help you out with. I’m looking forward to the next part in the series.
Execution plans got a cool new piece of diagnostic information in SQL Server 2012 SP3, and we believe we’ll soon have this change in SQL Server 2014 and the coming SQL Server 2016: “Number of Rows Read”.
In fancy language, this is “better diagnostics” when a query plan has “residual predicate pushdown” (KB 3107397).
In human language, SQL Server will now tell you “How many rows did I really have to read, even if I have a hidden filter in here?”
This appears in actual execution plans only. Sorry, there is no such thing as “Estimated Number of Rows Read” that I can find.
This is another piece of useful information now available in execution plans. Hopefully it’ll be supported in SQL Server 2014 and 2016 soon enough.
Once you select the option to create a new server, you see similar options to those you saw in the Azure Management Portal. However, this time you see a server name.
Alas, you can again name your own database servers! As you can see above in the Azure Preview Portal, the server has been created with the provided name. Then if we switch over to the Azure Management Portal, we will see the same.
Choose your names wisely.
If you click on the query_plan link, you can see what the plan looks like. After you have reviewed it and determined the plan is bad then you can paste your plan handle over the one below to remove it from the proc cache.
DBCC FREEPROCCACHE normally is something you don’t want to play with in production, but this is narrowly focused enough not to harm you down the line.