A Fine Slice Of SQL Server
Tom Collins has troubleshooting tip for us:
Receiving a regular ” There is insufficient system memory in resource pool ‘default’ to run this query” message in the SQL Server Error logs, leading to unavailability of sufficien resources to connect.
This query supplied by Jonathan Kehayias is a great way to start assessing if there is SQL Server memory presure .
Click through for the query and some additional explanation.
Leave a CommentThe performance impact of pg_tde by Percona for encrypted data pages is measurable across all tests, but not very large. The performance impact of encrypting WAL pages is about 20% for write-heavy tests. The tests were run with an extension RC (Release Candidate), however the WAL encryption feature is still in Beta stage.
Andreas also has a post on the testing specifics:
Leave a CommentThis test was run on a dedicated physical server, to avoid external influences and fluctuations from virtualization.
The server has a Intel(R) Xeon(R) Gold 5412U CPU with 48 cores, 256 GB RAM, and a 2 TB SAMSUNG MZQL21T9HCJR NVram disk dedicated for the tests (OS was running on a different disk).
Jeff Horner doesn’t need permanence:
One of the simplest yet most impactful performance optimizations for SQL Server is relocating the tempdb system database to a fast, dedicated disk—especially if that disk is temporary or ephemeral. This is a common practice in cloud environments and high-performance database systems where tempdb churn is high and doesn’t require persistence across reboots.
Click through for more information on how this can work well for you, as well as how to do it safely and things to keep in mind. One thing I would add is, I vaguely recall that on Azure at least, the temporary D: drive might not be mapped at startup, and it may take a few seconds after startup for the mapping to occur. Thus, it may be a good idea to have the SQL Server service start on a delay to ensure that you don’t run into an issue due to task ordering on startup.
Leave a CommentTom Collins doesn’t need drive letters:
I have some SQL Servers and the file system is organised with mount points. SQL Server is configured to distribute the system files , data , transaction log and tempdb files on those mountpints. As these are Production systems – the DBA does not have permanent access to the Disak Manager without a request for elevated privileges. But they do have privileges to execute administration type of queries.
Do you have a query that allows the DBA to excute a query to determine the Total Size , Available Size and Space Free on the mountpoints
Read on for the answer, as well as a link to an older article from Tom on the topic.
Leave a CommentLast week, I asked y’all how frequently you ran your full backups, corruption checks, index maintenance, and stats updates. The results after over 1,000 responses:
Click through for the results and some of Brent’s thoughts.
Leave a CommentJordan Boich points out something interesting coming in SQL Server 2025:
Accelerated Database Recovery (ADR) was introduced in SQL Server 2019. Its main purpose is to allow for faster database recovery in the event of a crash or unexpected shutdown. Traditionally, the database engine handles crash recovery through a series of phases—analysis, redo, and undo—which can be inefficient and slow, especially when dealing with long-running transactions.
To make a long story short, ADR “shortcutsˮ the recovery process by introducing a new approach to handling undo operations. Instead of relying heavily on scanning the transaction log—which can be painfully slow for uncommitted or long-running transactions—ADR maintains a version store within the user database to track row-level changes. This allows SQL Server to quickly roll back uncommitted transactions without scanning the entire log. The result is much faster crash recovery, quicker rollbacks, and improved overall database availability, particularly in high-transaction environments.
Read on to see what’s new, as well as some of the consequences of enabling this feature.
Leave a CommentMatt Gantz makes a staggered change:
In a previous post I showed how to use a batching strategy to remove large amounts of data from a table while it is being used. Today I will apply the same technique to another common problem- changing the datatype of a column. A common use of this is to normalize a text column into an integer (that references another table), but could be used to transition to and from any datatype . Many of the considerations in the previous post apply, so I advise you to read it as well before using this technique.
Click through for the process.
Leave a CommentKevin Hill has a reminder for us:
Here’s a common theme I hear from small IT teams:
“Our SQL Server is in the cloud now. We don’t need a DBA.”
Not quite.
When you’re running SQL Server on cloud virtual machines like Azure SQL VM or AWS EC2, you’re still responsible for a lot of the same challenges you faced on-prem. The difference? Now you’re paying cloud rates for the same headaches. But you don’t have to deal with hardware and some of the other infrastructure hassles.
Read on to see what that entails in practice. Though I’m pretty sure my target audience generally understands this and it’s people two or three levels up who should give Kevin’s post a read.
Leave a CommentRob Douglas will reboot many things, but not the database server:
I am taking a slightly different tangent. My problem is neither strange or unique – in fact it’s infuriatingly common and it stems from one of the most common troubleshooting techniques in IT. While asking users “Have you tried turning it off and on again?” is a common go to for tech support call handlers, it is not a great idea when the “it” you are talking about is a database server
Click through for a cautionary tale, as well as an explanation of why this usually isn’t the smart play.
Comments closedJon Russell covers a quiet feature:
A checkpoint is a background process that writes dirty pages to disk. A checkpoint performs a full scan of the pages in the buffer pool, lists all the dirty pages that are yet to be written to disk, and finally writes those pages to disk. In SQL instances that do not have many dirty pages in the buffer pool, this is a trivial operation. However, with SQL instances that have OLTP databases, use more memory and/or involve sequential scanning of all pages, the performance of the system could be impacted.
With SQL Server 2012, indirect checkpoints were introduced. In this case, the dirty page manager manages the dirty page list and keeps track of all the dirty page modifiers of the database. By default, it runs every 60 seconds and tracks the dirty pages that need to be flushed.
Read on to learn more about why indirect checkpointing exists, the kinds of capabilities it offers, and the extent to which you might want to tweak its settings.
Comments closed