Press "Enter" to skip to content

Curated SQL Posts

Latch Promotion

Published 2016-08-15 by Kevin Feasel

Ewald Cress discusses latch promotion threshold calculations:

Now I wish I could use the phrase “cycle-based promotion threshold” in a tone that suggests we were all born knowing the context, but to be honest, I don’t yet have all the pieces. Here is the low-down as it stands in SQL Server 2014:

  • Everything I’m describing applies only to page latches.

  • A cycle-based promotion simply means one that is triggered by the observation that the average acquire time for a given page latch (i.e. the latch for a given page) has exceeded a threshold.

  • Because the times involved are so short, they are measured not in time units but in CPU ticks.

  • There exists a global flag that can enable cycle-based promotions, although I do not know what controls that flag.

  • If cycle-based promotion is disabled, there is another path to promotion; this will be be discussed in Part 4.

I don’t think I’d ever seen the informational message Ewald mentions, so this was a brand new topic to me.

Comments closed

Table-Valued Parameters With Always Encrypted

Published 2016-08-12 by Kevin Feasel

Arvind Shyamsundar wants to use Table-Valued Parameters to load data in batches into an Always Encrypted table:

With this setup on the database side of things, we proceed to develop our client application to work around the TVP limitation. The key to doing this is to use the SqlBulkCopy class in .NET Framework 4.6 or above. This class ‘understands’ Always Encrypted and should need minimal rework on the developer front. The reason for the minimal rework is that this class actually accepts a DataTable as parameter, which is previously what the TVP was passed as. This is an important point, because it will help minimize the changes to the application.

Let’s get this working! The high-level steps are outlined below; there is a full code listing at the end of this blog post as well.

The upshot is that, at least as of today, Table-Valued Parameters are not supported with Always Encrypted.  Arvind does give an alternative, however, so click through for more information.

Comments closed

Enabling Table Changes

Published 2016-08-12 by Kevin Feasel

Sander Stad shows how to enable GUI-based table changes in SQL Server Management Studio:

When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.

Examples of changes that require table re-creation:

  • Change a column to no longer allow NULL values

  • Adding columns in the before another column

  • Moving a column

I agree with Sander:  this is a useful feature, but not something you want to abuse.  If you don’t understand the magnitude of your change, it could cause production problems.  And if you do understand the magnitude of your change, typically you’ll want to script it out for later.

Comments closed

Write-Only Permissions

Published 2016-08-12 by Kevin Feasel

Kenneth Fisher looks at granting write permissions but no read permissions to a user:

Now wait, why are they getting a read error when trying to UPDATE or DELETE? Because of the WHERE clause. The WHERE requires reading the data to see if a row meets the required conditions.

It turns out that write-only permissions don’t really work the way you’d want, as typically you want to read data even if your final goal is to update or delete rows.

Comments closed

Task Manager Graffiti

Published 2016-08-12 by Kevin Feasel

Brent Ozar has time to kill and a 72-core box on which to kill it:

For example, when I run it with 10,000 joins:

  1. Msg 8631, Level 17, State 1, Line 1
  2. Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

When I drop it down to a much more realistic 5,000 joins:

  1. Msg 4074, Level 16, State 1, Line 1
  2. Client drivers do not accept result sets that have more than 65,535 columns.

Ah! Okay, that’s fair. (That’s also two error messages I’ve never seen before. Probably a good thing.) Alright, let’s take out the SELECT * and replace it with SELECT p1.* and see what happens:

This is a fun read.

Comments closed

Technical Debt

Published 2016-08-12 by Kevin Feasel

Daniel Hutmacher takes on the idea of technical debt:

When you think of technical debt, you may think only of classic shortcuts like making assumptions about the data, not using a TRY-CATCH block or perhaps hard-coding a manual correction into a stored procedure or view.

But I would argue that not paying attention to performance is just as much a technical debt. And rather than just crashing with an error message, performance issues are not always easy to just fix in production when your business users are working late to meet their deadlines, or when your web request are timing out. Start thinking of performance as an important part of your development process – half the job is getting the right data in the right place, the other half is making sure that your solution will handle double or triple the workload, preferably under memory pressure conditions with other workloads running at the same time.

Read the whole thing.

Comments closed

Log Chains

Published 2016-08-12 by Kevin Feasel

Dave Mason discusses broken log chains:

There are a handful of activities that have broken my SQL Server backup routines. Sometimes a DIFFERENTIAL (or LOG) backup would fail because there was no FULL backup. Other times, a LOG backup would fail because the log chain was broken. Some of those activities include:

  1. Creating a new database.

  2. Changing the recovery model to SIMPLE.

  3. Restoring/reverting a database from a snapshot.

  4. Any other activity that breaks the transaction log chain.

Dave discusses an easy way of figuring out if you’ve just performed an activity which breaks the log chain.

Comments closed

Learning Performance Tuning

Published 2016-08-12 by Kevin Feasel

Kendra Little gives some tips on how to gather performance tuning skills:

Start writing queries that demonstrate TSQL anti patterns – and make them slow

You know how people say that the best way to learn something is to teach it?

The best way to learn to speed up queries is to write slow ones.

The best way to get a job speeding up queries is to write a blog about the queries you’ve sped up.

This is a long-term learning process, but is absolutely a worthwhile skill for any database professional.

Comments closed

Using Focus() On Correlations

Published 2016-08-11 by Kevin Feasel

Simon Jackson explains how to use the focus() function in R to narrow down a data frame of correlation coefficients based on a subset of variables:

focus() works similarly to select() from the dplyr package (which is loaded along with the corrr package). You add the names of the columns you wish to keep in your correlation data frame. Extending select(), focus()will then remove the remaining column variables from the rows. This is whympg does not appear in the rows above. Here’s another example with two variables:

Click through for the entire article.

Comments closed

Power BI DirectQuery

Published 2016-08-11 by Kevin Feasel

Adam Aspin explains some of the concepts behind DirectQuery:

In this article I will not be looking at Power BI as a data visualization tool. Indeed, I will not be discussing in anything more than a cursory way the data transformation and modelling capabilities of this product. Instead, I would like to focus on using Power BI Desktop to connect directly to certain data sources. So although you need no previous knowledge of Power BI Desktop to understand this article, I will be providing only an extremely succinct glance at the extensive array of the data mashup and modelling techniques that are available. Nor will I be explaining any of the approaches that can be taken to create visuals and dashboards. I will, however, try out a few classic techniques using the Power BI Query Editor and Data Model to see if there are any limits to using DirectQuery when compared to data loaded into the in-memory model. The aim here is to see just how, and in what circumstances, DirectQuery can help you to analyze your data faster and more easily.

This article is lengthy but good.  Check it out.

Comments closed