Press "Enter" to skip to content

Day: April 21, 2022

Search in KQL

Robert Cain looks at the search operator in KQL:

In this post we will examine the KQL (Kusto Query Language) search operator. Search allows us to look across all columns in one or more tables for a specific text string.

The samples in this post will be run inside the LogAnalytics demo site found at This demo site has been provided by Microsoft and can be used to learn the Kusto Query Language at no cost to you.

Click through to learn more about this very useful operator.

Comments closed

Entity Framework and Include Operations

Josh Darnell has a warning:

I can imagine someone reading that and not seeing the gravity of the situation. “Hey, 500 rows isn’t that many – we have modern hardware!”

I thought it was worth writing about a real world situation where this can get seriously out of hand.

Read on for a scenario in which 64 rows turns into 100,000 rows pretty quickly.

Comments closed

Quotation Marks in Azure SQL DB vs Managed Instance

Michael Bourgon notices a difference between Azure SQL DB and Managed Instance/box product:

I was trying to get some Xquery parsing working, and ran into a weird error:

"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"

Weird.  Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now.  

Read on for the solution.

Comments closed

Using Buffer Pool Extension in SQL Server

Chad Callihan looks at buffer pool extension:

Perhaps you started out with X amount of memory when your SQL server was brought online and over time, with additional load and activity on that SQL server, users are not quite getting the type of performance they’re used to getting. Sure, you can buy more memory. What if that’s not an option?

If you’re running low on memory and need a little boost, enabling buffer pool extension can take advantage of an SSD as an “extension” for the buffer pool.

This is one of those interesting features that probably help a small number of customers but shouldn’t be generally useful. That’s because even with SSD performance improvements, memory is still a couple orders of magnitude faster, so as long as you have the ability to increase RAM, that brings much better performance.

Comments closed

The User Transaction Scope for Temporary Objects

Bob Dorr troubleshoots a performance problem:

When the temporary table is bound to the user transaction it is both created and destroyed as part of the transaction.  The same logic in a procedure attempts to avoid the creation and destruction, for each execution, by using temporary table caching.

From the issue I was debugging, the user transaction scope mattered because creation and destruction of metadata may be an expensive operation.  

This post ties into two separate things: first, how temp objects tie to specific sessions; and second, the cost of creating and destroying temporary objects. For the latter, a couple quick pieces of advice:

  • Reduce the number of temporary objects you create. If you can solve a problem with fewer temp tables or table variables while maintaining acceptable performance, that can help on busy systems.
  • Never explicitly drop temp tables. There’s no benefit to explicitly dropping temp tables, as they’ll go away as soon as the session ends. Also, not dropping temp tables is the first step to:
  • Embrace temp table reuse. There are specific rules around when you can re-use a temp table. Each re-use of a temp table means two fewer metadata operations (one delete and one create).
  • Use memory-optimized table variables instead of temp tables or table variables.
  • Turn on memory-optimized tempdb metadata. The biggest issue here is that you lose cross-database queries into tempdb views. That can end up being painful and is why I can’t recommend it as a general solution.

Comments closed