I came across this solution recently when I had to shrink tempdb. I tried shrinking each of the 8 data files plus CHECKPOINTs, repeatedly. It would not budge. I almost threw in the towel and emailed out that the space issue would be fixed during our next patching window, but then I found David Levy’s reply. DBCC FREEPROCCACHE worked like a charm.
Word of warning: understand what FREEPROCCACHE does before running it. In an emergency like the scenario Tara describes, the benefit outweighs the cost, but do be aware that there is a cost.
In SQL Server 2016 the OLTP Systems have received a significant improvement – support for the Columnstore Indexes (disk-based Nonclustered Columnstore & In-memory based Clustered Columnstore).
In both cases we have as the base the underlying OLTP-style table, with a Delta-Store object (or Tail Row Group for InMemory tables), that will hold the new data being inserted or updated by the final users. The data that is being frequently updated in OLTP-style systems is called Hot Data. The data that just being inserted into your table is definitely a Hot Data.
The important moment for the table is when the data becomes Cold or mostly infrequently read-accessed, and meaning that it can be compressed into Columnstore format.
This does seem interesting and can be very helpful in using columnstore indexes across different data patterns.
Horizontal scaling refers to adding or removing databases in order to adjust capacity or overall performance. This is also called “scaling out”. Sharding, in which data is partitioned across a collection of identically structured databases, is a common way to implement horizontal scaling.
Vertical scaling refers to increasing or decreasing the performance level of an individual database—this is also known as “scaling up.”
It’s not free and application changes might be required (especially for horizontal scaling), but scaling with Azure SQL Database is pretty straightforward.
Continued enhancement of Stretch Database: Stretch Database allows you to stretch operational tables in a secure manner into Azure for cost-effective historic data availability. CTP 3.3 includes multiple improvements to Stretch Database, including Azure Stretch database edition preview with support for up to 60TB, Point-in-time restore and geo-failover support.
Enhancements to In-Memory OLTP: In-Memory OLTP, which dramatically improves transaction processing performance, has added support in CTP 3.3.
Enhancements to Analysis Services DirectQuery models: Analysis Services Tabular Models running in DirectQuery mode now also allows us of DAX filters when defining roles and creation of calculated columns.
Enhancements to the new Reporting Services web portal: An updated preview of the new web portal now enables you to add the KPIs and reports you use to your Favorites, to create and edit shared data sources for your KPIs and reports, and to perform other management tasks.
Admittedly, none of those strikes me as compelling “must-download” reasons but the technical overview does have some more details.
Remember that this is an application problem and is not a SQL problem. We only get into trouble when applications (or people) expect results to be sorted when they’re not. So unless you have a tiny application, or a huge amount of discipline, it’s likely that there is some part of your application that assumes sorted results when it shouldn’t.
Here’s a method I used that attempts to identify such areas, exposing those assumptions. It involves reversing indexes.
It’s an interesting idea to try out in a dev environment.