Office Hours Speed Round, Easy Text Answers Edition

Some of the questions y’all post at https://pollgab.com/room/brento have easy one-line answers. Let’s knock ’em out:

George: Hi Brent, what recently has been the most challenging/surprising/new-to-you performance issue you have encountered?

SQL Server 2019’s slowdowns. I spent days working on that.

RoJo: Have you used Distributed AG as a way to upgrade major versions of SQL server without downtime? Seems like a nice way to try it out on a second site before a switch. Any concerns? Maybe jump from 2016 to 2019, or 2022 to big a jump? Cheers

No because it’s so much work to set up.

ConsultantWannabe: I’m a generalist trying to make the jump into the consultant role, I don’t want to be “a jack of all trades”. How should I start finding that niche (or that “expensive” thing to stand next to, apart from SS)? Do you think just asking around to the guys in suits is a good idea?

Ask executives what technology problem they can’t solve with their current staff.

Ive_Got_Heaps: Hey Brent, Our DB is loaded with heaps as our ERP system doesn’t utilize primary keys (begins crying). My plan is to create clustered indexes on existing columns where possible, or create an Id column for tables where no existing column can be used. Is this a sound approach?

Ask the ERP vendor. If it’s an in-house app, watch this.

Yevgeny: What are the top causes of data file corruption for SQL Server on a Windows cloud VM and how do you avoid them?

In the cloud, you don’t get root cause analysis from your vendor. Do backups and high availability (like AGs for automatic page repair.)

SQLrage: In 2019, can statistics updates on a table cause an execution plan to be recreated for a parameterized proc that hits the table but does not use that updated statistic in particular? Trying to better understand why plans regenerate automatically.

Read this and do a test.

Bart: Is there any harm in deleting Extended Properties of a table column? I inherited a database that’s been converted from MS Access into MS SQL several years ago and I think the extended properties are a result of that conversion.

What’s the benefit in deleting them? Why risk it? Who cares?

Piotr: What file system folder convention do you like to use when locating data files / log files for a new SQL DB?

\MSSQL\DATA

Chetan: Which nice car did you buy recently? What do you drive now?

A 1964 Porsche 356 coupe, which is getting its engine checked out first before I take it on any road trips. Until it’s done, and because it’s springtime, I mostly drive my Speedster replica.

MacAries: I had an On Prem 3 CTE then join for result query that ran subsecond, but coming from Azure Function that sent multiple and crippled the on-prem server to a 20 result /minute nevermind the lock and batch waits is their some basic translation that azure needs to get the query run?

Read this or watch this.

Will Marshall: Do you run into any common performance issues with SQL always encrypted?

I’ve never had a client use it.

Haydar: What is the best way to copy a few tables from SQL Server to PostgreSQL?

I would ask this guy.

CB: Hi Brent – It seems SQL functionality isn’t supported in SQL task editor. Statement: Declare @sql nVARCHAR(max) Error: The Declare SQL construct or statement is not supported. Is there a solution to that?

I don’t know what the “task editor” is.

Will Marshall: What are the best courses / books for learning SQL Always ON?

I haven’t seen any that were updated with what’s new in SQL Server 2019, 2022, or Managed Instances Link.

Bocephus: For network perf testing between two windows nodes, what tools do you like to use?

Copy a large file like a backup.

Hal Jordan: What should we look at when OS pages per second paging rate is high for the bare metal SQL Server 2019 instance?

Attend this class.

Mirza: Discussion happening in the company about automating SQL patching on clusters using Powershell and SCCM. Both PS script and SCCM are owned by the server team. Does the DBA team lose control and does it matter? What is your experience/opinion regarding automating SQL patching?

Read this.

Kyle: Hi Brent! What are the best practices for restarting your SQL service on an HA system? Is there any way to do it with causing any downtime?

No, all restarts will cause downtime, even for cluster and AG failovers, so for minimal downtime, use clusters and AGs.

SQL_Developer_Admin: If there is left outer join, then why there is right outer join as well, if we could just swap the sides of the tables. Any scenario you know where only left join can be used or right join can be used.

Sometimes it’s nice to have multiple tools to approach the same problem from different angles.

Eyvindur: You mentioned caching as a possible solution to lessen the load on SQL Server. Are triggers are good solution for cache invalidation?

The idea of caching is to lessen load. Do triggers add or lessen load on the database?

Eduardo: Do you see any RDBMS disruptors threatening to steal Microsoft / Oracle market share in the near future?

If by “near” future you mean 5-10 years, yes, Postgres and AWS Aurora.

ChopstickWizard: Been mulling this over for sometime. There are instances where I want to recommend a product to a team, like example : Cockroach db, mage.ai etc. But the problem is, they seem “non enterprisey” just by their name in case of “cockroach” or mage.ai’s tag line. Have you faced this?

You mean names like ChopstickWizard?