can high number of query errors generated by monitoring tool cause 100 % CPU utilization?

Question

can high number of query errors generated by monitoring tool cause 100 % CPU utilization?

ebooklub

Hall of Fame

Points: 3905
More actions
January 4, 2016 at 2:00 pm

#323696

Hi,
installed 3 SQL server instances STD 2014 (inst1, inst2, inst3) on VM (VM001) -- WIN 2008R2 (4 CPU , 60 GB RAM 15 GB RAM for each SQL instance)
All SQL instances monitored by 3-party tool.
Monitoring tool installs "admin" database on each instance right after it detects new instance on network.
I got alert notification from VM001 about high CPU utilization (100%)
Actions taken
1. logged on VM001
2. stopped each SQL instance one by one, after inst2 were stopped , CPU utilization return to 10%
3. restarted inst2 , executed "sp_who2" and found several (7) connection open (spid > 50) ,all of the connections were trying to run same query against "admin" database
that does not exist, CPU = 100%,
4. I started killing those connections one by one. After number of connections dropped from 7 to 3 CPU dropped from 100% to 70%.
5. Executed script to create and populate "admin" db, CPU utilization dropped to 10% ,monitoring tools stopped constantly query "admin"
database
Question
Can several constantly open connections (7) running same query (select count(*) from admin.monitor where a_date is Null and b_value = 3) against not existing db
bring VM(s) CPU utilization to 100% ?
Yes, those query will constantly generate Errors even ,but how they can overload the CPU?
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

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

Brad Feaker-195979 SSChasing Mays Points: 639 More actions · Answer 1

It might be helpful if you told us exactly what the monitoring software is. And are you using VM-Ware or Hyper-V for your virtualization?

Brad Feaker"Tantum religio potuit suadere malorum." - Lucretius

ebooklub Hall of Fame Points: 3905 More actions · Answer 2

thanks for the tip about "monitoring tool" ,this is pointed me to right directions

Host using VM-Ware

I called 3-rd party, but it fact it is home grown monitoring based on PowerShell code

Until I added missing database in SQL profiler I am able to see that monitoring agent (installed locally) where trying to run the query every 1 sec or less.

I traced in profiler All errors and warning and Completed actions.

My best guess:

since SQL server instance itself does not generate high CPU during query execution (based on CPU column values in sysprocesses),

all high CPU activity generated by PowerShell code after query return errors from SQL server

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

If you monitor the CPU usage when your PowerShell tool is running or is not running, you should be able to tell if your tool is causing the issue(s).

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ebooklub Hall of Fame Points: 3905 More actions · Answer 4

I confirm this is power shell problem with monitoring tool.

I got CPU alert from another VM with new SQL instances installed ,

same story ...admin db was missing and monitoring tool was querying it constantly from 5 open connections.

As soon I as created db , monitoring tools stops generating errors ,CPU fail to 5% mark.

Ed Wagner SSC Guru Points: 287015 More actions · Answer 5

It sounds like the monitoring tool itself is the source of the problem. Is disabling it an option?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions