Post reply

SQL Server elevation of privilege vulnerability patch and sp_pkeys SP

  • February 23, 2021 at 2:57 pm

    #3849064

    We have a SQL Server 2017, which was recently updated with https://support.microsoft.com/en-us/topic/kb4583468-microsoft-sql-server-elevation-of-privilege-vulnerability-b51e9244-d952-0372-0cf0-2929da230340.  Against a database on this server, we run an application which connects via RDO and SQL Server Native Client.  As a result of this patch, the application has slowed down to a state that it has now become unusable - operations that previously took a couple of seconds now take around 30 seconds to complete.

    Uninstallation of this patch immediately fixed the issue.  Examination of the MS documentation yielded no clues.

    We ran SQL Server Profiler whist running the application, both with and without the patch, and the only thing that we found to be of interest (i.e., different) is that calls to the [sys].sp_pkeys stored proc seemed to take a lot longer with the patch installed.

    • Without patch would be effectively instantaneous, around 1500 page reads and no time for CPU
    • With patch would be around 150 milliseconds, around 6500 page reads and take around 140 milliseconds CPU

    The documentation for the stored proc - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-pkeys-transact-sql?view=sql-server-ver15 doesn't give any help, and from looking at the code of the SP, nothing immediately jumps out at me, and I don't really want to start modifying a system stored proc anyway.

    Would anyone have any thoughts as to what the next steps should be?

     

     

  • February 23, 2021 at 4:54 pm

    #3849124

    I'd check the execution plan, not profiler.  You may be able to improve performance with more/less/different indexes or some query hints (like forcing a hash match).

    Or, another thought, did you update statistics after installing the patch?  Could be that the estimates are way off.

    Either way, first step when doing performance tuning due to performance changes is to check the execution plan before and after the change is applied.

    With that one stored procedure, seeing a 150 millisecond change is not going to cause the stored procedure to take 30 seconds from a couple previously unless that one stored procedure is being called a LOT of times.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • February 23, 2021 at 11:48 pm

    #3849259

    Its a system stored proc, not one of ours, and points to system tables, which are not ours.  I'm slightly hesitant about changing the internals of SQL Server system objects - I don't even know if I can.  We did update statistics but it didn't make any appreciable difference - I don't know if stats work for system tables, but just about everything seems to be an index seek, connecting these four tables: -

    • sys.indexes i,
    • sys.all_columns c,
    • sys.all_objects o,
    • sys.key_constraints k

    It is used by RDO (Remote Data Objects), and is used  a lot of times in the simplest of processes - it seems that it is used for every single table that is referenced.  It takes a table name and returns the name of that table's primary key, and so is used for the building of recordset objects - it gets the primary key, and then selects a single row in order to get the complete list of columns.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply