If you haven’t been a DBA that’s OK too. Years ago I came up with my own naming standard for SQL Agent artifacts and I’ve always felt better when the messy room was clean. No Really! That’s exactly what this is like. A messy room where you are pretty sure you put the item you’re looking for in but you just can’t seem to find it until you clean 95% of the mess and then you’re so exhausted that you don’t have time to do what you wanted to in the first place. Ever been there?

If you’re new to Azure Purview or don’t know what it is check out this resource which gives a great overview of this Data Catalog and Governance platform:  https://docs.microsoft.com/en-us/azure/purview/overview.

Scan Naming Best Practice

I’ve blogged previously on the SQL Agent naming standard which I applied to Azure Data Factory (ADF) triggers and you can find that here: https://sqltechblog.com/2020/04/30/azure-data-factory-trigger-tip/

The catch with Azure Purview is you can’t schedule a scan daily. To do this you’d need to setup a scan for each day of the week and that’s really unnecessary. Instead Purview allows you to configure a weekly, monthly, or on-demand (once) scan. Since we’re talking about meta data for assets that shouldn’t change frequently weekly or monthly is just fine; but, this breaks my naming standard for ADF and SQL Agent.

Let’s take a look at the messy room. To find scans that have been run you need to choose insights from the menu on the left and then click “Scans”, finally choose “View more” on the chart. Once clicked you’ll get a grid showing all scans for the last 30 days, which you can filter by name or change the time range. It should look like this but with extra columns for success, failed, canceled counts and more. I took those out because they aren’t needed for this topic. Let’s have a look!

The first thing you may notice is that Last scan run date and Scan name are not in any order. Yes, you can click the header and order the grid results but why?

Next you probably see that all these scans and the detail provided is really not helpful if you want to know what the resource is that was scanned. Which data lake was scanned? Are these all the same one? BUT, WAIT! That last line!! It’s different!!! Now’s the time where I say, I’ll give you $20 if you can’t tell me which server this scanned and when it runs.

Using a proper naming standard helps us keep the room clean. Just from that last line, I know this ran for a SQL Server named MySQLServer001 and it runs every Thursday at 2 PM.

I’m not saying you need to follow my standard or that you need one at all. What I am suggesting is that life will be happier if you identify a naming standard that works for you and adopt it for your organization.

Here’s what I’ve come up with:

Purview scans are Weekly, Monthly, or Once and scan names cannot exceed 63 characters. Knowing this we want to have a name that is easily readable from Purview Studio’s grid. My standard is [DataSource]-[day]-[time] for daily, [DataSource]-[DD]-[time] for monthly, and [DataSource]-[YYMMDDHH] for once.

MySQLServer001-Mon-2300 = Scans MySQLServer001 every Monday at 11 PM.

MySQLServer001-08-0500 = Scans MySQLServer001 on the 8th day of each month at 5 AM.

MySQLServer001-21081214 = Scanned MySQLServer001 once in the 2 PM hour on 8/12/21.

It’s worth noting that I didn’t account for scans that run every other month or every few months. I’d suggest running your scans more frequently than every quarter and with that said I didn’t bother thinking of anything past monthly but you “could” do [datasource]-[nM]-[DD]-[time] if you had a need to scan infrequently. In this case n represents the number of months (up to 5) and M is there for context.

I bring up this relic of a tool because it’s here that I got into the habit of using the “Insert File” shortcut that was on the toolbar. If you’ve worked as a DBA or SQL developer for any reasonable amount of time you can probably relate to having 20+ query windows open. Back then I’d have people come up to my desk and they ask about a perf issue or to kill a SPID. The problem is that I’d open a saved script and then type/edit things in it and leave it open. This was all good until one day the company started restarting machines nightly and the tool would auto save on close. Ouch! Everything was gone or completely broken. Sure you can put your things in Git now-a-days but this is much easier, trust me.

I said “Quick” tip right? Let’s get to it! This is so simple you’re going to laugh and when you start this habit you’ll never go back. Instead of using the “Open File” shortcut to open a script, click New Query and then choose “Edit” & “Insert File As Text…”.  You’ll see this link has been added to my standard toolbar in the screen shots below. More on this in a moment. For now just look at the before and the after.

Open a script with the “Open File” button: Note that the saved file name is listed. Saving anything here overwrites your file. Yikes!

Now Choose “New Query” instead and then “Insert File As Text…” from the Edit menu.

Note that the query script has been “inserted” but the file name says “SQLQuery10.sql”. Yay! No chance to overwrite this one!

If you’d like to add the “Insert File as Text…” button to your toolbar here’s a few screen shots showing how:

Customize the toolbar and click “Add Command”.

Choose “Edit” on Categories and then select “Insert File”.

Move the command to your preferred location with the “Move Up” or “Move Down” buttons.

And now it’s there

So today I’m posting about “the Force” as in Forced Parameterization. If you’ve ever queried using the same query but different parameters, you may have noticed that there are multiple plans in cache by default. As an example if you ran:

SELECT ProductID, Name
FROM Production.Product
WHERE isnull(Weight, 5) between 1 and 20

and then ran:

SELECT ProductID, Name
FROM Production.Product
WHERE isnull(Weight, 5) between 20 and 50

This could store two plans because the isnull function doesn’t allow “Simple parameterization” to occur. With that said, You may think it’d be much, MUCH better to write this as “WHERE Weight between 1 and 20 or Weight is null” but this also negates Simple parameterization.

Let’s take a look at the query plans. You can see the query text for each. Note the parameters.

Because the values for query 1 and query 2 are not parameterized, any time the values change a new plan will be cached which consumes more memory in the instance.

How can we fix this? Alter the database setting “PARAMETERIZATION” to  Forced.

Now that we’ve changed from Simple to Forced we should check the query plan again.

Woohoo! All the values have been parameterized. All good right? Not so fast. Everything and I mean EVERYTHING in the database world is “It depends”.

We’ve condensed the query plans to save memory. All should be good but think about queries that could have greatly different result returned. If query 3 returned 2% of the table for one set of values and 80% of the table for another set of values this could be very bad because you’d essentially have a parameter sniffing problem. Be sure to test before blindly changing any settings and May the Fourth Forced be with you.

#SQLServer #AzureSQLDatabase

This is because the names are not meaningful at all. They are either a GUID thanks to SSRS or something useless like “Schedule 1” or you have 6 different versions of “Every 5 min” when the schedule actually only runs every 15 min on Mondays.

Many years back I came up with a simple naming convention that isn’t perfect but helps me keep track of things at a glance. I use this same convention when naming triggers in ADF (Azure Data Factory).

When you’re working in ADF, you can schedule the pipeline to run on demand, on a schedule, on on an event. If you want the pipeline to run on a schedule you have the option to create a new trigger or select an existing one.

This is the annoying experience I’m referring to. How can you tell which to pick?

Let’s edit a couple of the schedules to make the names more meaningful.

What’s my secret but not perfect sauce? I name my schedules with day and time. It’s that simple.

SMTWTFS_000000 would run at midnight every day.

SMTxTFS_000000 would run at midnight every day except Wednesday.

xMxWxFx_030000 would run at 3 AM on Monday, Wednesday, and Friday.

If the job runs every 5 min then I use “SMTWTFS_Every 5 Min”.

So let’s change the name for two of the 4 and see how this can help. This trigger runs at 9:45 PM UTC every Monday, Wednesday, and Friday. Since my brain doesn’t work in UTC, I chose to name the trigger xMxWxFx_1645 which represents when the trigger runs in my timezone.

Now that we’ve changed this and another, let’s look at the choose trigger dialog.

Can you tell when the triggers run without any extra work? That’s the goal. I hope this post helps make your life easier.

It’s impossible to remember every fact about every DMV and every system table. It’s also great to show documentation from time to time when you demo.

How can you pull up docs easily from SSMS? Easy, there’s a shortcut (SHIFT-F1). Just highlight any item and press SHIFT-F1 and the documentation pulls up in your default browser.

How can you import a bacpac to an Azure Managed Instance? Simply connect to the Managed Instance using the newest build of SSMS (SQL Server Management Studio). Next, right-click Databases and choose Import Data-tier Application..

Next follow the import wizard. Oh dear! Looks like we can’t because the wizard fails to load.

A quick googling on Bing pointed to some forum posts that suggest adding registry keys to fix this issue. Not having time for troubleshooting I decided to go with ‘ole reliable… the command line.

SQLPACKAGE.EXE is a command line utility that can be used to export and import Data-tier Applications. To learn more about SQLPACKAGE check out this reference:

https://docs.microsoft.com/en-us/sql/tools/sqlpackage

I’ve found it quite useful to create a batch file (.bat) when importing many bacpac files because you can run it and then check the results later.

When using SQLPACKAGE to import your command should include /a:import /tcs: /sf. The reference above gives a lot of examples for importing and exporting.

Take special note that when importing to Azure SQL Database you’ll want to validate the database configuration to be sure it’s sized appropriately. For example, using /p:DatabaseMaximumSize=150 will result in a premium SKU with a database of 150 GB. Please don’t forget this because Azure SQL DB can be costly.

It’s worth noting that you can import bacpac files using the Azure portal but only for Azure SQL Database and in my experience the Azure portal import often fails with larger database bacpac files and can take hours before the import starts. With that said I generally use SQLPACKAGE instead.

Right now the extension is using regex, which isn’t the best for parsing SQL but it works for the few test cases I’ve added.

I’m hoping that the community can help evolve the project into something really cool; so, I’m asking for your help in making that happen.

The public project can be found here: https://github.com/DanielJanik/tsqlchecker_adsextension

Here’s a little sample of the extension at work:

Finally, I’d like to thank Drew (https://github.com/dzquared) for creating the step-by-step extension development walk-through that I always wished I could create. Thanks so much Drew for your great work to the community and the guidance you gave me over twitter pms.

I strongly recommend you check out his walk-through here: https://github.com/dzsquared/AzureDataStudio_ExtensionDevelopment/blob/master/0-Prereqs.md

In SQL Server Management Studio (SSMS) you can use SHIFT+ALT+[up/down arrow] to bulk edit lines. Note that this key-combo in Azure Data Studio adds a new line so the functionality isn’t the same.

Note that you can always use CTRL-H to find/replace text; but, it’s nice to have options.

Here’s a look at SHIFT+ALT+UPARROW and SHIFT+ALT+DOWNARROW in action.

I would think that the majority of SQL Server professionals primarily use SSMS (SQL Server Management Studio) to do everything they need in their day-to-day work and I’ll wager that many have never tried using Azure Data Studio; but, they should.

This post isn’t about why you should give Azure Data Studio a try. Instead, I’d like to focus on a new feature called Notebooks. If you’re unfamiliar with the notebook concept don’t worry they are easy to work with and actually quite cool.

A few things to know

1. To start using Notebooks in Azure Data Studio, simply press CTRL-SHIFT-N or choose New Notebook from the File menu.
2. You can add code or text blocks and they can be added before or after existing blocks.
3. Each code block is similar to a stored proc in that declared variables and table variables are only available in the block itself and are not accessible to any other block of code.
4. Temp tables are at the connection level and they can be used at anytime by another code block as long as they have been created before use. Nothing new here.
5. You don’t have to use T-SQL. In fact you can change the kernel to several different types such as python.

As it turns out I was on a six hour flight from NYC to Seattle on an old Alaska plane that didn’t have any screens in the seats. Having nothing better to do I thought I’d gamble with Notebooks or at least see if I could make a gambling game to use for demo purposes.   First thing you’ll need is Azure Data Studio installed. Once that’s done you can check out my Black Jack notebook.

I believe learning should be fun and often times unconventional ways of learning are the best. While the code for the game is not “production ready”, it’s not meant to be either. My hopes are that the game gives you a few scenarios that help you quickly acclimate to this new cool feature. Enjoy!

What is “How’s My Database?” I’d say it’s something that should have been written a long time ago. Query tuning is a difficult task and can take a lot of practice to become good at. My hopes are that this tool will help the community tune their queries easier, raise awareness to certain issues, and train up those that are just starting out.

You can find the tool in the Windows 10 app store. Just search for “How’s my database”.

How’s My Database is a simple Universal Windows App (UWP). Microsoft didn’t include SYSTEM.DATA.SQLCLIENT in UWP until recently with the release of .NET STANDARD 2.0. Since then they’ve added enough features to allow me to create this tool. I’ve also included the Syncfusion treegrid control to aid in displaying a query plan. I’d like to also thank ComponentOne for lending me some tools for this project; however, I didn’t end up using any.

I’d really love if Microsoft could add SYSTEM.DIAGNOSTICS.PERFORMANCECOUNTER to UWP apps so I could truly write the health check or migration app I’ve always wanted to. Sadly I don’t think it’s on any road map.

UWP is limited from accessing the network locally and thank you to Lance McCarthy of Telerik for helping me with a workaround to access local SQL instances from the tool. You simply need to grab the Windows 8 AppContainer Loopback Utility and use it to unblock any UWP app.

Without this tool you won’t be able to connect to a SQL instance on the local machine.

So what can this tool help with? Lot’s of things.

For example, the “Problems with IN” post I wrote sometime back (thanks Joe Sack for teaching me this).

It can tell the difference between a scan forced by a UDF or scalar function as well.

There are actually about 40 things it checks for.

Current limitations are that queries with a cursor or temp table are not analyzed. There’s also a bug where the missing indexes and warnings appear on the wrong node/operator. Since the tool is using estimated plans at the moment, it may not be as accurate.

I’m planning on a few new features in the next month to add feeding the utility a query plan and displaying the original query. I’m also planning on adding history and the ability to execute a query from the tool. Before we get to those we need to fix some known bugs though. I’m hoping that you. Yes! you can help me identify other bugs to make this a great tool for the SQL community.

Please keep in mind that this is a BETA tool and it’s also free. Please use kind words.