Trace discrepancies
-
Hello Folks,
I'm chasing my tail. I have a procedure thats been running well for 6 months or so... suddenly its jumped from 30 second runtime to over 3 or 4 minutes. I've run traces against the proc, and have consistentyly been getting results that suggest that its not the SQL SERVER thats slowing me down, but I have no way to prove that. My trace shows 103 SP:StmtCompleted events with a total duration of 2149ms and a total CPU of 2212ms. where else should I be looking? I'm open to suggestions.
Thanks in advance!
Patrick
-
What is the total duration time, cpu and read time for entire sp?
-
allen,
the totals are....
Duration 2155
CPU 2275
Reads 3388
Writes 33
-
"suddenly its jumped from 30 second runtime to over 3 or 4 minutes. "
"Duration 2155
CPU 2275
Reads 3388
Writes 33"
I am confused.
-
Allen,
So am I!
the confusion comes from the fact that the trace says its taking seconds to run and the actual results... even in QA take several minutes. I am correct in assuming that the times in the trace are in milliseconds right?
if the trace is reporting the time the proc spends on the server, can i assume the lost time is transit time? I'm at a loss to explain where the bottleneck is on this query.
The other thing thats got me scratching myhead is that its just this query... none o fthe other 3 million transactions are slowing down (not that I could tell unless someone complained... this one is still in params as far as the server is concerend)
-
How many records the query return? Turn on statistics IO and statistics time in QA and post back the execution result.
And try "set nocount on" before executingg the sp.
-
allen,
here are the results.. the final portion of the SP returns 333 rows.
Application Profile Statistics
Timer resolution (milliseconds) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 2 0.666667
Rows effected by SELECT statements 666 222
Number of user transactions 6 2.66667
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0
Network Statistics
Number of server roundtrips 2 1.33333
Number of TDS packets sent 2 1.33333
Number of TDS packets received 134 45.3333
Number of bytes sent 136 101.333
Number of bytes received 264311 88153.3
Time Statistics
Cumulative client processing time 203 68
Cumulative wait time on server replies 108062 36020.7
the Trace looks like...
set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0 select IS_SRVROLEMEMBER ('sysadmin') SQL:StmtCompleted 0 0 0 0 set nocount on SQL:StmtCompleted 0 0 0 0 select @NumberOfDays =120 SP:StmtCompleted 0 0 0 0 if Upper(@getnew) = 'Y' SP:StmtCompleted 0 0 0 0 if @ProductType = 'EPL' SP:StmtCompleted 0 0 0 0 if @ProductType = 'IAE' SP:StmtCompleted 0 0 0 0 if @ProductType = 'APR' SP:StmtCompleted 0 0 0 0 if @ProductType = 'MP ' SP:StmtCompleted 0 0 0 0 if @ProductType = 'PM ' SP:StmtCompleted 0 0 0 0 if @ProductType = 'TK ' SP:StmtCompleted 0 0 0 0 if @ProductType = 'CD ' SP:StmtCompleted 0 0 0 0 if @ProductType = 'SP ' SP:StmtCompleted 0 0 0 0 if @ProductType = 'NDO' SP:StmtCompleted 0 0 0 0 if @ProductType = 'PCL' SP:StmtCompleted 0 0 0 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 INSERT INTO rn_tblRenewals (Stage, PolicyNumber, ExpirationDate, Admitted, State, Insured, CustomerID, ContactID, insco, buscode, OrigPemium) SELECT distinct -9999,UPPER(PUPolicy.PolicyNumber), PUPolicy.ExpirationDate, 0, SP:StmtCompleted 227 10 232 76 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Update rn_tblRenewals Set AutoRenew = 'N', Flags = lTrim(coalesce(Flags,'') + '; ' +'Farm') FROM RemoteServer.PersonalUmbrella.dbo.PUPolicy PUPolicy INNER JOIN RemoteServer.PersonalUmbrella.dbo.PUQuo SP:StmtCompleted 94 62 222 1 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Update rn_tblRenewals Set AutoRenew = 'N', Flags = lTrim(coalesce(Flags,'') + '; ' +'Driving') FROM RemoteServer.PersonalUmbrella.dbo.PUPolicy PUPolicy INNER JOIN RemoteServer.PersonalUmbrella.dbo.PU SP:StmtCompleted 68 57 249 3 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 UPDATE rn_tblRenewals SET AutoRenew = 'N', Flags = lTrim(coalesce(Flags,'') + '; ' +'Endts') FROM dbo.rn_tblRenewals a Left Outer Join RemoteServer.PersonalUmbrella.DBO.PUPolicy b on a.Policynumber = b.PolicyNumber Left outer join SP:StmtCompleted 136 156 157 4 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Update rn_tblRenewals Set AutoRenew = 'N', Flags = lTrim(coalesce(Flags,'') + '; ' +'WtrCrft') FROM RemoteServer.PersonalUmbrella.dbo.PUPolicy PUPolicy INNER JOIN RemoteServer.PersonalUmbrella.dbo.PUQuote PUQuote ON PUPolicy.Qu SP:StmtCompleted 76 157 63 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Update rn_tblRenewals Set AutoRenew = 'N', Flags = lTrim(coalesce(Flags,'') + '; ' +'Limit') FROM RemoteServer.PersonalUmbrella.dbo.PUPolicy PUPolicy INNER JOIN RemoteServer.PersonalUmbrella.dbo.PUQuote PUQuote ON PUPolicy.Quot SP:StmtCompleted 32 78 204 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 update rn_tblRenewals Set AutoRenew = 'N', Flags = lTrim(coalesce(Flags,'') + '; ' +'CustCancel') -- Select CustomerID,StatusID,* from dbo.rn_tblRenewals left outer join enterprise.dbo.CustStatus on AgentID=CustomerID SP:StmtCompleted 62 62 82 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Update rn_tblRenewals Set Flags = lTrim(Substring(coalesce(Flags,''),2,999)) where stage = -9999 and Left(lTrim(Flags),1) = ';' SP:StmtCompleted 63 47 184 8 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 32 0 0 0 Delete rn_tblRenewals from rn_tblRenewals inner join rn_tblRenewalslog on rn_tblRenewals.PolicyNumber = rn_tblRenewalslog.PolicyNumber where rn_tblRenewals.stage in (9999,-9999) and rn_tblRenewals.policyNumber like 'PCL%' SP:StmtCompleted 125 78 20 4 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Delete rn_tblRenewals --select rn_tblRenewalslog.* from rn_tblRenewals inner join rn_tblRenewalslog on rn_tblRenewals.PolicyNumber = rn_tblRenewalslog.PolicyNumber where rn_tblRenewals.stage in (0) and rn_tblRenewalslog.r SP:StmtCompleted 78 78 38 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Update rn_tblRenewals set stage =0 where stage = -9999 -- Remove rows marked for removal SP:StmtCompleted 0 0 35 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Delete rn_tblRenewals where [Remove] = 1 -- Remove rows that where cancelled SP:StmtCompleted 0 0 8 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Delete rn_tblRenewals where Flags like '%CANCELLED%' -- remove if Status = Cancelled, void, etc -- select PolicyStatus, * from PersonalUmbrella.dbo.PUPolicy SP:StmtCompleted 47 47 60 0 SELECT statman([PolicyNumber],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [PolicyNumber]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 32 31 116 0 SELECT statman([PolicyNumber],[ExpirationDate],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ExpirationDate] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [PolicyNumber],[ExpirationDate]) AS _MS_UPDS SP:StmtCompleted 47 47 166 0 SELECT statman([PolicyNumber],[Stage],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[Stage] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[Stage]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPT SP:StmtCompleted 47 47 34 0 SELECT statman([PolicyNumber],[Remove],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[Remove] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[Remove]) AS _MS_UPDSTATS_TBL OPTION (BYPASS SP:StmtCompleted 47 47 34 0 SELECT statman([PolicyNumber],[Flags],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[Flags] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[Flags]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPT SP:StmtCompleted 31 31 34 0 SELECT statman([PolicyNumber],[CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[CustomerID]) AS _MS_UPDSTATS_TBL OPT SP:StmtCompleted 47 47 34 0 SELECT statman([PolicyNumber],[ExpirationDate],[Stage],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ExpirationDate],[Stage] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[ExpirationDa SP:StmtCompleted 62 62 34 0 SELECT statman([PolicyNumber],[ExpirationDate],[CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ExpirationDate],[CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[Ex SP:StmtCompleted 63 63 34 0 SELECT statman([PolicyNumber],[ExpirationDate],[ID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ExpirationDate],[ID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[ExpirationDate],,[ExpirationDate],[Flags],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ExpirationDate],[Flags] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[ExpirationDa SP:StmtCompleted 63 63 34 0 SELECT statman([PolicyNumber],[ExpirationDate],[Remove],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ExpirationDate],[Remove] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [PolicyNumber],[Expiration SP:StmtCompleted 46 47 34 0 SELECT statman([PolicyNumber],[ID],[Remove],[Flags],[Stage],[CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ID],[Remove],[Flags],[Stage],[CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) O SP:StmtCompleted 79 78 34 0 SELECT statman([PolicyNumber],[ID],[ExpirationDate],[Remove],[Flags],[Stage],[CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [PolicyNumber],[ID],[ExpirationDate],[Remove],[Flags],[Stage],[CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITT SP:StmtCompleted 63 62 34 0 SELECT statman([Flags],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [Flags] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [Flags]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 15 15 51 0 SELECT statman([Flags],[ID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [Flags],[ID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [Flags],[ID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 47 31 34 0 SELECT statman([Flags],[PolicyNumber],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [Flags],[PolicyNumber] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [Flags],[PolicyNumber]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPT SP:StmtCompleted 78 78 34 0 SELECT statman([Flags],[Stage],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [Flags],[Stage] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [Flags],[Stage]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP SP:StmtCompleted 47 47 34 0 SELECT statman([Flags],[ExpirationDate],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [Flags],[ExpirationDate] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [Flags],[ExpirationDate]) AS _MS_UPDSTATS_TBL OPTION (BYPA SP:StmtCompleted 78 47 34 0 SELECT statman([Flags],[CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [Flags],[CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [Flags],[CustomerID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER SP:StmtCompleted 62 47 34 0 SELECT statman([Flags],[Remove],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [Flags],[Remove] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [Flags],[Remove]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXD SP:StmtCompleted 47 47 34 0 SELECT statman([CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [CustomerID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 16 15 48 0 SELECT statman([CustomerID],[ID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CustomerID],[ID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [CustomerID],[ID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, M SP:StmtCompleted 47 47 34 0 SELECT statman([CustomerID],[Flags],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CustomerID],[Flags] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [CustomerID],[Flags]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER SP:StmtCompleted 47 47 34 0 SELECT statman([CustomerID],[Remove],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CustomerID],[Remove] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [CustomerID],[Remove]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMI SP:StmtCompleted 31 31 34 0 SELECT statman([CustomerID],[PolicyNumber],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CustomerID],[PolicyNumber] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [CustomerID],[PolicyNumber]) AS _MS_UPDSTATS_TBL OPT SP:StmtCompleted 32 31 34 0 SELECT statman([CustomerID],[ExpirationDate],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CustomerID],[ExpirationDate] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [CustomerID],[ExpirationDate]) AS _MS_UPDSTATS_T SP:StmtCompleted 16 15 34 0 SELECT statman([CustomerID],[Stage],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CustomerID],[Stage] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [CustomerID],[Stage]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER SP:StmtCompleted 31 32 34 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 delete rn_tblRenewals -- select top 10 PolicyStatus, * FROM RemoteServer.PersonalUmbrella.dbo.PUPolicy PUPolicy where ( PolicyStatus Like '%C,%' or PolicyStatus like '%N,%'or PolicyStatus like '%V,%'or PolicyStatus li SP:StmtCompleted 142 50 180 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 delete rn_tblRenewals -- select binderdate,boundby,PUPolicy.* from rn_tblRenewals inner join RemoteServer.PersonalUmbrella.dbo.PUPolicy PUPolicy ON PUPolicy.RenewalnUMBER = dbo.rn_tblRenewals.PolicyNumber WHERE rn_tblRenewa SP:StmtCompleted 35 10 82 12 exec sp_GetNewPCLRenewals SP:StmtCompleted 20 152 166 142 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Delete rn_tblRenewals where [Remove] = 1 and stage= 0 --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% EPL %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% SP:StmtCompleted 15 15 109 0 if UPPER(@ProductType) = 'EPL' SP:StmtCompleted 0 0 0 0 if UPPER(@ProductType) = 'NDO' SP:StmtCompleted 0 0 0 0 if UPPER(@ProductType) = 'SP ' SP:StmtCompleted 0 0 0 0 <TD style="BORDER-RIGHT: #eae6dd; BORDER-TOP: #eae6dd; BORDER-LEFT: #eae6dd; BORDER-BOTTOM: #eae6dd; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="if UPPER(@ProductType) 'PCL' ">if UPPER(@ProductType) <> 'PCL' SP:StmtCompleted 0 0 0 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 delete rn_tblRenewals FROM Enterprise..vw_PolicyFlags where ( -- Flag Like '%CANCEL%' or Flag like '%NONRENEW%'or Flag like '%VOID%'or Flag like '%Exten%' ) and rn_tblRenewals.PolicyNumber =Enterprise..vw_Pol SP:StmtCompleted 88 88 213 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 Delete rn_tblRenewals FROM Professional2.dbo.pro_tSolicitedBy , rn_tblRenewals where rn_tblRenewals.PolicyNumber = Professional2.dbo.pro_tSolicitedBy.PolicyNumber and (Professional2.dbo.pro_tSolicitedBy.SolicitedB SP:StmtCompleted 104 104 65 45 exec sp_RemoveUnWanteds @RunDate, @ProductType SP:StmtCompleted 207 207 172 45 SET @counter = 0 --************************************************************************************* --New code to assign Underwriter by classCode SP:StmtCompleted 0 0 0 0 SELECT statman([BeenOnReport],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [BeenOnReport] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [BeenOnReport]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 31 31 34 0 SELECT statman([AssignedTo],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [AssignedTo] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [AssignedTo]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 32 32 34 0 SELECT statman([BusCode],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [BusCode] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [BusCode]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 32 32 34 0 SELECT statman([ID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [ID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 16 16 54 0 SELECT statman([ID],[ExpirationDate],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ID],[ExpirationDate] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [ID],[ExpirationDate]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMI SP:StmtCompleted 31 31 34 0 SELECT statman([ID],[Remove],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ID],[Remove] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [ID],[Remove]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 32 31 34 0 SELECT statman([ID],[CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ID],[CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [ID],[CustomerID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, M SP:StmtCompleted 31 31 34 0 SELECT statman([ID],[Flags],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ID],[Flags] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [ID],[Flags]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 31 32 34 0 SELECT statman([ID],[Stage],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ID],[Stage] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) ORDER BY [ID],[Stage]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) SP:StmtCompleted 31 32 34 0 SELECT statman([ID],[PolicyNumber],[Remove],[Flags],[Stage],[CustomerID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ID],[PolicyNumber],[Remove],[Flags],[Stage],[CustomerID] FROM [dbo].[rn_tblRenewals] WITH(READUNCOMMITTED,SAMPLE 4.000000e+001 PERCENT) O SP:StmtCompleted 63 62 34 0 CREATE TRIGGER rn_tblRenewalsUpDel ON dbo.rn_tblRenewals FOR UPDATE , delete,insert AS SP:StmtCompleted 0 0 0 0 insert into rn_tblRenewalsLog ( [OldID], [PolicyNumber], [Insured], [ExpirationDate], [AutoRenew], [Remove], [SystemReview], [ManualReview], [Flags], [Claims], [Transactions], [State], [Admitted], [InsCo], [Stage], [Rene SP:StmtCompleted 0 0 0 0 update rn_tblrenewals set rn_tblrenewals.assignedto = rn_tblUWSelector.UWName from rn_tblrenewals, rn_tblUWSelector where policynumber like (@productType+'%' SP:StmtCompleted 62 63 80 0 0 ">Select @UWCount=count(distinct UWName) from rn_tblUWSelector where productType = @ProductType and ClassCode is null and percentage >0 SP:StmtCompleted 0 0 10 0 SET NOCOUNT ON SP:StmtCompleted 0 0 0 0 <TD style="BORDER-RIGHT: #eae6dd; BORDER-TOP: #eae6dd; BORDER-LEFT: #eae6dd; BORDER-BOTTOM: #eae6dd; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="SELECT @PolicyCount=count(*) FROM rn_tblrenewals where Len(Flags) 0 and BeenOnReport is null and policyNumber like (rtrim(@ProductType)+'%') and Assignedto is Null ">SELECT @PolicyCount=count(*) FROM rn_tblrenewals where Len(Flags) <> 0 and BeenOnReport is null and policyNumber like (rtrim(@ProductType)+'%') and Assignedto is Null SP:StmtCompleted 47 46 14 0 SELECT @sum = sum(percentage) FROM rn_tblUWSelector WHERE producttype= @ProductType AND ClassCode is Null SP:StmtCompleted 0 0 10 0 SELECT DISTINCT UWname, ROUND((@policycount*percentage)/@sum,0)+1 as HighRange into #UserPercent from rn_tblUWSelector where productType = @ProductType and ClassCode is nul SP:StmtCompleted 0 0 128 0 SET NOCOUNT ON --************************************************************************************* SP:StmtCompleted 0 0 0 0 <TD style="BORDER-RIGHT: #eae6dd; BORDER-TOP: #eae6dd; BORDER-LEFT: #eae6dd; BORDER-BOTTOM: #eae6dd; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="SELECT [ID],AssignedTo FROM rn_tblrenewals where Len(Flags) 0 and BeenOnReport is null and policyNumber like (@ProductType+'%') and Assignedto is null FOR UPDATE of AssignedTo ">SELECT [ID],AssignedTo FROM rn_tblrenewals where Len(Flags) <> 0 and BeenOnReport is null and policyNumber like (@ProductType+'%') and Assignedto is null FOR UPDATE of AssignedTo SP:StmtCompleted 0 0 80 0 OPEN Renewal_Cursor SP:StmtCompleted 0 0 0 0 FETCH NEXT FROM Renewal_Cursor INTO @ID, @UWName SP:StmtCompleted 62 63 179 0 <TD style="BORDER-RIGHT: #eae6dd; BORDER-TOP: #eae6dd; BORDER-LEFT: #eae6dd; BORDER-BOTTOM: #eae6dd; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="WHILE (@@fetch_status -1) ">WHILE (@@fetch_status <> -1) SP:StmtCompleted 0 0 0 0 CLOSE Renewal_Cursor SP:StmtCompleted 0 0 0 0 DEALLOCATE Renewal_Cursor --************************************************************************************* SP:StmtCompleted 0 0 0 0 SET NOCOUNT OFF SP:StmtCompleted 0 0 0 0 drop table #UserPercent SP:StmtCompleted 0 0 49 0 exec sp_AssignUWToRenewalPCL 'PCL' SP:StmtCompleted 159 97 55 11 select @RunDate = convert(datetime,convert(varchar(10),@RunDate,101)) SP:StmtCompleted 0 0 0 0 Select [ID], PolicyNumber, Insured, ExpirationDate = convert(varchar(10),ExpirationDate,101), Flags, State, Admitted, SP:StmtCompleted 79 78 79 0 sp_GetTriage '8/19/2004 11:59:59 AM','pcl' SQL:StmtCompleted 209 22 0 198 -
"Cumulative wait time on server replies 108062 36020.7"
Where is above information from?
-
"Where is above information from?"
Query Analyzer Client Statistics
-
Can you run same sp from QA in the server machine instead from your workstation?
-
Allen,
I found the culprit.... our networking group was doing some "maintenance". They were replicating 2 terabytes of data to a mirrored server... it must have eaten all the bandwidth. They denied it when I asked, then boasted about it at a meeting Friday afternoon.... GRRRRRRR. Thanks for all your help. The wait time really was generated by a lack of resources on the network.
-
if your SP take huge time to execute .. then you have to check these options that may help u
1- take care that your SQl server doesn't have any other programs that run with it .. so that machine Having
only SQL Server installed
2- recompile your stored procedure
3- update Statistics to tables or views that uses this Stored procedure
4- reindex your tables .. so your query get faster
5- add any other index to your table .. to get beter performance
6- with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not
recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock
these tables
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply