April 20, 2012 at 8:24 am
So I recently migrated a database from a 2000 server to a 2008R2 server. From the 2000 server, a job called a stored procedure which loops through a cursor, joining data in temp tables to permatnent tables in 60 some databases on 6 other servers which are 2008 (not R2). Running from the 2000 server, this job took 4 minutes to complete.
After moving the proc and job over to the 2008R2 server (the database left in 8.0 compatability mode), the job takes ages to complete. After adding some indexes to the temp tables, I'm down to 40 minutes for the job to complete, but still this is way above where I was before.
Is there something different about the way R2 does joins across linked servers?
April 20, 2012 at 8:32 am
To really help would probably need to see some of these queries that are running across linked servers. I am unaware of nay changes to how linked servers work between SQL Server 2000 <-> SQL Server 2005 <-> SQL Server 2008 <-> SQL Server 2008R2.
Part of my guess would be that if you are joining data from remote servers to local tables that you are pulling all the data across the network and the joins are occuring locally.
April 20, 2012 at 10:09 am
I don't think it should be linked server. You added new server in same domain, right?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 23, 2012 at 2:03 pm
The execution plans were the same (which is what had me scratching my head)...
Looks like this was related to a difference in the Provider Options. "Allow inprocess" was checked on the 2000 box, not on the 2008 R2 box. Yet to test it out, but fairly sure that's the issue.
April 24, 2012 at 8:32 am
You may be onto something with the settings for linked server being different. But also make sure you updated all statistics in 2008 database with a FULLSCAN. This is a MANDATORY step after upgrading.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 24, 2012 at 10:07 am
REally- I did not know about that. Is it documented somewhere in BOL that it's mandatory to do an update stats wth fullscan after upgrading to 2008?
April 24, 2012 at 10:28 am
Not sure if it is in BOL, but if you want the SQL Server 2000 statistics updated correctly, then yes, you need to do it with a full scan.
IIRC, this was also needed when migrating from SQL Server 2000 to SQL Server 2005.
April 24, 2012 at 11:28 am
one other noe about the upgrade from SQL 2000 - drop all system generated statistics before performing the update of all statistics. Attached is a code snippet you can use ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 24, 2012 at 11:41 am
realyl need to find something from MS saying to do this... cant say "some well respected posters on SQLServerCentral said so"... I mean, that's good enough for me, but...
April 24, 2012 at 11:45 am
NJ-DBA (4/24/2012)
realyl need to find something from MS saying to do this... cant say "some well respected posters on SQLServerCentral said so"... I mean, that's good enough for me, but...
Search for 'Next Steps'...
http://msdn.microsoft.com/en-us/library/ms144267(v=sql.100).aspx
April 24, 2012 at 11:53 am
I did see that, but "To improve query plans and query performance, we recommend that you update statistics on all databases following upgrade from SQL Server 2000" is not the same as "it is mandatory to udpate all stats with fullscan" and "you should delete all system generated statistics"...
I'm not saying I dont believe that those are good things to do, I just need to be able to justify it to satisfy peer and mgmt review.
April 24, 2012 at 11:53 am
NJ-DBA (4/24/2012)
realyl need to find something from MS saying to do this... cant say "some well respected posters on SQLServerCentral said so"... I mean, that's good enough for me, but...
I haven't found anything specific from Microsoft, but at a previous employer when we upgraded from SQL Server 2000 to SQL Server 2005, the instructions from PeopleSoft were explicit in stating to update statistics with full scan.
Not sure if that helps you or not.
April 24, 2012 at 12:01 pm
I don’t think Microsoft has documented all the best practices in MSDN. Most of the best practices are outcome of user experiences that are scattered on web in forms of blog & forum. You can refer some of those if you Google and IMO there is no harm in giving ‘Update Statistics’ a try (on test environment). Rest is up to you.
April 24, 2012 at 12:04 pm
I would look at Paul Randals blog on SQLSkills.com if you are looking for something more authoritative.
April 24, 2012 at 12:10 pm
Lynn Pettis (4/24/2012)
I would look at Paul Randals blog on SQLSkills.com if you are looking for something more authoritative.
The following still holds good...
realyl need to find something from MS saying to do this... cant say "some well respected posters on
SQLServerCentralSQLSkills said so"... I mean, that's good enough for me, but...
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply