I’ll try to highlight the top takeaways per session from my side.

Keynote – Level Up with Azure Data

This was an entertaining show by Microsoft’s Bob Ward and Buck Woody . They pretended that Bob has a dream about presenting and could do everything. Great consequence was that they had a lot of great people from the Microsoft Team invited presented including Pedro Lopes, Anna Hoffmann, Patrick Leblanc (Guy in a cube) and much more.

Demos were given with SQL Server 2022 CTP 1.3

Great announcement was that SQL Server will be available for public preview at the end of the first half of year 2022. Seems to still take a bit till it is oficially released tough ;-).

Interesting demo for me was about Parameter Sniffing.

Pedro Lopes showed Batch Requests for Perfmon with parameter sniffing problems in effect as we know them and I have just learned in more detail from Brent Ozar.

Afterwards switching to SQL2022 Compatibility mode the procedure ran much faster.

Most interesting we had two different queries with one plan each now visible in QueryStore.

So hopes are that in the best cases…performance is really boosted in real life situations with the improvements while at the same time the DBA can still have a decent overview in Query Store.

Other demos included PowerBI realtime streaming leaderbords, Data VIrtualization with Azure Data Lake (note: I need to know more about the magical Parquetfiles) Azure Hyperscal and much more.

Being a classic on-prem-guy by now….the engine optimizations are the most important for me.

How to Troubleshoot SQL Server CPU Problems

I recently faced an outage where we had serious CPU problems so the session by Kevin Kline naturally was the first for me to attend.

Kevin delivered a great presentation with things I could add to my existing knowledge.

These are my top takeaways:

You should use a checklist for problems.
This is how professional doctors or pilots do it and really helpful to quickly have actionable results.
Kevin’s checklist consists of the following parts:

1. Did we change anything?
Bad situation if you installed a SQL Server update, windows updates and updated the app….this is a situation I had just recently being naive and never burnt before. In the end the SQL Server update was not the culprit but it could have been and it’s not easy to argue in such a situation with your peers :-).

2. Is the issue inside or outside of SQL Server?
Just open up Windows Event Log and watch out for errors. It could be a general hardware failure or another Windows process eating up your CPU.

3. Could the issue be caused by a SQL Server error?
Have a look at the SQL Server Error Log and investigate the errors you might find

4. How do wait statistics look like?
Watch out for any CPU related waitstats (in specific SOS_SCHEDULER_YIELD). Maybe you have another problem like blocking and the CPU problems are just an effect of that.

5. Correlate wait statistics against other metrics like PerfomCounters, Trace/XEvents or QueryStore
This is where I assume the real work begins and something I still have to learn thoroughly.

6. Have a review meeting afterwards and implement measures to counteract running into the same problem again
This could be writing down the problem or building alerts for the future.

Most importantly know your baselines…meaning how your metrics look like if your server is not under pressure. Maybe you already have a monitoring tool for that…if not I personally can recommend Brent Ozar’s First ResponderKit with Power BI. The Power BI part is discontinued, meaning that there are no updates to it, but it still works fine.

My final learning from this Session is this quote of Kevin:

80% of database performance problems come from the way we designed our database (datatypes, modelling) and the code that interacts with it

Regarding the tools he showed Erik Darling’s sp_pressureDetector is new to me…I will give it a try before the next problem awaits.

Learning to Listen: Making the Most of Mentoring

This was an interesting shorter session (20 minutes) on mentoring. A mentor (Alexandar Arvidsson) and a mentee (Linda Torrang) shared their experience.

Top things which made me think…being in a mentoring relationship as a mentee myself:

• “Know thyself”: Inform the mentor about the things that make you tick….Focus on strengths in mentoring sessions not on weak spots alone.
• “Consider your why”: Why are you doing this? Why is the mentor doing this? Set goals!
• “You cant solve all problems” (as a mentor): Every mistake and challenge is context-bound and has its own solution,
• “Mentoring is daring to ventour out of your comfort zone”

Planning your career

Brent Ozar always has a great presenting style…as in this presentation as well. He had no the massive applause like the Microsoft Product Team from the other room doing all the announcement but IMHO was much more entertaining. As I already heard a lot of announcement from Microsoft in the PreCon I decided to listen to him on career planning. The session turned out to be much focused around a self-employed role such as Brent does but I could take away a few learnings for me as well.

• Define what carreer success and also career failure means to you (seems to be natural but to be honest I have not done that explicitly yet)
• Make a plan what will you have accomplished in 1 year (ebook, presentation, precon class) and start to be very intentional about the sessions you sit in at conferences and the blog posts you read.

Brent said:

Just because there is already a blogpost online for a specific topic doesn’t mean you should not write another of yourself.

And he also shared a quote by Lori Edwards, which truly resonates within me as a family guy:

The people you work for are waiting for you at home.

Top 5 Disasters as a SQL DBA

We best learn from mistakes and challenging situations right? What better chance to learn from situations others had and prevent happening them to oneself then. That’s the reason why I went to this great sesison by Gethyn Ellis and David Postlethwaite.

Remedy to fix deleted logins
Start SQL Server in Single User Mode and then recreate the login. I have done this in the past as I once had forgotten the SA password right after a fresh install of the server and still needed that sa-login. What I learned new is that you can use startup parameter -m SQLCMD to only allow a specific program (in this case SQLCMD) to connect. Then you can restart the SQL Service and connect with SQLCMD without specifying user name or password.

Disaster Recovery with one Database with Recovery Pending
They showed a corrupted database that has no logfile any more and the backup would be to old to restore.

There are two ways to fix this:
1. dirty and undocumented way using the feature “FOR ATTACH_FORCE_REBUILD_LOG”

or 2.

• Create a new database with the same name. Then alter the database to use the data file from the original database.
• Put the database into emergency mode
• Put it into single user mode
• Run checkdb
• Bring it online again
• Review the error messages and work out what data you might have lost

This is something I did not have to do before and sounds really helpful. However it remindes me of something I did years ago in a SQL Server 2008 cluster to put tempdb on another drive by fooling sql server around it ;-).

Other disasters included data center flooding (natural disaster) and failing SQL Agent Jobs for backup processes because of the user creating the jobs had been deactivated.