February 18, 2011 at 5:42 am
Hi all,
I have a server with two SQL instances - both are being hammered by something at regular intervals of about 1/2 hour.
It is hard to examine what is happening at the time because sql server is consuming all the cpu.
What I have managed to see is a query being issued 5-10 times per second for a period of minutes at a time.
The query is this:
SELECT * FROM "C3PO_CONNECTIONTEST"
Does anyone recognise that table name from anywhere ?
It does exist in the database but I don't know who or what put it there - it is empty and contains one column called "a"
At the moment I am hoping it is part of a monitoring package or some other non-sinister tool !
Thanks for any help.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 18, 2011 at 5:57 am
I think it was created to test the connection pooling of C3P0 which is used by JDBC and nHybernate.
-Roy
February 18, 2011 at 6:20 am
Roy Ernest (2/18/2011)
I think it was created to test the connection pooling of C3P0 which is used by JDBC and nHybernate.
Great, thanks very much Roy, that has given me enough information to chase the right people!
Big Thumbs Up!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 18, 2011 at 6:37 am
Roy, you were spot on..I did a bit of research, found out about the idle timeout testing that C3P0 does and that led me to the configuration file that contained this:
<property name="automaticTestTable" value="C3PO_CONNECTIONTEST"/>
Which is exactly what I was looking for - now I just have to figure out why it is flooding the server! More research I think 😛
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 18, 2011 at 6:41 am
Glad I could be of help... 🙂
-Roy
February 18, 2011 at 7:07 am
February 18, 2011 at 7:18 am
Mr Magoo I'd love to hear the back story on this...might be very educational for us....
what happened that made you start thinking something was flooding the server...after looking at the connections with i assume sp_who2? you tried to track down the offending connections? did the hostname in sp_who2 not really help?
I'd not be surprised if I end up in the same situation a month or so from now, or whatever my [memory retention for this thread] + 1 day is...so i'd forget what c3PO was.
Lowell
February 18, 2011 at 7:51 am
Lowell (2/18/2011)
Mr Magoo I'd love to hear the back story on this...might be very educational for us....what happened that made you start thinking something was flooding the server...after looking at the connections with i assume sp_who2? you tried to track down the offending connections? did the hostname in sp_who2 not really help?
I'd not be surprised if I end up in the same situation a month or so from now, or whatever my [memory retention for this thread] + 1 day is...so i'd forget what c3PO was.
Ok, seems a fair exchange of information - Roy helps me, I tell you what happened...
I am working remotely on a customer's development server (which is a VM with very little RAM allocated and running two instances of SQL2008 and two enterprise applications - CRM and ERP).
The connection is at times slow so it is hard to tell when things are going slow because of the remote nature of the work or whether something on the server (or the host server) is affecting my work.
I am the only person using this system right now, but the appplications are "on" and do perform automated tasks.
I noticed during one piece of my work that an update in the CRM application that normally is instantaneous was taking a long time - and the proof that it was at the server end was that it is a web browser interface and I could see the little blue bar was moving, so it was not a screen refresh issue.
So, I fired up Windows Task Manager and saw that the two sql instances were consuming all available cpu between them (~50% each).
I tried Activity Monitor in SSMS, but that kept failing because the machine was going so slow.
I tried SQL Profiler but by the time it had whirred it's wheels into motion, the activity had died down. Being a bit short of time I thought nothing more of it for now.
When it happened again I was quicker on the draw with SQL Profiler (it being still open waiting for me this time) and managed to catch a massive amount of activity - the most obviously abnormal being the SELECT * FROM "C3P0_CONNECTIONTEST" query being executed tens of times per second over what seemed to be a quite long time period.
The entries in SQL Profiler were odd, because they were all coming from a group of SPIDS in the late tens (50-99 roughly) and they had no hostname and no application name.
Of course, my first thought is that there is some malware on the server and this is a symptom of it's probing - but that didn't seem likely, so I got out my good friend Mr Google and asked him what he thought.
He said "huh?" (no results for that exact query) so I tried him on just the table name but didn't see anything that seemed relevant.
I tried checking the spids out using sp_who2 and Mr Machanic's alternative http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx but could not see anything helpful.
At this point, I asked the good people at SSC for help and Bam! Mr Ernest gets it first time. C3P0.
So, I asked Mr Google if he had heard of C3P0 - he had and gave me the address of the project on SourceForge http://sourceforge.net/projects/c3p0/.
A bit of squirrelling around that project site and a bit more googling led me to http://community.jboss.org/wiki/HowToconfiguretheC3P0connectionpool where I learned about the automatic connection testing that C3P0 performs.
Out came the dos command prompt to find me a file with the name "hibernate*" and up pops "hibernate.properties" underneath the tomcat installation folders.
Nothing much of interest in that file, but the folder contained other configuration files.
A quick DOS FIND got me to an xml file that contained settings related to C3P0 and in there I hope are the answers I am looking for.
At the moment, I have asked the software vendor to explain conditions that would lead to this problem. If they don't help (they are not usually too hot at this sort of thing), then I will "Have A Tinker"
By this time, my work is done and I am going to get some lunch - and a coffee (got to think of my liver)...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply