Searching Within Execution Plans

Arthur Daniels shares how you can search within an execution plan using Ctrl+F:

Let’s say we want to find all the operators that used the Posts table. Simply use Contains Posts, and use the arrows to go through the operators. See the video demo below.

This is new to me. I’m surprised I never even accidentally stumbled upon it.

Capturing Queries With XEvent Profiler

Erin Stellato explains how to use the XEvent Profiler in SSMS 17.3 and later:

It’s worth pointing out that neither the Standard or TSQL session writes out to a file. In fact, there’s no target for either event session (if you didn’t know that you can create an event session without a target, now you know). If you want to save this data for further analysis, you need to do one of the following:

1. Stop the data feed and save the output to a file via the Extended Events menu (Export to | XEL File…)
2. Stop the data feed and save the output to a table in a database via the Extended Events menu (Export to | Table…)
3. Alter the event session and add the event_file as a target.

Read the whole thing.

Attempted To Read Or Write Protected Memory

Kenneth Fisher explains a nasty-looking error to us:

So, are you seeing this error?

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

If you read the error it might freak you out a bit. The key words memory and corrupt can be a bit .. concerning. Fortunately in this case they are also rather misleading.

Click through to understand what’s going on and how you can fix the problem if you see this error.

Formatting with RegEx in SQL Server

Shane O’Neill has a problem:

This is a contrived example but I was given a script that got the “Discipline”, “DocumentVersion”, “DocumentNumber”, “SectionNumber”, and “SectionName” out of the above.

And while it works, I hate that formatting. Everything is all squashed and shoved together.

No, thanks. Let’s see if we can make this more presentable.

Shane has a regular expression. Now Shane has two problems.

In all seriousness, regular expressions are extremely powerful in the right scenario. Shane mentions being okay with it not in the database engine and I’m usually alright with that, but there are cases when it’s really helpful like figuring out if a particular input is valid. One example I have on a project is finding legitimate codes (like ISBN) where you can solve the problem easily with a regex but my source data is abysmal. I can use the SQL# regular expression functions to drop into CLR and figure out whether that value is any good, something I would have a lot more trouble with in T-SQL alone.

The Value Of Central Management Server

Jeff Iannucci explains the benefits of Central Management Servers:

If you have more than a handful of instances, you really owe it to yourself to take a few minutes and set up your own CMS. The hard part is getting a list of all the instances, but you need to do that anyways, right?

Once you have that list pick ONE SQL Server instance to create your CMS. You want this to be something with solid up-time that is accessible to other team members, but not an instance that’s already spending CPU cycles on something critical. 

The lack of CMS compatibility is one of my larger pain points with Azure Data Studio and an issue which hopefully gets fixed this year.

Management Studio Query Shortcuts

Michelle Haarhues shows how you can use query shortcuts in SQL Server Management Studio:

Back in the day, with the introduction of programs like Word and Excel, I used keyboard shortcuts to make my job easier.  Then we started using a mouse and reduced the number of keyboard shortcuts I used.  It took me a long time to switch from the keyboard shortcuts to the mouse.  Now I am back to using shortcuts, especially in SQL Server Management Studio (SSMS).  Microsoft allows users to create shortcuts that, if you use them, could make your job easier.  Setting up the shortcuts in SSMS are pretty simple.

My shortcuts are all around running sp_whoisactive: Ctrl+F1 gets results back the way I want, Ctrl+3 shows only my sessions, and Ctrl+4 gives me more details (like execution plans) when I’m willing to wait the extra time to get them.

Navigating Execution Plans In Management Studio

Greg Low shares a few tips on working with graphical execution plans in SQL Server Management Studio:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.
But in one of the least discoverable UI features in SSMS, there is an option to pan around the plan.

Click through for the demos. My favorite way of navigating graphical execution plans in SSMS is to use SentryOne Plan Explorer instead.

Improving The SSMS Scroll Bar

Michelle Haarhues shows how you can enable an enhanced scroll bar in SQL Server Management Studio:

There are so many tools within SQL Server Management Studio (SSMS) that can make your job as a DBA or Developer easier that you may or may not be using.  One of the tools available is the customization of the Scroll Bar.  You can change the display and the behavior of the scroll bars, which can make working with code a lot easier and more efficient, especially when working with long code.  The two options we will discuss are Scroll Bar Display and Behavior.

I didn’t like this a lot at first, but as I used it a few times, it grew on me.

What Happens With Multiple Missing Indexes

Arthur Daniels shows us what happens when there are multiple missing indexes in an execution plan:

This is missing index request #1, and by default, this is the only missing index we’ll see by looking at the graphical execution plan. There’s actually a missing index request #2, which we can find in the XML (I know, it’s a little ugly to read. Bear with me).

I am of two minds on this. It probably should be easier to see multiple index candidates, but there’s already so much risk of people just copy-pastaing missing index recommendations that adding more seems like a bad idea.

Static Data Masking In SSMS 18.0

Monica Rathbun introduces a new feature in SQL Server Management Studio:

Ever need to have a test database on hand that you can allow others to query “real like” data without actually giving them actual production data values? In SQL Server Management Studio (SSMS) 18.0 preview Microsoft introduces us to Static Data Masking
. Static Data Masking is a new feature that allows you to create a cloned copy of your database and replace sensitive data with new data (fake data, referred to as masked). You can use this for things like development of business reports and analytics, trouble shooting, database development and even sharing data with outside teams or third parties. Unlike Dynamic Data Masking
 added in SQL Server 2016, this feature does not hide the data with characters, rather it replaces the entire value.  For example with dynamic data masking the name Peter = Pxxxx, whereas Static Data Masking changes Peter to Paul.  This makes it very easy to use in place of production. Let’s see it in action. If you are not on a newer version on SSMS, don’t worry, you can download it

It looks like there are a few limitations to keep in mind, so click through to read about those.

Categories

March 2019
MTWTFSS
« Feb  
 123
45678910
11121314151617
18192021222324
25262728293031