After taking the weekend off, I’m back with my next TIL Holiday SQL Learning series entry. Tonight, I chose Jes Borland’s (b|t) session Are Your Indexes Helping You or Hurting You? I remember running into Jes in the Community Zone, after she finished this presentation, and she was elated with how well it went. This made me really excited to check it out once recordings became available, so here we are!
DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.
Here’s my list of interesting notes & tidbits:
- What’s your Workload?
OLTP = Transactional. Generally write speed is priority over read speed. Narrow, fewer indexes.
OLAP = Analytical. Generally read speed is priority over write speed. Wider indexes more acceptable.
- Use the SSMS Split Window functionality – it’s the bomb!
- SSMS Execution Plan – On an INSERT operator, SSMS hides subsequent operations against Non-Clustered Indexes. Must dig into Properties to see this! Or just use SQL Sentry Plan Explorer.
- Trace Flag 2371 – Changes SQL Server auto-update statistics threshold, to scale based on # of rows. See KB 2754171.
- STATS_DATE() – Didn’t know this function existed! Can see when statistics was last updated via sys.indexes. Great for a baseline/benchmarking diagnostic query!
Yet again, Jes hits a home run with this session. I love how she opens, rapidly ramping up and making sure everyone is on the same page. I also recall her talking about how she explained SARGability and how well received it was… and wow, I really liked her clever idea to demonstrate it to the audience! That worked VERY well! I’d say that this session is most definitely a must-watch for anyone who has to consider doing anything with indexing.