Press "Enter" to skip to content

Curated SQL Posts

Watching and (Not) Messing with Optimization Phases

Published 2022-03-09 by Kevin Feasel

David Alcock giveth:

The full optimisation stage is where the optimiser uses a bag of tricks to optimise our query (surprise, surpise), well technically it has three bags of tricks that are named optimisation phases that each contain a collection of transformation rules (which I cover in this post that you should never do). The optimiser is not limited to using just one of the phases and each has a set criteria which determines if the optimiser can use that particular phase.

In order to see what how the optimiser is using these phases we need to enable Trace Flag 8675 as well as Trace Flag 3604 which will redirect the output to the query messages tab in Management Studio:

And David Alcock taketh away:

Now it has to be said it’s undocumented for a reason, the reason is that it’s really not a good idea to do this. In fact enabling this trace flag is such a bad idea that it will probably cause no end of issues with query performance…so let’s do it, but before we do let me add yet again that please don’t do this! Disabling optimisation features is a really bad idea, just like we did in this post – the purpose for this demo is just to show that we can, and how dangerous it can get.

This is fun to learn and interesting when doing advanced troubleshooting, but maybe not something you want to do very often.

Comments closed

Preventing Triggers from Firing for a Single Process

Published 2022-03-09 by Kevin Feasel

Andy Mallon builds a trigger guard:

I recently saw a question on DBA Stack Exchange (it has since been deleted by the author), who had a “special process” that ran regularly, and as part of that process, they disabled the trigger, did some stuff, and re-enabled it. During that process, the step that disables the trigger would deadlock, and cause problems. So the asker was wondering how to catch & handle the deadlock during the DISABLE TRIGGER step.

Yeah, disabling the trigger, not so great. Read on for one interesting way of doing it, as well as a few other methods in the comments.

Comments closed

Trivial Plans and Stats Updates

Published 2022-03-08 by Kevin Feasel

Lonny Niederstadt lays out the harshness of reality:

OK.  SQL Server trivial plans for rowstore table INSERT. And related optimizer stats interaction.
TL;DR cached trivial plans for INSERT can be surprisingly stubborn. If a query matches to one, it won’t perform or queue a stats update even if the stats are stale.  If the stats have been updated and would otherwise warrant a per-index plan – but there is a matching cached trivial plan for a per-row plan… outta luck. Might hafta DBCC FREEPROCCACHE or add OPTION(RECOMPILE) hint to make sure a cached trivial plan doesn’t prevent a per-index update for an INSERT when you really want one.

Read on for a dive into the topic.

Comments closed

The Power of PIVOT and GROUPING SETS

Published 2022-03-08 by Kevin Feasel

Aaron Bertrand builds a report:

Without comprehensive reporting tools (or Excel), it can be cumbersome and frustrating to produce perfect report output from SQL Server SELECT statement or stored procedures. In modern versions, we have access to T-SQL functionality that far exceeds old-school ROLLUP and CUBE, like PIVOT, UNPIVOT, and GROUPING SETS. Let’s look at how to produce output we can easily plug into a simple front end and produce great-looking reports.

GROUPING SETS is one of my favorite under-utilized operators.

Comments closed

From Access to SQL Server

Published 2022-03-08 by Kevin Feasel

Tom Collins has some tips to make an Access to SQL Server migration more successful:

-Access has a size limit of 2 GB

-Access has a concurrent users limit of 255 users

-Require increased capacity 

The SQL Server Migration Assistant for Access (SSMA) is a very useful tool  offered by Microsoft . 

The main objective of these notes is to supplement the Microsoft documentation and to assist in Access to SQL Server journey.      

Read on for those notes.

Comments closed

Stringing Azure Data Factory between VNets

Published 2022-03-08 by Kevin Feasel

Ahmed Mahmoud performs networking wizardry:

Customer wants to connect Azure Data Factory on one subscription to an Azure SQL Server on Virtual Machine (SQL VM) on another subscription. check out the architecture diagram below for more clarification.

Click through for that diagram as well as the process. And between VNet peering and Private Link, I believe (but could be wrong in saying) the traffic would never leave Azure-hosted machines even when it transits between subscriptions.

Comments closed

Query Plans in Azure Data Studio

Published 2022-03-08 by Kevin Feasel

Grant Fritchey is excited:

I have long been a fan of Azure Data Studio, but one shortcoming has kept me from truly adopting it: Query Plans in Azure Data Studio. Sure, there was a plug-in you could install. Also, you could use a somewhat truncated version of Plan Explorer, but all I wanted was for SQL Server Management Studio plans to be query plans in Azure Data Studio.

Go and get version 1.35 of the tool. Right now.

I think there’s still a fair amount of work to do on those plans but it’s a far cry from where they were prior to this.

Comments closed

Database Offline Works but Online Permissions Failure

Published 2022-03-08 by Kevin Feasel

David Alcock unravels a mystery:

I was browsing the SQL Server subreddit earlier where someone had posted a problem where they’d been able to take a database offline but couldn’t bring the database back online via a script or the UI in SSMS (full thread here).

There’s a bit of a back story; all the DBA’s have left the business (facepalm) so a non-DBA has been left with the admin type tasks. Secondly the reason the database was being taken offline was to take physical backups of the databases mdf and ldf files (double facepalm).

That is its own issue but read on for the problem at hand.

Comments closed

Building posexplode() in the Serverless SQL Pool

Published 2022-03-07 by Kevin Feasel

Jovan Popvic rides to the rescue with JSON:

The array cells are pivoted and returned as simple scalar columns. Now you can simply use WHERE or GROUP BY clauses to filter or summarize information by array element values. Another very useful piece of information might be the index of every element (generated as pos column).

Spark enables you to use the posexplode() function on every array cell. The posexplode() function will transform a single array element into a set of rows where each row represents one value in the array and the index of that array element. As a result, one row with the array containing three elements will be transformed into three rows containing scalar cells. This flattened/normalized representation is much easier for the analysis.

Once the array is flattened and normalized, you can easily analyze the data and find how much people knowing SQL or Java.

Read on to see how you can implement the equivalent of POSEXPLODE() using OPENJSON() in the Azure Synapse Analytics serverless SQL pool.

Comments closed

Writing Extended Events to InfluxDB

Published 2022-03-07 by Kevin Feasel

Gianluca Sartori’s speaking my language:

The TIG software stack (Telegraf, InfluxDB, Grafana) is a very powerful combination of software tools that can help you collect, store and analyze data that has a time attribute. In particular, InfluxDB is a time series database, built with sharding, partitioning and retention policies in mind. It is absolutely fantastic for storing telemetry data, like performance counters from SQL Server or other software products.

In order to store data in InfluxDB, you can use Telegraf, a data collection agent that takes care of extracting telemetry data from the object to observe and upload it to the InfluxDB database. Telegraf is built with the concept of plugins: each object to observe has its own plugin and it’s not surprising at all to find a specialized plugin for SQL Server.

Click through for more details and how to set it up.

Comments closed