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.
#Recompile a stored procedure professional
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 kommentar(er)
