Press "Enter" to skip to content

Author: Kevin Feasel

Resuming Index Operations but Using Different Options

John Morehouse has an interesting use case for resumable indexes:

Documentation on ALTER INDEX provides which options we can set when resuming a rebuild or creation operation:

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait> 
 }

<low_priority_lock_wait>::= 

    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,  
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } ) 

This means that we can change the MAXDOP, MAX_DURATION, and WAIT_AT_LOW_PRIORITY.

I’m going to gather that this was not necessarily the original intent, but it’s pretty nice, as it means that you can resume with fewer cores and lower priority during the day, but more cores and higher priority after hours.

Comments closed

When PolyBase Startup Fails in SQL Server 2019

Niels Berglund hits an annoying error after installing PolyBase on SQL Server 2019:

At MS Ignite in Orlando November 4 – 8, 2019, Microsoft announced the general availability of SQL Server 2019. At the same time, the SQL Server 2019 Developers Edition appeared as an MSDN download, and of course, I downloaded it and installed it on my dev box.

After the installation, I noticed that PolyBase did not start up correctly, and I saw dump files all over the place. After some investigation, I figured out what the issue was, and this blog post describes the fix.

This only affects Developer and Express editions, not Standard or Enterprise.

Comments closed

Memory-Optimized Tables and Error Log Entries

Shaun J. Stuart points out impoliteness on the part of In-Memory OLTP:

It’s nice that they are labeled with [INFO], so you can be fairly sure they aren’t a major issue, but they still annoyingly fill up the log with information that is of no use to anyone outside of Microsoft. It would be nice if you could disable these messages but, to my knowledge, you cannot.

These are the types of error messages which should, by default, not write to the error log. My real bugbear is “Hey, we successfully backed up the transaction log!” You should not need a trace flag to turn that off; you should need one to turn it on for diagnostic purposes.

Comments closed

When mssql-cli Installation Fails on Ubuntu

Kellyn Pot’vin-Gorman shares a slew of reasons why mssql-cli might fail to install:

The easiest scenario for many to deploy SQL Server 2019 on Linux to start working with it, is most likely an Ubuntu distribution, (flavor) of Linux.  With that, you may want to play with the newest tool for command line execution of SQL, which isn’t sqlcmd, but mssql-cli.  It’s got some awesome new features, which I won’t go into here, but focus on installation failures instead, which happens not because the installation is complicated but because of the demands still for Python 2.7 when 3+ versions are required for newer software.

mssql-cli requires Python 3, so I recommend checking the version before running the mssql-cli installation command, as this may save you a lot of work with dependencies.  I’ll still go through the steps to if you want to force it to work with Python 2.7, but seriously, just using the right version of Python will make it so much easier.

Read on for just shy of a dozen different failure modes.

Comments closed

The Joy of Decision Trees

Tom Jordan explains how a simple set of “if” statements forms the basis of some powerful data science algorithms:

While it is true there are techniques in machine learning that required advanced maths knowledge, some of the most widely used approaches make use of knowledge given to every child at secondary school. The line of best fit, drawn by many a student in Year 8 Chemistry, can also be known by its alter-ego, linear regression, and see applications all over machine learning. Neural networks, central to some of the most cutting-edge applications, are formed of simple mathematical models consisting of some addition and multiplication.

A personal favourite technique, and the subject of this blog, is the humble decision tree, taught in schools all over the country. This blog will take a high-level look at the theory around decision trees, an extension using random forests, and the real-world applications of these techniques.

Read on for more.

Comments closed

Fun with Date Math and Performance

Erik Darling has started a new series on how hard it can be to get a date:

I’ll often see people need to “flatten” dates to certain intervals.

By flatten, I mean the start of a day, week, month, or year, and likewise for the end values to all those intervals.

I’ve seen some really bad implementations of this. Most notable was probably a scalar valued function that converted a datetime to a 10 character string to remove the time portion.

In every where clause.

Click through for additional introductory notes and some links to good resources.

Comments closed

Bot Framework 101 Notes

Annie Xu has some notes from an introductory course on the Microsoft Bot framework:

Not long ago, I got a chance to learn a Bot 101 lesson from my teammate Wayne Smith. It was a great class because it helped me who is an new learner to understand a lot of key concepts of Microsoft bot. Because it is in an internal meeting and there is no public video released, I wrote some notes below to share with you.

Click through for Annie’s notes and a bunch of links to additional resources.

Comments closed

Multi-Server Diagnostics with dbatools

Gianluca Sartori shows how we can collect diagnostic information from multiple SQL Server instances by way of dbatools:

What I really love about PowerShell is how simple it is to filter, extend and manipulate tabular data using the pipeline, in a way that resonates a lot with the experience of T-SQL developers.

The main part of the script is the one that invokes all the diagnostic queries included in the list $queries. This is done by invoking the cmdlet Invoke-DbaDiagnosticQuery, that takes care of using a version of the diagnostic query that matches the version of the target server and selecting the data. As usual with dbatools, the -SqlInstance parameter accepts a list of servers, so you can pass in the list of all the SQL Servers in your infrastructure.

Gianluca has a GitHub repo for the script and explains it in more detail as well.

Comments closed