Following up on this and this, the red X in an execution plan means that you have a problem in your plan and a heap is a table without a clustered index wherein there is no general sort order and where a rid (row identifier) may be used in the absence of a clustered index. DBAs are encouraged to open up execution plans to developers as developers need the plans to tune. What is more, DBAs should probably just open up permissions enough for developers to just screw up dev environments. Just worry about keeping UAT (user acceptance testing) and prod clean and pristine. "Great Performance Tuning" by Grant Fritchey was recommended. I don't see that exact book here so maybe it is "SQL Server Query Performance Tuning" instead. Inline functions are pulled into query plans allowing SQL server to make optimizations. In inline table functions one may also have parallelization. In general you will want to try to avoid using functions as these sabotage SARGability. If something is SARGable (search-argument-able) it is searchable by a WHERE cause. WHERE, ORDER BY, GROUP BY, and HAVING all open the door to SARGability, but you may sabotage SARGability by wrapping the left side of the predicate in a scalar function and forcing a scan instead of a seek. For example, if you wrap a DateTime type column in a function that just returns the year at the left of predicate and then set that equal to 1999 (at the right) in the name of trying to find the year 1999, you will sabotage the index on the column (assuming there is one) and will have to increment rbar through every record to find matches for 1999. Speaking of assume, the photo here is of Wayne Sheffield reexplaining Benny Hill's old explanation of the word. I can't really exactly recall how this came up in our conversation. Scalar functions by their very nature return a single value and prevent parallelization. What is parameter sniffing? When you have several different parameters at a sproc, depending upon the amount of data being returned, one of two different execution plans may be returned. Parameter sniffing happens every time you execute SQL. SQL will try to compile the faster plan, but, this may not always be the fastest plan. 97% of all data results may fall into the center of the bell curve where the calculated plan works, but there may be outlier scenarios forcing a square peg into a round hole. What to do for the outliers? Putting RECOMPILE in strategic places within the sproc is one solution. Another is to use local parameters instead of globals. Global parameters are the usual inbound parameters at a sproc while to make locals, you take the globals and immediately turn around and assign them to new variables just inside of the stored procedure's code for mechanics. Then do everything with the locals instead of the globals in the name of fighting bad parameter sniffing scenarios. Conor Cunningham a product engineer at Microsoft has created, to fight this problem, "The Query Store" which is to come out in the next version of SQL Server. What is a plan guide? Every time you execute a query it creates a plan guide and every time you rerun the query it will use the plan guide. Conor's thing will allow you to force the use of particular plan guides to tell queries to work in ways that is not SQL's first choice. A query hint will allow you to optimize a query for a particular circumstance in which the inbound parameters are specific x, y, and z values. ADD EVENT sqlserver.query_pre_exection_showplan is going to show you an estimated plan and ADD EVENT sqlserver.query_post_exection_showplan is going to show you an actual plan.
No comments:
Post a Comment