PASS 2015 10-28-30 Seattle WA

SQL Server Indexing for Performance

Kimberly Trip

NOTE: Narrow NC indexes very rarely are useful.
NOTE: Putting a clustered index PK on an NC index only puts it there once, as opposed to a covering index without it (which adds it as a hidden column). This is important to know.

NOTE: Manually index your FKs with NCs.

NOTE: Creating WHERE only clause NC indexes rarely work to improve performance. Consider adding in the SELECT and JOIN columns too.

NOTE: Adding month-end indexes and then dropping them can be a prudent way to improve performance. Easy to implement in an Agent Job.

NOTE: DateTime columns in NC indexes get used a lot, especially if you are using table partioning based on this.

NOTE: Get rid of the unused and duplicate indexes. There are queries already in PLDBA for this. sp_BlitzIndex also does this.

NOTE: Putting MAXDOP(1) will create the same plan for everyone, regardless of the machines they are run on, for demo purposes.

NOTE: Remember the little plus sign in SSMS when viewing large execution plans. (Magnifyer)

TODO: Play with AutoPilot which tests the usefulness of a missing index without really adding it first. COOL!

Here is the session with Kimberly:

DBA Mythbusters

Paul S. Randal

— Wednesday 1:30pm – 3:00pm

NOTE: “PLE should be 300”: was true, but SSDs and Flash make it obsolete. a low number means likely a memory pressure issue

NOTE: Using SSDs means using at least 2 for Mirror 1, otherwise it will hard crash if TempDB is on it.

NOTE: Page splits are logged!!!

NOTE: You generally want you data density on a page to be as dense as you can get in the buffer pool, as memory is SQL Servers most pressions resource.

NOTE: “Warming up” the buffer pool means that time needed to load the normal load of data pages into the pool so that SQL Servers executes queries without doing IO.

NOTE: Enable T1118 automatically? Did I do this already on the new cluster?

TODO: Jonathon wrote a query that interogates the plan cache and suggests the best cost threshhold of paralellism for your system.

NOTE: Checkpoints write both clean and dirty pages from either committed or uncommitted to disk. Crash recovery with roll back those pages that were uncommitted. It is far more efficient to just write out the pages that interrogate each transaction to see if it is committed or not.

TODO: make sure T1117 is turned on, it makes sure tempDB grows accross all data files, important for round-robin.
Here is Paul and I after the session:

Views – 2630