Office Hours: One-Word Answers Edition

Some of the questions y’all post at https://pollgab.com/room/brento are pretty straightforward. Let’s get ‘er done.

LetTheDbaHandleIt: My friend needs to track who has accessed what data. This is easy at OS and app level, but how can my friend verify that those who have direct DB access are not snooping on data inappropriately? e.g. payroll or HR DB. Yes, trust them, but how to verify (efficiently)? SS Profiler?
Erik: Do you know how to see last execution of a SQL view in SQL server?

Auditing.

PaginationWoo!: Hi Brent. In all of your training and demos you’re very AR (lol) about always prefixing your tables with the schema name (.dbo) which, let’s face it, is the correct way to roll, but was there an experience you had that enforced this decision?

No.

Cats_Everywhere: Do you think Microsoft will stop supporting on-prem Windows Failover Clustering to push customers to the cloud in the next 5 years? WFC appears to no longer be covered in their training on learn.microsoft.com — that’s all just azure focused now.

No.

SeekingUnderstanding: You frequently make it clear that you don’t care about external fragmenation. Do you care about internal fragmenation (page density)?

Yes.

Dopinder: What are your thoughts about new support for the MERGE statement in PostgreSQL 15? Better than Microsoft implementation?

Dunno.

Gershom: Does use of NOLOCK affect the query plan?

Rarely.

ZappyDBCC: Hi Brent, what would your advice be to dev teams to allow DBA to easily associate SQL requests to applications ? “Application Name” in cnx string ? comments in SQL requests ? other ? Thanks

Logins.

Peter Riis: How does a Nonclustered index behave on a Heap?

Watch.

Wes: Hello Brent, what do you think is the best way to have multiple sequences in the same table (one for each company id). I currently use a trigger with an UPDLOCK when selecting the highest previous value to avoid duplicates.

Dunno.

Efraim: Does having access to PostgreSQL source code make it easier to troubleshoot hard performance issues as compared with SQL Server?

Yes.

EngineHorror: Hi Brent! How often do you see PolyBase used in production? What are the top used data sources and their sizes?

Never.

Srinivas: Is it ever ok to consolidate two NC indexes with some overlapping include fields if it puts you over the 5 field max suggestion while reducing the number of NC indexes for a table?

Yes.

Pyjamarama: Hi Brent! How can I locate unused and missing indices using sp_BlitzIndex? Thaaaanx

Watch.

Gershom: Are there any RDBM’s that do a good job of automating NC index management (creation, monitoring, altering)?

Dunno.

Gershom: Do you have a recommended way of testing the selectivity of multi col Index A vs multi col Index B when consolidating similar NC indexes?

No.

Sister Hazel: Will you be releasing any new course content featuring SQL Server 2022 functionality?

Yes.

John-DK: Do you know how SQL server select which rows when I use sample in runstat?

No.

Rush: Hey Brent, I’ve got a set of tables that have a non-clustered PK (long story). I saw in your index tuning class that you don’t need to have the ID in the index normally. Is that also the case with a non-clustered PK? Thanks!

No.

CraigUK: I assume that paramaterised Dynamic SQL is prone to param sniffing? Any benefit to concatentaing the variable to build SQL that contains a hardcoded string literal and have a plan per value? Assuming that Forced Paramaterisation is not on. Or would that lead to too many plans?

Read.

Better than Ezra: Any new SQL 2022 books you look forward to reading?

Yes.

DBAbyDefault: Big fan, thanks for answering these questions Brent! My friend is wondering how much of a difference going from a default setting of 512 byte sectors on data/log/tempdb drives to the recommended 64k sectors? Is it work the effort to shuffle data around and redo everything?

No.

Ezrah: Should DBAs embrace the awesomeness of Ottertune or fear being made obsolete by it?

Yes.

Anatoli: Do you have any recommended books on the origins / history / evolution of relational databases?

No.

Efraim: SQL Sever 2022 not announced at Microsoft Ignite, should we be concerned?

Why?

I’m a potato: Any word of advice against indexing views?
Haydar: When addressing non-sargable queries, what are the pros / cons of materialized – indexed views?

Watch.

Yitzchak: Before accepting a new VM from sysadmin for a new SQL installation, what Network and Disk checks should SQL DBA’s perform?

Read.

Mikail_Tal: Brent! Have you or someone you know ever switched jobs to work with a particular technology?

Yes.

Blurred_Lines_on_Docs: On the official Microsoft documentation I found a query for Azure SQL Database that allows to pull data from sys.resource_stats and find AVG and MAX CPU, IO, Log Write, Sessions and Workers… but what are Sessions and Workers?

Really?

FrankieG: After 20+ years in the MSSQL space the more I feel like there is no clear answer to the majority of the problems I’m tasked to resolve. One could argue that this is due to the advanced features etc but frankly I feel that it’s just making it less attractive as a product.

Okay.