SQL Server Performance Office Hours Episode 32

Questions:

I’ve just installed SQL Sentry. Any advice?
Order By NEWID() will impact on Query Execution on large data table?
Are you aware of any issues related to restoring a query store database with forced plans to a different server with a different database name that the forced plans will no longer work? The plans have the database name in them but not sure if that’s a problem.
When you modify a stored procedure that contains a query with a forced plan it get a new query_id in query store even if the query hasn’t changed. Are there any tricks for applying the already forced plan to the new query id?
Will you do a jig or a goofy dance. Feel free to have fun with your green screen with this one

Video Summary

In this video, I dive into answering five user-submitted questions on various SQL Server topics, including troubleshooting font issues in SQL Sentry, the pitfalls of using `ORDER BY NEWID()` for large datasets, and the complexities of restoring query store databases to different servers. I also share some insights on modifying stored procedures with forced plans and even reveal a little secret about my constant, albeit invisible, dance moves. If you have any questions or want more details on these topics, be sure to check out the links in the video description for interactive ways to engage with me and other community members. Stay tuned for tomorrow’s session where we’ll continue our journey into T-SQL and SQL Server performance tuning!

Full Transcript

Erik Darling here with Darling Data. And you wouldn’t believe it’s that time. Can you believe it’s just another Monday? And we have to do office hours, don’t we? This is where I answer five user submitted questions at a time, usually. Every once in a while I’m off by one. But let’s get on to that. Before I answer questions, we’re not doing anything. You are not contributing anything to this except questions, which hopefully you’ll do. And you can find the link to do that down in the video description. There are all sorts of helpful links down there for you to interact with me in all sorts of exciting ways. So I hope you will choose to peruse those links and excite me in some way. If you want to get excited in person, we can do that together. That’s it. That is definitely a wee thing. I will be on tour with the nice folks at Redgate. We have two mini Pass On Tour events coming up. One in Dallas, September 15th and 16th. And one in Utrecht. That is, in fact, the Netherlands, I’m told. October 1st and 2nd. And of course, all this is leading up to Pass Data Community Summit taking place in Seattle November 17th to 21st, where I will have two wonderful, mellifluous days of pre-cons with the lovely and talented Kendra Little. All about T-SQL and performance tuning a little bit, because it’s hard to talk about T-SQL without some performance stuff sneaking in there, isn’t it?

Anyway, let’s go get them. Let’s go do these here questions. Boy, I have a lot of stuff in the taskbar today. A lot of work going on. A lot of stuff flying around. A lot of moving pieces here. Hopefully, you can keep track of them all. All right. First question. Let’s see what we got here. I don’t understand why the font is a little wonky on that. Not my fault. I’ve just installed SQL Sentry. Any advice? Well, we should open it. Make sure it’s running. Make sure it’s collecting data. These are all good things to do. Assuming that you’re talking about the monitoring tool and not just PlanExplore. If you’ve just installed PlanExplore, I have bad news for you.

Use SSMS instead. But if you’re using SQL Sentry, that is one of my favorite monitoring tools out there. I would suggest maybe taking a look at the alerts section and making sure that the alerts that are set up and being sent out are ones that are of use and value to you. It does default to sending out a lot of alerts and not all of them are particularly useful or meaningful in every environment.

The other thing that I would suggest you do is when you go into the top SQL tab, there’s a little settings button somewhere. And there’s a little settings tab along with top SQL, procedure stats, query stats, stuff like that. And by default, SQL Sentry only shows you the top 15 queries.

It will show you a max of 5,000 queries. So I suggest changing the 15 in there to 5,000. Alongside that, way up at the top of SQL Sentry, there’s a little button that sort of looks like a Greek E, right?

Whatever that little epsilon thing maybe. I don’t exactly know what it’s called. I’m not Greek.

And it’s all to me. So you should push that button because that button will group the queries in the top queries and procedure stats and query stats tabs together by text pattern. So if you have the same query over and over again doing stuff, you can see sort of top-level grouped results for CPU duration, memory, things like that.

So that’s my advice. That’s where I spend most of my time in there. Don’t neglect the blocking and deadlocking tabs.

Those are also very useful. Aside from that, I would just say enjoy yourself because you have the best monitoring tool on the market despite SolarWinds’ best efforts to have it do nothing new or interesting. So I actually think that they should be arrested for how they’ve neglected SQL Sentry.

As a monitoring tool, DPA is, of course, a tire fire that no one should ever be forced to use. All right. Next question.

Order by new ID will impact on query execution on large data table. Yes. Don’t do that unless you hate yourself or someone else. Ordering by new ID is a great way to have a performance problem.

Demo is really well, but don’t do it. It’s avoid at all costs. If your question is about getting random data out, ask that question instead and we can talk about that. If you just really are not sure about what new ID is going to do with an order by, good luck.

All right. Here’s a question, I believe. Yeah, there’s a question, Mark.

Look at that. Are you aware of any issues related to restoring a query store database with forced plans to a different server with a different database name that the forced plans will no longer work? That sounds like a statement.

It sounds like something that you have found. It sounds like you have found an issue. And you are asking me if I’m aware of an issue. And it sounds like it’s an issue to me. I would think that that I would guess like this is without me going and, you know, looking at the looking at that and actually going through with all of the steps that you’ve outlined there.

I would guess that the plan forcing would have some difficulty because the database name stored in the query plan XML might throw things off. And you would probably end up getting new query IDs for the queries that had forced plans once the new database name comes in with the plan XML. So I would guess that that’s not going to work, but you know, probably not great.

So here’s another question. When you modify a store procedure that contains a query with a forced plan, it get a new query ID in query store, even if the query hasn’t changed. Are there any tricks for applying the already forced plan to the new query ID?

Well, no, you have to get that plan for that new query ID if you want to force it for that query ID. Otherwise, otherwise, query store throws an error saying that there’s no plan ID for that query. I would be interested because you’re saying modify.

And I think that if you alter or create or alter, actually, you know, I think if you alter a store procedure, it keeps the same object ID. I know that dropping and recreating will assign a new object ID. So I’m not sure that that would be it.

But it’s an interesting question. And I think that’s something that I would need to dig in on a little bit. But I don’t have a quick answer for that because I’ve just never looked at it.

So there we go. All right. Last question here. Will you do a jig or goofy dance?

Feel free to have fun with your green screen on this one. Well, I am dancing currently. So what you don’t know about me is that I am constantly dancing. You can’t see from here down.

But my legs are just in a constant flurry of dance. So maybe that’s just the level of skill that I have at the form of dance that I practice. Is that you can’t even tell I’m doing it because nothing up here moves.

So now you’ve learned something new about me. Anyway, that’s five questions, I think. Well, that’s five questions and some homework for me, I think.

All right. So I’ll have to go look at that. 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 do some more T-SQL learning or something along those lines. And after that, who knows?

We’ll figure it out when we get there, won’t we? 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.