Before you call me crazy SQL guy (I think that name is already taken) I had several motives for driving 10 hours to Nebraska. In no particular order:
- My wife and I work in different states, Lincoln happens to be on the way.
- We like nature, this one was in a State Park
- There was a cabin available for 3 nights and at a decent rate that allowed dogs
- SQL Saturdays are awesome
- Free sessions and networking
- Free volunteering (sounds weird but if you have done it, you understand)
- I’m considering coordinating my own SQL Saturday
However, with every opportunity there is a cost
- All-in I probably spent $700 I would not have spent otherwise
- I missed some football games (no big deal)
- I missed opening day of hunting season (kind of a big deal)
Here are some of my notes from the sessions I attended
Getting Started with Extended Events – Andy Galbraith
Switching from a working solution to something new when you don’t necessarily have to can cause some anxiety. This anxiety causes paralysis. Sometimes we all need a little nudge to really get going.
I was a little confused about the statement that extended events fire synchronously which causes them to be lightweight. It is a little bit of a contradiction I still have to sort out but I think I understand that some parts are always running, and you can asynchronously choose to record them.
The key bits are packages which contain events, actions which is the info you track, and predicates which filters like a where clause
The event file is a .xel file and is least impactful one of a few ways to save off the information
System_health is like the default trace and always running, don’t modify this.
Use caution which what events you pick and where you store the events, query plans can cause a lot of overhead and storing in the buffer can bloat in memory
Set state=start to make the XE work
Get Your Optimizer to Give up All Its Secrets – Brian Hansen
Brian had a nice organized flow which made me compare his style to more of a college professor
Logical order of operation is important and starts with From to ON
Intellisense doesn’t work until you have FROM
He pointed out some confusion with select meaning something different in Algebra(it’s a filter). In Algebra a projection is like a select
The query tree is the output of the algebrizer and he had a nifty app to visual query trees
Distinct is like a group by
Contant scans are usually good because they can help avoid data access – ex: where clause A>50 and A<50
Tips and Tricks for Performance Troublshooting – Phillip Podhrasdsky
Phillip had a faster pace and covered a lot of ground for an intro session.
Query Plans are key along with statistics. Stats have 200 levels, so you might not get stats for every item in a “colors” table. Creating additional filtered stats can help with this issue
Query Plan Deep Dive – Kevin Boles
Kevin is a great speaker and had a well-organized slide deck.
Ref: Paul White – cross apply blog post
Spot Nested loop joins can be inefficient if a large data pipe is on top
The optimizer will sometimes choose to sort the data to do some joins
Merge join works well if there is order to both tables
Hash joins – the table is built in memory and will spill to tempdb, works well if not ordered
Stream aggregates require order (non-blocking)
Fast first – can cause more overall work, but create a better user experience for poorly tuned query
Asserts- check things like True or False conditions (ex: finding a key to delete)
Pick fill factor intelligently
ADO.net timeout is 60sec ish
Optimize for unknown or recompile are options if you run into sniffing issues
UDFs will generate warnings in the query plans and cause some bad estimates
Dammit Jim! Dr McCoy’s Field Guide to system_health (and the default trace) – Ed Leighton-Dick
Finding answers to “who done it” with the things that record data by default
System_health is XE and trace if profiler – they both get trimmed after a while
Afterwards we met up at one of the Peter Kiewit Lodge cabins for some drinks and food. I talked to the organizer a bit and got some great information from volunteering and the after party to help me consider organizing my own SQLSaturday.
I had a great time and look forward to attending more SQLSaturday events next year.