Press "Enter" to skip to content

Curated SQL Posts

Describing the First Result Set

Published 2020-02-19 by Kevin Feasel

Phil Factor dives into sys.dm_exec_describe_first_result_set_for_object():

I’ve been working on a project unkindly nicknamed ‘The Gloop’ because the code is a bit amorphous. Basically, it is an approach to documenting SQL Server databases, using the facilities provided such as the metadata views and DMFs. Although it is relatively simple to record the result returned by a table-valued function I’d rather neglected the stored procedures because there was no metadata that could produce the first result set produced by a procedure or trigger.

I’d been silly because there is, of course, an Execution system DMF that does it: sys.dm_exec_describe_first_result_set_for_object(). it takes as its parameter the object_id of a procedure or trigger and describes the first result metadata for the module with that ID. It has the same result set definition as sys.dm_exec_describe_first_result_set.

I’m going to have to try this out, but I have problems with sp_describe_first_result_set and how it blows up if you use a temp table in the stored procedure whose result set you’re grabbing.

Comments closed

Configuring MAXDOP Correctly

Published 2020-02-19 by Kevin Feasel

John Welch has a script to check if MAXDOP is configured correctly:

There’s a lot of information on the internet about how to set MAXDOP correctly. Microsoft even provides a knowledge base article with their recommendations. However, if you look at it, there’s a fair amount of information to digest. I’m lazy forgetful efficient, so I wanted to put this into a script I could easily reuse and not have to remember all the details.

Please note that these are just guidelines, and you should consider carefully whether they fit your workloads and scenarios. As is the case anytime you are evaluating system settings, you should test carefully before and after making changes.

Read on for the explanation as well as a link to the script itself.

Comments closed

String Aggregation with T-SQL

Published 2020-02-19 by Kevin Feasel

Andy Levy talks about one of my favorite functions in SQL Server 2017:


This is another in a group of several posts on modernizing T-SQL code with new features and functionality available in SQL Server.

SQL Server 2016 gave us the STRING_SPLIT() function, but what about the reverse – compiling a set of values into one delimited string? We only had to wait 15 months for the release of SQL Server 2017, and the STRING_AGG() function.

I had the STUFF() / FOR XML PATH trick memorized for quite some time, but that was always a solution which felt like it worked on accident. Even if the new solution weren’t faster than the old, I’d still use it.

Comments closed

Tying a Database User Back to a Login

Published 2020-02-18 by Kevin Feasel

Dave Bland shows how to figure out which database users tie back to which logins:

Over the years I have had to provide information about logins and database users, most of the time per a request of an auditor.  Many times this is very easy to accomplish because the login name matches the name of the database user account. If you look at the “New User” screen you can see that I am able to enter a different User Name.

Because of this, I can have a User Name that doesn’t match the Login Name.  From an audit perspective this can create some confusion.  More importantly, it can make it difficult to provide accurate information to the auditors when asked.

Read on to see how you can tie these together even if the names don’t match.

Comments closed

Fun with asciidocs

Published 2020-02-18 by Kevin Feasel

Sheldon Hull explains the value of asciidocs:

Documentation is such an important part of a developer’s life. I think we often take it for granted, and it’s an afterthought in many projects. However, as I consider my work, I know that I’m not reinventing the wheel very often 😀. Most of what I do is built on the back of others’ work. When I use tooling, I’m reading the documentation and using it as my basis to get work done. When I use my notes and blog posts as a reference, I’m using my informal version of knowledge gathering.

INVEST in documenting your work as you go, for the person behind you. You don’t find time to do it, you make time to do it while you work, as a first class citizen of your work, not an after-thought. Think of all the times you’ve had to dig for answers and save someone else that experience.

Sheldon is not wrong.

Comments closed

Installing .NET Notebooks for Powershell

Published 2020-02-18 by Kevin Feasel

Max Trinidad shows us how to install .NET Interactive on Linux:

In Windows, just takes a few steps to set it up. For Linux, it takes a few extra steps but still is quick enough to get you started.

For Windows, follow the instructions found at the .NET Interactive page in Github.

For Linux, for Ubuntu 18.04, follow the blog post “Ubuntu 18.04 Package Manager – Install .NET Core“.

Basically, in either operating systems, you install:

– Install the .NET Core SDK
– Install the ASP.NET Core runtime
– Install the .NET Core runtime

Click through for the step-by-step instructions. Once you have it done, you get not only Powershell but also F# and C#.

Comments closed

T-SQL Tuesday Life Hacks

Published 2020-02-18 by Kevin Feasel

Jess Pomfret wraps up a T-SQL Tuesday on life hacks:

A lot of people had more than one life hack so I recommend reading all of the posts linked to below. For this summary post I’ve tried to pick one or two hacks from each post and group them into logical buckets.

There are several hacks shared that I plan on integrating into my life, and I hope this post will serve as a good reference for us all going forward.

There were some interesting entries in here.

Comments closed

Reducing Visual Count to Improve Performance

Published 2020-02-18 by Kevin Feasel

Chris Webb explains that you might get better performance in Power BI with fewer visuals:

Before we go any further, I don’t want you to go and change your reports if you’re not going to get any benefit from doing so. Use Performance Analyzer (as shown here) to determine which visuals on your report are the cause of slow performance – there’s no point redesigning visuals that are fast anyway.

As a general rule the more visuals you put on a report page the slower it’s going to get. It’s logical if you think about it: the more visuals there are, the more queries have to be run against your dataset and the more work Power BI has to do to render the report. I know there is a tendency to try to pack as much information onto a page as possible and this often happens when someone else has designed the report you’re trying to build, but you should always try to resist this. Splitting a single large page into multiple smaller pages, using slicers or filters to reduce the amount of data shown at any one time and avoiding gigantic Excel-like tables are a good idea.

It certainly doesn’t mean “get rid of all of your visuals;” after all, speed is only one part of the story. Read the whole thing.

Comments closed