Month: April 2016

Jos de Bruijn notes that there is a way to use the legacy cardinality estimator even when you are in Compatibility Level 130 (new in SQL Server 2016):

In SQL Server 2016 there are a lot of goodies under Compatibility Level 130, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. So we are faced with a situation where there are workloads that can benefit from some enhancements that come with Compatibility Level 130, but that cannot use the new CE.

The solution is to use one of the new database-scoped configuration options, namely the Legacy Cardinality Estimation option. You can enable this using the following ALTER DATABASE command:

I’ve had a very positive experience with the new cardinality estimator, but I certainly appreciate the option being there just in case.

Comments closed

If “shrink that database” makes you cringe, that’s a good sign.  Nevertheless, sometimes it has to happen.  On that rare occasion in which it needs to happen, Andy Mallon gives you a how-to guide:

Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.

Shrink should not be part of your normal routine, but it does come in handy sometimes.  Auto-shrink, though?  Burn that to the ground.

Comments closed

Slava Murygin walks through running SQL Server on Windows Server core.  First, the steps for installing SQL Server at the command line:

There are three reasons why Windows Server with Core is better than Server with a GUI:
1. Security: Core has less features. That means less surface for intruders to attack.
2. Management: No unnecessary updates and planned reboots.
3. Resource consumption: Less memory usage and less CPU overhead

Even if you aren’t using Windows Server Core, it’s a good idea to be able to script out your SQL Server installation so you can automate it later (or at least so you don’t forget options and have some servers look different than others).

Second, Slava notes that certain features are not compatible with Windows Server core:

The following error occurred:
You have selected a feature that is not supported on Windows Server Core.
The supported features on Windows Server Core are:
Database Engine Services,
SQL Server Replication,
Full-Text and Semantic Extractions for Search,
Analysis Services,
Client Tools Connectivity,
Integration Services,
and SQL Client Connectivity SDK.

That list is a bit more limiting than I’d like, to be honest.

Comments closed