Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. This session was shot in Siglufjörður, a beautiful little oceanfront fishing village in the north.
Yes, I’m on the airport runway. No, seriously. Here’s what it looks like from the air in the winter.
Questions covered in the video:
- 00:00 Introductions
- 00:45 What are the best practices for linked server security?
- 04:20 Have you ever told a customer to change their collation?
- 06:28 What compatibility level do you recommend?
- 09:06 Should parallelism be 70% of my waits?
- 11:36 Should I append GUIDs to my temp table names?
- 13:30 Why is there a car hood in the video?
- 14:25 Why are my TempDB write stalls slower than my user databases?
- 18:34 Should I send an email for each failed login?
- 19:43 Has your opinion of table-valued user-defined functions changed?
- 20:36 How do I measure the overhead of Transparent Database Encryption?
- 21:46 Can highly fragmented indexes cause PAGEIOLATCH waits?
- 22:30 How do I track down which query is using a specific index?
- 24:45 Wrap-up
If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.
8 Comments. Leave new
Off topic – New StackOverflow survey:
https://insights.stackoverflow.com/survey/2021
Thanks so much for answering my question on TempDb write stalls! Your response on this and the unhelpfulness of percentages without time stats to go along with, were particularly helpful. Love the scenery . . . thanks for doing these Office Hours videos! They’re a delight.
My pleasure, ma’am!
Hello Brent.
How can I get a jacket like yours?
Regards, Leon
Glad you like it! It’s a Marmot Goretex rain jacket.
I am so used to watching your recorded training videos, I looked for the “Mark as complete” button when I finished watching this 😀
HA!
Thanks a lot for the great stuff you are giving us for free here. As always, it’s all the full package, funny, smart ,instructive and awesome views lately. I don’t use to be so active, but staying more in the shadows, because I think if you don’t have anything smart to add, just remain in silence; but a long time passive follower and getting some paid training videos in the past, too.
But now I think I have my 2 cents to add here, regarding the collation thing. I needed to struggle with that in the past, mainly because SAP related apps, always requiring special things, including a different collation. In general, I would recommend to match databases collation with general SQL collation, and one of the reasons is because TempDB stays with the general SQL Server collation, which may be a problem when it’s different to your DB one if your app uses temp tables for ordering or comparing, and some other usages even out of the app control. So my first option is to create a separated box when it requires a specific collation (there are not too many cases, actually, since the default one is very standard/international and case insensitive/accent sensitive which is usually the expected behavior). But If you still need to have it in the same box, check with the app vendor or developer first if they are supporting it and/or expecting problems with the TempDB in a different collation, and then it’s just a matter to add “COLLATE” and the needed collation at the end of the CREATE DATABASE statement. Finally, it would be even possible to change the default collation at instance level, but a big disclaimer should be stated here, such do this under your own risk, try to avoid it and so on. So not putting the whole command here, to avoid “copy/pasters” to run it without thinking, but who is interested can search for it with these hints: it requires to run SQL installer (SETUP) with /ACTION=REBUILDDATABASE and /SQLCOLLATION=TheNewCollation. This will change the engine level and all system databases collation. For the user ones, you can change it manually with the ALTER DATABASE dbname COLLATE newcollation statement, too. And here of course applies what you were mentioning, that if your user DBs have some data, this is not updating the data itself. And even more, the collation also applies to column and indexes names, store procedures, and a lot of other stuff, you need to have into consideration.
Sorry for the very long post, but for once I write… And not good in summarizing
Thanks again!