Office Hours in My Backyard: SQL Server Q&A

Рет қаралды 3,403

Күн бұрын

I sat down on the patio to take your top-voted questions from pollgab.com/room/brento. Here's what we covered:
00:00 Start
00:56 MyTeaGotCold: When going in to a database blind, do you worry at all about its compatibility level? I see a lot of unloved databases that are still on 2008's level, but the tiny potential for breaking changes makes me scared to touch it.
02:01 Vishnu: For boxed SQL, Is it ok for users to create SQL agent jobs that run periodic business logic and/or email end users?
02:59 Karthik: What's your opinion of live query plan viewing in SQL Sentry Plan Explorer?
03:26 Jessica : I am DBA who previously managed dozens of Azure VMs running SQL Server AGs. After a layoff and new job I'm now managing 1 prod Azure SQL db. Any tips for someone making a transition like this? Every day I'm finding features that I no longer have access to and minimal monitoring.
04:29 Hong Kong Phoey: Currently, when we see a non-clustered index on a given multi-tenant SQL table, we may or may not know why it's there, who created it, when it was created or which app needs it. What's the recommended change control process for answering these questions?
05:35 Kevin M: What's the best place to go for commercial PostgreSQL training?
06:02 NotCloseEnoughToRetirementToStopLearning : Hi Brent Inheriting a VLDB (50Tb) any recommended articles or trainings for working with something this size?
07:05 Mike: Hi Brent! When migrating from SQL Server 2017 to SQL Managed Instance, on MI databases after restore become FORCE_LAST_GOOD_PLAN = ON (On 2017, it was OFF). Do you recommend leaving it ON, or should we turn it OFF on initial stages ?
08:27 Venkat: What's the most common detrimental complacency you see with SQL dbas?
09:13 Mattia: Can a big analytical query that does many logical reads (compared to the SQL Server RAM) give SQL Server Plan cache amnesia? Or is the Buffer Pool completely separated from the Plan Cache?
09:48 Nickelton: Is it possible to forward select queries to AG secondary replica without changing application side? (connection string etc.) For example running code from SSMS or for legacy applications.
11:22 WB_DBA: My friend suggests creating all indexes on a test database since it mirrors the production database. Is this a good approach?

Пікірлер: 12

@allenshepard7992 Ай бұрын

Thanks for the "One on one" after the questions. MyTeaGotCold asks some good questions.

@andreyh2944 Ай бұрын

Regarding question about Index and "why was it created?". I'm not sure if it's applicable to dbs, but in app development all the code is tracked in VCS. For instance, with EF Code First, you would not have a Jira ticket in the index name, you would have your db versioned in VCS as well. Then, if you have a question "why was IX_Name_Name_Name created", you search for IX_Name_Name_Name in your VCS and it brings up the commit which introduced it in the first place. Each commit should have a reference to a Jira ticket in its message and/or come from a branch named after the corresponding Jira ticket. Apart from that, wonderful QA, thanks Brent for those.

@BrentOzarUnlimited Ай бұрын

No, not applicable to dbs, sadly.

@christophero3869 Ай бұрын

Depends on what kind of reality you’re living. If you’re in a situation where all index tuning is done by developers, that’s a great solution. If your index tuning is at least sometimes done by DBAs that aren’t on board with your DCM (database change management) solution, then it’s unreliable at best. However, if your index tuning is done by DBAs who ARE on board with your DCM solution, then you’re back to happyville.

@BrentOzarUnlimited Ай бұрын

@@christophero3869 Nope. Listen to the question again: there are too many people involved in adding indexes there already.

@TheRealSmendle Ай бұрын

@9:48 ApplicationIntent=ReadOnly as a client connection property is the closest you will get to "forwarding" reads to the secondary replica. Caution tho that will require Enterprise SQL and configuring read only routing on a AG listener.