SQL, Code, Coffee, Etc.

09/19/2022
by Marlon Ribunal
1 Comment

Book Review: SQL Server Query Tuning & Optimization

SQL Server 2022 Release To Manufacturing is just around the corner. The current release as of this writing is SQL Server 2022 Release Candidate 0. This SQL Server edition is the most “Azure-enabled release yet.” What that means, according to Microsoft, is that it “provides true resilience by connecting SQL server to Azure through seamless disaster recovery to Azure SQL Managed Instance.” There are tons of resources that you can find online to learn more about the features of the latest iteration of SQL Server.

SQL Server Query Tuning and Optimization Book — SQL Server Query Tuning and Optimization

If you are like me and still prefer the physical book as your primary reading material, Benjamin Nevarez’s book from Packt Publishing is a great book to add to your bookshelf (it’s available in digital format too).

I have reviewed the last two editions of Benjamin’s other book on “High-Performance SQL Server” (here & here).

And just like how I recommended those to friends, I highly recommend SQL Server Query Tuning and Optimization: Optimize SQL Server 2022 Queries and Applications to anyone interested in making their SQL Server performant; more so if you are a SQL Database Administrator or SQL Developer.

TL;DR – I highly recommend this book.
Marlon Ribunal

I like how the table of contents is arranged in a two-column format which allows easier scanning when you are looking for a specific feature or section in the book. I know this is a minor feature but it’s kinda a big deal to me 🙂

SQL Server Query Tuning and Optimization Table of Content Example — SQL Server Query Tuning and Optimization Table of Content

As the book title indicates, you don’t expect to see all the new features introduced in SQL Server 2022 here (e.g, Ledger, Azure Synapse Link for SQL, etc). If you are looking for a great resource for SQL Server query tuning and optimization, this is your book. And if you are not planning to implement SQL Server 2022 in the near future, need not worry because it also covers older versions.

It is just proper that the book starts with Introduction to Query Tuning and Optimization in Chapter 1, which covers how the query optimizer works and how it produces efficient execution plans. The first chapter takes you from the fundamental of query tuning to the basics of an execution plan.

I like the cadence of this book. It’s kinda fast-paced when it comes to the progression of discussion. By Chapter 2, you are lunging at Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), SQL Trace, Extended Events, and Data Collector. This chapter covers the foundation of query and performance tuning with the use of DMVs and DMFs. In this early part of the book, you’re already shown how to find expensive queries, how to investigate bottlenecks such as blocking and waits, and how to work with SQL trace and extended events to investigate further.

Chapters 3 to 5 cover the Query Optimizer, Execution Engine, and Working with Indexes. I feel like Chapter 3 – Query Optimizer – should have been the second chapter but that’s no big deal. Chapter 4 covers the Execution Engine, which can help you find what is happening under the hood while queries are being executed. Although this isn’t the first time I heard about Halloween Protection, I like the fact that Benjamin dedicated a whole section about this Update operation behavior. Some of the books out there don’t even mention Halloween Protection. Chapter 5 covers Indexes. Working with Indexes is a big deal and this book covers the topic quite well considering that extensive discussion about Indexes merits its own book.

Chapter 6 covers Statistics which includes a section about the “new” Cardinality Estimator introduced in SQL Server 2014 and a little background on estimation models. Cardinality Estimation is a big aspect of an execution plan and I like that this chapter recommends ways of detecting and fixing cardinality estimation errors. This is the chapter that I might often refer to in my day-to-day job as a Database Administrator.

Chapter 7 covers In-Memory OLTP. I don’t work with this feature and didn’t delve into it, to be honest. It’s in this book if you need it.

Now onto the biggest features and enhancements of existing features in SQL Server 2022, Chapters 8 to 10 cover Understanding Plan Caching, Query Store, and Intelligent Query Processing. The discussion of the execution plan in the previous chapters focuses on how the query optimization process produces them. Chapter 8 – Understanding Plan Caching – focuses on what happens to those plans. This is basically an extension of Chapter 1. If you are planning to read this in phases, you could probably sew them together as a contiguous chapter. Chapter 8 is capped with a discussion of a SQL Server 2022 feature, the Parameter-sensitive plan. The Query Store has become a de facto method of gathering performance-related information. In fact, Quey Store is on by default in SQL Server 2022. Chapter 9 covers the enhancements to Query Store. Chapter 10 covers an overview of Intelligent Query Processing (set of features) which was intended to improve the workload performance without the need for application changes. SQL Server 2022 further enhanced some of the features within the Intelligent Query Processing family.

Chapter 11 covers an Introduction to Datawarehouse and the common performance problems and how to solve them. Again, this is a big topic and probably requires another book for its extensive discussion. This chapter is here if and when you need it.

I understand why the author decided to place the chapter on Query Hints at the end of the book, Chapter 12. Do you need Hints? Probably not. Do you always “not need” Hints? Probably not, either. There are instances where Hints are “necessary” depending on your needs. This chapter covers some of those scenarios.

06/08/2022
by Marlon Ribunal
2 Comments

How To Create A SQL Server 2022 Learning Sandbox In Few Easy Steps

Microsoft has recently released the public preview of SQL Server 2022. You can find info here.

Microsoft peddles SQL Server 2022 as “the most cloud enabled version Microsoft has ever released.” So, whether you’re hosting your SQL Server On-Premises or On Cloud (IaaS and/or PaaS), SQL Server 2022 might just be the perfect solution.

Below are some of the SQL Server 2022 features that I am most excited about:

New permissions & roles (Info here)
Query Store hints (Info here)
Ledger (Info here)
Parameter sensitive plan optimization (Info here)
Degree of parallelism (DOP) feedback (Info here)

You can find the whole list of features here. The latest version is still on Public Preview as of this writing, expect for many changes as Microsoft nears the RTM release.

Maybe you want to get your hands dirty with the bells and whistles of the latest iteration of SQL Server, but you don’t have an extra bare metal or Azure or GCP based VM. Well, you’re in luck because Microsoft just released container images for SQL Server 2022 (check the container announcement here).

Here are the few steps to get started with the SQL Server 2022 version using a Docker container image for SQL Server 2022 on Linux:

Download and Install Docker Desktop in your laptop. Get started with Docker Desktop here.
Once you’re set up with Docker, the next step is the installation of container. For this example, I’m using the Linux version. Open Power Shell and enter the following docker command:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=MySuperSecureSAPassword" -p 1433:1433 -v 'c:\docker\sql2022:/var/opt/mssql/data' -d mcr.microsoft.com/mssql/server:2022-latest

That’s pretty much intuitive so I am not going to explain the parameters. The -p parameter (1433:1433) is there so I can access the SQL Server instance in the container via the SSMS installed in my laptop.

At this point, you now have a container running a SQL Server 2022 instance (Click on the Play button if the container is not running).

Note: Docker logs says, “This is an evaluation version. There are [163] days left in the evaluation period.”

Docker creates their containers with funny names (I got “fervent_almeida” in this case), to change that you can simply rename it by:

docker rename <Container> <New Name>
docker rename fervent_almeida SQL2022
Start the container. Now, you can connect to the SQL Server 2022 instance in the Docker Container like this:

And, we are connected…well, I was able to connect after 3 cycles of starting the container. For some reason, the container kept stopping. Then after the 4th cycle, the container status stabilized.
The SQL Server 2022 instance is unusable without a full-fledged database of course. Download a copy of WideWorldImporters-Standard.bak here. I downloaded the backup file to C:\temp.

Note: I couldn’t make the WideWorldImporters-FULL.bak work. The RESTORE was barfing on the filestream file, and I just gave up and went with the standard backup which worked. Another thing is that you can only raise the compatibility level of the database from the standard backup file up to 2019.

Create a backup directory in the SQL Server Container.

docker exec -it SQL2022 mkdir /var/opt/mssql/backups

Now copy the backup file from the local C:\temp to the backup folder in the container:

docker cp C:\temp\WideWorldImporters-Standard.bak SQL2022:/var/opt/mssql/backups

List the logical name of the data file, just so you know how to call them:

docker exec -it SQL2022 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "MySuperSecurePassword" -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/WideWorldImporters-Standard.bak'"
Restore the WideWorldsImporters-Standard.bak. You may notice that I have added the REPLACE command. This was because the RESTORE was barfing about the system not finding the file specified.

NOTE: As a workaround, after the first failed RESTORE, “touch” the files so that they have a modification time. You can then replace them later in the RESTORE command. (And I don’t know why this works, so don’t ask me why)

Ref: Stackoverflow

Touch the files like this:

docker exec SQL2022 touch /var/opt/mssql/data/WideWorldImporters.mdf docker exec SQL2022 touch /var/opt/mssql/data/WideWorldImporters_userdata.ndf docker exec SQL2022 touch /var/opt/mssql/data/WideWorldImporters.ldf

You can then RESTORE WITH REPLACE like this:

docker exec -it SQL2022 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "MySuperSecurePassword" -Q "RESTORE DATABASE WideWorldImporters FROM DISK = '/var/opt/mssql/backups/WideWorldImporters-Standard.bak' WITH MOVE 'WWI_Primary' TO '/var/opt/mssql/data/WideWorldImporters.mdf', MOVE 'WWI_UserData' TO '/var/opt/mssql/data/WideWorldImporters_userdata.ndf', MOVE 'WWI_Log' TO '/var/opt/mssql/data/WideWorldImporters.ldf', REPLACE"

And, there you go…

Disclaimer: This test is performed in my own personal laptop. No company laptop is involved in any way. This blog is provided “as is” without warranty of any kind.

04/14/2021
by Marlon Ribunal
Comments Off on Book Review: High Performance SQL Server (Second Edition)

Book Review: High Performance SQL Server (Second Edition)

One of the technical books that sit on my work desk (or dining table, whichever is the case because I work from home full time now) at arm’s length as a resource book is Benjamin Navarez’s ( t | b ) book on SQL Server performance, High Performance SQL Server: Consistent Response for Mission-Critical Applications, now on it’s second edition.

I have the first edition of this book, and you can find my review here.

There are 2 new chapters in the new edition: SQL Server on Linux & Intelligent Query Processing. All the other chapters are also updated. The total number of new pages is at around 120 according to the author. And what I like with the new edition is that the chapters are divided among Part Headings which gives a good flow of the content. The chapters are divided into:

SQL Server Internals (Chapter 1 & 2)
Design and Configuration (Chapter 3 & 4)
Monitoring (Chapter 5 & 6)
Performance Tuning and Troubleshooting (Chapter 8, 9, 10 & 11)

Just to give you an idea of how much has changed since the first edition, aside from the fact that the first edition has smaller fonts and was hard to read for people like me who wears prescription glasses (thanks, Apress!), here’s the physical comparison of the two editions:

High Performance SQL Server First & Second Edition

The first chapter, which deals with Internals, has more coverage on the process of SQL Server Query Processing such as Simplification, Trivial Plan Optimization, the Memo, Full Optimization, Cost Estimation, etc.

The new Chapter 2 is a short chapter which covers SQL Server on Linux. It deals mostly with the short history and background of how SQL Server on Linux became possible (SQLOS/SQLPAL, Project Helsinki, and Drawbridge).

Chapter 3 (SQL Server Configuration) has a new section for Configuring SQL Server On Linux with new topics like Environment Variables, Performance Best Practices, Memory, Kernel Setting, etc.

Chapter 4 (tempdb Troubleshooting and Configuration) has a new sub-section for the SQL Server 2019 tempdb enhancement, Memory-Optimized tempdb Metadata.

Chapter 5 (Analyzing Wait Statistics) includes a sub-section for What’s New on SQL Server 2019. This was Chapter 2 in the first edition. So this is one chapter that was moved so the book can have a better flow.

Chapter 6 (The Query Store) has a new sub-section for Wait Statistics. As noted in this chapter, “collecting wait information on the Query Store was not implemented until SQL Server 2017.” So that was a good tidbit.

Not much has changed in Chapter 7 (SQL Server In-Memory Technologies). This chapter is placed under the heading of Performance Tuning and Troubleshooting. As such, I’d like to believe that this might be a good component to consider for performance purposes. I have no experience with In-Memory/Memory-Optimized Tables and so I cannot really tell if the author has done In-Memory Technologies justice. The in-depth coverage of any important technology like this probably requires an entire book by itself. This chapter gives me a good overview of the technology and examples, which, I think, is enough to provide me the understanding that I need to get started.

Chapter 8 (Performance Troubleshooting) deals with, as the heading suggests, performance troubleshooting. This chapter lists and explains the most useful Performance Counters, Dynamic Management Views and Functions.

Chapter 9 (Indexing) did not change that much, if updated at all from the first edition. Indexing is a big topic and you can find full books on this topic (I suggest Jason Strate’s Expert Performance Indexing in SQL Server 2019: Toward Faster Results and Lower Maintenance).

Chapter 10 (Intelligent Query Processing) is a new chapter. As noted by the author, this chapter is an introduction to this feature. The short topics included in this chapter are Batch Mode Adaptive Joins, Memory Grant Feedback, Interleaved Execution, Batch Mode on Rowstore, Table Variable Deferred Compilation, Scalar UDF Inlining, and Approximate Count Distinct.

Finally, Chapter 11 (SQL Server Storage) which deals with, well, storage. Data files configurations, VLFs, Volumes, RAID Levels, and other related topic are discussed in this final volume. This did not change much, if updated at all from the first edition.

But..one note…

One note that I have though is that, on Chapter 8, there are a couple of instances where the author emphasized certain topics with phrase “Introduced in SQL Server 2016 blah blah” but he did not clarify whether or not the same topics were updated or improved (or not) in SQL Server 2019. I’m talking specifically about the sub-sections on Operator-Level Performance Statistics and Trace Flags on Plans. Maybe they did not change at all; but the fact that these features were referred to in terms of the older version of SQL Server made it sound like this whole chapter is outdated. But it’s no big deal.

Do I recommend this book?

Yes, that’s for sure!