Press "Enter" to skip to content

Author: Kevin Feasel

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

More On Columnstore String Handling

Published 2017-02-17 by Kevin Feasel

Niko Neugebauer focuses on two potential columnstore issues, NULL strings and string aggregation:

You can see a filter iterator taking estimated 9% of the resources and filtering over 5,1 million rows that were taken out of the lineitem_cci table (Columnstore Index Scan operation). Before it took place all of the table data was read and the values for the aggregate values were calculated, meaning a significant resource waste (when the data was filtered and while it was occupying memory).

The predicate properties of the Columnstore Index Scan are shown on the left picture and you can see that nothing has been pushed into the Storage Engine, making this query perform quite slow. On my local test VM it takes on average 0.7 seconds to execute while spending over 1.9 seconds of the CPU time. This happens because of the inability of the Columnstore Indexes to push NULL expression predicate into the storage engine.

To solve this problem in this test case I will need to rewrite the query and remove the NULL expression by substituting it by = ‘RAIL’ OR IS NULL logic:

Definitely worth a read.

Comments closed

Avoiding Hard-Coded Paths In Powershell

Published 2017-02-17 by Kevin Feasel

Jana Sattainathan gives a few options for getting around hard-coded paths in a Powershell script:

What if the KillerApp’s home folder suddenly moves?

Now, how do you make your app work with all its scripts without having to change code if you move it to a different folder?

You could now change the initial script that that dot-sources all the functions to alter the path and you are all set. This is still not ideal because you have to make a change when the location changed.

Click through for several options, including PSDrives and even automatically dot-sourcing Powershell scripts in the current and all child directories.

Comments closed