Office Hours: Ask Me Anything About the Microsoft Data Platform

Показать описание

00:00 Start
00:37 AllThePartyPeople: On our server, MAXDOP=1 (not my choice); we had a poor performing SP call (called as dynamic SQL; again not my choice); Ran in SSMS; ran fine. Cleared plans & tried; Failed web; SSMS worked.
Set MAXDOP=4, and both work fine.
What are we missing? Why did it always work in SSMS
02:09 FrankieG: Hi Brent, How do I sell the sp_Blitz scripts and, for that matter other advice you and other similar experts, have imparted that I have come to rely on, to the "owners" of the MSSQL client databases at a company where I'm the new guy?
03:05 APB: Hi Brent, Just curious about your thoughts on creating a view in a data warehouse with an index (schemabinding + unique clustered index) versus creating a similar table and truncating and reloading after a nightly data refresh. Which is most efficient?
05:01 Medazzaland : What criteria do you use to decide if a given long running transaction is a concern or not?
06:14 Medazzaland: What are the best training resources for learning SQL Availability Groups?
06:54 PB&J: Hi brant, I’m a big fan of transactions and error handling in stored procedures. Developers say they implement try/catch and transactions in the application code so they don’t want to add it on the SP as well. Is it indeed duplicate or handling errors in SQL has its benefits? Tnx
08:00 Ramil: Hi Brent! App controls a lot of things like amount of database files - only one, indexes can be created only via app, partitioning is not allowed. How can I scale up horizontally in this case? because DB is growing 300gb per month. OLTP DB, currently 1.8 TB, in AlwaysOn AG.
10:05 bamdba: Hi Brent, I've inherited a SQL estate where I have multiple SQL HADR (2012 to 2017) on a single OS and with data files on mount points. Any suggestions on how can I consolidate these servers without migrating to new ones?
11:43 Mehmet: Do untrusted foreign keys adversely affect query plans?
12:03 Mehmet: Have you ever experienced an ailing SQL Server that sent out so many failure simultaneous notifications that it took down the mail server?
12:49 Timbalero: Hi Brent, apart from inaccurate stats, UDFs and table variables, what other reasons can make the CE produce estimates of exactly 1 row? Can multiple joins have something to do with it?
14:56 Ingibjorg: Do you have any recommended guidance for creating / optimizing new VM configuration for SQL Server 2019 Standard in an Azure VM? Is it any different than bare metal new server configuration?