ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I found myself live-streaming SQL Server tips and tricks, only to be greeted by the emptiness of an empty chat. It’s moments like these that make you question your entire existence as a content creator. Despite the initial disappointment, we managed to gather a small but enthusiastic audience, including some familiar faces and a few curious newcomers. The discussion ranged from enterprise edition licensing headaches to optimizing SQL Server performance with core-based configurations and max degree of parallelism settings. I also shared insights on free monitoring tools like OpsServer, which might just be the lifesaver for those stretched thin in terms of budget but not in need. The stream turned into a lively debate about workload replay tools and memory allocation strategies, making it an engaging session despite the initial lull.
Full Transcript
I’m live. Uh, no one yet. I don’t even have a thumbs up. I didn’t even give myself a thumbs up. What a sad day. Oh, boy.
So iffy. Will anyone actually be here? How long will I have to wait? How long? One person. One person. There we go. You better have a lot of SQL Server questions, one person, or else I’m screwed. My big plan after this is to go wine shopping for the weekend.
Oh, well, if it’s Josh, I’m not too excited. That’s never a good sign. These developers show up and get nothing out of them. Three of you.
Wonderful. Wonderful. We have enough people to successfully implement quorum until a fourth person shows up. No, there you are. There you are. You ruined the quorum, fourth person. Thanks for nothing. Thanks for nothing.
Do, do, do, do, do, do, do, do, do, do. Do, do, do, do, do, do. Back down to three.
Thank God. now we’re back to quorum now we can successfully run our failover cluster we can successfully fail that was funny it was fun successfully fail fail upward onward and upward that’s my that’s my that’s my plan yeah what do you what do you what do you know about failure kenneth what do you know about it by the way this week’s whatever you want to call it is brought to you by a thing that helps babies fart that’s our sponsor this week so everyone round of applause for our generous sponsor plastic thing that helps babies fart this has never been used by the way just something that came with a new baby a while ago that i found and i thought it looked funny you kind of like a little flute but you know don’t worry that’s never been anywhere weird i still have no idea what that means don’t i don’t want to know i have nothing wow an actual sql server question hello steve how are you uh lars says i use sql enterprise recently came across a 20 cpu limit due to the wrong install media attempts at addition upgrade failed what’s the difference between variants of enterprise edition uh so there is the old version which is like the cal based what you want to do is uh in download the install media that has the core based licensing in the title and do a skew upgrade to that you don’t need to do an edition upgrade you need to do a skew upgrade so um yeah that’s that’s that’s where you want to go with that you need to upgrade additions your edition is fine enterprise is enterprise but the uh the skew of enterprise is what’s skewed up zane says what kind of deluxe baby store did you pick yours up at that it came with accessories uh the hospital where um it it plopped out uh dev dba asks can i send you my copy of great post eric for an autograph uh hmm no no um if you no i don’t even have any copies of that left uh no nothing personal i just don’t want anyone to have my my home address who doesn’t who i don’t know don’t take it personally but everything i do everything from home and it would be weird if people just knew my address but if i if you ever had a conference and i’m at or a SQL saturday or anything that i’m at then i would happily sign it for you for free p.o box yeah no why would i do that yeah like madison yeah madison wisconsin i’m gonna be there what is it friday the 5th for a full day of training and then saturday the 6th where i have a session about the optimizer and how the optimizer works it’ll be fun devdb i don’t know i don’t know what which conferences you get to so be prepared i just might show up anywhere who knows who knows i don’t think i’m going to pass this year though they didn’t pick me for a pre-con and that’s an expensive trip when you’re not getting paid for it expensive if i lived in seattle that’d be one thing yeah bring it everywhere i mean what’s the what’s the worst thing that can happen you’ll run into lots of interesting new people who are like man that’s a cool book what’s that about and then more people will buy buy the book and then i’ll i’ll make another six dollars and that’ll be that’ll be nice yeah mcdonald’s the mall uh rest stop bathrooms you know various basements uh you know bars bars with no one in them that open at 9 00 a.m good places to go good places to be let’s see if there’s any questions uh on another thing yeah no all right good there was a question that came in via email that uh i answered via email and it was it was a it was that someone um someone uses uh sentry one at work but they have so many sql servers that it was getting expensive to keep licensing them all for sentry one because sentry one you know well i mean not just sentry one any any paid monitoring tool is going to be about the same price so it doesn’t matter if it’s like sentry one or uh like quest spot fog or whatever they’re calling it these days but uh yeah like anything’s gonna be about the same price but because there were so many people there are so many servers right there not so many people so many servers who um they needed to monitor uh they were looking for something free or open source and i suggested uh uh so stack overflow and the the very very smart people at stack overflow um have developed their own monitoring tool over the years the price you pay is that it is open source and it’s a little difficult to install but it’s it’s called ops server and it gets a lot of cool stuff it collects a lot of awesome metrics i’m actually gonna pull the link up for it but yeah it’s pretty sweet uh if you can get into if you can if you can get past the the install and config and get it up and running it’s it’s pretty awesome so if you need a free monitoring tool i would do that uh what uh that uh what is that it doesn’t make sense nothing makes sense anymore yes you’re welcome that is a great link it is a wonderful link uh okay i’ll show that uh sql watch dot io wow i need to get a dot io website those things are very popular right what would i do could make it i could get like taxes dot io that would be funny or i get like slow io what else could i do yeah i don’t know lots of stuff dot io that would be a funny url uh have you used the newest azure data studio it has some pretty cool monitoring built in that you can report off of uh no i i have never used azure data studio um because the majority of the stuff that i care about is in execution plans and azure d azure data studio is more like azure duty studio when it comes to execution plans it does not have uh anything anything good going on about it so no and i i i despite a lot of people being very excited about notebooks uh you know i i have i have my own little little pad of paper that i much prefer to keep my notes on my wife’s dad was it was a cop for a long time and when he retired he gave me all his all his like uh his police notebooks so i keep all my notes kind of kind of funny i keep all my notes in like a police notebook and the the first the first the first page has all sorts of funny stuff an ambulance rescue squad fire so like all this like a justice of the peace i don’t know what a justice of the peace does what do they do it’s like it’s like all it’s missing is like a constable in a priest i don’t know what goes on there let’s see here someone has a very important sql server question but they haven’t asked it yet so i’m just going to wait on that i’ll wait 20 minutes if i have to kapil says there was one i recall from spaghetti dba a sql workload yeah if so that’s not i don’t as far as i know that’s not a monitoring tool that allows you to replay a workload on one server from another server in a little bit better way than uh the the built-in tooling uh does that so um that’s that’s all i know about it though that’s what i know that is that is most i know no collusion wants to know how can i get over a million application locks per second the most i can get is 325 000 per second uh try harder that’s what i would do try harder doesn’t sound like you’re trying very hard so eat right get eight hours of sleep and literally just try harder is all you need lar says what do you like to use to replay workloads uh i don’t actually do a lot of that i just don’t um as far as stuff that i know that does it uh what was it benchmark factory is good but expensive um so this used to come up on on other office hours quite a bit where someone want to know how to do that and tara would always talk about her experience where she’s worked at like very large companies uh that had like entire teams dedicated to uh setting up workload tests like they would like they had their own in-house homegrown software uh and they would set stuff up to run to do like have like a like a like a production quality workload inserts updates the leads stuff like that uh yeah distributed replay is a piece of crap it is not fun or funny or useful or amusing it’s like it’s like wow what a great idea imagine being able to do that then you go to use and you’re like hell no i don’t want it i wouldn’t want anything to do with this this is awful dan says if you have a 56 core cpu on a 2012 standard instance of sql server can that cause issues with performance no uh i would so i would say no it’s not going to cause issues with performance standard edition certainly isn’t going to see all those cores just because of the of the limits imposed on it um you know 2017 you can see 24 cores so i the the next question i have is that uh is that um 56 physical cores or is that 56 logical cores with hyperthreading that’s another question who knows let’s see do i have questions coming in from anywhere else no not yet good good good everything’s quiet it’s just you i think we’re alone now what about memory allocation to the cores uh sure so that’s why i was asking uh kind of about the setup over there is um if it’s like if it’s a four-way if it’s a well no maybe yes uh so run sp blitz and see if you have memory offline uh it’ll it’ll warn you about that so uh sp blitz will warn you if you have schedulers offline if you have sequels if you have schedulers on your box that’s equal server isn’t using or if memory is allocated to a numino that sql server can’t currently see so yeah i guess like in like a weird situation if you had like a four-way proc but in like every numino had memory attached to it then yeah you could run into some weirdness with that but um run run blitz and it will tell you about it no collusion insists that having 56 cores on standards and might throw off worker thread counts yeah it very well may very well may uh trying to think of some other funny stuff that might happen let’s see i don’t know like i get a parallelism i don’t know like like i’m just trying to think about like like parallelism and stuff like that but i don’t know it’s it’s one of those things that i’d love to test out but i’m just not good i’m not good at like theoretically figuring out if like one like wait if one numino has like three schedulers hooked up like what will happen yeah look at that look at no collusion with the with the smart answers kavil says if i have a four socket 20 core hyper threading enabled total of 80 logical processors what is my best option to set mac stop eight eight eight just leave it at eight eight’s good uh i wouldn’t i wouldn’t go to one around much past that if you if you set it higher than that it doesn’t really like like so like eight really is kind of like a weird sweet spot for mac stop i have never uh seen an entire workload that benefited from a super high max stop i’ve seen like some queries that benefit benefited from higher max stop but overall setting max stop isn’t just about like you know single query performance either setting max stop is about um you know uh making sure that parallelism doesn’t harm concurrency because if you set max stop to like a very high number then you can like the number of like cores and threads that get spun up for a parallel query can increase exponentially you end up with like a crazy amount of threads allocated to parallel queries and that can that is generally considered a bad thing because the more of these you know super parallel queries you have with lots of threads assigned to them the fewer queries you can run overall you’ll run right into thread pool weights so setting max stop isn’t just about oh gee what’s the best setting for you know like general query performance it really is like a it’s a concurrency setting as well and if you’re not setting cost threshold appropriately aside from that you know setting max stop is you know only only half the battle so careful careful with that uh i would i would start with eight though eight is eight is a nice number for max dot eight is eight is a pretty decent uh sweet spot for query performance and it sounds like the number of cores you have uh hopefully hopefully you have adequate concurrency with max stop set to eight but you might have to you might have to chop max stop down to like six or four you might have to raise cost threshold up to like from 50 to like 75 or 100. who knows i don’t know if you if you’d like more specific advice uh i would i would love to love to help you out in a consulting capacity but all i can give you is vague recommendations set max stop to eight set cost rest so for parallelism to 50 and and tune accordingly let’s see here check db can benefit yes check db can benefit from using the number of cores in a single numenode but who does that god uh let’s see zane says some stuff about some things uh no collusion says if you ever have your work spread over too many schedulers and some schedulers are significantly more busy than the overall query time will degrade because some threads will finish work early that’s also true if you have like two cores though because there’s all those uh there’s all those old craig friedman demos where he make you know he like sets up like a two core vm and he makes one core very very busy with like a max stop one query and then runs a parallel query and you can like see the imbalance i think as long as microsoft hasn’t torn his blog down uh you can still find one of the posts where he does that uh so if you if you’re really interested look for like uh craig friedman post about like parallel scans and i think there’s a demo in there of that happening yeah well i hope you’re not look kabil says uh he’s a consultant too well gee i hope i hope you’re not billing anyone for this time careful on that you’re here you are seen sir you are seen cost threshold for parallelism i start at 50. yes that’s a good starting spot um but it’s it’s funny how that like it’s it’s it’s also it’s also amusing to me how it’s like you have cost threshold set to 50 and that certainly prevents like low cost queries from going parallel but then it’s like like you have like some low cost queries it will never break like like 10 and then you have like like the really bad queries that i’ll have a cost of like 2000. and so and so you’re sending me a look at this like well well cost threshold of 50 is good like it’s keeping like the really small ones from doing anything weird but the man those those those bad queries are really bad so you gotta be a little careful with those uh let’s see no collusion has oh boy no collusions on a roll today holy cow there are some latches and spin locks use some types of queries that don’t scale well with max dot yes it would be nice if someone blogged about those who’s smart and who knows these things and has 96 core servers or or if they’re not haven’t been downgraded to 48 core servers who can who could write about these things with some authority that would be wonderful nesting transaction full is used for parallel inserts into heaps and column stores a good example going above maxed up eight may not help with runtime at all might not you’re right you’re right about that again it would be it would be nice if if someone smart wrote about these things in some detail because the the the only the only other the only person i know who might do that wears a funny hat why should i ever enable auto growth i i enable auto growth all the time can you imagine a job where you you got alerts that you had to go grow a data file i would lose my damn mind and quit auto growth is a wonderful thing auto growth auto growth saves your life just don’t set it to something stupid like a percentage 10 of one gig is not the same as 10 of 100 gigs so i love auto growth i would keep auto growth why not use it for log files i don’t i don’t i don’t know who you’re asking that question to if only conversation threading worked better in here yeah one meg auto growth is a funny one uh that’s one of those one of those cute things that’s like you just well you know the funny thing about the one meg auto growth is at least you’ll never wait a long time for it if you like if you if you were just constantly growing by a meg cool you’re never going to wait a long time to grow by one meg let’s see no collusion says i like auto growth for log files and not data files well aren’t you just a pre-sizing madman oh so okay so then then how how big do you make your data files then if you don’t like them to auto grow that’s what i want to know this is where this is where we see some absurd number like in the in the terabytes i guarantee it you guarantee it how many terabytes as big as required you size queen you dirty dirty size queen how do you how do you know how big is required it’s not that big oh thanks for letting me know that’s the uh lars says he read recently that you might want to allow for enough space to hold two copies of your largest index during rebuilds yeah but who rebuilds indexes that’s for the birds don’t rebuild indexes is a waste of time go do something important like check db or backups no inclusion says i created a database with 72 data files huh how many on purpose like are you sure you didn’t like it wasn’t like a loop that got out of control how big were those 72 data files that’s what i want it must have been huge huge what do you put on 70 what do you put across 72 data files 1.5 so you had i don’t know whatever 70 100 and something gigs i did a little math on the top of my head yeah i want file group that would be funny that would be funny problems with pfs contention yeah that’s a good reason to create that pfs contention is not just for tempdb folks if you are creating enough objects in your data files you can run into page contention in the same way that you can run into it in tempdb and it will show the same exact weight types it’ll show show you those same exact where is it uh the page latch up page latch up weights yeah show you those in the exact same way and there’s no real differentiation like sp who is active can show you like uh which database are happening in but uh if you’re just looking at weight stats and you’re like holy crap i have these page latch up weights what’s going on i already have tempdb set up right could be happening in a regular data file could be anywhere just don’t you could you could just take a a cautionary tale from no collusion and not create well like millions of tables in your user databases or in a short span of time you might run into some scalability issues rcsi is a magic bullet not for pfs contention it’s a magic it’s a magic bullet for uh readers and writers not arguing with each other but not unfortunately for pfs contention that’s not going to help too much uh microsoft is doing some cool stuff with uh with tempdb’s system tables being in memory coming up and it’s in sql server 2019 so that’ll be fun it’s a magic bullet for developer sanity yes that is true otherwise otherwise you make developers spend a lot of time writing no lock hints and they could be doing much much more productive things with their time than writing no lock another quick like the question always comes up when i’m talking to people is uh about if if no lock is different from read uncommitted they think like like one like one is somehow magically better than the other like no it’s the same thing just with less typing well i guess a little bit more typing up front a little bit less typing overall yeah no one wants to solve deadlocks deadlocks are the most boring thing in the world to solve except parallel deadlocks parallel deadlocks make the sexiest deadlock graphs they they they look so cool though it’s like amazing that that kind of line work can be done in a tool designed to write queries and manage a sql server instance with but they’re pretty nifty uh and i like those regular deadlocks this is like i took a key lock i took a key lock i took a key lock i took a uh dull dull dull get up kapil says uh any link that can help me in setting up a four node geo cluster or multi subnet cluster you know what’s funny um my friend sean who works at microsoft had a blog post about that but his blog got taken down recently so i had a link but the link doesn’t work anymore so sorry about that uh you’ll you’ll just have to i don’t know read the documentation let’s see uh someone who who got who got very drunk and didn’t have a good time the next morning at SQL bits is saying they came into a job with deadlock priority low everywhere yeah they that would be a that would be a deadlock priority that would be a deadlock issue huh yeah you look great the next day did you ever figure out where that blood came from i take that as a no yeah that was a good time if anyone if anyone needs a a good place to drink in manchester go to the britain’s protection they have 300 kinds of whiskey and uh and and we have drank them all so that was a good time just stay away from the irish whiskey in a green with a green label because that’ll that’ll do you in yeah that’s where we were yeah good question yeah that was like there’s like a bar within walking distance from the hotel and you didn’t know that’s where we were you wouldn’t you would did what did you like you and martin like bump like bump heads real hard and knock each other out maybe let’s see uh yes where else would we have been that could that could explain it yes that could explain the blood blood can come from many mysterious places mostly inside things though inside living things you could just keep a jar of blood with you sprinkle it on stuff where did it come from i don’t know all right all right any other questions anything else fun going on what’s everyone doing it’s friday some of you should be doing things that are better than this it’s at least it’s 5 30 where you are see darren says last week or week before you said you were doing some century one mining scripts are those still forthcoming yes i actually just scheduled that blog post last night uh that’ll get published you know let me see here uh i’ll go look i have magical powers where i can see when blog posts are gonna happen unfortunately i think i’ll my website is slow who do i call if only there were some kind of website performance tuning expert i could talk to about why my website is slow by the way that that’s just that’s just one query that i have lined up it’s nothing i don’t have like a whole series of them it was just one that i was particularly proud of that’s just one of the things that i’m not going to be able to do it i blame wordpress kenneth uh stephen cutter says any good tips for moving file stream data yes put it in the garbage stop using file stream an awful awful awful feature that is hi devdba says uh trying to get toad to recognize my new tns name dot aura file bad time yeah a lot of things with oracle are a bad time sorry about that uh use sql developer instead i hear jeff smith says that that’s much much better uh let’s see wordpress unshared hosting uh do i have that i don’t know what i have managed maybe i don’t know how do i get a how do i get a faster website who do i pay for like enterprise edition of wordpress zane says oracle sql developer is my nightmare well you should open a ticket about that you know what you should tell jeff smith on twitter that you don’t like don’t like it let’s see here i gotta answer that question though where are my posts where are my posts is uh they’re still loading so i blogged so much i made wordpress slow zane just doesn’t like oracle zane has oracle problems let’s see oh yes so the query that uh i wrote for century one will get published april 10th sorry about that darren uh you know what tell you know what i how about this just because darren because you asked i will have that go out uh when can i get rid of this thing what’s tomorrow the 23rd i have blog posts scheduled until april 12th that’s how crazy my life is that’s how little i have going on with myself so let’s see today’s the 22nd uh and that was uh uh yeah you know what i’ll uh i’ll do i’ll do a quick thing i’ll do a quick schedule change and uh when when i get off here and i’ll have the the century one helper query go out on uh i guess monday of next week so watch watch for it monday the 25th that’s what i’ll do for you because we’re best we’re best friends now and i don’t mind changing my blog post schedule for you let’s see uh lara says not small table with old data but typically only last two weeks of data queried i’ve heard that partitioning for performance is not a good idea but need to keep stats updated to retain good query plans uh so that’s where something like filtered statistics or filtered indexes would probably be helpful uh where so like even so so here’s the thing is even if like you partition the table you’re right it wouldn’t be helpful for performance and it also wouldn’t help stats because while sql server will sample statistics at the partition level the final histogram that it puts together is the same 200 steps for the entire table so it’s not particularly helpful uh for that so it won’t help you any that won’t help you any better like you could update stats for a single partition but it’s not good it’s still going to be the same it’s still going to do the whole 200 table things they’re not gonna i’m not gonna help you with that you really need to do is create like filtered stats or uh filtered indexes that will have filter statistics on your hot data and that way you can figure that way you can keep that chunk of data um you know better stats up to date it did so there’s always that dean says uh dude has a bug that caused it to get stuck when opening okay i don’t want to read that uh darren’s yep no problem darren tana says beats me i only have them uh i don’t know what that means uh yes spiffles let’s see uh did a 3000 word blog post the last day i’m taking a week off what would ever possess you to put 3000 words in one blog post that’s a book that’s like five blog you could have had you could have had like a week of blogs with that why would you put that all in one blog post there’s nothing nothing good about a 3000 no one’s going to get to the end of that no one’s going to get to the end people are going to die reading that thing it’s like a farewell to arms or remembrance of things past it’s far too long to exist sounds like half of a paul white blog post paul thanks to my expert tutelage has gotten much better about breaking his blog posts up into consumable chunks you have me to thank for that that rogo’s blog post was one blog post said four and give it recaps tldr you need that stuff no one’s as smart as you yes that that likely is where the blood came from banging banging your head against a laptop for 3000 words i would do the same thing yeah i think i think rogo’s rogo’s was originally one post it’s like you are out of your damn mind what software do you use to compose blog posts uh google chrome on wordpress uh i don’t use the block editor uh and i don’t use what’s that god i don’t eat gutenberg that thing is such a piece of crap writing blog posts in gutenberg maybe you want to stop blogging i eventually like there was a plug-in to use the classic editor and i’ve never looked back god gutenberg such garbage uh hang on a second my wife is texting me okay all right apparently my wife is ready to go wine shopping so i’m going to get out of here it was lovely having you all this week uh i will see you next week and we will talk about more stuff and things for sql server or whatever you bring up i will maybe maybe i’ll start reviewing wine on here because that would be the only reasonable use of a friday is to drink wine in front of all you people anyway uh thank you all for coming i’ll see you next time goodbye uh remember this week’s sponsor is a thing that helps babies fart so thank you thing that helps babies fart is is is is is is is is
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.