A Fine Slice Of SQL Server
Itzik Ben-Gan posted an interesting T-SQL challenge on SQL performance dot com. I’m writing up my solution in my own blog post because I have a lot to say and getting code formatting right can be tricky in blog post comments. For reference, my test machine is using SQL Server 2019 CU14 with an i7-9700K CPU @ 3.60 GHz processor. The baseline cursor solution completes in 8465 ms on my machine.
Click through for a bit of formal logic and a lot of tuning.
Comments closedTom Collins has an answer for us:
Do you have a powershell script or t-sql script to find if ssl encryption is enabled on SQL Server? I don’t want to check if connections are SSL enabled but actually check the Force Encryption flag is enabled
Click through for two answers, one in T-SQL and one in Powershell.
Comments closedEitan Blumin explains what hypothetical indexes are and why they might be useful:
Using Hypothetical Indexes, you can generate an estimated execution plan for a given query, that would essentially assume the existence of a “hypothetical” index as if it actually exists as a real index. Compare that estimated execution plan to its counterpart without the hypothetical index, and you’ll be able to determine whether creating this index for real is worth the time and effort.
Hypothetical Indexes are actually nothing new in SQL Server. It existed since SQL Server version 2005. However, its use is still not widespread to this day. Most likely because it’s not very easy to use and the relevant commands are undocumented.
Click through to see how to use them and an important warning if you try it in production.
Comments closedJared Poche looks at a procedure to track query variation over time:
Since Query Store stores statistics long term, we can use it to look for trends or major changes in performance. The Query Variation component of the QDS Toolbox helps us review changes and has a lot of options to allow us to select the kinds of changes we are interested in.
So, let’s review our options and go through some examples.
Click through for some examples of how to use the query variation procedure.
Comments closedErik Darling has a recommendation:
When working with ORMs, care has to be taken to strongly type your parameters to match the data type, length, precision, and scale of the columns those parameters will be compared to. Time and time again, I see the same patterns with string parameters:
– They’re unnecessarily typed as Unicode/nvarchar
– They’re not defined with an appropriate length
– They’re used as catch-all parameters for temporal types (dates, etc.)
Spoiler: these aren’t benefits.
Comments closedTomas Zika takes us through a conundrum:
If it’s the first time you’re seeing a problem like this, it can be confusing. How can
15be less than13?My first train of thought was the same as the original poster’s. Ok, so both values are converted to
varcharaccording to data type precedence – similar to this:
It’s not due to a varchar comparison, but Tomas does have an answer for us.
Comments closedAaron Bertrand preps us for SQL Server 2022:
In an earlier tip, “Find MAX value from multiple columns in a SQL Server table,” Sergey Gigoyan showed us how to simulate GREATEST() and LEAST() functions, which are available in multiple database platforms but were – at least at the time – missing from Transact-SQL. These functions are now available in Azure SQL Database and Azure SQL Managed Instance, and will be coming in SQL Server 2022, so I thought it was a good time to revisit Sergey’s methods and compare.
Read on to see how the workaround compares.
Comments closedI wind down a series on window functions:
SQL Server typically operates in row mode, which means that an operator processes one row at a time. This sounds inefficient, but tends to work out pretty well in practice. However, something which may work out even better is to process more than one row at a time, especially when the number of rows gets to be fairly large. Enter batch mode.
Batch mode was introduced in SQL Server 2012 alongside non-clustered columnstore indexes. It became interesting in SQL Server 2016 and very interesting in SQL Server 2019. That’s because 2016 introduced writable clustered columnstore indexes and 2019 gives us batch mode outside of columnstore indexes.
There are some nice potential performance gains for queries involving window functions.
Comments closedI continue a series on window functions in SQL Server:
If you’ve been around the block with window functions, you’ve probably heard of the POC indexing strategy: Partition by, Order by, Covering. In other words, with a query, focus on the columns in the
PARTITION BYclause (in order!), then theORDER BYclause (again, in order!), and finally other columns in theSELECTclause to make the index covering (not in order! though it doesn’t hurt!).
But do read on to understand why this is not sufficient.
Comments closedI continue a series on window functions:
So far, we’ve seen some really cool things we can do with window functions. That said, there are some limitations with these windows. Some of them are built into how window functions work and others are incomplete implementations of the ANSI SQL standard. Let’s go check it out.
Click through to check it out.
Comments closed