September 23, 2015 at 2:35 pm
I just moved a database from SQL 2000 to SQL 2012. In the database, I have a stored procedure that pulls data from a 2008R2 server using a linked server.
On the 2000 server, the stored procedure runs in about seven minutes. On the 2012 server, the stored procedure takes two hours to run.
I can't figure out why it is taking so long to run on the 2012 server. Any suggestions as to what to look for?
Thanks in advance for any help that you can provide.
September 23, 2015 at 4:30 pm
Assuming you have the properties of the Linked Server matched on the old and new servers, it could be down to...
1. Permissions - the login that is used to access the remote server must have permission to retrieve statistics in order to build a decent plan.
2. Collation conflict - have you kept the same collation when you moved the database over and does your tempdb have the same collation on old an d new? If you have any cross server joins, make sure the collation matches on the columns used to make the join.
3. Different optimisations - 2012 may be producing a different execution plan - compare them if you can.
Can you give us some idea of the query you are using?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 23, 2015 at 5:09 pm
Are local tables involved too? Is so may need to update statistics after migration before query plans will be optimal.
September 24, 2015 at 10:11 am
Hi mister.magoo Kristen-173977,
Thanks for the suggestions. I checked permissions and everything is okay there. Collation is the same on old and new server, include the column collations. Local DB tables are involved in the query as well. Statistics are updated nightly and I even manually updated all the stats on the server.
I ran the query a few times after making all the corrections and it took less than two minutes to run. Now when I run it again, it is taking over 30 minutes and I had to kill it each time. Trying to figure out what is going on here.
Thanks,
MC.
September 24, 2015 at 10:28 am
mceventphoto (9/24/2015)
Hi mister.magoo Kristen-173977,Thanks for the suggestions. I checked permissions and everything is okay there. Collation is the same on old and new server, include the column collations. Local DB tables are involved in the query as well. Statistics are updated nightly and I even manually updated all the stats on the server.
I ran the query a few times after making all the corrections and it took less than two minutes to run. Now when I run it again, it is taking over 30 minutes and I had to kill it each time. Trying to figure out what is going on here.
Thanks,
MC.
Can you post the actual execution plan?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 24, 2015 at 10:52 am
Ran it again and it took 00:01.29 to complete. There is a few warning and table scan in the plan.
Type conversion in expression (CONVERT(char(8),[Data_04].[dbo].[Rpt_FiscalCalendar].[BPCSSDate],0)) may affect "CardinalityEstimate" in query plan choice,
Actual execution plan attached. This will be a good learning experience for me.
Thanks,
MC
September 24, 2015 at 11:12 am
Are you using distributed queries, that is a select statement that joins between the local and remote server using four part naming convention?
Or are you using pass-through queries, that is a select statement that runs entirely on the remote server using EXEC() AT <linked_server_name> or OPENQUERY(<linked_server_name>) and then returning result into a temp table for joining with local table(s) ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 24, 2015 at 11:23 am
I am using distributed queries, that is a select statement that joins between the local and remote server using four part naming convention.
September 24, 2015 at 4:28 pm
One thing that is easy to fix is the WHERE clause from the last remote query, the one in this section:
-------------------------Warehouse Adjustments and AM Pickups 5/29/2014 Sue Klotz
You have this:
WHERE (CONVERT(char(8),SV.STL_DELIV_DATE,112) BETWEEN @CurrToDate_pSDt AND @CurrToDate_pSDt7 )
AND (LEFT(SV.STL_DELIVROUTE_NUMBER, 3) = '004') AND
(SV.STL_LOAD_ADJWH_C <> 0 or
SV.STL_LOAD_ADJAM_C <> 0 )
Which is preventing good row count estimates, which are multiplying out higher up the tree.
The filter on STL_DELIVROUTE_NUMBER is not remoting, so maybe try using LIKE instead of substring...
Try not to CONVERT columns in the WHERE clause like that. Are those variables declared as date:-D/datetime:-) or char:crazy: ?
Maybe try this:
WHERE SV.STL_DELIV_DATE>@CurrToDate_pSDt AND SV.STL_DELIV_DATE<dateadd(day, 1, @CurrToDate_pSDt7)
AND (SV.STL_DELIVROUTE_NUMBER LIKE '004%') AND
(SV.STL_LOAD_ADJWH_C <> 0 or
SV.STL_LOAD_ADJAM_C <> 0 )
AS to why this is slower than the old server, who knows? If you can get an actual plan from the old server as well to compare, we may find out.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 25, 2015 at 8:35 am
There are hints that can force joins to occur locally or remotely, but the way I've worked around this issue in the past is to pull the data I need using a pass-through query from the remote database into a local temp table, and then joining that with my local tables.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply