I’ve been wanting to blog about several topics in the last few months and now that I’ve finished a couple of projects, including working on the SQL Server 2012 Internals book, I think I will have some time to do just that. The first of these topics takes me back to the first day at the PASS Summit 2012 keynote where Ted Kummert, Microsoft Corporate Vice President, Data Platform group, made several announcements, including the release of SQL Server 2012 Service Pack 1. Then after looking at this service pack documentation I noticed that one of its enhancements is that the DBCC SHOW_STATISTICS statement now works with SELECT permission. This has been a fix requested while ago, related to a problem with statistics and linked servers, something I blogged about previously in my post Optimizer Statistics on Linked Servers. In summary, the problem was that the query processor was not able to get the required optimizer statistics from a remote SQL Server instance due to permissions of the user used by the linked server. Previous to SQL Server 2012 Service Pack 1, in order to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.
So let us see how it works, using an example similar to what I did on my previous post.
SELECT l.SalesOrderID, l.CustomerID FROM AdventureWorks2012.Sales.SalesOrderHeader l JOIN [remote].AdventureWorks2012.Sales.SalesOrderHeader r ON l.SalesOrderID = r.SalesOrderID WHERE r.CustomerID = 11000
My test configuration uses a linked server relying on a login with read only permissions on the data (for example, db_datareader). Running the previous query against a remote SQL Server instance without Service Pack 1 installed returns a bad cardinality estimate and the following plan using a hash join. Since the local query processor does not have access to the statistics on the remote server it has to rely on a guess, in this case estimating 2,362.49 records. You can notice a big difference between the actual and estimated number of rows.
After applying Service Pack 1 on the remote instance I can run the same query and this time we get a better cardinality estimate and the query optimizer is able to make a better decision. In this case we get a new plan using a nested loops join, which is more appropriate for a small number of records.
Although the new behavior is enabled by default after service pack 1 is installed, you also have the choice of disabling it by using trace flag 9485, which you can use in case of regressions in scenarios where the performance of some queries may be getting worst. Enabling trace flag 9485 reverts the new permission check to the original behavior.
Run the following statement to enable trace flag 9485.
DBCC TRACEON (9485, -1)
Running the query again will produce the original plan with the hash join and the 2,362.49 cardinality estimate. You may also need to use a statement to clear the current plan or force a new optimization during your testing, for example, DBCC FREEPROCCACHE. Apart from the change in permissions mentioned in this post, the behavior of using statistics with linked servers is the same as the one described in my post, so you may refer to it to understand how it works. Finally, Microsoft has not mentioned yet if this fix will be implemented on SQL Server 2008 R2 or other previous supported releases.