| Author |
Topic |
|
sqlwebby
Starting Member
4 Posts |
 Posted - 2010-09-22 : 03:20:31
|
| I need some help from anyone who can give me some tips.We had a web app running on WebSphere and it used OptA drivers to connect to SQL Server 2000. However, there was a requirement to migrate to SQL Server 2008 and I ain't allowed to use the OptA drivers anymore.In brief: WebSphere/OptA/SQL Server 2000 --> WebSphere/MS JDBC Drivers/SQL Server 2008The migration was done after running Upgrade Advisor which showed no problems. I find that the execution of the queries is markedly slow (easily perceivable), plus it become dead slow after the system is up for around 7-8 hours.I have never worked with SQL server administration and I don't know where to begin. I don't know why the queries are so slow right now after the migration. Can anyone with knowledge/experience give me some tips/some places to look at?Could it be something to do with the drivers?Thanks for your help. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-09-22 : 06:45:12
|
| Can you take the external environment factors out of the equation and benchmark the queries on inside raw SS2008 itself?you may have a database with poor/no statistics and poor execution plan/cache.timing the queries in SS2008 will allow you to establish if your external environmetn is part fo the problem or if your raw code/database needs work itself.can you post a typical execution plans from the 2008 environment? |
 |
|
|
sqlwebby
Starting Member
4 Posts |
Posted - 2010-09-22 : 07:25:17
|
| Hi Andrew,Thanks for the reply.Sorry if I sound like a newbie, I am indeed so. If I understand your reply right, it means that I should see how long the queries themselves take to execute. So that once I know that (and find that they are indeed taking the same time on both SS2000 and SS2008), then there must be an external reason like network connectivity or something else but not SS 2008 itself. Please correct me if I am wrong.Can you tell me how I can benchmark the queries (or "time the queries") on the SS 2008? I googled and found the following answers:- I can "set statistics on" to see how long queries take to run- I can use an SQL profiler- I can use 'show plan' to see the execution plan of the queriesIs this how I do it? However the app I am talking about has a large number of queries and stored procedures. So I assume seeing the execution plan would be done once I find the queries that are taking a large amount of time and to do that, I have to use the SQL profiler, is it?You also said "you may have a database with poor/no statistics and poor execution plan/cache."What did you mean by "poor cache"? What can I do to remedy this? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-09-22 : 07:41:47
|
| q1. yes.q2. see advice posted here by some other members, especially TKizer and Kristen.you will need to clear cache (dbcc freeproccache)select getdate()run queryselect getdate()subtract the two to find elapsed timeusing statistics on will also help.benchmark one/two key procedures for a start - ask the business users which functions are slowing down the most - unless you already have statistics from Profiler. profiler is more suitable for long-term monitoring of jobs...if you already have a problem, profiler will only assist in profiuling executinos from here onwards...if can't look backwards to what has already run.poor execution plan...read this line as "poor execution plan in cache". this can be caused by an initial execution plan being loaded which is "not-typical" of normal execution and which leads to all other subsequent executions running slower than planned/needs be. clear cache using the dbcc command should nudge SS2008 to behave better.You may also need to use the likes of sp_who and sp_who2 if you have locking issues?This is a long and continual process you are beginning...but you have to start somewhere. |
|
|
|
sqlwebby
Starting Member
4 Posts |
Posted - 2010-09-22 : 10:21:42
|
| Hey Andrew,Thanks again. That sure gives me some pointers.Before I go forth, it looks like at least part of the problem is solved. It had to do with VARCHAR and NVARCHAR. The MS JDBC driver was having problems with NVARCHAR. And hence the setting 'Use ASCII mode' had to be changed. Now it looks like it is running faster (on the test environment). At this point, the business users don't have access to the migrated app.I don't need previous data as every time a new round of test is fired (in a fresh env), after about 8 hours the website was beginning to crawl and from the backend I could see the queries were taking a long time. I guess I still have a lot of figuring out to do because the entire problem isn't solved yet. Now I understand what proper caching meant. I am going to look into TKizer and Kristen's posts too. If I have some doubts, I'll come back to this thread or start a new one. Thanks again for your help! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-09-22 : 11:43:27
|
| If performance is good after a restart and degrades over time, then the cause may be a memory hog/leak. search here for advice on same. posting the spec of the environment may also attract some useful directions. |
|
|
|
sqlwebby
Starting Member
4 Posts |
Posted - 2010-09-22 : 12:14:54
|
| I was thinking it might be a leak too. TKizer has tons of replies, so its taking time to sift through them :) I'll also post the env spec if I can't get this fixed. Thank you. You'll see me again :) |
|
|
|
pgriff
Starting Member
1 Post |
Posted - 2011-07-11 : 15:53:18
|
| To eliminate memory leaks, you might try switching out the JDBC driver and use the DataDirect Connect JDBC driver for SQL Server. Just changing drivers can eliminate memory leaks as well as increase performance. |
|
|
|
|
Slow query after moving to SS 2008, JDBC drivers