Now both plans are perfectly fast, we can observe on the second one that we have our option recompile which generated an optimized parallelized plan for ParentId equals 0. So we will have a complete new plan for the value 0 but not for the other ones where the number of records is really low and where we don’t have a chance to have a bad plan. WHERE p.ParentId = BY u.Reputation DESC' ĮXEC = this code we check if the parameter of the Stored Procedure is 0 and in this case we force a recompilation. SET NOCOUNT, XACT_ABORT nvarchar(MAX) = N'' We will write a Dynamic SQL which will force the recompilation if we execute our Stored Procedure for ParentId equals 0: To avoid this recompilation problem and to boost our SP the best way is to use Dynamic SQL. The selectivity of our index will be more than poor for 99% of the records… But if we check the distribution of the data in the Posts table: To fix our problem we can try to create a selective index on the Posts table, put the column ParentID first and have a look of that happens. If we remove again the plans cached, execute the second query first (with ParentID = 0) and after the one with ParentId equals 184618 we have a completely different results:Įxecution for ParentId equals 0 is now really fast compare to the previous one and the one with ParentId equals 184618 is still acceptable.īut the plan has changed and if the query is executed in the bad order performance problem will come back.Ī way to solve this issue would be to add the hint OPTION(RECOMPILE) in the Stored Procedure to RECOMPILE our query and recreate a fresh query plan each time the SP is executed.īut Erik showed us that doing recompilation each time the Stored Procedure is executed can used CPU and it’s not where we want to use our CPU! The first question is “can we fix the key lookup?”, here we want to retrieve all columns of the Posts table so we will not create an index which will cover the start of a query. Here we have issues with parameter sniffing where a plan is very good for a small number of rows but bad for a large number of rows. The query retrieves more than 6 Millions rows instead of 518… it takes a lot of time processing these rows for the fourth operators. The result is not exactly the same… The execution time increases dramatically with more than 11 minutes. Lets run the same Stored Procedure with ParentId equals to 0 and check the result: We can see that the query runs very fast, 7ms, it uses index seek on Posts table with our non-clustered index, the Posts clustered index for the lookup to find the others needed columns and do an index seek also on Users table with the clustered index of this table. (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE) Īfter having removed the plans in cache with DBCC FREEPROCCACHE, we run the Stored Procedure with ParendID equals to 184618 and check the execution time and query plan: We have also a non-clustered index on the Posts table where index keys are ParentId and OwnerUserId: We have a Stored Procedure, here called MySP, where we select the top 10 values from Posts table join to Users table where column called ParentID in the Posts table equals the supplied ParentID column. In this blog post, I will come back on one of the tips Erik gave us during his training session of one day about The Professional Performance Tuning Blueprint and show you how dynamic SQL can save our life, here save the performance, when parameter sniffing is not good enough. SQLBits is one of the largest SQL Server conference in the world with some famous speakers like Brent Ozar, Bob Ward, Erik Darling… Sadly, I have to do it remotely this year but I promise next time I will come in-person □ Since Tuesday, I have the chance to follow the SQLBits 2022 conference.
0 Comments
Leave a Reply. |