Press "Enter" to skip to content

Month: October 2016

Unit Testing With SSDT And tSQLt

Published 2016-10-13 by Kevin Feasel

Steph Locke looks at adding tSQLt to an existing SQL Server Data Tools database project:

Whilst I won’t be showing code in this, there is a companion sample database project. This is on GitHub and each key stage is shown by a branch of work. This means you can jump in at most stages and work from there. If you need some git advice, check out my quick git in 5 presentation.

The core DB is a super hero themed database.

Database unit testing involves a lot of compromises (because most of what we care about as database professionals tends to be integration tests rather than unit tests), but having imperfect tests generally beats having no tests, so check this out.

Comments closed

Windows Server 2016 Licensing

Published 2016-10-13 by Kevin Feasel

Brent Ozar looks at an ugly aspect of Windows Server 2016 licensing:

Windows Server 2016 is now licensed by the core, but that’s not really the big problem. The bigger problem is buried in the licensing guide (PDF):

  • “A minimum of 16 core licenses is required for each server.”
  • “A minimum of 8 core licenses is required for each physical processor.”

When most sysadmins see that, they’ll think, “Okay, so I shouldn’t bother buying a server smaller than 2 8-core processors.”

There are plenty of scenarios in which this doesn’t hurt (much):  mainly when you need a hefty server with more than 16 cores, or when you are running in a virtualized environment and can split that hardware across a number of logical servers.

As for me, this is one reason why I’m looking forward to SQL Server on Linux.

Comments closed

Format Your Code

Published 2016-10-13 by Kevin Feasel

Grant Fritchey looks at some poorly-formatted code:

You are going to cause all sorts of problems if you write code like this. First off, if you really do have these three queries within the same stored procedure, how hard will it be to confuse which is table ‘a’ in each of the queries when you go back to edit them? Pretty easy.

It gets worse though. I know that none of us will ever write a query that exceeds 26 tables in a JOIN… well, except that one time… and that other time. In fact, it happens. Oh, it’s not always a good thing, but it’s a thing. How do we respond to that? I’ve seen this:

Some of this stuff is egregious.  Some of it is debatable.  But either way, well-formatted code helps prevent bugs and aid understanding of queries.

Comments closed

Syncing Logins Between AG Replicas

Published 2016-10-13 by Kevin Feasel

Daniel Hutmacher has a new series on Availability Group synchronization, starting with logins:

You’ll need a linked server from your secondary replica to the primary replica. This linked server should feature the absolute bare-minimum of permissions, preferably with only the “be made using the login’s current security context” selected.

The account running the procedure (or the mapped login in the linked server) will need SELECT access to the following DMVs on the remote (primary) server:

  • master.sys.server_principals

  • master.sys.sql_logins

  • master.sys.server_role_members

  • master.sys.server_permissions

The code itself is a download from Daniel’s website; go check it out.

Comments closed

Text-Based Execution Plans

Published 2016-10-13 by Kevin Feasel

Erik Darling looks at the old SET STATISTICS PROFILE command:

Before you think this is to perf tuning what boxed wine is to pest extermination; it’s not. It’s another tool that has pros and cons. The plan cache is cool too, but cached plans don’t have all the information that actual plans do. You can run Traces or Profiler or Extended Events, but they all sort of have their own caveats, gotchas, and overhead. If you don’t have a monitoring tool, though, what are you left with?

Let’s take a look at what you can do with STATISTICS PROFILE, and then the (rather obvious) limitations. Here’s the setup and a simple query.

I’ll admit that outside of learning what they are, I’ve never used text execution plans.  I’ll read the XML, view the graphical results, pipe them out to SentryOne Plan Explorer (formerly SQL Sentry), etc.  But the text plans never held much allure for me.

Comments closed

Range Lock Deadlocks

Published 2016-10-13 by Kevin Feasel

Dmitri Korotkevitch looks at one scenario in which range locks can cause deadlocking:

The range locks are usually acquired only in SERIALIZABLE isolation level; however, there is another, pretty much undocumented case, when SQL Server can use those locks. It happens even in READ UNCOMMITTED and READ COMMITTED SNAPSHOT modes when you havenonclustered indexes that have IGNORE_DUP_KEY=ON option. In that case rows with the duplicated index keys would not raise an error but rather being ignored. SQL Server would not insert then into the table.

This behavior leads to very hard to explain cases of blocking and even deadlocks in the system. Let’s look at the example and create the table with a few rows as shown below. As you see, nonclustered index on the table has IGNORE_DUP_KEY option enabled.

This is an interesting risk when using IGNORE_DUP_KEY.

Comments closed

Foresight

Published 2016-10-12 by Kevin Feasel

Anders Pedersen shares an easily-avoidable tale of woe:

ETL.  Spec said only Address Line 1 is needed to be loaded, so the developers only bring that line in (plus name, city etc.).  Fast forward 8 years, I get a request on my desk: “Please add Address Line 2 to import, and all tables.  Oh, and we need historical data for previously loaded files.  And for all address types”.

Groan.
No normalization in this database (which is just one of about 40 databases with SIMILAR structure, but not identical).

Read on for the damage done, as well as another example of foresight saving the day.

Comments closed

Review Your Process

Published 2016-10-12 by Kevin Feasel

Chris Sommer wants you to think about why you follow certain processes:

We’re still dealing with the same problems because we’re dealing with the problems in the same way.

I think it can be cultural and can propagate from the senior level DBA’s right on down to the new hires. Sometimes it’s just lack of knowledge or understanding. Sometimes it’s just pure laziness to not want to do a deep dive and find a better solution to a recurring problem.

Here is a pretty extreme example but I think it portrays all of these.

Given some of the things I’ve seen, I’d say his example is not at all extreme.

Comments closed

SSMS Grids

Published 2016-10-12 by Kevin Feasel

Riley Major airs grievances with SQL Server Management Studio’s old-timey grids:

After years of using SQL Server Management Studio (and its predecessor Query Analyzer), I’m struck by how incapable the results grids still are. Unlike Excel, you can’t sort them, you can’t filter them, you can’t search within them, and you can’t easily change their font size. In any commercial software product, grid tools are table stakes. For some reason vendors still like to run through them, but they’re never a differentiator. That’s because you can just buy a grid component and use it in your application. Even the basic grid control which came with .NET 2.0could sort.

Click through to read more, and also check out the Trello board that Riley mentions.

Comments closed

Attaching Databases With CDC Enabled

Published 2016-10-12 by Kevin Feasel

Amit Banerjee notes an issue with detaching a database with Change Data Capture enabled and moving it to a new version of SQL Server:

When you detach a database with Change Data Capture enabled on SQL Server 2014 and below and attach it to a SQL Server 2016 instance, you could run into the error mentioned below while execute Change Data Capture (CDC) related procedures.

Error:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 639 [Batch Start Line 0]

Could not update the metadata that indicates table [<schema name>].[<object name>] is enabled for Change Data Capture. The failure occurred when executing the command ‘insert into [cdc].[captured_columns]’. The error returned was 213: ‘Column name or number of supplied values does not match table definition.’. Use the action and error to determine the cause of the failure and resubmit the request.

Read on to learn more, including how to fix the issue.

Comments closed