Office Hours: Ask Me Anything About Azure SQL DB and SQL Server

We had an abrupt ending when my camera overheats! I went through your top-voted questions from pollgab.com/room/brento and here's what we covered: 00:00 Start 00:43 cha2rg: We have an OLTP table size of 4TB and rows of 6 billion. Scheduled an old data deletion job for this table and it deleted 5 million rows (Duration 1h, 500 records batch) per day. During this data deletion period does it need to re-build the index for better performance? 02:10 SSASsy_DBA: We are using SSAS in multi-dimensional mode. Our DW=10 TB and SSAS Cube=900GB. Looks like this SSAS technology is being phased out by Microsoft and we are struggling to find expertise on this. What would you consider as good technology for pre-crunching OLAP style data like SSAS. 04:13 RollbackIsSingleThreaded: Hi Brent, What is your opinion about Google delisting due to AI-generated content ? 06:30 Punjab: What's your opinion of the new JSON data type in Azure SQL DB? Will this be popular enough to make it to boxed SQL Server? 07:03 Sean: Two databases (joined queries across) one Latin1_General_100_CS_AS the other SQL_Latin1_General_CP1_CI_AS: How will performance in tempdb be affected if I match the two so they both use Latin1_General_100_CS_AS even though tempdb will still be in SQL_Latin1_General_CP1_CI_AS? 08:12 Qimir: What's your opinion of the new Azure Database watcher for Azure SQL DB? 10:31 MyTeaGotCold: Do you find many shops that should have went to fourth normal form, but stopped at Boyce–Codd normal form? 13:02 RoJo: While you did answer this previously, can you elaborate on the dangers of removing 'dead code' or stProc that have been inherited. There seems to be an inherent desire to clean out regardless of cost. 15:10 Christos Mavroidakos: Hi Brent, We have an issue with Auditing in Azure SQL database. We need to eliminate to audit certain tables and avoid all junk of audits that are produced. This skyrocks the cost and the actual auditing is not efficient. Is there a way? 17:27 Aaaaall Night Lo(n)g: TopicCategories is a many to many table. Every topic has a primary category. Would you put PrimaryCategoryId on the Topics table or IsPrimary on the TopicCategories table? 5 of one or half of ten of the other? Or is there a clear winner for most cases? 18:29 TeaEarlGreyHot: When performing test restores, is it necessary to test the log backups, or only fulls and differentials? 19:33 YouTubeFreeLoader: I occasionally see some statistics for a primary key column that have an equal rows value of over 800 but all of the rest of them are 1 like I'd expect. When I run a select for that value the estimate is still 1 in the plan. Any idea what causes this? Is it a problem? 20:36 Markive: We have a SAAS application, each customer has 1 SQL server database per 'project'. Only our application makes queries to the SQL instance. Does it make sense to isolate each customer with a separate user account, or are the overheads not worth it in your opinion? 21:53 Mustafa: What is your opinion of Sios Data Keeper for SQL Server? 22:58 Gerardo: Have you always been a segmented sleeper or did it start at a certain age? Many Spaniards are segmented sleepers. 24:44 hoping-to-retire-soon-dba: Hi Brent - I was just reviewing issues with index maintenance and see where allow_row_locks and allow_page_locks should be set to "ON" for standard edition SQL Server 2016+. What's the skinny on these settings? Are there any downs to setting them on? 27:13 DBA G: Hi Brent. When a recurring agent job overruns past the time of the next scheduled run (e.g. runs every 15 mins but took 16) it skips a run and waits until the next time it should run. Is there any way of detecting / alerting on skipped runs? I cannot see anything obvious. Thanks! 29:10 iloatheesributlovesql: Have you ever worked with ESRI Geodatabases on SQL Server or PostgreSQL, any advice at all? 29:32 i_use_uppercase_for_SELECT: Is there a situation where you've ever suggested taking logic out of SQL and putting it in the application for performance reasons? What about the logic made you push for that? 31:30 Sigurður: When can we look forward to the first Ozar PostgreSQL recorded training classes? 31:46 Ben: Version 1: Evaluate a CTE, insert into a table, and then select top 101 from table Version 2: Select top 101 from CTE and then insert into a table. Why is version 1 generally better? 34:25 WouldLikeToKnow: I have custom SQL code in a SP that I would like to protect from being viewed by people. Creating a SP “with encryption” offers some protection but there are still ways to view it. Would storing the custom SP code in a file or another way you suggest offer more protection? 35:41 JordanT: My friend has several objects (Quotes/Orders, ...) with different active statuses and 1 or 2 non-active statuses. When active records represent less than 5% of all records, what are the performance trade-offs using a standard index, filtered index and indexed view?

• #science_technology
• #sqlserver
• #brentozarunlimited
• #brent_ozar
• #mssql