A Fine Slice Of SQL Server
Lukas Eder shows how much variety there is in a simple request:
Do you need to know what RDBMS Server version you’re on, and you only have SQL at your disposal? No problem. Most RDBMS provide you with that information in some form of meta data table.
Read on to see just how different each each platform is.
Comments closedAndrew Pruski tells us there is no spoon:
I’ve been interested (obsessed?) with running SQL Server in containers for a while now, ever since I saw how quick and easy it was to spin one up. That interest has led me down some rabbit holes for the last few years as I’ve been digging into exactly how containers work.
The weirdest concept I had to get my head around was that containers aren’t actually a thing.
Containers are just processes running on a host that implement a set of Linux constructs in order to achieve isolation.
So if we know what constructs are used…shouldn’t we be able to build our own container from scratch?
Read on as Andrew breaks out the three necessary constructs and dives into it.
Comments closedAlex Stuart has a way to find blocked processes:
So we need monitoring and alerting on it. Enterprise monitoring tools can do this, and do it well – but if you don’t have one, or don’t have enough licenses for your entire estate, you’ll need to roll your own. (OK, or copy someone else’s if you don’t need the learnin’). This post will demonstrate a basic method for detecting blocking and alerting based on a given threshold.
Read on for the process.
Comments closedErik Darling is overdrawn at the memory bank (which was, sadly, not a very good MST3K episode):
Whomever decided to give “memory bank” its moniker was wise beyond their years, or maybe they just made a very apt observation: all memory is on loan.
Even in the context we’ll be talking about, when SQL Server has lock pages in memory enabled, the pages that are locked in memory may not have permanent residency.
If your SQL Server doesn’t have enough memory, or if various workload elements are untuned, you may hit one of these scenarios:
There are three of them, which is really that there are two of them but they can join forces in an effort to make your life a pain.
Comments closedErik Darling shows us the nuances of using a new DMV in SQL Server 2019:
When dm_db_missing_index_group_stats_query got documented, I was really happy. After all, this has been a peeve of mine for ages.
“Wow look at all these missing index requests. Where’d they come from?”
So this is neat! And it’s better than nothing, but there are some quirks.
Read on for more detail.
Comments closedGrant Fritchey doesn’t have time to wait:
A question that came up recently was how to track the query compile time. It’s actually a pretty interesting question because, there aren’t that many ways to tell how long it took to compile the query, and they don’t necessarily agree. For most of us, most of the time, compile time for a given query doesn’t matter. However, I love telling the story of the query I had on an old system that could run in 90ms, but took 5 minutes to compile. In short, sometimes compile time matters.
Read on to learn several ways to determine how long it took that query to compile.
Comments closedJeffrey Hicks proffers some advice:
The other day someone who is learning PowerShell reached out to me with a problem. He couldn’t understand why the relatively simple PowerShell expression to pull information from the System event log wasn’t working. He wasn’t seeing errors, but he also wasn’t seeing the events he was expecting. Searching event logs with PowerShell is a common task. But as you’ll see, you may need to update your approach to mining event logs with PowerShell. Things change in the PowerShell world, and sometimes in subtle ways that you may not notice. Although to be fair, some of these changes my arise from new versions of the .NET Framework and/or Windows 10. Here’s what we encountered.
Read on to learn a more efficient technique for this process.
Comments closedErik Cobb takes us through an undesirable experience:
Recently, during patching for a 2019 SQL Server, the SQL services refused to start after the patching. It was throwing the following heart attack inducing error:
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.
This is not the first time I have seen this error. Actually, this seems to be the default error that Microsoft throws any time there is a problem with the patching process. The good news is, most likely this is a false error and the master database is perfectly fine.
Read on to learn more.
Comments closedDeepthi Gogrui takes us through page splitting in SQL Server:
Page splits can happen with the logical fragmentation and low page density causing the transactional log to be huge. Page splits are very expensive. As we learned from the previous post, page splits happens when SQL Server tries to insert a row and there is no more space on the page to fit in the page so page will split the page to give the space to fit it in that record. When SQL Server has to insert any rows in to the pages, first the free space on the page is checked at the header information of the page and if the record is within the mentioned free bytes, the record fits in. If the space is not contiguous within the page but there is total space to fit in the record, then the in-memory compaction of the page making the amount of free space on the page contiguous. This is not page split. Page split occurs when the space is not available on the page to fit in the record, in that case page split into half and the split point of the page is usually 50 percent each. Sometimes, the split can happen at the different point on the page is chosen by the storage engine as the obvious split point which is known as skewed page split. This is even more expensive when compared with the regular page split as this will create much larger transaction log.
What causes the page splits?
Read on for the full article.
Comments closedAnyways, with that obvious answer out of the way, let’s talk about something more interesting, like sp_help_revlogin.
Remember sp_help_revlogin? It’s that stored procedure that Microsoft published more than 20 years ago, that never found its way into the SQL Server built-in system procedures. Microsoft still maintains that same KB page till this day (by “maintains”, I mean copy-and-pasting it from one place to another as they change their KB platforms).
Read on to understand what this is and several ways of doing the same thing better, including a new sp_help_revlogin2 that Eitan has put together.