A Fine Slice Of SQL Server
Josephine Bush tracks some errors:
Logging is a necessity in the database world, well, the entire tech world. You need to know what is happening in and around your databases. It’s easy to setup in Azure SQL DB.
Read on to learn more about the logging options available in Azure SQL DB, as well as how you can configure and view the log and audit results.
Comments closedReitse Eskens tries out a tool for monitoring Microsoft Fabric installations:
Let me set the scene quickly for you. You’re working for an organisation where Fabric is in the process of being adopted or it’s already fully in use. Regardless of the number of capacities, workspaces, etc, you’re interested in what’s going on in your Fabric environment. You have questions like “Who is using the reports?”, “Who is changing settings in the Admin panel?” and “How is my capacity being used?”.
Read on for a single tool that can solve these sorts of questions.
Comments closedKevin Hill notes that the error log isn’t just for errors:
SQL Server’s Error Logs often get overlooked, until something breaks. But if you know how to use them, they can tell you what went wrong before you’re knee-deep in outage mode.
Whether you’re a sysadmin who inherited SQL or an MSP juggling multiple environments, understanding the ErrorLog is low effort, high return.
Let’s decode the basics.
Click through for examples of information you can find in the error log. Kevin mentions cycling the error log weekly. Back in my DBA days, I’d cycle them daily because I didn’t want the files to grow too large and become unwieldy. Some of this, of course, depends upon how hard people are pushing that box and how much you need to log.
Comments closedTom 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.
Comments closedThe 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:
Comments closedThis 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).
Tom 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.
Comments closedJeff 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.
Comments closedLast 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.
Comments closedJordan 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.
Comments closedMatt 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.
Comments closed