Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

SQL Server 2014 pushes the pedal to the metal

Sean McCown | March 21, 2014
SQL Server 2014 is a significant release with two overarching themes: cloud and speed -- or, to be specific, Azure integrations and in-memory OLTP (online transaction processing). Truth be told, I'm more excited about the speed features than the cloud stuff, but I also understand there is a growing portion of the customer base that is heading to cloud-based operations, and these shops will find the cloud features useful.

Resource Governor has finally gained physical I/O control. Disk is quite often the bottleneck on any given system because it's the one component with moving mechanical parts. Quite often, disks get overworked because of some rogue query sucking up tons of extra I/O against the disks. In fact, I've seen warehouses brought down by queries pulling billions upon billions of IOPS. Now you can finally put queries into their own resource pool and limit the amount of I/O per volume they're allowed. This means no more ridiculous queries taking up all of your precious disk resources. Reducing the amount of I/O on a disk system can have a tremendous impact on overall performance.

Taking a shortcut
This next feature, called Delayed Durability, doesn't actually speed up the database, but it makes the database seem faster from the end-user point of view. Let's walk through a typical transaction (simplified) to illustrate this.

Let's say the end-user updates a record. The update first gets written to the log in memory, and the log entry gets written to the disk. After the log entry is written to the disk, the application is informed that the transaction is complete and the user can continue working. The actual update to the database happens behind the scenes at a later time. Now, with Delayed Durability, the application is informed that the transaction is complete before the log is written to the disk. This means the end-user doesn't have to wait for the transaction to be hardened to the much slower disk media before he can continue working. I've seen many systems where the log was the bottleneck, and Delayed Durability can definitely help address that.

But like I said, Delayed Durability is not reducing the workload at all. Everything that used to be done is still being done. The database engine just releases control back to the client sooner than it used to, so the end-user can get back to work faster. This feature is configurable as well — you can control it at the database level, the transaction level, or even at the atomic block level (for an entire stored procedure). Nevertheless, be careful when considering this feature. If your system should fail before these transactions get hardened to disk, you will lose them. This is what durability means: The transaction is recoverable. If you delay the durability of your transactions to get better client response times, be aware that it's possible to lose some data.

And finally, SQL Server 2014 brings a couple of security enhancements. Of course we now have backup encryption, which is one more nail in the coffin of the third-party backup products. The final nail will be object-level recovery. There are also a couple of new server-level permissions — CONNECT ANY USER DATABASE and SELECT ALL USER SECURABLES — that allow you to manage security much more easily than ever before. The reason is that you can assign these permissions once for all current and future databases. You no longer have to keep assigning these permissions as databases come and go.

 

Previous Page  1  2  3  4  5  Next Page 

Sign up for CIO Asia eNewsletters.