Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ken.hum
Starting Member
3 Posts |
 Posted - 2009-02-02 : 11:52:44
|
Hi everyone; The problem I am experiencing recently is a performance-related one. The flavor of SQL server 2005 is Standard Edition with Service Pack 3 + Cumulative update 1 on Windows server 2003 OS.Our application is accessing the SQL server 2005 DB using the Microsoft SQL Server 2005 JDBC Driver(sqljdbc.jar). The way our test goes is that we have a pool of 24 dedicated connections; the clients lifespan is somehwere from 2 to 10 seconds; the clients continuously get a connection from the pool, use it and put it back in the pool when it's done. Our schema is composed of 4 simple tables linked by col_a = col_b for every table. Every table has over 1 million records. We’ve noticed performance is not an issue for low # of parallel access to DB. But when the connections go higher (such as 24); we experience very high response time (of ~6-7 seconds) from the DB. We have indexes on all columns where the “where” clause is using (which reduce response time from 10 to ~6-7 seconds). Normal we; expect something under 1 second CPL. The sql queries sent by those messages are basically in this form:Select xxx from xxx where xxx = xxxInsert into xxx values xxx;Delete xxx from xxx where xxx = xxxWe are using (almost) the same code on the JDBC side for Oracle DB and we do not see this performance problem for the same database size.Is there any other optimization possible aside from indexes? We have tried using preparedStatement with no better outcome. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-02 : 12:03:25
|
| How many columns are you returning from the select queries? Select * is going to be much more expensive than select cola, colb. You might consider looking into the include clause for your indexes which would give you a covering index and wouldn't need to scan the table. What proportion is your reads to your delete's/inserts? Also describe your RAID and disk configuration, file groups etc? Are temp tables being heavily used? How much RAM is on your DB server and do you have the 3GB switch or AWE enabled?Mike"oh, that monkey is going to pay" |
 |
|
|
ken.hum
Starting Member
3 Posts |
Posted - 2009-02-02 : 13:37:54
|
| Usually only one column will be returned with select queries. Our test is rather unusual using the proportion of about 1:1 insert/delete. I am not too sure what kind of RAID the DB machine is using; all i know is that it got 2x 750 GB 7200k rpm physical disk. How can i know the frequency of use for the temp tables?Our DB host has 16 GB RAM and the 3GB switch is not enabled as i am not sure if the sqlserv process will go over 2 GB. No crash observed though |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-02 : 13:42:27
|
| Is it 32-bit or 64-bit SQL 2005 Standard Edition? |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-02 : 13:50:02
|
| If you're on a 32 bit OS with 16GB of ram then I would strongly reccomend you either enable AWE or the 3GB switch provided SQL is the only thing running on the server. Probably not the root cause of your problem but definitely worth doing regardless and should help. How are these statements being run? Are they dynamic sql or tsql statements or are they being executed via stored procedure. If they're dynamic or tsql run from your front end I would strongly reccomend using stored procedures instead. I forgot to ask if there are any triggers underneath these tables that might be firing as well. I was hoping in the temp table question that you had some familiarity with what was being run but now that I think about it you describe the statements you most commonly use so I doubt that's the issue. Have you looked at the execution plans for these statements for table scans or index scans? The reason for so many questions is because performance problems are a wide ranging collection of possibilities.Mike"oh, that monkey is going to pay" |
|
|
|
ken.hum
Starting Member
3 Posts |
Posted - 2009-02-05 : 16:12:57
|
| Our dev team have update the queries at this point and we saw a 5 fold improvement: changes were:Instead of complex queries with a lot of where and subqueries; queries are updated with a few simple querieseliminate some redundant queries such as select count(*) from tablea |
|
|
|
|
|
|
Sql server 2005 large database performance problem