Tuesday, October 16, 2012

Cardinality estimates over a linked server

So, I was at SQL Saturday #149 listening to Joe Sack (blog|twitter) talk about how many different ways cardinality misestimation can really wreck the query optimizer's day. One part in particular stuck out for me, though. Specifically, if you run a query over a linked server and whatever principal you end up with on the "remote" side doesn't have either sysadmin, db_owner, or db_ddladmin, table statistics aren't returned to the "local" query optimizer and so it starts guessing (and poorly). Joe and I talked a little bit about it and he pointed me to blog posts by Linchi Shea and Benjamin Nevarez. I thought that we should be able to reduce the required permissions by doing some module signing. So I tried. I'm happy to report that it's possible, but it does involve modifying the resource database (ever so slightly). So if that's the sort of thing that makes you squeemish, stop reading now.


Okay... so you're still here. I'm glad! If you're not familiar with module signing, it allows you to grant elevated permissions through a module (stored procedure, function, trigger, or assembly). For example, let's say that I have a junior DBA that I want to give the ability to change user's passwords to. Normally, that would require ALTER ANY LOGIN permission. But let's say that I don't want her to be able to alter the logins arbitrarily, only to change their passwords. With module signing, I can create a stored procedure that takes the name of the login and the new password, "sign" the stored procedure, do some other permissions related monkey business, and finally grant my junior DBA execute permissions on the stored procedure. Now they'll be able to run the procedure and change the password for any login.

So, back to the original problem, in order to get at the statistics on the remote server, stored procedures are called that essentially do a dbcc showstatistics on the remote table. It's from this call that we require any of sysadmin, db_owner, or db_ddladmin. So, I ran a trace of a remote query and found the following stored procedures called (all in the mssqlsystemresource database):

  • sp_table_statistics2_rowset
  • sp_indexes_100_rowset
  • sp_columns_100_rowset
  • sp_tables_info_90_rowset_64
  • sp_check_constbytable_rowset
The first step is to create a certificate with which we'll do our code signing and permissions granting.

use master;

create certificate [CodeSigningCert]
encryption by password = 'Super Secret Password!'
with subject = 'Code signing certificate'

backup certificate [CodeSigningCert] to file = 'c:\temp\code.cer'
with private key (
   file = 'c:\temp\code.pkv',
   encryption by password = 'abc123!',
   decryption by password = 'Super Secret Password!'
)

use AdventureWorks2012;

create certificate [CodeSigningCert]
from file = 'c:\temp\code.cer'
with private key (
   file = 'c:\temp\code.pkv',
   decryption by password = 'abc123!',
   encryption by password = 'Super Secret Password!'
)

create login [CodeSigningLogin] from certificate [CodeSigningCert]
revoke connect sql from [CodeSigningLogin]
create user [CodeSigningUser] for login [CodeSigningLogin];
exec sp_addrolemember @rolename = 'db_ddladmin', @membername = 'CodeSigningUser'

Next, because the abovementioned stored procedures are in the resource database, we have to start out server in single-user mode (using the -m flag in the startup parameters). Once you do that, you can do this:

alter database mssqlsystemresource set read_write;
use mssqlsystemresource;

if not exists (select 1 from sys.certificates where name = 'CodeSigningCert')
begin
    create certificate [CodeSigningCert]
    from file = 'c:\temp\code.cer'
    with private key (
        file = 'c:\temp\code.pkv', 
        decryption by password = 'abc123!',
        encryption by password = 'Super Secret Password!'
    )
end

IF NOT EXISTS (
    SELECT 1
    FROM sys.crypt_properties AS cp
    INNER JOIN sys.[certificates] AS c
        ON [cp].[thumbprint] = [c].[thumbprint]
    WHERE cp.[major_id] = OBJECT_ID('[sys].sp_table_statistics2_rowset')
        AND c.[name] = 'CodeSigningCert'

)
BEGIN
    add signature to [sys].sp_table_statistics2_rowset
    by certificate [CodeSigningCert]
    with password = 'Super Secret Password!'
END

IF NOT EXISTS (
    SELECT 1
    FROM sys.crypt_properties AS cp
    INNER JOIN sys.[certificates] AS c
        ON [cp].[thumbprint] = [c].[thumbprint]
    WHERE cp.[major_id] = OBJECT_ID('[sys].sp_indexes_100_rowset')
        AND c.[name] = 'CodeSigningCert'

)
BEGIN
    add signature to [sys].sp_indexes_100_rowset
    by certificate [CodeSigningCert]
    with password = 'Super Secret Password!'
END

IF NOT EXISTS (
    SELECT 1
    FROM sys.crypt_properties AS cp
    INNER JOIN sys.[certificates] AS c
        ON [cp].[thumbprint] = [c].[thumbprint]
    WHERE cp.[major_id] = OBJECT_ID('[sys].sp_columns_100_rowset')
        AND c.[name] = 'CodeSigningCert'

)
BEGIN
    add signature to [sys].sp_columns_100_rowset
    by certificate [CodeSigningCert]
    with password = 'Super Secret Password!'
END

IF NOT EXISTS (
    SELECT 1
    FROM sys.crypt_properties AS cp
    INNER JOIN sys.[certificates] AS c
        ON [cp].[thumbprint] = [c].[thumbprint]
    WHERE cp.[major_id] = OBJECT_ID('[sys].sp_tables_info_90_rowset_64')
        AND c.[name] = 'CodeSigningCert'

)
BEGIN
    add signature to [sys].sp_tables_info_90_rowset_64
    by certificate [CodeSigningCert]
    with password = 'Super Secret Password!'
END

IF NOT EXISTS (
    SELECT 1
    FROM sys.crypt_properties AS cp
    INNER JOIN sys.[certificates] AS c
        ON [cp].[thumbprint] = [c].[thumbprint]
    WHERE cp.[major_id] = OBJECT_ID('[sys].sp_check_constbytable_rowset')
        AND c.[name] = 'CodeSigningCert'

)
BEGIN
    add signature to [sys].sp_check_constbytable_rowset
    by certificate [CodeSigningCert]
    with password = 'Super Secret Password!'
END

alter database mssqlsystemresource set read_only;

Once you're done with that, restart your server in multi-user mode (i.e. get rid of the -m startup parameter) and you should be good to go. I used the following test query:


SELECT l.* FROM [Sales].[SalesOrderHeader] as l
JOIN [REMOTE].[AdventureWorks].[Sales].[SalesOrderHeader] as r
ON l.SalesOrderID = r.SalesOrderID
WHERE r.CustomerID = 1
option(recompile)
If I take the CodeSigningUser out of the db_ddladmin role, I get this (notice the difference in estimated vs actual):
So there you have it: a way to get around the cardinality problem over a linked server without having to grant permissions are too permissive. Thanks to Joe not only for a really educational session, but also for tech reviewing the solution.

No comments:

Post a Comment