0

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? )

  • Which version is the linked server, does the user that crosses the link have dbo permission on the pkt database (thomaslarock.com/2013/05/…) – Spörri Oct 13 '16 at 10:06
  • Do SAP support your version of SAP on SQL Server 2014? – Philᵀᴹ Oct 13 '16 at 10:13
  • The user has adequate permissions, otherwise the first query (the one without WHERE) wouldn't work. The same goes for SAP. If it was not supported the first query wouldn't work. – DanteeChaos Oct 13 '16 at 10:18
  • try your query with OPTION(QUERYTRACEON 9481) as the last line. This is the same as changing compatibility level but possibly the plans did not get invalidated when that change was made. – Bob Klimes Oct 13 '16 at 14:05
  • Unfortunately, the result is still the same - hours of waiting. – DanteeChaos Oct 13 '16 at 14:18
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.

  • 1
    Also, if you're talking to a SQL 2000 instance, to even get the remote statistics to help out the optimizer you need sa, db_ddladmin or db_owner on the destination server. I believe this behavior was changed in SQL 2012 SP1 where only db_datareader is required to fetch remote statistics. – Kris Gruttemeyer Oct 13 '16 at 15:56
  • PI2 runs on SQL server 2008. Servers 2014 and 2000 are linked through different users to PI2. Those users, however, have the exact same permissions (public + read/write) - adding more permissions to my user won't fix the problem since the user on the server 2000 doesn't have them as well. The query you posted works great - unfortunately it doesn't solve my problem - I can't edit every single stored procedure like this. And yes, I only updated statistics on my local server. I'll try to update them on PI2 as soon as the admins let me. – DanteeChaos Oct 14 '16 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.