While waiting for the dry cleaner to open, I went live to stream a quiet session going through a bunch of y’all’s questions from https://pollgab.com/room/brento.
Here’s what we covered:
- 00:00 Start
- 01:45 Rollback is single threaded: Hi Brent! In microservices application architecture, Using a database per service or a shared database? I asked this because using a database per service is not straightforward and there are many drawbacks. Thanks!
- 03:39 fajitapete: Covering indexes, what benefit is derived once you are past 2-3 columns, wouldn’t putting the rest as included be just as good
- 05:07 ILoveData: SQL 2022 makes it easier to call external endpoints. In your eyes as a DBA, what acceptable use cases (if any) are there for a feature like this? I appreciate the idea, but we all know how this ends up once devs find out it exists…
- 07:02 Dopinder: What is your opinion of SSMS 19? Anything to get excited about?
- 07:52 Sajan: Do you have any interesting arguments to explain why you shouldn’t use Managament Studio on an instance with a SQL server? My argument was mainly that the price of the license is conditioned by the amount of CPU (which is wasted by unnecessary applications)
- 08:47 Mike: My on-prem server has 16 physical cores / 32 logical cores. If we migrate this box to SQL Server on Azure VM, how many vCPUs we need – 16, or 32 ?
- 09:54 Mike: When you deploy Azure VM with preinstalled SQL Server 2019 EE, it only shows projected VM cost. What will be the additional (hidden) SQL license cost $ per core / month? Is it $274 as in new billing model in SQL 2022 (selected during SQL install), or different amount ?
- 11:04 DevInHiding: Hi Brent, an older colleague of mine claims that (at least in much older versions of SQL Server) that it is better to query bit data type fields as “WHERE fieldname anything other than 0” than “WHERE fieldname = 1”. Is there anything to that?
- 12:06 Curious DBA: In what scenarios would you utilize CROSS APPLY instead of INNER JOIN? Is CROSS APPLY an optimal way to get SQL Server to do several backward seeks (1 per row) instead of a large forward seek? (I.E. composite PK (ID, DateTime) and want to return max(DateTime) for multiple IDs)
- 13:14 zlobnyfar: WhatIsTheBestWayOfLogsGeneratingAbout CRUD interactions (changes in roles or permission) AND AUTHENTICATIONS (login attempts (success/failures) and attempts to elevate privileges (success/failures)) AND executed QUERIES stats AND SA Actions Thanks for comprehensive answer!
- 15:32 Dont Bother Answering: Hey Brent, why does my query go parallel when it’s cost in sp_BlitzCache (33.7) is lower than cost threshold for parallelism (35)? Just looking to understand why, thank you!
- 16:40 Trushit: What do you think will be the impact of tools like ChatGPT on SQL developers? Which role do you think will be most impacted : developer, development DBA or production DBA? What skills will remain relevant even when AI learns low level coding?
- 18:09 BlackFriday-Bundle2: Hi Brent and thanks for the courses. What is your view on PAGE vs ROW compression in SQL Server? Would you consider it a bad fit for multi part NC where latter key parts and included columns are “hot”? Finally, does it ever make sense to change FILLFACTOR if compression is on?
- 18:57 Fillfactor 1% for the win: Hi Brent, what are your thougths about creating 3 docker container on a physical box, one for dev, one for test and one for live. It’s for a (modern and classic) DWH environment. Live is using all resources during night, and during the day the developers can work on dev/test.
- 20:22 YouGottaDoWhatYouGottaDo: Hi Brent, what’s your opinion on the new T-SQL snapshot backup functionality in SQL Server 2022? Do you see any hidden problem to be aware of?
- 21:18 Brandon: Any insights to offer regarding how db design might differ (or if it should) when developing for microservices, and any resources or people to follow for further study? For example, do you find it common to have many DBs where traditionally there would only be one or two?
- 22:48 RufusStone: Asking for a friend, what is a suitable punishment for someone who creates a database with space in its name?
- 23:44 i_use_uppercase_for_SELECT: How do you manage expectations at your clients that not all index changes won’t have unexpected consequences? Create and index that helps several queries, but causes another to blow up because of a new query plan.
- 25:26 Sajawal: Hi Brent, You are doing great for people like me who love to play with SQL. Would you please let us guide what is DOP feedback architecture in SQL Server 2022?
I like the question and response about the on-prem cores vs physical cores a lot.
Figuring out what is equivalent processing power for a VM in AWS or Azure is DIFFICULT. Especially with azure where from one version of the same product line to another, there can be major differences in performance characteristics. You think you get close to finding something and then spend a week going around in circles researching the performance characteristics of each instance type.
For me, more difficult than figuring out equivalent CPU power, was finding the instance that had enough disk i/o with the correct amount of memory and CPU. Almost inevitably, you end up looking into compromises in smaller SQL servers then look at instances with more memory and more scratch space for bpe or tempdb, to operate a little bit under on storage i/o or CPU but it mostly doesn’t work and you end up needing to spend more money than was planned for.
Biggest takeaway is that especially in some of the more modern VM instances offered (especially in azure) your code just has to be better optimized than it was running on-prem. Its not as bad a database as a service offerings where it generally has to be almost perfect, but I would not call it flexible in accommodating inefficient code at all unless you really spend a lot on the instance.
(referring to azure being mor difficult, AWS seems to have more options with better delineation of performance characteristics that are much more linear than Azure.)