I recorded an Office Hours stream answering a bunch of your questions quickly, but…I forgot to turn on my microphone, so the video only had the sound effects:
So here are the questions in text form instead:
Q: 22DBA: what performance recommendation you see people give all the time but it actually doesn’t work
Lowering fill factor server-wide. They somehow think that making your table larger will make things go faster.
Q: Not the Licensing Police But….: I work for a company that has 18 SQL Servers (mix of Standard and Enterprise) that I know are not licensed. I’ve flagged it and management simply don’t care about them not being licensed, and want to leave as is. “We need to save money” -What would your approach be here?
Put it in writing. Email your manager with a list of SQL Servers, their core count, and the approximate licensing fee. Don’t do it as an accusation, just put in writing that we have this many SQL Servers, and I don’t think we’re licensed for it. It’s then the manager’s problem. If the company doesn’t solve it, then it’s up to you to decide whether you want to keep working for that company – because if they’ll screw Microsoft, they’ll probably screw you when they get a chance, too.
Q: Higgins: Is Disk Queue Length a good metric to monitor for SQL server? If so, what are recommended values?
No. (Good job on the Ferrari-related name though, hahaha.)
Q: Alf: For partitioned tables, do you ever see partition keys on fields other than dates? If so, what are they?
No. I’m sure they’re out there, but I just don’t see ’em.
Q: Wally: When should “DBCC UPDATEUSAGE” be run? Is it expensive like CHECKDB?
After you upgrade SQL Server 2005 to 2008. I haven’t run it in over a decade though.
Q: Keld Rasmussen: Hi Brent, thanks for great posts. When I do select I some times use Read uncommitted because otherwise I lock the database for the application and the application updates fails. – How do I read committed from database without locking for the application? Best regards Keld
Check out RCSI.
Q: NotCloseEnough2RetirementToStopLearning: Hi Brent Any advice on how to move from a production DBA role to a Data Architecture role?
Talk to the data architects at your company to start gradually transitioning into that work. You probably won’t be able to leave your current company and jump to a new company in a data architect role – that jump is too far.
Q: Krishna: How many hours do you sleep and work per day on average?
I usually sleep 8-9pm to 3-4am, plus a 1-hour nap in the afternoon. On the days when I work, it’s usually 8-10 hours, but I just try to work as few days as possible.
Q: .NET User: Hi Brent, Love your office hours, thanks it helps a lot. In previous session someone asked about soft delete and you suggested to add IsDeleted field to the table. I wonder what are your thought about using soft delete vs temporal tables approach? Thanks.
Temporal tables make copies of the entire row as the row changes – that’s a waste of space when you just need soft deletes.
Q: Ezra: The previous CTO convinced all the developers to always use NOLOCK in all their sp’s. How do we change the mindset for this bad practice?
Search for Brent Ozar nolock and you’ll find a ton of videos and demos.
Q: Wally: What is your opinion on using views as constants holders? e.g. CREATE VIEW vStatus AS SELECT 1 AS Active, 0 AS Inactive, 2 AS Paused. Could multiple CROSS JOINs to similar views cause compilation issues?
It’s dumb because there are no statistics on the output of views. Use tables instead.
Q: Midwest DBA: What are your thoughts on Ottertune.com? Seems like a cool technology. Do you foresee Microsoft employing Machine Learning in this way to improve database/server performance?
Love the idea of OtterTune. Microsoft doesn’t appear to be taking this approach. They’re hard at work figuring out Cost Threshold for Parallelism. Gotta learn to crawl before you learn to walk, I guess.
Q: DBA_Mufasa: Hi Brent! What’s your best approach for tracking tables usage in SQL databases, in order to find tables that haven’t been touched (for eg. no select, updates, or inserts ) for a a defined amount of time. Considering that table statistics get cleared after server restart.
What’s the problem you’re trying to solve? If you’re trying to drop tables, go talk to the users.
Q: Morty: How do you determine the optimal number of DBA’s for a given product / company?
Q: CKI: Management wants to create a copy of the production database for reporting purposes. The new “reporting” copy should be refreshed nightly with production data. Production database is on AWS Web Edition 45GB. What is easiest way to do it? Thank you very much!
Q: Quincy: How do you determine which SQL changes are low risk and which changes are high risk?
Whoever makes the change needs to list at least 2 ways the change might screw things up. If they can’t come up with 2 ways, they don’t understand enough about the change. Then, think about the business risks for those problems.
Q: Brutus: How do you determine the optimal Windows OS page file size for a bare metal SQL server?
I read the documentation. Doesn’t work for everybody, I know – some people struggle to read.
Q: Sandeep Pawar: I am a SQL DBA with 12 years of experience and also learning and trying my hands on Postgres as well. Is SQL+ Postgres DBA combo would be beneficial? Thanks
I wouldn’t go learning random technologies. Ask what your company needs, or if you want to change companies, think about the technology you wanna use for the rest of your life. Don’t gamble on what unknown companies might like – companies need janitors. Does that mean you should pick up janitorial skills?
Q: Stimpy: For concurrently executing queries against the same tables, will SQL Server share the read operation data between the two different queries?
Q: cyrpl: Moving SQL query analysis out of SSMS to Azure data studios will be a big hill to climb for most DBAs, where are you on that hill Brent? Have you written any Jupyter notebooks yet, it looks like Microsoft and others are moving all in on this, what do you think?
Q: Twiki: What are the top SAN concepts that are beneficial for the SQL DBA to know about?
Start learning the storage your company uses. Often, it’s not SAN at all, but instead it’s cloud-based storage, and that’s completely different.
Q: TJ: We have 1TB database containing 950 GB single table. This table has 95% of data that is not needed anymore. Application team is recommending a slow and gradual delete of the data from front end, but it can lead to performance issues as you know. Can you advise best approach
If you’re going from a 1TB database down to 100GB, don’t do deletes. Insert the data you’re keeping into a new database.
Q: WhatsUpDocs?: Hi Brent, have you ever needed to look at business documentation (check business rules/logic) when consulting or as an employee, but it was severely lacking? Recently joined a different team in work and trying to find simple answers to questions is an uphill struggle…
The vast, vast majority of companies don’t document their technology. The tech is in a constant state of flux, and it’s a miracle if the tech even works, let alone is documented accurately. If you’re the kind of person who needs accurate, up-to-date documentation on the tools you use, you’ll be happier working for very large, slow-moving companies with compliance needs. Think giant global financial corporations.
Q: Neil: When a query goes parallel, does it use the MAXDOP number of threads or only as many threads as it needs ?
Generally speaking, it starts at MAXDOP + 1 coordinating thread.
Q: Erdem: Why does multi column index only have single column histogram step value?
Because Microsoft chose to fit stats in an 8KB page, and that required design tradeoffs.
Q: Conan: Non Tech question: Next Travel plans if any?
Israel this fall, Antarctica this winter.
Q: Preben: What is your least favorite operator in an index plan? E.g: EriK Darling’s eager spools
I thought a lot about this, and to refresh my memory, I looked at Hugo Kornelis’s operator list. My current obsession is the Parallelism Gather Streams operator, but I’m still in the honeymoon phase with it because everything seems so cool. Each new thing I learn about it, I think, “Ooo, neat, I can understand why they made that tradeoff.”
When I was looking at Hugo’s list just now, I cringed a little when I saw the Bitmap operator. I’ve managed to get through almost 25 years of working with SQL Server without having to know what that operator did. I’d kinda always had it in the back of my mind as, “I should learn more about that someday,” and so I read through Hugo’s explanation just now. I nodded my way through it, and closed the browser tab like Grandpa Simpson walking back out of the club. If you made me pick, that’s probably my least favorite.