SQL2000 to SQL2005. Query now a lot slower

Question

Hi

This query used to take 3secs in SQL2000, now it takes about 70secs. Both databases give the same results. The 2005 database is not running in compatibility mode.

Currently we're rebuilding the query to run in SQL2005.. by a process of elimination and understanding the logic.

However - can anyone see anything obvious that we've missed.

And/or are there any tools that could help here?

We've been looking at the Execution plan... and profiler. And index tuning wizard.

Profiler points to a massive number more records being queried to get the same results.

I know that this is a very hard question to debug without the data... another pair of eyes is always good if there is anything obvious!

Cheers

Dave

ALTER            PROCEDURE [dbo].[GetNodeList]
@ViewID int,
@UserID int = null
as

Select ProcessList.*, 
A.NDOC_DOC_ID, 
A.NDOC_Order,
A.OMNIBOOK_ID, 
A.Node_Order
from (
    (SELECT N.NOD_ID, 
    N.NOD_Name, 
    N.NOD_Procname, 
    N.NOD_Xpos, 
    N.NOD_Ypos, 
    N.NOD_Zpos,
    VN.VNOD_VIE_ID
    FROM Node N 
    INNER JOIN View_NODe VN 
    ON N.NOD_ID = VN.VNOD_NOD_ID
    Where VN.VNOD_VIE_ID = @ViewID) ProcessList
Left Join
 (
    SELECT N.NOD_ID, 
    N.NOD_Name, 
    N.NOD_Procname, 
    N.NOD_Xpos as NOD_Xpos, 
    N.NOD_Ypos as NOD_Ypos, 
    N.NOD_Zpos as NOD_Zpos, 
    VN.VNOD_VIE_ID, 
    ND.NDOC_DOC_ID as NDOC_DOC_ID, 
    ND.NDOC_Order as NDOC_Order,
    null as OMNIBOOK_ID, 
    null as Node_Order
    FROM Node N 
    INNER JOIN View_NODe VN 
    ON N.NOD_ID = VN.VNOD_NOD_ID
    LEFT JOIN NODe_DOCument ND 
    ON N.NOD_ID = ND.NDOC_NOD_ID
    WHERE VN.VNOD_VIE_ID=@ViewID
    and ND.NDOC_DOC_ID is not null

    and (@UserID is null 
     or exists (Select 1 
       from Document D 
       where Doc_ID = ND.NDOC_DOC_ID 
       and dbo.fn_UserCanSeeDoc(@UserID,D.Doc_ID)<>0
     )
    )

    UNION

    SELECT N.NOD_ID, 
    N.NOD_Name, 
    N.NOD_Procname, 
    N.NOD_Xpos, 
    N.NOD_Ypos, 
    N.NOD_Zpos, 
    VN.VNOD_VIE_ID, 
    null, 
    null,
    NOM.OMNIBOOK_ID, 
    NOM.Node_Order
    FROM Node N 
    INNER JOIN View_NODe VN 
    ON N.NOD_ID = VN.VNOD_NOD_ID
    LEFT JOIN NODe_OMNIBOOK NOM 
    ON N.NOD_ID = NOM.NODE_ID
    WHERE VN.VNOD_VIE_ID=@ViewID
    and NOM.OMNIBOOK_ID is not null
    and exists (select 1 from Omnibook_Doc where OmnibookID = NOM.OMNIBOOK_ID)
) A
--On ProcessList.NOD_ID = A.NOD_ID
ON ProcessList.NOD_Xpos = A.NOD_Xpos
And ProcessList.NOD_Ypos = A.NOD_Ypos
And ProcessList.NOD_Zpos = A.NOD_Zpos
And ProcessList.VNOD_VIE_ID = A.VNOD_VIE_ID
) 
ORDER BY 
ProcessList.NOD_Xpos,
ProcessList.NOD_Zpos,
ProcessList.NOD_Ypos,
Coalesce(A.NDOC_Order,A.Node_Order),
Coalesce(A.NDOC_DOC_ID,A.OMNIBOOK_ID)

Answer 1

Is it possible that your statistics haven't come across? in the 2k5 dbase? So the dbase doesn't have the info needed to make a good plan? As opposed to your old database which has good stats on the table and can choose a better plan for the data?

Answer 2

I've seen this before when the statistics haven't kept up with the data. It's possible in this instance that SQL Server 2005 uses the statistics differently to SQL Server 2000. Try rebuilding your statistics for the tables used in the query; so for each table:

UPDATE STATISTICS <table> WITH FULLSCAN

Yes, I'd add the FULLSCAN unless you know your data well enough to think that a sample of records will give good enough results. It'll slow down the stats creation, but will make it more accurate.

Answer 3

Could it be an issue with "parameter sniffing", i.e. SQL Server caching a query plan optimized for the parameters supplied for the first execution? Microsoft technet has more

Answer 4

A college has come up with a solution... regarding bringing the function fn_UserCanSeeDoc back into the SQL.

Shown below is the old commented out function code, then the new inline SQL below it. The code now runs super quick (from over 1 minute to about a second)

Looking at the old SQL I'm surprised how good a job SQL2000 did of running it!

Cheers

--and dbo.fn_UserCanSeeDoc(@UserID,D.Doc_ID)<>0

-- if exists(Select 1 from Omnibook where Omnibook_ID = @DocID) 
--   Begin
--    Set @ReturnVal =  1
--   End 
--  
--  else
--   Begin
--    if exists(
--     Select 1
--     from UserSecurityModule USM
--     Inner join DocSecurity DS
--     On USM.SecurityModuleID = DS.SecurityModuleID
--     where USM.UserID = @UserID
--     and DS.DocID = @DocID
--    )
--    
--     Set @ReturnVal =  1
--    
--    else
--      
--     Set @ReturnVal = 0
--   End

AND D.Doc_ID IN (select DS.DocID from UserSecurityModule USM
       Inner join DocSecurity DS
       On USM.SecurityModuleID = DS.SecurityModuleID
       where USM.UserID = @UserID)