Query slowness after migration from sql server 2000 to 2014

Question

I'm not a DBA. I have two servers. One of them runs on sql server 2000, the other on sql server 2014. Both have almost identical hardware (2014 has more RAM). My job is to migrate all databases and activity from the first server to the second one.

To achieve this I made database backups on SQL 2000 and restored them on a server running on SQL 2008. Then made another set of backups and restored them on the 2014 server. You can't restore 2000 backups on a 2014 server - you need an intermediary - in my case it was 2008. Anyway, now that I have all the databases on the 2014 server I began testing some stored procedures and queries. Some queries run as good as on the 2000 server. Unfortunately, there are some that take ages - what takes about 20 seconds on the SQL 2000 server, takes a couple of hours on the 2014 server which is not acceptable. I made several steps to improve the performance:

• I rebuilt/reorganized indexes
• I ran Update Statistics table_name with FULLSCAN
• I ran sp_updatestats
• I ran DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
• I altered between database compatibility levels 2014, 2012 and 2008 (2005 displays an error and there is no option to select 2000)

Nothing worked. Am I missing something? Optimizing the problematic queries is not a real option - there are too many of them - the problem needs to be addressed globally.

Example query which works fine:

select  
MKPF.MANDT,
MKPF.MBLNR,
MKPF.MJAHR,
MSEG.BWART,
MSEG.MATNR,
MSEG.LGORT,
MKPF.BUDAT,
MKPF.CPUDT,
MKPF.CPUTM,
MKPF.AEDAT,
MSEG.SHKZG

from PI2.PKT.pkt.MSEG AS MSEG 
join PI2.PKT.pkt.MKPF AS MKPF 
on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
join PI2.PKT.pkt.MARA MARA
on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
and MARA.spart ='01'

However, when I add a WHERE to the query it takes a couple of hours to finish instead of 20 seconds like on SQL Server 2000:

select  
MKPF.MANDT,
MKPF.MBLNR,
MKPF.MJAHR,
MSEG.BWART,
MSEG.MATNR,
MSEG.LGORT,
MKPF.BUDAT,
MKPF.CPUDT,
MKPF.CPUTM,
MKPF.AEDAT,
MSEG.SHKZG

from PI2.PKT.pkt.MSEG AS MSEG 
join PI2.PKT.pkt.MKPF AS MKPF 
on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
join PI2.PKT.pkt.MARA MARA
on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
and MARA.spart ='01'
WHERE 
MSEG.Mandt='500' and MSEG.MATNR like '00000000%' and 
MKPF.CPUDT between @datefrom and @dateto
AND MSEG.BWART in ('101','561')

FYI @datefrom and @dateto are defined beforehand (I'm sure they don't affect the preformance). PI2 is a linked server. The linked server has the exact same properties on 2014 as it does on 2000.

Any ideas on what I should do? At this point I'm not sure whether it's an issue with the SQL Server 2014 configuration or maybe there's an issue with the linked server (maybe the linked server had a better network connection with the old sql server 2000? )

Answer 1

What version of SQL Server is the PI2 server in this situation? When was the last time the underlying statistics were updated on these tables on the Linked Server? While you updated statistics, it sounds like that was only done on the local database server that you migrated to. Updating statistics on these underlying remote tables may help the optimizer identify a more optimal plan and allow for the entire query to execute remotely.

Another possibility is permissions (though you say that's no different between the old and new servers), four-part (aka distributed) queries require proper permissions to remote tables in order to get an optimal plan. From the Guidelines to Using Distributed Queries page:

To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

Really since all of these tables are on the linked server, I suggest you force the query to execute remotely in it's entirety instead of hoping it will do so with four-part/distributed query syntax. You do this by reworking the query into an EXECUTE AT statement, such as follows:

EXECUTE('select  
        MKPF.MANDT,
        MKPF.MBLNR,
        MKPF.MJAHR,
        MSEG.BWART,
        MSEG.MATNR,
        MSEG.LGORT,
        MKPF.BUDAT,
        MKPF.CPUDT,
        MKPF.CPUTM,
        MKPF.AEDAT,
        MSEG.SHKZG
    from PKT.pkt.MSEG AS MSEG 
    join PKT.pkt.MKPF AS MKPF 
        on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
    join PKT.pkt.MARA MARA
        on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
        and MARA.spart =''01''
    WHERE 
        MSEG.Mandt=''500'' and MSEG.MATNR like ''00000000%'' and 
        MKPF.CPUDT between ? and ?
        AND MSEG.BWART in (''101'',''561'')'
, @datefrom, @dateto) AT PI2

I'm suggesting the EXECUTE AT approach instead of using other LinkedServer syntax such as OPENQUERY or OPENROWSET as this will allow you to still generate the date parameters from data hosted on your local server (as your estimated query plan indicates) and pass them as parameters to the query you wish to run remotely in what will likely be a more optimal fashion.