Press "Enter" to skip to content

Curated SQL Posts

Developer Edition Now Free

Published 2016-04-01 by Kevin Feasel

Microsoft is making SQL Server 2014 Developer Edition free for Visual Studio Dev Essentials members:

Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members. We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.

Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support.

SQL Server 2016 will also be covered under this plan.  Granted, Developer Edition would not break the bank anyhow, but it does lower (ever so slightly) those barriers to entry, and I think it’ll be a driving point for SQL Server on Linux.

Comments closed

Updated Scheduling

Published 2016-04-01 by Kevin Feasel

The CSS SQL Server Engineers team talks about a new scheduling algorithm in SQL Server 2016:

SQL Server 2016 gets a scalability boost from scheduling updates.   Testing uncovered issues with the percentile scheduling based algorithms in SQL Server 2012 and 2014.  A large, CPU quantum worker and a short, CPU quantum worker can receive unbalanced access to the scheduling resources.

Take the following example.  Worker 1 is a large, read query using read ahead and in-memory database pages and Worker 2 is doing shorter activities.   Worker 1 finds information already in buffer pool and does not have to yield for I/O operations.    Worker 1 can consume its full CPU quantum. 

On the other hand, Worker 2 is performing operations that require it to yield.  For discussion let’s say Worker 2 yields at 1/20th of its CPU, quantum target.  Taking resource governance and other activities out of the picture the scheduling pattern looks like the following.

I’m going to have to reserve judgment on this.  It’s been in Azure SQL Database for a while, so I’m not expecting bugs, but I wonder if there are any edge cases in which performance gets worse as a result of this.

Comments closed

Against The Cloud

Published 2016-04-01 by Kevin Feasel

SQLWayne is against “the cloud:”

First off, the word itself.  The Cloud.  What is The Cloud?  It’s a server that you don’t own.  You can’t touch it, it’s in someone else’s data center.  It may or may not be virtual.  Amazon’s Cloud or Microsoft’s or Google’s are several data centers with racks and racks of servers.  They are physical, just not at your location.  And they’re accessed across the Internet.  This is something that we’ve been doing for 30 years, it’s called a Wide-Area Network, just scaled up bigger.  We had bi-coastal WANs before the World Wide Web came along.

Four or five years ago, I was absolutely in agreement.  Today, I’m 50/50, being near 100% for many types of servers (web servers, etc.) and closer to 25-30% for databases.  My expectation is that those numbers will continue to shift upward as time goes on, but there will always be reasons not to migrate certain servers to someone else’s data center.

Comments closed

Multi-Threaded Log Writer

Published 2016-04-01 by Kevin Feasel

Chris Adkin has a very detailed post digging into log writer changes affecting high-scale throughput:

To understand why we get this performance degradation with SQL Server 2016 RC1 three key parts of a transactions life cycle need to be understood along with the serialisation mechanisms that protect them

Chris digs into call stacks as part of his post.  We’ll see if there are some performance improvements between now and RTM on this front.

Comments closed

SAN Snapshots Aren’t Backups

Published 2016-04-01 by Kevin Feasel

Denny Cherry explains why SAN snapshots aren’t a good backup policy:

SAN snapshots, and I don’t care who your vendor is, by definition depend on the production LUN. We’ll that’s the production data.

That’s it. That’s all I’ve got. If that production LUN fails for some reason, or becomes corrupt (which sort of happens a lot) then the snapshot is also corrupt. And if the snapshot is corrupt, then your backup is corrupt. Then it’s game over.

SAN snapshots are a good part of an infrastructure-side recovery plan, but databases have their own recovery time and recovery point objectives.  Conflating these two can lead to sadness.

Comments closed

When To Test

Published 2016-03-31 by Kevin Feasel

Kenneth Fisher thinks about when to test:

To test or not to test. That is the question. Or is it? Shouldn’t we always test? That was the question posted here. It’s a big question so let’s break it down a bit.

My short answer is:  test as much as necessary to keep two conditions from occurring:  first, that you get fired (or worse); second, that the inevitable bug somebody else discovers in your code is too embarrassing.  Kenneth’s answer is better.

Comments closed

Indexes To Build Indexes

Published 2016-03-31 by Kevin Feasel

Erik Darling shows missing index requests while creating indexes:

So there I was, creating some indexes. Since I was in demo mode, I had execution plans turned on. I’m actually terrible about remembering to turn them off. There have been times when I’ve run loops to create some REALLY big tables, and left them turned on. SSMS crashed pretty quickly. Apparently generating execution plans isn’t free! Keep that in mind when you’re timing/tuning queries.

Since they were there, I took a look at them. They were basically what I expected. The only way to index data is to read it and sort it and blah blah blah. But there was a little something extra!

Yo dawg, I heard you like indexes…

Comments closed

Availability Group Backup Failures

Published 2016-03-31 by Kevin Feasel

James Anderson had a recent experience in which a database in an Availability Group failed to back up properly:

Last week, I received an alert that the percentage of transaction log in use on one of our production databases was increasing more than it should have been. I’ll refer to this database as DB1 from here on in. It had reached 18%, which is above the normal 5-10% we like to see it at. DB1 is in an Always On Availability Group so if the log usage jumps we jump.

The first thing I checked was that the log backups were running. We use Ola Hallengren’s maintenance solution to manage our backups. The backup jobs were running without error.

The next thing to check was the log_reuse_wait_desc column in sys.databases.

The highlighted row is the row for DB1. A log_reuse_wait of 2 means there were no changes to backup since the last log backup. If that were the case then why was my transaction log slowly filling like an unattended bath?

Read on to learn more.  James also has a link to a (closed/won’t-fix) Connect item.

Comments closed

Finding Changes

Published 2016-03-31 by Kevin Feasel

Aaron Bertrand  shows different ways of looking for metadata changes in different versions of SQL Server:

Back in December, I published a post entitled, “How I spot not-yet-documented features in SQL Server CTPs.” In that post, I revealed a few of the ways that I get an early jump on what’s changed between CTP and/or RC builds of SQL Server. You can do those same things if you want to see what new objects or columns have been created, or which system modules have changed, between – say – SQL Server 2014 and SQL Server 2016. This will likely be a bigger list than any of the individual sets of items I’ve posted about in our SQL Server 2016 builds post, but the same concepts apply – create a linked server to the older instance, optionally create some synonyms for easy adaptation, and go to town.

There are a few other things I check as we get closer to the final release, and they can be quite revealing about what features have made it into the product. We can also get some insight into things they tried to get in but couldn’t (for example, there are error messages and Intellisense verbiage for STRING_AGG(), which does not seem to be in the cards for RTM). I’m going to point out a few, but I’m not going to iterate through all of the things I’ve learned – this is more to serve as as a starting point so you can experiment on your own.

If you’re interested in this kind of spelunking, you can learn a lot without having to reverse engineer binaries.

Comments closed

Perspectives

Published 2016-03-31 by Kevin Feasel

Bill Anton walks through perspectives in Analysis Services:

In an enterprise solution, you might have 10+ perspectives – some of which might have similar names – and without a clear description it will be confusing for a user (especially new users) to know which perspective is the correct one.

A better idea is to add a description/annotation property for each perspective where a more helpful text description can be provided indicating the business process, common types of analysis, etc. This would provide a metadata hook for self-service reporting tools (e.g. Excel, Power BI) as well as enterprise data cataloging solutions such as Azure Data Catalog.

Another helpful feature would be the ability to set the visibility of a perspective – or if you’re more familiar with the Tabular vernacular: “hide it from client tools”!

While you’re reading about perspectives, fill out Bill’s SSAS survey.

Comments closed