Office Hours: Ask Me Anything in Vegas

Post your SQL Server and Azure SQL DB questions at https://pollgab.com/room/brento and upvote the ones you’d like to hear me discuss. Here’s what we covered today:

00:00 Start
02:47 therealprodDBA: In the last webcast, I heard you cite that majority of your clients are on AWS. I got curious now that my shop is considering migrating onprem SQL to azure. Is there any known issue that the said clients were avoiding on azure SQL DB, managed instance and hyperscale?
04:08 Festus: What naming convention do you prefer to use for unique constraint indexes vs non-unique indexes?
05:21 EnglishmanDBAInIsrael: Following Garðarshólmur question on backups, is snapshot backup suitable for a highly active environment? I thought of having a AG secondary dedicated to snapshot backups, and then IO freeze does not affect users, but still gives the super fast disaster recovery.
06:03 Seeker of Parallelism: Hi Brent, I added indexed views to tune my query. Unfortunately only my dev system uses the indexed views, the prod system gets the old query plan (already tested with option recompile). What could be the reason for different execution plans on both systems?
07:21 END TRY BEGIN CRY: Hi Brent. I need to shrink a log after it grew to about 3 times the size of the DB @ 150GB (bad dev coding). The DB is in Simple recovery mode and had a backup done, and the log file says its 99% free space, but shrinkfile doesn’t make it any smaller. Can you think why? Cheers
09:24 Doug E: Should we be concerned when “Reason for early termination of statement optimization” equals “Timeout” for a complex query in a stored proc?
10:16 boutaga: Hi Brent ! Do all application using queries with parameter have parameter sniffing in SQL Server ? In my experience it is the case, but if not, how can I code an application in that manner ?
12:42 Peter: How do you go about learning new technologies? You help a lot of us in your articles and teaching, but who teaches the teacher – and what methods work best for you?
17:20 fajitapete: So I got a customer who handles the database server. The server is serving my application. 5 filegroups (3 for data, 2 for indexing) with 17-23 files per filegroup. Files are all different sizes, am I wrong in trying to show there is a performance issue with this many files?
19:23 Todd C: Hi Brent: Do you ever do escape rooms with friends or family? They are popping up all over the place now. Even cruise ships have them (Royal Caribbean at least). I bet you would be pretty good, given your problem solving skills and analytical mind.
20:51 Yuvati: What is your opinion of SQL Trace Flag 4199 (Query Optimizer fixes released in SQL Server Cumulative Updates and Service Packs) and when is it appropriate to use?
23:15 Dave: Should indirect checkpoints be used with the master database?
24:14 Eduardo: Is Azure ultra disk storage performance specs closer to onprem SAN specs or usb thumb flash drive?
25:57 Zoom Towny: Do you have a quick/easy way to determine if a given query historically used the stats density vector vs histogram when estimating cardinality for a given index?
27:44 SleepyDBA: Hi, I have trouble with a vendor application that includes a table with three columns and just one row. Application actively updates that row. So, table has large number of ghost rows, querying it results in 32k logical reads. is the existence of ghost rows connected to RCSI?
28:43 Eduardo: For running SQL Server VM in the cloud, does one cloud vendor provide better storage price to performance ratios than the others?
30:37 Ezra: Should SQL DBA’s know docker containers?