Love Your DBA

In our payment systems projects, we work with a number of constituencies at our OLS.Switch client sites.  An incomplete list (arranged in no particular order) consists of:

• The core application team (our everyday contacts)
• The store systems team (we interact with these guys)
• The application's line-of-business owner (the core application team are the stewards, but this is the person who decides features and directions)
• Accounting
• Fraud Control
• Telecommunications
• Data Security
• Systems Administration
• Database Administration

This post concerns DBAs.  I've mentioned before that "a talented DBA is a very important part of a successful jPOS implementation...I know this from experience."  That feeling was validated once again over the past couple of weeks.  I'm thankful that our flagship OLS.Switch client has a super-strong DBA team.  If anything, over the past year they've become an even stronger group, as their manager is re-tooling and expanding his team with MS SQL Server-specific talent.  That's a boon for us, as this client runs OLS.Switch on a Windows Advanced Server 2003/MS SQL Server 2005 configuration.  From this team, we get proactive monitoring of our operating environment as it relates to our consumption of DB resources.  A few weeks back they contacted me with concerns about occasional resource contention lockups in SQL Server.  I'd seen these, too, but was having trouble pinpointing the source, as I don't have the visibility in production that those guys have. 

Turns out, the issue came down to contention in SQL Server's tempDB space.  After getting a full debrief from those guys as to their findings, I did some research and found out that this was an issue encountered by others and with a very clear resolution.  It involves our use of the jTDS driver.  [We use jTDS for our SQL Server connectivity.  As best described on the jTDS Project's web site, it is "an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000 and 2005)."] 

The key dialogue about this problem is something I found on the Hibernate forums.  Alin of the jTDS project provides the key insight: 

Updating jTDS to the latest version will fix the problem. tempdb is used by earlier versions because prepared statements were by default creating temporary stored procedures to "prepare" the query.  jTDS 1.1 uses sp_prepare and sp_execute by default (you can still switch to 3 other modes, read the jTDS FAQ for more detailed information); this doesn't use tempdb and isn't affected by transaction rollbacks.

The key passage of the FAQ is the one that describes the prepareSQL parameter:

prepareSQL (default - 3 for SQL Server, 1 for Sybase)

This parameter specifies the mechanism used for Prepared Statements.

                           

Value	Description
0	SQL is sent to the server each   time without any preparation, literals are inserted in the SQL (slower)
1	Temporary stored procedures are   created for each unique SQL statement and parameter combination (faster)
2	sp_executesql is used (fast)
3	sp_prepare and sp_cursorprepare   are used in conjunction with sp_execute and sp_cursorexecute (faster, SQL Server only)

We came into jTDS before their 1.1 driver, and hadn't made switch from prepareSQL=1 mode to prepareSQL=3.  We made the switch in test and just confirmed in a stress test (we revved the thing up to 130 TPS sustained on a really small box) that we addressed the tempDB contention concerns.  The exact response from the DBA was:  "I am happy with the numbers I am seeing. CPU time is low and the tempdb issues we have in prod are not happening in test."  Woohoo!

So, this coming week, we'll upgrade to a production environment like this:

• JRE 1.5 (we were on 1.4.2, but Sun is EOL-ing that).
• MS SQL Server 2005 SP 2 (our stress test validated that proposed upgrade, too...we were on SP1).
• jTDS 1.2.2 (we were on 1.2)
• jTDS connection.url with prepareSQL=3.