Office Hours, Text Edition: No-Brainer Questions

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.