Press "Enter" to skip to content

Month: December 2019

Troubleshooting RESOURCE_SEMAPHORE Waits

Published 2019-12-05 by Kevin Feasel

David Fowler takes a look at the RESOURCE_SEMAPHORE wait type:

These aren’t something that I ever want to see and if I do I am straight away going to go looking for the cause. Basically what’s that’s telling you is that you’ve got processes waiting on a memory allocation. This is going to be because SQL Server hasn’t got enough memory to dish out to that particular process.

What’s going on here is that every time a query runs, it’ll ask for a certain amount of memory. A happy SQL Server will serve up a tasty slice of memory for that query to run in. The problem comes when the query is asking for a bigger slice of the pie than SQL has available. In that case the query will need to wait until SQL has enough free to give the query what it wants, that’s when the RESOURCE_SEMAPHORE wait starts ticking up.

Read on for a second look at this, as well as what you can do to help. Sometimes the answer is “add more memory” but this isn’t necessarily the case.

Comments closed

Automated ML Pipelines with SAS

Published 2019-12-04 by Kevin Feasel

Sophia Rowland shows off SAS’s auto-ML action:

The dsAutoMl action does it all. It will explore your data, generate features, select features, create models, and autotune the hyper-parameters of those models. This action includes the four policies we have seen in my first two blogs: explorationPolicy, screenPolicy, transformationPolicy, and selectionPolicy. Please review my previous blogs if you need a refresher on the data exploration and cleaning process or feature generation and selection process. The dsAutoMl action builds on our prior discussions through model generation and autotuning. A data scientist can choose to build several models such as decision trees, random forests, gradient boosting models, and neural networks. In addition, the data scientist can control which objective function to optimize for and the number of K-folds to use. The output of the dsAutoMl action includes information about the features generated, information on the model pipelines generated, and an analytic store file for generating the features with new data.

This is an area where several companies are investing a lot of money, trying to simplify the process of training models.

Comments closed

Realistic-Looking Islands with R

Published 2019-12-04 by Kevin Feasel

Holger K. von Jouanne-Diedrich uses fractal math to create realistic-looking artificial islands:

Here we will turn this principle on its head and use it to actually create realistic-looking landmasses with R. The inspiration for this came from chapter 4 “Infinite Detail” of the book “Math Bytes” by my colleague Professor T. Chartier from Davidson College in North Carolina.

The idea is to start with some very simple form, like a square, and add more detail step-by-step. Concretely, we go through every midpoint of our ever more complex polygon and shift it by a random amount. Because the polygon will be getting more and more intricate we have to adjust the absolute amount by which we shift the respective midpoints. 

Click through for the code.

Comments closed

Incremental Data Moves to Azure Blob Storage

Published 2019-12-04 by Kevin Feasel

Ginger Daniel continues a series on moving data incrementally from SQL Server to Azure Blob Storage:

In Part 1 of this series, we demonstrated how to copy a full SQL database table from a SQL Server database into an Azure Blob Storage account as a csv file.  My client needed data moved from their on premise SQL Server database to Azure, and then needed the daily incremental data changes uploaded as well.  This article will discuss how to upload the incremental data changes to Azure after the initial data load.

Click through for the process.

Comments closed

Managing SQL Server Documentation with JSON

Published 2019-12-04 by Kevin Feasel

Phil Factor gives us the gloop:

Metadata extract files are handy for documentation, study, cataloguing and change-tracking. This type of file supplements source because it can record configuration, permissions, dependencies and documentation much more clearly. It is a good way of making a start with documenting your database.

Here is a sample of a json metadata file (from AdventureWorks 2016). It was generated using GloopCollectionOfObjects.sql that is here in Github, and is being viewed in JSONBuddy. I use this format of JSON, a collection of documents representing SQL Server base objects (no parent objects) when I need to read the contents into MongoDB. The term ‘Gloop’ refers to a large query that, you’d have thought, would be better off as a procedure. Here is a typical sample of the output.

This is an interesting approach to documentation. I’m not totally buying into it, but that might just be due to my not having tried it.

Comments closed

SQL Undercover Inspector v2

Published 2019-12-04 by Kevin Feasel

Adrian Buckman announces version 2.0 of Undercover Inspector:

There is a new setting in the Settings table called ‘ReportDataDetailedSummary’ this setting is on or off (0 or 1) and will control the level of detail logged in the summary column. When set to a 1 you will get granular detail of Warning/Advisory counts per server per module, setting this setting to 0 will return it back to the original way of logging which was to summarize the entire report into Warning count and advisory count.

There are a lot of changes in here.

Comments closed

Eager Spooling Against Indexes

Published 2019-12-04 by Kevin Feasel

Erik Darling finds an eager spool even when there is a good index to use:

But he did write about Eager Index Spools recently, and the post ended with the following statement:

Eager index spools are often a sign that a useful permanent index is missing from the database schema.

I’d like to show you a case where you may see an Eager Index Spool even when you have the index being spooled.

Click through for Erik’s demonstration.

Comments closed

Testing In-Browser Power BI Report Performance

Published 2019-12-04 by Kevin Feasel

Chris Webb gives us some tips on testing Power BI reports in a web browser:

It turns out that testing performance of a report in the browser is not as straightforward as it seems. In this post I’m going to describe some of the factors you have to take into account when doing this type of testing; in the next post I’ll go into more detail about how you actually measure report rendering times in the browser and how to see what happens when the report is rendered.

Click through for those factors.

Comments closed

The Difficulty of Tracking CPU Usage

Published 2019-12-04 by Kevin Feasel

Grant Fritchey digs into the difficulties of tracking CPU usage on machines:

There are a bunch of ways to look at processor usage. The simplest, and probably most common, is to use the Performance Monitor counters such as ‘% Processor Time’. Query this, you can get an average of the processor usage at a moment in time.

Ta-da! Fixed it. I thought you said this was hard Grant.

Spoilers: that didn’t fix it.

Comments closed