Press "Enter" to skip to content

Curated SQL Posts

ML Services Can Fill The Plan Cache

Published 2018-01-04 by Kevin Feasel

I have a post talking about a bug in SQL Server:

For now, the workaround I have is to restart the SQL Server service occasionally. You can see that I have done it twice in the above screenshot. Our application is resilient to short database downtimes, so this isn’t a bad workaround for us; it’s just a little bit of an annoyance.

One thing to keep in mind if you are in this scenario is that if you are running ML Services hundreds of thousands of times a day, your ExtensibilityData folders might have a lot of cruft which may prevent the Launchpad service from starting as expected. I’ve had to delete all folders in \MSSQL14.MSSQLSERVER\MSSQL\ExtensibilityData\MSSQLSERVER01 after stopping the SQL Server service and before restarting it. The Launchpad service automatically does this, but if you have a huge number of folders in there, the service can time out trying to delete all of them.  In my experience at least, the other folders didn’t have enough sub-folders inside to make it worth deleting, but that may just be an artifact of how we use ML Services.

It’s very unlikely to affect most shops, as we only notice it after running sp_execute_external_script millions of times, and that’s pretty abnormal behavior.

Comments closed

Getting Dates Between Two Dates In Power BI

Published 2018-01-04 by Kevin Feasel

Imke Feldmann has created an M function to get the set of dates from a start date to an end date at some fixed interval in Power BI:

This function takes 3 parameters:

  1. From- or Start-date
  2. To- or End-date
  3. A selection of ONE of these intervals: Year, Quarter, Month, Week or Day

All dates will be created at the end of the chosen interval: So if you want to analyse events with a duration for example, where you want to transform your data to show one day per (monthly) event, this function generates month-end-dates for every month within the timespan. Please not that if the To-/End-date is within a month, the last element of the list will NOT be that day, but the day of the end of that month.

The default-value for the 3rd parameter is “Day”, so if you omit the specification, the function will return a list of all days in between.

Click through for the script and some explanation of the code.

Comments closed

SSMS Tip Per Day

Published 2018-01-04 by Kevin Feasel

Wayne Sheffield has pledged to do a month worth of SSMS tips and he’s off to a good start.  Here’s day 1, where he talks about creating a solution in SSMS:

Are you the type of person that has all of your custom queries in one folder, and finding the particular one that you are looking for can sometimes be a pain? Well, solutions can certainly help you. A solution consists of one or more projects, which then contain files. In SSMS, the project can contain Connections, Queries and Miscellaneous files. These various projects can be used to group your queries so that they are easily accessible.

Personally, I maintain two different solutions. One is for all the presentations that I do, each in a separate project within that solution. The other is all of my day-to-day scripts.

The first step in using a solution is to open up the Solution Explorer window. This is available from the View Menu | Solution Explorer, or by pressing the keyboard shortcut Ctrl+Alt+L.

Day two shows you how to split the screen in SSMS so you can view two sections of the same script at the same time.

Day three shows you how to create tab groups, so you can see two scripts at the same time.

Comments closed

Generating Passwords In T-SQL And Powershell

Published 2018-01-04 by Kevin Feasel

Dave Mason shares a couple methods for generating good passwords:

There’s really nothing special there. On line 7 I specify how many characters long I want the password to be. I can run the code as many times as needed, or put it in a UDF or a loop if I want to get fancy.

Recently, though, I had the need to generate passwords outside of a T-SQL environment. I immediately went looking in the .NET Framework, and none to my surprise, I found something: the function System.Web.Security.Membership.GeneratePassword(). I did some initial testing in C#, then decided to proceed with a PowerShell scrip

Click through for the scripts.

Comments closed

Restricting Accidental Operations

Published 2018-01-04 by Kevin Feasel

Shane O’Neill shares a few methods for preventing accidental script runs:

Recently I came across a question in Stack Overflow (SO) that said the following:

The other day I was trying to hit another button on the menu but hit Execute – which executed the whole code and ended up deleting some tables. I have always found this scary that hitting one button can execute the whole code.

I want SQL Server to execute code only when something is selected. Is it possible? Or can SQL Server prompt before executing a query?

I thought this is a great question because it can be answered in 4 different ways…

Click through for those four methods.

Comments closed

Project-Oriented R Development

Published 2018-01-03 by Kevin Feasel

Jenny Bryan explains how building projects in R can reduce the likelihood that someone will come in and set your computer on fire:

I suggest organizing each data analysis into a project: a folder on your computer that holds all the files relevant to that particular piece of work. I’m not assuming this is an RStudio Project, though this is a nice implementation discussed below.

Any resident R script is written assuming that it will be run from a fresh R process with working directory set to the project directory. It creates everything it needs, in its own workspace or folder, and it touches nothing it did not create. For example, it does not install additional packages (another pet peeve of mine).

This convention guarantees that the project can be moved around on your computer or onto other computers and will still “just work”. I argue that this is the only practical convention that creates reliable, polite behavior across different computers or users and over time. This convention is neither new, nor unique to R.

I admit that I’m just now getting into using projects regularly for my one-off stuff.  This is very good advice.  H/T David Smith

Comments closed

What Is A Dashboard

Published 2018-01-03 by Kevin Feasel

I’ve started a new series on data visualization:

There are a few things which make dashboards useful:

  • Ideally, the dashboard is a “single pane of glass.”  By that, I mean that all relevant indicators are visible on the screen at the same time.  With my car, it’s close but no cigar:  I can see one of miles traveled, average fuel mileage, or current fuel mileage at a time.  If I want to see a different item, I need to hit a button on the steering wheel to scroll through those options.  By contrast, the TV show dashboard has everything on a single screen with no scrolling or switching required.

  • Key Performance Indicators (KPIs) are readily apparent.  For the TV show dashboard, we have a couple key metrics on display:  episode rating and number of votes as sourced from IMDB at the time I pulled those numbers.

  • Relevant KPIs are bunched together in a logical fashion.  On the top half of the dashboard, we see two visuals relating to average rating by show.  The bottom half show rating & user vote counts for the three highest-rated shows.

  • Layouts are consistent between dashboard elements and between related dashboards.  On the TV show dashboard, bars and columns use a single, consistent color.  Also, shows have thematic colors:  Daredevil in red, Jessica Jones blue, Punisher black, etc.  If I had a second dashboard for season two, I would want to use the same theme.

Read on for more details about what a dashboard is and some of the sundry forms of dashboards.

Comments closed

DBA Morning Health Checks

Published 2018-01-03 by Kevin Feasel

Patrick Keisler has a process which walks through some morning health checks for SQL Server instances:

A few weeks ago, a customer asked me to help develop a way to improve their morning routine of checking the health of each SQL Server. This daily task would typically take about an hour for the DBA to complete. The solution I provided him, reduced that task to under one minute.

The DBA needed me to answer these questions for each SQL Server:

1.       What is the uptime of each SQL Server?

2.       What is the status of each database?

3.       What is the status of each Availability Group?

4.       What is the backup status of each database?

5.       What is the available disk space?

6.       Are there any SQL Agent failed jobs in the last 24 hours?

7.       What errors appeared in the SQL errorlog in the last 24 hours?

In addition, the customer asked to display the results using the typical stop light colors (red, yellow, and green) to represent the condition of each server.

Click through for more details, as well as a link to Patrick’s GitHub repo which hosts the script.

Comments closed

Finding Open Transactions

Published 2018-01-03 by Kevin Feasel

Andy Mallon has a stored procedure to find open transactions:

I’m fussy. Paul’s example script is awesome, but I want to tweak it to meet my needs. I also want to wrap it into a stored procedure so that I can put it in my DBA database, and have it installed on every server I manage. In addition to the columns Paul pulls back, I want to find out:

  • More session details: host name, application name, etc
  • More transaction info: Transaction state, duration, etc
  • Filter out very short transactions: I want to filter out the noise for regular activity

There’s also a transaction state that I exclude. Transaction State 3 means “The transaction has been initialized but has not generated any log records.” A transaction in this state won’t affect log reuse. However, it could affect the size of the version store in TempDb if you’re using Read Committed Snapshot Isolation. I haven’t run into this as a problem, so I filter it out.

I used dbo.Check_Blocking as a base to create another check script to check open transactions

Click through to see what Andy’s procedure offers and also for a link to get the script.

Comments closed