Office Hours: Read This, Watch This Edition

This week, a lot of the questions you posted at https://pollgab.com/room/brento just need pointers to resources at other places. Welcome to the Read This, Watch This series of answers.

Don’t Blame Anthony: Our developers prefer writing TSQL queries directly in their .NET code rather than writing sprocs and calling them. What are the pros / cons to this approach?

Read this.

Divakar: Is there any formula based on number of processor cores that I can set for Maxdop for a sql server

Read this.

Mr. No: What is your favorite NOSQL database?

Read this.

Dan Griswold: How would you manage access to SQL Server Agent jobs. Our development team shares jobs and they all want to have access to run and modify them. Of course, sysadmin is the only way to grant that privilege. So, we created a sql account and gave them all the pswd. Any better ideas?

Read this.

Jr Wannabe DBA: Hi Brent, once in a lifetime I saw a new index blocking insert operations to a table. What are the reasons it can happen? The support team removed the index before I was able to take a look at it, but it looks like the index created some restriction on what can be inserted. Thx.

Watch this video and this video.

John Martin: A query is imbedded in a compiled program. Is there a way to force it to use a specific index? I can’t change the query. It often picks the wrong index even if we’ve updated statistics and rebuilt the index

Read this.

Magnús: Do you have any scenarios where you like to use the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL query hint?

Watch this video. 

Barney Fife: Worried about unmanaged / unlicensed SQL servers running under someone’s desk. What are some good ways to automate discovery of all SQL servers on the company network?

Try the Microsoft Assessment and Planning Toolkit.

ScenarioFromRealWorld: You log on to a SQL Server to help your client. You notice that almost every process/connection in the SQL Server is using the sa account. Both locally and also from clients through the network. Would you say anything regarding this, and if so, what would you say to your client?

Read this and this.

Leif Hole: Hi Brent ! Is there a noticeable performance difference between cluster index on one vs two integer columns ?

Watch this video.

Dominique B: What’s the white (moto) helmet’s story ?

Read this.

MyFriend: Hi Brent. sp_BlitzIndex (latest) is reporting “Index Hoarder: Unused NC Index with High Writes” for some indexes that have 0 Reads in Usage stats. But have numbers in “singleton lookups” and/or “scans/seeks” in Op Stats. Why is that & would you still consider them safe to drop?

Watch this.

Ramil: Hi Brent! Is there a tool to replicate production load on other environment? For example, we make backup or snapshot of a database, then we start to record all transactions with sessions. Then we replicate this load on other environment as it was on prod.

Read this.

MyFriend: Hi Brent Have you ever “fixed” Parameter sniffing problems, because of spills to disk, by turning Adaptive Memory grants off in SqlServer2019? And if so, what would be the main drawback in doing so?

Watch this.

CliveP: Hi Brent. Can you let me know how you would handle an Aggressive under index warning on a table where there are no missing recommendations and I don’t have access to the application code?

Watch this.

I chuckle a lot when I see data professionals banging their heads against a wall on a hard problem, Googling their brains out, frustrated because they’re not finding any easy answers.

You’ve been consuming my free material for years. You’ve worked hard to polish your knives, to continuously get better.

But for some reason, you think you’re too smart for training classes.

Does it ever occur to you that the reason why you’re not finding any easy answers is because there aren’t any? You’re a senior data professional. You’ve run out of easy buttons. It’s time for you to upgrade your skills so you don’t have to keep beating your head against that wall.

Or, Buy It for Life.

Pay just once, and get access to my recorded training classes for the rest of your life. (Note that these don’t include the SQL ConstantCare® or the Consultant Toolkit apps.)

The Fine Print

To get these deals, you have to check out online through our e-commerce site. We only take payment via credit cards – no Subway coupons or IOUs.

Can we pay via check, purchase order, or wire? Yes, but only for 10 or more seats for the same package, and payment must be received before the sale ends. Email us at Help@BrentOzar.com with the package you want to buy and the number of seats, and we can generate a quote to include with your check. Make your check payable to Brent Ozar Unlimited and mail it to 9450 SW Gemini Drive, ECM #45779, Beaverton, OR 97008. Your payment must be received before we activate your training, and must be received before the sale ends. Payments received after the sale ends will not be honored. We do not accept POs as payment unless they are also accompanied with a check. For a W9 form: http://downloads.brentozar.com/w9.pdf

Can we get discounts for group buys? Not during the Black Friday sale: these prices are as low as I go all year.

Can we send you a form to fill out? No, to keep costs low during the Black Friday sales, we don’t do any manual paperwork. To get these awesome prices, you’ll need to check out through the site and use the automatically generated PDF invoice/receipt that gets sent to you via email about 15-30 minutes after your purchase finishes. If you absolutely need us to fill out paperwork or generate a quote, we’d be happy to do it at our regular (non-sale) prices – email us at Help@BrentOzar.com.