April 30, 2009 at 8:10 am
Hi
We are moving over from sql2000 to 2005 on many of our production systems and I am coming to terms with the new functionality of Sql Profiler. The way it merges with performance monitor is impressive.
A problem I have though is trying to identify the actual contents of dynamic SQL being run against the server. One of the new systems here is a web based system being developed with a technology called Hibernate. The net result is that when I run traces against this system to identify performance problems all I get in the textdata column of the trace output are lines like this........
exec sp_execute 57,'2009-04-30 00:00:00:000','2009-05-01 00:00:00:000'
exec sp_execute 23,'2007-01-01 00:00:00:000','2009-04-30 23:59:59:000',923
exec sp_execute 22,923,7,'2009-04-30 23:59:59:000'
exec sp_execute 13,1259874.30,1000000.00,1,259874.30,25.99,55
exec sp_execute 12,NULL,413,1,NULL,'2009-03-20 14:12:00:187',55,412
or maybe 10 or 20 lines of ......
exec sp_tables N'PROBABLYNOT',NULL,NULL,N'"''TABLE''"'
exec sp_tables N'PROBABLYNOT',NULL,NULL,N'"''TABLE''"'
exec sp_tables N'PROBABLYNOT',NULL,NULL,N'"''TABLE''"'
exec sp_tables N'PROBABLYNOT',NULL,NULL,N'"''TABLE''"'
exec sp_tables N'PROBABLYNOT',NULL,NULL,N'"''TABLE''"'
These occurance occur with the full scans/sec counter of performance monitor peaking.
Ideally I would like to try and find out whats actually being run here, what tables are referenced, to try and find out what areas of the new system are causing problems. Hsas anyone come across this before, I googled this extensively and have had no joy. The output from the trace doesnt give me anything to go on
any help figuring out this cryptic sql would be appreciated
May 1, 2009 at 7:01 am
We have the same issue from Cognos. There's also a sp_prepare statement running before those queries that you're seeing. That's what sets the value for those integers you're seeing. Look for it in the trace, you should be able to find it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2009 at 12:52 pm
exec sp_tables N'PROBABLYNOT',NULL,NULL,N'"''TABLE''"'
Is coming from Hibernate. You should be able to use some cheaper setting on the Test Connection Query as well as the way in which 3cp0 handles the testing of the connections in the pool.
* Noel
May 4, 2009 at 12:53 pm
by capturing statement completed you maybe able to tell what is being executed.
* Noel
November 17, 2014 at 11:57 am
Hi Noel, can you please let me know how to test connection in cheaper way in Hibernate.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply