Press "Enter" to skip to content

Curated SQL Posts

Running The Azure DTU Calculator On An Older Server

Published 2018-04-25 by Kevin Feasel

Jim Donahoe shows us how to get the Azure DTU calculator running on an older server without Powershell:

I recently had to do an analysis of a client’s database workload using the Azure DTU Calculator(DTU Calculator) and thought it might be interesting to share just how I did that.  I have run this tool numerous times on other clients via the PowerShell method and the Command Line method, however this client’s environment was: Windows Server 2008R2, and SQL Server 2008R2 SP3 and had to be done differently.

Now, from the DTU Calculator page itself, it tells you how the process works.  It essentially runs a perfmon trace for an hour with the following counters:

  • Processor – % Processor Time
  • Logical Disk – Disk Reads/sec
  • Logical Disk – Disk Writes/sec
  • Database – Log Bytes Flushed/sec

My client did not have PowerShell accessible for me to use unfortunately.  I normally prefer the PowerShell script, however in this case I had to use the Command Line Interface, they both return the same results.

Click through to see how Jim did it.

Comments closed

Finding Database Files In The Wrong Folder

Published 2018-04-25 by Kevin Feasel

Matthew McGiffen has a script which shows which database log and data files are outside of the default folder for that instance:

One common issue that bugs me is where databases have been moved from one instance to another, usually through backup and restore, and the files haven’t been moved as part of the restore so they get recreated in the data\log folders for the old instance.

This has caused me various problems, e.g. working out which instance is hammering the disk or using up all the space.

Click through for the script.

Comments closed

Availability Group Improvements In SQL Server

Published 2018-04-25 by Kevin Feasel

Allan Hirt points out two improvements to Availability Groups in the latest SQL Server 2016 and 2017 patches:

First and foremost, SQL Server 2016 Service Pack 2 was just released today. There are two major improvements in it for AGs:

1. SQL Server 2016 now has full Microsoft Distributed Transaction Coordinator (DTC) support. SQL Server 2016 had partial support for DTC with one of the two scenarios (cross instance/cross platform), but not intra-instance DBs. SQL Server 2017 had both, and now that was backported so SQL Server 2016 supports all DTC scenarios with AGs. This is great news.

Click through for the other major improvement.  This is in addition to yesterday’s notice regarding the distribution database.

Comments closed

DateTime Arithmetic

Published 2018-04-25 by Kevin Feasel

Claudio Silva points out a difference between the DATETIME and DATETIME2 data types:

I’m currently working on a SQL code migration from Firebird to SQL Server and I hit an error that I haven’t seen for some time.

The error message is the following:

Msg 206, Level 16, State 2, Line 4
Operand type clash: datetime2 is incompatible with int

This ringed the bell right away! Somewhere on the code someone was trying to do an arithmetic calculation without using the proper function.

Read on for the solution.

Comments closed

Viewing Deadlock Graphs With The system_health Session

Published 2018-04-25 by Kevin Feasel

Jes Borland shows how you can avoid using trace flags 1204 and 1222 and view deadlocks from the system_health Extended Event:

This one isn’t bad, but imagine a multi-statement deadlock, or a server with several deadlocks in an hour – how do you easily see if there were other errors on the server at the same time?

With SQL Server 2012+, we have a better tool to see when deadlocks occur – and the deadlock graphs are saved by default, so we don’t have to read the text version to figure it out, or run a separate trace to capture them.

In SSMS, open Object Explorer and navigate to Extended Events > Sessions > system_health > package0.event_file. Double-click to view the data.

Click through for the entire process.

Comments closed

Copying Azure SQL Databases

Published 2018-04-25 by Kevin Feasel

Arun Sirpal noticed a problem when he tried to copy an Azure SQL Database:

Now, I was looking at the following code.

CREATE DATABASE CodeDBP1
  AS COPY OF CodeDB ( SERVICE_OBJECTIVE = 'P1' )  ;

You would think this is okay? I did, especially with the fact that it parsed and was executing. I was thinking a copy of the CodeDB database will be created as a premium P1 database regardless of what the source database service tier was. This  source database is 0.5GB in size under the basic tier and 40 minutes later the copy was still executing. It just didn’t seem right.

Click through for the solution.  If this is going to be normal behavior, I’d really like to see an error message.

Comments closed

R 3.5.0 Released

Published 2018-04-24 by Kevin Feasel

Tal Galili announces that R 3.5.0 is now available:

  • By default the (arbitrary) signs of the loadings from princomp() are chosen so the first element is non-negative.

  • If –default-packages is not used, then Rscript now checks the environment variable R_SCRIPT_DEFAULT_PACKAGES. If this is set, then it takes precedence over R_DEFAULT_PACKAGES. If default packages are not specified on the command line or by one of these environment variables, then Rscript now uses the same default packages as R. For now, the previous behavior of not including methods can be restored by setting the environment variable R_SCRIPT_LEGACY to yes.

  • When a package is found more than once, the warning from find.package(*, verbose=TRUE) lists all library locations.

  • POSIXt objects can now also be rounded or truncated to month or year.

Click through for the long, long list of changes.  H/T R-Bloggers

Comments closed

Natural Language Generation With Markov Chains

Published 2018-04-24 by Kevin Feasel

Abdul Majed Raja shows off Markovify, a Python package which builds sentences using Markov chains:

Markov chains, named after Andrey Markov, are mathematical systems that hop from one “state” (a situation or set of values) to another. For example, if you made a Markov chain model of a baby’s behavior, you might include “playing,” “eating”, “sleeping,” and “crying” as states, which together with other behaviors could form a ‘state space’: a list of all possible states. In addition, on top of the state space, a Markov chain tells you the probability of hopping, or “transitioning,” from one state to any other state — -e.g., the chance that a baby currently playing will fall asleep in the next five minutes without crying first. Read more about how Markov Chain works in this interactive article by Victor Powell.

Click through for a fun example of headline generation.

Comments closed

Putting The Distribution Database In An Availability Group

Published 2018-04-24 by Kevin Feasel

Sourabh Agarwal announces that you will soon be able to put the distribution database into an Availability Group:

Many enterprise customers have asked the capability to combine the usage of SQL Server replication and Always On, such that they can place replication distribution databases within an Always On AG to achieve high availability for their distribution databases, with the expectation that after doing so and when AG failover happens, SQL Server replication will continue functioning seamlessly and correctly. While the Replication publication and subscription databases can be configured to use Availability Groups, this support was lacking for the replication Distribution Databases.

SQL Server engineering team is excited to announce the new enhancement around the configuration of Replication Distribution Database in an Availability Group. This feature enhancement would be available with SQL Server 2017 CU6 and will be ported to SQL Server 2016 in a subsequent CU for SP2.

For those people using merge replication, it won’t be supported in this release.

1 Comment

Using Logical OR Conditions With DAX

Published 2018-04-24 by Kevin Feasel

Marco Russo shows how to create multiple slicers with logical OR (rather than the default AND) conditions in Power BI and Excel:

Usually, the logical AND condition is the expected behavior for scenarios with different slicers. However, there could be cases where there is a need for a logical OR condition between several slicers. In our example, the semantic would be, “show the quantity sold of products that are of brand Contoso or that have been sold to customers who have a Professional occupation”. The measures OR #1, OR #2, and OR #3 show the same result obtained with three different techniques.

The first two techniques are best practices and might provide different performance depending on the data distribution. The third technique is usually a bad idea because it implements a table filter rather than a multi-column filter. It is included for informational purposes, and as an example of what not to do in production code.

It’s not trivial, but it’s possible.

Comments closed