Note on my shorthand [+] means actionable insight, [i] refers to something of interest, [!] indicates a warning
[+] All content available on www.BrentOzar.com/go/tunequeries
He uses a checklist approach based on the Mnemonic BECREEPI. [+] Add this to the DBA OS for Performance Tuning
B = Blitz - Use SP_Blitz and SP_BlitzIndex from BrentOzar.com to rule out fundamental server level issues before you waste time going any further. It gives you the advice for what to change to tune the server.
E = End-User Requirements - Figure out what the End-User Requirements are. Essentially you're defining the goal. It may be a level of performance but and they may have an acceptable wait time.
C = Capture Metrics - You need to collect data about the query in order to assist the tuning process.
R = Read the Metrics - You went to the trouble of collecting and storing it, now read it. There could be answers in this data.
E = Experimenting - Only now are you starting to refactor the code. Keep the original, version the changes with comments reflecting your thought process.
E = Execution Plan - Dive into the plan - but he warns against trusting an estimated plan. It looks like a foreign language and you should treat it like Arabic - start at the top right and work to the left and down.
P = Parallelism - If MAXDOP = 1 then the SQL Server cannot use parallelism when it's beneficial.
I = Index - It's the last resort. Indexes are not free so don;t just throw NC_IDX's at the problem as you'll slow down transactional writes thus creating another problem elsewhere.
He downloaded and used the StackOverflow DB in his demo. It's free and more "real world" than AdventureWorks.
He used www.StatisticsParser.com to copy his Statistics IO messages into and it presented the output very nicely for him.
[!] - "Estimates are junk" - he says in the actual plan you often get estimates as well so you can't rely on them. You must look at the reads to see the true picture. Often table variables look great in the estimated plan but it treats every table variable as a table of 1 row so it's false. This is precisely because they are variables.
Estimated sub-tree cost is a generic rating of a query based on arcane Microsoft code but valid if comparing queries on the same server. Across servers it has no value.
[i] - He advises no more than 5 indexes per table as a rule of thumb.
[+] He uses SQL Sentry Plan Explorer which is free. Add it to the core software build list for the DBAs.
[+] Go back and look again at Indexed Views & Schema Binding. They could be useful in some DW cases. It means a query across multiple tables can have an index on it. But it does cost and the Schema Binding means no schema changes to the underlying objects so the tables are static otherwise the view must be recompiled. And some ANSI NULL settings need to be aligned as well.
Overall - Excellent fast paced session worth watching again. 5*'s
No comments:
Post a Comment