SQL Server Performance

slow Stored Procedure when converted to 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by knightprincessp, Jul 19, 2007.

  1. knightprincessp New Member

    I am hoping that someone could help how to diagnose my problem when running the SP in my dev desktop.

    Hardware: IBM ThinkCentre Lenovo MT-M 8808-9HU
    Architecture: AMD64
    RAM: 2GB
    # of CPU: 2
    # of nodes: 1 (dedicated sql server)
    OS: Windows Server 2003 R2 Standard Ed 64-bit
    SQL: SQL Server 2005 SP2

    To prepare for upgrade from 2000 to 2005, i ran the SP in the above machine and the execution was slow. From our 2000 it ran in about 10sec but in the new box it ran 48sec. Indexes are optimized, statistics are updated (auto update is set). From perfMon, % of Processor time is at above 50 whenever the SP is being executed and PRocessor Queue Length is around 7-10. Context switches/sec is between 1200 - 1800. Buffer cache hit ratio is at 99, with no Wait Statistics, nor locks nor Latches. However, the Plan Cache Cache hit ratio is at 63.

    The % Processor Time, Context Switches,and Processor Queue Length must NOT be the problem bec in 2000, they are at pace with that values. The big difference is in Client Statistics result for Client Processing Time. In 2005, i have around 44023 while in 200, its 0.


    the SP is below:
    ALTER PROCEDURE [dbo].[xxxxxxxxxxx] (
    @OrgID Int,
    @IsActiveInt
    )
    AS
    DECLARE @Lists TABLE
    ( [name] varchar(89),
    [PatientID] int,
    [ptContractStatusOrder] int,
    [start] datetime,
    [patientepisodeid] int,
    [PMTRole] int,
    [ProvAffiliationID] int,
    [ContractActualEnd] datetime)

    INSERT INTO @Lists ([name], [PatientID], [patientepisodeid], [PMTRole], [ptContractStatusOrder], [start], [ProvAffiliationID], [ContractActualEnd])
    SELECT DISTINCT
    PMI.PMILNameFName
    ,PMT.PatientID
    ,PC.PatientEpisodeID
    ,PR.PMTRoleID
    ,PtContractStatusOrder= CASE PCS.ContractStatusID
    when -3 then 1 -- Active
    when -2 then 2-- Pending
    when -1 then 3-- In Development
    when -9 then 4-- Hold
    else 5
    END
    ,PMT.Effective
    ,PMT.ProvAffiliationID
    ,PC.ActualEnd
    FROM
    Arch.dbo.PtContractPMT PMT INNER JOIN
    Arch.dbo.ParadigmStaff PS ON PS.ParadigmStaffID = PMT.ParadigmStaffID INNER JOIN
    Arch.dbo.vw_PMI PMI ON PMI.PMIID = PS.PMIID INNER JOIN
    Arch.dbo.PatientContractStatus PCS ON PCS.PatientContractID = PMT.PatientContractID AND PCS.expiration IS NULL INNER JOIN
    Arch.dbo.PMTRole PR ON PR.PMTRoleID = PMT.PMTRoleID AND (PR.PMTRoleID = -1 OR PR.PMTRoleID = -5) INNER JOIN
    Arch.dbo.PatientContract PC ON PMT.PatientContractID = PC.PatientContractID
    WHERE PMT.ProvAffiliationID IS NULL AND
    PMT.[Expiration] IS NULL-- For DCS or FA
    UNION
    SELECT DISTINCT
    PMI.PMILNameFName
    ,PMT.PatientID
    ,PC.PatientEpisodeID
    ,PR.PMTRoleID
    ,PtContractStatusOrder= CASE PCS.ContractStatusID
    when -3 then 1 -- Active
    when -2 then 2-- Pending
    when -1 then 3-- In Development
    when -9 then 4-- Hold
    else 5
    END
    ,PMT.Effective
    ,PMT.ProvAffiliationID
    ,PC.ActualEnd
    FROM
    Arch.dbo.PtContractPMT PMT INNER JOIN
    Arch.dbo.ProvAffiliation PA ON PA.ProvAffiliationID = PMT.ProvAffiliationID INNER JOIN
    Arch.dbo.Provider Prov ON Prov.ProviderID = PA.ProviderID INNER JOIN
    Arch.dbo.vw_PMI PMI ON PMI.PMIID = Prov.PMIID INNER JOIN
    Arch.dbo.PatientContractStatus PCS ON PCS.PatientContractID = PMT.PatientContractIDAND PCS.expiration IS NULL INNER JOIN
    Arch.dbo.PMTRole PR ON PR.PMTRoleID = PMT.PMTRoleID AND PR.PMTRoleID = -2 INNER JOIN
    Arch.dbo.PatientContract PC ON PMT.PatientContractID = PC.PatientContractID
    WHERE PMT.[ParadigmStaffID] IS NULL AND
    PMT.[Expiration] IS NULL-- For NWM
    ORDER BY PMT.PatientID ASC, PR.PMTRoleID ASC, PtContractStatusOrder ASC, PC.ActualEnd DESC, PMT.Effective DESC

    DECLARE @PMIorgidLists TABLE
    ( [pmiorgid] int,
    [patientid] int)

    INSERT INTO @PMIorgidLists ([pmiorgid], [patientid])
    SELECT [Arch].[dbo].[provorg].[pmiorgid], D.patientid
    FROM @Lists D
    inner join Arch.dbo.ProvAffiliation ON D.provaffiliationid = Arch.dbo.ProvAffiliation.provaffiliationid
    inner join Arch.dbo.provorg ON Arch.dbo.provorg.provorgid = Arch.dbo.ProvAffiliation.provorgid
    WHERE D.PMTRole = -2-- -2 is NWM

    ----*
    declare @UserSessionCheckout TABLE
    ([EpisodeID] int,
    [UserLogin] varchar(80),
    [UserFirstName]varchar(25),
    [UserLastName]varchar(35),
    [ActionDate] datetime,
    [FormType] varchar(10)
    )
    INSERT INTO @UserSessionCheckout ([EpisodeID],[UserLogin],[UserFirstName],[UserLastName],[ActionDate],[FormType])
    SELECT [EpisodeID],[UserLogin],[UserFirstName],[UserLastName],[ActionDate],[FormType]
    FROM [CATS].[dbo].[Usersession]
    WHERE [Action] = 'CHECKOUT'
    ----*

    IF @IsActive = 1
    BEGIN
    SELECT DISTINCT
    PatientID= PE.PatientID
    ,EpisodeID= PE.PatientEpisodeID
    ,IWFirstName= PMI.FirstName
    ,IWLastName= PMI.LastName
    ,CarrierName = vEC.CarrierName
    ,NWM= (select TOP 1 name FROM @Lists
    Where PatientEpisodeid= PE.PatientEpisodeID AND PMTRole = -2)-- NWM
    ,DCS= (select TOP 1 name FROM @Lists
    Where PatientEpisodeid= PE.PatientEpisodeID AND PMTRole = -1)-- DCS
    ,CSC = (select TOP 1 name FROM @Lists
    Where PatientEpisodeid= PE.PatientEpisodeID AND PMTRole = -5)-- FA
    ,ReferralDate= PE.Referral
    ,CurrentFormUserLogin= US.UserLogin
    ,CurrentFormUserFirstName= US.UserFirstName
    ,CurrentFormUserLastName= US.UserLastName
    ,ActionDate= US.ActionDate-- Added by RK
    ,ClinicalExists= CASE When C.EpisodeID IS NULL
    Then 0
    Else 1
    END
    ,FormType= US.FormType-- Added by ND
    ,PE.closure
    FROM
    Arch.dbo.ProvOrg PO
    INNER JOIN Arch.dbo.ProvAffiliation PAON PA.ProvOrgID= PO.ProvOrgID
    INNER JOIN dbo.vGetPtContractPMT vPMTON vPMT.ProvAffiliationID= PA.ProvAffiliationID And vPMT.[End] IS NULL
    INNER JOIN Arch.dbo.PatientEpisode PEON PE.PatientEpisodeID= vPMT.PatientEpisodeID
    LEFT JOIN Arch.dbo.Patient PON P.PatientID= PE.PatientID
    LEFT JOIN Arch.dbo.PMI PMION PMI.PMIID= P.PMIID
    LEFT JOIN dbo.Clinical CON C.EpisodeID= PE.PatientEpisodeID
    LEFT JOIN @UserSessionCheckout US ON US.EpisodeID= PE.PatientEpisodeID
    --LEFT JOIN dbo.UserSession USON US.EpisodeID= PE.PatientEpisodeID AND US.[Action] = 'CHECKOUT'
    LEFT JOIN vGetPtEpisodeCarrier vECON vEC.PatientEpisodeID= PE.PatientEpisodeID
    WHERE ((DATEDIFF(day,PE.closure,getdate())<61) OR (PE.Closure IS NULL))
    AND @OrgID= (SELECT TOP 1 D.[pmiorgid] FROM @PMIorgidLists D WHERE D.patientid = vPMT.PatientID)
    ORDER BY
    PMI.LastName,
    PMI.FirstName
    OPTION (MAXDOP 1)
    END -- END of IF @IsActive = 1
    ELSE
    BEGIN
    SELECT DISTINCT
    PatientID= PE.PatientID
    ,EpisodeID= PE.PatientEpisodeID
    ,IWFirstName= PMI.FirstName
    ,IWLastName= PMI.LastName
    ,CarrierName = vEC.CarrierName
    ,NWM= (select TOP 1 name FROM @Lists
    Where PatientEpisodeid= PE.PatientEpisodeID AND PMTRole = -2) -- NWM
    ,DCS= (select TOP 1 name FROM @Lists
    Where PatientEpisodeid= PE.PatientEpisodeID AND PMTRole = -1)-- DCS
    ,CSC = (select TOP 1 name FROM @Lists
    Where PatientEpisodeid= PE.PatientEpisodeID AND PMTRole = -5)-- FA
    ,ReferralDate= PE.Referral
    ,CurrentFormUserLogin= US.UserLogin
    ,CurrentFormUserFirstName= US.UserFirstName
    ,CurrentFormUserLastName= US.UserLastName
    ,ActionDate= US.ActionDate-- Added by RK
    ,ClinicalExists= CASE When C.EpisodeID IS NULL
    Then 0
    Else 1
    END
    ,FormType= US.FormType-- Added by ND
    ,PE.closure
    FROM
    Arch.dbo.ProvOrg PO
    INNER JOIN Arch.dbo.ProvAffiliation PAON PA.ProvOrgID= PO.ProvOrgID
    INNER JOIN dbo.vGetPtContractPMT vPMTON vPMT.ProvAffiliationID= PA.ProvAffiliationID And vPMT.[End] IS NULL
    INNER JOIN Arch.dbo.PatientEpisode PEON PE.PatientEpisodeID= vPMT.PatientEpisodeID
    LEFT JOIN Arch.dbo.Patient PON P.PatientID= PE.PatientID
    LEFT JOIN Arch.dbo.PMI PMION PMI.PMIID= P.PMIID
    LEFT JOIN dbo.Clinical CON C.EpisodeID= PE.PatientEpisodeID
    LEFT JOIN @UserSessionCheckout US ON US.EpisodeID= PE.PatientEpisodeID
    --LEFT JOIN dbo.UserSession USON US.EpisodeID= PE.PatientEpisodeID AND US.[Action] = 'CHECKOUT'
    LEFT JOIN vGetPtEpisodeCarrier vECON vEC.PatientEpisodeID= PE.PatientEpisodeID
    WHERE (PE.Closure IS NOT NULL)
    AND @OrgID= (SELECT TOP 1 D.[pmiorgid] FROM @PMIorgidLists D WHERE D.patientid = vPMT.PatientID)
    ORDER BY
    PMI.LastName,
    PMI.FirstName
    OPTION (MAXDOP 1)

    END -- END of IF @IsActive <> 1


  2. satya Moderator

    Have you cleared the cache to recompile afresh?

    Strange that you are not using dynamic sql queries here that will have similar downtrend in some cases, have you checked the PROFILER trace during the execution as it can get a lot of prepared things, if the procedure are called with EXEC statements rather than the RPC mechanism.

    The best way to improve the cache hit ratio, is to find those queries that pushes things out of the cache, because they unnecessarily scan large tables. Also take help of SQL Server's "showplan" feature can be used to determine whether a query has been auto-parameterized. Various SET options — most of them showplan-related— affect compilation, caching, and reuse of query plans and execution contexts in complex ways, worth checking the SET options inthis case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. knightprincessp New Member

    Thank you for your reply.

    The strange thing is, this is the only SP being run at that time as I have exclusive use of the server. In addition, SQLServer:Wait Statistics are all empty during the execution. What is strange is that, SQL Server Profile shows SP:CacheMiss on the SP followed by a SP:CacheHit with no textdata. Even if i do drop clean buffers and free procedure cache and then recompile the SP, and run it couple of time, the CacheMiss always comes up. Is it really missing in the proc cache? it should be in Procedure Cache already. I have a 2GB RAM on my server and Buffer Cache Hit ratio is at 99.99. The SP is a complete read and I'm not even using temp table but table variables instead.
  4. yorkw New Member

    FWIW - I've recently had to resolve occasional (ie. 4-5 out of 200+) processes which ran much slower on 2005 (after upgrading from 2000) - every one involved a UNION.

    One 'fix' was to use hints forcing 2005 to use the same execution plan as 2000 - this would result in an SP that ran much faster (as expected) on the 2005 platform. Due to the complexity of the execution plans, this was pretty time consuming.

    The second 'fix' I found was to analyze the query with Database Engine Tuning Advisor - then apply most, if not all, of the recommendations. This, again, would produce the expected, significantly faster, results on 2005.

    Both of these 'fixes' are 'brute force' methods....if I had the time, these probably could have been opened as issues with Microsoft....
  5. spirit1 New Member

    are you joining on the columns of different datatypes?
    there were some problems with implicit conversions in joins by sql server that was slowing things down.

    _______________________________________________
    Causing trouble since 1980
    blog:http://weblogs.sqlteam.com/mladenp
  6. satya Moderator

    .. also any chance in increasing the physical memory to 4 gb for handling performance in such circumstances.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. knightprincessp New Member

    Thank you all.

    I have fixed the problem. Looks like what's causing the lag are the TABLE VARIABLES used in the last query. I converted all the TABLE VARIABLES into a TEMPORARY TABLES and it was back to it's speed. I have done load testing to see if there will be disk bottleneck as a result but the performance is optimized to the fullest.

    Using TABLE VARIABLES slowed down the query bec SQL doesn't keep statistics. It work fine in 2000 but not in 2005. Why??? I know that query optimizer works differently in 2005 compared to 2000, but SQL Server 2005 Upgrade Advisor should have caught this.

    Anyway, hope this helps those who will ran into the same problem.

    By the way, using PARAMETRIZATION FORCE didn't work, i believe it is best suited for Ad Hoc queries on multiple concurrent users.

    Thank you all
  8. satya Moderator

    I think Upgrade advisor will not be able to, as the changes you have mentioned is already documented in Books ONline for 2005 it is upto the user to perform such checkout.

    FYi links about parameterization:
    http://sqlserver-qa.net/blogs/t-sql/archive/2007/07/24/1135.aspx
    http://www.sql-server-performance.com/bm_forced_parameterization.asp
    http://www.sql-server-performance.com/bm_new_query_hints.asp

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page