Tag: Performance

Free SQL Server Performance Monitoring: Dashboard Overview: Server Health, Query Plans, and More!

Posted on by Erik Darling

Free SQL Server Performance Monitoring: Dashboard Overview: Server Health, Query Plans, and More!

Summary

In this video, I delve into the exciting world of free SQL Server monitoring tools, specifically focusing on a dashboard I’ve developed called Performance Monitor. This tool is designed to make life easier for DBAs and developers by providing real-time insights into server performance, resource usage, blocking issues, and more. With a user-friendly interface that includes various tabs like an overview, resource metrics, and query analysis, the dashboard aims to simplify complex monitoring tasks. I also highlight some of its unique features such as the plan viewer, which leverages Microsoft’s SQL Server extension for Visual Studio Code to provide detailed execution plans with actionable insights. Whether you’re a seasoned DBA or just starting out, this tool can help you keep your SQL Server environment running smoothly and efficiently.

Chapters

  • *00:00:00* – Introduction to SQL Server Performance Monitoring Tool
  • *00:07:39* – Detailed Walkthrough of Query Tabs
  • *00:15:00* – Downloading and Installing the Tool
  • *00:16:55* – Exploring the User Interface <– Thanks, AI
  • *00:21:48* – Advanced Features and Future Developments <– Thanks, AI
  • *00:25:37* – Conclusion and Thank You <– Thanks, AI

Full Transcript

Erik Darling here with Darling Data, here to talk more free SQL Server monitoring, make all the people of the world happy, or at least the SQL Server people of the world happy, as happy as they can be, being SQL Server people. So, I want to give a little bit of an overview of the dashboard. I’m also going to, at least at current, you know, when this video publishes, these features might still be in dev, but if you’re watching this well after the fact, they’ll, you know, just look at the date on the video, you can see some cool new stuff that I’m going to be releasing. So, the full dashboard has a bunch of different tabs in it, because people love tabs, graphs, and all that stuff. I’m not saying that, like, you know, it’s for everybody, but, you know, a lot of people who like monitoring tools like this sort of thing, or need monitoring tools, they’re like, oh, look at that pretty line that shows me where everything went wrong. So, I have, like, an overview, a bunch of stuff in it, of course, a lot of stuff about performance, resource usage, how memory is getting used, a bunch of stuff about blocking and deadlocks. And then, my store procedure, SP Health Parser, which goes into the system health extended event, populates a lot of system events, but I’m going to tell you something, if these graphs are empty, it’s a good thing, right? Because you don’t want, like, ideally, like, you want the system health extended event to be like, I got nothing, we’re healthy, we’re good. No news is good news.

Also, you know, kind of, you know, some standard stuff, like a Nox style landing page, sidebar with all your servers in it, and you can pick favorites if you want. Everyone has favorites. And, of course, a whole bunch of ways to alert you. There’s email, there’s, you know, like, little pop-up alerts, stuff like that. There’s also, like, little alert badges on different charts and graphs when we find bad things. So, like, the Nox style health cards, they basically just tell you, like, kind of like a short story overview of what’s going on with the server. Is it up? Is it online? Like, CPU usage? Is there blocking? Stuff like that. And then all of the, like, the overview tabs that you see, like, all the other tabs that you’ll see going through the dashboard will break out other resources, usage, queries, stuff like that.

There are a bunch of UI controls that you can use to look at different time periods. I tried to put some simple ones in there that help you sort of figure out, you know, just, like, last one hour, 12 hours, 24 hours, week, 30 days, stuff like that. Because, like, no one likes fiddling with, like, you know, like, calendar pickers for everything.

There are calendar pickers in there in case you need to fiddle with those things. But in general, people kind of want, like, like, show me the last day or something. Like, show me, you know, the last, like, since, what happened since this morning.

And there’s also a bunch of stuff that you can do to get data out of there and, you know, things like that. So, like, when you right click, you can, like, export stuff to CSVs, you can save graphs as pictures, you can copy cells and rows. Again, you get, like, system tray and toast notifications when things go wrong, like, in the middle of whatever.

And there’s also a way via the dashboard to edit schedules so you can manage how things, like, what cadence things get collected at. I put some pretty aggressive defaults in there because I wanted, like, it’s like, like, let’s start, create, let’s just get stuff. And then you can tone that down based on your environment if you want.

Like, I have some stuff that I would be like, look, if we’re going to figure some stuff, if we’re going to figure out some performance issues on your server, we’re going to have to collect some data, right? Because you can’t see a problem, you can’t solve a problem. But, so, I start pretty aggressive there.

But if you want to tone that down, you are absolutely welcome to. If you want to check out any of this, go to code.erikdarling.com. Everything is in a repo called Performance Monitor. The way to download it, because I’ve gotten a lot of questions about it, the way to download it is you go to the Releases page.

It’s a pretty easy to find link at the repo called Releases. There are zips for everything. There’s the Lite dashboard bundled separately, the installers, and, of course, the full dashboard, which is what we’re going to be looking at today.

So, if we come over here and look at stuff, this is basically what you’re going to be greeted with when you first open it. It looks like this. So, you have these server cards that show you sort of everything that you kind of want to know just at a glance about a server.

Like, what’s CPU? Like, how many threads are in use? Like, what’s memory allocation look like? Ah, I got a little thing there.

That’s hanging off the edge a little bit. I’ll fix that. But this tells you sort of the difference between, like, the buffer pool and query memory grant. So, if you have queries using a bunch of memory on there, it’ll let you know.

Also, like, when the last time you had blocking or deadlocking was, and if your collection health is okay. So, that’s all that stuff. There’s also an alert history tab.

So, if, like, I haven’t had any alerts fire off in the last 30 hours, but if I change this to seven days, we’ll see, like, a history of alerts that went out. You know, you can dismiss these and, you know, do what you want with them. But, you know, I just think it’s nice when people say, hey, like, hey, like, maybe I was away from my computer for a little while.

Maybe I have an email, like, let’s set up email alerts. Maybe I just look at what fired off that I might have missed. But once you dig into a server, once you open up one of the server tabs, this is what life generally looks like.

You know, like I said, there’s a resource overview there. So, you get, like, CPU. And if you hover over this stuff, you can see all the little numbers and, like, you know, what was going on and what your weights were.

So, like, just sort of a general overview of things. There are other tabs. They’re not going to have anything interesting for me in there right now. But, like, you know, like, this will give you a summary, just a one-line summary of, like, if your server is in normal health, what your total weight was and how much wait time was spent on it.

How many, like, you know, like, long-running queries and deadlocks and blocking and stuff we found for that. And then, you know, like, I’m going to be expanding on this. But this gives you sort of a breakdown of, like, critical issues on the server.

It basically runs the code from my perf check procedure and looks for terrible things happening on your server and reports about them here. I have some stuff from the default trace in here as well. So, like, some of the, like, high-level stuff that comes out of there.

Also, a tab that tells you about your current server configuration. That’s at the server level, database scope configuration stuff, and any trace flags that someone might have enabled. This will log configuration changes.

So, at the server database and trace flag level, if anyone has changed anything about the server, that’ll show up in there because we’re just checking to see, like, every day or so if things change. Collector health tells you how things are going, like, if anything’s failing, getting messed up. And then the running jobs tab.

This tells you about active agent jobs and stuff like that. So, like, if you’re, like, come in and you’re, like, why is CPU crazy? You can come in here and look. You can say, oh, well, this, you know, this stupid index maintenance job that the dummy DBAs won’t get rid of is screwing things up. So, I’m going to start backwards a little bit here.

System events. Again, this is all the system health stuff. And, again, you don’t want to see things in here. Like, I have some stuff in the severe errors tab because, like, you know, it’s killing queries at some point. But all the other stuff on this server, at least, is pretty clean because I’m not, like, I just not collect, like, there’s just nothing to collect in here.

The locking and blocking tabs. This will talk about, I mean, I don’t have anything in the last 24 hours, but I do have some lock waits. I just didn’t have any blocking.

Right? So, like, I don’t, like, there’s nothing showing up in here, which is fine. That’s okay for me. But down in here, I’ll get these populated when I’m going in closer on those. But then, like, this will be the deadlock XML report.

This will be the block process report. And this is just sort of blocking trends where there’s, like, you know, how many blocking and deadlocking events we had and then, like, the duration of them. Under memory, there’s all sorts of stuff about memory grants, memory clerics, the plan cache.

If there was any memory pressure going on in the server, it would get noted here. And then, under resource metrics, there’s some neat things, right? So, this is, you know, like, CPU, 10 dB, and, you know, like, some perfmon counters in here.

Just to kind of get an idea of, like, server busyness, like, you know, anything weird going on. I got wait stats over here so we can, like, you know, get a good idea of, like, you know, sort of, like, how, like, did anything spike up? Is anything bad?

Like, what should I look at? What, you know, what time frame is, like, would matter to me? You know, like, usual 10 dB stuff, 10 dB usage by different consumers, 10 dB latencies, like, all the file I.O. latency that you’d be used to seeing. You can see, like, the different database files down here and the different log files, different database files here and here.

And, you know, this is just read and write. Under perfmon counters, something that I just added, which I’m very, very proud of, because I am always forgetting exactly which perfmon counters I should be using for different things.

I’ve added these sort of, like, perfmon packs or, like, perfmon templates. So, depending on what you’re looking at, right, you might care about, you know, like, memory pressure, CPU, I.O. pressure, 10 dB, locking and blocking, stuff like that. So, if you want to explore a specific scenario in here, you can change these and it’ll select all of the perfmon counters that, you know, you would normally look at for this without you having to go through a whole list and click all of them.

Because that’s annoying and you’re going to forget and you’re going to miss one or, you know, I don’t know, maybe you do this when you’re drunk and forget stuff. But, you know, there’s just all sorts of things in here that make sense to look at and, you know, just to dig in from a perfmon level. This is on the dev branch now.

This will be out sometime this week. No promises because I’m still kicking stuff around. But the thing that I added in here that I’m real, real proud of is the plan viewer. So, Microsoft released the MS SQL extension for Visual Studio Code, which is MIT licensed.

And it just happened to have, crazily enough, a whole bunch of assets that I could use for graphics and also for, like, you know, making sure the XML is all in there correctly. Right? Like, the XML is all documented.

I can make sure that what I get from the XML is exactly what Management Studio would get from the XML. And I can add my own stuff to it. I am a hog in heaven because all the, like, you know, plan analysis and indicators and stuff that I would want to see when I’m looking at a query plan, I can put into these query plans. Right?

So, check this out. Right? So, like, if you right click on any one of these rows and you say get actual plan, you’ll get a little warning. It says you’re going to execute this query. Now, this does something that I always loved a lot from SQL Sentry, which was you can execute a plan.

It’ll throw away the results, but it’ll give you the actual execution plan. If we zoom in a bit, you’re going to see that it has all the execution numbers. I break things out a little bit further.

So, you get the duration number that you’re used to in SSMS, but you also get the CPU numbers, right? So, if we look over here a little bit, we can see, like, this was 633 milliseconds of wall clock time, but 3.366 seconds of CPU time. Now, if you’re looking at the little yellow bubbles up there, those are parallelism indicators.

I chose not to put those over the icons for one reason. If we have multiple things on a plan that I want to show you, like, down here is a good example, right? We have, sorry, the zoom is a little slow on me.

We have a warning, and we have the parallelism indicators. You can see them both, right? So, it’s not like, oh, was that parallel or not? Is it just a warning? What happened?

Right? And if, like, but, you know, this plan is kind of boring, right? Now, we have, like, a, you know, missing index suggestions up here. We can actually see both of them, right? Isn’t that crazy, right? You can see all the missing index suggestions.

It’s wild. But the real thing that I wanted to show you is in this plan, right? You know, this thing runs for about 20 seconds. So, I didn’t want to sit there and make you wait for 20 seconds. But this has two things in it that I have been dying for SSMS to have that Microsoft just wouldn’t give you, right?

And that is, if we zoom in a little bit, right? Let’s get this over here so it’s sort of a little bit more out in the open. We have an eager index spool.

And if you hover over the eager index spool, it has the index that you should create to get rid of the eager index spool, right? So, like, there’s a warning here that’s like, yo, SQL Server is creating an index for you. Here’s the index it’s creating.

If you go create that, this thing goes away, right? And then over here on the clustered index scan, because something I always talk about when I’m talking about eager index spools and parallel execution plans, is that they make, is that like when the spool is getting built from the clustered index scan, SQL Server only uses one thread to do it.

So, when you get an actual execution plan and you have skewed rows on parallel threads, I can warn you about that, right? Thread 5, process 100% of the rows, right? This is fantastic.

The amount of stuff that I can put into this to make your lives better and easier is going to blow your mind. I have way more stuff lined up. So, you know, keep an eye out and all that. But otherwise, these query tabs, you know, they kind of give you the normal set of information that you’d want to get.

You know, like all this, like, you know, sort of like resource uses for the queries, the query text. You know, if you want to just say view an estimated plan for something, you don’t have to get the actual plan. You just click on view plan.

You get all that. You can save the plan if you want, right? Like all this stuff that you can, like, you know, normally do with SSMS. And this should be a pretty SSMS-like experience, right? At least from viewing it, like the perspective of viewing an execution plan.

Another thing that I like that I could put in here was warnings on filters, right? And, you know, stuff like, you know, like filter up, like just warning you, like, hey, if there’s a filter, you’re sometimes leaving this, like, you know, like, granted, this is like a query store query. So what am I going to do?

But like I can warn you, like, hey, you know, that filter operator that discards rows, I have to like way later than you maybe want to. So I can like bring, I can point that stuff out. So this is just a small, like a, like intro to some of the stuff that I’m building into this. Again, it’s totally free, totally open source.

I have received one single $25 contribution from some nice person who was like, wow, you’re doing a lot of hard work on this on GitHub. So thank you, JiHuan. I apologize if I said your name wrong.

If you’re out there listening, you can give me a phonetic typing of it somewhere in a comment, maybe. But anyway, I’m getting pretty happy with this, where it’s going. Again, I’ve got a lot of other stuff lined up that I’m not quite ready to talk about yet, but soon enough, soon enough, soon enough.

Anyway, again, if you want to check this out, it’s at code.erikdarling.com. You can download it from the releases section. It’s where all the zips are.

If you download a zip and there’s no EXE in it, sometimes that’s Windows blocking stuff. You might have to unblock that, but it should all be pretty easy and straightforward once you go through the readme, which I have carefully crafted to make your life much, much easier.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something, and I will see you in tomorrow’s video, where we will talk some more about this wonderful, free SQL Server performance monitoring tool. All right.

Thank you for watching.

 

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Free SQL Server Performance Monitoring: Full Dashboard Installation

Posted on by Erik Darling

Free SQL Server Performance Monitoring: Full Dashboard Installation

Summary

In this video, I delve into the installation process of my free SQL Server performance monitoring tool, addressing a common issue where users have questions about setup despite having access to detailed documentation. I explain that there are two main installers: one for command line use and another with a graphical user interface (GUI). The command line installer offers various options such as reinstalling the database, choosing encryption levels, managing server certificates, and handling password input in different ways. Additionally, I cover how the tool runs through 52 installation scripts, including community dependencies like SP WhoIsActive and my own scripts for data collection and analysis.

Chapters

Full Transcript

Erik Monitoring Tool Mogul Darling here, back to talk to you about, well, my free tool that monitors your SQL Server’s performance. And we’re going to talk a little bit today about installation because I’ve actually moved this up in the lineup because I have gotten a bevy of questions. I have a readme file that documents all this stuff very nicely, but I keep getting questions about installation. things. People are, like, impervious to reading, right? Not even comprehension just won’t do it, right? So here’s a video where we’re going to talk about installation options. So there are two installers. There’s a command line installer and there’s a GUI installer. That’s a graphical user interface. The command line installer has options where you can choose what you want to do. Like you can reinstall, which will drop your old database and make a new one. You can choose your encryption level, right? Which that’s optional, mandatory, strict. You can choose to trust or not trust the server certificate. And you can choose how to hang, how to put in your password. There is an environment variable that you can use if you’re uncomfortable sending your password across a connection like this. You can also use Windows authentication. There are many wonderful things that you can do with this.

I promise you. I promise you. I promise you. I promise you. It’s there. Wait. Those words just waiting to be read. But the performance monitor database runs through, well, 52 install scripts and then some extras, right? So I’ll show you what that means in a minute. It will also go and grab some community dependencies, right? So, you know, like I wanted to make sure that this was a very community oriented tool and that people would get some behaviors and some results back that look like what they might find in community tools with a whole lot of pretty like graphs and charts on top of them. All of these collectors run off agent jobs. The collection job runs every minute. Not every collector runs every minute, but the like some of them do, some of them don’t. But the collector agent job runs every minute, goes through and figures out which like what schedule each collector is on. And then if that schedule meets the criteria, then it will run them.

There’s a retention job that will run at 2 a.m. And look for old data to get rid of. And then there is a third agent job that looks for the monitoring tools agent job being stuck. And if it’s stuck, it’ll kill it and say you did a bad job. You have to start over again, right? So that’s how that works. Behind the scenes, we create a performance monitor database. That’s where all the tables and views live. This is again for the full dashboard. The light dashboard is very different. We talked about that in the previous video.

And then all the collector store procedures that go through and look at your metrics and job monitoring, those will all get installed. There’s a there’s a neat store procedure that calculates delta frameworks, right? So we need to like, like, like, like charting raw data is kind of weird. So we do all this. We I do all this in my head. It’s like, what’s the delta?

There’s a store procedure that does all the delta calculations for things that we want to graph so that we can we get an accurate view of what change so that the spikes in the graph makes sense. Again, the community dependencies we SP who is active goes in there all the the SP blitzes all get installed for you and all of my scripts. And this is just in case you want to use them. The performance monitor does use some of them for some stuff like SP who is active goes for the active queries.

Of course, SP blitz lock, which I wrote most of at this point that shreds the the deadlock XML and then my store procedures, human events block viewer and health parser also do some work in there. Again, we have the three agent jobs responsible for collection, data retention and looking to make sure that those jobs don’t get stuck. There’s a bunch of like metadata tables that keep track of like installation history and stuff in case you’re on the upgrade path and then but 55 scripts in total get executed.

But there are three of them that are sort of not involved with that. But let’s take a quick look at what the installer looks like. So this was this is this is an installer run that I did where it just completed.

It’s not fun to watch like, you know, a log fill up live, but I can I can hit install again and we can we can watch it go. But this was SQL 2016. I keep instances going back for all the supported versions so that I can test stuff and make sure it works.

And on this main screen is where you put in the server you want to connect to you want you choose between Windows and SQL off. And if you, you know, I use SQL Server authentication because I’m lazy and I have VMs. And this is also where you would use the SSMS like settings about encryption and trusting the server certificate.

So this just like the monitoring dashboards uses Windows Credential Manager. So you are not getting anything like like this is the same connection methodology as SQL Server Management Studio. If you don’t trust SQL Server Management Studio, fine.

But if you do, if you like, OK, SQL Server Management Studio, you can connect to my SQL Server. This uses the same thing behind it. So there’s nothing weird.

There’s a big warning down here, right? The big warning says if you perform a clean install, we are going to drop the performance monitor database and start fresh. There are a variety of reasons you may want to do this.

If you’re on the upgrade path, you probably don’t want to do this. But there might be a reason for you to, right? There’s all sorts of things that you might don’t want to say, let’s just start this over with. So there’s that.

There’s a somewhat newer option to reset the collection schedule to recommended defaults. The reason why this is here is because as I’m working with the monitoring tools and I’m kind of tuning and tweaking things, I might change the collection schedule now and then when I find that certain things work better at certain cadences.

And so if you want to use the cadence that I recommend, you can hit that button and you’ll get the schedule that I recommend. You are free to not check that button and keep your schedule. I am totally cool with whatever you do with it.

And then there’s another checkbox here to run some validation tests. The validation tests basically just run all of the stored procedures in there a first time to make sure that everything runs without error. Because if you run into something there, then you’re going to run into that probably every single time the collector runs and you don’t want to do that.

So you want to make, if there are any errors, report them on GitHub. Let me know. I will do my best to fix them. But down in here is where we sort of walk through everything getting created and all these things happening.

And at the very bottom, like this is like, again, just, you know, installing up, like installing all the stored procedures. Down here is where we start installing the community dependencies. Right. So everything that, you know, who is active, my darling data, and then the first responder kit scripts.

And then this is where we execute the validation thing. And if anything weird happens in here, well, let me know. I will do my best to fix it.

And then, of course, you get, you know, all of this sort of, you know, stuff in here. Right. So if anything happens, it tells you where the log is. So if you get an error and you want to report it to me on GitHub, you can include these helpful details.

I would love it. Like, you know, more details are better. So put everything you can in there.

You can maybe leave out like your, you know, computer name, like whatever, whatever this is. But, you know, everything else in there is good. And then you will get sort of like an installation summary over in the corner that I’m helpfully standing in front of.

There are other buttons. There’s the, oh, I got to move this way. There’s the install button.

There’s the troubleshoot button. And there’s another button that says view report, which will bring up a text file with all of your log information in it so that you can share it with someone who might care to troubleshoot it like me. There’s also a close button over under this armpit.

Hold on. This armpit where it says I’m never going to get this mirror camera thing right where you can close things. And that’s how you exit the installer. So that’s pretty much it there.

Again, all of these options are available in the command line as well. And I highly recommend that you read the documentation about these things before emailing me the same question over and over and over. All right.

Thank you for watching. And I’ll see you in tomorrow’s video where we’re going to do a bit of a deeper dive into some of the charts and graphs in our wonderful free open source SQL Server monitoring tool. All right.

Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

SQL Server Performance Office Hours Episode 54

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 54


 

To ask your questions, head over here.

Summary

In this video, I dive into some of the most pressing questions submitted during my office hours session, covering topics like `sp_whoisactive` and its parameters, memory grants and their management in SQL Server, query performance variability, and when to step in with manual interventions. I also discuss the nuances of parameter sensitivity and how it can affect query performance. Whether you’re a seasoned DBA or just starting out, these insights should help you navigate common challenges in SQL Server performance tuning. If you found this content valuable and want more, consider supporting my channel by subscribing, liking the video, and sharing with your colleagues. And if you need personalized assistance, I’m available for consulting services—no monitoring tool mogul lifestyle here!

Chapters

Full Transcript

Erik Darling here with Darling Data, and we’re going to take some valuable time away from my new career as a monitoring tool mogul to answer some of your very important office hours questions, because, I don’t know, why not? I still like you. You know, I have not went and joined the monitoring tool glitterati. We’re partying with starlets in limousines, with champagne, and I don’t know, whatever else people do in limousines that probably just make the toes curl. Anyway, down in the video description, you’ll find all sorts of helpful links, and you’ll also see useful things like, I don’t know, what do you call it there, summaries and chapters, which the robot sometimes gets right and doesn’t hallucinate the videos being two and a half hours long, and other times, other times, it’s not so good. Anyway, if you would like to hire me as a consultant, I still, you know, again, my monitoring tool mogul career has not quite eclipsed my consulting career, so if you would like to hire me to help you with your SQL Server performance problems, I am available in all sorts of ways, and you can see the ways that I’m available down in the links.

You can also find ways to purchase my training, become a supporting member of the channel. If you like this content so deeply and feel strongly motivated to spend $4 a month, you can do that. You can also find links to ask me the office hours questions, and of course, as always, if you like this content, but, I don’t know, perhaps you’re not financially motivated to give me a little hug and kiss, you can always like, subscribe, and tell a friend, so some channel numbers go up. And I can feel some sense of joy in my day-to-day life, aside from watching the downloads and GitHub of the monitoring tool, which, speaking of which, if you want to check that out, it is completely free, it is completely open source. There’s no email requirement, there’s no application phoning home to tell me all about your dirty server secrets.

It’s just a whole bunch of T-SQL collectors running on a schedule, collecting all sorts of important performance data, supplying you with a Nox-style dashboard and all sorts of pretty charts and graphs, and there’s even, if you’re, you know, becoming one with the robots, there is even an opt-in, optional, built-in MCP server, so you can ask questions about your performance data and see what the robots think about your server crisis. And it’s a wonderful little thing, so maybe the finest thing that I’ve contributed to the SQL Server community, aside from my handsome face, which will get better looking when I officially become a monitoring tool titan and get a whole bunch of crazy plastic surgery. If you’d like to see this handsome face out in public in the world, I will be at a variety of events coming up over the next portions of time.

Data Tune, down in Nashville, March 6th and 7th. Data Saturday, Chicago, March 13th and 14th. SQL Day, Poland, May 11th to 13th.

And, man, jeez, that one’s a dash, the other ones are ands. Whoa, why are these? I gotta beat up my PowerPoint guy. And, of course, Data Saturday, Croatia, June 12th and 13th.

I will be doing pre-cons at all of these events about advanced T-SQL. I don’t know, maybe I should do advanced SQL monitoring at this point. Might be fun.

But you should go to all of those. Each and every one of them does travel around the world catching me. For now, we are still trapped in the miserable doldrums of February, waiting for March to arrive and springle our springs. But, man, I hate it outside right now.

I’m miserable everywhere I go. So, let’s stay inside and answer some questions. What do you think about that, huh? All right. So, let’s see.

SP, who is active? Do you like at getplans equals 1 or at getplans equals 2 and why? I threw in an and there. I apologize for my editorial interjection into your question.

But I usually just run with getplans equals 1. Getplans equals 2 is a little known thing. Getplans is not a bit.

It is, I believe, a tiny int. But you can choose getplans equals 1 or getplans equals 2. And I’m going to get maybe a little deeper on this than most people will care about. If you are running, I would say, an older version of SQL Server, getplans equals 2 would return all of the plans associated with a batch.

So, kind of like if you look in the plan cache and you look for a stored procedure and you open it up and it’s all of the query plan for the stored procedure. It’s like a big, long thing.

getplans equals 2 would give you that. getplans equals 1 would just get you the query plan for the query that’s currently running on the batch. However, if you’re on a newer version of SQL Server and SP who is active prefers the new query plan DMV that gives you the sort of in-flight query plans or like the live query plan, it just returns the statement level 1 anyway.

So, me, I just go with getplans equals 1 because usually the only plan I care about is the plan that is currently up for the query running. You might find different uses for getplans than I do.

That’s totally okay, but that’s my thing. All right, question number two. How do I know when it is time to reduce the memory grant limit on the default pool below 25%?

Is it determined based on how much RAM I have or is it some other factor like memory weights? Well, I will typically start messing with this as soon as RAM becomes an appreciable number, somewhere around the 128 gig mark.

And the reason for that is because SQL Server by default and what this question is really asking about is using Resource Governor to reduce the default max memory grant for queries.

And you can do that with Resource Governor. And you can even do that with standard edition of SQL Server 2025, which now has Resource Governor, which is so lovely.

But the reason why is because by default, 25% can be a pretty big memory grant. That’s 25% of your max server memory setting. And boy howdy, the more memory you give SQL Server, the bigger that 25% gets.

Like 25% of 128, 25% to 256, 25% to 384, 25% to 512. Like, oh, those numbers get up there. So really the more memory you have, the smaller you want to make this.

SQL Server will ask for memory grants pretty heavily. Getting this to the right number is a somewhat different exercise because it, well, it sounds good to just drop this number down.

You know, you do have to start keeping an eye on queries to make sure that like, you don’t have important ones that start spilling off the disk. So like there is some caution that needs to be exercised here.

But one, like, so like, well, it is a good sign to look at memory weights like resource semaphore to figure out if this, you know, does this apply to me? The other thing to look at is that even if maybe, even if you’re not hitting resource semaphore weights, you might still have queries asking for pretty big memory grants.

Like even if you don’t have multiple queries fighting over memory grant space, because by default, SQL Server will give about 75% of your max server memory setting out to a bunch of queries running.

Even if you don’t have that happening, you might have queries asking for those 25% memory grants and knocking a whole bunch of important data out of your buffer pool, which is not a good time either.

So, you know, well, memory weights like resource semaphore are certainly fantastic indicators of queries having memory grant clashes. You should also keep an eye on those page IOLATCH SH weights because you may have queries that are asking for memory grants, knocking a whole bunch of data out of the buffer pool, and then other queries have to go read that data back in.

Remember that SQL Server does not work with pages on disk except to read them into memory. All right. Let’s see here. Oh, another memory question.

Jeez Louise. People love memory, huh? If SQL Server shows plenty of free memory, how can queries still suffer from memory pressure? Well, I don’t know.

What’s that free memory doing? Is it really free? I don’t know. Did you download that memory? Is that why it’s free? I don’t know. Well, that’s a very vague question. The mind wanders a little bit on this one.

I would say that if SQL Server shows plenty of free memory, in other words, memory that is not dedicated to the buffer pool, then, or like other memory clerks and consumers within SQL Server, I would say that perhaps you have had queries ask for a lot of memory, clear a lot of that space out, and SQL Server may not have recovered from that just yet.

That’s probably what’s happening there. Where I would look is at what we were kind of just talking about in the above, with the above question. I would probably look to weight stats and I would see if you have resource semaphore weights going on and I would also look at weight stats to see if you have a lot of page.io latch underscore whatever weights going on because that might inform you as to whether SQL Server has plenty of free memory because a lot of it is not being dedicated where it should because it was dedicated somewhere else temporarily and SQL Server has not filled itself back up yet, primarily in the form of the buffer pool.

That’s the best I can do with that one. All right. Next up. When should I stop trusting the optimizer and intervene manually? Probably right about the time you start asking that question.

Honestly, that’s about when I would do it. No. So, I mean, what are you trusting the optimizer with? To make every query perfect? You know, it ain’t gonna happen.

You know, it’s, you know, the SQL Server has a fantastic query optimizer but, you know, nothing’s perfect and not all modeling is perfect. You know, models are models, right?

They’re not, they are not like exactitudes. They are not, they are not certainties. They are just models that we try to make predictions about and SQL Server uses its various cost modeling and cardinality estimation to try to, try to forecast what the best query plan for your query will be.

But, you know, it, like, maybe not everything makes it, is there for it to make a correct decision. You know? So, when should you intervene manually?

Like, via, like, query hints or index hints or other things like that? Well, that’s, that’s probably last along the way. Before, well before that, there are all sorts of things you can do to make your queries a bit happier.

You know, materialization with a temp table, that’s, that’s a good one. Making sure that your indexes are aligned to the goals of your query, that’s another one. Making sure that you have adequate constraints, whether they be uniqueness constraints or foreign keys or domain constraints to let SQL Server know values that can exist in a place.

You know, there’s all sorts of things that you can and should do. You know, and, that’s just from like the, the database side, from the query side, you know, writing sorgable queries, you know, again, breaking up complex queries.

There’s all sorts of things you can do, but there would also potentially be a manual intervention that, that are not just, you know, dirty, dirty tricks with hints of, of various states of brittleness.

So, you know, when should you intervene? When, when, when things start getting bad. If, if, if you need help figuring out interventions, I, my rates are reasonable, as always.

Don’t forget that. All right, the last question. Of course, this is, why does the same query sometimes run in milliseconds and sometimes in minutes?

My friend, you have asked the most biblical of questions. I could, I could go on and on for days and hours on this one.

Most, most commonly, you, you are dealing with an issue of parameter sensitivity. That, that’s a very, that’s a, probably the most common reason. There are, there are other reasons though that might lead to variable performance within queries in your workload.

Locking would be one of them. Remember SQL servers, default, up isolation level for databases is read committed in which read queries will wait for locks from write queries and get blocked and all sorts of, might, might, might take minutes for all those locks to release.

Another thing might be a buffer, buffer pool availability of your data. I mean, if you have to read lots and lots of data into the buffer, into the buffer pool from disk, well, yeah, that can certainly introduce a lot of query variability.

All right? well, but, but most, most likely it is an issue of parameter sensitivity and once again, if you need help figuring that out, I’m, I’m here.

I’m like, like the Maytag man just hanging out with my glass of milk, my pencil, my, my, my fancy hat waiting to help people. Anyway, that’s five questions I think, right?

I’m gonna make sure one, two, three, four, five. It’s probably about good for today. So, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I’ll see you in tomorrow’s video where we are gonna talk more about my, my, my fantastic tool that monitors your SQL Server’s performance.

So, I will see you there. All right. Goodbye.

 

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Free SQL Server Performance Monitoring: Lite Dashboard Overview

Posted on by Erik Darling

Free SQL Server Performance Monitoring: Lite Dashboard Overview

Summary

In this video, I delve into the exciting world of SQL Server monitoring tools, focusing on the Lite version of my free open-source tool, which is a lightweight and secure solution for monitoring performance across multiple servers. I walk through how to download and set up the Lite edition, highlighting its unique features such as no server-side installations or databases, making it an ideal choice for environments like Azure SQL Database where agent jobs are used. The video covers the tool’s 20 collectors that run with minimal permissions, ensuring data collection is both efficient and secure. I also showcase the various tabs within the dashboard, including weight stats, query trends, CPU and memory usage graphs, blocking information, and performance monitor counters, all designed to provide a comprehensive view of SQL Server health without any heavy lifting on the server side.

Chapters

Full Transcript

Erik Darling here with Darling Data. And boy, I just keep getting more and more excited about this here monitoring tool journey that we’re on together. In case you have missed all of the other stuff around this, I released a free open source SQL Server monitoring tool that makes it very easy for you to get up started monitoring performance on your SQL servers. The Lite version, which is what we’re going to talk about, today is like my second child, you know, the full dashboard that I like originally started working on, had some limitations to it and also presented some things about it that would probably cause some friction in people using it. So the main limitation is not being able to support Azure SQL database because it creates a database and logs data to it using agent jobs. If that’s too much for you, that’s fine. This is what the Lite version is for. So that was like sort of the friction point in and the missing little bit of supportability in there. But the Lite version, if you head to code.erikdarling.com, I’ll show you that URL in a moment. All you have to do is download the zip for it, extract it to single executable with an embedded DuckDB database. I am an unabashed DuckDB fanboy. I think it’s about one of the coolest things to happen in databases in a long time. And so it just starts running a bunch of data collection, pulling into the DuckDB database and then showing it to you in the graphs. So you just extract it, point it to a server, hit connect. And if you trust SQL Server Management Studio or Azure SQL DevOps Operations Manager, whatever, that’s getting retired this month to connect to SQL Server, then you can trust my tools to connect to SQL Server, because they all use Windows Credential Manager. They do not store passwords and usernames and files or anywhere stupid. It’s all stored in Windows. So there’s nothing dumb happening, right? This is a very, very big deal to me. This is not some like half butted effort anyway. But then, you know, so try not to mess anything up from a security point of view. The Lite dashboard requires very minimal permissions. It just really needs like view server performance state, or if that’s not available, view server state. A max of seven servers will collect at one time. So if you have more than seven servers that you’re monitoring with it, seven will go get their stuff and the next seven will go get their stuff.

And they all have a timeout on them. Now, like I just redid the data collection for this. So that if you have servers that are timing out, they will not hinder collectors that are able to run. Before I had done something kind of stupid, and they would keep trying the servers, even though like some of the is this server online queries were failing. So that’s fixed now. So got less stupid. But the cool thing about the Lite edition, which is also sort of one of the drawbacks of it is there’s nothing installed on the server, there’s no database, no agent jobs. But that means if the app is not open and running, it’s not actively collecting data, right? So if you close the app, and you go away for two weeks, and you come back, and you’re like, what’s wrong with SQL Server, there’s not going to be anything in there for you do have to have it running somewhere to collect the stuff. But we’re going to talk through what the what it looks like, kind of how it works, the different things that it shows. And these are the 20 collectors that it runs. So it’s a lot of stuff. And we run the run these as much as possible. So that I make sure that you have the most up to date data. This is all very lightweight stuff. I’ll show you that I’ll show you that in a moment. And I’ll show you a couple areas where I’m working on improvements to. So again, if you want to check this out, I’ll show you a little bit more now. So this thing is currently running and collecting data. I started off around about an hour or so ago, and I spun up my hammer DB workload to make sure that we had some stuff to look at, and it wasn’t all boring. So this is the weight stats, and the weight stats will show you the top 20 weights. Really, it’s the top 10 weights, along with like the sort of the usual suspect weights, SOS scheduler yield, the CX weights, page latch weights, stuff like that, along with the poison weights to you see that over in this this part here, like I have thread pool, and I have like some other stuff that you would like normally see when things are like bad on a server. But like, like, I want to make sure that we represent like what’s actually happening on the server and also call out some of the scarier stuff if that’s, if that’s, if that’s happening to sometimes it’s good to see when that’s not happening. Over in the queries tab, we have some performance trends, right? So these are graphs that tell you like what was going on in a server, the query store one looks a little flat, because I didn’t turn on query store in the TPC databases that the workloads running in. So that one looks a little dull. But all the rest of these are very interesting. This is query executions, that’s procedure duration, that’s query duration. And over here, you can see queries that are actively running over here. And one thing that’s like you can, you can sort this data like any way you want, right? So like if you wanted to sort by like, I don’t know, like CPU, right, you can change the way that the data is sorted and get stuff that has the highest CPU.

And you can also filter. So if you wanted to get rid of stuff that like is greater than zero CPU, you can do that too. You have queries by duration here, that’s the default view. But you know, you can switch all these to order by whatever other thing you want. You can filter all these, these are store procedures. And this is all the stuff from query store. Now what’s neat about all of the query grids is something that you can do if you want to troubleshoot a slow query. You can right click on it. And you can hit copy repro script. And then you can go over to SSMS and paste it in. Now, this isn’t the greatest example, because there are some local variables in here that like we don’t have the data types and stuff of, but we do have the execution parameter that got used in here for the where clause, right? So like the DW, whatever ID, we would just have to figure out like, okay, what’s the data type is just run this query, right? So it does warn you about that up at the top too, right? So like, but other than that, it’ll give you everything you need to reproduce a query just copying and pasting it.

Now, coming back to the dashboard. Oh, wait, is that the right one? Yeah, it’s the right one. Okay, good. Not losing my mind. We also have some individual graphs for things like CPU memory usage. There are lines here. There’s also some information up at the top about like plan cache buffer pool, other things like that. Um, you know, like how much physical memory the SQL Server has, how much is currently using, um, you know, this, you know, like some nice stuff to sort of just see visually and not just have to like, oh, what is this thing? Um, one thing I actually forgot to mention is you can hover over any graph line you see and get how like the, the, uh, data, the data point from down to the legend that it represents and like some other stuff about it. Right. So, uh, we got information about 10 dB. This is like a current, like space usage, like what’s using a lot of space in there. So you can see if it’s user objects, internal objects or version store. And then you can, uh, down here, we have 10 dB, uh, file latency. So you can see exactly which files are spiking up when, right? And every once in a while you get lucky and there’s a spike and like what was using stuff and you know, what was, what was going on in there. Um, over in the blocking tab, uh, there’s not nothing going on for blocking right now, but we do.

I do have deadlocked. In fact, the deadlock alert just popped up and made my green screen angry. Uh, but we have some trends over here, which will tell us like, like about deadlocks that are happening and, uh, blocking happening. And then, you know, you, you get like the, the fully parsed out deadlock graph over here. So I can just make, put all much information in front of you at one point as you can, um, over here, we have perfmon counters. So there’s by default, I show batch requests, optimizations, compilations, and recompilations.

So you like, that’s usually the stuff that I eyeball first on a server. So, but you can add in, you can click in any one of these and add a new line to it. Like we just added in lock, wait time. And because there’s a bunch of deadlocking and stuff on here, uh, we get a whole new spiky thing going on in the graph. Um, we also look at running agent jobs. Uh, this one is actually seeing the performance, the full performance monitor database agent job running and doing stuff.

So we’ve got that going on, uh, in the configuration tab, you can see if anyone has changed trace flags, um, you get, uh, uh, database scope configurations, um, like, uh, database configuration stuff from sys.databases. This needs some more columns in it. I’m going to make a note about that. And then the general server configuration as well. Um, and then over here we see collection health where we can see the, like a summary of things that are running, how long they take on average, uh, a full log of everything.

So if you wanted to like, if you saw something like had, um, you saw that something had some number of errors in this column, you could come over here and see when it, when, when those errors happened. Uh, you could just filter down to the error message. And then over here are duration trends. So, um, you know, because I care about performance, um, like I am working on some of these, uh, sometimes they take a little bit longer, especially like the XML parsing ones. You have a lot of deadlocks or a lot of blocking or like, um, anything else that deals with XML and SQL Server. Sometimes it takes a little bit longer, especially when the server is under a lot of load from a hammer DB test.

So, uh, I clearly have some work to do, which means I’m going to end this video here and go figure out which one of these jobs I need to go have a talk to. Anyway, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. Uh, again, this is all available for free at code.erikdarling.com. Uh, you should go try it out, start monitoring the performance of your SQL servers and have a good time. All right, cool. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Free SQL Server Performance Monitoring: How To Talk To Your Performance Data With The MCP Server

Posted on by Erik Darling

Free SQL Server Performance Monitoring: How To Talk To Your Performance Data With The MCP Server

Summary

In this video, I delve into the built-in MCP server feature of my FreeSQL server monitoring tool, Darling Data. This feature allows users to interact with their collected monitoring data in a conversational manner using language models like Claude or other compatible tools. The MCP server is designed for local use only and does not expose any data beyond what’s available within the performance monitor database, ensuring that no sensitive information is at risk. By enabling this feature, you can ask questions about your server’s performance directly to an LLM, receiving quick insights into issues such as deadlocks, query performance, and workload spikes. This approach simplifies the process of diagnosing problems without requiring extensive manual analysis or script writing.

Chapters

Full Transcript

Erik Darling here with Darling Data. And in this video, I want to go over a little bit about using the built-in MCP server. That is a model context protocol server available in my FreeSQL server monitoring tool that allows you to have a little bit of a chat with your collected monitoring data. It’s not allowed to go out and start running random ad hoc DMV queries. It’s not allowed to, you know, you know, drop, delete, update, mess up, merge, merge anything. It’s not, it’s very tightly bound to the tools that it has defined and available to it and nothing else. Right? So, uh, I wanted to have this in there for people who are maybe not fully comfortable looking through monitoring tools, uh, maybe not fully comfortable, um, you know, like digging into things, figuring out what was actually wrong and when, uh, and, uh, you know, just maybe people who are maybe a little bit more geared towards, you know, like, or gotten used to over the last couple few years, like having chats with LLMs about things and figuring things out with them. Right? Cause I mean, they’re not always right the first time. Sometimes you get a little poking and prodding and you got to, you know, nudge them a little bit, but you can usually get there. Right? So this feature is opt-in. Um, it’s only running locally. It is not like some big broadcast web server out there in the world. Uh, it’s just running as a local host on your machine. You have to turn it on. Right? Again, I don’t want to alienate anyone who hates the robots. Uh, but if you, uh, find yourself using robots more and more in your day to day work, you can use this here. Um, and then you can start talking to your performance data with the tools that I’ve made available to, um, to, uh, basically expose the tables and views. Uh, if you’re on the full version in the performance monitor database, so only that database. And if you’re on the light version, it’s just talking to the local DuckDB database where all the data got collected. So you have nice sort of like, like pre arranged time series data, the, the model knows the schema and everything, and it can just talk directly to that. And it can look at everything in there and it can tell you like, you know, some pretty neat things about it. There are a whole bunch of tools available for, uh, different things like discovery, weight, CPU, queries, memory blocking. There’s a whole bunch of stuff. There’s like 30 or something tools available to it across all the different data collectors. So you have a lot that you can look at and dig into. And what you have to do, and I’ll, I’ll show you what this looks like is you have to, um, enable the MCP server, right? So again, not the DOM by default, you turn it on. Uh, and then depending on which version you’re on, the port might be different, but it’s unimportant, unimportant ports. Uh, and then you have to go into the LLM of your choice. Um, you know, we are a strong Claude household. We’re a Diet Coke, Claude, um, Lagavulin, like we have strong allegiances. Uh, so this is what we’re doing. If you use something else, as long as the LLM that you use is, um, MCP compatible, it’s fine. Right? And then you just start asking questions about, what’s been going on with your servers. So you, and you can ask it a lot of just normal conversational questions. Like if you like, instead of having to like, you know, like someone’s like, Oh, the server was slow yesterday. And you’re like, ah, you’re going to go look at yesterday. What the, come on, give me a break yesterday. You’re telling me today, like, if you were to go like do this on your own, it might take you five or more minutes to, you know, like, you know, either like, like, let’s say you’re lucky enough to have a free open source monitoring tool. It might take you five or more minutes to like go through all the charts and graphs and dig into stuff and take screenshots and make notes and pull things out and go like, you know, the normal sort of like outlining of things. But with Claude, you can ask it pretty quickly and get some pretty fast answers. It’s maybe not like, you know, as fast as you would like it, but it’s still reasonably quick. Uh, if you want to check this out, go to code.erikdarling.com.

It’s the performance monitor repo. Uh, and again, totally free, totally open source. If your company does want to, um, get support or some contract guarantees with that, I have things available at training.eric.com to, um, to do that. So let’s go look, uh, real quick at, um, first let’s look at the, uh, the settings that I’m talking about. So, um, if you want to look at that, uh, like I already have mine turned on. So this is the MCP server. Um, and if you go in here and you’re using Claude, you can copy the setup command right from here. It tells you like, just like, just the, gives you the little one line command to add the MCP tool. If you use a different LLM and you want to like include like the, the, the correct command to do it with whatever you use, you can submit it to the documentation. I just don’t use anything else. So that’s how you set that up there. And I’ve pre-baked a little bit of a, um, conversation with our dear friend Claude, uh, via the MCP server because, um, you know, like it’s not really a lot of fun sitting around like, like watching an LLM, like spin its gears with stuff. You get bored. You’ll look at something else for a little while, right? Oh, I don’t know. Gamble on, gamble online or something. So no, I just started off like, Hey Claude, can you see my MCP server? It was like, yeah, I see that. Look at that. It’s connected and working. You have seven monitor servers, but only SQL 2022 is available. That’s fine. All right. And then I asked Todd, Claude, not Todd. I don’t know who Todd is. Todd is not cool. Can you list all the tools? Right. Let’s tell me what you have. And then it, so we have a list here of all of the available tools that it has to look again, just at your performance data, not at anything else, right. Just local to that. Um, so don’t, don’t think that I’m sending queries off to like other databases and collecting things. And I’m going to, you know, I don’t know, Ashley Madison, you or something, but these are all the tools that we have out there. And, uh, you know, like I, this is just me, like mostly hitting tab, uh, because there’s like autocomplete stuff where it’s like, well, what, what, what, what’s it, what should I do here? And so I just said, yeah, run a health check on SQL 22, 2022. And it went, cause I’d run a hammer DB TPCH workload on, on SQL 22. You might see some fun spikes and stuff in there, uh, where the queries are running. And it went through a whole bunch of the tools and it said, well, you know, right now things are normal. Um, I ran through, um, all sorts of things and, you know, your server was idle most of the day, but you did have a big spike in there for 33 minutes. 33 minutes is exactly how long my TPCH workloads for, right. And it even says, it looks like a hammer DB, right? It’s like, it knows. It’s like, I got you, buddy.

All right. Uh, it’ll tell you about weight stats. Uh, you know, it says, wow, yeah, 2009 deadlocks today. That’s, that’s a lot. You might want to think about that. All right. It’ll give you a breakdown of how, like what memory looks like file IO running jobs. Um, so, you know, we’re all good there. And then, um, it’s like, you know, there’s some things that you might want to think about, right? Like 2009 deadlocks, 32, 33 minute spike, but you know, whatever. And I was like, Hey, yeah, you know, Claude, um, well, where was it? Uh, there was, uh, there was another prompt.

What was the longest running query in the hammer DB workload? And it comes back with some information in here, right? It’s like, it was a TPCH, TPCH workload, right? Tells you this was the longest running query. It gives you information about all the stuff that we have collected about the query, right? Like all this stuff that make you, you know, you would have to go and look at and like, wait for a tool tip. It’s all, it’s all a nightmare. Right. Uh, and then like, you know, say, okay, well, you know, that’s cool. I can, I can go, I’ll, I’ll make a note of that. I’ll go work on, uh, query 21 later. And then we can say, well, you know, this is the next one. Dig into those 2000 deadlocks. And it did, um, I had a little boo-boo there with some JSON, but that’s okay. And then, um, you know, it tells you what happened, right? And so all 2009 deadlocks are from the TPCC workload, right? And it tells you all the stuff that was going on there and the two deadlocks that were, uh, that were the two queries that were involved in the deadlock, right? So new order and, uh, execute, yeah, new, well, new order and delivery. Huh? See, we like new order too. It’s a good band, right? Uh, so they take some X locks on these and delivery takes some X locks on these and they acquire page level locks in the opposite order, right? And so we just get all this information.

We can say, cool, I can take that information and I can do something actionable with it, right? So, uh, this is just a sort of basic example of, um, you know, what you can do with the MCP servers that are built into my monitoring tools so that you can do sort of performance investigations without breaking a sweat, running a bunch of scripts, like gathering a bunch of query plans, query texts, looking at like 50 million different metrics, you know, trying to corroborate a whole bunch of stuff together. Uh, and, uh, you know, again, if you’re uncomfortable, you know, doing that sort of thing, or even if you’re uncomfortable, just looking at charts and graphs and figuring out when things were crappy, this is a very, very easy way to just talk to your data and get some answers about it. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I hope that you will try out my wonderful, uh, free open source SQL Server monitoring tool.

Again, that is at code.erikdarling.com. Uh, so I would encourage you to head over there and check that out. All right. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Free SQL Server Performance Monitoring: Which Version Is Right For You?

Posted on by Erik Darling

Free SQL Server Performance Monitoring: Which Version Is Right For You?

Summary

In this video, I delve into my new, completely free, open-source SQL Server monitoring tool, discussing which edition—full or light—you might find most useful based on your specific needs. The full edition creates a database and agent jobs to continuously collect data, offering complete control over the collection schedule and allowing for easy customization of stored procedures. It’s ideal for scenarios where you need constant data collection and want the flexibility to manage the monitoring tool as needed. On the other hand, the light edition uses an embedded DuckDB version inside itself, collecting data only when it’s open, making it perfect for quick triage or situations with limited server access, ensuring that no data is missed while you’re away from your computer. Both editions offer alerts and notifications for critical issues like blocking and high CPU usage, providing a seamless experience regardless of which edition you choose.

Chapters

  • *00:00:00* – Introduction
  • *00:06:28* – Features and Capabilities
  • *00:10:37* – Setup and Installation Process
  • *00:13:59* – Platform Support and Configuration <– Thanks, AI

Full Transcript

Erik Darling here with Darling Data, and we’re going to continue talking a little bit more about my new, completely free, open-source SQL Server monitoring tool, and we’re going to spend some time in this video talking about which edition of the monitoring tool you might want to use, because there are two of them. They have two somewhat different design philosophies and two somewhat different, I think, usage patterns, it’s important to figure out which one you might find the most useful. So, there’s a full edition, which I believe I talked about in the last video a little bit, it creates a database on your server. It creates some agent jobs on your server, and it starts pulling data into that database on a schedule. You have complete control of the schedule. You can change how frequently it runs, you can change whether things run or not, and even if there’s, you know, like, you want to change one of the stored procedures, you can do that, right? You can, like, the code is right there, it’s not encrypted or anything, nothing’s hidden from you. Again, completely naked and vulnerable to the world. There are two ways to install it. There is a command line installation process, and there is a GUI front-end installation process, whichever one you find easier, or if you, I mean, if you need to do something programmatic to a bunch of servers, then the command line is probably for you.

It spins up, like I said, some agent jobs, three of them. One of them is to do data collection, one of them manages data retention, and the other one, like, goes and looks for if the agent job, either of our agent jobs is hung, and knocks them out so that nothing, nothing weird happens, right? Because it’s important to monitor the monitoring tool. But then, what you do is you open up a dashboard, and you point it to the server, and the dashboard finds the performance monitor database, and it goes and starts pulling data from it. So, it’s pretty easy there, and there’s pretty standard monitoring stuff. There’s a bunch of tabs, and, you know, you look through them and make a bunch of wise decisions.

The light edition is a little bit different. The light edition does not create a database on your server. The light edition uses an embedded sort of DuckDB version inside of itself and starts pulling data in. This is where things start to differentiate a little bit. The full edition is going to be constantly, via the agent jobs, pulling in data. The light edition only pulls in data when it’s open. So, if you close down, if you close light edition, if you, you know, go on vacation, shut your computer down, just kidding, who, no one does either of those things.

It will not be collecting data while you’re away. Probably, if you, you know, don’t want anything weird happening, you might want to stick, if you, and you, like, if you’re, like, depending on, like, what sort of access you have, you might just want to stick light edition on a jump box or something and have it constantly pulling stuff in, and that way anyone can go in and look at the dashboard when they want. I mean, you could do that with full edition, too, but, you know, some other stuff, you know, like I talked about in yesterday’s video, you know, when I was first designing the full edition of this, I did sort of picture scenarios where someone would want to, you know, maybe have the monitoring database, and they would, they might want to, like, just, you know, back it up, zip it up, send it to a, send it to someone for analysis without having to give them full access to the server, the whole other thing, right?

It’s a fairly easy thing to do, and, you know, if you’re, if you’re collecting data for, like, a week or a few days or something, there’s not going to be a huge database backup to, you know, manage and install. It’s not like a multi-terabyte database or anything. We do store a reasonable amount of data in there. By default, it’s up to 30 days, but you can manage that as well. If you want more data, less data, you can change the retention policies.

Right. So, which edition kind of fits your needs is going to depend on how you use things and what your level of access is and probably what your job title is. You know, like if, like, you know, like the full edition, if you want, like, you know, like, guaranteed that it’s always going to be up there collecting data, then, you know, you probably want the agent jobs running collecting stuff. Light edition, if, you know, anything happens to it, if, like, you know, you lose network connectivity, you shut your computer down, Windows update kicks in and ruins your life, you’re going to have gaps in collection.

If you want to do a really quick triage, the light edition is probably better for you because it just, like, you can just open it, point it at a server, it’ll start immediately pulling in data. It does make an attempt to pull in, like, a bit of historical stuff to, like, populate some of the dashboards, but, like, not everything in SQL Server plays nicely with that.

Wait stats and other perfmon counters, other things like that, they’re, like, aggregate since the server started up. So, you know, if you need to support Azure SQL database, then the light edition is the only one that does that because, you know, Azure SQL DB only supports one database, so we couldn’t create a monitoring database and start pulling in data.

So, you know, depending on your level of access to the server and what you’re allowed to create and do there, the full edition might just not be possible for you, like, depending on, like, if you’re, like, if you’re not allowed to, you know, like, create agent jobs without, like, you know, compliance and all the other stuff getting involved or whatever other change management stuff going in, light edition’s a little bit easier to sneak in and say, oh, I don’t know, I’m just checking out some stuff here, let’s run an SP, who is active.

So, you know, you know, like, for the, like, consultants, though, like, people out there who might be interested in using this, you know, like, it is, I think the full edition is useful for a lot of reasons, like, you know, again, it’s always collecting data. Someone can take a backup and send you the backup.

You can run analysis on whatever, like, whatever data is in there without someone having to give you full access to the server, schedule a call, whatever it is. Light edition, you know, if you, you know, like, like, are getting started with a client or, like, you know, like, I don’t know, like, let’s say you’re me and you sign a client and then you’re like, well, you know, we can have our first call in, like, you know, three, four days or something, why don’t you get this running, collecting some data so we have stuff, like, good stuff to look at when we kick things off.

Like, I don’t have to sit there and, like, you know, run a bunch of scripts and collect stuff and, you know, dig through things and be like, oh, well, these wait stats, oh, the server’s been up for 2,000 hours. Well, I don’t know how, I don’t know which of these wait stats is relevant anymore. That really bad one could have happened 1,900 hours ago and never again, right?

Like, it’s just a nice way to sort of get some preamble data before you start talking to someone. We do also provide some alerts and notifications. So for stuff like blocking, deadlocks, high CPU, connection changes, stuff like that, like connection changes, meaning, like, server being unreachable, important stuff, kind of.

Those do generate notifications. And the notification thresholds are all configurable as well. It’s not like, you know, like everything in here, I try to make it so that you can customize it to make sense to you as much as I can.

The only thing that would not be easy there would be managing two separate presentation modes. Right now, the dashboard is all dark mode. Light mode hurt my eyes while I was making it.

It’s a lot of late nights on this. So, like, it will fire off notifications. And there are a few different ways to get the notifications. There’s this, like, you get system tray pop-ups when stuff happens.

And you can set up SMTP to send you emails. I tested that with a weird, like, dev SMTP thing. And it worked wonderfully.

If you, you know, again, if you hit any problems, just, you know, open an issue on GitHub. I’ll get to it as soon as I can. And then the other neat thing here is that you can just, you can either acknowledge or silence notifications. So, like, if, you know, you, if you’re, like, you know, looking at the server and you’re, like, well, there’s 70 deadlocks.

I’m, okay, I saw those. You can just click acknowledge on them. If there’s a specific notification you don’t care about, you can just silence it. You can silence notifications for an entire server.

Like, whatever works for you. And then also both of them include MCP server analysis. Now, something that seems to be confusing to people is in my setup stuff, like, I use Claude. And so the setup instructions that I have to add an MCP server are geared towards that.

You can use it with any LL. You can use it with whatever you want that supports MCP servers. So it’s not limited to Claude.

You can use whatever you want with it. It just really is, like, I use Claude specifically. So that’s what I geared stuff towards, right? There’s nothing specific about an MCP server or, like, the descriptions or the tools in here that are Claude only. You can use whatever you want with it.

And the cool thing here is that, like, as far as security goes, it’s not like, you know, it just binds to your local host. So, you know, you’re not sending your data all over the place. You’re asking specific questions about just the data that has been collected by the monitoring tool.

Like, it can see database and table names and stuff because that’ll be in various collection things and, like, see server names and stuff because that’s what you’re connecting to. But, like, it’s not going out to your user databases. It does not have permissions or privileges beyond the collected data set.

So nothing outside of that gets touched, looked at. It’s just collected performance data. And that’s nice, too, because it really helps to narrow the focus of what goes on in there. It all works off tools that are specific to the tables and schema that I have and some views that I have set up in there.

So it’s not like, you know, it has to go and crunch numbers itself. Like, everything is pretty well laid out. So you can ask, you know, you can ask questions of your collected performance data and make life very easy for doing analysis because you’re not, like, again, if you’re not the type of person who does a lot of performance monitoring regularly or even if you are and you just want to get quick answers without looking at, you know, a bunch of dashboards and everything else.

Like, if you want to, like, sort of get a, like, like, you want to get a story to tell before you start looking through things, you know, it’s very easy to do that, right? So that’s probably about good here. If you want to check, oh, I guess there’s a little bit of platform support stuff to talk about.

All this stuff is somewhat configurable. You know, it’s a little annoying that on RDS, if you want to use the block process report, that’s outside of my control. You need to use a parameter group for that.

Azure SQL DB is fixed at 20 seconds. But for on-prem in Azure managed instance, I will auto-configure the block process report stuff for you, run the SP configure stuff, create the extended events to read the block process and deadlock reports. So, like, I do as much setup as I can, but that’s one thing that kind of sticks out.

So, like, the default trace stuff, like, if you’re, if you have it turned on, it’ll read from it. If you don’t, it’ll just say it’s not turned on. There’s also a specific trace that I use.

Like, one thing that I really liked about SQL Sentry back when it was good was that it used trace to find stuff. So, you can get, like, some, like, good performance metrics pulled out of that. And so, you know, I use that.

So, I have something similar set up in mind to give you a good experience in that realm. But, you know, pretty, pretty simple stuff there. Really, the only, really the big thing is that the old, like, Azure SQL database that’s only supported by the Lite version, again, because of the database creation thing.

So, if you want to check that out, go to code.erikdarling.com, and you can download it for free. If you want to contribute to the project, it is open source. If you want to support it as an open source project, that is available to you.

And if your company requires some sort of support contract or you need to have some other vendor validation stuff before you run software in your environment, the purchase terms for that are at training.erikdarling.com under the monitoring header. So, just head over there if you require additional stuff before you get going with an open source project. Anyway, thank you for watching.

Hope you learned something. Hope you enjoyed yourselves. I hope you try out this free open source SQL Server Monitoring Tool, and I will see you in tomorrow’s video where we will dig a little bit deeper into some of the inner workings of it. All right.

Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Free SQL Server Performance Monitoring: Introduction

Posted on by Erik Darling

Free SQL Server Performance Monitoring: Introduction

Summary

In this video, I introduce my new free performance monitoring tool for SQL Server, which was developed after a bit of boredom during the holidays and a desire to create something simple yet powerful. The main issue I’ve noticed is that many existing tools are either overly complex or too expensive, making them less accessible to smaller teams or individuals. My goal with this tool was to provide a straightforward solution that doesn’t require setting up VMs, dealing with firewalls, or managing domain accounts—essentially streamlining the process for anyone looking to monitor SQL Server performance without the hassle of a full-blown enterprise setup. The tool supports various editions of SQL Server and even Azure SQL DB, offering both a full version with database logging and an easier-to-use light version that’s self-contained within DuckDB. I also highlight how it integrates machine learning models for those who prefer not to dive deep into dashboards, providing quick answers through natural language queries.

Chapters

  • *00:00:00* – Introduction and Announcement
  • *00:09:38* – Alert History Tab
  • *00:10:41* – Dashboard Overview
  • *00:11:06* – Query Execution Metrics
  • *00:12:11* – Performance Metrics and Graphs
  • *00:13:13* – How to Check Out the Tool
  • *00:13:41* – Next Steps and Conclusion

Full Transcript

Erik Darling here with Darling Data and in today’s video we are going to talk a little bit about my new free performance monitoring tool for SQL Server. It’s something that I started cooking up a little bit after Thanksgiving because it was the holidays and I was bored and I wanted to take advantage of a little bit of like sort of supplementing what I can do with what, you know, our new robot friends can do. For a long time, you know, I had like this idea in my head but I was not able to talk anyone who knows about like C Sharp or WPF or any like any of the other stuff into working on something totally for free. The good news is, Claude will do lots of stuff for about 200 bucks a month. So that’s what I put Claude to work on. Now, I have a bunch of videos and I’m going to go over a lot of like the features, usability stuff, like what it monitors, what it grabs, how it works, stuff like that. But today’s video, I just want to give this like a quick intro overview, stuff like that. I’ll just kind of tell you why I decided to do all this stuff. So the problem that I saw a lot working with clients is that like either like you had enterprise monitoring tools that were very expensive and then you had a lot of like free or like free or lower price monitoring tools that still had like all the sort of complexity of use and installation that the enterprise tools had. And a lot of people just like they’re not like they don’t want to set up a VM with a SQL Server or whatever on it and start like, you know, like setting up like domain accounts and dealing with firewalls and getting everything connected and like pulling data over. And so I just wanted something that was pretty simple. Now, like granted, like what I started out with was sort of like a full flick thing, right? Like not like enterprise install thing, but like, you know, like create a database, start logging data to it, have the dashboard pull from that. And then sort of like midway through that, I was like, wait a minute, I need I want to have something like a little bit simpler to for other people. But we’re going to talk about all that stuff.

So the thing with my monitoring solution is it’s totally free. I do offer sort of support tiers, paid, like not paid versions, but just like paid support contracts for it. If your company needs that for compliance reasons or whatever. That’s all available via training.erikdarling.com. You can check that out over there. It supports SQL Server 2016 to 2025, Azure SQL Managed Instance, Azure SQL DB and AWS RDS. Only the lite version supports Azure SQL DB though, because this one doesn’t have a create database dependency alongside it. So like I brought a lot of sort of the stuff that I care about that I monitor that I look at into this monitoring tool, which is something that like I felt a lot of other monitoring tools were kind of missing. Most of people, most monitoring tool shops do not have like performance people who are like doing that stuff every day working on those products.

And you miss a lot of the sort of like important stuff that people who do performance tuning work would care about and want to look at. So that’s kind of why I, I wanted to do this. Um, you have two additions, you have a full edition, um, which, you know, like creates a database on a server and some agent job that then starts logging via the agent jobs to that database, which is, I know not ideal for everyone. Um, it is like as a very specific purpose, like in my world, in my life, where, um, where, um, you know, if someone who I’m working with is having like performance problems and they can’t give me like remote access to the full server, whenever I want it, we have this thing running over there and you know, well, they don’t have to give me access to the full server. They can take a backup of the performance database, share it with me. And I can look through the data in there. So that gives, that gives me like, you know, something helpful, which is kind of why I started with that one there, but also just like, you know, like, like, like having that dashboard available, you know, that dashboard can turn on a jump box somewhere, connect to just that database. And I can go in and review stuff in the dashboard too. So for people where there’s like, you know, some additional layer between like what I can do remotely and, um, like what they’re allowed to give me access to, that’s really helpful.

Then like, like I said, like midway through doing that one, I was like, well, I’d also like to support Azure SQL database and, you know, um, maybe make something a little bit simpler that doesn’t create a database dependency. And, you know, I have this, this absolute love for the DuckDB database. And so I was just like, Hey, let’s build off what we’ve already done for the full one. And let’s just create like a self-contained monitoring tool. Right. We use DuckDB because it’s real fast, right? Like, you know, like before we could use, end up using like SQL light, if you wanted an embedded type database music, DuckDB, which is insanely fast for both the inserts that we need to do from the data collection on the SQL Server.

And also like the queries that run internally and it’s like made for like, you know, reporting queries made for analytics. So then just made total sense to like use that instead. There’s also kind of a neat thing in there where, um, both of these, uh, both of these monitoring tools have built into them. You can enable it. It’s disabled by default, right? And I didn’t want to like, I’m not forcing AI or LLMs on anyone, but if you just want to like, if you’re not the type of person who like is good at looking through dashboards, is good at digging through data, it’s good at identifying problems and stuff, there are MCP servers and the MCP servers allow you to have what the LLM of your choice.

I use cloud. I have a strong cloud preference. You can use whatever like LLM you want to just hook into those MCP servers and ask questions about what data they’ve gathered. So you don’t have to give them access to your entire SQL Server and all your databases and all your schema and like all the crazy stuff that can live out there. They can only like the MCP server for the full full edition will only talk to data in the performance monitor database, right?

It has very specifically crafted read tools to only touch that data. You can’t run ad hoc queries with it. And it’s all set up to work with the data model in the performance monitor database. The light version is even further detached because that is only talking to the DuckDB database where all the data gets pulled into. So you can use an LLM and you can just say, hey, someone was complaining yesterday at noon about something. Tell me what was going on.

And it will go through all the data and be like, hey, well, we found this blocking problem. This query was running for a long time. All the other stuff. All the other stuff. You can also get alerts from both monitoring tools. They will show up as system tray alerts. And also you can also set up an SMTP server. It’s built in. So you can you can send yourself emails to with with with alerts that come up in there.

Right now it’s like CPU blocking deadlocks, a few other things, but like, you know, stuff that like, you know, you would maybe want to get a pretty quick notification about if it was going on. So like I said, we pretty much support all things with both of them. But light is the only one that supports Azure SQL database because the full version does have does create a database and start logging data to it and uses agent jobs.

And the story for that with Azure SQL DB is impossible to tell. Right. Because I don’t want to like start creating tables in the one database you’re allowed and like go through elastic jobs and the rest of that nightmare. So there’s a light version that just sucks data out.

The requirement permissions for the install and creating the agent jobs are a little higher priv than I wanted to do. But in general, all you need is like view server performance state to pull out the data and start logging to it after that. So to install, you need a little bit more. But the general data collection, you don’t need much.

And then for the light edition is just either view server state or view server performance state if if you have that one available. If you want to check it out, go to code.erikdarling.com. It’s the performance monitor repo on there. It’s doing pretty well so far.

I have over a thousand downloads, a bunch of stars and forks and a pretty active community of people submitting people submitting issues, stuff getting worked on fixed. So very, very excited about this. I don’t want to just talk about it, though. I do want to show show you a little bit of it.

So when you first open up the this is the full version, the light version is being worked on at current. And I just want to use this one. But this one, like when you open it up, you get an overview of all of the servers you’ve connected to it. You see, I have a bunch of servers offline here because I don’t have all my VMs up and running, which is totally fine.

I don’t need them. But then I have SQL Server 2022 online and running. You also have an alert history tab. So like all the alerts that you’ve sent, you can see what time they were, what the alert was, all that stuff. It’s a good way to sort of track like when you had problems.

So if you want to like MCP and do stuff, I’m going to show you the MCP stuff in a later video. But if you go into back to the overview and you double click on any one of your servers, you get, you know, like, again, people love dashboards. And I like dashboards because it gives me a visualization of the data. Right.

And I don’t want to pretend that this thing like those dashboards like differently or better than most other monitoring tools. But if you want to just kind of like get a picture of what like, you know, the metrics on your server over time look like these things paint real pretty pictures. A lot of people like having screenshots and stuff to like like, hey, this is what was going on and they can point to things.

It’s like it’s a nice sort of visual helper. There are a whole lot of tabs in here. I’m just going to do like a high level of these at the moment.

But, you know, you get like a server overview. It’ll talk through critical issues, server configuration changes, database configuration changes, if anyone’s adding or removing trace flags, stuff like that. You also get some insight into if your collectors are running well, if any of them are having problems, stuff like that.

Query execution stuff as well. That’s a, you know, stuff that I usually spend the most time with. You know, you’ll get the full version runs basically just SP who is active to look at active queries.

The light version runs a slimmed down version of just a query that pulls them. But, you know, like there’s a lot of tool familiarity in here, right? There’s a lot of stuff that you’ll see in here that you would you would be used to seeing.

And like, you know, like like basically like if you ran like a script in SSMS, you would see very similar results. So if you have some familiarity with these things already, you’re in a pretty good place because you already know kind of what the results look like.

So we would pull from, you know, query stats, procedure stats. We pull from query store. I don’t know how many like paid monitoring tools out there are actually touching query store. But damn it.

I wrote SP quickie store. We’re hitting query store, right? Not messing around with it. You know, we go over resource metrics. You would see all sorts of like nice stuff about like how your server looked over time. We graph out weight stats for you individually, right?

It’s not like just a conglomeration of like, you know, weight stat colors and blah blahs and whatnot. You can look at weight stats by total weight time by average weight times. You can see like if you had like any like big like spiky weights by like what weights that took a long time to run versus like weights that accumulated a lot of time like in a different way.

There’s all sorts of stuff about tempdb, file.io, perfmon. You name it. It’s in there.

You know, I break down a lot of stuff about memory usage. You see memory grants. That’s not a very exciting graph at this point. But anyway, it’s like if you if there’s something about SQL Server performance that you care about, it is in there. Right.

You get I guess they don’t have any blocking going on, but at least it didn’t show up quickly enough for me to care about. But, you know, I spike out like, you know, lock weights over time specifically under blocking blocking like like how many blocking events, deadlock events, like durations for blocking and deadlocks. And then over in the system events tab, this is all data that would come from SP Health Parser.

A lot of these are going to be empty for most of you. And that’s totally fine because the more of these are empty, the better shape your server is in. Like you don’t want to see anything under corruption events.

You don’t want to see stuff under contention events. You know, we have some severe errors in here where I was killing queries just to have some one of these graphs show stuff. But, you know, there’s like there are there are things that will pop up in here.

There are things that are useful in here, but this is all from the system health extended events. So, again, if you don’t see anything in here, you’re that means something good for you. Right. It means your server is not doubling over and crying.

So anyway, just a quick introduction, sort of like an announcement video to talk about things a little bit. If you want to check this out, again, it’s code.erikdarling.com. It’s the performance monitor repo.

There’s a big readme file in there with lots of instructions and stuff. And if you have any questions, comments, you haven’t run into any problems with it, open a GitHub issue. That’s where I’m doing all the support and fixing and stuff.

So check that out. And tomorrow’s video, I’m going to go over a few more things in deeper detail about how the tool works, how to use it, things like that. So anyway, thanks for watching.

I hope you enjoyed yourselves. Please check out this nice free monitoring tool that I’m giving everyone. And that’s about it. All right. See you tomorrow.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

SQL Server Performance Office Hours Episode 53

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 53

Summary

In this video, I dive into answering five community-submitted questions about a wide range of topics related to SQL Server and more—ranging from optimizing long-running queries to migrating between different versions of SQL Server. I also share some exciting updates on my free monitoring tools, which are now available in two versions for various needs. It’s been a chilly February, but I’m looking forward to the warmer days ahead when I can rejoin the live events circuit at data tune Nashville, Data Saturday Chicago, and more. If you have any questions or want to participate in this Office Hours session, head over to the video description where you’ll find all the links to ask me directly and learn more about my consulting services and training options.

Chapters

  • *00:00:00* – Introduction and Overview
  • *00:04:27* – Monitoring Tool Explanation
  • *00:13:56* – Parameter Sniffing and Plan Stability
  • *00:28:49* – Concurrency Safe Index Maintenance Risks <– Good job, AI
  • *00:38:59* – Conclusion and Next Steps <– Good job, AI

Full Transcript

Erik Darling here with Darling Data. I’m in the mood for, well, I guess it’s Monday, so I’m in the mood for Office Hours. This is where I answer five, as long as my fingers work, five community-submitted questions about, well, you know, SQL Server, Love, Life, Fitness, Dietary Restrictions, I don’t know, about tramp stamps recently, so pretty good spread of stuff here. So, you know, just send them on in. If you want to ask me a question, if you want to partake in this circus, you can find that link down in the video description. You’ll find all sorts of wonderful, helpful links there, where you can hire me for consulting, buy my training, become a supporting member of this channel, and, like I said, ask me Office Hours questions, and, of course, you know, the magic, like, subscribe, tell a friend, we’re all happy. And in case you didn’t know, I released a free monitoring tool. I mean, actually, I mean, technically, I released two of them. There’s two versions of it, depending on what your needs are. But they’re both totally free. They are open source. They don’t, you know, you don’t need to, like, sign up via email, or they don’t phone anything home to me. It’s just a bunch of things that run, collect performance data, from SQL Server, things that you would care about, and part of a performance investigation. We got, like, a Nox style dashboard, so you can see which servers are up running, if any of them have any crazy stuff going on. And if you are AI ready, like I am, right, because, because you’ve been getting AI ready with Eric quite a bit lately, then you’ll be happy to know that there is a built in MCP server, so you can actually talk directly to your performance data. You don’t have to give the, you don’t have to give this access to, like, all of your SQL Server data, right? Like, databases and all the system, sys DMVs and stuff. It just talks to the data that it has stored, which is a nice way to have a very defined, well-defined set of data for the, to ask questions about.

So you can say things like, uh, user complained about something being slow yesterday, around noon, what was going on? And it can, it can use all the built-in read-only, no going outside the boundaries, no running weird queries, uh, by accident tools, to tell you what was going on at noon yesterday. So, uh, very excited about this. Um, it’s available at my GitHub repo. There’s a, a long link there, but, um, anyway, very exciting stuff. And, of course, you know, uh, the reason why these things can get built is because it is cold and awful outside, and I don’t want to go anywhere as soon as I walk outside and I can see my breath in the air.

I just want to, like, hide in a, uh, the, I don’t know, a couch cushion somewhere and not, never, never think about outside again. But, uh, eventually, outside will become nice and tolerable again, and I will rejoin the living. Uh, I will sprout, I will blossom from the ground, uh, and I will be at data tune Nashville, March 6th and 7th. Data Saturday, Chicago, March 13th and 14th. SQL Day, Poland, May 11th to 13th. And data Saturday, Croatia, June 12th and 13th.

And, uh, that’s going to be my, my world tour so far for the year. Uh, so I’ll be at all those places, and you should be at those places, too, because I, I will be teaching pre-cons at all of them on Advanced T-SQL. And, uh, it would be just such a joy, a treat, and a pleasure to see you there. I will sign your, your copy of the monitoring tool, autograph it for you. It’ll be fun, right? Be a good time. Anyway, it’s still February here. I still hate life a lot. So, uh, let’s, let’s answer some questions, right? Because that, that’s what keeps us warm.

And, uh, by the way, this, this is the, the, the monitoring tool of doing stuff, right? Like, look at these, look at that weight stats graph. I, I could, I don’t have anything running on here now. I was running some HammerDB, HammerDB tests on this thing earlier, right? And, uh, we got some, got some nice graphs, and we got a whole bunch of stuff about what was going on on the server, right? We, like, you got some CPU, we got some memory, we got all this fun stuff happening, right? Even got some blocking. Look at that. Oh, well, that, then that’s not that interesting, right?

Yeah, that one’s a little lame. But, uh, we have all sorts of good stuff in here. So, um, you should go get this for free and start monitoring your SQL Server for free and stop wasting money on those awful, either, well, I mean, those awful enterprise monitoring tools that are way too expensive or, um, not having monitoring at all, right? Neither one’s a very good idea. Yeah, I got all the good ideas. Anyway, um, here’s the first question now. I have a select statement running for 30 plus minutes. Sounds like you could use some professional help. Sounds like you could use the help of perhaps a young and handsome consultant with reasonable rates, 30 minute plus query there, fella.

Uh, then I manually kill it. Then it’s in a rollback for five, 10 minutes. What’s rolling back? Well, it’s a select query. You don’t have to roll back CPU. Uh, you’re probably not going to be rolling back memory. Uh, so really, uh, the, the most common thing that I see cause, uh, select queries. And I’m just going to assume that you’re a nice person and you’re not like, but it’s a select into cause that would be, that’d be annoying. Uh, so really the most common thing is if your query has done IO of some variety, that could be a spool in the query plan. It could be a spill in the query plan and, uh, that stuff has to clean up after itself. So if you were running for 30 minutes and doing all sorts of nonsense, then probably at the end of that 30 minutes, you had to do some more nonsense to revert those things.

All right. Do, do, do, do, do. Hi, Eric. Hi, you, whoever you are. You know, people never tell me their names, do they? I’m just out here, out here in the wind, fully exposed. Uh, I had a linked server query running for weeks. Where, where am I in the world? Uh, that ran in minutes locally. The remote view I got delivered had four scalar subqueries and the select list, each doing a nested loops join against a 5 million row table. Is what happens that 5 million table get pulled unfiltered for each subquery execution per row. So roughly 5 million times row count times four in data transfer. Um, I would really have to see the query plan in order to tell you what was going on, but that sounds rather unlikely. Um, most of the time, SQL Server just will pull all the data across, uh, for a remote, for a remote query.

And then sort of hit the stuff, uh, locally, but it really all does depend on what the execution plan looks like. So if, if you feel like sharing that in some form or fashion, then I can provide a better answer for you. But until then, uh, it is impossible to tell, but the optimizer usually isn’t quite that dumb. That being said, remote queries really, you get, you, you, you get what you, you get what you get with those, right? You get exact, I think more like you get what you deserve with remote queries. So, all right. Hello, Eric. Hello. Another one. Uh, watched a few of your videos on SQL performance. Oh, you know, everything now then. Uh, actually I have one issue not able to bind the solution to. Oh, do you actually? Uh, we recently migrated from SQL Server 2016 to SQL Server 2022 query on one table is taking indefinitely long earlier. It used to complete in 10, 20 minutes. Jeez. Like why? Um, I’m, I’m, I’m right here.

Uh, I can, I can, I can make these queries take less than 10 to 20 minutes, but now it’s running for three to four hours with no result. I tried optimizations like update stats with full scan, tried using index hints for join, rebuilding the index. I, I don’t know whose videos you’ve been watching. I, this is, this is, this is not my advice. I’m, I’m a little insulted here. But nothing is helping would be great if you can give some pointers to investigate the issue further. Um, so, I mean, again, uh, the, the execution plan is going to have reveal most of the mysteries to you. Um, if you, if you still have the, the 2016 server around or any data from it, or you’re still able to access it in some way, you might be able to find the query plan for your, your query over there that you could use. And you can, you can, you can compare it to the, uh, the, the, the current query plan that you’re getting, but the most valuable thing you can do is in open up SQL Server management studio, hit, hit the include actual execution plan button and start running your query.

And the reason why you’re going to do this is because even though you’re saying it runs for three, four hours and it’s no way you’re going to be able to, I mean, maybe there is, but it’d be boring. Uh, and you probably don’t want to wait for the whole thing to finish. What you can do is you can start that running in one SSMS tab.

And then over in another SSMS tab, you can run SP who is active with the get plans parameter set to one. And you can look at the, uh, in flight actual execution plan for your query and you can start to see where it’s getting jammed and glued up. So that, that’s where I would start.

Um, I would not start with all the other stuff you mentioned. You got bad advice from someone else. I don’t know who you’ve been talking to. You did not get that from my videos. All right.

Let’s see. Oh, I did that wrong. Yeah. Uh, should we prioritize plan stability or optimal plans when those goals conflict? What goals?

Um, yeah. So, oh, I, I get it. So you, you want to know if you should, um, prioritize having one execution plan that works well for everybody, or, uh, if, you know, you’re, you’re in a situation where lots of different, maybe, um, like parameter values, uh, would create lots of different execution plans.

Um, and my answer is you, you know, like it, like you don’t have to have one extreme or the other. Um, often you can like find reasonable ways to bucket these things. Um, you know, that’s sort of like what SQL servers, um, uh, parameter sensitive plan optimization does.

It attempts to bucket things, but it’s, it’s bucketing is, uh, way too coarse. Uh, the bucketing that that thing does is not fine grained enough. So what I would recommend doing is taking a look at, uh, which values you’re passing in, um, are sort of like the, the root of the evil for you.

Right. And maybe you can figure out like some sort of distribution counts for those values. And you can most likely, maybe not always, but you know, most likely in these, in these circumstances, you could find reasonable buckets of things for these, like, let’s just to throw some numbers out there, like one to 10,000, 10,000 to a hundred thousand, a hundred thousand to 500,000, 500,000 to a million.

You know, if you have values beyond that, you can go beyond that. But, you know, it doesn’t have to be, you know, either everyone gets one plan or like everyone gets every plan that they ever wanted.

You can, you can usually bucket these things, uh, and you can use like the option optimize for, uh, syntax to sort of bucket things in a way that, um, that, that, that, that would work for you.

Um, you can also use various dynamic SQL tricks to do that. Um, you know, each bucket gets its own one equals select one, two equals select two, three equals select three. Uh, you know, I’ve, I’ve, I’ve, I have a video called defeating parameter sniffing with dynamic SQL that goes into all that quite a bit.

So what does concurrency safe index maintenance actually mean and what risks remain even when following best practice? So I think you’re asking, how can I do index maintenance without blocking people?

And, uh, no, I’m, I’m, I’m just gonna, I’m just gonna say this once, once, once out there. Um, and, unless you really need to do something to your index, like, like maybe you want to add page compression to your index.

Um, you know, or I guess if you’re like deleting a lot of data and rebuilding an index once in a while would make sense. This, this really isn’t something that you should be doing in a way where you have to worry about concurrency all that much with it.

Like, you know, if you’re, if you’re, if you have a process that’s deleting a bunch of data and doing archival stuff, just do an index rebuild after that. Uh, if you want to apply page compression, you should be worrying about doing that during a maintenance window.

Um, we should not just be, uh, running scheduled index rebuilds because logical fragmentation is a thing. It’s just, it just doesn’t hold the same value, uh, for SQL Server anymore. There are of course, you know, index options like wait at low priority and all the other stuff and, you know, online equals on, but you might not be in a position to use all of those, all of those things.

So, um, if you are, that might make things a bit more concurrency safe, but, uh, really I just find, I find the whole ordeal mostly offensive. Anyway, that’s probably good for me here.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And, uh, I think, and I’m going to do some videos, uh, more in depth about the monitoring tool stuff, uh, this week to sort of help promote it, get it out there in front of people and hopefully act as some instructional guides as well.

So, uh, this week we’ll be, we’ll be talking monitoring boy. All right. Thank you for watching. Where’s my mouse? I got to close.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Get AI-Ready With Erik: Retrieval Augmented Generation

Posted on by Erik Darling

Get AI-Ready With Erik: Retrieval Augmented Generation

Summary

In this video, I delve into Retrieval Augmented Generation (RAG) and its application in SQL Server databases, particularly focusing on how it can be used to build developer support chatbots. I explain the process of using a stored procedure to retrieve relevant posts from your database and send them as context to an LLM (Language Model), which then generates answers based on community knowledge. The video demonstrates this with a practical example where we build a store procedure that includes source URLs, allowing users to verify information provided by the LLM. I also discuss the importance of embedding questions using AI generate embeddings and highlight how SQL Server can serve as a knowledge base for storing and retrieving relevant content efficiently.

Chapters

  • *00:00:00* – Introduction
  • *00:01:01* – Retrieval Augmented Generation Overview
  • *00:02:02* – Basic Process of RAG
  • *00:02:21* – LLM Verification with Source URLs
  • *00:03:02* – Store Procedure for Embeddings
  • *00:04:26* – Example Store Procedure Execution
  • *00:05:38* – Summary of RAG Workflow

Full Transcript

Erik Darling here with Darling Data. If you have hated all of the vector content that I’ve been covering over the last month or so, your long national nightmare is over. This will be the last, like, dedicated course promoting video that I have on it. Again, that is available on my training site. The full link is available down in the video description. There is an active coupon code called AIReady where you can get $500 off. And in today’s video, we’re going to talk about Retrieval Augmented Generation, or RAG, which is, you know, probably what most of you will have to deal with within, like, within the context of, like, a SQL Server database doing things with vectors, embeddings, stuff like that. So, let’s say, let’s say something like, you can even just pretend, right? You can, you can totally just cosplay as this person that I’m, this fictional person that I’m talking about. But let’s say you’re building a developer support chatbot. And developers need to know stuff about Git, like, I screwed up, how do I undo that? Or, like, I don’t know, like, left join, and left join, what is that? Or, like, maybe even something as ambiguous as, why is my query slow? Which, if you need an answer to that question, let me tell you this, my rates are reasonable, I can answer that for you, like, immediately. Bam, it’s done. But the general flow of this is, you know, you would, like, you know, find some relevant posts in your database. And then you would take those posts and send them as context to an LLM, right? Because SQL Server doesn’t do this stuff internally, right? You need, like, a, you need, you need an LLM in some fashion, where you can send bulks of information to, and then the LLM is supposed to, like, distill some answer based on, like, community knowledge around a topic. And that’s basically, that’s the basics of RAG, or Retrieval Augmented Generation.

SQL Server handles the data storage and data retrieval. The LLM handles the generation, and you have some application in the middle that connects them, right? Just says, oh, I need to, like, send in this search string to SQL Server and find relevant stuff. And then I take the relevant stuff that I found, and I send it to the LLM, and the LLM gets it all wrong, right? So, that’s the basic process there. So, if your chatbot, you know, wants to know, like, you know, you can undo a commit with git reset, users might be like, well, how do you know? Like, can you cite some sources? Can you, can you back that up with anything factual? Are you sure that’s a real command?

Does git exist? Like, there’s lots of stuff. People would rightfully question and ask about, when an LLM does stuff. So, what we’re going to do is use a store procedure to include source URLs that will let users verify the information, right? Because you might, if you’re the type of person who just takes what LLMs say at face value, you might be in for some interesting times in your life.

But, anyway, this is what the store procedure looks like. The name is unimportant, but we’re going to pass in a query. We’re going to define a max number of rows we want back. We’re also going to filter on distance, something that I’ve said a few times is pretty important with vector distance stuff. And then we’re going to, like, output a prompt. Just, this isn’t necessary, necessarily. This is just something for me to show, so I can show you that, like, what the full results would look like.

Inside the procedure, we’re going to make a variable that’s going to hold the embedding that we want, and we’re going to go grab that embedding using AI generate embeddings. I talked through a little bit about how to, like, get that set up in another video, but I’m not going to redo all that here. And then this is the query that runs to return stuff, and then, like, what it’s going to do is, like, you know, the usual, like, here’s the distance, here’s the source URL.

And we’re going to dump that into a temp table called prompt, which hopefully will happen promptly. And then we’re going to, I mean, I’m doing this so it makes sense in this context. You might not do this. You might do something different. But we’re going to say our prompt is, based on the following Stack Overflow questions, answer the questions, and then the query that the user originally passed in, right?

So, like, that’s, like, the question that needs to get answered. And then what we can do is run the store procedure, right? So, we’re going to declare a prompt variable just to hold the output here.

The query that we’re sending in is how to undo the most recent commits in Git. We’re asking for the top 10 results with a max distance of 0.20. And when we run this, what the prompt returns is, I mean, I have some XML content here so I can click and open it for you.

You might choose to do something differently. But it says, based on the following Stack Overflow questions, and all these questions are things that we found using the search string that someone passed in. Like, based on these 10 URLs, answer the question, how to undo the most recent commits in Git.

So, that’s, like, sort of the basics of, like, what you would do to, like, A, like, you have all this stuff stored in SQL Server. You have your vectors. You have your embeddings.

And then, you know, you have your users go in, search for things that are similar. Then you have an LLM build out a response based on what it finds in there. So, it treats, like, treats your database like a knowledge base, right?

You can just search through all this, like, you know, historical knowledge, get an answer, and hopefully it’s, you know, well-founded in reality and the LLM doesn’t hallucinate anything along the way. All things that we hope for. So, the application basically, like, basically, again, the workflow is the user asks, like, how do I do this thing?

How do I undo my last commit in Git? The app embeds the question. We use AI generate embeddings. I guess you don’t need to know it’s in Video 20.

That’s stupid. You could also call an external embedding, but I don’t make anyone do that because it costs extra money. The app calls SQL Server to execute some store procedure and get the results. Then the app builds the LLM prompt, right?

So, like, in this case, I had the store procedure do it because I don’t have an app, right? I’m appless. But you could have an application do that and then, you know, answer the question down here. The LLM generates an answer using all the, like, context from the links that you provided it.

And then the app shows the answer with source links for verification, right? Wouldn’t it be nice if LLM’s always cited sources? So, SQL Server’s job is to find relevant content quickly.

And the LLM’s job is to then synthesize a coherent answer from that content. So, that’s sort of in a nutshell how one would approach RAG with SQL Server. I hope you enjoyed yourselves.

I hope you learned something. And today is Friday, so I won’t see you in tomorrow’s video, but I will see you in Monday’s video, assuming that we all survive, which will be office hours. And then, what am I going to do after office hours?

Well, that week is wide open. Wide open. Who knows what will happen? All right. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Get AI-Ready With Erik: Capacity Planning

Posted on by Erik Darling

Get AI-Ready With Erik: Capacity Planning

Summary

In this video, I delve into the fascinating world of capacity planning as it pertains to vector embeddings in SQL Server databases. I start by comparing the size of a typical Stack Overflow post table from 2010 to its corresponding embeddings table, revealing just how much space these embeddings can consume—eight and a half gigs compared to six and a quarter gigs for the original data. This comparison sets the stage for an exploration of vector sizes, which depend on the number of dimensions in your chosen model. I also discuss the implications of different models and their embedding sizes, touching on both theoretical maximums and practical considerations. Additionally, I explore how vector indexes work differently from traditional B-tree indexes, emphasizing the importance of columnstore indexes for optimizing space usage. Finally, I provide some practical tips for managing database space when working with large embeddings, including the use of clustered columnstore indexes to achieve significant savings.

Chapters

Full Transcript

Erik Darling here with Darling Data. Boy oh boy, exciting times are upon us. We’re going to talk about a DBA-ish subject here, but who knows, maybe someday a developer will care about this sort of thing as well, and that is capacity planning. Because vectors themselves, the embeddings, can be rather large. And what I’m going to show you is You know what, actually, I’m going to change this on the fly a little bit, because I want to show you actually just how big embeddings can get. And I don’t know why it didn’t occur to me to do this at the beginning. Posties. I’m going to compare the size of the post table to the size of the post embeddings table. Now, this is the Stack Overflow 2010, so this is like not even close to the full-size database. This is like the first like two, three years of Stack’s existence. The post table with the title column, the tags column, the bodies column, body column, not bodies, like last editor, like a whole bunch of string columns and stuff in there. The whole thing, right, is about 6.4 gigs. The post embeddings table with far fewer columns in it, just having the embeddings in there is eight and a half gigs, right? Like, it’s no joke, size-wise.

And, like, one way that you can sort of figure that out is, like, we’re going to take, it’s like 8218. We’re going to take that number, and we’re going to just sort of, like, try to do a rough projection here. Now, remember, post embeddings is about eight and a half gigs, like, full-size up here. If we just sort of look at different row counts and how big they might be, that still doesn’t get us close to, like, we’re still, like, about a gig off for all of those different row counts, right? And, like, part of that is going to be, like, well, like, the post embeddings table has the embeddings column in it, which is the majority of the space, but there are a few other columns in the table.

Nothing huge. It’s, like, ID and, like, post ID and, like, a date time to column or something. So, like, if you look at the table itself, like, those embeddings take up the majority of it, and, you know, every embedding looks like this. It’s, you know, 1024 floaty numbers jammed into a JSON string.

So, you know, not kind of storage. If you’re a storage vendor out there, God bless. God bless. I’m in the wrong line of work, probably.

But vector size is going to depend on the number of dimensions that your embeddings are, and the number of dimensions in your embeddings is going to depend on the model you choose. There are all sorts of different models out there that generate different numbers of embeddings.

The max number of embeddings that you can use for a float32 data, float32 vector in SQL Server is 1,998. That doubles for float16, but float16 is still a preview feature. So, yep, ba-ba-ba-ba-ba-ba-ba-ba-ba.

Here we are back at square one. So, if we look at this and we look at what different models might generate as far as size goes, you know, like 768, you know, theoretically, you know, you know, like, I don’t even know how many gigs that would be.

But this is, you know, like, they get bigger as you involve more embeddings up until you hit, you know, 8,000 actual bytes and when you hit the 1,998 vectors for a float32. But I don’t know, at least no model that I’ve ever seen uses specifically 1,998.

If you’re using, like, OpenAI, you’re going to have, like, the 1,532 or whatever that you see in the majority of the Microsoft demos because, gee, I wonder why they’re all using OpenAI, self-talking, whatever. Anyway, what’s cool about vector indexes, though, sort of, it’s like, it’s not like a B-tree index, right?

Because it’s not like you’re taking all the data and just putting it in order, right? So, it’s like you’re making a graph out of it and you’re sort of connecting lines within that graph. That’s why the vector index create process looks the way it does.

If you go back a few videos to where I showed you the stuff that Microsoft is running when they build a vector index, like, you’re not just, like, taking the data and putting it in, like, B-tree order. It’s not like you’re seeking into, like, a regular B-tree and just being like, oh, this is my entry point. I found this thing. I seek exactly to this primary key vector row.

It’s not like that at all. It builds a graph and the graph structure itself is a lot smaller than the vector embeddings are. Like, you can see the size of the edge table, right, is 286 megs, which is much, much smaller than the size of the embedding data that we have in the post-embeddings table.

If you want to control embedding sizes, like, outside of just which model you choose, right? Like, if you choose a model that’s, like, 1,024 or 1,500, whatever, you know, like, you’re going to have some pretty big vector data.

Like, columnstore works. Like, you can create a, I mean, non-clustered columnstore is, like, it’s okay, but, like, you still have the base table, which is, you know, it can be like a rowstore clustered index or something, and that’s not going to be very well compressed.

So, like, clustered columnstore, you can create that on your table that has the embeddings in it, and columnstore will compress it pretty well. Row and page compression don’t do anything for vectors, right? That is not the type of data that you can actually compress, because it’s, like, the same reason you can’t compress, like, JSON or XML, or, like, you have compressed XML indexes in SQL Server, whatever, but, like, if you just have, like, an Envarkar max or something, like, that doesn’t get compressed.

The only thing is you can’t intermix columnstore and vector indexes. If you have a table with a columnstore, like, a clustered columnstore index on it, or I think even non-clustered columnstore, too, you can’t create a vector index on it.

And if you have a vector index on a table, you can’t create a columnstore index on it. They just don’t, they don’t interoperate. That’s not super important today, since vector indexes are still preview only. The only generally available stuff is float32 and vector distance, and a few of the other, like, AI generate chunks and, you know, things like that.

So, you may as well, at this point, just get the space savings and maybe some batch mode in your query plans by creating the clustered columnstore index on it.

Anyway, just a little bit of stuff about the capacity planning there, a couple hints on how you can maybe save some database space by using clustered columnstore.

But anyway, that’s it there. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you will buy this course, Get AI Ready with Erik, available today with a coupon code AI Ready, which takes 100 entire dollars off the cost of the course.

It makes it well worth your time and money if your company is planning on doing anything with vectors in SQL Server. All right.

Well, that’s probably good there. Thank you for watching. Goodbye.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.