SQL Server Performance Forum – Threads Archive
slow Stored Procedure when converted to 2005
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-9HUArchitecture: 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
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.
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.
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….
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
.. 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.
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
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.
]]>