1

SQL 13.x will intermittently spike a CPU usage (60-90%). It will stay high so we added AWS Performance Insights. These queries are not something we have defined or used but something is calling them from our IIS machines. Has anyone seen this before or any ideas on what is discovering columns/reasons this is happening?

select * from #spproccol

insert into #spproccol exec sys.sp_sproc_columns @p1,@p2,@p3,@p4,@ODBCVer

create table #spproccol
        (
            PROCEDURE_QUALIFIER sysname collate catalog_default null,
            PROCEDURE_OWNER sysname collate catalog_default null,
            PROCEDURE_NAME sysname collate catalog_default not null,
            COLUMN_NAME sysname collate catalog_default not null,
            COLUMN_TYPE smallint not null,
            DATA_TYPE smallint null,
            TYPE_NAME sysname collate catalog_default not null,
            PRECISION int null,...

This seems to correlate with another odd command

select
            PROCEDURE_QUALIFIER         = s_scov.PROCEDURE_QUALIFIER,
            PROCEDURE_OWNER             = s_scov.PROCEDURE_OWNER,
            PROCEDURE_NAME              = convert(nvarchar(?),
                                            s_scov.PROCEDURE_NAME +'?'+ ltrim(str(s_scov.procedure_number,?))),
            COLUMN_NAME                 = s_scov.COLUMN_NAME,
            COLUMN_TYPE                 = s_scov.COLUMN_TYPE,
            DATA_TYPE                   = s_scov.DA
6
  • Which applications does this SQL Server host databases for? Feb 9 at 20:52
  • It's an inventory application using .Net and ASP. Connection string is Driver={SQL Server} using ODBC for that side. Maybe sniffing parameters for sprocs?
    – Brad G
    Feb 9 at 21:11
  • Yeah, looks like it. It’s probably making similar calls to other system procedures as part of discovery processes. I’m not sure there’s much you can do about it. How many CPUs do you currently have assigned to this server? Feb 9 at 21:46
  • 4 cores, AWS r6i with 32gb. Just discovered that we only have one tempDb file so will be updating to 4 to reduce bottle neck there. Will try some different connection strings to see if that helps
    – Brad G
    Feb 9 at 21:58
  • 1
    If you've come up with a solution, you should add it as an answer to your question. Feb 11 at 17:31

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.