Press "Enter" to skip to content

Month: March 2021

Extended Events without XML

Published 2021-03-30 by Kevin Feasel

Grant Fritchey shows how you can avoid working directly with XML in Extended Events:

One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped.

Look, I get it. I don’t like XML either. It’s a pain to work with. It’s actively difficult to write queries against it. If there weren’t a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let’s see how.

Click through for two such methods. Another thing I might note is that quite often, you only need to mess with the XML once to set up your session and once to set up how you’ll view and handle your results. If you’re constantly writing XPath queries from scratch to work with Extended Events, that’s by choice.

1 Comment

Cost versus Performance Optimization for SQL Server on VMs in Azure

Published 2021-03-30 by Kevin Feasel

Pam Lahoud takes a look at multi-constraint optimization:

So how do you get the best price-performance possible when configuring your SQL Server on Azure VM? In this blog, we’re going to cover three key aspects to right-sizing (and right-configuring) your Azure VM for SQL Server that are based on some common pitfalls customers face when migrating their on-premises workloads to Azure VM:

– Choosing the best VM series and size for your workload
– Configuring storage for maximum throughput and lower cost
– Leveraging unique to Azure features such as host caching to boost performance at no additional cost

One key point of the article is that there are several factors which can make a big difference in price and performance, but which you might not think about on-premises. It’s definitely worth taking the time to research this. It’s also a great example of how administrators are still important in a cloud-based world—having an admin who understands these settings and can get the most out of a given server can save a lot of money very quickly.

Comments closed

Finding Securables for a Database Role

Published 2021-03-30 by Kevin Feasel

Jack Vamvas answers a question:

Question: I  need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions  including the Permission,Grantor,Grant,With Grant and Deny.  And also the Securables – Schema,Name,Type

How can I get this information via t-sql?

The only downside in Jack’s query is that it enumerates the securables for the principal. But if the principal is part of an Active Directory group (or multiple groups), this becomes more difficult.

Comments closed

No Respect for NOCOUNT

Published 2021-03-30 by Kevin Feasel

Thomas LaRock notes an oddity in SQL Server Management Studio and Azure Data Studio:

Anyway, I spend time trying to debug what is happening. I am able to manually set NOCOUNT on and off inside of T-SQL and see a count of rows affected returned (or not). I check and recheck everything I can think of and feel as if I have lost my mind. I’m starting to question how I ever became certified in SQL Server.

I mean, it’s a simple configuration change. This isn’t rocket surgery.

So I do what anyone else in this situation would do.

I turn off my laptop and forget about everything for a few days.

I’d never used this particular style of setting NOCOUNT on for a user (I would always enable it by session using SET NOCOUNT ON), so I’m not sure when certain tools started ignoring the user-level setting, but read the whole thing for maximum intrigue.

Comments closed

Temp Tables and Nested Stored Procedures

Published 2021-03-29 by Kevin Feasel

John Morehouse takes us through a catch in creating temp tables inside nested stored procedures:

SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables.  One of the capabilities is to allow for stored procedures to call other stored procedures.  This is called “nesting” one stored procedure within the context of the calling stored procedure.  In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.

But wait! I’m not done yet!

You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure.  But there is a catch!

I’ve done the former (and more frequently, had to support when somebody else did the former), but I don’t think I’ve done the latter. Mostly because it seems like it’s an invitation for problems.

Comments closed

Deploying a Storage Solution to a Kubernetes Cluster

Published 2021-03-29 by Kevin Feasel

Chris Adkin continues a series:

Before we dive into deploying a storage solution to our Kubernetes cluster, we need to understand the basics of storage in the world of Kubernetes, which can appear to be both exotic and mysterious to the uninitiated. To dispel some confusion around Kubernetes and storage, the storage IO path is exactly the same as that with common garden vanilla variety Unix or Linux. The Kubernetes storage ecosystem introduces two extra things we need to concern ourselves with above and beyond conventional Unix/Linux storage, firstly there are some extra layers of abstraction between the physical storage and filesystems that pods use, what I like to refer to as . . .

Read the whole thing. And that was a particularly mean cut-off point on my part, if I do say so.

Comments closed

Error Messages on SSDT Database Project Deployments

Published 2021-03-29 by Kevin Feasel

Chris Johnson has some advice if you’re hitting an error when deploying a SQL Server Data Tools database project:

Today I’d like to talk about three error messages you might see when deploying an SSDT database project, either through Visual Studio or via a dacpac and script. I’m going to focus here on what you see from inside of Visual Studio, but you will see similar errors returned when you deploy using a script and the reasons behind them will be the same.

Read on for Chris’s findings. These errors definitely aren’t a complete survey of possible messages, but they do hit some of the less obvious cases.

Comments closed

Finding Query Performance Issues with Query Store

Published 2021-03-29 by Kevin Feasel

Andrea Allred has a primer on Query Store:

So if I can’t modify or add indexes and I can’t change code, how do I get my query times to drop? Query Store to the rescue.

I love to tune queries. I feel so satisfied to see the times dropping on my server as I tune things. Recently, I have been tracking my Batch Requests per Second and my Instance Waits to see if I am making improvements when I tune. It has been awesome!

What I am going to show you today is how I dig into my query store to find those misbehaving queries and make their performance better.

Click through for some high-level tips on how to use Query Store.

Comments closed

Limitations with Control Flows in Azure Data Factory

Published 2021-03-29 by Kevin Feasel

Meagan Longoria has a list:

If you’ve been using Azure Data Factory for a while, you might have hit some limitations that don’t exist in tools like SSIS or Databricks. Knowing these limitations up front can help you design better pipelines, so I’m listing a few here of which you’ll want to be aware.

1. You cannot nest For Each activities.
Within a pipeline, you cannot place a For Each activity inside of another For Each activity. If you need to iterate through two datasets you have two main options. You can combine the two datasets before you iterate over them. Or you can use a parent/child pipeline design where you move the inner For Each activity into the child pipeline. Fun fact: currently the Data Factory UI won’t stop you from nesting For Each activities. You won’t find out until you try to execute the pipeline.

Click through for several other limitations and workarounds.

Comments closed