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 |
|
simonwalton
Starting Member
2 Posts |
 Posted - 2010-11-17 : 11:33:13
|
| First thing I made a huge error with my database, it was moved to sql 2008 R2 from sql 2000 using attachI did this first on a test machine and tested some queries we use to build data files (small sample), worked fine, or so I thought.The sample was too small because after we did the switch and went for a full build the builds are all 50 times slower than on sql 2000.I have read the other threads and gone through changing the database level to 100 and checking the data and rebuilding the indexes and statistics, all to no avail.I have though two different collations on the server and systems tables to our user databaseSo I assummed thsi must be the problemSo I uninstalled sql server and re-installed with the same collation as the database, then re-attached itSame problem, 50 times slower buildsI am now going round in circles and up the wall trying to fix thisI thought I could script the schema, without indexes and stats and script the data direct to script files,then create a new temp database using, run the schema scripts, run the data scripts, create indexes and statistics and bobs yer uncleBUT the data scripts are too large to load back into SSMSAny ideas anyone? please....! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-17 : 13:18:40
|
| Examine a query plan from the old server / version, and one from the new, and see how they are different.Maybe your new SQL installation is limited on hardware in some way - e.g. a setting to limit memory (or its grabbing too much and not leaving enough for the O/S) |
 |
|
|
simonwalton
Starting Member
2 Posts |
Posted - 2010-11-17 : 13:32:22
|
quote:
Originally posted by Kristen
Examine a query plan from the old server / version, and one from the new, and see how they are different.Maybe your new SQL installation is limited on hardware in some way - e.g. a setting to limit memory (or its grabbing too much and not leaving enough for the O/S)
I think I may have found the problemCurrently our applications connect using ADO and OLE DBIf I change the connection to ADO and Natiev Client the speed is back, in part at least |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-17 : 14:50:41
|
I definitely remember reading something about making sure to use the appropriately connection strings for correct ADO/OLE DB versions - and, indeed, when we moved from SQl2000 to SQl2008 we changed the connect strings to reference latest version drivers.www.connectionstrings.com is the bible on connection strings  |
|
|
|
|
|
|
|
|
Problems with SQL 2000 database on SQL 2008 R2