A Fine Slice Of SQL Server
Welcome back to Part 3 of my SQL Server Backup Internals Series.
In Part 1, I introduced the “parts” of a BACKUP Operation and in Part 2, we delved into Backup Buffers. Today, we’re going to talk about what manipulates those Backup Buffers = CPU Threads. This’ll be a longer blog, so go refill your coffee now.
Andy did an outstanding job explaining what reader and writer threads do and how SQL Server picks the numbers of each.
Comments closedAndy Yun continues a series on backup internals:
Welcome to Part 2 of SQL Server Backup Internals. This blog series is a companion piece to my How to Accelerate Your Database Backups for MSSQLTips.com. In Part 1 of this blog series, I introduced the parts of a BACKUP operation.
Now let’s start focusing on performance characteristics. We can impact the performance of a BACKUP operation by making changes to or more of the following:
Click through for that list, as well as an extended analogy on buffer count and max transfer size.
Comments closedChad Callihan sees a g-g-ghost record:
Have you ever had to deal with ghost records? While I’ve never had a problem that required intervention, I’ve read about people who have.
Let’s talk about what ghost records are and how you should treat them if they’re giving you problems in your environment.
Click through to learn more about them, including some phone numbers for Catholic priests trained in performing database exorcisms.
Comments closedAndy Yun has started a new series:
Recently, I had the pleasure of delivering a new presentation called How to Accelerate Your Database Backups for MSSQLTips.com. This blog series is intended to be a companion piece, particularly for those who prefer to read content instead of watching a video.
In this first post, Andy describes the threads and queues involved with taking a backup.
Comments closedForrest McDaniel abuses that poor server:
The prod version of this issue manifested as 1-3 minutes of SCH-M blocking for index ops, yet only on a single server; I couldn’t replicate it anywhere else. Of course, bad blocking meant nasty timeouts, and that in turn meant I was reluctant to run live diagnostics. Better to just stop index rebuilds. One does not take down production in order to test weird SQL behavior.
An important clue came from a tiny table with a rebuild, and is visible in the whoisactive screenshot above. Why would such a small rebuild need so much CPU? That worker was definitely doing something – the question was what.
Read on to find out the answer, a repro script (that you should not run in your own production environment!), and what you can do about it.
Comments closedRyan Booz explains one area where Postgres’s implementation differs from most other vendors:
All relational databases handle transaction isolation in some way, typically with an implementation of Multi-version Concurrency Control (MVCC). Plain ‘ol, mainline SQL Server uses a form of MVCC, but all older rows (currently retained for ongoing transactions) are stored in TempDB. Oracle and MySQL also do something similar, storing (essentially) diffs of the modified data outside of the table that is merged at runtime for ongoing transactions that still need to see the older data.
Among these databases, PostgreSQL stands alone in the specific way MVCC is implemented. Rather than storing some form of the older data outside of the current table for transactions to query/merge/etc. at runtime, PostgreSQL always creates the newly modified row in-table alongside the existing, older versions that are still needed for running transactions. Yes, every UPDATE creates a new row of data in the table, even if you just change one column.
Read on to understand some of the implications of this and how it affects the way we manage databases.
Comments closedSean Gallardy performs some troubleshooting:
I was asked if I knew any reason why SQL Agent would be using “a bunch” of memory and more cpu than normal. You and I, reader, now have the same information to go on. What do you do? Think about for a minute or two if you want before reading on. I’m not saying the way I did it was the way to do it or the only way, but I gave an action plan for data capture and once data was in hand, was solved in a few minutes. There are all sorts of things that can cause this in a program, but Agent typically doesn’t use a whole lot, unless it’s executing many concurrent T-SQL jobs (which, it really shouldn’t be, get enterprise level scheduling) and even then, the memory should deallocate.
My first inclination turned out to be right: I figured it had to do with a job running. The specifics, that was something I wasn’t sure about, but Sean takes us through the troubleshooting process.
Comments closedJose Manuel Jurado Diaz digs into a customer problem:
Today, we got a new service request where our customer asks about the time spent populating the full-text. Following, I would like to share with you some lessons learned during this process, specifically working with Azure SQL Database.
For this example, we are going to use a General Purpose database with 8 vCores. Let’s get started with the creation of the table and fulltext.
Read on for a walkthrough of setting up full-text indexing and figuring out what the indexing engine is doing at any point in time.
Comments closedSarath Sasidharan has some guidance for us:
Synapse Dedicated pools have been battle tested at enterprise customers across the globe. We deal with data in the magnitude of PetaBytes. Synapse can provide you with the scale of the cloud and the high performance required for your enterprise-grade requirements. The key to maximizing your performance is to follow best practices, check out best practices for dedicated SQL pools in Azure Synapse Analytics
Failure to do so causes performance issues. In such scenarios, is it important to understand where the bottlenecks are. This blog focuses on the different steps a query goes through; from the time the query is fired from the client until it returns back. Delay caused in any of the steps would impact the overall run-time of the query and hence indicate degraded performance.
Click through for a walkthrough of each step along the way, potential problems you could run into, and remediations for those problems. Much of the advice is similar to what you’d get with SQL Server, though there are differences interspersed throughout each level.
Comments closedBob Dorr talks about a trace flag:
SQL Server optimizes database log file flush requests, performing these flush requests inline on the active worker. Certain patterns of log record activity may encounter increased spinlock contention while performing the log cache block flush activities.
Trace flag -T8904 (startup only trace flag) disables inline log flush, limiting the contention possibility from many workers to the subset of background LogWriter workers. When the trace flag is enabled, the worker adding log records mark the log cache block to be flushed and a background LogWriter worker performs the flush activity.
Read on to learn more, including a call-out to Curated SQL favorite Lonny Niederstadt.
Comments closed