Press "Enter" to skip to content

Month: February 2017

T-SQL Tuesday 87 Roundup

Published 2017-02-20 by Kevin Feasel

Matt Gordon has the roundup for T-SQL Tuesday 87:

Finally, as a first time host, I was obviously hoping that this topic would garner some responses, but you never know until you hit that post button whether you’ve selected something of interest to the community or not. Thankfully, this month’s topic picked up views from over 20 countries and over 20 blog responses. The list (with a brief post-by-post commentary from me) is below. Happy reading and thanks again for reading/writing/participating!

Read on for the full list of respondents.

Comments closed

Accepting Risk

Published 2017-02-20 by Kevin Feasel

Daniel Hutmacher argues that modern companies have reached an inefficient risk equilibrium:

Which brings us to the matter of getting stuff done. Imagine if everything you do has to be approved by a stakeholder and a manager, every line of code you write is peer-reviewed, then tested in a dev test environment, then in an acceptance test environment (which should both contain reasonably fresh, yet scrambled copies of the production data), then approved for deployment by the stakeholder (who ideally should also take time to verify the results), and finally deployed to production by two other people, under a four-eyes principle where no single person can perform any change in production alone. Sprinkle this with a bunch of project meetings, all while leaving a long and winding trail of tickets and documentation.

This is how most development cycles look. Except, you know, the test environments are rarely fresh, the tests aren’t really that thorough, and the peer-review could probably be called a peer-glance at best.

A lot of this depends upon the industry and the likelihood that an outage will cause direct physical harm to people.  I’m personally ambivalent about where the right risk acceptance point is, but Daniel makes a good argument on the “accept more risk” side.

Comments closed

Time Series Aggregation

Published 2017-02-20 by Kevin Feasel

Steph Locke answers an important question related to time series:

Additive or multiplicative?

It’s important to understand what the difference between a multiplicative time series and an additive one before we go any further.

There are three components to a time series:
– trend how things are overall changing
– seasonality how things change within a given period e.g. a year, month, week, day
– error/residual/irregular activity not explained by the trend or the seasonal value

How these three components interact determines the difference between a multiplicative and an additive time series.

Click through to learn how to spot an additive time series versus a multiplicative.  There is a good bit of very important detail here.

Comments closed

Using Powershell To Add SQL Agent Job Steps

Published 2017-02-20 by Kevin Feasel

Rob Sewell gives a detailed walkthrough of a small Powershell script which adds a job step to every SQL Server Agent job:

This code was run on PowerShell version 5 and will not run on PowerShell version 3 or earlier as it uses the where method
I put all of our jobs that I required on the estate into a variable called $Jobs. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file

Click through for the script and line-by-line explanation.

Comments closed

SQL Server vNext CTP 1.3 Available

Published 2017-02-20 by Kevin Feasel

The SQL Server team has announced a new CTP:

Key CTP 1.3 enhancement: Always On Availability Groups on Linux

In SQL Server v.Next, we continue to add new enhancements for greater availability and higher uptime. A key design principle has been to provide customers with the same HA and DR solutions on all platforms supported by SQL Server. On Windows, Always On depends on Windows Server Failover Clustering (WSFC). On Linux, you can now create Always On Availability Groups, which integrate with Linux-based cluster resource managers to enable automatic monitoring, failure detection and automatic failover during unplanned outages. We started with the popular clustering technology, Pacemaker.

In addition, Availability Groups can now work across Windows and Linux as part of the same Distributed Availability Group. This configuration can accomplish cross-platform migrations without downtime. To learn more, you can read our blog titled “SQL Server on Linux: Mission Critical HADR with Always On Availability Groups”.

That’s a big headline.  In the Other Enhancements section, I like resumable online index rebuilds as well.

Comments closed

MAXDOP-Related Extended Events

Published 2017-02-17 by Kevin Feasel

Paul Randal looks at a couple of Extended Events which help you find CXPACKET waits and degrees of parallelism:

Now I’ll set up a simple Extended Events session to track down the offending code (based on the query from here). It’s very important that you query the sys.dm_xe_map_values DMV to find the correct number to use in the query for the CXPACKET wait, as these numbers often change from release to release, and even in Service Packs. For instance, CXPACKET was 191 in SQL Server 2014 RTM, but is 190 in the 2014 build I’m using.

Be very careful about running this in production, as the event will fire for *every* wait that occurs and so will likely affect your workload throughput, even though it’ll short-circuit if the wait isn’t CXPACKET. I’ll show you a better event to use lower down.

These are good events to know.

Comments closed

Logging Space Used

Published 2017-02-17 by Kevin Feasel

Andrew Peterson has a script to grab space used for each database:

After reviewing the posts, many, perhaps all of the procedures and commands I found provided valuable information. Just not what I wanted. We have:

sp_spaceused, but it returns data in two data sets. Not ideal.  Next we have…

sp_databases, but it just returns a list of databases and the size. Not ideal.  Or use…

DBCC showfilestats,   Ok, but still incomplete. And, since it is not listed on the main DBCC page at MSDN, its most likely deprecated.

And speaking of DBCC, there are other commands to consider. But what I was really hoping to find was a simple way to use the sys.database_files DMV. It is very close, but not quite complete.

Read on for the solution.  One small change I’d prefer is using Aaron Bertrand’s sp_foreachdb for iterating.  Or write a Powershell script if you want to take the looping outside of SQL Server.

Comments closed

R 3.4.0 Performance Improvements

Published 2017-02-17 by Kevin Feasel

David Smith discusses performance improvements upcoming in R 3.4.0:

A “just-in-time” JIT compiler will be included. While the core R packages have been byte-compiled since 2011, and package authors also have the option of btye-compiling the R code they contain, it was tricky for ordinary R users to gain the benefits of byte-compilation for their own code. In 3.4.0, loops in your R scripts and functions you write will be byte-compiled as you use them (“just-in-time”), so you can get improved performance for your R code without taking any additional actions.

Stay tuned for the release.

Comments closed

Understanding sp_who2

Published 2017-02-17 by Kevin Feasel

Kendra Little explains what the sp_who2 procedure does and how to read its results:

Let’s talk about sp_who2

I started out using sp_who2, also! And I was often confused by sp_who2.

sp_who2 is a built-in stored procedure in SQL Server.

  • Shows a lot of sessions, even on an idle instance
  • Doesn’t tell you much about what it’s doing

Here’s what an idle SQL Server looks like in sp_who2

This is my dev SQL Server instance. I have a few sessions open, but the only one which was executing anything was the one you see, where I ran sp_who2. I don’t have any agent jobs scheduled on this thing. It’s just waiting for something to happen.

It’s hard to know what to look at, because we see so much. And only 19 of the 49 sessions is on the screen, too.

Kendra then goes on to explain that there are better ways of getting this information and plugs sp_whoisactive.  I’m 100% in agreement.

Comments closed

Trivial Plans With Columnstore

Published 2017-02-17 by Kevin Feasel

Dmitry Pilugin looks at columnstore query behavior when trivial plans get involved:

Both queries are now fully optimized and that lead to different plans. First of all, both queries run in a Batch Mode, which is much faster than a Row Mode.

In the first query, we see Hash Match Aggregate instead of Stream Aggregate, more to the point you may see that Actual Number of Rows is 0, because all the rows were aggregated locally at the Storage Engine level, you may see property Actual Number of Locally Aggregated Rows = 60855. This is faster than a regular aggregation and is known as Aggregate Pushdown.

In the second query, you may observe a new Window Aggregate operator which is faster than a Window Spool and runs in Batch Mode also.

Read the whole thing.  Dmitry also looks at SQL Server vNext and how it handles the same trivial-plan-generating scenario.

Comments closed