TIL #5 – Are Your Indexes Helping or Hurting? w. Jes Borland

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.

Advertisements

One thought on “TIL #5 – Are Your Indexes Helping or Hurting? w. Jes Borland

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s