Not giving a shit about performance is tech-debt

For practically every piece of code you develop, there will be trade-offs. Sometimes, you can combine the best of two worlds, other times it comes down to some hard choices. For T-SQL developers, it typically boils down to a few key questions:

  • How much time can you spend perfecting code instead of just shipping?
  • Can we just fix it when it becomes a problem?
  • Is buying more hardware cheaper than paying for developers to tune their code?
  • Is better code harder to read, and will a junior developer be able to work with it?

Continue reading

Selectively disable “Include actual execution plan”

The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.

But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:

Continue reading

Detaching a database also alters file permissions

Moving a database or some of its files from one drive to another or from one instance of SQL Server to another is as simple as detaching it and re-attaching it again. This is actually pretty smart, compared to backuprestore, because you only perform one I/O operation (moving the file), as opposed to two (backing up, restoring).

But when you try to attach the database, you might get something like

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf".
Operating system error 5: "5(Access is denied.)".

The reason, as I found out the hard way, is that SQL Server can actually modify the file permissions of the .mdf and .ldf files when it detaches a database.

Continue reading

Migrating a database from Enterprise to Standard Edition?

You can move or copy a database from Enterprise Edition (or Developer Edition, which supports more or less the same feature set) to Standard Edition. The simplest way is to take a backup of the database and restore that on the new server. However, if there are any Enterprise Edition features left in the database, the restored database won’t start up, and you’ll get this error, or something similar:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database ‘databaseName’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)

------------------------------

Database 'databaseName' cannot be started in this edition of SQL Server because part or all of object 'myTableName' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'databaseName' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

Continue reading