A Fine Slice Of SQL Server
Erik Darling ponders several techniques for solving the last page contention problem in SQL Server:
If one dare go looking, a list of Microsoft Approved™ solutions for dealing with last page contention exists.
I’m going to walk through my experiences with them in different scenarios, so you get a better understanding of which ones work best, and which ones can be tough to implement.
Read the whole thing.
Comments closedPhil Factor has a couple T-SQL scripts for us to remove and reload a test database:
Copying a database using a BCP dataset from a DOS script or from PowerShell is fairly quick and trivial, but what if the data is already in another copy of the database on the same instance? Imagine you have a database build that is a copy of an existing database on the same instance and you want to run a ‘destructive’ test on the data, and do it over and over again.
Deleting all the data in a database should, you’d have thought, be trivial. Truncation doesn’t work because it doesn’t like to truncate tables with foreign keys, even if they are disabled. DELETE is safer but you will need to then reset any identity columns. Deleting large tables in one chunk isn’t scalable, so you have to do it in smaller chunks. I like to disable triggers while I do this as well. The only disadvantage comes if you have triggers that are supposed to be disabled because the simple code just re-enables them all.
This is, I think, one of the biggest selling points for containers where the database is built into the container image. You spin up a container based off of an image, perform your destructive testing, and destroy the container afterward. The next time you need to run these tests, spin up a new container. And if you need to change the data, modify the container. This introduces new challenges like how SQL Server on Linux has limitations which don’t exist on Windows, but for supported functionality, it’s a nice solution.
Comments closedAndy Mallon gives us a way in SQL to find the service account used for SQL Server:
SQL Server 2005 introduced Dynamic Management Views(DMVs). Technically, there are both views & functions–DMVs & DMFs, but colloquially they’re often simply referred to as just “DMVs”. DMVs simply return information about the state of the server or database and can be used for monitoring and troubleshooting of server/database health & performance.
New DMVs are added all the time. Today, let’s take a look at one of those useful DMVs.
This won’t cover all of the services—for example, it does not include the PolyBase engine or data movement service accounts—but it will give you some of the most important ones.
Comments closedI was recently given the task of identifying the state of an Azure VM so that an automation script using the
az vm run-command invokewould not fail if the VM was down or under a reboot.I initially thought the task would be really easy and a simple query of the VM state using
Get-AzVMwould provide us with a running state property of the VM, but as it happens the state is a little abstracted.
Click through to see how Mark solved the challenge.
Comments closedGuy Glantser helps us troubleshoot who is using tempdb space and for what purpose:
In the past two weeks I encountered 3 different scenarios in which tempdb has grown very large, so I decided to write about troubleshooting such scenarios.
Before I describe the methods for troubleshooting tempdb space usage, let’s begin with an overview of the types of objects that consume space in tempdb.
The answer, of course, was Professor Plum in the Aviary with the eager spool.
Comments closedSean Gallardy lays down the gauntlet:
You’re probably wondering why you couldn’t spawn a new thread, why this error happened, why you shouldn’t just up the max worker threads, and probably also thinking this must be some kind of “bug” in SQL Server. So here’s where our awkward conversation starts… It’s you. Contrary to every relationship breakup you’ve ever had, it’s definitely you. I’m not saying this to be mean but to really drive the point home here. The major reasons for this occurring are large blocking chains, too much workload for the server size (databases, users, etc.), and/or your virtual infrastructure sucks. There aren’t too many reasons for getting yourself into this situation, and while what I’ll be putting forth here isn’t exhaustive of all edge cases and scenarios, these are by far the majority of all the items in the wild that I’ve either worked on or have been involved in at some level. Side Note: If you’ve read this far, are shaking your head, calling me names that an irate sailor might utter, and telling yourself that upping the max worker threads as the product error suggests and Microsoft should fix their bugs then you can stop reading here as you’re probably not open to learning why you have issues in your environments.
One more scenario I’ve seen is mirroring thousands of databases on a single instance. That scenario fit none of Sean’s criteria—there was very little blocking, most of the databases were small and infrequently-used, and the infrastructure was the right size. It was just a huge number of databases and each database requiring a minimum of X worker threads. Mind you, it was still a bad idea…
Comments closedErin Stellato shows how you can track the results of those automated CHECKDB runs you’re doing:
First, you need to be running CHECKDB on a regular basis. If you’re going to ask what you mean by regular basis, I’ll direct you to a post of Paul’s that is still relevant: Importance of running regular consistency checks. There are a few factors to consider, but in general I recommend at least once a week. If you’re a 9 to 5 shop and have the maintenance window to run CHECKDB daily, feel free to do that.
Erin walks us through it and also recommends checking out Ola’s scripts for integrity checks. I’d add to that Minion CheckDB.
Comments closedTaiob Ali walks us through the concept of memory grants:
DesiredMemory: Memory estimated to fit intermediate results in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialDesiredMemory.
(Amount needed to store all temporary rows in memory. This depends on the cardinality estimate, expected number rows and average size of row). This is called additional because a query can survive lack of such memory by storing part of temporary rows on hard disk. A query is not guaranteed to have the full amount if the total exceeds the preset limit.)
Read on for explanations of each of the elements in MemoryGrantInfo.
Niko Neugebauer walks us through improvements in certificate management with SQL Server 2019:
If you have ever used them for connection encryption (TLS 1.2), you might have had some battles with the certificates, having to go into the registry to edit the thumbprint and if you doing a Failover Cluster or Availability Group installation – you would have to enjoy this operation on the multiple nodes. Not-so-very-user-friendly to say at least!
So many times, it would scare-off a non Server/Database Administrator or a junior Server/Database Administrator from trying those features.
Niko also mentions something very interesting about SQL Server Configuration Manager compatibility at the end of the post.
Comments closedBrett Powell has a pair of Power BI templates for monitoring Azure Synapse Analytics:
Upon clicking ‘Load’ you’ll either need to provide your credentials for this source (if you don’t have this data source saved from previous use) or the queries will execute and the following report pages will be available:
– Executions
– Waits
– Sessions
– Waits Detail
– Execution Detail
– Memory
– ExecutionDrillThrough (hidden)
Click through to see what the templates look like and how to obtain them.
Comments closed