February 24, 2010 at 10:33 am
The application developed by the company I work for, I'm a junior DB Developer btw, is a Java app that does much of it's data access via the Java Persistence API. I don't particularly care for it, but Java devs don't particularly care to learn how to write good SQL either, so here we are. Lately we have been having problems with connections from the Java app servers and as I am a junior dev and don't know Java, I'm stabbing at the dark. I ran the query below to try and get a look at what's going on:
CREATE TABLE #who
(
spid int,
status varchar(50),
[login] varchar(50),
[hostname] varchar(50),
BlkBy varchar(10),
DBName varchar(50),
Command varchar(50),
CPUTime int,
DiskIO int,
Last_batch varchar(50),
ProgramName varchar(200),
spid2 int,
requestid int
)
INSERT INTO #who
EXEC sp_who2
SELECT sp.*, st.*
FROM #who w
JOIN sysprocesses sp with(nolock) ON w.spid = sp.spid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) as st
WHERE left(w.hostname, 5) = 'javaserver'
ORDER BY w.diskio desc
DROP TABLE #who
Our dba pointed me at the sys.dm_exec_sql_text().
In the results I see a lot of spids running select @@TEXTSIZE that also have high diskIO. Now, I know there must be more to this than just select @@TEXTSIZE. Is there a way to see what else a spid might have run during it's session that would lead to such high IO? I can't really set up a profiler trace at this time to dig through.
Alternately, has anyone had to deal with Java Persistence before? Do you have some trouble shooting tips on that piece of ... you know what?
February 24, 2010 at 4:31 pm
If you want to see what a SPID is doing, then Profiler/trace is how to do it.
Sorry, but I do not have any Java Persistence API experience. And yes, I do agree that "SELECT @@TEXTROW" doesn't really explain anything by itself.
I really think that you are going to need to use Profiler for this. Without it, you (and anyone of us) really are just stabbing in the dark.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 25, 2010 at 7:00 am
Thanks for the reply. I figured Profiler was going to be the answer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply