Office Hours: Ask Me Anything

00:00 Start
00:22 CKI: Could please give me pointers how to answer the question
"Do we need to create a new instance for the new application or can use the existing one?"
Thank you very much
01:18 Chris May: Within an index, if you can guarantee unique selectivity with 2 key columns, is there a fundamental difference between adding an extra key column or an included column? Which would you typically prefer?
03:15 Pyjamarama: Hello Brent.
In one of our customers our User databases (Greek_CI_AI) are in different Collation than TempDB (Latin1_General_CI_AS). What is the best way to fix that? (We prefer to keep the CI_AI). Thanx!
04:41 Maksim Bondarenko: What would you do first if two sql servers with the same version and CU generate almoust identical execution plan with one exception? Second server generates one additional block (in my case it is Index Spool after Cl scan) and that's why 10 times work faster. Thx
05:47 Piotr Rasputin: What's your opinion of azure vm host caching for SQL vm's?
07:30 Peter: What does your op love ones at home say when you talk like Clippy all the time? :-)
Thanks for all the knowledge you share with us all
08:00 Gerardo: What is your top story for when you were burned by missing / incorrect SQL Server documentation?
10:30 Too Much Spare Time: Last time there was a question about how to deliver bad news as a consultant. What type of bad news do you find delivering the most?
11:49 chris: With a SQL Server built on a virtual machine on VMWare is there a way to determine CPU and/or memory pressure on the host without having access to VCentre?
12:38 LockedUp: I have a proc that is causing deadlocks. I have tracked it down to an Update statement with a ROWLOCK hint. Could the ROWLOCK hint be causing the deadlock? Update statement is well written and indexed, not sure why ROWLOCK is even needed.
14:24 Leonard: What is your opinion of the SQL Server data collector? Any good for performance troubleshooting?
15:12 Maciej: Hi Brent, do you have a missing feature in SSMS and/or SQL Server that you treat (at this point) as an old friend? ;-)
In addition if you could "influence" Microsoft to implement one which one would it be?
17:11 Leif Hole: Hi Brent ! Really enjoy your "think like the engine" and the fundamentals !
My friend have this theory that Mr. Codd meant that all joins between tables is the best way of getting data. Never denormalize !
Is there any thumb of rules when it comes to denormalizing ?
18:09 chris: I understood Azure SQL DB was always current; however, when I view the compatibility level of some of my databases they're not current. Is it up to me to change this? Any gotchas with doing so?

[andreysamykin]

03:58 they can’t change the collation of tempdb without changing collation of model database. In fact, they have to change collation of model db and restart the instance.

[jeffmoden]

On the question of "burned by missing/incorrect SQL server documentation", my take comes from a page that existed for decades that no longer exists but people still insist to use the principle of supposed "Best Practices" for index maintenance. On that page, it use to say "use the following table to determine the BEST METHOD to remove the fragmentation" (all caps is my emphasis). That page contain the old "recommendation" of REORGANIZE between 5 and 30% fragmentation (it was 10 and 30 a long time ago) and REBUILD above 30%.

Then, in the first footnote, it says... "These values provide a ROUGH GUIDELINE..." . Apparently, no one ever read or followed that footnote because 98% of the world started treating those values as a "Best Practice" more than 2 decades ago and most people still treat them as "Best Practices". A younger me was one of the victims of that poorly laid out page and the "expert opinions" on the internet, etc, etc, and I was really burned by nearly impenetrable blocking on Monday, 18 Jan 2016, which was the proverbial "morning after" index maintenance occurred (and the blocking had been building up for the previous 12 weeks because of some threshold reached).

Having seen on of Brent's flicks and writeups on the subject, my fix was to stop doing all index maintenance for nearly 4 years (I don't recommend that but it was a damned fine experiment) and only did Statistics Updates on the weekends.

Much longer story made shorter, I've found that it's better to do no Index Maintenance than it is to do it wrong and, if you're using REORGANIZE, you're probably doing it wrong for 90% (or so) of your indexes. The documentation about what REORGANIZE does is actually correct but most people have only taken the part about "according to the Fill Factor" totally incorrectly.

There are other major gotchas (and I'm "only" talking about ROWSTORE indexes) but, 98% (my personal estimate) of the world is still doing the old "Best Practice" thing using the 5/30 recommendation and you're actually not only causing massive blocking on the morning after, you're actually perpetuating the problem.

Do it right or stop doing it and, if you're using the supposed "Best Practice" 5/30 method or using REORGANIZE to any great extent (pun not intended), you're doing it wrong. The supposed "Best Practice" method is even responsible for the bad rep that Random GUIDs have for fragmentation.

Here's an "intro" to how the "Best Practice" Index Maintenance is helping you to destroy performance. Make sure you watch the quick part after the "QnA". And, no... it's not just about Random GUIDs. I start with those because they're the "Poster Child" for fragmentation... and I demo how to fix those and it's NOT by using "NewSequentialID".

[nedgrady]

Sure hate it when I run into query problems in the morning

[petermoniquenauta-vankeste]

I like where you say you don't do clippy imitations at your home office and then start doing imitations of Microsoft guys 🙂

[euromicelli]

8:00 yep, that made the Top 50 Question list in StackExchange for a while. Congratulations! _Now you’re famous…_