Some questions that come in at https://pollgab.com/room/brento have such straightforward answers that I don’t need to cover them on video. It’s time for a rapid-fire speed round:

Q: Uncle Buck: Is there a good way to tell if a given include column for a NC index is being used at all?

No.

Q: Steve E: Hi Brent, In the past you have shared links to a “reading list” tool you used to bookmark various online articles and share them with others,what was the tool please? I’d like to bookmark the various articles I’ve read over the years and be able to easily share those with others

For bookmarks I use Pinboard, and here are mine. For RSS feeds and newsletters I use Feedly, and here are my shared items.

Q: FrankieG: When faced with hundreds, if not thousands of NonSargable predicates over a wide range of solutions/products that have been in production for years in a huge base of legacy code how to begin to “fix” the problem?

Use sp_BlitzFirst @SinceStartup = 1 to find your server’s top wait types, and use sp_BlitzCache to find the top queries causing those wait types. I explain more about that process in my How I Use the First Responder Kit class.

Q: Faruk: Is it a fair expectation for employers to expect DBA’s to also write reports (SSRS, Power BI, etc)?

Generally, development and administration are separate job roles.

Q: Luis: Any performance gotcha’s to watch out for when the query plan is using the Filter operator to satisfy a non-sargable search predicate?

If you understand enough to write the question, then you already understand the answer. Performance will be bad.

Q: Last Action Hero: What are your thougths about contained databases?

I explained the problems with ’em here in 2009: part 1, part 2, and part 3. As I predicted, most DBAs have still never seen these in the wild, nor do I expect them to in the near term.

Q: Jose Luis: I need to migrate my existing DWH in SQL2012 to a new OnPremises SQL 2019. Should I consider AlwaysON for it? maybe SQL Failover Cluster? Or just a BIG Standalone Server? Most of my DBs are in Single Recovery Model.

For personalized architecture planning, hire me for consulting.

Q: Reed Richards: What is the best resource for extended events training?

Start with Jonathan Kehayias’ series 31 Days of Extended Events.

Q: Punxsutawney Phil: I thought that when you refresh a database on a nightly schedule for reporting/production use, that’s considered a “groundhog day” method and should be avoided. Yet, I’ve seen you twice in recent months recommend that as a solution to refresh production reporting databases.

Groundhog Day refers to the practice of reloading data via logged methods: inserts, updates, deletes, SSIS, etc. Restoring a backup is not a logged method.

Q: Leon Spangenberg: I keep seeing trace flag 3604 being turned on and off in my sql log. Why would someone do this?

That is detailed here.

Q: Max_Null :A very small table in Azure SQL used to keep track of locking in the application (~15 columns, no triggers) and has no more than 3 rows at any given time. Other than stale stats and blocking, is there any other reason that this table would be slow for a simple SELECT?

If you’re trying to track locks in a table, you’re going to hit locking problems, period, full stop, end of story. Bad design idea. Do something different.

Q: Alan: Do you think cheap cloud storage, like s3, will eventually replace old fashioned tape libraries and off-site backups?

For many of my clients, it already has.

Q: FrankieG: Hi Brent, can you talk about and explain why upgrading to the rewritten cardinality estimator (i.e. upgrading SQL Server from v2012 to v2016) causes serious performance degradation and the only way around it (that I’m aware of) is to force using the legacy estimator in settings?

Yes, I cover that and the alternatives in my Mastering Query Tuning and Mastering Parameter Sniffing classes.

Q: Debbie Downer: Does Stack Overflow db have more downvotes than upvotes? If so, why?

You can download the database here for free to find out.

Q: Stan Sitwell: Is PostgreSQL consulting as opportune and lucrative as SQL Server consulting?

I have no idea – I don’t consult on PostgreSQL.

Q: Pina: Should I drop temp tables at the end of my stored procedures? What if there is one stored procedure that calls a bunch of other stored procedures, each of which has several to many potentially large temp tables? Is there a performance gain to dropping them? A performance loss?

I cover this in my Fundamentals of TempDB class.

Q: Ravi Pratap Singh: what are the books need to cover for good SQL understanding

Check out this list.

Q: Does Basically Anything: Hi Brent! When looking to tweak MAXDOP from a “sane default” to the best setting for my workload, what metrics should I pay most attention to as I try different MAXDOP settings to test the performance difference?

I cover that in the parallelism module of the Mastering Server Tuning class.

Q: DGW in OKC: Have you ever used encrypted connections (like SSL) to a SQL instance? Is there ever a reason one might be required to do this?

No, but I’ve heard of security teams requiring it in some cases.

Q: Need4Speed: A friend asked if there was a way to bring a detached database back online if you are missing one of the three data files.

Detaching a database is dumb. Deleting a database is dumb. If someone did *two* dumb things to a database, I’d say you know what, I can’t trust those files. Let’s restore from backup. If that person tried to fight me, I’d say, “You’re the one who did two dumb things already – how’s about you step away from the keyboard and cool off for a while, and let the pros take over?”

Q: Kirk Saunders: Are there situations where you would use a RIGHT OUTER JOIN? I have found I can put that table higher in the JOIN structure and LEFT OUTER instead. I just don’t want to miss out on a solution option if there is a real strong use case for it.

Me personally, I’ve never used it.

Q: Have you tried turning it off and on: We want to implement RCSI on a (10k batches/sec / 4.5TB / 7K session) server to help reduce blocking. What QA do you suggest, and any books/videos to consult?

Check out the isolation levels module in my Mastering Server Tuning class.

Q: KIRBY W BURKHOLDER: While watching a recording of “Mastering Query Tuning” recently, I remember you mentioning a problem the CE has with “Ford” and “Mustang”. My friend has the problem but all Fords are Mustangs and all Mustangs are Fords. Vendor supplied code that he can’t change. Any suggestion

Leave a comment on the training class module. There’s space for longer questions in there, including PasteThePlan options to include the execution plan, so I can see the exact problem you’re talking about.

Q: George: What is your favorite way to measure storage speed in Azure SQL?

The fast/easy storage test is CrystalDiskMark. It’s not completely definitive – it’s just a hot-or-not test – but in most cases, the storage is nooooot hot, so I don’t need a definitive test.

Q: Neil: We always used default instances. Planning on a multi-instance server (in test) to host multiple web environments with copies of the same db’s on each instance. What are some ‘gotchas’ to look out for? I’d probably give each instance its own data/log folder names, anything else?

The term you’re looking for is instance stacking.

Q: Doug E: Do you ever bother with reconciling AWS / Azure bills to actual resource usage?

Yes, we’ve done it for SQL ConstantCare’s own back end during performance tuning.

Q: SQL Serenader: Hi Brent, We have seen an uptick in dropped connections. Do you think using the deprecated SQL Native Client 11 driver, instead of the more connection resilient ODBC 17 or OLEDB 18 drivers could have anything to do with our connection issues?

I have no idea.

Q: SeeCoolGuy: what is your opinion if you find your colleague forcing the engine join hint to use a hash join vs letting the query engine find it’s own join criteria?

That colleague might have attended my Mastering Parameter Sniffing class where I cover hint usage.

Q: Philip: I’m getting a warning against an Azure SQL DB saying “Non-parameterized queries are causing performance issues” with Azure wanting to turn on ALTER DATABASE Name SET PARAMETERIZATION FORCED. Are you able to talk about this a little, and how you would go about investigating?

I assume you’re getting that warning in sp_BlitzCache or sp_Blitz. Copy the URL into your browser and read that. I’ve written extensively there on how to fix it.

Q: Enzo: Hi Brent, As you like fast cars, will you be attending the first F1 race in your home town next year (Las Vegas 2023)?

Yes, if I can get good tickets.

Q: Roadtripping fool.: Any chance you can make these into a podcast like your old office hours? I used to make my family listen to them on road trips. Ah memories.

No, it took time and cost money.

Q: Michael Devor: Hi Brent, My friend would like to know if there are any special considerations or concerns when tuning indexes on a partitioned table?

Yes. Have your friend attend my Mastering Index Tuning class.

Q: SQL Crooner: What is your opinion of the Parquet file format?

I have no opinion. Never used it, never opened it. Nothing against it.

Q: Festus: What percent of the time do you see customer tables where the primary key / clustered index are on same vs different columns?

Way, way, way less than 1%.

Q: prasad: What r the steps we should take to learn sql in depth?

Use it, help other people solve their problems with it, and then teach it.

Q: Eric S: How many years before our AI overlords displace human DBA’s?

OtterTune is already working towards that for MySQL and PostgreSQL, and I’ll be excitedly watching their progress. I think it’s a cool problem. Given Microsoft’s lack of progress on the automatic indexing front, and given how little of that is coming in SQL Server 2022, I think our jobs are still safe for a long, long time.

Q: SQL_Ninja: Hi Brent, what is your thoughts about SQL server vulnerability assessment functionality or would you recommend any other third party software for similar assessment ?

I don’t get the chance to try every tool out there, so I’m not really qualified to give a good review.

Q: cloudy: Hi Brent, we are currently use on-prem sql server for dwh & reporting, my company is starting to run some workload on aws, what’s your take on migration to Amazon Aurora (we can change the application to support it)?

What’s the problem you’re trying to solve?

Q: Anatoli: What is the best cloud database technology / brand for a shop that has plenty of developers but doesn’t want a DBA?

Use the one most of your developers are most familiar with. That’ll result in faster delivery to customers.

Q: Joe: Hi Brent, Under what circumstances would you set Min SQL Memory to anything other than zero?

None, because I hate instance stacking.

Q: Maksim: What is your opinion of disabling the guest and SA users in SQL Server?

I don’t really care. My bigger issue is usually shared passwords, like app passwords, and disabling accounts doesn’t fix that.

Q: Haydar: Do you foresee any DB company acquisition / buyouts on the horizon? What company takeover is the odd’s on favorite?

I don’t track the business side of the industry.

Q: Gary: Hi Brent. What’s your take on the number of instances one Production DBA should be responsible for? Automation helps and it’s rare everything breaks or needs service at the same time, but it can be a burden for one employee to manage 200+ instances. Thanks!

See this post.

Q: Joel: Do you ever look at sys.dm_os_schedulers for performance related troubleshooting?

Yes. We talk about it in my Mastering Server Tuning class.

Q: ?akir: What is your favorite tool / technique for monitoring failed SQL agent jobs?

Alerts.

Q: Gözde: How often should we back up SQL agent job definitions?

Back up your system databases daily. Jobs are stored in the msdb database.

Q: Ronaldo: Is tech documentation reading a lost art? If so, why?

Yes. Words are great for searchability, but videos are often easier for beginners to approach. Thus the rise in just-in-time learning: people searching for something, finding a 5-minute YouTube video on it, watching it while doing the thing, and then discarding the video, and never really learning the technique. I’m fine with that. It works for beginners.

Q: Leon Spangenberg: Hello Brent. I am inserting row into an existing table. However when ever I do the insert with a predefined set of filter the insert just does not work. I don’t get an error its just like sql sits there and does nothing. Any advice?

Use sp_WhoIsActive to see what it’s waiting on.

Q: Brent: Why are so many of these answers pointing people towards a training class?

Because the answers are 30-60 minutes long, and that’s why I teach classes. I love these questions, don’t get me wrong – but I love them so much that I’ve built entire presentations to do justice to their answers.

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:

Oops.

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?

This.

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!

Nightly restores.

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?

Read this.

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?

Yes, I’ve written blog posts with notebooks. I can’t really teach with notebooks yet because they don’t have execution plan support.

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.

For my Office Hours streams on my Twitch channel, y’all post questions at PollGab and upvote the ones you’d like to see me discuss on the stream. However, not all questions require a video answer. Here are some simple no-brainer ones:

Q: BehindTheScenesDBA: With all its limitations, what do you think about memory-optimized tables? Have you recommended to a client to use it?

A: They don’t make any sense for the vast, vast, VAST majority of clients, and no, I’ve never recommended them. In-memory temp tables are different though, and I cover when to use those in this module of my Fundamentals of TempDB class.

Q: Sultan: Does this mean Azure storage is now as fast as a Y2K SAN?

A: Well, a couple of VM types are now competitive with my laptop, so…that’s good, I guess?

Q: allmhuran: Azure SQL databases come with lots of connectivity issues with SSMS – a tenant MFA prompt (legit), then another MFA prompt for every other tenant where I have a guest account (?), then a firewall prompt (if I can add the rule, why even ask?). Do you know if MS is working on this? 

A: Did you call them to open a support case to get them to fix it?

Q: Florence Nightingale: What is your opinion of the stat histogram step limit (199) in SQL Server? Should this be changed in future versions?

A: It’s fine for most use cases. I think in the grand scheme of things, there are other things I’d rather have Microsoft focus on fixing instead. Most people don’t even have accurate knowledge about this kind of thing, let alone know that it’s a problem. Hell, in your own question, your facts are wrong, ha ha ho ho – the limit isn’t 199 steps.

Q: Levi: Will long running index reorg / rebuild jobs for large tables prevent the transaction log from clearing? Is this a risk?

A: For rebuilds, yes and yes.

Q: Mr. Bean: Hi Brent, do multiple data files per file group help with user DB performance like how they help with TempDB performance? Running SQL 2019 on NVME SAN.

A: Only if your workloads are the same as TempDB, meaning users create and drop thousands of tables per second.

Q: JerseyDba: Migration from 2016 to 2019 OR 2022 – I am currently ready to upgrade our SQL database servers and was wondering if it was worth upgrading to 2019 or waiting for a stable version of 2022?

A: Don’t hold out hope for something that doesn’t even have a public preview yet, let alone a release date.

Q: Sev A: Hi Brent, when Compilations/Sec are high, is there good way to view the recently compiled queries in real time / near real time?

A: sp_BlitzCache @SortOrder = ‘recent compilations’

Q: Dwight: Is analysis of the auto generated stats ever recommended or useful (i.e. WA_Sys_00000003_15502E72)?

A: Yes, and I discuss it in my Fundamentals of Query Tuning class.

Q: Mr. Ed: Hi Brent, what tool / script do you recommend for formatting SQL agent job query results to HTML for subsequent emailing?

A: If you want reports, use a reporting tool. You’re trying to use a job scheduling tool for reports. Sir, this is a Wendy’s.

Q: Augustus Gloop: Will the max of 160k query plans be increased to accommodate Parameter Sensitive Plan Optimization in SQL Server 2022?

A: Anything about 2022 is subject to change until release, so I wouldn’t worry about the specifics yet.

Q: Noam: What’s the easiest way to see the number of locks acquired for a given update statement (for lock escalation concerns)?

A: sp_WhoIsActive @get_locks = 1

Q: Unsigned Biggie: Hi Brent, worried about eventually running out of int64 values for an identity column on a new table in SQL. Should I seed the identity column value with the max negative value for an int64 to prolong the life of this table?

A: Sure.

Q: Sir Logs-Alot: Hi Brent, have you ever seen sp_whoisactive intermittently crash dump on SQL 2019 CU14?

A: No.

Q: Hatsune Miku: Hi Brent, what are your recommended settings for SQL Agent job history log size and job history rows per job?

A: I don’t have any opinion on that.

Q: Marco: How often does using the KEEP FIXED plan option get you over the finish line?

A: Never. Not even once.

Q: Brandon: Has a SQL Server service pack / cumulative update ever given you buyers remorse? If so, which one?

A: Many. Search for “breaking news” on this site.

Q: JAH: Do you know if Polybase suffers from the same performance limitations as linked servers? MS docs says “the benefit of Polybase is to allow the data to stay in its original location & format so that it can be queried in place like any other table in SQL Server.” But at what cost?

A: Yes.

Q: Doug: Hi Brent, is there a good tool or way to compare the estimated number of rows (equality) for each step in a stats histogram with the actual number of rows in the real table? Might be good to know when there is large variance between the two.

A: Nothing built in, no.

Q: OhLordI’mStuckInAzureAgain: My friend has a stored procedure that takes a minute to execute. The plan has a statement with a MEMORY_ALLOCATION_EXT wait where the WaitTimeMs is 400, but the WaitCount is almost 1 million. Should he be concerned by the high WaitCount even though the WaitTime is relatively low?

A: No.

Q: Voltron: Hola Brent! I inherited a SQL Server 2016 database that has the trustworthy database property enabled. The application vendor confirms this setting is required. What can be done to provide a secure environment for this database and our other SQL Servers?

A: I don’t do security work.

Q: Talip: What is your opinion of the SQL Virtual Machines best practices assessment tool for Azure? How does it compare with sp_Blitz?

A: I have no idea.

Q: Stanley Hudson: Sometimes we see log shipping alerts notifying us that we are more than X minutes out of sync on the log shipping secondary.Hard to tell what burst of transaction log activity (other than Index maintenance) causes these warnings. Is there a good way to track down the root cause?

A: Try logging sp_WhoIsActive to table and check out the Writes column.

Q: Robert: Do you know why, in the execution plan, the number in % of actual vs expected rows is stored as an integer instead of a bigint? It went negative on me today cause of int overflow 🙁

A: “Why?” For “Why” you would need to ask Microsoft, right?

Q: eshirvana: Why optimizer is using nested loops join when I join my tables? what can I do to force optimizer to use a better join algorithm? under which circumstances should I use join hits, If I should at all.thank you

A: Check out my Mastering Query Tuning class.

Q: Ralph Hinkley: Does sp_blitz check for high VLF counts?

A: Open the code and do a control-F for VLF.

Q: Dan Heile: Why can’t sql DMVs or monitoring software like SentryOne capture performance statistics on stored procedures that open symmetric keys / certificates to decrypt encrypted columns?

A: “Why?” For “Why” you would need to ask Microsoft, right?

Q: Bill B: On an alert 14 insufficient permissions for example is there a way to get the machine name instead of just the ip address? DESCRIPTION: Login failed for user ‘userxxx’ . Reason: Could not find a login matching the name provided. [CLIENT: 123.123.123.123]

A: I have no idea.

Q: Accidental_dba: Hi Brent,Thank you so much for this great work,I have question on memory table,my normal insert for 18m record taking 40 hour with 8core and 150 gb ram,top waitype as per blitzfirst are Sos_work_dispatcher,pwait_directlogconsumer_getnext Anything i can do here ?Feel like m baddba

A: That’s beyond what I can answer quickly here, but check out the Consulting section of the site.

Q: DBA_Europe: Hi! We have a few 3 node AGs(2 ha in sync & 1 DR cross site async). Cross site network throughput is too slow for replication to DR(log send queue accumulates). Can you combine AGs and log shipping? BOL doesn’t mention this in the interoperability Section.

A: Yes.

Q: Tobias Fünke: What is your opinion of the Azure SQL Database DTU Calculator app for lift and shift capacity planning a move from onprem SQL to AzureSQL? https://dtucalculator.azurewebsites.net/

A: I’ve never used it.

Q: Mark Tomlinson: Hi Brent – can you think of a situation where a sql server spid could exist without a query plan associated/created for it to operate?

A: Yes, system processes.

Q: RetiredDBA: I used to admin a third-party db which had hundreds of tables containing millions of rows and lots and lots of indexes. The vendor used guid’s exclusively for PK’s. What would be your advice on this choice of datatype for PK’s?

A: I explain that in the clustered indexes module of my Mastering Index Tuning class.

Q: GoingWalnuts: Brent, I have an Azure database where foreign keys are just not being enforced. All enabled/trusted. Full of bad data now. You can even drop and create FKs that should never EVER work, and they do. Am I missing a setting… or some medication? SQL just whistles on by. Weird..

A: I explain that in the foreign keys module of my Mastering Index Tuning class. (I know, I feel kinda bad when I link to those, but there’s a reason those modules are 30-40 minutes long.)

Q: CKI: My developer wants to have permission to Kill his own processes . Currently the user has only read/update/delete/insert permissions on the database. How can I grant the “Kill only his processes” permission ? Thank you!

A: Put the kill in a stored proc, and sign the proc with a certificate. Here’s an example in the sp_BlitzFirst documentation.

Q: Ben Cox: Hey Brent – What are the technical reasons why CDC is not supported for CCIs?

A: I have no idea.

Q: Timbalero: Hi Brent, a 1TB clustered table has a nvarchar(max) column and a nc index built of all base columns. Including the nvarchar column into the nc index was supposed to be a way to fix blocking on that column but I wiev it as a sledgehammer approach. Is there a more elegant solution?

A: Answering that would really require seeing the tables, queries, etc.

Q: Kamy k: Can’t find the answer anywhere, Is it possible to pass environment variables into Code snippets to autofill ie current date, user, DB. Specifically for use in generating header documentation.

A: I have no idea.

Q: Jr Wannabe DBA: Hi Brent, I see often wait stats like “For 2004 seconds over the last 5 seconds” on servers with 4 to 12 CPUs with sp_BlitzFirst, mostly CXPACKET or CXCONSUMER. How to read/understand this? It looks too much for that time frame.

A: Your server has multiple CPU cores, each of which can run multiple queries simultaneously. I go into more details on that in the first module of the Mastering Server Tuning class.

Q: DannyDing: Hi Brent did you have any experience with SQL Server supporting PeopleSoft ERP?

A: Yes.

Q: StanTheMan: Hello Brent,sa account.When master DB stores usernames and pswds,how can I secure the master DB before attack?Recommendation is not to use sa as users DBs owner+disable sa and the one and only DB account per server as DB owner in case of Princpiles of least priviligies.Thats all?

A: I don’t do security training, sorry.

Q: SeeCoolGuy: can sp_blitz help out to identify those long Network I/O sessions?

A: No, for that, use sp_BlitzWho or sp_WhoIsActive.

Q: PleaseAndThankYou: Hi Brent, It says online that cluster indexes are faster that non-cluster indexes. Does changing a non-CI to a CI have performance benefits? I ask, cause they want me to do this to see if the company program wil run faster.

A: Check out my free How to Think Like the Engine course.