This is a short post, since we’re on the subject of index spools this week, to show you that the columns that go into the spool will impact spool size and build time.
I know, that sounds obvious, but once in a while I care about “completeness”.
We’re going to look at two queries that build eager index spools, along with the time the spool takes to build and how many writes we do.
Query 1
On the side of the query where a spool gets built (inside the apply), we’re only selecting one column.
SELECT TOP ( 10 )
u.DisplayName,
u.Reputation,
ca.*
FROM dbo.Users AS u
CROSS APPLY
(
SELECT TOP ( 1 )
p.Score
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
AND p.PostTypeId = 1
ORDER BY p.Score DESC
) AS ca
ORDER BY u.Reputation DESC;
In the query plan, we spend 1.4 seconds reading from the Posts table, and 13.5 seconds building the index spool.
Work it
We also do 21,085 writes while building it.
Insert comma
Query 2
Now we’re going to select every column in the Posts table, except Body.
If I select Body, SQL Server outsmarts me and doesn’t use a spool. Apparently even spools have morals.
SELECT TOP ( 10 )
u.DisplayName,
u.Reputation,
ca.*
FROM dbo.Users AS u
CROSS APPLY
(
SELECT TOP ( 1 )
p.Id, p.AcceptedAnswerId, p.AnswerCount, p.ClosedDate,
p.CommentCount, p.CommunityOwnedDate, p.CreationDate,
p.FavoriteCount, p.LastActivityDate, p.LastEditDate,
p.LastEditorDisplayName, p.LastEditorUserId, p.OwnerUserId,
p.ParentId, p.PostTypeId, p.Score, p.Tags, p.Title, p.ViewCount
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
AND p.PostTypeId = 1
ORDER BY p.Score DESC
) AS ca
ORDER BY u.Reputation DESC;
GO
In the query plan, we spend 2.8 seconds reading from the Posts table, and 15.3 seconds building the index spool.
Longer
We also do more writes, at 107,686.
And more!
This Is Not A Complaint
I just wanted to write this down, because I haven’t seen it written down anywhere else.
Thanks for reading!
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.
Microsoft recently published new guidance on setting server level MAXDOP. I hope to help the community by analyzing the new guidance and offering some of my own thoughts on query parallelism.
Line by line
Documentation is meant to be shared after all, so hopefully no one minds if I quote most of it:
Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. The Database Engine places logical processors from the same physical core into different soft-NUMA nodes.
This is true and one of the bigger benefits of auto soft-NUMA as far as I’ve been able to tell.
The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node.
SQL Server is not designed to keep all worker threads in a single soft-NUMA node. That might have been true in SQL Server 2008, but it changed in 2012. The only semi-official documentation that I know of is here and I looked into the behavior here. Read through both if you’re interested in how scheduling of parallel worker threads is performed by SQL Server, but I’ll provide a quick summary via example here.
Suppose you have two soft-NUMA nodes of 6 schedulers each and the server just restarted.NUMA node 0 has positions 0-5 and NUMA node 1 has positions 6-11. The global enumerator starts at position 0. If I run a MAXDOP 4 query then the enumerator advances by 4. The parallel workers are allowed in positions 0-3 which means that any four out of six schedulers can be chosen from NUMA node 0. All parallel worker threads are in NUMA node 0 for the first query. Suppose I run another MAXDOP 4 query. The enumerator advances by 4 and the allowed positions are 4-7. That means that any two schedulers can be chosen from NUMA node 0 and any two schedulers can be chosen from NUMA node 1. The worker threads are split over two soft-NUMA nodes even though query MAXDOP is less than the size of the soft-NUMA nodes.
Unless you’re on a server with a single soft-NUMA node it is difficult to guarantee that all worker threads end up on the same soft-NUMA node. I strongly recommend against aiming for that as a goal. There are more details in the “Preventing hard NUMA worker splits” section of this blog post.
This will improve the performance of the queries and distribution of worker threads across the NUMA nodes for the workload. For more information, see Soft-NUMA.
I’ve heard some folks claim that keeping all parallel workers on a single hard NUMA nodes can be important for query performance. I’ve even seen some queries experience reduced performance when thread 0 is on a different hard NUMA node than parallel worker threads. I haven’t heard of anything about the importance of keeping all of a query’s worker threads on a single soft-NUMA node. It doesn’t really make sense to say that query performance will be improved if all worker threads are on the same soft-NUMA node. Soft-NUMA is a configuration setting. Suppose I have a 24 core hard NUMA node and my goal is to get all of a parallel query’s worker threads on a single soft-NUMA node. To accomplish that goal the best strategy is to disable auto soft-NUMA because that will give me a NUMA node size of 24 as opposed to 8. So disabling auto soft-NUMA will increase query performance?
Starting with SQL Server 2016 (13.x), use the following guidelines when you configure the max degree of parallelism server configuration value:
Server with single NUMA node [and] Less than or equal to 8 logical processors: Keep MAXDOP at or below # of logical processors
I don’t understand this guidance at all. If MAXDOP is set to above the number of logical processors then the total number of logical processors is used. This is even mentioned earlier on the same page of documentation. This line is functionally equivalent to “Set MAXDOP to whatever you want”.
Server with single NUMA node [and] Greater than 8 logical processors: Keep MAXDOP at 8
This configuration is only possible with a physical core count between 5 and 8 and with hyperthreading enabled. Setting MAXDOP above the physical core count isn’t recommended by some folks, but I suppose there could be some scenarios where it makes sense. Keeping MAXDOP at 8 isn’t bad advice for many queries on a large enough server, but the documentation is only talking about small servers here.
Server with multiple NUMA nodes [and] Less than or equal to 16 logical processors per NUMA node: Keep MAXDOP at or below # of logical processors per NUMA node
I have never seen an automatic soft-NUMA configuration result in more than 16 schedulers per soft-NUMA node, so this covers all server configurations with more than 8 physical cores. Soft-NUMA scheduler counts per node can range from 4 to 16. If you accept this advice then in some scenarios you’ll need to lower MAXDOP as you increase the number of physical cores per socket. For example, if I have 24 schedulers per socket without hyperthreading then auto soft-NUMA gives me three NUMA nodes of 8 schedulers, so I might set MAXDOP to 8. But if the scheduler count is increased to 25, 26, or 27 then I’ll have at least one soft-NUMA node of 6 schedulers. So I should lower MAXDOP from 8 to 6 because the physical core count of the socket increased?
Server with multiple NUMA nodes [and] Greater than 16 logical processors per NUMA node: Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16
I have never seen an automatic soft-NUMA configuration result in more than 16 schedulers per soft-NUMA node. I believe that this is impossible. At the very least, if it possible I can tell you that it’s rare. This feels like an error in the documentation. Perhaps they were going for some kind of hyperthreading adjustment?
NUMA node in the above table refers to soft-NUMA nodes automatically created by SQL Server 2016 (13.x) and higher versions.
I suspect that this is a mistake and that some “NUMA node” references are supposed to refer to hard NUMA. It’s difficult to tell.
Use these same guidelines when you set the max degree of parallelism option for Resource Governor workload groups.
There are two benefits to using MAXDOP at the Resource Governor workload group level. The first benefit is that it allows different workloads to have different MAXDOP without changing lots of application code. The guidance here doesn’t allow for that benefit. The second benefit is that it acts as a hard limit on query MAXDOP as opposed to the soft limit provided with server level MAXDOP. It may also be useful to know that the query optimizer takes server level MAXDOP into account when creating a plan. It does not do so for MAXDOP set via Resource Governor.
I haven’t seen enough different types of workloads in action to provide generic MAXDOP guidance, but I can share some of the issues that can occur with query parallelism being too low or too high.
What are some of the problems with setting MAXDOP too low?
Better query performance may be achieved with a higher MAXDOP. For example, a well-written MAXDOP 8 query on a quiet server may simply run eight times as quickly as the MAXDOP 1 version. In some scenarios this is highly desired behavior.
There may not be enough concurrent queries to get full value out of the server’s hardware without increasing query MAXDOP. Unused schedulers can be a problem for batch workloads that aim to get a large, fixed amount of work done as quickly as possible.
Row mode bitmap operators associated with hash joins and merge joins only execute in parallel plans. MAXDOP 1 query plans lose out on this optimization.
What are some of the problems with setting MAXDOP too high?
At some point, throwing more and more parallel queries at a server will only slow things down. Imagine adding more and more cars to an already gridlocked traffic situation. Depending on the workload you may not want to have many active workers per scheduler.
It is possible to run out of worker threads with many concurrent parallel queries that have many parallel branches each. For example, a MAXDOP 8 query with 20 branches will ask for 160 parallel workers. When this happens parallel queries can get downgraded all the way to MAXDOP 1.
Row mode exchange operators need to move rows between threads and do not scale well with increased query MAXDOP.
Some types of row mode exchange operators evenly divide work among all parallel worker threads. This can degrade query performance if even one worker thread is on a busy scheduler. Consider a server with 8 schedulers. Scheduler 0 has two active workers and all other schedulers have no workers. Suppose there is 40 seconds of CPU work to do, the query scales with MAXDOP perfectly, and work is evenly distributed to worker threads. A MAXDOP 4 query can be expected to run in 40/4 = 10 seconds since SQL Server is likely to pick four of the seven less busy schedulers. However, a MAXDOP 8 query must put one of the worker threads on scheduler 0. The work on schedulers 1 – 7 will finish in 40/8 = 5 seconds but the worker thread on scheduler 0 has to yield to the other worker threads. It may take 5 * 3 = 15 seconds if CPU is shared evenly, so in this example increasing MAXDOP from 4 to 8 increases query run time from 10 seconds to 15 seconds.
The query memory grant for parallel inserts into columnstore indexes increases with MAXDOP. If MAXDOP is too high then memory pressure can occur during compression and the SELECT part of the query may be starved for memory.
The query memory grant for memory-consuming operators on the inner side of a nested loop is often not increased with MAXDOP even though the operator may execute concurrently once on each worker thread. In some uncommon query patterns, increasing MAXDOP will increase the amount of data spilled to tempdb.
Increasing MAXDOP increases the number of queries that will have parallel workers spread across multiple hard NUMA nodes. If MAXDOP is greater than the number of schedulers in a hard NUMA node then the query is guaranteed to have split workers. This can degrade query performance for some types of queries.
Worker threads may need to wait on some type of shared resource. Increasing MAXDOP can increase contention without improving query performance. For example, there’s nothing stopping me from running a MAXDOP 100 SELECT INTO, but I certainly do not get 100X of the performance of a MAXDOP 1 query. The problem with the below query is the NESTING_TRANSACTION_FULL latch:
Preventing hard NUMA worker splits
It generally isn’t possible to prevent worker splits over hard NUMA nodes without changing more than server level and query level MAXDOP. Consider a server with 2 hard NUMA nodes of 10 schedulers for each. To avoid a worker split, an administrator might try setting server level MAXDOP to 10, with the idea being that each parallel query spreads its workers over NUMA node 0 or NUMA node 1. This plan won’t work if any of the following occur:
Any query runs with a query level MAXDOP hint other than 0, 1, 10, or 20.
Any query is downgraded in MAXDOP but still runs in parallel.
A parallel stats update happens. The last time I checked these run with a query level MAXDOP hint of 16.
Something else unexpected happens.
In all cases the enumerator will be shifted and any MAXDOP 10 queries that run after will split their workers. TF 2467 can help, but it needs to be carefully tested with the workload. With the trace flag, as long as MAXDOP <= 10 and automatic soft-NUMA is disabled then the parallel workers will be sent to a single NUMA node based on load. Note that execution context 0 thread can still be on a different hard NUMA node. If you want to prevent that then you can try Resource Governor CPU affinity at the Resource Pool level. Create one pool for NUMA node 0 and one pool for NUMA node 1. You may experience interesting consequences when doing that.
The most reliable method by far is to have a single hard NUMA node, so if you have a VM that fits into a single socket of a VM host and you care about performance then ask your friendly VM administrator for some special treatment.
Final thoughts
I acknowledge that it’s difficult to create MAXDOP guidance that works for all scenarios and I hope that Microsoft continues to try to improve their documentation on the subject. Thanks for reading!
Certain spools in SQL Server can be counterproductive, though well intentioned.
In this case, I don’t mean that “if the spool weren’t there, the query would be faster”.
I mean that… Well, let’s just go look.
Bad Enough Plan Found
Let’s take this query.
SELECT TOP (50)
u.DisplayName,
u.Reputation,
ca.*
FROM dbo.Users AS u
CROSS APPLY
(
SELECT TOP (10)
p.Id,
p.Score,
p.Title
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
AND p.PostTypeId = 1
ORDER BY
p.Score DESC
) AS ca
ORDER BY
u.Reputation DESC;
Top N per group is a common enough need.
If it’s not, don’t tell Itzik. He’ll be heartbroken.
The query plan looks like this:
Wig Billy
Thanks to the new operator times in SSMS 18, we can see exactly where the chokepoint in this query is.
Building and reading from the eager index spool takes 70 wall clock seconds. Remember that in row mode plans, operator times aggregate across branches, so the 10 seconds on the clustered index scan is included in the index spool time.
One thing I want to point out is that even though the plan says it’s parallel, the spool is built single threaded.
One Sided
Reading data from the clustered index on the Posts table and putting it into the index is all run on Thread 2.
If we look at the wait stats generated by this query, a full 242 seconds are spent on EXECSYNC.
Armless
The math mostly works out, because four threads are waiting on the spool to be built.
Even though the scan of the clustered index is serial, reading from the spool occurs in parallel.
Spange
Connected
Eager index spools are built per-query, and discarded afterwards. When built for large tables, they can represent quite a bit of work.
In this example query, a 17 million row index is built, and that’ll happen every single time the query executes.
While I’m all on board with the intent behind the index spool, the execution is pretty brutal. Much of query tuning is situational, but I’ll always pay attention to an index spool (especially because you won’t get a missing index request for them anywhere). You’ll wanna look at the spool definition, and potentially create a permanent index to address the issue.
As for EXECSYNC waits, they can be generated by other things, too. If you’re seeing a lot of them, I’m willing to bet you’ll also find parallel queries with spools in them.
Thanks for reading!
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.
“We recently upgraded to SQL Server 201(4, 6, 7), and performance is AWFUL…”
And the problem was pretty easily solved by flipping the compatibility level back to 110, which fixed (most) of the issues?
(Or just went back to having the issues they knew that they had before, which is often far less scary.)
In those versions, flipping compatibility level uses the new Cardinality Estimator (CE). That new Cardinality Estimator is real hit or miss.
The worst part is that there’s practically no gain to be realized for using higher compatibility levels — that changes with SQL Server 2019.
Feature Creature
There are two things that are pretty cool in SQL Server 2019: Scalar UDF Inlining (FROID), and Batch Mode for Row Store (BMFRS?).
FROID potentially solves a big problem that’s been plaguing SQL Server users for decades. Scalar UDFs are just straight up performance poison.
This fixes the problems with them (I mean, sure, not every UDF is eligible, and you can run into other problems, but still…).
BMFRS does a bunch of stuff: It makes Batch Mode processing available for Row Store indexes (duh), it also makes Adaptive Joins and Memory Grant Feedback available for them.
Those two things were introduced in 2017, but only available if you used column store (which is what Batch Mode was originally created for).
These things have the potential to fix some very big workload problems for people.
But there’s a thing.
Monkey Paw
In order to use them, you gotta be in compatibility level 150. That also brings along the new CE.
You could be trading one set of problems for another, here. That makes flipping the switch a hard sell.
It all depends on where your biggest problems are, and the time and resources you have to fix regressions.
For most people, it’s not realistic to test their entire workload. You can test your most important queries, as long as they’re reliable.
This is a good place to plug Workload Tools by Gianluca Sartori, which can make this easier.
You can also flip the switch during a low usage time and see if monitoring freaks out.
If it doesn’t, great. If it does, you have a lot of work to do.
Of course, if you’re on SQL Server Standard Edition, this might not matter. As of this writing, I have no idea if these two features will be available there.
Whack-A-Query
The addition of these two features is pretty neat. I’m excited for them.
I’m also very interested to see how customers react, both from the point of view of adopting SQL Server 2019, and adopting compatibility level 150.
I bet a lot of people are gonna want UDF inlining without having to buy the cow.
Thanks for reading!
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.
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Thanks for watching!
Video Summary
In this video, I dive into some of the exciting new features coming in SQL Server 2019, particularly focusing on batch mode memory grant feedback and adaptive joins. These features are only available if you’re using compatibility level 150, which presents a unique set of considerations for database administrators. I also delve into key lookups, explaining their role in execution plans and how to identify potentially expensive ones using the SQL Server Blitz cache procedures. The discussion then shifts to practical advice on running SSIS and Reporting Services (SSRS) services alongside the SQL Server engine, emphasizing the importance of keeping production environments as clean and efficient as possible. Throughout the video, I share some personal insights, including my ongoing journey towards achieving a long-held goal and reflecting on recent changes in my professional landscape, such as the exciting news about Zane Brunette joining Microsoft. The conversation also touches on the nature of working alone versus being part of a team, with a humorous nod to my gym routine and its impact on my current state of mind—hot, hangry, and ready for some serious database tuning challenges!
Full Transcript
It is so hot. It’s so hot and I’m so annoyed with all the heat. I finally got to eat lunch. Prior to that, I was both hot and hangry at the same time. My hair is sweaty, I’m sorry. I look like garbage. I look like garbage. It’s not fun. It’s not fun. I should probably mention that I’m doing this somewhere. I don’t think I did that. The one person who is here. Thank you for showing up. Thank you for having faith in me. One person who is here. Hopefully you’re not just some Russian bot.
Here to collude. Here to collude with me about things. Oh, and you’re gone. Goodbye, Russian bot. Goodbye forever. Do-do-do-do. There, I’ve tweeted about it now, so no one has an excuse for not showing up.
So, if no one shows up on this beautiful Friday, it’s a hot Friday, then I’m going to talk about something that’s been on my mind lately. And that something is the coming… Off to a good start. I’m not going to say the word coming correctly. The coming reckoning. That’s where a coming came from, was coming reckoning.
The coming reckoning that is due with SQL Server 2019. So, there were things introduced in SQL Server 2017 that I thought were really cool. And that was batch mode memory grant feedback and adaptive joins.
Of course, both of those things were only available if you had columnstore indexes somewhere in near or around the query. So, there were ways to get it legitimately if you had columns run. And then there were some hacky ways to get it regardless.
And those things are being introduced now in SQL Server 2019. And another cool thing that’s coming along in SQL Server 2019 that’s brand new to that product is called Freud. It’s the scalar valued function inlining that our buddy Karthik has been working on brilliantly and doing a brilliant job of.
So, there’s those two things coming. And they are only available if you are in compatibility level 150. Which of course means that if you flip to compatibility level 150, then you are also using that new cardinality estimator.
And that has worked out not so well for a lot of people who I’ve seen start trying to use it. Now, it’s not all the cardinality estimators. Well, they didn’t do testing really.
So, they should have tested before flipping that switch over. But there’s been a lot of consulting calls where someone has said, I’ve upgraded to SQL Server 2014 or 16 or 17. And performance is kind of tanked.
And, you know, when you hop on the phone with them, you start looking at stuff and you say, Oh, well, did you change the compatibility level? And I said, Yeah, of course. We always use the newest one. I said, Well, I have good news for you. With the flip of this one switch, I can restore performance to where it used to be.
Which might not be a good thing or a great thing, but at least you will have the problems that you’re used to on your server. You will not have these brand new problems. So, that’s nice.
But those two features being tied into Compat Level 150 give you a weird choice. It’s the choice between solving potentially a couple big problems with SQL Server. And then also introducing potentially a couple or many more problems by using the new cardinality estimate.
You have to do a lot of very thoughtful, very careful planning to get around that stuff. And what’s crazy is that this is going to be the version, I think, where people have to finally start making those choices. Because not everyone is available to make the kind of changes they need to to get performance right from other perspectives.
So, getting the batch mode stuff and the scalar UDF inlining stuff in is going to be pretty huge. Anyway, that’s it for me. So, Darren says, thanks for the stickers.
Yes, you’re welcome. My pleasure. If you, to anyone who sends me their address, I have Twitter DMs open. If you send me your address, I will send you stickers. I’m happy to do that. Postage costs almost nothing. And I got a very good deal on stickers.
Sticker Mule is a great website for stickers and they often have sales and other things. So, that’s no problem. Darren says, I haven’t looked at CTP3 yet. Did they make any significant changes or have you? I have looked. I did some tweets about new stuff that I found in there.
There was nothing that like blew my mind. There is a weird new use hint. And I’m going to go check on exactly what it’s called so that I don’t get, so I don’t misquote anything here. Excuse me, one of those days I can’t spell select right.
From valid. Is this not valid use hints? And it is called disable loop join avoidance. So, there is a new use like option use hint thing called disable loop join avoidance.
And I’m not exactly sure what it does yet. I have some guesses. I have theorized a couple things. But perhaps it is to make stuff like key lookups more common.
So, there are a lot of queries where you would expect a key lookup to occur and it doesn’t. You get a clustered index scan instead. So, it could be useful for those.
I haven’t put any of these theories to use yet. Mostly because I like having these theories. I dislike having them disproven. Because then I was like, I don’t know what the hell to do. It could also be for columnstore too. Because I know columnstore queries are very apprehensive about doing any kind of key lookups. So, I think it could be for that too.
I don’t know though. I just don’t know yet. I just don’t know yet. We will have to wait and see, won’t we? We will have to eventually wait for Paul White to break out the debugger and figure it out for all of us. Because that is usually what happens, isn’t it?
There is something new or there is something weird going on. And is there anything more comforting or is there anything more reassuring than the words, well, Paul White says. Because you just know that it’s going to be correct. You know that there is a sufficient level of research and care taken in the words that he chooses to share with the rest of us.
That you’re not going to be led astray. So, if there is anything, well, Paul White says. Well, Paul White wrote.
I heard from Paul White. I love it. I love it. It’s a great source. Do we have any other questions? There are eight of you here at least. Are there any other questions?
Nine of you. Wow. Totally watching while at work. Lee, you devil. You dog. Zane says, you may occasionally get your mind broken by Paul White. Yes, that happens quite frequently. It’s a strange thing. What happens when he starts talking.
It’s crazy. It’s like you think that he’s just really good at SQL Server, but he’s good at like many, many things that surround SQL Server as well. And it’s like the stuff that he can also do. So, I’m going to like relate this to like, there are some people who can get good at SQL Server, but it will occupy the entirety of their mind.
Like me, like this is all I can do at once. I can’t have any other hobbies. I can’t have friends.
I can barely walk. And then there are people who like, like SQL Server is like swinging like, like below their weight. It’s like T-ball to them. They can do SQL Server and other stuff. And that’s impressive.
Paul White is one of the people. He can do SQL. He can know a ton about SQL Server and other stuff. It has not pushed all other knowledge out of his brain. And that’s an impressive thing. I have to dedicate everything in this melon. It’s abused melon to SQL Server or else I will not. I will not know any of it.
I will have absolutely nothing. So, it’s always. It’s always fun to watch people who can do this and other things and be good at them. So, yeah, that’s fun stuff, too. All right.
There are a lot of you here. Holy cow. All you playing hooky. No one is at work today. What other questions do we have here? Dan says, are singleton lookups good or bad? I found some indexes that don’t have any seeks or scans in them, but they do have singleton lookups. Singleton lookups just mean that they’re used for key lookups.
They’re not really bad. They’re used for seeks. So, they don’t have any seeks or scans against them, but they do have singleton lookups. All right.
So, yeah. So, usually that just, usually that means that they’re the key, like a primary key or a clustered index or a non-clustered primary key. And they primarily get used for lookup values that are not present in nonclustered indexes. They’re not really a good or bad thing.
I would just peek through the plan cache and I would peek through the plan cache rather. And what I would do is, since you’re using the Blitz procs already, I would say that you should run SP Blitz cache and you should look for a warning called expensive key lookups, which will point to, it will point to if you have an execution plan where a key lookup operation is greater than like 50% of the plan cost, I think. And you could check those out.
So, there are two kinds of key lookups. There are output key lookups where you just go back to the clustered index or the base table if it’s a heap and you just grab columns to show people. So, those are like window dressing.
Those are like columns that you’re just selecting. And then there are predicate key lookups. And predicate key lookups are when you do the same thing except to evaluate a filter. So, if I had, let’s say, an index just on, let’s say I have a clustered index on column one and I have a nonclustered index on column two and my query is like select some column two where column two equals something and column one equals something. I might do a key lookup just to figure out that where clause on column one.
So, for me, fixing the predicate part of key lookups is usually much more important than fixing the output list part. So, unless I’m running into bad parameter sniffing, but working all that stuff out really turns into a very, very long conversation about what exactly is going on with the query. And it’s like, can we rewrite the query?
Do we really need all these columns? Like, is it any frameworks? Is it a sort of a ceiling? Like, there’s a lot of stuff that, like, you need to start working through when you see those things. So, what I would leave at is that there’s nothing inherently wrong with them, but you might want to take a peek through your execution plans and see if you have any expensive key lookups. Again, SV Blitzcache will warn you about that.
And that’s where I would go with it next. Maral says, thoughts on running SSIS and RS services on the same production box as the engine? Yeah, not a fan of that. They take up weird resources and do weird things. I always want to have a different, I always want to have those things on a different server. I’m not crazy about them being on the production box.
Those resources are precious, man. I’ve seen y’all’s production servers. They’re not beefy. They’re not big beefcake servers. And even if they are, you don’t want SSIS and SSRS gobbin’ up the works. It sucks from a licensing perspective, but from a keeping production safe perspective, it is much, much better.
How is my goal of smoking cigarettes in a French graveyard? I am slowly working client by client. I work towards that goal. Client by client.
We’ll see. We’ll see how close we can get. I just need to keep doing it. It would really help if I didn’t live someplace so damn expensive. Oh, man. Yeah.
Yeah. Me and Tara agree on a lot of stuff. That’s why we’re still buddies. At least I hope we are. I don’t know. She might hate me now that I’m consulting competition. She’s working at straight past SQL. And I don’t know. I don’t know.
Maybe we’re at wars. Start licking some shots. Dude, drive by or something. I’m kidding. No, actually, it’s been really nice. Mike Walsh has been great. He’s been offloading. When he has too much work, the poor guy, when he has too much work for his people, he’s been offloading stuff to me.
And so, you know, it’s really nice to have that sort of like, you know, if nothing else is going on, I can, you know, like, hey, Mike, what’d you got? And, you know, have, you know, a few days worth of work to kind of fill up the week, which is always a good thing. So no, no beef there.
No actual beef there. Tara is just very, very busy having to be a remote DBA now. And that’s the lazy consultant. So good for her. That landed in almost the same exact job. That’s a nice, nice touch. Zane posted a link.
Memory management with SSIS and SQL Server can be a large problem. You don’t want to drop pages out of memory because an SSIS package sucks. Yes. And from what I know, most SSIS packages suck. It’s one I’m aware of. Most SSIS packages suck.
Nature of the beast, I suppose. Nature of the beast. Yeah. Not Zane’s own. Zane’s SSIS packages are great. They’re wonderful. Unfortunately, Zane is probably not going to be writing too many SSIS packages anymore.
Mr. Zane Brunette has recently accepted a job as a PFE at Microsoft. He’s going to have the blue badge and the list of trace flags and access to source code and all sorts of other crazy cool things that I’m envious of except for the working at Microsoft part.
I’m very envious of many things that he has going for him except there was a way for me to get those things. And not go to jail, I would do all of those things. And I’m just too happy being by myself.
That’s the problem. I love being alone. It’s wonderful. Love. I love loneliness. Loneliness is great. A lot of people think loneliness is a dirty word. I love it. It’s the best thing in the world. I can do what I want. Wear what I want.
Not wear what I want. Sweatpants at the gym. No two to blame. Yeah. Yeah. Yeah. That’s true. That’s true. I do have to accept the blame for everything. But it’s okay because I’m the only one who notices. I’m very lenient on myself in that regard.
Very lenient. Oh. Yeah, that was fun. So yesterday I was at the gym and I was doing deadlifts because that’s what I usually do. And I was doing some, I was doing like singles, like 475. And I got, well, I was doing doubles at 475 and I got bored.
And I wanted to start doing singles at a higher weight. So I started doing singles at 525 and I videotaped one. And I think it went particularly well. It went like, it was just like, it went up like crazy. You can barely see what’s going on there.
But man, I was very happy with how quick it went up. It’s going, it’s going, it’s going. Ah, come on. Lift it dummy. Why are you so slow? Big fellow walking behind me.
Yeah, that went all up. That went all the way up. I was very happy with that lift. And then I did, I did a few more of those and I was dead. I still think I’m dead. That’s why I’m so hot and hangry today is because it’s tough to recover from doing that. It’s hard.
It’s hard. Your central nervous system is just like yelling at you. It’s like you are the worst. Why do you do that? I don’t know. I don’t know. Mostly just to impress myself. No one else is impressed.
Ah, man. All right. Come on. Give me a, give me a question here. People can’t not have any questions. Why else would you show up to Q and A? You didn’t have questions. You can’t just, you can’t just be here to watch me. I’m not that, I’m not that excited. I’m not that interested.
I’m not that interested. Come on. At least your forehead doesn’t explode like half the horn. Yeah, that’s true. It comes close though. If I, if I strain hard enough, I can get, so I can get, I get this like split vein thing right here.
It looks like, like the flux capacitor. It’s crazy. Uh, yeah. Rogue plates. Can’t do it. If I don’t have, I mean, what other plates do you buy? The only thing that sucks about the gym that I go to is they don’t have like the, like the, like real Ollie, like thin plates.
It’s all like the thick, the thick rubbery ones, not like the thin and ones. So like, if you have like six blue plates on the bar, that’s all you can do. Like, and then if you want to do like more, you have to like take off some of like the good rogue plates and put on like some of the, the metal octagon plates. And that’s, I mean, it’s, it’s not that they’re bad.
It’s just, you know, it breaks up. It breaks up how cool it looks. And when you’re OCD like me, and you just want to have a bar, same, like, damn it. Give me, do this. Let’s see the thoughts on diagnosing network connectivity issues other than trying to tell net via command prompt or using the deck.
So is it like you set up a SQL Server and you just can’t connect to it off the bat or like queries are timing out getting weird? Cause it’s like, you just set up a SQL Server. The first things I always do is check firewall rules.
If it’s a named instance, I make sure that the browser service is running. I’ve also learned to very, very, very carefully check the instance names. Because there, there was a, there was an, there was an instance around the time. Oh, I don’t know when CTP three came out when I spent 20 minutes trying to connect to the wrong server name.
So that was, that was great. Random users and apps can’t connect. The most common thing, most common reason that I’ve seen for random users and apps not being able to connect is thread pool.
Thread pool is when you run out of worker threads and you can no longer assign even like a session ID to a query. And that’s a pretty bad thing. So what I would do is if you wanted to run like SP blitz on your server, you might get a warning about thread pool weights.
If you don’t have that or can’t run that, then you can, you can run pretty much just any weight stat aggregating script and see if you have thread pool weights on there. Like lining up because that’s usually a sign that bad things are happening. Thread pool generally happens if like you have a bunch of parallel queries coming in and trying to run stuff at once.
Or if you have like a big blocking chain or something and you know, stuff like if you have a monitoring tool, you might see big blank spots in the monitoring tool too, because that thing can’t, that thing needs threads too. And it can’t connect. So there’s a lot of stuff, a lot of reasons why that might happen, but thread pool is among chief among them.
Stop naming your instances. Oh, yeah. Woo woo. Stop naming your instances. Weird things. I named my instance CTP and I thought that I had named it CTP two in the past.
And so I was trying to connect to CTP two and I couldn’t. And I guess kept trying to like, damn it, what’s going on? And I was like, oh yeah, it’s just CTP. No two. And I did that.
Everything worked and it was magical. I felt it was a big, big, big win for me there. It’s like amazing. Let’s, let’s sanity check. I never sanity check first. It’s always like the fourth or fifth thing I do. And then I feel dumb because I’m like, through all these years, why isn’t that the first thing that you do? Like, I’m not crazy.
I can’t be crazy. I know I did this right. Oh, yeah. Dan has a good point. I do have a script. Now you’re going to make me go find that. Let’s see if I can remember how to type.
There we go. I can even get to, I can even get to my own website. Paul Randall even said, this is a nice script.
If you check the comments, you check the comments. There’s a thing that says from Paul, comment from Paul Randall that says, nice script. And then I felt bad because like a week later, someone posted a question about wait stats on the DBA stack exchange site. And they were like, I ran this script and they paid they pasted the script in but it was like just the script was like just the queries from the script and they were like, I ran this script.
And it’s telling me that I have like 86% CX packet or something. And I was like 86% of what in my head. I’m like, damn it.
Why do you do? And so like I write this answer. I’m like, the reason why that script stinks is because it doesn’t tell you like what the uptime of the server is. You don’t know what 86% of, you know, like, like if you have like, you know, like three days of CX packet weights, but your server’s only been up for a half hour. That’s way different than if like you have three days of CX pack away.
So it serves enough for a month. And I didn’t like, cause you know, working for Brent for the last four years, I didn’t, I haven’t spent a lot of time looking at other people’s scripts. It was always very much like, you know, write scripts from scratch, like do stuff on your own. Don’t look at other people’s stuff.
You don’t want to like get, get caught like influencing or have anyone say you stole that script I posted on the blog. You suck. And so I was like, okay, no problem. I’ll write everything. So I don’t remember what, what Paula scripts looked like. And so like, like a few hours later on my answer, there was, there was like, there was a comment from Paul.
And that’s when, that’s when I started thinking, I was like, why would Paul randomly comment on this, this answer? And then I went and I looked and I was like, oh shit, that’s a script. It’s like, eh.
So I know he’s not watching, but sorry about that, Paul. Honest mistake. It wasn’t, it wasn’t really, I wasn’t trying to make fun of you. I was just trying to help someone. Anyway, I can’t remember if I’ve told that story on here before.
I remember telling someone that story before. I forget, I forget when it was. Anyway, could have been here. It could have not been. Don’t tell me if it was, cause I don’t want to get embarrassed. Don’t tell me. Don’t tell me anything. Don’t tell me when I forget things. There’s nothing more embarrassing than forgetting things.
Let’s see. Anything on Twitter. No. So I, on Twitter, I made a prediction that. If SQL Server 2019 does not increase the RAM cap. It really should increase the RAM cap to 256 gigs of RAM.
128 is nothing these days. Nothing. My desktop, this thing that I can kick. Willy nilly has 128 gigs of RAM. There’s no reason to expect. A.
Production SQL Server instance, even standard edition to be capped to 128 gigs of RAM. It also helps because there are very strange instance sizes up in the cloud. They’re very oddly sized with the CPU to RAM ratio. And I think.
There are, there are many situations. There are many servers that’s see that standard edition would make sense on if it could have 256. Gigs of memory, but don’t make sense. Um, because it only accepts 128. Like there are some 16 core instances up there. When, or like 16 to like 24 core instances up there that have.
Like 256. Or like 200 and some 66. Some like weird number gigs of memory, which is, which sounds great. And you’re like, man, standard edition would be cool to put on there, but. I would have.
A hundred or so gigs of RAM. Sitting around. No, I, I, I get that. The differentiation, but they’re like that number is the cap on the buffer pool and you can use stuff above the buffer pool. I just don’t see a lot of people who are like, I only need to cash. 228 gigs of memory of stuff in the buffer pool, but boy, howdy.
I sure wish I had a hundred gigs for memory grants and the plan cash and other stuff. I just, I don’t, I don’t meet those people terribly often. So I think that having standard edition go to 256 gigs of memory would be an excellent thing. But anyway, I was saying that my prediction was if it doesn’t have, if they don’t increase the RAM cap or if they don’t make the scale or UDF inlining or what do you call it?
Batch mode for rowstore available on standard edition, then I think adoption is going to suck. Going to continue to stifle adoption because the salespeople are jerks.
They really got to cut that crap out. Not helping anyone. Not doing yourself any favors. Not, you know, you’re not selling more enterprise licenses by doing that. You’re keeping more people on the old licenses as they already have.
People just refuse to upgrade. I know I knew people on SQL Server 2008 standard edition who would not upgrade to 2008 R2 because in 2008 there was no cap on memory. 2008 R2 is when standard edition got the 64 gig cap. So there are people who, clients who I knew stayed on 2008.
Probably still on 2008 because there was no cap on memory. They could put in as much as they wanted. No. I don’t know. That’s what I do.
But, you know, I’m free spirited. Might not fit in well with that corporate culture. Anyway, we’re about at the half hour mark. It’s very hot. It’s very hot.
Very hot in here. And I want to stop being hot. So I’m going to get these headphones off and step away and sit down and drink some water. Anyway, thanks for joining. I hope you at least had a good time. Anyway, I’ll catch you next week because as far as I know I’ll still be here.
It’s amazing. Friday after Friday. It’s nuts. Goodbye.
Video Summary
In this video, I dive into some of the exciting new features coming in SQL Server 2019, particularly focusing on batch mode memory grant feedback and adaptive joins. These features are only available if you’re using compatibility level 150, which presents a unique set of considerations for database administrators. I also delve into key lookups, explaining their role in execution plans and how to identify potentially expensive ones using the SQL Server Blitz cache procedures. The discussion then shifts to practical advice on running SSIS and Reporting Services (SSRS) services alongside the SQL Server engine, emphasizing the importance of keeping production environments as clean and efficient as possible. Throughout the video, I share some personal insights, including my ongoing journey towards achieving a long-held goal and reflecting on recent changes in my professional landscape, such as the exciting news about Zane Brunette joining Microsoft. The conversation also touches on the nature of working alone versus being part of a team, with a humorous nod to my gym routine and its impact on my current state of mind—hot, hangry, and ready for some serious database tuning challenges!
Full Transcript
It is so hot. It’s so hot and I’m so annoyed with all the heat. I finally got to eat lunch. Prior to that, I was both hot and hangry at the same time. My hair is sweaty, I’m sorry. I look like garbage. I look like garbage. It’s not fun. It’s not fun. I should probably mention that I’m doing this somewhere. I don’t think I did that. The one person who is here. Thank you for showing up. Thank you for having faith in me. One person who is here. Hopefully you’re not just some Russian bot.
Here to collude. Here to collude with me about things. Oh, and you’re gone. Goodbye, Russian bot. Goodbye forever. Do-do-do-do. There, I’ve tweeted about it now, so no one has an excuse for not showing up.
So, if no one shows up on this beautiful Friday, it’s a hot Friday, then I’m going to talk about something that’s been on my mind lately. And that something is the coming… Off to a good start. I’m not going to say the word coming correctly. The coming reckoning. That’s where a coming came from, was coming reckoning.
The coming reckoning that is due with SQL Server 2019. So, there were things introduced in SQL Server 2017 that I thought were really cool. And that was batch mode memory grant feedback and adaptive joins.
Of course, both of those things were only available if you had columnstore indexes somewhere in near or around the query. So, there were ways to get it legitimately if you had columns run. And then there were some hacky ways to get it regardless.
And those things are being introduced now in SQL Server 2019. And another cool thing that’s coming along in SQL Server 2019 that’s brand new to that product is called Freud. It’s the scalar valued function inlining that our buddy Karthik has been working on brilliantly and doing a brilliant job of.
So, there’s those two things coming. And they are only available if you are in compatibility level 150. Which of course means that if you flip to compatibility level 150, then you are also using that new cardinality estimator.
And that has worked out not so well for a lot of people who I’ve seen start trying to use it. Now, it’s not all the cardinality estimators. Well, they didn’t do testing really.
So, they should have tested before flipping that switch over. But there’s been a lot of consulting calls where someone has said, I’ve upgraded to SQL Server 2014 or 16 or 17.
And performance is kind of tanked. And, you know, when you hop on the phone with them, you start looking at stuff and you say, Oh, well, did you change the compatibility level?
And I said, Yeah, of course. We always use the newest one. I said, Well, I have good news for you. With the flip of this one switch, I can restore performance to where it used to be. Which might not be a good thing or a great thing, but at least you will have the problems that you’re used to on your server.
You will not have these brand new problems. So, that’s nice. But those two features being tied into Compat Level 150 give you a weird choice.
It’s the choice between solving potentially a couple big problems with SQL Server. And then also introducing potentially a couple or many more problems by using the new cardinality estimate. You have to do a lot of very thoughtful, very careful planning to get around that stuff.
And what’s crazy is that this is going to be the version, I think, where people have to finally start making those choices. Because not everyone is available to make the kind of changes they need to to get performance right from other perspectives. So, getting the batch mode stuff and the scalar UDF inlining stuff in is going to be pretty huge.
Anyway, that’s it for me. So, Darren says, thanks for the stickers. Yes, you’re welcome.
My pleasure. If you, to anyone who sends me their address, I have Twitter DMs open. If you send me your address, I will send you stickers. I’m happy to do that. Postage costs almost nothing.
And I got a very good deal on stickers. Sticker Mule is a great website for stickers and they often have sales and other things. So, that’s no problem.
Darren says, I haven’t looked at CTP3 yet. Did they make any significant changes or have you? I have looked. I did some tweets about new stuff that I found in there. There was nothing that like blew my mind.
There is a weird new use hint. And I’m going to go check on exactly what it’s called so that I don’t get, so I don’t misquote anything here. Excuse me, one of those days I can’t spell select right.
From valid. Is this not valid use hints? And it is called disable loop join avoidance.
So, there is a new use like option use hint thing called disable loop join avoidance. And I’m not exactly sure what it does yet. I have some guesses.
I have theorized a couple things. But perhaps it is to make stuff like key lookups more common. So, there are a lot of queries where you would expect a key lookup to occur and it doesn’t.
You get a clustered index scan instead. So, it could be useful for those. I haven’t put any of these theories to use yet.
Mostly because I like having these theories. I dislike having them disproven. Because then I was like, I don’t know what the hell to do. It could also be for columnstore too. Because I know columnstore queries are very apprehensive about doing any kind of key lookups.
So, I think it could be for that too. I don’t know though. I just don’t know yet. I just don’t know yet.
We will have to wait and see, won’t we? We will have to eventually wait for Paul White to break out the debugger and figure it out for all of us. Because that is usually what happens, isn’t it?
There is something new or there is something weird going on. And is there anything more comforting or is there anything more reassuring than the words, well, Paul White says. Because you just know that it’s going to be correct.
You know that there is a sufficient level of research and care taken in the words that he chooses to share with the rest of us. That you’re not going to be led astray. So, if there is anything, well, Paul White says.
Well, Paul White wrote. I heard from Paul White. I love it.
I love it. It’s a great source. Do we have any other questions? There are eight of you here at least. Are there any other questions? Nine of you. Wow.
Totally watching while at work. Lee, you devil. You dog. Zane says, you may occasionally get your mind broken by Paul White. Yes, that happens quite frequently. It’s a strange thing.
What happens when he starts talking. It’s crazy. It’s like you think that he’s just really good at SQL Server, but he’s good at like many, many things that surround SQL Server as well. And it’s like the stuff that he can also do.
So, I’m going to like relate this to like, there are some people who can get good at SQL Server, but it will occupy the entirety of their mind. Like me, like this is all I can do at once. I can’t have any other hobbies.
I can’t have friends. I can barely walk. And then there are people who like, like SQL Server is like swinging like, like below their weight. It’s like T-ball to them.
They can do SQL Server and other stuff. And that’s impressive. Paul White is one of the people. He can do SQL. He can know a ton about SQL Server and other stuff. It has not pushed all other knowledge out of his brain.
And that’s an impressive thing. I have to dedicate everything in this melon. It’s abused melon to SQL Server or else I will not. I will not know any of it.
I will have absolutely nothing. So, it’s always. It’s always fun to watch people who can do this and other things and be good at them. So, yeah, that’s fun stuff, too.
All right. There are a lot of you here. Holy cow. All you playing hooky. No one is at work today. What other questions do we have here?
Dan says, are singleton lookups good or bad? I found some indexes that don’t have any seeks or scans in them, but they do have singleton lookups. Singleton lookups just mean that they’re used for key lookups.
They’re not really bad. They’re used for seeks. So, they don’t have any seeks or scans against them, but they do have singleton lookups. All right.
So, yeah. So, usually that just, usually that means that they’re the key, like a primary key or a clustered index or a non-clustered primary key. And they primarily get used for lookup values that are not present in nonclustered indexes.
They’re not really a good or bad thing. I would just peek through the plan cache and I would peek through the plan cache rather. And what I would do is, since you’re using the Blitz procs already, I would say that you should run SP Blitz cache and you should look for a warning called expensive key lookups, which will point to, it will point to if you have an execution plan where a key lookup operation is greater than like 50% of the plan cost, I think.
And you could check those out. So, there are two kinds of key lookups. There are output key lookups where you just go back to the clustered index or the base table if it’s a heap and you just grab columns to show people.
So, those are like window dressing. Those are like columns that you’re just selecting. And then there are predicate key lookups.
And predicate key lookups are when you do the same thing except to evaluate a filter. So, if I had, let’s say, an index just on, let’s say I have a clustered index on column one and I have a nonclustered index on column two and my query is like select some column two where column two equals something and column one equals something. I might do a key lookup just to figure out that where clause on column one.
So, for me, fixing the predicate part of key lookups is usually much more important than fixing the output list part. So, unless I’m running into bad parameter sniffing, but working all that stuff out really turns into a very, very long conversation about what exactly is going on with the query. And it’s like, can we rewrite the query?
Do we really need all these columns? Like, is it any frameworks? Is it a sort of a ceiling? Like, there’s a lot of stuff that, like, you need to start working through when you see those things. So, what I would leave at is that there’s nothing inherently wrong with them, but you might want to take a peek through your execution plans and see if you have any expensive key lookups.
Again, SV Blitzcache will warn you about that. And that’s where I would go with it next. Maral says, thoughts on running SSIS and RS services on the same production box as the engine?
Yeah, not a fan of that. They take up weird resources and do weird things. I always want to have a different, I always want to have those things on a different server. I’m not crazy about them being on the production box.
Those resources are precious, man. I’ve seen y’all’s production servers. They’re not beefy. They’re not big beefcake servers. And even if they are, you don’t want SSIS and SSRS gobbin’ up the works.
It sucks from a licensing perspective, but from a keeping production safe perspective, it is much, much better. How is my goal of smoking cigarettes in a French graveyard? I am slowly working client by client.
I work towards that goal. Client by client. We’ll see. We’ll see how close we can get. I just need to keep doing it. It would really help if I didn’t live someplace so damn expensive.
Oh, man. Yeah. Yeah.
Me and Tara agree on a lot of stuff. That’s why we’re still buddies. At least I hope we are. I don’t know. She might hate me now that I’m consulting competition. She’s working at straight past SQL. And I don’t know.
I don’t know. Maybe we’re at wars. Start licking some shots. Dude, drive by or something. I’m kidding.
No, actually, it’s been really nice. Mike Walsh has been great. He’s been offloading. When he has too much work, the poor guy, when he has too much work for his people, he’s been offloading stuff to me. And so, you know, it’s really nice to have that sort of like, you know, if nothing else is going on, I can, you know, like, hey, Mike, what’d you got?
And, you know, have, you know, a few days worth of work to kind of fill up the week, which is always a good thing. So no, no beef there. No actual beef there.
Tara is just very, very busy having to be a remote DBA now. And that’s the lazy consultant. So good for her. That landed in almost the same exact job. That’s a nice, nice touch.
Zane posted a link. Memory management with SSIS and SQL Server can be a large problem. You don’t want to drop pages out of memory because an SSIS package sucks. Yes.
And from what I know, most SSIS packages suck. It’s one I’m aware of. Most SSIS packages suck. Nature of the beast, I suppose.
Nature of the beast. Yeah. Not Zane’s own. Zane’s SSIS packages are great. They’re wonderful. Unfortunately, Zane is probably not going to be writing too many SSIS packages anymore.
Mr. Zane Brunette has recently accepted a job as a PFE at Microsoft. He’s going to have the blue badge and the list of trace flags and access to source code and all sorts of other crazy cool things that I’m envious of except for the working at Microsoft part.
I’m very envious of many things that he has going for him except there was a way for me to get those things. And not go to jail, I would do all of those things. And I’m just too happy being by myself.
That’s the problem. I love being alone. It’s wonderful. Love. I love loneliness. Loneliness is great. A lot of people think loneliness is a dirty word. I love it.
It’s the best thing in the world. I can do what I want. Wear what I want. Not wear what I want. Sweatpants at the gym. No two to blame.
Yeah. Yeah. Yeah. That’s true. That’s true. I do have to accept the blame for everything. But it’s okay because I’m the only one who notices. I’m very lenient on myself in that regard.
Very lenient. Oh. Yeah, that was fun. So yesterday I was at the gym and I was doing deadlifts because that’s what I usually do. And I was doing some, I was doing like singles, like 475.
And I got, well, I was doing doubles at 475 and I got bored. And I wanted to start doing singles at a higher weight. So I started doing singles at 525 and I videotaped one.
And I think it went particularly well. It went like, it was just like, it went up like crazy. You can barely see what’s going on there. But man, I was very happy with how quick it went up.
It’s going, it’s going, it’s going. Ah, come on. Lift it dummy. Why are you so slow? Big fellow walking behind me.
Yeah, that went all up. That went all the way up. I was very happy with that lift. And then I did, I did a few more of those and I was dead. I still think I’m dead.
That’s why I’m so hot and hangry today is because it’s tough to recover from doing that. It’s hard. It’s hard.
Your central nervous system is just like yelling at you. It’s like you are the worst. Why do you do that? I don’t know. I don’t know. Mostly just to impress myself.
No one else is impressed. Ah, man. All right. Come on. Give me a, give me a question here. People can’t not have any questions. Why else would you show up to Q and A?
You didn’t have questions. You can’t just, you can’t just be here to watch me. I’m not that, I’m not that excited. I’m not that interested. I’m not that interested. Come on.
At least your forehead doesn’t explode like half the horn. Yeah, that’s true. It comes close though. If I, if I strain hard enough, I can get, so I can get, I get this like split vein thing right here.
It looks like, like the flux capacitor. It’s crazy. Uh, yeah. Rogue plates. Can’t do it. If I don’t have, I mean, what other plates do you buy? The only thing that sucks about the gym that I go to is they don’t have like the, like the, like real Ollie, like thin plates.
It’s all like the thick, the thick rubbery ones, not like the thin and ones. So like, if you have like six blue plates on the bar, that’s all you can do. Like, and then if you want to do like more, you have to like take off some of like the good rogue plates and put on like some of the, the metal octagon plates.
And that’s, I mean, it’s, it’s not that they’re bad. It’s just, you know, it breaks up. It breaks up how cool it looks.
And when you’re OCD like me, and you just want to have a bar, same, like, damn it. Give me, do this. Let’s see the thoughts on diagnosing network connectivity issues other than trying to tell net via command prompt or using the deck.
So is it like you set up a SQL Server and you just can’t connect to it off the bat or like queries are timing out getting weird? Cause it’s like, you just set up a SQL Server. The first things I always do is check firewall rules.
If it’s a named instance, I make sure that the browser service is running. I’ve also learned to very, very, very carefully check the instance names. Because there, there was a, there was an, there was an instance around the time.
Oh, I don’t know when CTP three came out when I spent 20 minutes trying to connect to the wrong server name. So that was, that was great. Random users and apps can’t connect.
The most common thing, most common reason that I’ve seen for random users and apps not being able to connect is thread pool. Thread pool is when you run out of worker threads and you can no longer assign even like a session ID to a query. And that’s a pretty bad thing.
So what I would do is if you wanted to run like SP blitz on your server, you might get a warning about thread pool weights. If you don’t have that or can’t run that, then you can, you can run pretty much just any weight stat aggregating script and see if you have thread pool weights on there. Like lining up because that’s usually a sign that bad things are happening.
Thread pool generally happens if like you have a bunch of parallel queries coming in and trying to run stuff at once. Or if you have like a big blocking chain or something and you know, stuff like if you have a monitoring tool, you might see big blank spots in the monitoring tool too, because that thing can’t, that thing needs threads too. And it can’t connect.
So there’s a lot of stuff, a lot of reasons why that might happen, but thread pool is among chief among them. Stop naming your instances. Oh, yeah. Woo woo.
Stop naming your instances. Weird things. I named my instance CTP and I thought that I had named it CTP two in the past. And so I was trying to connect to CTP two and I couldn’t.
And I guess kept trying to like, damn it, what’s going on? And I was like, oh yeah, it’s just CTP. No two. And I did that. Everything worked and it was magical.
I felt it was a big, big, big win for me there. It’s like amazing. Let’s, let’s sanity check. I never sanity check first. It’s always like the fourth or fifth thing I do. And then I feel dumb because I’m like, through all these years, why isn’t that the first thing that you do?
Like, I’m not crazy. I can’t be crazy. I know I did this right. Oh, yeah.
Dan has a good point. I do have a script. Now you’re going to make me go find that. Let’s see if I can remember how to type. There we go.
I can even get to, I can even get to my own website. Paul Randall even said, this is a nice script. If you check the comments, you check the comments.
There’s a thing that says from Paul, comment from Paul Randall that says, nice script. And then I felt bad because like a week later, someone posted a question about wait stats on the DBA stack exchange site. And they were like, I ran this script and they paid they pasted the script in but it was like just the script was like just the queries from the script and they were like, I ran this script.
And it’s telling me that I have like 86% CX packet or something. And I was like 86% of what in my head. I’m like, damn it.
Why do you do? And so like I write this answer. I’m like, the reason why that script stinks is because it doesn’t tell you like what the uptime of the server is. You don’t know what 86% of, you know, like, like if you have like, you know, like three days of CX packet weights, but your server’s only been up for a half hour. That’s way different than if like you have three days of CX pack away.
So it serves enough for a month. And I didn’t like, cause you know, working for Brent for the last four years, I didn’t, I haven’t spent a lot of time looking at other people’s scripts. It was always very much like, you know, write scripts from scratch, like do stuff on your own.
Don’t look at other people’s stuff. You don’t want to like get, get caught like influencing or have anyone say you stole that script I posted on the blog. You suck.
And so I was like, okay, no problem. I’ll write everything. So I don’t remember what, what Paula scripts looked like. And so like, like a few hours later on my answer, there was, there was like, there was a comment from Paul.
And that’s when, that’s when I started thinking, I was like, why would Paul randomly comment on this, this answer? And then I went and I looked and I was like, oh shit, that’s a script. It’s like, eh.
So I know he’s not watching, but sorry about that, Paul. Honest mistake. It wasn’t, it wasn’t really, I wasn’t trying to make fun of you.
I was just trying to help someone. Anyway, I can’t remember if I’ve told that story on here before. I remember telling someone that story before.
I forget, I forget when it was. Anyway, could have been here. It could have not been. Don’t tell me if it was, cause I don’t want to get embarrassed. Don’t tell me. Don’t tell me anything.
Don’t tell me when I forget things. There’s nothing more embarrassing than forgetting things. Let’s see. Anything on Twitter. No. So I, on Twitter, I made a prediction that. If SQL Server 2019 does not increase the RAM cap.
It really should increase the RAM cap to 256 gigs of RAM. 128 is nothing these days. Nothing.
My desktop, this thing that I can kick. Willy nilly has 128 gigs of RAM. There’s no reason to expect. A.
Production SQL Server instance, even standard edition to be capped to 128 gigs of RAM. It also helps because there are very strange instance sizes up in the cloud. They’re very oddly sized with the CPU to RAM ratio.
And I think. There are, there are many situations. There are many servers that’s see that standard edition would make sense on if it could have 256. Gigs of memory, but don’t make sense.
Um, because it only accepts 128. Like there are some 16 core instances up there. When, or like 16 to like 24 core instances up there that have.
Like 256. Or like 200 and some 66. Some like weird number gigs of memory, which is, which sounds great. And you’re like, man, standard edition would be cool to put on there, but.
I would have. A hundred or so gigs of RAM. Sitting around. No, I, I, I get that.
The differentiation, but they’re like that number is the cap on the buffer pool and you can use stuff above the buffer pool. I just don’t see a lot of people who are like, I only need to cash. 228 gigs of memory of stuff in the buffer pool, but boy, howdy.
I sure wish I had a hundred gigs for memory grants and the plan cash and other stuff. I just, I don’t, I don’t meet those people terribly often. So I think that having standard edition go to 256 gigs of memory would be an excellent thing.
But anyway, I was saying that my prediction was if it doesn’t have, if they don’t increase the RAM cap or if they don’t make the scale or UDF inlining or what do you call it? Batch mode for rowstore available on standard edition, then I think adoption is going to suck.
Going to continue to stifle adoption because the salespeople are jerks. They really got to cut that crap out. Not helping anyone. Not doing yourself any favors.
Not, you know, you’re not selling more enterprise licenses by doing that. You’re keeping more people on the old licenses as they already have. People just refuse to upgrade.
I know I knew people on SQL Server 2008 standard edition who would not upgrade to 2008 R2 because in 2008 there was no cap on memory. 2008 R2 is when standard edition got the 64 gig cap.
So there are people who, clients who I knew stayed on 2008. Probably still on 2008 because there was no cap on memory. They could put in as much as they wanted.
No. I don’t know. That’s what I do. But, you know, I’m free spirited.
Might not fit in well with that corporate culture. Anyway, we’re about at the half hour mark. It’s very hot.
It’s very hot. Very hot in here. And I want to stop being hot. So I’m going to get these headphones off and step away and sit down and drink some water. Anyway, thanks for joining.
I hope you at least had a good time. Anyway, I’ll catch you next week because as far as I know I’ll still be here. It’s amazing.
Friday after Friday. It’s nuts. 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.
Despite being a bouncer for many years, I have no interest at all in security.
Users, logins, roles, grant, deny. Not for me. I’ve seen those posters, and they’re terrifying.
Gimme 3000 lines of dynamic SQL any day.
This is a slightly different take on yesterday’s post, which is also a common problem I see in queries today.
Someone wrote a function to figure out if a user is trusted, or has the right permissions, and sticks it in a predicate — it could be a join or where clause.
High Finance
Stack Overflow isn’t exactly a big four accounting firm, but for some reason big four accounting firms don’t make their databases public under Creative Commons licensing.
So uh. Here we are.
And here’s our query.
DECLARE @UserId INT = 22656, --2788872, 22656
@SQL NVARCHAR(MAX) = N'';
SET @SQL = @SQL + N'
SELECT p.Id,
p.AcceptedAnswerId,
p.AnswerCount,
p.CommentCount,
p.CreationDate,
p.FavoriteCount,
p.LastActivityDate,
p.OwnerUserId,
p.Score,
p.ViewCount,
v.BountyAmount,
c.Score
FROM dbo.Posts AS p
LEFT JOIN dbo.Votes AS v
ON p.Id = v.PostId
AND dbo.isTrusted(@iUserId) = 1
LEFT JOIN dbo.Comments AS c
ON p.Id = c.PostId
WHERE p.PostTypeId = 5;
';
EXEC sys.sp_executesql @SQL,
N'@iUserId INT',
@iUserId = @UserId;
There’s a function in that join to the Votes table. This is what it looks like.
CREATE OR ALTER FUNCTION dbo.isTrusted ( @UserId INT )
RETURNS BIT
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @Bitty BIT;
SELECT @Bitty = CASE WHEN u.Reputation >= 10000
THEN 1
ELSE 0
END
FROM dbo.Users AS u
WHERE u.Id = @UserId;
RETURN @Bitty;
END;
GO
Bankrupt
There’s not a lot of importance in the indexes, query plans, or reads.
What’s great about this is that you don’t need to do a lot of analysis — we can look purely at runtimes.
It also doesn’t matter if we run the query for a trusted (22656) or untrusted (2788872) user.
In compat level 140, the runtimes look like this:
ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 140;
SQL Server Execution Times:
CPU time = 7219 ms, elapsed time = 9925 ms.
SQL Server Execution Times:
CPU time = 7234 ms, elapsed time = 9903 ms.
In compat level 150, the runtimes look like this:
ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 150;
SQL Server Execution Times:
CPU time = 2734 ms, elapsed time = 781 ms.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 142 ms.
In both runs, the trusted user is first, and the untrusted user is second.
Sure, the trusted user query ran half a second longer, but that’s because it actually had to produce data in the join.
One important thing to note is that the query was able to take advantage of parallelism when it should have (CPU time is higher than elapsed time).
In older versions (or even lower compat levels), scalar valued functions would inhibit parallelism. Now they don’t when they’re inlined.
Thanks for reading!
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.
I’m going to use a funny example to show you something weird that I see often in EF queries.
I’m not going to use EF to do it, because I have no idea how to. Please use your vast imagination.
In this case, I’m going to figure out if a user is trusted, and only if they are will I show them certain information.
Here goes!
Trust Bust
The first part of the query establishes if the user is trusted or not.
I think this is silly because no one should ever trust users.
DECLARE @UserId INT = 22656, --2788872
@PostId INT = 11227809,
@IsTrusted BIT = 0,
@SQL NVARCHAR(MAX) = N'';
SELECT @IsTrusted = CASE WHEN u.Reputation >= 10000
THEN 1
ELSE 0
END
FROM dbo.Users AS u
WHERE u.Id = @UserId;
The second part will query and join a few tables, but one of the joins (to the Votes table) will only run if a user is trusted.
SET @SQL = @SQL + N'
SELECT p.Title, p.Score,
c.Text, c.Score,
v.*
FROM dbo.Posts AS p
LEFT JOIN dbo.Comments AS c
ON p.Id = c.PostId
LEFT JOIN dbo.Votes AS v
ON p.Id = v.PostId
AND 1 = @iIsTrusted
WHERE p.Id = @iPostId
AND p.PostTypeId = 1;
';
EXEC sys.sp_executesql @SQL,
N'@iIsTrusted BIT, @iPostId INT',
@iIsTrusted = @IsTrusted,
@iPostId = @PostId;
See where 1 = @iIsTrusted? That determines if the join runs at all.
Needless to say, adding an entire join in to the query might slow things down if we’re not prepared.
First I’m going to run it for user 2788872, who isn’t trusted.
This query finishes rather quickly (2 seconds), and has an interesting operator in it.
Henanigans, S.Pump the brakes
The filter has a startup expression in it, which means it’s sort of a gatekeeper, here. If the parameter is 0, we don’t touch Votes.
If it’s 1… Boy, do we touch Votes. This is another case of where cached plans can lie to us.
Rep Up
If we run this for user 22656 (Jon Skeet) afterwards, we will definitely need to touch the Votes table.
I grabbed the Live Query Plan to show you just how little progress it makes over 5 minutes.
Dirge
The cached plan will look identical. And looking at the plan, it’ll be hard to believe there’s any way it could run >5 minutes.
CONFESS
If we clear the cache and run this for 22656 first, the plan runs relatively quickly, and looks a little different.
Bag of Ice
Running it for an untrusted user has a similar runtime. It’s not great, but it’s the better of the two.
Fixing It?
It’s difficult to control EF queries with much granularity.
You could branch the application code to run two different queries based on if a user is trusted.
In a perfect world, you’d never even consider that join at all, and avoid having to worry about it.
On the plus side (at least in this case), the good plan for trusted users runs in the same time as the good plan for untrusted users, even though they’re different.
If you’re feeling extra confident, you can try adding an OPTIMIZE FOR hint to your code, or implementing a plan guide.
Thanks for reading!
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.
A lot of people I’ve talked to about dynamic SQL have been under the misguided impression that just using sp_executesql will fix safety issues with SQL injection.
In reality, it’s only half the battle. The other half is learning how to act sober.
The gripes I hear about fully fixing dynamic SQL are:
The syntax is hard to remember (setting up and calling parameters)
It might lead to parameter sniffing issues
I can sympathize with both. Trading one problem for another problem generally isn’t something people get excited about.
Trading all the money in your company bank account to ransom your database probably isn’t something you’d get excited about either.
That’s not a very good lead on your rezoomay.
Holic
Here’s a trivial example:
CREATE TABLE dbo.DropMe(id INT);
DECLARE @DatabaseName sysname = N'';
SET @DatabaseName = N'S%'';DROP TABLE dbo.DropMe;--';
DECLARE @sql NVARCHAR(MAX) = N'
SELECT *
FROM sys.databases AS d
WHERE d.name LIKE ''%' + @DatabaseName + '%'';
';
PRINT @sql;
EXEC sys.sp_executesql @sql;
This will not only return a list of database names that contain S on my instance, but the printed SQL statement shows the whole string is executed.
SELECT *
FROM sys.databases AS d
WHERE d.name LIKE '%S%';DROP TABLE dbo.DropMe;--%';
Blue Flowers
The only way to not have that happen is to do this, and this is where people start complaining about remembering syntax:
CREATE TABLE dbo.DropMe(id INT);
DECLARE @DatabaseName sysname = N'';
SET @DatabaseName = N'S%'';DROP TABLE dbo.DropMe;--';
DECLARE @sql NVARCHAR(MAX) = N'
SELECT *
FROM sys.databases AS d
WHERE d.name LIKE ''%@iDatabaseName%'';
';
PRINT @sql;
EXEC sys.sp_executesql @sql,
N'@iDatabaseName sysname',
@iDatabaseName = @DatabaseName;
What prints out is this:
SELECT *
FROM sys.databases AS d
WHERE d.name LIKE '%@iDatabaseName%';
There’s also no search result returned, because no database is currently named ‘S%”;DROP TABLE dbo.DropMe;–‘.
But I get why people think this is annoying, because it is quirky at first.
If the string you use to encapsulate your parameters isn’t NVARCHAR, and/OR prefixed with N, you’ll get an error.
If you put your dynamic SQL variables on the wrong side of the equal sign, you’ll get an error.
And yes, if you’ve got skewed data, you’ll be more open to parameter sniffing.
The syntax stuff just takes a little getting used to, and performance stuff is often easier to fix than lost, stolen, or vandalized data.
Even if you’re real comfy with your backups, you’re still at risk of someone stealing confidential data.
Data Is A Liability
It’s really important that you review the personal data you collect to make sure it’s totally necessary.
It’s also really important for you to regularly archive data that you don’t actively need in your database.
For everything else, taking precautions like fixing unsafe dynamic SQL is just part of mitigating your data liabilities.
Thanks for reading!
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.
There’s often a lot of shouting about this brand new thing that you simply must learn, lest ye perish in the unholy flames of obsoletion.
Having worked with SQL Server for a while now, I’ve heard it about a great many things.
I’m going to be honest: Those great many things have never called to me.
There’s no need to list them all out. It’d take me longer than I’d like, anyway.
But I’ll tell you something funny: I’ve never opened SSIS, SSAS, or SSRS.
Don’t even know how to. I’m quite happy other people have found their passion with them.
It’s just not me.
Catching Flies
The very specific thing that calls to me is performance tuning, by way of understanding the query optimizer.
That’s quite enough to keep me busy. There’s a ton to learn, and the deeper you get, the more you find.
I’m not going to tell you to learn it, or that if you don’t learn it you’ll be playing bucket drums in a train station.
Either it calls to you, or it doesn’t.
If a new thing arrives that changes that space, you can be damn sure I’ll be all over it.
Even if it’s a dud, I have to know why it’s a dud.
I’m looking right at you, Hekaton.
Threat Of A Good Time
I know, I know. Performance tuning will someday be a thing of the past.
The database will be quantumly self-tuning, self-healing, and whatever other things a database does by itself alone in the dark.
(I’ll give you a hint: it’s not index maintenance.)
I’m comfortable with that eventuality, even if I don’t think the people making those claims are totally in touch with reality about the timeline.
It will likely depend on the lengths to which software will be allowed to make fundamental changes to a database.
I’d be happy if the optimizer would explore UNION/UNION ALL optimizations for OR predicates more often, but hey.
We Out Here
In about five years of consulting — I don’t have an exact count — I’ve looked at probably a thousand servers.
I’m totally willing to concede that I didn’t talk to the right people to make a big judgement call here, but I don’t see a lot of people out there using the things that I’ve been told I mustlearn.
Granted, I see a lot of people with performance problems, because that’s what I’ve chosen to specialize in.
I might not be seeing people with the problems that other things solve: I fully acknowledge my own myopia here.
With the exception of Availability Groups (I maybe see them in the 5% range), all those Next Big Things™ don’t seem to pop up at all.
They haven’t changed what I do, or more importantly what I love to do one iota.
Thanks for reading!
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.
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I delve into some fundamental SQL Server and database design principles. Starting off, I discuss the importance of avoiding wide tables—tables with more than 100 columns—and how they can lead to indexing challenges and maintenance headaches. I also share a few patterns to look out for in your schema that might indicate poorly designed tables, such as prefixing column names or using numeric suffixes for similar data. Additionally, I explore the concept of Michael J. Swartz’s 10% rule, emphasizing that while SQL Server supports up to 1,024 columns per table, it’s wise to keep your tables narrow and well-structured.
Moving on, I cover memory grants in queries, explaining how they are necessary for operations like sorting and hashing but not always required. I provide practical examples of scenarios where a query can process terabytes of data without needing any memory grant, highlighting the importance of understanding these nuances for optimizing performance. Throughout the video, I offer actionable advice on improving your database design, referencing Lewis Davidson’s book “Relational Database Design for SQL Server” as an excellent resource to guide developers in creating more efficient and maintainable databases.
Full Transcript
So, let’s see. Science and technology is the, I don’t know, channel, stream that this thing takes place in. I don’t know if that’s really accurate. I don’t know if I’d call it science or technology. Maybe, maybe neither. It’s not terribly scientific. And aside from the fact that I use a computer to broadcast, I have absolutely no technology in here. I did get a new phone. Got a Pixel 3 XL. Pretty psyched about that. It’s a picture of my kid picking her nose on there. She’s a good time. So, just to kill a little time until questions come in, I see people in here, which is very exciting. Thank you for joining me. I’ve been writing some blog posts about my first six months of consulting independently. And, I don’t know, it was a real tear-jerker. There were some emotional moments in there. I’m kidding.
I replaced feelings with whiskey many decades ago. But, anyway, I’ve been, shut up, motorcycle. Back to your biker gang. I’ve been getting allergy shots for, I want to say, like, three years. At least three years. I’m on, like, the monthly maintenance shots now where, like, I just, like, they don’t even make me safe for a half hour anymore. I get my shot and I hang out for, like, 10, 15 and just say goodbye. Whiskey. Whiskey. Tisigr asks, whiskey or bourbon? I would never drink bourbon. Bourbon is just hyped up maple syrup. I would never drink bourbon. I’m a scotch guy.
And, like, very specifically, I like Iowa scotches. I like the stuff that tastes like burning band-aids. That’s my jam. But, yeah, we’ve been getting allergy shots for three years now, monthly maintenance. And the last, I don’t know what changed in the, I don’t know what changed in the world. I don’t know what new life form showed up on planet Earth. But it’s, like, I’ve never had a shot in my life. For, like, a long time, they were great.
I, like, I went from having, like, severe, like, eyes running, nose constantly bubbling, gross stuff, allergies, like, unable to function allergies, to, like, I would take maybe, like, antihistamine, like, once a week or so or, like, once every couple weeks. It was great. But, man, the last week has been absolutely positively brutal. Like, I’ve been taking stuff every day. Like, every morning, I wake up at, like, 3, 4 in the morning with, like, my face in awful condition.
I don’t know what the hell is different this year. But, man, it is bad. Bad. Ugh. It’s terrible.
Anyway, I don’t know. I don’t know. Someone ask a question. There are at least six of you here.
I don’t know how many more are going to come in. There are some of you. Someone has to have a SQL Server question. You can’t just come here to hear me complain about things or blab on and on about blog posts and whatnot.
Someone has to have a question about SQL Server. Please, God, someone have a question. Come on, there’s, like, 10 of you. Laura says, do you have any thoughts on replacing temp tables with in-memory OLTP?
Yeah, don’t. Hecaton is like herpes. It doesn’t have the nerve to kill you.
It just hangs around being awful for the rest of your life and flaring up at inappropriate times. So, I just have not found anything compelling about Hecaton. It seems like every time…
And this is not just me. This is some very smart people that I’m friends with. Every time they think that they have a specific problem with latches that Hecaton might solve, it is an absolute dead end.
Absolute dead end. In SQL Server 2019, TempDB is going to use… Well, you can use TempDB as, like, an Hecaton-y thing anyway.
I would pry this whole lot to see if that happens. But, whereas, I guess the question is, why? Why?
What are you trying to fix? What is a problem that we’re trying to solve here? What do you think Hecaton is going to make better in your stored procedure that you want to use in memory for?
That’s the big question. What’s going to get better about it? What’s going to get better? I don’t know.
Do, do. Do, do. Do, do. Try to have better indexing of… What on earth? You can index TempTables now.
What’s missing from your current TempTable indexing that Hecaton is going to provide a safe and secure solution for? That’s what I’m curious about.
Too slow. Well, so here’s the thing. What’s too slow? Creating the index or populating the Temp…
or populating the table? Josh says Hecaton indexes are extra confusing. Yeah, they are. So who knows how many hash buckets you should set up? Creating the index.
So… Creating the index is too slow. Okay. Fair enough. See, this is one of those things where it’s like, we’re going to end up going down a real rabbit hole.
Because I’m going to ask you what kind of index. And what kind of data types you’re indexing. And how many rows it is. And a lot of other stuff.
And this is… I hope you’re prepared. Because this can go on for a long, long time. I know.
Then you’d have to actually look at code. Bad news is you’d actually have to look at code to implement Hecaton. and get it set up and running there. If you don’t…
If creating an index is slow now on a Temp table, I don’t think it’s going to be any faster in Hecaton. It’s not free.
Nothing is free. Nothing is free. Yeah, no. Well, you know, my…
So Hecaton is very specifically designed to deal with locking and lashing issues. The in-memory portion, I think, is…
The misgiving around it is that it’ll make any workload faster. And that’s not really true. And the use cases where I’ve seen Hecaton be successful is with really large-scale, fast data ingestion into tables where data is not going to live for very long.
So the example that I always give, because it’s the example that I’ve seen work best, was with online gambling, where data had to come in very fast. And we cared about that data for a very short amount of time, where we wanted very fast updates and being able to get to that data to be snappy and not get blocked up and locked.
And then after like an hour or so, or whenever the betting thing is over, they get pushed out to regular on this table. So that’s the only time.
It’s the only time I’ve ever seen Hecaton be successful. For every other weird niche thing that someone’s like, oh, I bet if we just did this in memory, it’d be faster.
It has never worked out. Never worked out. I don’t know. It’s like, people hear about these features and they, I don’t know, the pamphlets that Microsoft comes up with for these things are amazing because they make it seem like they’re going to fix every single problem that you’re having.
They just sound like this, like golden acres retirement home for your data. And geez, never, never seems to go.
Yeah. So like with the Lars, I guess to put it, put it as succinctly as possible. you can try it, but be very careful with the database that you try it in because once you have created an in-memory file group in a database, one cannot drop an in-memory file group.
One must drop and recreate the database. So if you’re going to try it somewhere, create a new database and enable things there. Don’t do it with a database you care about.
So that’s about all I got. Let’s see. TZH X says, do you have a rule of thumb for how wide a table is too wide? Currently have a bunch of core tables in the system with a hundred plus columns each.
Yeah. You’re about there. So I think one way to, one way to phrase this really well is the maximum number of columns you can have in a table is a thousand and 24.
And a really smart friend of mine, Canadian fella, says it has, has sort of a, what do you call is it? Michael’s Michael J. Swartz 10% rule with, if you’re using 10% of a maximum of a limit, right?
Cause these are limits. They are not goals. They’re not things that you are trying to attain. They are things that they are, they are like, like the, the capacity at which SQL Server will stop functioning. So you’re, you’re right about there.
And the thing that really sucks about a hundred column tables is they’re really, really difficult to index. Well, unless you don’t care about read speeds from them, unless you’re just dumping data in there and not really doing anything with it, then it becomes really hard to index that.
Cause people are, people are going to you know, they’re going to want to search on, on weird sets of columns. And they’re going to want to select weird sets of columns and potentially order by weird sets of columns.
And all that adds up to is a lot of a heartache and pain and trying to index those things. So when I start seeing tables like that, that there are three patterns that I look out for very specifically.
one is column names that have prefixes. So like customer name, customer number, customer address, customer, things like that. Cause those should all be in a table called customer. The other thing that I look out for is, uh, columns that end in numbers.
So like phone one, phone two, phone three, email one, email two, email three. I’m also, I also pay attention to those. Cause those should probably be in their own sort of like entity, attribute value type table, like a, like a long narrow table where, you know, you link up, you know, like, you know, different, different, uh, entities to different attributes and different values than EAV table.
It’s amazing how that works out. The third thing that I look out for when I see very wide tables is, uh, clusters of missing index requests.
So like, like a lot of the times when I’ve seen crazy tables like that, um, what’s, what’s jumped out to me is that, um, missing index requests will be very specific around, which columns are in them.
So the, so like you’ll have a very similar set of search columns and a very similar set of select columns. And oftentimes you can find ways to break, to normalize that table, to break that table apart into separate tables based on what people are searching on within that table.
So you might find like, you know, setting, let’s say, like say you find 12 missing indexes and out of those 12, like four of them are selecting columns one, two, and three.
And the where it causes on columns four or five and six. And then, and the, another one, there’s like four requests and now there’s some, you know, sort of tanglement between columns eight, nine and 10 and 11, 12 and 13.
So like, you can usually find patterns within the missing index requests and within the column names, they can give you some pretty good direction and breaking. I keep saying breaking. I mean, normalizing.
It don’t mean you’re breaking the table. You’re not hurting the table. You’re not breaking your SQL Server. You’re normalizing your data, which is a wonderful thing to do. If you want more advice, if you want a lot of really good advice on doing that, Lewis Davidson has a book called relational SQL Server database design or something like that.
Let me go, let me go over to Amazon and grab a link for it. Cause it’s, I’m probably, I’m probably getting the title or something wrong.
I wish Davidson equal server design. It’s a book that I have in my bookshelf too. It’s not like I just tell people to buy these things and then screw off and let you do all the hard work.
It’s a book that I actually own and I’ve actually read. So let me stick that link in there. There you go. Wonderful.
We have a link. That’s beautiful. That’s a good thing. And let me actually, while I’m doing that, Michael, I should spell Michael, J sport 10% rule. There we go.
Swartz 10% rule. So I would send this stuff over to your developer. I would, I would get a copy of that book for your developers. And I would, I mean, unless you’re the developer, in which case I’m sorry for making money, but you know, could sell, send them, send them, show them that book, show them that link.
Say maybe, Hey, pal, we need to fix this. We need to do better. We need to do better and be better at our SQL Server, relational database design and implementation.
It’s, it’s crazy. So like, I’m like around the five year mark of consulting. I mean, obviously not independent, like, like just sort of generally like general consulting.
And I’m going to say something, uh, that is, is very, okay. I think it’s going to be annoying to a lot of people.
And as your problems are not special, your problems are very fundamental. the problems that your database is having is because you did something weird and wrong. You embraced wide tables.
You did not embrace clustered indexes. You embrace scale, our functions. You, uh, you know, did not pick a decent indexing strategy. There are so many just basic, like you, you chose data types really poorly.
Everything is a, is a long string for some reason. Everything is, uh, mistyped across tables. This, it’s very often, just very fundamental, easy fix things that like you, like should have been done from the get go, but just weren’t.
And, and it screws everything up. And it, it breaks my heart to come in and see like the same problems over and over again, where it’s just like you, like if someone had just made a couple better decisions at the outset, you could have avoided years of problems.
Years of problems. Let’s see. Kapil asks, can a query have a long running query on terabytes of database? Zero granted query memory.
I see some on large queries and don’t get why. Yes. Uh, so query memory grants. So like every query gets some memory. Because every operator.
Requires some memory to like figure out what state it’s in and what it’s doing and what it’s up to. So that every query gets some memory memory grants are very specific to a couple operations. One of them is sorts, right?
So if you sort data, you’re going to require a memory grant to do that. Uh, if you hash data, whether it’s a hash match aggregate or a hash join, you’re going to require memory to do that. And if your query requires, or if your query goes parallel, you’ll require a little extra memory to manage the exchange, the, the parallel exchanges, the buffers and the parallel exchanges.
So there are, there are three things that base, uh, in a nutshell require memory. There are some less, uh, frequent things like optimized nested loops join, which also will ask for a memory grant. And there’s, using like doing like inserts, the columnstore will also ask for a memory grant, but, uh, yeah, it’s, it’s entirely possible to have a query run across terabytes of data and not have to sort or hash anything.
It’s entirely possible for that to happen. And for a query, not to ask for a memory grant to do any of that work.
So fun stuff, huh? Like, like it’s, it might not be a great query. It might be a very slow, terrible query because at that point, I’m picturing a query with like, like lots of like little nested loops joins and, or maybe like in like index supported merge joins where, uh, memory just isn’t required to do any of that stuff.
So yeah, it’s totally possible. Uh, I find it a little suspicious that, that it’s happening on terabytes of data. Cause usually when you get terabytes of data involved, SQL Server is, uh, is pretty keen on doing some hash joins there, but who knows, who knows if you have a query plan that you can share and that you have a question about, uh, that would be a good question for DBA dot stack exchange.
Dot com. That’d be a wonderful place to ask a longer version of that, where perhaps you could provide some more detail and people could give you much more detailed answers. But I think in a nutshell, that should, that should get you going in the right direction.
Let’s see here. TZH asks, I’ve got a handful of nonclustered indexes on the clustered index key with separate columns included to cover some repeat queries. It just seemed dirty.
Yeah. I mean, you’re between a rock and a hard place, right? It’s either you, you, you got these wide tables. And if you don’t index them, uh, people are going to complain and it’s hard to index them. And if you over index them, people are going to complain.
And, uh, it all sucks. It all sucks. Uh, James says, how do I sign up to get the blogs you write sent to my email?
I added both my personal and work email to the newsletter, but never seemed to get any emails. I have checked junk mail and filters. Um, um, I don’t know what would, what would be going wrong there.
I, uh, that email list seems to work because, um, uh, what do you call it? I get, like, I see the email comes to me and I get bounce backs from everyone.
Who’s like, I get everyone’s out of office reply. Cause I’m an idiot and I don’t know how to change that in MailChimp. And when I think about Googling it, I find something better to do.
So I, but it’s, it’s kind of nice to see it’s working. It’s like, it’s like my way of knowing that I’m not just like alone in my office for eternity. Uh, but yeah, uh, I’m not sure if you shoot, if you send me an email, like if you use the contact form on my site to send me an email, um, I can, I can, I can look at your email address and look in MailChimp and kind of see what’s happening there.
It’s entirely possible that like, for some reason, I don’t know, maybe you spelled your email address wrong or, uh, or, or maybe you’re like, whatever your, your email host is just, just hates stuff from MailChimp so badly that it doesn’t even make it to spam, but just gets like immediately quarantined and junked.
It’s also entirely possible that I’ve been blacklisted again, because I don’t know. It’s happened before. I got an SSL certificate and I thought things were cool and people are still like, yeah, we don’t trust you.
I’m like, I got a certificate. I paid GoDaddy like 125 bucks for this damn certificate. How can I be untrustworthy? How, how is, how tell me how that works? Very annoying.
I don’t know. Anyway, Forrest, my furry friend. Oh, by the way, I meant to say if, if you sent me your address last week to get stickers mailed out to you, stickers were put in the mail, uh, this week.
So you should be getting them eventually. I don’t know when. Be honest with you. I wish I could, I wish I could predict and control the mail. Unfortunately, that is where my powers run out.
Anyway, Forrest says, or asks, do you ever find that knowing about memory management internals is helpful? It’s helpful when I create very specific problems that deal with memory management.
I would say that for the general public who have a pretty well-defined set of SQL Server issues, memory management is almost never, um, the issue.
The issue is that SQL Server is managing like 12 gigs of memory when it needs like 128 gigs of memory. Uh, so, you know, it’s, it’s good stuff to know.
It’s good. Like, like SQL jeopardy stuff to know. There’s a lot of good SQL jeopardy stuff to know, but you got to keep stuff, that stuff like back here, the stuff you got to keep up here is way different. Um, so yeah, it’s, it’s good to know.
It’s nice to know about, but, uh, whether it’s ever, whether I’ve ever like walked into a customer site and been like, uh, ha, I see you’ve got stolen pages. Let’s solve that.
Let’s crack that caper. And it’s been like some weird problem. It’s never been a weird problem. It’s always been like, well, you, you, Oh, you put like 500 gigs of, of memory inside SQL Server, but you forgot to raise max server memory from 64 gigs.
When you made the change or like, you’ve got a SQL Server, you are like, you’ve got a VM host with like eight SQL servers on it. And they’re all just fighting over memory constantly.
Or like, just like, like, like stuff has never been like, like, Oh, SQL services bad managing memory. It’s always been like, Oh no, there’s a people problem.
We have a big people problem here. So there’s that. Kapil says, perfect. I got it. Yes, indeed. It’s one heck of a crappy query. Too many inner joins with no sorting in a serial plan.
Woo. We, so wow. Serial plan. So we have a serial plan with no sorting over terabytes. Is there a scalar valued function involved here or a table variable involved here? Because there, there is something amok when we have a query that looks like that over that much data.
And, and there’s like no memory grant or sorting or anything that something stinks about that. Something is stinky in there.
Something is very, very stinky in there. Kapil falls. Are you planning to release some training videos? I learned a lot from, you’re available at Brent’s query tuning classes.
Yes, I am. So I’m in, I’m in a weird, weird position. And I wrote about my weird position a little bit in, in my, in my six months of consulting on my own blog posts, which will be out.
I don’t know, but I think on the anniversary of, on the anniversary of me getting laid off. So it’ll be like June 3rd, which is exactly six months from January 3rd, which is when I got beheaded.
Or when I, when I, when I had a fall from grace and became unfamous or whatever, whatever you want to call it. But yeah, so it’s, it’s been a tough mix of, you know, trying to write good training material.
In other words, like from scratch. So it’s like, I, I do like, well, I’m not, I will never ever be accused of being accused of being a perfectionist. I, I do like to provide high quality or as high quality as I’m capable of material with, with the training.
So I do try to like, you know, have, have nice pictures and have things be laid out clearly and look nice. And, and the other thing is that I don’t want to just repeat myself. So I wouldn’t want to just like completely rehash training videos that I had recorded that are up on Brent’s site.
If I’m going to, if I’m going to do this thing, it’s got to be me. So it’s, it’s tough because now I have to sort of find new ways to say things or new ways to present things.
Uh, so it is, it is writing all the material from scratch and, and it’s, it’s been going a bit more slowly than, than I’d like just because, you know, I mean, it’s a great problem to have because I, I have enough like consulting work coming in where it takes serious chunk, serious bites out of my, my time during the week where I can’t sit and dedicate it to, to writing the training, but it’s getting written.
Um, you know, I’m going to, I’m going to take, uh, some of the material from the, the, the server tuning pre-con that I’ve been doing. And, uh, I’m going to, that’s going to be like worked into some of the more advanced material.
Uh, a lot of the more advanced, uh, query tuning stuff is written right now. My, uh, my outline is, uh, like, like beginner stuff, uh, which I’m going to call starting SQL.
And that’s going to cover, um, that’s going to like do like, uh, sort of like a, like a jump, jump right into like, this is what a query does.
This is how, why it’s fast, why it’s slow. This is indexes, uh, you know, then going deeper into what is an index, what’s it, what’s a weight, you know, what’s a query plan. So like get, like getting like from like, like, like beginner stuff, but like, you know, not like, I think you’re a dummy beginner stuff.
Like I’m going to like teach you the really important, stuff about those things. Uh, then after that, I’ll probably do like a little bit of internals, not like, not like a book of boring, like, this is a database page.
This is a slaughter a type internals, like, like the stuff that I’ve found useful over the years. Um, then we’ll do like hardware stuff and then indexes more like more advanced index stuff. And then more advanced execution plan stuff.
And then query tuning. Um, I’m also hoping to have a very special guest, maybe do some, some stuff on columnstore. But I can’t say who or when, but that would be nice if that happened too.
So yeah, that’s my, that’s my plan. And, uh, and, and Josh to, to answer your email.
No, I don’t, I don’t have your address anymore. That was in my old email account. I don’t have access to any longer. Uh, so if you, if you, if you want to send me your, your actual address, that would be actually, I didn’t, I didn’t read enough of your email.
What the hell? What is this? I don’t even know what this is. Tastings. I’m not, I’m not going to you. You’re annoying me.
Email is terrible. email is the worst thing in the world. Darren says, he’s always found my classes, very informative and entertaining. Thank you, Darren.
I appreciate it. Uh, I’m glad someone does because most of the time, uh, when, whenever I’m talking to the camera, I’ll leave my office and my wife is staring at me like there’s something terribly wrong with me. I’m glad someone out there is, is, is entertained and informed.
The things that I say in here, otherwise, otherwise I don’t, I don’t know what I do. I don’t know. If, if you, if you, if you were one or the other, I would be, I would be ecstatic.
If you were informed or entertained by me, I would be thrilled. But the fact that you’re both, wow. I don’t, I don’t even know what to say to that. Enjoy, enjoy the stickers that I say to that.
Uh, yeah, that’s the thing. I don’t, I don’t carry over well to a lot of crowds. I have, I have a very specific set of people. I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I’ll tell you that that’s probably the hardest part about consulting is, is not like the consulting poker face or like, like the business stuff or the, like the, like the landing clients part.
It’s, it’s like the, the, how can I say this to like, how can I say this to people who I’ve never met in a way where like, I’m not gonna, I’m not gonna make everyone too angry.
And I think like, like that’s probably the hardest thing is, you know, like I, I need to say things in a way that they keep me interested in it. Entertain me.
But then at the same time, like I, I know someone’s not going to be happy with it. Like someone, someone’s going to be unhappy with it. Someone is going to be mad at me or, or, or think that I’m inappropriate or something.
So, ah, whatever. It’s a good time. You know what? Uh, if, if, if I had to pick a different way to make a living, I’m not sure what it would be.
Actually, I can’t, you know what I would do? I would open up a laundromat with a bar in it and I would only have it open from like, I don’t know, like 10 AM to like 8 PM, maybe 9 PM.
Cause I don’t want to like, I don’t want to have like an all night laundromat bar thing. I like, like, like, like, that’s like, like, like just washing machines full of pee would vomit would be the thing there. So I would open up a laundromat with a bar and I have very limited hours and I would just sit there and watch people do laundry.
That’s what I would do. Maybe I’d make a friendly conversation. I’d be the bartender, I guess.
I have a very limited drink menu. Cause I’m not very good at mixing things. Beer. The world doesn’t have enough shot in beer bars.
Everything’s a, everything’s a mixology bar. Everything’s going to have muddled, whatever in it. Shaved ice and rinds of things shot in a beer. Never did anyone wrong.
Anyway, uh, we’re at a, we’re at about a half hour here. And, um, uh, uh, uh, oh boy, this is a question. Hang on.
I just went through one of Joe’s blogs regarding soft Numa starting CWL server 2016. Per your experience, have you turned that feature off due to performance issues as raised by Joe in that blog? You should leave a comment on, on Joe’s blog post and ask him about it.
Cause Joe is by far a much, a much bigger expert on that Numa stuff than I am. So leave a comment with your question and, uh, mate, mate, and Joe will get back to you because he’s contractually obligated to is my guest.
No, I’m kidding. He’s not, he might ignore you, but if he does, I’m sorry. I’m sorry. I can’t control Joe. I was at first.
I was like, you know, if I’m going to do this, I should like, you know, say, okay, I’ll schedule your post to go out. And then I really just give Joe, let Joe off his leash, let Joe off his chain and do his Joe thing. Be ridiculous.
Trying to contain that man. Anyway, uh, that’s about a half hour. Uh, I’m going to, uh, I’m going to get going and, uh, get back to work or whatever you want to call it. Uh, thanks for joining me.
Uh, and I will see you most likely next week, unless something terrible happens. I’m kidding. Nothing. Unless I win the lottery, which would be wonderful. But if I do, if I win the lottery, I’m going to do this, uh, really drunk and tell you all what I actually think about you.
Goodbye. Have a great, great long weekend. if you are memorializing anything. Goodbye. Bye.
Bye. Bye. Bye. Thank you.
Video Summary
In this video, I delve into some fundamental SQL Server and database design principles. Starting off, I discuss the importance of avoiding wide tables—tables with more than 100 columns—and how they can lead to indexing challenges and maintenance headaches. I also share a few patterns to look out for in your schema that might indicate poorly designed tables, such as prefixing column names or using numeric suffixes for similar data. Additionally, I explore the concept of Michael J. Swartz’s 10% rule, emphasizing that while SQL Server supports up to 1,024 columns per table, it’s wise to keep your tables narrow and well-structured.
Moving on, I cover memory grants in queries, explaining how they are necessary for operations like sorting and hashing but not always required. I provide practical examples of scenarios where a query can process terabytes of data without needing any memory grant, highlighting the importance of understanding these nuances for optimizing performance. Throughout the video, I offer actionable advice on improving your database design, referencing Lewis Davidson’s book “Relational Database Design for SQL Server” as an excellent resource to guide developers in creating more efficient and maintainable databases.
Full Transcript
So, let’s see. Science and technology is the, I don’t know, channel, stream that this thing takes place in. I don’t know if that’s really accurate. I don’t know if I’d call it science or technology. Maybe, maybe neither. It’s not terribly scientific. And aside from the fact that I use a computer to broadcast, I have absolutely no technology in here. I did get a new phone. Got a Pixel 3 XL. Pretty psyched about that. It’s a picture of my kid picking her nose on there. She’s a good time. So, just to kill a little time until questions come in, I see people in here, which is very exciting. Thank you for joining me. I’ve been writing some blog posts about my first six months of consulting independently. And, I don’t know, it was a real tear-jerker. There were some emotional moments in there. I’m kidding.
I replaced feelings with whiskey many decades ago. But, anyway, I’ve been, shut up, motorcycle. Back to your biker gang. I’ve been getting allergy shots for, I want to say, like, three years. At least three years. I’m on, like, the monthly maintenance shots now where, like, I just, like, they don’t even make me safe for a half hour anymore. I get my shot and I hang out for, like, 10, 15 and just say goodbye. Whiskey. Whiskey. Tisigr asks, whiskey or bourbon? I would never drink bourbon. Bourbon is just hyped up maple syrup. I would never drink bourbon. I’m a scotch guy.
And, like, very specifically, I like Iowa scotches. I like the stuff that tastes like burning band-aids. That’s my jam. But, yeah, we’ve been getting allergy shots for three years now, monthly maintenance. And the last, I don’t know what changed in the, I don’t know what changed in the world. I don’t know what new life form showed up on planet Earth. But it’s, like, I’ve never had a shot in my life. For, like, a long time, they were great.
I, like, I went from having, like, severe, like, eyes running, nose constantly bubbling, gross stuff, allergies, like, unable to function allergies, to, like, I would take maybe, like, antihistamine, like, once a week or so or, like, once every couple weeks. It was great. But, man, the last week has been absolutely positively brutal. Like, I’ve been taking stuff every day. Like, every morning, I wake up at, like, 3, 4 in the morning with, like, my face in awful condition.
I don’t know what the hell is different this year. But, man, it is bad. Bad. Ugh. It’s terrible.
Anyway, I don’t know. I don’t know. Someone ask a question. There are at least six of you here.
I don’t know how many more are going to come in. There are some of you. Someone has to have a SQL Server question. You can’t just come here to hear me complain about things or blab on and on about blog posts and whatnot.
Someone has to have a question about SQL Server. Please, God, someone have a question. Come on, there’s, like, 10 of you. Laura says, do you have any thoughts on replacing temp tables with in-memory OLTP?
Yeah, don’t. Hecaton is like herpes. It doesn’t have the nerve to kill you.
It just hangs around being awful for the rest of your life and flaring up at inappropriate times. So, I just have not found anything compelling about Hecaton. It seems like every time…
And this is not just me. This is some very smart people that I’m friends with. Every time they think that they have a specific problem with latches that Hecaton might solve, it is an absolute dead end.
Absolute dead end. In SQL Server 2019, TempDB is going to use… Well, you can use TempDB as, like, an Hecaton-y thing anyway.
I would pry this whole lot to see if that happens. But, whereas, I guess the question is, why? Why?
What are you trying to fix? What is a problem that we’re trying to solve here? What do you think Hecaton is going to make better in your stored procedure that you want to use in memory for?
That’s the big question. What’s going to get better about it? What’s going to get better? I don’t know.
Do, do. Do, do. Do, do. Try to have better indexing of… What on earth? You can index TempTables now.
What’s missing from your current TempTable indexing that Hecaton is going to provide a safe and secure solution for? That’s what I’m curious about.
Too slow. Well, so here’s the thing. What’s too slow? Creating the index or populating the Temp…
or populating the table? Josh says Hecaton indexes are extra confusing. Yeah, they are. So who knows how many hash buckets you should set up? Creating the index.
So… Creating the index is too slow. Okay. Fair enough. See, this is one of those things where it’s like, we’re going to end up going down a real rabbit hole.
Because I’m going to ask you what kind of index. And what kind of data types you’re indexing. And how many rows it is. And a lot of other stuff.
And this is… I hope you’re prepared. Because this can go on for a long, long time. I know.
Then you’d have to actually look at code. Bad news is you’d actually have to look at code to implement Hecaton. and get it set up and running there. If you don’t…
If creating an index is slow now on a Temp table, I don’t think it’s going to be any faster in Hecaton. It’s not free.
Nothing is free. Nothing is free. Yeah, no. Well, you know, my…
So Hecaton is very specifically designed to deal with locking and lashing issues. The in-memory portion, I think, is…
The misgiving around it is that it’ll make any workload faster. And that’s not really true. And the use cases where I’ve seen Hecaton be successful is with really large-scale, fast data ingestion into tables where data is not going to live for very long.
So the example that I always give, because it’s the example that I’ve seen work best, was with online gambling, where data had to come in very fast. And we cared about that data for a very short amount of time, where we wanted very fast updates and being able to get to that data to be snappy and not get blocked up and locked.
And then after like an hour or so, or whenever the betting thing is over, they get pushed out to regular on this table. So that’s the only time.
It’s the only time I’ve ever seen Hecaton be successful. For every other weird niche thing that someone’s like, oh, I bet if we just did this in memory, it’d be faster.
It has never worked out. Never worked out. I don’t know. It’s like, people hear about these features and they, I don’t know, the pamphlets that Microsoft comes up with for these things are amazing because they make it seem like they’re going to fix every single problem that you’re having.
They just sound like this, like golden acres retirement home for your data. And geez, never, never seems to go.
Yeah. So like with the Lars, I guess to put it, put it as succinctly as possible. you can try it, but be very careful with the database that you try it in because once you have created an in-memory file group in a database, one cannot drop an in-memory file group.
One must drop and recreate the database. So if you’re going to try it somewhere, create a new database and enable things there. Don’t do it with a database you care about.
So that’s about all I got. Let’s see. TZH X says, do you have a rule of thumb for how wide a table is too wide? Currently have a bunch of core tables in the system with a hundred plus columns each.
Yeah. You’re about there. So I think one way to, one way to phrase this really well is the maximum number of columns you can have in a table is a thousand and 24.
And a really smart friend of mine, Canadian fella, says it has, has sort of a, what do you call is it? Michael’s Michael J. Swartz 10% rule with, if you’re using 10% of a maximum of a limit, right?
Cause these are limits. They are not goals. They’re not things that you are trying to attain. They are things that they are, they are like, like the, the capacity at which SQL Server will stop functioning. So you’re, you’re right about there.
And the thing that really sucks about a hundred column tables is they’re really, really difficult to index. Well, unless you don’t care about read speeds from them, unless you’re just dumping data in there and not really doing anything with it, then it becomes really hard to index that.
Cause people are, people are going to you know, they’re going to want to search on, on weird sets of columns. And they’re going to want to select weird sets of columns and potentially order by weird sets of columns.
And all that adds up to is a lot of a heartache and pain and trying to index those things. So when I start seeing tables like that, that there are three patterns that I look out for very specifically.
one is column names that have prefixes. So like customer name, customer number, customer address, customer, things like that. Cause those should all be in a table called customer. The other thing that I look out for is, uh, columns that end in numbers.
So like phone one, phone two, phone three, email one, email two, email three. I’m also, I also pay attention to those. Cause those should probably be in their own sort of like entity, attribute value type table, like a, like a long narrow table where, you know, you link up, you know, like, you know, different, different, uh, entities to different attributes and different values than EAV table.
It’s amazing how that works out. The third thing that I look out for when I see very wide tables is, uh, clusters of missing index requests.
So like, like a lot of the times when I’ve seen crazy tables like that, um, what’s, what’s jumped out to me is that, um, missing index requests will be very specific around, which columns are in them.
So the, so like you’ll have a very similar set of search columns and a very similar set of select columns. And oftentimes you can find ways to break, to normalize that table, to break that table apart into separate tables based on what people are searching on within that table.
So you might find like, you know, setting, let’s say, like say you find 12 missing indexes and out of those 12, like four of them are selecting columns one, two, and three.
And the where it causes on columns four or five and six. And then, and the, another one, there’s like four requests and now there’s some, you know, sort of tanglement between columns eight, nine and 10 and 11, 12 and 13.
So like, you can usually find patterns within the missing index requests and within the column names, they can give you some pretty good direction and breaking. I keep saying breaking. I mean, normalizing.
It don’t mean you’re breaking the table. You’re not hurting the table. You’re not breaking your SQL Server. You’re normalizing your data, which is a wonderful thing to do. If you want more advice, if you want a lot of really good advice on doing that, Lewis Davidson has a book called relational SQL Server database design or something like that.
Let me go, let me go over to Amazon and grab a link for it. Cause it’s, I’m probably, I’m probably getting the title or something wrong.
I wish Davidson equal server design. It’s a book that I have in my bookshelf too. It’s not like I just tell people to buy these things and then screw off and let you do all the hard work.
It’s a book that I actually own and I’ve actually read. So let me stick that link in there. There you go. Wonderful.
We have a link. That’s beautiful. That’s a good thing. And let me actually, while I’m doing that, Michael, I should spell Michael, J sport 10% rule. There we go.
Swartz 10% rule. So I would send this stuff over to your developer. I would, I would get a copy of that book for your developers. And I would, I mean, unless you’re the developer, in which case I’m sorry for making money, but you know, could sell, send them, send them, show them that book, show them that link.
Say maybe, Hey, pal, we need to fix this. We need to do better. We need to do better and be better at our SQL Server, relational database design and implementation.
It’s, it’s crazy. So like, I’m like around the five year mark of consulting. I mean, obviously not independent, like, like just sort of generally like general consulting.
And I’m going to say something, uh, that is, is very, okay. I think it’s going to be annoying to a lot of people.
And as your problems are not special, your problems are very fundamental. the problems that your database is having is because you did something weird and wrong. You embraced wide tables.
You did not embrace clustered indexes. You embrace scale, our functions. You, uh, you know, did not pick a decent indexing strategy. There are so many just basic, like you, you chose data types really poorly.
Everything is a, is a long string for some reason. Everything is, uh, mistyped across tables. This, it’s very often, just very fundamental, easy fix things that like you, like should have been done from the get go, but just weren’t.
And, and it screws everything up. And it, it breaks my heart to come in and see like the same problems over and over again, where it’s just like you, like if someone had just made a couple better decisions at the outset, you could have avoided years of problems.
Years of problems. Let’s see. Kapil asks, can a query have a long running query on terabytes of database? Zero granted query memory.
I see some on large queries and don’t get why. Yes. Uh, so query memory grants. So like every query gets some memory. Because every operator.
Requires some memory to like figure out what state it’s in and what it’s doing and what it’s up to. So that every query gets some memory memory grants are very specific to a couple operations. One of them is sorts, right?
So if you sort data, you’re going to require a memory grant to do that. Uh, if you hash data, whether it’s a hash match aggregate or a hash join, you’re going to require memory to do that. And if your query requires, or if your query goes parallel, you’ll require a little extra memory to manage the exchange, the, the parallel exchanges, the buffers and the parallel exchanges.
So there are, there are three things that base, uh, in a nutshell require memory. There are some less, uh, frequent things like optimized nested loops join, which also will ask for a memory grant. And there’s, using like doing like inserts, the columnstore will also ask for a memory grant, but, uh, yeah, it’s, it’s entirely possible to have a query run across terabytes of data and not have to sort or hash anything.
It’s entirely possible for that to happen. And for a query, not to ask for a memory grant to do any of that work.
So fun stuff, huh? Like, like it’s, it might not be a great query. It might be a very slow, terrible query because at that point, I’m picturing a query with like, like lots of like little nested loops joins and, or maybe like in like index supported merge joins where, uh, memory just isn’t required to do any of that stuff.
So yeah, it’s totally possible. Uh, I find it a little suspicious that, that it’s happening on terabytes of data. Cause usually when you get terabytes of data involved, SQL Server is, uh, is pretty keen on doing some hash joins there, but who knows, who knows if you have a query plan that you can share and that you have a question about, uh, that would be a good question for DBA dot stack exchange.
Dot com. That’d be a wonderful place to ask a longer version of that, where perhaps you could provide some more detail and people could give you much more detailed answers. But I think in a nutshell, that should, that should get you going in the right direction.
Let’s see here. TZH asks, I’ve got a handful of nonclustered indexes on the clustered index key with separate columns included to cover some repeat queries. It just seemed dirty.
Yeah. I mean, you’re between a rock and a hard place, right? It’s either you, you, you got these wide tables. And if you don’t index them, uh, people are going to complain and it’s hard to index them. And if you over index them, people are going to complain.
And, uh, it all sucks. It all sucks. Uh, James says, how do I sign up to get the blogs you write sent to my email?
I added both my personal and work email to the newsletter, but never seemed to get any emails. I have checked junk mail and filters. Um, um, I don’t know what would, what would be going wrong there.
I, uh, that email list seems to work because, um, uh, what do you call it? I get, like, I see the email comes to me and I get bounce backs from everyone.
Who’s like, I get everyone’s out of office reply. Cause I’m an idiot and I don’t know how to change that in MailChimp. And when I think about Googling it, I find something better to do.
So I, but it’s, it’s kind of nice to see it’s working. It’s like, it’s like my way of knowing that I’m not just like alone in my office for eternity. Uh, but yeah, uh, I’m not sure if you shoot, if you send me an email, like if you use the contact form on my site to send me an email, um, I can, I can, I can look at your email address and look in MailChimp and kind of see what’s happening there.
It’s entirely possible that like, for some reason, I don’t know, maybe you spelled your email address wrong or, uh, or, or maybe you’re like, whatever your, your email host is just, just hates stuff from MailChimp so badly that it doesn’t even make it to spam, but just gets like immediately quarantined and junked.
It’s also entirely possible that I’ve been blacklisted again, because I don’t know. It’s happened before. I got an SSL certificate and I thought things were cool and people are still like, yeah, we don’t trust you.
I’m like, I got a certificate. I paid GoDaddy like 125 bucks for this damn certificate. How can I be untrustworthy? How, how is, how tell me how that works? Very annoying.
I don’t know. Anyway, Forrest, my furry friend. Oh, by the way, I meant to say if, if you sent me your address last week to get stickers mailed out to you, stickers were put in the mail, uh, this week.
So you should be getting them eventually. I don’t know when. Be honest with you. I wish I could, I wish I could predict and control the mail. Unfortunately, that is where my powers run out.
Anyway, Forrest says, or asks, do you ever find that knowing about memory management internals is helpful? It’s helpful when I create very specific problems that deal with memory management.
I would say that for the general public who have a pretty well-defined set of SQL Server issues, memory management is almost never, um, the issue.
The issue is that SQL Server is managing like 12 gigs of memory when it needs like 128 gigs of memory. Uh, so, you know, it’s, it’s good stuff to know.
It’s good. Like, like SQL jeopardy stuff to know. There’s a lot of good SQL jeopardy stuff to know, but you got to keep stuff, that stuff like back here, the stuff you got to keep up here is way different. Um, so yeah, it’s, it’s good to know.
It’s nice to know about, but, uh, whether it’s ever, whether I’ve ever like walked into a customer site and been like, uh, ha, I see you’ve got stolen pages. Let’s solve that.
Let’s crack that caper. And it’s been like some weird problem. It’s never been a weird problem. It’s always been like, well, you, you, Oh, you put like 500 gigs of, of memory inside SQL Server, but you forgot to raise max server memory from 64 gigs.
When you made the change or like, you’ve got a SQL Server, you are like, you’ve got a VM host with like eight SQL servers on it. And they’re all just fighting over memory constantly.
Or like, just like, like, like stuff has never been like, like, Oh, SQL services bad managing memory. It’s always been like, Oh no, there’s a people problem.
We have a big people problem here. So there’s that. Kapil says, perfect. I got it. Yes, indeed. It’s one heck of a crappy query. Too many inner joins with no sorting in a serial plan.
Woo. We, so wow. Serial plan. So we have a serial plan with no sorting over terabytes. Is there a scalar valued function involved here or a table variable involved here? Because there, there is something amok when we have a query that looks like that over that much data.
And, and there’s like no memory grant or sorting or anything that something stinks about that. Something is stinky in there.
Something is very, very stinky in there. Kapil falls. Are you planning to release some training videos? I learned a lot from, you’re available at Brent’s query tuning classes.
Yes, I am. So I’m in, I’m in a weird, weird position. And I wrote about my weird position a little bit in, in my, in my six months of consulting on my own blog posts, which will be out.
I don’t know, but I think on the anniversary of, on the anniversary of me getting laid off. So it’ll be like June 3rd, which is exactly six months from January 3rd, which is when I got beheaded.
Or when I, when I, when I had a fall from grace and became unfamous or whatever, whatever you want to call it. But yeah, so it’s, it’s been a tough mix of, you know, trying to write good training material.
In other words, like from scratch. So it’s like, I, I do like, well, I’m not, I will never ever be accused of being accused of being a perfectionist. I, I do like to provide high quality or as high quality as I’m capable of material with, with the training.
So I do try to like, you know, have, have nice pictures and have things be laid out clearly and look nice. And, and the other thing is that I don’t want to just repeat myself. So I wouldn’t want to just like completely rehash training videos that I had recorded that are up on Brent’s site.
If I’m going to, if I’m going to do this thing, it’s got to be me. So it’s, it’s tough because now I have to sort of find new ways to say things or new ways to present things.
Uh, so it is, it is writing all the material from scratch and, and it’s, it’s been going a bit more slowly than, than I’d like just because, you know, I mean, it’s a great problem to have because I, I have enough like consulting work coming in where it takes serious chunk, serious bites out of my, my time during the week where I can’t sit and dedicate it to, to writing the training, but it’s getting written.
Um, you know, I’m going to, I’m going to take, uh, some of the material from the, the, the server tuning pre-con that I’ve been doing. And, uh, I’m going to, that’s going to be like worked into some of the more advanced material.
Uh, a lot of the more advanced, uh, query tuning stuff is written right now. My, uh, my outline is, uh, like, like beginner stuff, uh, which I’m going to call starting SQL.
And that’s going to cover, um, that’s going to like do like, uh, sort of like a, like a jump, jump right into like, this is what a query does.
This is how, why it’s fast, why it’s slow. This is indexes, uh, you know, then going deeper into what is an index, what’s it, what’s a weight, you know, what’s a query plan. So like get, like getting like from like, like, like beginner stuff, but like, you know, not like, I think you’re a dummy beginner stuff.
Like I’m going to like teach you the really important, stuff about those things. Uh, then after that, I’ll probably do like a little bit of internals, not like, not like a book of boring, like, this is a database page.
This is a slaughter a type internals, like, like the stuff that I’ve found useful over the years. Um, then we’ll do like hardware stuff and then indexes more like more advanced index stuff. And then more advanced execution plan stuff.
And then query tuning. Um, I’m also hoping to have a very special guest, maybe do some, some stuff on columnstore. But I can’t say who or when, but that would be nice if that happened too.
So yeah, that’s my, that’s my plan. And, uh, and, and Josh to, to answer your email.
No, I don’t, I don’t have your address anymore. That was in my old email account. I don’t have access to any longer. Uh, so if you, if you, if you want to send me your, your actual address, that would be actually, I didn’t, I didn’t read enough of your email.
What the hell? What is this? I don’t even know what this is. Tastings. I’m not, I’m not going to you. You’re annoying me.
Email is terrible. email is the worst thing in the world. Darren says, he’s always found my classes, very informative and entertaining. Thank you, Darren.
I appreciate it. Uh, I’m glad someone does because most of the time, uh, when, whenever I’m talking to the camera, I’ll leave my office and my wife is staring at me like there’s something terribly wrong with me. I’m glad someone out there is, is, is entertained and informed.
The things that I say in here, otherwise, otherwise I don’t, I don’t know what I do. I don’t know. If, if you, if you, if you were one or the other, I would be, I would be ecstatic.
If you were informed or entertained by me, I would be thrilled. But the fact that you’re both, wow. I don’t, I don’t even know what to say to that. Enjoy, enjoy the stickers that I say to that.
Uh, yeah, that’s the thing. I don’t, I don’t carry over well to a lot of crowds. I have, I have a very specific set of people. I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I’ll tell you that that’s probably the hardest part about consulting is, is not like the consulting poker face or like, like the business stuff or the, like the, like the landing clients part.
It’s, it’s like the, the, how can I say this to like, how can I say this to people who I’ve never met in a way where like, I’m not gonna, I’m not gonna make everyone too angry.
And I think like, like that’s probably the hardest thing is, you know, like I, I need to say things in a way that they keep me interested in it. Entertain me.
But then at the same time, like I, I know someone’s not going to be happy with it. Like someone, someone’s going to be unhappy with it. Someone is going to be mad at me or, or, or think that I’m inappropriate or something.
So, ah, whatever. It’s a good time. You know what? Uh, if, if, if I had to pick a different way to make a living, I’m not sure what it would be.
Actually, I can’t, you know what I would do? I would open up a laundromat with a bar in it and I would only have it open from like, I don’t know, like 10 AM to like 8 PM, maybe 9 PM.
Cause I don’t want to like, I don’t want to have like an all night laundromat bar thing. I like, like, like, like, that’s like, like, like just washing machines full of pee would vomit would be the thing there. So I would open up a laundromat with a bar and I have very limited hours and I would just sit there and watch people do laundry.
That’s what I would do. Maybe I’d make a friendly conversation. I’d be the bartender, I guess.
I have a very limited drink menu. Cause I’m not very good at mixing things. Beer. The world doesn’t have enough shot in beer bars.
Everything’s a, everything’s a mixology bar. Everything’s going to have muddled, whatever in it. Shaved ice and rinds of things shot in a beer. Never did anyone wrong.
Anyway, uh, we’re at a, we’re at about a half hour here. And, um, uh, uh, uh, oh boy, this is a question. Hang on.
I just went through one of Joe’s blogs regarding soft Numa starting CWL server 2016. Per your experience, have you turned that feature off due to performance issues as raised by Joe in that blog? You should leave a comment on, on Joe’s blog post and ask him about it.
Cause Joe is by far a much, a much bigger expert on that Numa stuff than I am. So leave a comment with your question and, uh, mate, mate, and Joe will get back to you because he’s contractually obligated to is my guest.
No, I’m kidding. He’s not, he might ignore you, but if he does, I’m sorry. I’m sorry. I can’t control Joe. I was at first.
I was like, you know, if I’m going to do this, I should like, you know, say, okay, I’ll schedule your post to go out. And then I really just give Joe, let Joe off his leash, let Joe off his chain and do his Joe thing. Be ridiculous.
Trying to contain that man. Anyway, uh, that’s about a half hour. Uh, I’m going to, uh, I’m going to get going and, uh, get back to work or whatever you want to call it. Uh, thanks for joining me.
Uh, and I will see you most likely next week, unless something terrible happens. I’m kidding. Nothing. Unless I win the lottery, which would be wonderful. But if I do, if I win the lottery, I’m going to do this, uh, really drunk and tell you all what I actually think about you.
Goodbye. Have a great, great long weekend. if you are memorializing anything. Goodbye. Bye.
Bye. Bye. Bye. Thank you.
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.
