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 |
|
darrenstewart
Starting Member
11 Posts |
 Posted - 2010-05-16 : 05:07:40
|
| Hi all,Our main back office application utilises a SQL database, its a faily complex one with a few thousand tables and stored procs. Last weekend we upgraded from SQL 2000 to SQL 2005 (but running in version compatability 80 mode) and moved to a new database server box. All system testing showed no problems, although this was not particuarly stress tested (about 5-6 users testing).After going live and with about 80 users, throughout the week we have seen some very strange performance, with at some point the database seemingly to run so badly that the business were unable to do many of their standard operations.We can see from the logs and traces that many of the stored procedure calls were timing out (timeout is set to 30 seconds).To try and remedy to issues we have so far re-indexed, ran Update stats, rewritten some of the more complex and reguarly called stored procs, nothing made a difference. On Friday afternoon we changed the way that the application connected to the database (i.e. through a different switch away from our 10.10 1gb network). This seemed to immediately fix the issue with full operations back up and running.However Saturday morning, with only about 7-8 users in the system most operations (which is basically adding insurance policies onto the database) was once again failing. Nothing has changed on the system sinceFriday afternoon where it was performing perfectly.I am now at a complete loss, with my only available option to migrate back off the new hardware and onto the previous box. Has anyone had experience of this happening, got any ideas or things I could try so that I may understand what is going on!Happy to go into a lot more detail if required! Unfortutanely I will not be able to post trace data etc as i'm restricted by data security/compliance laws.Thank you. |
|
|
darrenstewart
Starting Member
11 Posts |
Posted - 2010-05-16 : 05:24:45
|
| Update: I have just seen that we are on the RTM version, and that there are service packs available. Does anyknow know if there any major issues addressed within the service packs that may apply to the situation above? Thanks |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 05:44:04
|
OK, I'll get this off my chest first!Why not use latest Compatibility Mode? You've gone to the trouble of testing that SQL 2005 works, but you if you had used Compatibility 90 for your tests you would have then been able to take advantage of SQL 2005 specific features in future development.I'll go one step further, then I will shut up on the point! Why not go straight to SQL 2008 / Compatibility 100? You still have to do all the same regression tests to make sure the application is going to work with the upgraded SQL version, but then you would be bang up to date and could then forget about it for 5, maybe 10, years (or perhaps 2008 - 2000 = 8 years  )Right, some thoughts:By the by, I have a client who upgraded from SQL 2000 to either 2005 or 2008 (haven't worked for them for several years, but they told me this story recently) and had all sorts of performance problems. I gave them some advice but haven't heard from them since, no idea what the upshot was, but I will ask them in case relevant to your case.Have a look at this post:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230(its for SQL 2008, but all of it applies to SQL2005, but that post is much more thorough and peer-reviewed than the earlier post on this forum about migrating from SQL2000 to SQL2005)It sounds like you have done the Index Rebuild / Update Statistics stuff, but check the suggestions anyway - for example using FULLSCAN.Did you use Upgrade Advisor? If not, and you still have the old database around etc., then that might be worth a try - it might point to something, but I think it needs SQL Profiler logs, which given that your users are no longer on the old database may be hard to simulate.Next up would be to use SQL Profiler to find the slow running queries (particularly at the next slow-down). I would look very carefully at those queries, in particular if you have any JOINs between columns of different datatypes - e.g. MyIntColumn = SomeStringValueOrColumn - and put explicit casts on themHave you moved to new hardware? Is it possible that the data / index files are not using as optimal hardware as you had before? maybe before you had thingsData / Logs / TEMPDB / OS on different disk spindles, and now you have one mega-drive with fantastic specification  but actually its not optimal for SQL?Any other Server Apps running on that server? Memory management changed in SQL2005, and memory may be being paged-out between SQL / Other apps. I would recommend limiting SQL's memory to, say, 2GB less than the installed RAM - possibly give SQL even less if there are other Server Apps to accommodate. (This is a hunch, other learned folk on here take a diametrically opposed view that this will cripple SQL Server; my view is that it is a worthwhile experiment to see if that makes SQL no-slower but in turn frees up CPU/RAM for other apps) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 05:45:13
|
| You definitely want to be on SP1, but probably not the Cumulative Updates (unless you find that one of those has a fix for what you are describing)http://www.sqlteam.com/article/sql-server-versions |
|
|
|
darrenstewart
Starting Member
11 Posts |
Posted - 2010-05-16 : 06:08:29
|
| Kristen - Thanks very much, there's some point in there that I need to think about and test especially regarding Joins, RAM and disk setup.I regards to the bits you wanted to get off your chest - I totally agree, but unfortunately the software (fairly well known insurance back office application) is not 2008 compatible and has serious issues with full 2005 mode hence having to run on 80). Great! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 06:25:40
|
| Can you elaborate a bit on what is not SQL2005/SQl2008 compatible? Just in case that sheds some light on the problem.The vendor says its SQL2005 compatible (in compatibility mode=80)? and that it is NOT SQL2008 compatible (again, in compatibility mode=80)? (or maybe you are making your own tests/migration for other business reasons?)Its just a gut feeling, but I'm curious as to what is considered safe with mode=80 compatibility that isn't with native compatibility. |
|
|
|
darrenstewart
Starting Member
11 Posts |
Posted - 2010-05-16 : 06:36:03
|
| The queries need to be re-written, this is what happens:Error #1: ADO Error #-2147217900 Native Error (4147) SQL State (42000) Description The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. Source Microsoft OLE DB Provider for SQL ServerADO Version: 2.8DBMS Name: Microsoft SQL ServerDBMS Version: 09.00.1399OLE DB Version: 02.70Provider Name: sqloledb.dllProvider Version: 08.10.3959Connection String: Provider=SQLOLEDB.1;Password=******;User ID=******;Initial Catalog=******;Data Source=10.10.0.56;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=P1-******-APP;Use Encryption for Data=False;Tag with column collation when possible=FalsePMDAO VERSION = (2000.0.1)EXE = (D:\Program Files\******\******\Server\SystemDpmdao)Comments = () ****** username: (******) Calling App Name: (bClientManager) SourceID: (1) LanguageID: (1) Connection Pooling/COM+ Mode: (False)Err.No : -2147217900Err.Description : The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.Source : Microsoft OLE DB Provider for SQL Server |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 07:05:22
|
| Ah, OK. I'd forgotten about "*=" outer join syntax.Been deprecated since SQL 2005, and was "Advised against" in SQl2000, surprised that the Vendor hasn't removed them by now.Re: "Provider=SQLOLEDB.1", I wonder if you need to be using a more modern connection?See: http://www.connectionstrings.com/sql-server-2005 |
|
|
|
darrenstewart
Starting Member
11 Posts |
Posted - 2010-05-16 : 07:17:29
|
| Yes, they seem to be about 5 years behind the rest of the industry! I have been informed that a migration of data took place on Friday night, with an extra 10,000 policies being added to the system - however the database really should be able to cope with it. Wonder now whether a reindex tonight may solve the problems...I'm remains to be confusing as to why on Friday afternoon I had all the users working perfectly but on Saturday morning it was falling over with just 5 people logged on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 07:23:16
|
| Sounds like Index/Statistics problems ... but that's a guess.Suggest you get SQL Profiler logging long running queries and see what's in the list.I normally cut & paste then to a SQL Query window, surround with BEGIN TRANSACTION and ROLLBACK so they can't do any harm, and then look at the Query Plan / Logical I/Os. (Ask if you need to know how)If you can do that against SQL2000 copy as well then you could compare the two. |
|
|
|
darrenstewart
Starting Member
11 Posts |
Posted - 2010-05-16 : 07:46:28
|
| Will do. Many thanks for your advice Kristen. |
|
|
|
|
|
|
|
|
Poor/strange performance after 2005 upgrade