Words: 865
Time to read: ~ 5 minutes
T-SQL Tuesday Time
Welcome back to another installment of T-SQL Tuesday, the monthly blog post call. This month we have Kevin Chant ( twitter ) who has asked us for…
[…] a post about a fantasy SQL Server feature you’ve got in mind.
Kevin Chant
It’s hard for me to believe that my last T-SQL Tuesday post was back in May 2019 but, when I look back over the list of my blog posts, that’s the last one.
I can only put it down to “what I want to do” being out of sync with “what I can do with the time I have”.
So, with that major gap in T-SQL Tuesday posts in place, I’d like to start writing these again.
Beginning with this one, and an apology.
An Apology
I’m starting with an apology for this post because, no matter how I phrase this in my head, I cannot make it seem like I am not complaining.
So I ask that you forgive me if this post comes across as me whining about the level of effort that is currently involved with this.
Fantasy SQL Server feature
My fantasy SQL Server feature is…
- A performance rating.
I’m not talking about TCP ratings nor am I talking about Sentry One’s Health Score (although I’ll admit that’s pretty close) nor Brent Ozar ( twitter ) and sp_BlitzFirst.
What I would like is a performance rating, an X out of 100, a Low / Medium / High, a sub-par / on-par / above-par description of how your SQL Server is doing.
Why this?
I’m not whinging about this due to a mis-guided want to compare my instances against others. Believe me, I know the state of my instances are not up there.
Nor is a case of wanting to show that my instaces are “in the top 10 in Ireland / Europe / the world”. Believe me, I realised a long time ago that, while I enjoy what I do, I do not want to take the sacrifices needed to get to that level.
DevOps is the union of people, process, and products to enable continuous delivery of value to our end users.
Donovan Brown
We are trying to take major steps with DevOps in our company. To be more transparent, to reduce silos, and to share knowledge so we can get releases out to customers faster. So we can get value to our customers out there faster.
So when a Pull Request (PR) gets sent to me and I respond with concerns, suggestions, and pull some data from our instances to show as an example, I’m really not expecting this response.
Thanks for this but we’re not quite sure what you mean. Could you give us a number please? Like, our SQL Server is doing an x out of 100?
Response
It wasn’t until I was asked this and looked into how you could go about acheiving this that I realised how difficult this is?
First of all, are you talking query performance or SQL Server health?
If it’s the first, how are you going to measure that? Duration? CPU? IO? Sure Query Store would be a great help…
If it’s the later, sure include RPO and RTO. How do you measure HA and DR? Does deadlocks come into play here or query performance?
Are a failed statistics job going to affect the rating on SQL Server Health? Cause I know that it’s going to have an effect on query performance!
Fantasy Feature
So that’s my fantasy feature.
I want a performance rating built into SQL Server. One that you can measure against your own servers, or against telemetry gathered from other servers.
Break it down however you wish.
- Rating per Query Duration is way up but your Rating per Memory is down.
- Your Rating per Deadlock has become nearly nonexistent but your Rating per Dirty / Phantom Reads … I got some bad news there…
- Your Batch Transactions Rating has gone up from the Last Version push but that’s because you stopped doing
CURSORSandWHILEloops. Go you, we we’re thinking it was about time!
I don’t have an exact defintion
I don’t know if I’d want this as a single rating. SQL Server is more than the sum of it’s parts.
I don’t know if I’d want this as multiple ratings summed up since I don’t know how you’d weight them. Different companies have different concerns.
I also know that we have tools for this
We have Query Store, we have AGs, we have Performance counters, we have sp_Blitz%, we have Workload tools, we have git, and TFS, and Azure Devops, and AWS CloudFormation, and docker containers “kubeterised” into a CI pipeline.
I’m fully aware that we have nearly everything at our disposal to make this happen. All we need is time, a plan, and the ability to progressively see this through.
Like I said at the start, I apologise if this comes across as me whining.
But that’s not what this T-SQL Tuesday asked. It asked for your Fantasy Feature.
Well my name is Shane O’Neill and right now, I want to know that my SQL Server instance is doing X out of 100.
You tell me that and I’ll work on improving it.
You both inspired me to take these ideas a bit further and dream up comparative load-testing! Thanks for the good reads. 🙂 . https://natethedba.wordpress.com/2019/09/10/t-sql-tuesday-118-fantasy-feature/
I dig this, Shane. Yeah, there’s some of it in the SentryOne Environment Health Overview that’s packaged with their monitoring platform (https://www.sentryone.com/blog/jasonhall/environment-health-overview). And lots of community tools. And Microsoft’s put out a few upgrade/migration/tuning advisors. And there’s more that could be done! Checks for unused cruft in the databases, broken LSN chains, MAXDOP and tempdb files vs. CPU cores, excessive numbers of databases, lax security constraints, over-reliance upon triggers where constraints might be better, deprecated feature usage, Agent Job performance/reliability/history…the list goes on and on, doesn’t it?
But it’s pieces scattered all over the place (or not, in some cases) that one would have to figure out how to pull together. And then more to discover. Then how do you weight and score each factor to get that simple number? Can you decompose it so that you have a starting point, the “heavy hitter” that you should address.
And then there’s the other question – what’s the goal? Is the goal to hit a specific metric that may be somewhat arbitrary? Or do you apply your own personal weights to each subcomponent and focus on the things that matter most to you – or the things you can most readily address? There are things in my environment that would definitely bring this score down, but is there any chance of me changing them? Not singlehandedly, it’d be a whole-company effort.
I love this idea, and I think it’d be ideal for taking to numbers-focused managers who don’t need to hear about PLE, plan cache usage, and the troublesome wait stat du jour. One could make a nice career out of making this happen.