Month: November 2022

Query Store Hints in SQL Server 2022

~ Matthew McGiffen ~ 1 Comment

Another neat little feature in SQL Server 2022 is Query Store Hints. This is the ability to apply a query hint through Query Store rather than having to modify existing code or fiddle around with plan guides.


Query hints are a way to influence optimizer behaviour towards generating desired execution plans for a given query. The word “hint” is a bit of a misnomer as usually they mandate what you wish to happen.


I’m not a big fan of leaping straight away to use query hints as they force the behaviour rather than letting the optimizer work out the best plan for your query. This means you may implement hints that you see as desirable for the current issue you are trying to fix, but they create problems for different executions of the query, or if the data profile changes. In general it’s better to see if you can rewrite the SQL code to achieve what you want, but sometimes that’s not possible and a hint ends up being the best way to go – or at least a good short term solution.


The great thing about applying hints through query store is that it can be done without altering application code. That means the DBA can apply a temporary hint while waiting for code to be fixed – or in extremis use them against third party applications where there is not the ability to get code changed.


I first read about them in Bob Ward’s book SQL Server 2022 Revealed. You can also read about them in Grant Fritchey’s excellent (and long!) book SQL Server 2022 Query Performance Tuning.


Applying a hint is simple as long as the query has a plan stored in query store. First you need to obtain the query_id for the query. One method for doing that is to use the following query I got from this stackexchange post:


SELECT
qsq.query_id,
qsq.last_execution_time,
qsqt.query_sql_text
FROM sys.query_store_query qsq
INNER JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
WHERE
qsqt.query_sql_text LIKE '%your query text%';

Armed with the query_id, you can then apply the hint using the sys.sp_query_store_set_hints stored procedure. For example, if my query_id was 100 and I want to apply the RECOMPILE hint then I could do that with this SQL:


EXEC sys.sp_query_store_set_hints 100, N'OPTION(RECOMPILE)';

You can also view which queries have hints applied using the sys.query_store_hints view.


Finally if you want to clear a hint you have previously applied (such as the one above), you can do that as follows:


EXEC sp_query_store_clear_hints @query_id = 100;

Query store does not support all query hints. You see the full list of those supported (and not) at:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-query-store-set-hints-transact-sql?view=sql-server-ver16

And that’s it – it’s that simple. Thanks for reading!

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

Encrypting Your SQL Data Should be Your Default Option

~ Matthew McGiffen ~ 1 Comment

Cyber-attacks and data breaches seem to be perpetually on the rise. Every week we hear news of large companies that have had data stolen with both financial and reputational repercussions. It should be clear that access controls aren’t sufficient to fully protect us from attacks and we need a second line of defence – which is encryption.


Brent Ozar ran a twitter poll a few months ago asking if people are encrypting their databases and backups. We see the results here:

You can see that a small majority of DBAs who follow Brent on Twitter and respond to polls use some form of at-rest encryption. I guess that means that in the larger population the majority aren’t encrypting their data at all.


There are a number of reasons why that might be the case, the only good one is that your database doesn’t hold any personally identifiable information (PII) or sensitive data which will certainly be true in some cases but not in most.


Other reasons are likely to be around effort and expense involved. With the advances Microsoft has made in the encryption feature set over the last several versions of SQL Server I’d argue that those reasons are not as valid as they might have once been.


There are four areas you should be thinking about in terms of properly protecting your data.


Passwords
Passwords are the most sensitive piece of information we hold in our systems. If these are accessed then an attacker can access all a user’s information in our system, and likely in other services they use if they re-use passwords. We shouldn’t even store passwords at all, but should use hashing and salting instead. Most people are probably pretty good about following good practices around passwords, but you’re not then stop reading now and go start having a conversation with your team about how you can address that.


At-rest Encryption
Protecting our data where it is stored on disk is an important part of an overall encryption strategy. Some people will use disk encryption for this but it is better if you can use TDE (Transparent Data Encryption) which will protect you against those who get read rights against the file systems on your servers. I get that TDE can be expensive as it required Enterprise Edition to use it, but that changed with SQL 2019. If you are on an older version then that alone can be a compelling reason to upgrade. Where that is not possible, if you are on SQL 2014 or higher then you can at least use the backup encryption feature to give protection to your backup files.


In-transit Encryption
In my experience most people do use TLS to protect their data is it is transmitted across the network, so that is good news. Again, if you’re not doing this then start making plans to change that. TLS is easy to implement and has little downside in return for the protection it offers.


Column Encryption
At-rest and in-transit encryption are great, but they don’t protect you if someone gains direct read access against your database. That’s where column encryption comes in. Column encryption used to require significant effort to implement, but with the introduction of Always Encrypted in SQL Server 2016 it became a lot easier. In many cases data can be encrypted with no code change required in your applications and giving you the highest level of protection possible. With Always Encrypted, data is protected at-rest, in memory and in-transit. It only ever exists in unencrypted form when it reaches your application.


We should use column encryption against our PII and sensitive data. I know Always Encrypted does give you limitations around how you work with your data, but in most cases you’ll have some data where those limitations are not an issue. I’d recommend you start encrypting those items now, and then over time work out to to encrypt the rest bit by bit.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

My SQL Server Encryption book has just been published!

~ Matthew McGiffen ~ 6 Comments

It’s been a long journey, but I got the email from Apress yesterday to say that my book “Pro Encryption is SQL Server 2022” has now been published.

You can buy a copy at a massively reduced introductory price (nearly 80% off) through the following link:

http://www.dpbolvw.net/click-100730415-12898971?url=https%3A%2F%2Flink.springer.com%2Fbook%2F10.1007%2F978-1-4842-8664-7

If I’ve acheived my aims then this is the most comprehensive resource out there covering the tools available in SQL Server for encrypting your data. I hope to supplement it with a video course covering the same topics sometime in early 2023.

The book covers the following topics:

Part I: “Understanding the Landscape”
Discusses what we are hoping to achieve through encryption and why. We then look in brief at the tools available and how they should fit into your overall strategy.

Part II: At-Rest Encryption
Here we look at the tools you can use to encrypt your data where it is stored on the disk. We take a deep dive into Transparent Data Encryption (TDE) as well as covering the separate Backup Encryption feature.

Part III: Column Encryption Using Always Encrypted
This part is a comprehensive look at the Always Encrypted feature, introduced in SQL Server 2016, that allows you to encrypt your columns of data with the maximum level of security and the minimum amount of application rework.

Part IV: Column Encryption Using Always Encrypted with Enclaves
Having looked at the “basic” version of Always Encrypted, we now take an in-depth look at how the version with enclaves differs and how you work with it.

Part V: Completing the Picture
In addition to the big features like TDE and Always Encrypted, there are other items that should be included in a comprehensive encryption strategy such as TLS and EKM. We cover those in this part as well as looking at other methods that are available for encrypting data.

Appendixes
In the appendixes I look at how encryption differs (or not) if you are running on a cloud platform as well as exploring encryption algorithms in a little more depth.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

Optimized Plan Forcing in SQL Server 2022

~ Matthew McGiffen ~ 2 Comments

I’ve been reading Bob Ward’s book “SQL Server 2022 Revealed” and one of the new features in Intelligent Query Processing (IQP) jumped out at me. Not because it’s going to rock my world but rather it reminded me of an idea I had when I first started playing with Query Store when it came out.

I thought it would be cool if the execution plans cached in Query Store could be used to populate the plan cache after an instance restart, removing the need for all queries to be recompiled. I even raised a suggestion on Microsoft Connect for it to be considered as a new feature.

I won’t claim that anyone at Microsoft saw my post and thought “What a great idea!”, but by coincidence they have implemented in SQL 2022 something similar that works in specific scenarios – Optimized Plan Forcing.

The underlying idea behind it is to store – in query store – something called a compilation script that can be used to shorten significantly the compile time for a given query. Any query that runs when query store is enabled will get a compilation script stored if it has a significant compile time (the actual threshold is not disclosed). We can see can see which queries have compilation scripts by looking at the has_compile_replay_script in the sys.query_store_plan view.

If the query plan is forced in Query Store then the next time it needs to be compiled the optimization script will be used to build the plan instead of going through the usual query optimizer , greatly reducing the compile time.

Of course this only works for forced plans, but that makes sense as for other queries we often want a recompile to come up with a new and hopefully better plan. Still it’s an interesting feature. Bob talks about how it was designed in response to a pattern of performance problems they were calling a compile storm. This would be where the plan cache was emptied and then a lot of queries would have to be compiled concurrently causing CPU spikes. By shortening the compile time for some queries – and of course we DBAs can use Query Store to force as many plans as we like (with caution) it’s hoped to reduce the occurrence of such events.

I’m not going to go into trying to demonstrate this in action, but Bob has a full demo in his book which you can buy here:

The feature is turned on by default for databases on SQL Server 2022 as long as they have query store enabled – even if they’re not on the latest compatibility level. You can however turn it off for a given database using:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;

You can also turn it off at the level of an individual query. Either using the query hint DISABLE_OPTIMIZED_PLAN_FORCING, or if you force the plan using the sp.query_store_force_plan system stored procedure then you can disable it using the @disable_optimized_plan_forcing parameter.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

SQL Server 2022 is Coming

~ Matthew McGiffen ~ Leave a comment

We still don’t have an exact release date for SQL Server 2022 but at this stage we can have strong confidence that it will be with us before the end of the year – rather than slipping and becoming SQL Server 2023.


My first intimation that this was so, was earlier this year. I’ve been writing a book for Apress – “Pro Encryption in SQL Server 2022” that’s due to be released in a couple of weeks. At some point I questioned my editor about whether we should hold off as I didn’t want to be the author of a book referencing a version of a product that never existed. His answer was that Bob Ward says it’s happening, we’re releasing his book too, and it’s also got SQL Server 2022 in the title.


Given that Bob’s a Principal Architect for SQL Server with Microsoft, that gave me the confidence I needed. Bob’s book “SQL Server 2022 Revealed” was released a couple of weeks ago and goes over all the new features and enhancements in depth. I’ve started working my way through it and can highly recommend you get yourselves a copy. You get it currently at nearly a 70% discount through the following link:



As well as being a great technologist, Bob is an excellent and engaging writer. In addition to talking about the what the new features are, he gives insights into the design thinking that went into creating them which makes for interesting reading.
Here I reproduce Bob’s diagram that he calls the “Wheel of Power” showing the major new capabilities of SQL Server 2022:

I plan to blog about some of the new features I find most interesting and exciting over the next couple of weeks. I can’t wait to get dug in with the enhancements to Built-in Query Intelligence and Query Store. There are also enhancements to Always Encrypted which I’ve written about in my own book.


The cloud connected features are also very interesting and create new options for us in term of how we manage High Availability and Disaster Recovery as well as being able to offload parts of our workloads to the cloud.


Stay tuned, or if you can’t wait then grab a copy of Bob’s book and start working through the demos – there’s some great stuff. Like most releases on SQL Server, it’s not a quantum leap in computing, but there’s certainly enough for us DBAs to get our teeth stuck into and start thinking about what it offers us.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.