Press "Enter" to skip to content

Author: Kevin Feasel

Finding Failed Agent Jobs

Published 2017-06-16 by Kevin Feasel

Adrian Buckman has a stored procedure which retrieves failed SQL Agent jobs over a given timeframe:

So here is what it does:

  1. Check for failed agent jobs within the dates you specify (provided the agent log has data for this period) @FromDate will default to the past 12 hours if  no value is passed in, @ToDate will default to GetDate() if no value is passed in.

  2. Check that any failed jobs that have occurred within the date range have not subsequently succeeded, if the job has since succeeded since its last failure then this job will not be included in the results.

  3. Check that the job is not currently running, if the job is still running at the time of executing the procedure then this job will be excluded from the results.

  4. If a failed agent job exists that passes through the above checks then the Last Failure message will be obtained for the job and shown in the results along with the Job name, Failed Step and Failed Datetime.

Read on for the script.

Comments closed

Performance Effects Of Constraints

Published 2017-06-16 by Kevin Feasel

Shane O’Neill discusses how constraints can in some cases improve database performance:

David Morrison ( blog | twitter ) followed up with his presentation on “Query Plan Deep Dives” (I had seen this at SQL Bits, but it’s a great session so I had no problems watching it again) and, as an aside, through his presentation he showed us the compound effects that can happen from not specifying a columns NULLABILITY(it’s got letters so it’s word-like…)

Now his slides and scripts are up on his blog and they do a great job of walking you through them so check them out and you’ll see the compound effects they create!

Shane’s overview of two presentations was interesting, but despite all his rage, he is still just a Nicholas Cage.

Comments closed

Facial Recognition With Amazon Rekognition

Published 2017-06-16 by Kevin Feasel

Chris Adzima describes how his law enforcement agency uses Amazon Rekognition for facial recognition:

Setup was fairly straightforward. In the Washington County jail management system (JMS), we have an archive of mugshots going back to 2001. We needed to get the mugshots (all 300,000 of them) into Amazon S3. Then we need to index them all in Amazon Rekognition, which took about 3 days.

Our JMS allows us to tag the shots with the following information: front view or side view, scars, marks, or tattoos. We only wanted the front view, so we used those tags to get a list of just those.

Read on for sample implementation details, including moving images to S3, building the facial recognition “database,” and using it.

Comments closed

Foreign Key Batch Drop And Re-Create

Published 2017-06-16 by Kevin Feasel

Louis Davidson has a utility to batch drop and re-create foreign key constraints:

I was working on a database a few weeks back (maybe longer, I am kind of busy with several projects right now), and I wanted to truncate a set of tables. There was one central table, and 8 tables related by a FOREIGN KEY constraint. The central table had 6 million rows, and a few of the other tables 20+ million. TRUNCATE is amazingly fast,and when I tried to just use DELETE, it took almost as long as it took me to load the tables. (Exaggeration? A little bit, but not as much as you might imagine, since I use SSIS to BULK LOAD these tables, and the source data is very simple.)

I could just get rid of the constraints, but as a relational db lover, I love constraints. When enforced, they protect me. When not enforced (like when SSIS disables them for a BULK LOAD operation), they are still good documentation. So as any good nerd programmer type would, I started coding a tool to deal with the constraints for me. And as a decent blogger, as soon as it started to get interesting, I realized I could blog about it and upload the code to my website. This in the end makes the code better, because I have to test more, and I learn stuff from readers reminding me things (like a FK script needs to honor CASCADE and NOT FOR REPLICATION, oops.)

Read on for a link to the utility, as well as an example.

Comments closed

The Costs Of Statistics Updates With FULLSCAN

Published 2017-06-16 by Kevin Feasel

Kendra Little explains what happens when you update a table’s statistics with FULLSCAN:

On my test instance, the command that uses the default sampling takes 6 seconds to complete.

The command which adds “WITH FULLSCAN” takes just over five minutes to complete.

The reason is that those two little words can add a whole lot of extra IO to the work of updating statistics.

Kendra shows the query plans for each statistics update in some detail.  It’s a very interesting post, well worth taking the time to read.

Comments closed

DevOps T-SQL Tuesday Roundup

Published 2017-06-16 by Kevin Feasel

Grant Fritchey puts a bow on the latest T-SQL Tuesday:

Thank you to everyone who participated in T-SQL Tuesday #091 which was on databases and DevOps. As I anticipated, this brought out quite a bit of variety on the posts. This is because DevOps is still… not quite cooked…(?) in many peoples minds. I think with the range of posts we saw here, it’ll be a lot more clear to those who are just getting an introduction to it. Here are the posts (in no particular order) and a few comments on each:

Click through to see all the entrants.

Comments closed

New Version Of dplyr

Published 2017-06-15 by Kevin Feasel

Hadley Wickham reports that dplyr is now at version 0.7.0:

dplyr 0.7.0 is a major release including over 100 improvements and bug fixes, as described in the release notes. In this blog post, I want to discuss one big change and a handful of smaller updates. This version of dplyr also saw a major revamp of database connections. That’s a big topic, so it’ll get its own blog post next week.

Read on to learn about tidy evaluation and the Star Wars data set.  There’s a lot to wrap your head around in this release.

Comments closed

Power BI Report Server Released

Published 2017-06-15 by Kevin Feasel

Ginger Grant notes that Power BI Report Server is now generally available:

The most glaring change from what was announced earlier, is Power BI Report Server can only connect to analysis services data sources, both tabular and multidimensional.  If you want to connect to SQL Server, Oracle or Excel or all three, use the Power BI Web Service.  Only going to the cloud version will users be able to create a data mashup or connect to anything but SQL Server.

Connecting to one data source is not what was promised when the Power BI Report Server was announced in May.  Various Power BI Product members held a session at the Microsoft Data Summit where attendees were able to ask questions.  I asked “When are we going to be able to use Power BI Report Server with data sources other than analysis services?”  In a room full of people, I was assured that it was a top priority of the team to release the same data connectivity functionality for Power BI Report Server that currently exists for Power BI Services and the current plan was to release this functionality the next release.

This is the most glaring flaw with Power BI Report Server at the moment.  Unfortunately, that probably makes it DOA for my purposes, at least until they introduce SQL Server relational as a valid data source.

Comments closed

Planning A Power BI Enterprise Deployment

Published 2017-06-15 by Kevin Feasel

Melissa Coates announces a new whitepaper co-authored with Chris Webb:

I’m excited to share that a new technical whitepaper I co-authored with Chris Webb is published. It’s called Planning a Power BI Enterprise Deployment. It was really a fun experience to write something a bit more formal than blog posts. My interest in Power BI lies in how to successfully deploy it, manage it, and what the end-to-end story is especially from the perspective of integration with other data assets in an organization. Power BI has grown to be a huge, wide set of features so we got a little verbose at just over 100 pages.

It is a beefy whitepaper.  I haven’t had a chance to read it yet, but it’s now on my list.

Comments closed

Using rxInstallPackages

Published 2017-06-15 by Kevin Feasel

Tomaz Kastrun explains how to use rxInstallPackages to install packages on Microsoft R Server:

In rxInstallPackages function use computeContext parameter to set either to “Local” or to your  “SqlServer” environment, you can also use scope as shared or private (difference is, if you install package as shared it can be used by different users across different databases, respectively for private). You can also specify owner if you are running this command out of db_owner role.

This updated installation method is certainly easier than the prior method, which included incantations and sacrificing a chicken.

Comments closed