September 28, 2006 at 1:45 pm
The setup: SQL Server 2000 sp4, 2 CPU box with hyperthreading, 4G memory, more than enough free hard drive space. The application is written in Java, and uses Hibernate, c3p0, jTDS, JNDI, and I'm not sure what else.
The situation: stress testing, having our app do a routine set of calls, and increasing the number of users until something happens. Currently, the "something happens" is the application ceases to function at a certain ponit of volume.
The symptoms:
- Profiler shows that the vast majority of submitted work is sp_execute calls for previsoulys sp_prepared queries.
- Profiler also shows a very large number of "Lock:Timeout" events the minutes leading up to the application crash, in database id 2 (tempdb) for object id 0 (???).
- Connection Timeout settings are [unless it's hidden very well] totally untouched, meaning they should be "don't timeout".
So what does it mean to get a flood of lock timeouts on tempdb when temp tables are apparently not being used, on object ID 0 which does not actually exist?
Philip
September 28, 2006 at 2:17 pm
Lock timeouts are usually the result of blocking processes. Object ID of zero is the database object so if you are getting lock timeouts on your tempDB object 0, it is a database level lock timeout. You say that temp tables are apparently not being used, but the tempDB is used for much more than storing temp tables (storing intermediate query results for one). I would focus on looking at your tempDB and possible contention issues there. Make sure your TempDB is on it's own physical set of disks for starters.
September 28, 2006 at 9:12 pm
Thanks for the info, this points me in the right direction.
One question, where did you learn that "Object ID of zero is the database object"? I hit BOL, Google, and MS Knowledgebase fairly hard, but found nothing that said this. The more places I know to find obscure info like this, the better prepared I am...
Philip
September 29, 2006 at 12:16 am
get the creation of temporary tables out of the transaction if you can !
So if possible, create your #tmptbl(s) and then begin tran ... do your stuff ... commit/rollback and cleanup #tmptbl(s).
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
September 29, 2006 at 8:10 am
Definitely. Alas, I am just the Operations DBA, and have no control over what the application's actually doing in there. By and large, neither do the developers; Hibernate is an object-relational mapping utility, wherein you define and manipulate objects and the utility creates the code used to query the database. You would not believe some of the cruft I've seen come out of there... but I've never seen it generate temp table work. Loads of sp_prepare and sp_execute, however.
Philip
October 1, 2006 at 1:04 am
capture the query-load and examine it !
You may need to add some indexes, expecialy to support group by, order by operations ! as well as joins !
This way you may take away the pressure on tempdb.
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
October 13, 2006 at 9:52 am
I am a dba that has been hit with a new Hibernate app that has been hogging memory and causing locking/blocking (easy to notice when the SQL Enterprise Mngr Current activities coughs up Timeout exceeded errors) .
Hibernate seems to be parsing SQL from the app into TEMPORARY store procs and executing them. Perfect for the Java kids that dont get SQL. It is locking up the temp DB for this but why.... Is there a config setting for the Hibernate jTDS application connection? Look for #jdbc#* objects holding Table Schema locks.
The programmer downloaded this to "speed up development" ; yeaa shure.....
October 13, 2006 at 9:45 pm
Schema locks? Temporary stored procedures? I'll have to look for those--though I've never had much luck in detecting the precence of other logins' #temp objects in tempdb. I figured that yes, there was some kind of temp work going on, but it was by and large acceptable given our operating requirements, and anyway focus is on getting assorted java database connectivity tools to play nice on the app (not database) servers, so I haven't worried about this situation much since my initial analysis.
I am not familiar with the myriad configuration settings of Hibernate, C3P0, and all those guys. Partly because the developers have laid claim to them, and partly because it's hard finding thorough documentation. (Compare and contrast this with the size of SQL 2005 BOL, let alone the comptuer books section in the average mall bookstore. Ya get whats ya pay for.) There are a ton of configuration settings, and what you or I would want is probably in there somewhere, but it'd take serious effort to discover and become proficient in it.
Do you really have Hibernate and the java apps all installed on the database server? That will slow you down for all the usual reasons (sharing memory and CPU). You should definitely get a dedicated application server or two for them, or performance will never soar.
And yes, Hibernate will speed up development. But if you're business is concerned with performance in the Production environment, you better do some serious testing and proof-of-concept to justify addign it. (To be fair, speed of development may be more important that speed of Production operations. Sad but true: most times, software doesn't have to be good, it only needs to be good enough.)
Philip
October 18, 2006 at 3:51 pm
RE: "I am a dba that has been hit with a new Hibernate app that has been hogging memory and causing locking/blocking (easy to notice when the SQL Enterprise Mngr Current activities coughs up Timeout exceeded errors) ."
Found the problem! The Java app had a JDBC Conn pool defined AND a connection called jTDS was created by Hibernate. FIX: delete the jTDS connection and have Hibernate use the existing connection pool. Caught the Temp SPs in the profiler. Java app is on Appserver (with other behaving java apps).
Hibernate seems to be a thin edge of the wedge of Object Oriented databases/tools that are appearing. Cute when Marketing drives technology, isnt it?
John
September 24, 2015 at 7:23 am
Hibernate, nHibernate, Entity Framework and Lynq are just a few of the Object Relational Mapping tools you're going to see more of. They actually can work very well for most queries (around 95-98%). However, there are things to watch for. First, make sure they're using the appropriate data lengths in their prepared statements. You'll frequently see VARCHAR(3) for 'Dog' and VARCHAR(5) for 'Horse' when the column is actually VARCHAR(50). This results in a bunch of execution plans that are compiled but not reused. Also watch for the N+1 problem. It's very common. There are other issues you might hit from these tools as well. Just work with the Devs on addressing them. Trying to stop the use of these tools is a fools errand. Go in with a "let me help this all work better" attitude.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply