SQL Server Consulting, Education, and Training
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
SQL Server has incredibly verbose error logs, and sifting through them for the highlight reel can be a pain. They’re often full of messages that you aren’t actionable.
Making matters worse is that when you open large log files, the response time for loading all those messages can be painful, and even time out.
Then you have the nice folks who create a new log file every day and keep months of log files around.
But probably the worst thing is that you can’t really search for multiple things without losing all of the other messages.
If you’re like me, and you need to know when interesting or critical events happened, you’re going to love sp_LogHunter.
Right now, it has these parameters:
@days_back int = -7 /*How many days back you want to look in the error logs*/ @custom_message nvarchar(4000) = NULL /*If there's something you specifically want to search for*/ @custom_message_only bit = 0 /*If you only want to search for this specific thing*/ @language_id int = 1033 /*If you want to use a language other than English*/ @first_log_only bit = 0 /*If you only want to search the first log file*/
Along with the usual version, help, and debug parameters I add in to my procedures.
A sample execution looks something like this:
EXEC dbo.sp_LogHunter
@days_back = -30,
@custom_message = N'he''s dead, jim';
I’ll have a video walkthrough tomorrow to talk about the code and show some results in action.
If you run into any issues or would like to see any enhancements to the procedure, head over to GitHub to open an issue.
You get sp_LogHunter and raise any issues here.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
You’re not gonna wanna miss it! Registration is here.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I couldn’t have said it better myself. Registration is here.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
For this most recent T-SQL Tuesday, I challenged bloggers (using the term challenge weakly here) to think of the last time code they saw made them feel a feeling.
I wasn’t terribly specific about what kind of feelings were in play, and so I kind of expected some negative ones to creep in. Most of the results were overwhelmingly positive.
This challenge made me realize that code, like people, comes in all shapes and sizes. And that code, like music, has quite a wide audience. Some folks get down with the Bieber, and others need a full symphony to locate their jollies.
I’m not judging here, just making a casual observation. Just don’t wear a t-shirt of the language to the conference, and we’re still cool.
Anyway, on the roundup!
I’m curating these from the comment section of my post, in order. Here at Darling Data, we strive for fairness in all things.
If you wrote a post that didn’t ping back to me, or you didn’t leave a comment with the link, please let me know so I can add it here.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I recently found myself in the midst of a client issue that lead me to need a bunch of corroborating data from the system health extended event session.
There’s hardly any good documentation on it, and even fewer well-written queries or resources for parsing data out of it.
So now I’m making it easy for you, because I care about you more that Microsoft does.
If you need further proof of that, just look at the Query Store or Extended Events GUI.
Now look at sp_QuickieStore and sp_HumanEvents.
Who loves you, baby? I do.
Since this is currently in beta, it’s missing a lot of the bells and whistles that my other stored procedures have.
Right now, it just pulls all of the useful performance data out that I can get at:
I know that there’s gobs of data around errors and security and all that jazz, but that stuff is often irrelevant to what I’m trying to coax out of a SQL Server.
In the future, I’ll be doing what I can to make sure I’m pulling all of the performance-related event data that I can, and trying to add some analysis and additional filtering to each section.
If you have any feedback, please open issues on GitHub.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I’ve been spending a lot of time lately working on my free scripts. And, I know, I’ve been recording videos about them, but some folks out there like to read things.
In this post, I’m going to talk about a couple cool changes to sp_HumanEventsBlockViewer, a procedure I wrote to analyze the blocked process report via Extended Events, and wish I had given a snazzier name to.
You see, when I wrote it, I pictured it as a utility script for sp_HumanEvents, which will set up the blocked process report and an extended event. But it turns out I use it a lot more on its own.
Go figure.
Well, you live and your learn. Sometimes.
It used to be that there was a column in the output with SQL handles pulled from the blocked process report, and you could take those SQL handles and run Your Favorite Plan Cache Script to (maybe) find the plans for those queries.
That’s clunky, as a wise man once said. Now, there’s an additional result set with all of the available cached plans related to the blocked/blocking queries.
It will look something like this, and there are many related query execution metrics also returned. It just doesn’t make a good screenshot to capture them all.
The findings results section used to only be sorted by the check ID. Through the magic of window functions and aggregates, I’m now also sorting the results by which database/objects/whatever had the highest amount of blocking.
It should look something like this:
The code that handles this is pretty cool, and it’s not something I’ve seen many people do. It’s an aggregate inside of a windowing function, that does something like this:
SELECT TOP (10)
p.OwnerUserId,
n = ROW_NUMBER() OVER
(
ORDER BY
COUNT_BIG(*) DESC
)
FROM dbo.Posts AS p
GROUP BY
p.OwnerUserId;
In a normal query, you could just sort by COUNT_BIG(*) DESC to order results, but when you’re putting results into a table to return later, the sorting won’t be preserved.
Assigning a row number to the aggregates means I can sort by check number, and then the row number within each check, to put the worst offenders up at the top.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I’ve been spending a lot of time lately working on my free scripts. And, I know, I’ve been recording videos about them, but some folks out there like to read things.
In this post, I’ll be talking about some additions and changes to sp_PressureDetector, my script to quickly detect server bottlenecks like CPU, memory, disk, locking, and more. Well, maybe not more. I think that’s all of them.
I added high-level disk metrics similar to what’s available in other popular scripts to mine. Why? Sometimes it’s worth looking at, to prove you should add more memory to a server so you’re less reliant on disk.
Especially in the cloud, where everything is an absolute hellscape of garbage performance that’s really expensive.
By default, I’ll show you results where either read or write latency is over 100ms, but you can change that with the following parameter:
EXEC dbo.sp_PressureDetector
@minimum_disk_latency_ms = 5;
Results may vary. Mine look like this.
This only works for SQL Server Enterprise Edition right now, because it uses a DMV related to Resource Governor.
In the wait stats output, you’ll see how many hours of CPU time queries have consumed since server startup. I know, someone could clear out the Resource Governor stuff, but I’m willing to embrace that as an incredible rarity.
I’m also aware of the fact that I could get similar information from sys.dm_os_schedulers, but that’s only available in SQL Server 2016+, and I sometimes have to support older versions.
On the fence a bit about doing some checks, but right now it’s like…
I wrote a similar bit of code into sp_BlitzFirst, and the fallback is to sum all the CPU time from queries in the plan cache, but that’s awfully iffy. Most plan caches I see, all the plans are less than 24 hours old.
If I figure something else out, I’ll work on it, but for now I’m sticking with this.
Down in the CPU details section, there are some new columns that detail things like
These are useful, especially during THREADPOOL demos, ha ha ha.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.