Press "Enter" to skip to content

Month: February 2017

HTDELETE Wait Type

Published 2017-02-14 by Kevin Feasel

Joey D’Antoni troubleshoots a query with excessive HTDELETE waits:

Ultimately I think any thought of the readable secondary having a vastly different plan was a red herrings. Statistics are going to be the same on both instances, and if there were a missing statistic on the secondary, SQL Server would create it in TempDB. Anyway, columnstore indexes don’t use statistics in the traditional sense.

Fortunately I was able to catch a query in the process of waiting on HTDELETE, so I no longer had to look for the needle in the haystack, and I could get to tuning the plans. I was able to grab the SELECT part of the query and generate an estimated plan on both the primary and secondary nodes. The plans were virtually the same on both nodes, with just a minor difference in memory grant between them.

Click through for the solution.

Comments closed

Searching For Powershell Functions In Scripts

Published 2017-02-14 by Kevin Feasel

Stuart Moore has a regex which looks for Powershell cmdlets used in a script:

Having had a quick bingle around for a prewritten regex example I didn’t come up with much that fitted the bill. So in the hope that this will help the next person trying to do this here they are:

Assumptions:

  • A PowerShell function name is of the form Word-Word

  • A PowerShell function definition is of the form “Function Word-Word”

  • A Powershell function call can be preceeded by a ‘|’,'(‘, or ‘ ‘

  • The script is written using a reasonable style, so there is a ‘ ‘ post call

Click through for the script.

Comments closed

Contributing To Open Source

Published 2017-02-14 by Kevin Feasel

Drew Furgiuele explains the process of contributing to an open source project, specifically dbatools:

Step 2: Check out the Github project page what’s in development.

Next, you should visit the project issues page. Here, you’ll find a list of all the features requested, in development and completed on the project. You can also filter the pages to look at current bugs or requested enhancements. Once you see what’s what, if you think of something you want to work on or help with, make a note of it. You should also look at examples of things in development and things that have been completed so you get an idea of the creative and technical process that goes into the project.

Step 3: Speak up!

Head on back to the Slack channel and let everyone know you want to help out. Someone (probably Chrissy) will add your Github account to to the project as a contributor so you can have things assigned to you. Congrats, you’re now on the hook!

I’m happy that the dbatools community has sprung up and hope it’s a gateway to further open source development in the SQL Server community.

Comments closed

MDX Calculation Duration

Published 2017-02-13 by Kevin Feasel

Chris Webb wants to know how long specific MDX calculations take:

In my last two blog posts (see here and here) I showed how to use the Calculation Evaluation and Calculation Evaluation Detailed Information trace events to work out which MDX calculations are evaluated when a query runs in Analysis Services Multidimensional. That’s very useful, but wouldn’t it be great if you could work out how long any single calculation contributes to the overall duration of a query? If you could, it would make performance tuning MDX calculations much easier.

While you can’t get an exact amount of time taken for each calculation, the good news is that it is possible to get a duration rounded to the next second if your calculation is evaluated in bulk mode.

It’s an interesting way of backing into an answer.

Comments closed

Power BI Quick Calc

Published 2017-02-13 by Kevin Feasel

Nicolo Grando talks about a couple of Power BI features, conditional formatting and Quick Calc:

If you select a text column you can:

  • Show only the first attribute
  • show only the last attribute
  • Count the attribute
  • Distinct count the attribute

If you select a numeric column you can:

  • Sum of value

  • find the minimum or maximum value

  • Average the value of column

  • standard deviation of value

  • Count the value

  • Distinct count of value

  • Variance fo value

  • Median of value

The screenshots are in Italian, but it’s pretty easy to get the context behind them.

Comments closed

RStudio Connect

Published 2017-02-13 by Kevin Feasel

Jen Underwood discusses RStudio Connect:

RStudio officially introduced the newest product in RStudio’s product lineup: RStudio Connect. RStudio Connect is a new publishing platform for R that allows analytics users to share Shiny applications, R Markdown reports, dashboards, plots, and more. This release adds an improved user experience for parameterized R Markdown reports, simple button-click publishing from the RStudio IDE, scheduled execution and distribution of reports, and more security policies include hybrid data connections. Essentially RStudio Connect eases enterprise deployment scenarios.

Between what Microsoft is doing with its old Revolution Analytics holdings and what RStudio is doing, this is a great time to be an enterprise R customer.

Comments closed

Temporal Tables

Published 2017-02-13 by Kevin Feasel

Alex Grinberg has a tutorial on temporal tables, including combining temporal tables with In-Memory OLTP:

Although the process of converting an In-Memory Optimized OLTP table to a system-versioned table is similar, there are some differences that we need to cover and demonstrate in this section.

You need to be aware of some specific details when converting the in-memory optimized table to the system-versioned table

Read on for those specifics.

Comments closed

Troubleshooting SSRS 4xx Errors

Published 2017-02-13 by Kevin Feasel

Jeff Pries explains how to troubleshoot various Reporting Services configuration errors:

After installing SQL Server Reporting Services (SSRS), are you receiving an Error 404, Error 400, “Invalid Request” error, or “Bad Connection” error on first visiting the SSRS web portal (the error message seems to vary based on version, browser, and whether accessing via http/https or /reports vs /reportserver) ?

I’ve run into this a few times so I’m listing the steps I’ve used to fix it.  For me, the root cause of this error has been the SSRS Configuration Wizard automatically configuring SSRS to use HTTPS, but assigning an invalid machine SSL Certificate.  The fix is to self-generate a new and valid SSL certificate for the SSRS website to use.

Jeff then provides step-by-step instructions.

Comments closed

Scaling Up R

Published 2017-02-13 by Kevin Feasel

Ginger Grant explains how to use SQL Server R Services to take advantage of server resources instead of running from your local machine:

Microsoft’s R Server contains some specialized functions which are not part of the standard CRAN R installation. One of the ScaleR functions, RxInSqlServer will allow code to be processed on the server from the client. To make this work, you must have R Server and R Client installed. If you are doing a test on a local machine, you will need both R Client and R Server installed on that computer.

Click through for a script which walks you through the process.

Comments closed