Benjamin Nevarez Rotating Header Image

DBCC SHOW_STATISTICS Works With SELECT Permission

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.

clip_image002

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.

clip_image004

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.

About the author

Benjamin Nevarez Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.

3 Comments

  1. Paul McLoughlin says:

    Whilst I am obviously pleased that Microsoft have made improvements to the way that cardinality estimates are available when using linked servers, unfortunately there still exist issues when querying views over linked servers. In the example that you show above, if you create a view on the remote side of the form:

    create view Sales.SalesOrderHeaderView with schemabinding
    as
    select
    from Sales.SalesOrderHeader
    go

    and use that in the query, then a guess is still used – regardless of the credentials used for the linked server.

    I’ve logged this as a bug with Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/details/773286/incorrect-row-estimates-when-using-views-over-linked-servers but unfortunately it has just been closed today as ‘won’t fix’, so looks like we’re stuck with this issue for the time being. Sadly there are no comments on why this won’t be resolved.

  2. [...] DBCC SHOW_STATISTICS Works With SELECT Permission - SQL 2012 SP1 improvement when working with Linked Server queries shared by Benjamin Nevarez (Blog|Twitter). [...]

  3. Hi Paul,

    Thanks for the interesting comment. Unfortunately it looks like the best workaround for now is to just refer directly to the required tables in the view.

    Regards,

    Ben

Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>