Performance and usage of system stored procedure sp_columns_100

Question

Performance and usage of system stored procedure sp_columns_100

webrunner

SSC-Dedicated

Points: 31718
More actions
November 30, 2020 at 9:07 pm

#3815312
Hello experts,
I'm researching different ways to monitor SQL Server CPU usage by query. I know there are many ways to this, but I just noticed this result and want to know if anyone knows what might make this particular code come up at the top of this particular report.
It seems like this system stored procedure - [master].[sys].[sp_columns_100] - is coming up in the lead by far. Total_CPU is 903498568 and execution count is 230766.
Does anyone know what this procedure does, and is it coming up at the top just because (maybe) it is routine and this total is just built up since the SQL Server last restarted (in this case on 10/6/2020)? I'm just trying to figure out if this system procedure is noise or actually worth investigating.
Result (abbreviated for display):
```
execution_countTotal_CPUtotal_CPU_inSecondsaverage_CPU_inSecondstotal_elapsed_timetotal_elapsed_time_inSeconds
230766903498568903010006041241000
```
Report:
Source: https://stackoverflow.com/questions/39718602/sqlserver-exe-takes-high-cpu-usage-that-is-more-than-90-in-which-scenarios-s
```
SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
   st.text,
   qp.query_plan
from
    sys.dm_exec_query_stats as qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc
```
Thanks for any help!
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

this may give you a hint https://www.mssqltips.com/sqlservertip/3456/getting-sql-server-metadata-with-python/

it would seem that one of your applications does like to get info about tables and its columns a lot - I've seen this before on "generic" applications that try and see what the underlying db has before doing any work on it - but many tend to do it way too often instead of relying on a cache of said info.

unless you know which application is doing it based on the description above you will need to get an extended events trace setup so you can identify who is doing those calls - and then act upon it.

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 2

Check out Query Store

my QS ref: https://www.sqlskills.com/blogs/erin/category/query-store/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

webrunner SSC-Dedicated Points: 31718 More actions · Answer 3

Thanks to both of you for your help! I'll read through those references.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html