Sometimes, y’all post questions at https://pollgab.com/room/brento and they don’t get a lot of upvotes, and reading through ’em, I bet it’s because other people don’t quite understand what you’re asking. I think there might actually be a good question at the heart of it, but … I’m just not sure what it is, and it needs to be rephrased.
Here’s a rundown of some of ’em that came in recently. If you recognize one of these as yours, you’re welcome to re-submit it at PollGab, but with clarification.
MonkeySQLDBA: Recently watched one of your video on fragmentation, great stuff. quick question, if your fill factor is set to 80, will the internal fragmentation get to 70% faster than leaving fill factor to 0?
Zoom out: what problem are you trying to solve? What’s the action that you would take based on this knowledge? Think about those, and then re-post the question.
Piotr: If SQL Server and PostgreSQL were aircraft which aircraft models would they be? F14 vs SU57?
I love the question, but I don’t know enough about aircraft to answer it. I don’t even know if those two airplanes are good, bad, or the same, hahaha.
Keith: Hey Brent! Is it safe to upgrade my Azure SQL database from compatibility level 140 to 160 just to be able to run the GENERATE_SERIES function?
Is it hard for you to create a numbers table? Like, really? I’m not being sarcastic, but if you need a list of numbers, why wouldn’t you just create one?
I Cannot Do This Alone: I’m sure it has always been hard to get skilled help, BUT… have you ever heard of “non-tech” companies sharing there tech talent in like a pool? I know I’m on the hook for a trashing here, but better by you than my CIO 😉
I think you’re describing outsourcing – having a group of full time tech employees that you can call on whenever you need them, and just pay for what you use.
Henry: I am planning on an in place SQL Server upgrade from SQL Server 2012 to 2016 on a Windows Failover Cluster. All of my databases are in Full mode with Transaction logs taken every 5min. Should i put the databases in Simple during the upgrade?
Don’t upgrade in place, period.
Government Cheese: How do you like to measure IOPs for SQL Server storage (bare metal and cloud VM)?
First, I don’t, but even if I did – are you talking about measuring how many they already consume, or how much a new server provides? If you’re asking how many they already consume, how is that useful? You don’t know if the users are happy or not, or if you need to reduce storage throughput or increase it.
ConsultantWannabe: Hey Brent, you teach we should stand next to expensive things (like SS or Oracle) as contractors/consultants, any advice to identify our own expensive thing to stand next to? Obviously apart from standing nex to SS. Thanks
Are you … asking me … how to find out how much things cost? I’m confused. Why wouldn’t you just … ask management what the most expensive thing in the shop is?
Jeremiah Daigle: I have a server that has 2 8Core CPUs, and only have 8 enterprise core licenses. I was planning to just remove one of the CPUs, but ran into issues not having the blank to put back in. Is there anything to be concerned about by turning down each CPU to 4 cores in bios instead?
Having a blank to put in? I’m not sure what you mean – you shouldn’t need a “blank” CPU. I think someone’s pulling your leg, like they’re telling you to go get blinker fluid.
Maksimilian: What’s the best technique for a SQL sproc to self audit the params it was called with?
Self audit? Have the proc log them to a table. (I think I might be misunderstanding the question because it seems so obvious, but if you want to log something, and you’re in a database, well, uh, put it in a table.)
Eduardo: Linked-in provides automated public notification of completed course training. How should DBAs notify potential employers of completed Ozar training?
You mean … how do you add things to your resume in LinkedIn? I’m genuinely confused – are you asking how to edit your resume? I’m guessing you just click Edit on your profile, right? Put in whatever text you want there. If you’re saying that you have a problem because you need automation every time you complete any of my courses, and I have so doggone many of them, then stop putting each one – just put Fundamentals and Mastering.
Hangman: When is sp_whoisactive context_switches a useful metric for performance troubleshooting?
Ask whoever told you to look at that metric. Otherwise, don’t walk into the airplane cockpit, point at a gauge, and ask the pilot, “Hey, what’s that dial mean?” That’s not an effective use of anyone’s time. SQL Server is way worse than an airplane cockpit: there are precisely 1.21 gigawatts of metrics out there, and most of ’em just aren’t useful.
Eduardo: Is it good idea to start identity integer cols for new fast growing tables at the max negative value for a big int? Do you see this much in the field?
It’s fine. I almost never see it.
Tony: Will you be purchasing TSQL fundamentals 4th edition?
No. I’m sure it’s good, and I’m sure you doubt my T-SQL abilities, fair enough, but I’ve moved on to learning other stuff.
Isaac: How do you find all the queries that are using the kitchen sync query pattern (Col1 = @Col1Val or Col1 IS NULL) AND (Col2 = @Col2Val or Col2 IS NULL)? How do you find the worst of the worst?
Instead of looking for anti-patterns, ask, “What are the 10 worst-performing queries that I need to tune, and what are the anti-patterns in those?” That’s what sp_BlitzCache does.
TheCuriousOne: Hi Brent! From your perspective, is there any open problem/issue preventing a problem free upgrade from SQL Server 2019 to 2022 and if so, what are the gotchas to look out for?
Microsoft used to publish detailed upgrade guides for each version of SQL Server, but they stopped doing it. Check out the most recent one from 2014 (PDF) and that’ll give you a rough idea of how complex it is to migrate an existing environment.
Eduardo: What is your favorite graph database and why?
I don’t use any myself, so I’m not qualified to answer that.
Wasn’t_Me: We are thinking about switching from Azure to AWS. On docs.aws.amazon.com I find this phrase: “When you set up an Amazon RDS DB instance for Microsoft SQL Server, the software license is included.” What?? Does it means that on AWS I don’t have to pay SSRS, SSIS, SSAS?
Licensing is included in the hourly rate, yes. Amazon also offers bring-your-own-licensing. Keep in mind that you said RDS, and RDS doesn’t have SSRS, SSIS, and SSAS – you’ve got a lot more reading to do. Fortunately, I’ve got a training class to help.
Marian: Hi Brent! Have you even been to Romania? Would you consider attending some big tech event in Romania in the nearby future?
No, and since the pandemic, I’ve cut back a lot on my conference schedule. I’m sure Romania is nice, but I did a quick Google search and didn’t see any SQL Server conferences in Romania. I’m not really interested in non-SQL-Server conferences – when I want to learn other technologies, I tend to use cheaper/easier methods rather than traveling.
Neil: I set up all my SQL servers with TCP/IP enabled only. A developer is trying to connect with named pipes. Should I enable named pipes or force them to use TCP/IP?
I don’t have any opinion on this one whatsoever. (I don’t think I’ve ever disabled named pipes.) Why did you disable it?
18 Comments. Leave new
Actually the question from Jeremiah Daigle is interesting. BIOS settings to reduce the number of CPUs presented to the OS. If for example I need to start with less cores due to budget limitation, would that be a valid way to reduce license cost per core until I am abl to cough up the few extra bucks for SQL Enterprise on the remaining cores ? Does it affect perfomance of the CPU in any way ?
A *different* question worded like yours might be interesting, but that was *not* Jeremiah’s question. Make sure to read the first paragraph of this blog post.
I’m a bit of an aviation nut, so the F-14 vs Su57 question piqued my interest. I think it was intended to be more of a fun ice-breaker question than anything of practical use (perhaps it was an attempt at an interview question like ‘If you were a color, then what color would you be?’). The two planes were featured in Top Gun: Maverick (https://militarywatchmagazine.com/article/top-gun-f14-vs-su57-real-world-fight-possible). The TLDR version is that the F14 would be more like SQL Server 2008R2 (maybe 2016), while the su57 would be closer to SQL Server 2019. I don’t even pretend to know enough about PostgreSQL to offer an opinion.
I haven’t even seen Top Gun: Maverick, heh. I really should, now that I’ve built a home theater, but… every time I’m in there, I just end up playing Dead by Daylight.
if it helps, the F14 Tomcat is the fighter from the original Top Gun movie as well (it was retired from the USAF in 2006). The Su57 was officially introduced into Russian service in 2020.
The USAF didn’t fly the F-14. Only the US Navy. USAF predominantly flew the F-15 and F-16 during the years the F-14 was in service.
With the information I have on military planes (and as a real amateur pilot on a dozen small planes) and limited Postgress experience, I would say it is a Mig21 or the original F16 A/B: decent performance, but pretty basic out of the box. SQL is an F15, to keep the same era, more sophisticated.
pretty tough on the F14
Hey Brent! Regarding your answer about RDS – RDS does in fact include SSRS, SSIS and SSAS, if you want to use those services. SSIS is for 2016 and 2017, but SSRS and SSAS will
work with 2019 as well.
Thanks, that’s great to hear!
About the named pipes question. I think I’ve read somewhere long time ago that named pipes should be disabled for FCI’s. So we always disable it on our SQL clusters. Can’t find the article anymore though but this one still suggests to only use TCP/IP for FCI’s: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/install/before-installing-failover-clustering?view=sql-server-ver16
disabling unused services (such as named pipes) is a good hardening practice. If you know nothing is using it, having it on provides no benefit while also providing attack surface area for future exploits. I wouldn’t turn it off arbitrarily if it is on. I think it may default to disabled on new installs however.
They’re saying they *are* using it.
The CPU blank may be a real thing, but a bit weird for these days. In the old days multi-processor servers needed to have a special blank in the socket to maintain some electrical connections and capacitance, I had an old quad-CPU with Intel Xeon @700 MHz that had such a thing. The server came with 3 blanks, in case a single CPU was mounted, when I added the second CPU storing safely the blank was not a priority for us and I think we lost it. But these days for a dual socket system the need for blanks seems suspicious, if it is a 4 socket it is still weird.
I’ve worked on servers that needed an airflow baffle in place of a missing CPU to ensure that the proper airflow went over/through the other CPU(s) instead of just blasting through the now-open space. Maybe that’s what Jeremiah meant by “blank”. Here is an example: https://serverevolution.com/hp-289549-001.html
Thank you for your site. It has helped me transition from Oracle to SQL Server over the years, and help maintain my proficiency. I’ve mostly been a lurker. My company is too cheap to pay for your classes, sadly. But in a rare moment of giving back I have found a subtle and nasty… well… not error, but something DBAs should know. Perhaps you already know what I discovered, or if you never use TRIGGERS, you won’t stumble upon it.
This oddness has to do with TRIGGERS. Triggers in T-SQL have two pseudo-tables available to them, DELETED – which is the old row(s); and INSERTED, which is the new row(s). Unlike Oracle Triggers, the ones in SQL Server are always a Set (0 to many rows) based. A trigger fires once per transaction, not once per row. Oracle has both Row and Set based Triggers BTW.
Now here is the warning. I had to write a TRIGGER that checked the changed rows via the INSERTED pseudo-table. During my test my TRIGGER kept failing. I was checking certain columns in the INSERTED pseudo-table to see if I needed to set a default value for a column I will call RATE. My trigger tested to make sure that the application code did not set the RATE, AKA “WHERE RATE IS NULL”. It didn’t work. Then in my test update I added explicitly “RATE = NULL” to my SET. It worked.
So, in an update, if you do not specify the column, that column does not seem to be in the INSERTED pseudo-table at all. A test of “RATE IS NULL” will fail, because the column does not exist, but will also not raise an error.
Now my issue is telling the Application coders on how to test this, since they are already abusing TRIGGERS to avoid deploying code changes. It’s complicated, but that’s a DBA’s life.
Thanks again for the site, and your DB Blitz scripts.
I’m not sure how your trigger was defined, and if you were getting an error or if the trigger just wasn’t working that way you wanted, but all the columns from the table are in the pseudo tables, whether or not they were inserted or updated.
Checking for anything = NULL will never return anything because nothing can = NULL in the SQL Server world, hence we have to use IS NULL.
I would recheck the trigger logic because the scenario doesn’t sound right. Posting a code sample and question on Stack Overflow would get more detailed help.
Well unless your developers are fooling around with the ANSI NULLS settings. then I guess NULL can = NULL.
In any case, I would still recheck the trigger logic.