April 22, 2011 at 7:49 am
I am basically a .NET developer and trying to troubleshoot the performance of a query
I am noticing some database timeouts in my .NET application and on further analysis(of Actual execution plan) it seems the update statement in the stored procedure has Table Scans on a table with 33 million records.......So i created a non clustered index on the table which changed the table Scans to index seek
But now how do i know whether the performance changed significantly....what are the factors i need to consider???
I checked the total Execution time in Client statistics, it looked almost similar
April 22, 2011 at 9:33 am
sandyinfowave (4/22/2011)
I am basically a .NET developer and trying to troubleshoot the performance of a queryI am noticing some database timeouts in my .NET application and on further analysis(of Actual execution plan) it seems the update statement in the stored procedure has Table Scans on a table with 33 million records.......So i created a non clustered index on the table which changed the table Scans to index seek
But now how do i know whether the performance changed significantly....what are the factors i need to consider???
I checked the total Execution time in Client statistics, it looked almost similar
Basically, execution time, reads and scans. If you use SQL Server Management Studio to run the queries, you can use SET STATISTICS IO ON and SET STATISTICS TIME ON to capture that information. If you're still unsure, go ahead and post the execution plan up here and some of us will take a look at it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 22, 2011 at 10:40 am
Here is the query i was working on
UPDATE p
SET p.CPEReleaseID = @CPEReleaseID,
p.DateUpdated = getdate()
FROM Cln_ServiceInstPayer p WITH (NOLOCK)
INNER JOIN Cln_ServiceInstance i WITH (NOLOCK) ON p.ServiceInstKey = i.ServiceInstKey
INNER JOIN is_CPERelease cpe WITH (NOLOCK) ON cpe.CPEThresholdID = p.CPEThresholdID
WHERE p.CPEThresholdID = @CPEThresholdID
AND p.PayerID = 201
AND p.DeleteStatus = 0
AND cpe.CPEReleaseID = @CPEReleaseID
AND i.ServiceDateBegin BETWEEN @ReleaseStartDate AND @ReleaseEndDate
AND i.ServiceDateEnd BETWEEN @ReleaseStartDate AND @ReleaseEndDate
AND i.Status = @StatusID
AND i.VoidStatus IS NULL
AND i.ServiceInstType IN (40261,40262)
AND i.SubmitDate <= CASE WHEN cpe.ReleaseAtExecuteFlag = 0 THEN cpe.AppliedDate
WHEN cpe.ReleaseAtExecuteFlag = 1 THEN GETDATE() END
Also please find the attachment for the execution plan
I created the non clustered index on InstPayer table with columns DeleteStatus , PayerID, CPEThresholdID, ServiceInstKey(not sure whether that's the correct approach)....any other suggestions??
April 22, 2011 at 10:55 am
Please post table and index definitions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2011 at 12:09 pm
Here are the Execution times and Scans before and after creating indexe's
Before the creation of index
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 62 ms, elapsed time = 74 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'is_CPERelease'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(0 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 41 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
After Creation of index:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 49 ms, elapsed time = 49 ms.
Table 'cln_ServiceInstPayer'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'is_CPERelease'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(0 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 30 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Parse and Compile Time decrease significantly....So it looks like the creation of non clustered index on cln_serviceInstPayer did improve the performance
April 22, 2011 at 12:51 pm
But is it advisable to create index on a table which is involved in the update process(I guess index defragmentation will be more)
April 22, 2011 at 1:18 pm
Here i am updating CPEReleaseID and DateUpdated on Instpayer table and in the where clause i m filtering data based upon DeleteStatus, CPEThresholdID, payerID....So i thought creating a non clustered index(with DeleteStatus,CPEthresholdID, payerID columns) on Instpayer table will solve my problem but I am updating CPEReleaseID and DateUpdated on Instpayer table....So not sure whether creating a non clustered index would help me much or it will create problems for me in the future
April 22, 2011 at 2:21 pm
GilaMonster (4/22/2011)
Please post table and index definitions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2011 at 2:25 pm
sandyinfowave (4/22/2011)
SQL Server Parse and Compile Time decrease significantly....So it looks like the creation of non clustered index on cln_serviceInstPayer did improve the performance
Parse and compile time will not be reduced by indexes. They're the time SQL takes to break the statement up, convert it into an internal representation and generate an execution plan.
It's execution time that indexes affect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2011 at 2:38 pm
How many rows are in Cln_ServiceInstPayer? Is there any particular reason that you don't have a clustered index on that table?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2011 at 2:41 pm
here are the index definitions
ALTER TABLE [dbo].[cln_ServiceInstance] ADD CONSTRAINT [PK_cln_ServiceInstance] PRIMARY KEY CLUSTERED
(
[ServiceInstKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
ALTER TABLE [dbo].[is_CPERelease] ADD CONSTRAINT [PK_is_CPERelease] PRIMARY KEY CLUSTERED
(
[CPEReleaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here are the table definitions
CREATE TABLE [dbo].[cln_ServiceInstPayer](
[ServiceInstPayerKey] [uniqueidentifier] NOT NULL,
[ServiceInstKey] [uniqueidentifier] NOT NULL,
[PayerID] [int] NOT NULL,
[PatientPayerID] [varchar](80) NULL,
[AuthorizationNumber] [varchar](30) NULL,
[Responsibility] [int] NULL,
[BillableFlag] [char](1) NULL,
[PaidToDMHFlag] [bit] NOT NULL,
[PreviouslyPaidFlag] [bit] NOT NULL,
[PayerClaimKey] [uniqueidentifier] NULL,
[PayerLastRAKey] [uniqueidentifier] NULL,
[Status] [varchar](20) NOT NULL,
[PayerRAStatus] [int] NULL,
[PayerCOBAllowedAmt] [decimal](19, 2) NULL,
[ActualPaidAmt] [decimal](19, 2) NULL,
[ApprovedAmt] [decimal](19, 2) NULL,
[PayerBatchID] [int] NULL,
[DeleteStatus] [bit] NULL,
[DateCreated] [datetime] NOT NULL,
[DateUpdated] [datetime] NOT NULL,
[LastUpdatedBy] [varchar](255) NOT NULL,
[Multiple835Flag] [bit] NULL,
[AltCOBFlag] [bit] NULL,
[AltCOBStatus] [varchar](25) NULL,
[CPEThresholdID] [int] NULL,
[CPEMCalOnlyFlag] [bit] NULL,
[CPEReleaseID] [int] NULL,
[PayerAdjudicationDate] [datetime] NULL,
[PayerClaimID] [varchar](80) NULL,
[CPEManualReleaseFlag] [bit] NOT NULL,
[PayerProcedureID] [int] NULL,
[AltCOBTypeID] [int] NULL,
[AltCOBServiceInstPayerKey] [uniqueidentifier] NULL,
CONSTRAINT [PK_cln_ServiceInstPayer] PRIMARY KEY NONCLUSTERED
(
[ServiceInstPayerKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cln_ServiceInstance](
[ServiceInstKey] [uniqueidentifier] NOT NULL,
[ServiceID] [uniqueidentifier] NOT NULL,
[ISClaimID] [int] IDENTITY(1,1) NOT NULL,
[OrgType] [varchar](25) NULL,
[SubscriberPrimaryID] [varchar](80) NULL,
[SubmitterProviderID] [int] NOT NULL,
[SubmitterClaimID] [varchar](38) NULL,
[SeqNum] [int] NULL,
[UOFSSegSeq] [varchar](10) NULL,
[ServiceInstType] [int] NULL,
[ServiceDateBegin] [datetime] NULL,
[ServiceDateEnd] [datetime] NULL,
[ReceiverID] [int] NULL,
[BillingProviderID] [int] NULL,
[PayToProviderID] [int] NULL,
[RenderingProviderInstID] [int] NULL,
[ServiceLocationProviderID] [int] NULL,
[ProcedureCodeID] [int] NULL,
[HIPAAPrimaryDx] [varchar](30) NULL,
[Collateral] [int] NULL,
[MinutesTotal] [int] NULL,
[MinutesFaceToFace] [int] NULL,
[MinutesOther] [int] NULL,
[PlaceOfServiceID] [int] NULL,
[TelephoneServiceFlag] [bit] NOT NULL,
[GroupSessionID] [uniqueidentifier] NULL,
[CrossOverID] [int] NULL,
[MCareCertifiedFlag] [bit] NOT NULL,
[CompleteFlag] [bit] NOT NULL,
[Status] [int] NULL,
[Ward] [varchar](50) NULL,
[LateCode] [int] NULL,
[McalPrevPaidFlag] [bit] NULL,
[MCarePrevPaidFlag] [bit] NULL,
[MCareAssignment] [char](1) NULL,
[ServiceIDQual] [char](2) NULL,
[ServiceCode] [varchar](48) NULL,
[ServiceUnitType] [varchar](4) NULL,
[ServiceUnitCount] [decimal](9, 2) NULL,
[FFSServiceCode] [varchar](10) NULL,
[FFSRateConversionID] [int] NULL,
[FFSServiceQty] [int] NULL,
[SubmitDate] [datetime] NULL,
[ClaimAmt] [decimal](19, 2) NULL,
[ContractedRate] [decimal](19, 2) NULL,
[ClientPaidAmt] [decimal](19, 2) NULL,
[PrivInsPaidAmt] [decimal](19, 2) NULL,
[McarePaidAmt] [decimal](19, 2) NULL,
[McalPaidAmt] [decimal](19, 2) NULL,
[LocalAmt] [decimal](19, 2) NULL,
[ActualPaidAmt] [decimal](19, 2) NULL,
[ProviderRAKey] [uniqueidentifier] NULL,
[DenySource] [varchar](15) NULL,
[DenyReason] [varchar](10) NULL,
[DenyGroup] [varchar](4) NULL,
[AdjudicatedBy] [varchar](30) NULL,
[AdjudicationDate] [datetime] NULL,
[PaymentDate] [datetime] NULL,
[EligReqKey] [uniqueidentifier] NULL,
[ClaimDateCreated] [datetime] NULL,
[ParentISClaimNumber] [int] NULL,
[DeleteFlag] [bit] NOT NULL,
[VoidStatus] [int] NULL,
[RuleCode] [varchar](30) NULL,
[AuthCode] [varchar](20) NULL,
[HFFlag] [bit] NULL,
[DateCreated] [datetime] NOT NULL,
[DateUpdated] [datetime] NOT NULL,
[LastUpdatedBy] [varchar](255) NOT NULL,
[ServiceRateDetailID] [int] NULL,
[RateTableDetailsID] [int] NULL,
[Rate] [decimal](19, 2) NULL,
[ISDupOverrideCode] [int] NULL,
[RenderingProviderID] [int] NULL,
[BillingProviderNPI] [varchar](35) NULL,
[ServiceLocationNPI] [varchar](35) NULL,
[RenderingProviderNPI] [varchar](35) NULL,
[SatelliteFlag] [char](1) NULL,
[PublicSchoolFlag] [char](1) NULL,
[CPEContractRate] [decimal](19, 2) NULL,
[DTAOverrideId] [int] NULL,
[EmergencyService] [bit] NULL,
[PregnancyService] [bit] NULL,
[EPSDTService] [bit] NULL,
[Dupoverride] [bit] NULL,
[SDPhaseID] [int] NULL,
[SDIIDelayedClaimCriteriaID] [int] NULL,
[PriorAuthNumber] [varchar](80) NULL,
CONSTRAINT [PK_cln_ServiceInstance] PRIMARY KEY CLUSTERED
(
[ServiceInstKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[is_CPERelease](
[CPEReleaseID] [int] IDENTITY(1,1) NOT NULL,
[CPEThresholdID] [int] NOT NULL,
[ReleaseTypeID] [int] NOT NULL,
[ReleaseStartDate] [datetime] NOT NULL,
[ReleaseEndDate] [datetime] NOT NULL,
[AppliedDate] [datetime] NOT NULL,
[AppliedBy] [varchar](255) NOT NULL,
[ProcessFlag] [bit] NOT NULL,
[ProcessDate] [datetime] NULL,
[DeleteStatus] [bit] NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateUpdated] [datetime] NOT NULL,
[LastUpdatedBy] [varchar](255) NULL,
[ReleaseAtExecuteFlag] [bit] NOT NULL,
CONSTRAINT [PK_is_CPERelease] PRIMARY KEY CLUSTERED
(
[CPEReleaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
April 22, 2011 at 3:14 pm
Cln_ServiceInstpayer does have a clustered index but it's not being used in this query......that table has around 30 million records
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply