sp_primary_keys_rowset is consuming a lot of CPU
sys.sp_primary_keys_rowset returns details about the primary key columns present in the table name that is passed-in via the @table_name and @table_schema parameters.

The stored procedure has the following definition:

create procedure sys.sp_primary_keys_rowset
(
    @table_name         sysname,
    @table_schema       sysname = null
)
as
    select
        TABLE_CATALOG   = s_pkv.TABLE_CATALOG,
        TABLE_SCHEMA    = s_pkv.TABLE_SCHEMA,
        TABLE_NAME      = s_pkv.TABLE_NAME,
        COLUMN_NAME     = s_pkv.COLUMN_NAME,
        COLUMN_GUID     = s_pkv.COLUMN_GUID,
        COLUMN_PROPID   = s_pkv.COLUMN_PROPID,
        ORDINAL         = s_pkv.ORDINAL,
        PK_NAME         = s_pkv.PK_NAME
    from
        sys.spt_primary_keys_view s_pkv
    where
        (@table_schema is null and s_pkv.TABLE_NAME = @table_name) or
        s_pkv.object_id = object_id(quotename(@table_schema) + '.' + quotename(@table_name))
    order by 2, 3


Although we can't directly see the source of the sys.spt_primary_keys_view view referenced in the above code, we can indirectly see that the view gathers data from various system objects, including:


syssingleobjrefs
sysidxstats
sysobjvalues
sysschobjs
syscolpars


This is what part of an "actual" execution plan looks like when I run the store procedure on my test SQL Server 2016 instance:



As you can see, it's fairly involved.  Having said that, I wouldn't expect this stored procedure to be negatively affecting performance unless it is running against a database containing a very large number of objects.  Glenn's query is returning the top 50 rows ordered by total_worker_time, which is the total amount of CPU time, in microseconds, consumed by executions of this plan since it was compiled.  It's quite possible this procedure is showing up at the top of the list as a red-herring; if the number of executions is extremely high it may be consuming a lot of CPU time without actually having a seriously negative impact on performance.   For example, if the procedure runs for 100ms at the start of a batch that takes 3 seconds, it's responsible for 1/30th of the run time.  If you change the ORDER BY clause to return results in order of average run time, or perhaps total execution count, you may see something more meaningful.

I wrote about how to look at wait stats to see what, if anything, is slowing SQL Server down.