Press "Enter" to skip to content

Author: Kevin Feasel

When to Index Temp Tables

Published 2020-03-06 by Kevin Feasel

Erik Darling thinks about what phase in a procedure one should index a temp table:

You already know that your temp table needs an index. Let’s say there’s some query plan ouchie from not adding one. You’ve already realized that you should probably use a clustered index rather than a nonclustered index. Adding a nonclustered index leaves you with a heap and an index, and there are a lot of times when nonclustered indexes won’t be used because they don’t cover the query columns enough.

Good. We’ve fixed you.

But, like, when should you create the index?

I try to do as many inline operations as I can with temp tables because doing so means you might be able to take advantage of temp table reuse, and on a frequently-running procedure, that can make a difference.

Comments closed

Troubleshooting Azure SQL DB Elastic Jobs

Published 2020-03-06 by Kevin Feasel

Kate Smith wraps up a series on elastic jobs in Azure SQL Database:

This error means that the Elastic Job Agent cannot connect to the target server(s) because the target has some firewall rules blocking the connection requests.  Indeed – it is required that every target in the target group allows connections from Azure Services in order for Elastic Jobs to work.  To fix this, I go to the target server in the Azure Portal and click on the “Firewalls and virtual networks” item under “Security”.  Next, I toggle the “Allow Azure services” from OFF to ON, and save my changes.  

This has been an interesting series to read through, even though I don’t do much at all with Azure SQL Database.

Comments closed

Reducing Trigger Executions

Published 2020-03-06 by Kevin Feasel

Jared Poche writes up a fun scenario he discovered:

I’ve never been a fan of triggers. I don’t like the idea of them adding an additional tax on every operation. It’s easy to forget they are even there, consuming your cycles. I’ve even seen a few nasty death-by-a-thousand-cuts scenarios with them. But I found something out this week that makes me like them even less.

I was tuning a procedure that runs 284 million times a day.

Over a number of servers and databases, but yes, that number is correct. It takes 2.5ms to run on average, with 1.0ms of CPU time. I’ll spare you the math, but that means over 3 cores of SQL Server are doing nothing but running this procedure 24/7/365. Anything we can do to improve this will be significant, even if we just shave off half a millisecond.

The best stories start with “I was tuning a procedure [which] runs 284 million times a day.”

Comments closed

Backup Up Cosmos DB

Published 2020-03-06 by Kevin Feasel

Josh Smith shows us how to back up a Cosmos DB collection:

Now that ADF is a trusted service I wanted to document the state of my current solution since I’ve been able to dump the hack-y PowerShell script I put together. I haven’t been able to get the level of abstraction I’d really like to see but overall I think I’m pretty happy with the solution (and I still get to include a hack-y PowerShell script). My solution consists of

– a control pipeline,
– a notification pipeline and
– 1 pipeline for every Cosmos DB service I want to to work with. (This is because I wasn’t able to figure out a way to abstract the data source connection for the Copy Data task.)

Read on for the solution.

Comments closed

Plan Forcing and Stored Procedure Alteration

Published 2020-03-06 by Kevin Feasel

Grant Fritchey takes us through a scenario where plan forcing with Query Store has some unexpected results:

I absolutely love Query Store and the ability it provides to force a plan is amazing. However, there are a lot of little gotchas in this functionality. I just recently found one that has quite a far reaching effect. Let’s talk about what happens when you DROP and then CREATE a stored procedure.

It’s one of those things where you read Grant’s blog post and say, “Yeah, of course that makes sense; how could it be otherwise?” And yet, without having read the blog post, the thought might never have occurred.

Comments closed

Creating Triggers with Cosmos DB

Published 2020-03-06 by Kevin Feasel

Hasan Savran shows how you can create a trigger in Cosmos DB:

You have options if you need to use any type of triggers in Cosmos DB. There are two types of triggers in Cosmos DB. First one which I will cover here is the regular triggers which can be executed before (Pre-Triggers) or after (Post-Triggers) an operation. This type of triggers is written in JavaScript and you need to register them to a collection just like stored procedures. Second type of triggers can be created by Azure Functions and you can find more information about them in my older posts.

     Pre-Triggers and Post-Triggers do not take any input parameters. Since Cosmos DB needs to work more work to execute triggers, you will end up with higher Request Units for your queries. They might name triggers, but both do not get executed automatically with every operation. You need to call them programmatically if you want to run them.  If trigger throws any error for any reason, transaction will roll back and data will not be saved to the database.

Naturally, triggers are going to have a performance impact on your system regardless of the choice of data platform.

Comments closed

Confluent Developer

Published 2020-03-05 by Kevin Feasel

Tim Berglund announces Confluent Developer:

Today, I am pleased to announce the launch of Confluent Developer, the one and only portal for everything you need to get started with Apache Kafka®, Confluent Platform, and Confluent Cloud! Everything on Confluent Developer is completely free and ungated. It’s a single online source of everything you’ll need to learn Kafka: links to documentation, collections of video tutorials, links to sample code, the entire collection of guided Kafka Tutorials, an index of podcast episodes, and a link to our global network of meetups.

The site is laid out really well.

Comments closed

The Hype Cycle for Artificial Intelligence

Published 2020-03-05 by Kevin Feasel

William Vorhies takes a look at Gartner’s hype cycle for AI (among other things):

Supposing you’re a business leader and supposing you’re trying to make an intelligent decision about prioritizing your AI adoption plans.  It’s likely that like many of us the first thing you’d reach for would be one of Gartner’s many hype cycle or magic quadrant analyses.

What you might not know is that you now need an expert just to guide you through the expert literature.  There has been such a proliferation of hype cycles and magic quadrants that you could easily be looking in the wrong place.

The hype cycle is definitely opinion-based, but I think it’s a useful look at the relative maturity of different segments of an industry or technology cluster. Do read the whole thing, though, as these things aren’t perfect.

Comments closed

Deprecated and Discontinued Features in SQL Server

Published 2020-03-05 by Kevin Feasel

Randolph West takes a look at my favorite activity: deleting code:

The following statements are true:

– SQL Server 2019 does not have any deprecated features, but does have discontinued features
– SQL Server 2017 does not have any discontinued features, but does have deprecated features
– SQL Server 2016 has both deprecated and discontinued features

Let’s discuss the difference between deprecated and discontinued features, and explain how this affects database administrators looking to move to SQL Server 2016 or newer. My current advice is to target SQL Server 2019 with at least Cumulative Update 2 (CU2).

Read on for more details.

2 Comments

Running and Scheduling Azure SQL DB Elastic Jobs

Published 2020-03-05 by Kevin Feasel

Kate Smith continues a series on Azure SQL Database Elastic Jobs:

In previous posts, I have demonstrated how to create an Elastic Jobs Agent, setup credentials for Elastic Jobs, create a target group of servers/databases for the agent, and how to create and define an elastic job using both PowerShell and T-SQL.

In this post, I drill down into how to run an Elastic Job both in an ad-hoc fashion and how to schedule a job to run regularly. I do this both for PowerShell and for T-SQL.

The Powershell version is a one-liner and the T-SQL version looks a good bit like it does with SQL Agent jobs.

Comments closed