November 30, 2015 at 7:55 am
Sergiy (11/28/2015)
Welsh Corgi (11/27/2015)
I am against partitioning in this situation.I have archive procedure that is very complex.
How can I convince my VP that partitioning is not the way to go?
First - you need to answer - the way to go from where and to where?
Why do you need to go anywhere?
What is the problem you're trying to rectify by the move?
When you the problem clearly formulated you meed to collect possible solutions.
Then you evaluate pros and contras for each of them and choose the most appropriate For the task.
So, what is the problem(s) which caused the discussion about partitioning?
If it's unsatisfactory query performance then you need to look into the clustering of the tables.
In the following topic I showed how correct clustering makes partitioning irrelevant:
http://www.sqlservercentral.com/Forums/FindPost1738471.aspx
If it's not (only) performance issue then other factors must be taken into consideration.
Partitioning was not my idea.
I do not think that it is going to help and I'm against it.
I'm just trying to justify not going that way.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 8:02 am
ScottPletcher (11/25/2015)
Rushing this process more than normal right now, very busy on top of holiday fast approaching :-).Hopefully this helps a little more:
Chg1) Add column contr_id as an INCLUDEd column on index IDX_xactControlPoint_xactControlPoint.
Chg2) Add column contr_id as an INCLUDEd column on index IDX__xactControlPoint__Callid_xactControlPointTypeID.
Create a new index as follows:
CREATE NONCLUSTERED INDEX <your_index_name_goes_here> ON xactControlPoint ( xactTransactionIDValue ) INCLUDE ( call_id, xactControlPointID ) WITH ( FILLFACTOR = 95, SORT_IN_TEMPDB = ON ) ON [FG2]; --change filegroup as preferred
DROP INDEX IN_CallID ON xactControlPoint;
DROP INDEX IN_ContrID ON xactControlPoint;
DROP INDEX IDX_xactControlPoint ON xactControlPoint;
DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
DROP INDEX IX_ControlPointTypeID ON xactControlPoint;
Thanks. That helps a lot.
I had noticed that there is a lot of redundancy as far as indexes on this table.
How did you make the determination to add, modify and drop the indexes?
Are there any risks in making the changes that you suggested?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 8:08 am
Scott,
If I have to go with partitioning what changes to the indexes would you recommend?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 8:27 am
ScottPletcher (11/25/2015)
Rushing this process more than normal right now, very busy on top of holiday fast approaching :-).Hopefully this helps a little more:
Chg1) Add column contr_id as an INCLUDEd column on index IDX_xactControlPoint_xactControlPoint.
Chg2) Add column contr_id as an INCLUDEd column on index IDX__xactControlPoint__Callid_xactControlPointTypeID.
Create a new index as follows:
CREATE NONCLUSTERED INDEX <your_index_name_goes_here> ON xactControlPoint ( xactTransactionIDValue ) INCLUDE ( call_id, xactControlPointID ) WITH ( FILLFACTOR = 95, SORT_IN_TEMPDB = ON ) ON [FG2]; --change filegroup as preferred
DROP INDEX IN_CallID ON xactControlPoint;
DROP INDEX IN_ContrID ON xactControlPoint;
DROP INDEX IDX_xactControlPoint ON xactControlPoint;
DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
DROP INDEX IX_ControlPointTypeID ON xactControlPoint;
Why? DROP INDEX IDX_xactControlPoint ON xactControlPoint;
Why DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
I think I am interpreting the other changes correctly.
Thanks for the tips.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 8:39 am
Welsh Corgi (11/30/2015)
ScottPletcher (11/25/2015)
Rushing this process more than normal right now, very busy on top of holiday fast approaching :-).Hopefully this helps a little more:
Chg1) Add column contr_id as an INCLUDEd column on index IDX_xactControlPoint_xactControlPoint.
Chg2) Add column contr_id as an INCLUDEd column on index IDX__xactControlPoint__Callid_xactControlPointTypeID.
Create a new index as follows:
CREATE NONCLUSTERED INDEX <your_index_name_goes_here> ON xactControlPoint ( xactTransactionIDValue ) INCLUDE ( call_id, xactControlPointID ) WITH ( FILLFACTOR = 95, SORT_IN_TEMPDB = ON ) ON [FG2]; --change filegroup as preferred
DROP INDEX IN_CallID ON xactControlPoint;
DROP INDEX IN_ContrID ON xactControlPoint;
DROP INDEX IDX_xactControlPoint ON xactControlPoint;
DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
DROP INDEX IX_ControlPointTypeID ON xactControlPoint;
Why? DROP INDEX IDX_xactControlPoint ON xactControlPoint;
Why DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
I think I am interpreting the other changes correctly.
Thanks for the tips.
Actually I have two more duplicate indexes that I know that they have to be deleted on the ControlPoinTypeID COlum.
The current indexes are as follow:
index_nameindex_descriptionindex_keys
IDX__xactControlPoint__Callid_xactControlPointTypeIDnonclustered located on FG2call_id, xactControlPointTypeID
IDX_xactControlPointnonclustered located on FG1xactControlPointTypeID
IDX_xactControlPoint_xactControlPointnonclustered located on FG1xactControlPointTypeID
IN_CallIDnonclustered located on FG2call_id
IN_ContrIDnonclustered located on FG2contr_id
IX_ControlPointTypeIDnonclustered located on FG1xactControlPointTypeID
IX_xactControlPoint__K3_K4nonclustered located on FG2call_id, contr_id
PK_xactControlPointclustered, unique, primary key located on FG1xactControlPointID
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 8:56 am
I've adjusted the spreadsheet you originally posted to have both missing index and index usage stats. That is the minimum info needed to analyze indexes. I've pulled the table in q to the top of both sheets.
Typically you'd also want to have the {min, avg and max} columns lengths of any index key and/or include columns, and the column cardinality of potential index keys. But we can make do with this for now. You also would need to review some query text at some point, but very early on only if the query had some egregious coding practice, such as ISNULL() or other function(s) against a WHERE or JOIN column.
We can then step thru the various recommendations, and why I use the ident numbers on my list and not SQL's own index numbers when writing up analysis.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2015 at 9:18 am
Welsh Corgi (11/30/2015)
Welsh Corgi (11/30/2015)
ScottPletcher (11/25/2015)
Rushing this process more than normal right now, very busy on top of holiday fast approaching :-).Hopefully this helps a little more:
Chg1) Add column contr_id as an INCLUDEd column on index IDX_xactControlPoint_xactControlPoint.
Chg2) Add column contr_id as an INCLUDEd column on index IDX__xactControlPoint__Callid_xactControlPointTypeID.
Create a new index as follows:
CREATE NONCLUSTERED INDEX <your_index_name_goes_here> ON xactControlPoint ( xactTransactionIDValue ) INCLUDE ( call_id, xactControlPointID ) WITH ( FILLFACTOR = 95, SORT_IN_TEMPDB = ON ) ON [FG2]; --change filegroup as preferred
DROP INDEX IN_CallID ON xactControlPoint;
DROP INDEX IN_ContrID ON xactControlPoint;
DROP INDEX IDX_xactControlPoint ON xactControlPoint;
DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
DROP INDEX IX_ControlPointTypeID ON xactControlPoint;
Why? DROP INDEX IDX_xactControlPoint ON xactControlPoint;
Why DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
I think I am interpreting the other changes correctly.
Thanks for the tips.
Actually I have two more duplicate indexes that I know that they have to be deleted on the ControlPoinTypeID COlum.
The current indexes are as follow:
index_nameindex_descriptionindex_keys
IDX__xactControlPoint__Callid_xactControlPointTypeIDnonclustered located on FG2call_id, xactControlPointTypeID
IDX_xactControlPointnonclustered located on FG1xactControlPointTypeID
IDX_xactControlPoint_xactControlPointnonclustered located on FG1xactControlPointTypeID
IN_CallIDnonclustered located on FG2call_id
IN_ContrIDnonclustered located on FG2contr_id
IX_ControlPointTypeIDnonclustered located on FG1xactControlPointTypeID
IX_xactControlPoint__K3_K4nonclustered located on FG2call_id, contr_id
PK_xactControlPointclustered, unique, primary key located on FG1xactControlPointID
Thanks for all of your help.
I'm not clear on deleting some of the indexes.
Would it be possible for you to include a brief note?
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 9:37 am
Welsh Corgi (11/30/2015)
Welsh Corgi (11/30/2015)
ScottPletcher (11/25/2015)
Rushing this process more than normal right now, very busy on top of holiday fast approaching :-).Hopefully this helps a little more:
Chg1) Add column contr_id as an INCLUDEd column on index IDX_xactControlPoint_xactControlPoint.
Chg2) Add column contr_id as an INCLUDEd column on index IDX__xactControlPoint__Callid_xactControlPointTypeID.
Create a new index as follows:
CREATE NONCLUSTERED INDEX <your_index_name_goes_here> ON xactControlPoint ( xactTransactionIDValue ) INCLUDE ( call_id, xactControlPointID ) WITH ( FILLFACTOR = 95, SORT_IN_TEMPDB = ON ) ON [FG2]; --change filegroup as preferred
DROP INDEX IN_CallID ON xactControlPoint;
DROP INDEX IN_ContrID ON xactControlPoint;
DROP INDEX IDX_xactControlPoint ON xactControlPoint;
DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
DROP INDEX IX_ControlPointTypeID ON xactControlPoint;
Why? DROP INDEX IDX_xactControlPoint ON xactControlPoint;
Why DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
I think I am interpreting the other changes correctly.
Thanks for the tips.
Actually I have two more duplicate indexes that I know that they have to be deleted on the ControlPoinTypeID COlum.
The current indexes are as follow:
index_nameindex_descriptionindex_keys
IDX__xactControlPoint__Callid_xactControlPointTypeIDnonclustered located on FG2call_id, xactControlPointTypeID
IDX_xactControlPointnonclustered located on FG1xactControlPointTypeID
IDX_xactControlPoint_xactControlPointnonclustered located on FG1xactControlPointTypeID
IN_CallIDnonclustered located on FG2call_id
IN_ContrIDnonclustered located on FG2contr_id
IX_ControlPointTypeIDnonclustered located on FG1xactControlPointTypeID
IX_xactControlPoint__K3_K4nonclustered located on FG2call_id, contr_id
PK_xactControlPointclustered, unique, primary key located on FG1xactControlPointID
Why do you recommend DROP INDEX IN_ContrID ON xactControlPoint?
Why do you recommend DROP INDEX IX_xactControlPoint__K3_K4 ON xactControlPoint; --only *after* completing Chg1 and Chg2 above.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 10:11 am
--Edit: removed, exact dup of next post, I got an error during posting and didn't realize it had actually posted anyway.--
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2015 at 10:12 am
Using the spreadsheet I just posted, let's look at the xactControlPoint table. Notice my script adds an "ident" column before each missing index stat. That is to reference that stat by an easy #. It's impossible to use an index# anyway, since these are indexes that do not yet exist that SQL "wants" created. So, when giving reasons for an index modification (delete / insert / update), I'll refer back to that ident#. For consistency and ease of use, I also added an "ident" column to the index usage stats. That provides a unique number, whereas naturally SQL's actual index numbers will overlap between tables. In the rare cases of possible ambiguity, where the same table happens to have missing and usage idents in the same range, I'll refer to "m###" to be "missing index ident=###" and "u###" to be "index usage stats ident ###".
Ok, on to the good part! (Hey, I'm a DBA, so it is good to me.) First the clustering key. It's currently "xactControlPointID", which has 693,481 seeks. "call_id" has 685,780 combined -- not counting the ~60 shown in #125 and #131 -- so the choice is very close. But, all the "call_id" reads are doing lookups to the main table (709,800 total lookups). Moreover, other major tables are (correctly) clustered on call_id. When joining tables both clus on call_id, joins could often be merge joins, which are extremely fast on already sorted data. [Furthermore, at least one table that isn't clustered by call_id should be :-D).] Thus, overall, my first choice would be to cluster this table on call_id. Clustering the table on call_id would also remove the need for indexes #38, #39 and #40.
But note that xactControlPointID can still be the PK and definitely should still have its own unique index. It just wouldn't be clustered. Also, we have to check on nonclustered indexes that might need xactControlPointID added. Remember, it's currently the clustering key, so it automatically appears in every index. Once it's no longer the clus key, it might have to be explicitly INCLUDEd in one or more nonclus indexes. You can review missing index stats in the future to help determine that.
Now, let's look at the other indexes, in alpha key order.
There's an existing index on "contr_id" that's not being used, #41, so it can be dropped. But SQL "says" it needed an index on contr_id, #126, 8 times but only if it included 5 other columns, one of which is "notes". Hmm, that sounds like a long column (min/avg/max len would be useful here). That's iffy. Let's keep these needs in mind as we look at other indexes.
That brings us to "xactControlPointTypeID". We've got 3 existing indexes on that column, #42, #43 and #44. One is the key only, and two are identical and thus redundant. So drop #42 and #43. We'll add to #44 only. Based on #127, we should INCLUDE contr_id on index #44. Based on #129 and #132, you could consider adding "username", depending on its length and how critical those 4 lookup occurrences might be. Based on #130 and #126 we also want to explicitly include xactControlPointID. I'd ignore #131 -- I'm not adding that many columns for only 4 lookup occurrences. Certainly not yet.
Finally -- see, this really does end! :doze: -- based on #133 and #134, we need an index on "xactTransactionIDValue" that includes call_id and xactControlPointID. Back to #126 / notes column, I'd still hold off on that for now. Make the other changes, then review index usage again.
Tuning indexes is definitely an iterative process!
Edit: Corrected index #10 to "#130 and #126" (in bold above). Also, naturally you'd normally want a lot more than just 3 days of index activity. If you think that usage is typical, proceed, else you might want to wait a few more days and re-analyze.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2015 at 10:40 am
ScottPletcher (11/30/2015)
--Edit: removed, exact dup of next post, I got an error during posting and didn't realize it had actually posted anyway.--
I'm sorry but what index are you referring to?
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 10:59 am
I'm looking at another table that is much worse:
/****** Object: Table [dbo].[tblCall] Script Date: 11/30/2015 12:27:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCall](
[Call_ID] [int] NOT NULL,
[Call_Date] [datetime] NULL,
[Call_Time] [datetime] NULL,
[Operator_ID] [smallint] NULL,
[Reviewer_ID] [char](30) NULL,
[Insurer_ID] [int] NULL,
[DivisionID] [int] NULL,
[Insurer_Name] [char](50) NULL,
[Insurer_Approval_Comments] [text] NULL,
[Insurer_Daily_Comments] [text] NULL,
[Adjuster] [char](30) NULL,
[CSR_ID] [char](30) NULL,
[CSR_Name] [char](30) NULL,
[CSR_Phone] [char](50) NULL,
[CSR_Ext] [char](10) NULL,
[CSR_Conf] [char](1) NULL,
[CSR_Conf_Date] [datetime] NULL,
[CSR_Conf_Time] [datetime] NULL,
[Insured_Full_NM] [char](50) NULL,
[Insured_FName] [char](14) NULL,
[Insured_LName] [char](16) NULL,
[Insured_Address] [char](30) NULL,
[Insured_City] [char](20) NULL,
[Insured_State] [char](2) NULL,
[Insured_Zip] [char](10) NULL,
[Insured_HPhone] [char](50) NULL,
[Insured_WPhone] [char](50) NULL,
[Insured_CellPhone] [char](50) NULL,
[Insured_Pager] [char](50) NULL,
[Insured_Email] [char](100) NULL,
[Insured_AltPhone] [char](50) NULL,
[Insured_Comments] [text] NULL,
[ThirdParty_Claimant] [char](50) NULL,
[ThirdParty_Phone] [char](50) NULL,
[ThirdParty_AccessName] [char](50) NULL,
[ThirdParty_AccessPhone] [char](50) NULL,
[Policy] [char](25) NULL,
[Claim] [char](25) NULL,
[Deductible_Amt] [money] NULL,
[Loss_Date] [datetime] NULL,
[Loss_Location] [char](30) NULL,
[Loss_Address] [char](30) NULL,
[Loss_City] [char](20) NULL,
[Loss_State] [char](2) NULL,
[Loss_Zip] [char](10) NULL,
[Loss_Descr] [text] NULL,
[Loss_Comments] [text] NULL,
[CONTR_ID] [int] NULL,
[Contractor_Name] [char](50) NULL,
[Contr_Rep] [char](30) NULL,
[Contr_Fax_Date] [datetime] NULL,
[Contr_Fax_Rec_Date] [datetime] NULL,
[Contr_Fax_Rec_Time] [datetime] NULL,
[Contr_Insured_Date] [datetime] NULL,
[Contr_Insured_Time] [datetime] NULL,
[Contr_Appt_Date] [datetime] NULL,
[Contr_Appt_Time] [datetime] NULL,
[Contr_Comments] [text] NULL,
[Contr_GetJob] [char](1) NULL,
[Contr_Cancellation] [char](1) NULL,
[Contr_Assigned_Date] [datetime] NULL,
[PRISM_Just_Dev] [char](1) NULL,
[PRISM_Frst_Est_Date] [datetime] NULL,
[PRISM_Frst_RevReq_Date] [datetime] NULL,
[PRISM_Received_Date] [datetime] NULL,
[PRISM_Closed_Date] [datetime] NULL,
[PRISM_Inspection_Date] [datetime] NULL,
[PRISM_FollowUp_Date] [datetime] NULL,
[PRISM_Comments] [text] NULL,
[PRISM_Frst_Review_Date] [datetime] NULL,
[PRISM_ClaimClosed_Date] [datetime] NULL,
[Owner_ID] [int] NULL,
[DateRec_ID] [int] NULL,
[Creator_ID] [int] NOT NULL,
[CALL_LST_REV_ID_NB] [int] NULL,
[CALL_LST_REV_SER_NB] [int] NULL,
[CALL_LOCK_ID_NB] [int] NULL,
[CALL_BIX_FILE_NM] [varchar](80) NULL,
[CALL_LST_SENT_DT] [datetime] NULL,
[CALL_RCV_EN_IN] [char](1) NULL,
[Est_System] [char](3) NULL,
[CALL_STAT_CD] [char](20) NULL,
[Contr_WorkAuthorization] [char](1) NULL,
[Contr_EMail] [varchar](80) NULL,
[PRISM_ClaimCancellation] [char](1) NULL,
[Loss_Location_Same] [char](1) NULL,
[Nexus_ID] [int] NULL,
[Loss_Cause] [int] NULL,
[CommitDate_Con] [datetime] NULL,
[CommitDate_Ins] [datetime] NULL,
[CommitDate_Est] [datetime] NULL,
[Insured_Company] [varchar](50) NULL,
[PRISM_Frst_RevRec_Date] [datetime] NULL,
[CommitDate_Rev] [datetime] NULL,
[Type_ID] [int] NULL,
[Has_Been_Cancelled] [tinyint] NULL,
[Claim_Cancelled_Reason] [int] NULL,
[Program_Option_ID] [int] NULL,
[ClientAdminResource] [int] NULL,
[CustSurveyTag] [tinyint] NULL,
[ReinspectTag] [tinyint] NULL,
[RandomNum] [real] NULL,
[BilledClient] [tinyint] NULL,
[BilledContractor] [tinyint] NULL,
[billedContrDate] [datetime] NULL,
[billedAssign] [tinyint] NULL,
[billedAssignDate] [datetime] NULL,
[USAAregionName] [varchar](200) NULL,
[singleTradeListID] [int] NULL,
[IsDirty] [tinyint] NULL,
[custSurveyPendDate] [datetime] NULL,
[callDateFull] [datetime] NULL,
[roofInspectionFlg] [bit] NULL,
[USAAprogramArea] [varchar](200) NULL,
[xactTransactionIDValue] [varchar](50) NULL,
[profileCode] [varchar](5) NULL,
[contrEmailSentFlg] [bit] NULL,
[Reinforcement_FLG] [bit] NULL,
[JobStartOverrideDate] [datetime] NULL,
[THDSentFlg] [bit] NULL,
[AddlEstNeeded] [bit] NULL,
[CheckPointPriceList] [varchar](25) NULL,
[PriceList] [varchar](25) NULL,
[ReferralFlg] [bit] NULL,
[MRPSupplementDate] [datetime] NULL,
[CATAssignment] [bit] NULL,
[RvwFlg] [bit] NULL,
[CMS_NextFollowUpDate] [datetime] NULL,
[TestFlg] [bit] NULL,
[CatCode] [varchar](10) NULL,
[PendingCancellationFlg] [bit] NULL,
[PendingNCFFlg] [bit] NULL,
[ReferralId] [varchar](25) NULL,
[CallCenterRepAssigned] [int] NULL,
[isBeingHeld] [bit] NULL,
[XADataSet] [varchar](100) NULL,
[CC_CATCode] [varchar](30) NULL,
[SymbilityClaimId] [uniqueidentifier] NULL,
[SymbilityClaimAssignmentId] [int] NULL,
[SymbilityIntermediateClaimAssignmentId] [int] NULL,
[Loss_BuildYear] [int] NULL,
[Target_Followup_Date] [datetime] NULL,
[AssignmentReceiptMethodID] [int] NULL,
[TermsAndConditionsFlg] [bit] NULL,
CONSTRAINT [PK_tblCall] PRIMARY KEY CLUSTERED
(
[Call_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]
) ON [FG1] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [_dta_index_tblCall_5_1301175981__K6_K98_K96_K109_K1_K48_K87_K2_40_61_63_66_95_123] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [_dta_index_tblCall_5_1301175981__K6_K98_K96_K109_K1_K48_K87_K2_40_61_63_66_95_123] ON [dbo].[tblCall]
(
[Insurer_ID] ASC,
[Program_Option_ID] ASC,
[Has_Been_Cancelled] ASC,
[singleTradeListID] ASC,
[Call_ID] ASC,
[CONTR_ID] ASC,
[Nexus_ID] ASC,
[Call_Date] ASC
)
INCLUDE ( [Loss_Date],
[Contr_Assigned_Date],
[PRISM_Frst_Est_Date],
[PRISM_Closed_Date],
[Type_ID],
[AddlEstNeeded]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
/****** Object: Index [_dta_index_tblCall_6_1301175981__K1_K6_K48] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [_dta_index_tblCall_6_1301175981__K1_K6_K48] ON [dbo].[tblCall]
(
[Call_ID] ASC,
[Insurer_ID] ASC,
[CONTR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [_dta_index_tblCall_6_1301175981__K45_K6_K48_K87_K1_2_21_23_24_25_38_49] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [_dta_index_tblCall_6_1301175981__K45_K6_K48_K87_K1_2_21_23_24_25_38_49] ON [dbo].[tblCall]
(
[Loss_Zip] ASC,
[Insurer_ID] ASC,
[CONTR_ID] ASC,
[Nexus_ID] ASC,
[Call_ID] ASC
)
INCLUDE ( [Call_Date],
[Insured_LName],
[Insured_City],
[Insured_State],
[Insured_Zip],
[Claim],
[Contractor_Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [_dta_index_tblCall_6_1301175981__K6_1_25_45] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [_dta_index_tblCall_6_1301175981__K6_1_25_45] ON [dbo].[tblCall]
(
[Insurer_ID] ASC
)
INCLUDE ( [Call_ID],
[Insured_Zip],
[Loss_Zip]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [_dta_index_tblCall_6_1301175981__K6_K1_K87_K45_K48_2_21_23_24_25_38_49] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [_dta_index_tblCall_6_1301175981__K6_K1_K87_K45_K48_2_21_23_24_25_38_49] ON [dbo].[tblCall]
(
[Insurer_ID] ASC,
[Call_ID] ASC,
[Nexus_ID] ASC,
[Loss_Zip] ASC,
[CONTR_ID] ASC
)
INCLUDE ( [Call_Date],
[Insured_LName],
[Insured_City],
[Insured_State],
[Insured_Zip],
[Claim],
[Contractor_Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
/****** Object: Index [CallIndex1] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [CallIndex1] ON [dbo].[tblCall]
(
[Call_Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG2]
GO
/****** Object: Index [CallIndex2] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [CallIndex2] ON [dbo].[tblCall]
(
[Insurer_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG2]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [CallIndex8] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [CallIndex8] ON [dbo].[tblCall]
(
[Loss_Zip] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IDX_tblCall__K109_K6_K21_K20_K23_K24_K25_1] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall__K109_K6_K21_K20_K23_K24_K25_1] ON [dbo].[tblCall]
(
[singleTradeListID] ASC,
[Insurer_ID] ASC,
[Insured_FName] ASC,
[Insured_LName] ASC,
[Insured_City] ASC,
[Insured_State] ASC,
[Insured_Zip] ASC
)
INCLUDE ( [Call_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IDX_tblCall__K115_1] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall__K115_1] ON [dbo].[tblCall]
(
[xactTransactionIDValue] ASC
)
INCLUDE ( [Call_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IDX_tblCall_Composite] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall_Composite] ON [dbo].[tblCall]
(
[Insurer_ID] ASC,
[CONTR_ID] ASC,
[Has_Been_Cancelled] ASC
)
INCLUDE ( [Call_ID],
[Loss_Zip],
[Contr_Assigned_Date],
[singleTradeListID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IDX_tblCall_Composite_Include] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall_Composite_Include] ON [dbo].[tblCall]
(
[Insurer_ID] ASC,
[Has_Been_Cancelled] ASC
)
INCLUDE ( [Call_ID],
[Loss_Zip],
[Contr_Assigned_Date],
[singleTradeListID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IDX_tblCall_Composite_Includes] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall_Composite_Includes] ON [dbo].[tblCall]
(
[Insurer_ID] ASC,
[Has_Been_Cancelled] ASC
)
INCLUDE ( [Call_ID],
[Loss_Zip],
[Contr_Assigned_Date],
[singleTradeListID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
/****** Object: Index [IDX_tblCall_Composite2] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall_Composite2] ON [dbo].[tblCall]
(
[Insurer_ID] ASC,
[CONTR_ID] ASC,
[Has_Been_Cancelled] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IDX_tblCall_COMPOSITE2_INCLUDE] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall_COMPOSITE2_INCLUDE] ON [dbo].[tblCall]
(
[CONTR_ID] ASC,
[Has_Been_Cancelled] ASC,
[Insurer_ID] ASC
)
INCLUDE ( [Call_ID],
[Insurer_Name],
[Insured_FName],
[Insured_LName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
/****** Object: Index [IDX_tblCall_CONTR_ID_INCLUDE] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall_CONTR_ID_INCLUDE] ON [dbo].[tblCall]
(
[CONTR_ID] ASC
)
INCLUDE ( [Call_ID],
[Insurer_ID],
[Contr_Assigned_Date],
[Has_Been_Cancelled],
[BilledContractor]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IDX_tblCall_Loss_State_Loss_Date_INCLUDE] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tblCall_Loss_State_Loss_Date_INCLUDE] ON [dbo].[tblCall]
(
[Loss_State] ASC,
[Loss_Date] ASC
)
INCLUDE ( [Call_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
/****** Object: Index [IN_CallDateFull] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IN_CallDateFull] ON [dbo].[tblCall]
(
[callDateFull] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG2]
GO
/****** Object: Index [IN_CreatorID] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IN_CreatorID] ON [dbo].[tblCall]
(
[Creator_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [FG2]
GO
/****** Object: Index [IN_Nexus_ID] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IN_Nexus_ID] ON [dbo].[tblCall]
(
[Nexus_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG2]
GO
/****** Object: Index [IN_PendingCancel] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IN_PendingCancel] ON [dbo].[tblCall]
(
[PendingCancellationFlg] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
/****** Object: Index [IN_PendingNCF] Script Date: 11/30/2015 12:27:50 PM ******/
CREATE NONCLUSTERED INDEX [IN_PendingNCF] ON [dbo].[tblCall]
(
[PendingNCFFlg] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
GO
SET ANSI_PADDING ON
GO
Attached is a list of Indexes which I may at least identifies the duplicate Indexes and\or those that could be modified to reduce the number of Indexes.
But taking a quick glance do you concur with my initial observations as noted in the attached workbook?
This table is a mess with 2,427,429 records and a lot of performance issues.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 6:21 pm
ScottPletcher (11/30/2015)
Using the spreadsheet I just posted, let's look at the xactControlPoint table. Notice my script adds an "ident" column before each missing index stat. That is to reference that stat by an easy #. It's impossible to use an index# anyway, since these are indexes that do not yet exist that SQL "wants" created. So, when giving reasons for an index modification (delete / insert / update), I'll refer back to that ident#. For consistency and ease of use, I also added an "ident" column to the index usage stats. That provides a unique number, whereas naturally SQL's actual index numbers will overlap between tables. In the rare cases of possible ambiguity, where the same table happens to have missing and usage idents in the same range, I'll refer to "m###" to be "missing index ident=###" and "u###" to be "index usage stats ident ###".Ok, on to the good part! (Hey, I'm a DBA, so it is good to me.) First the clustering key. It's currently "xactControlPointID", which has 693,481 seeks. "call_id" has 685,780 combined -- not counting the ~60 shown in #125 and #131 -- so the choice is very close. But, all the "call_id" reads are doing lookups to the main table (709,800 total lookups). Moreover, other major tables are (correctly) clustered on call_id. When joining tables both clus on call_id, joins could often be merge joins, which are extremely fast on already sorted data. [Furthermore, at least one table that isn't clustered by call_id should be :-D).] Thus, overall, my first choice would be to cluster this table on call_id. Clustering the table on call_id would also remove the need for indexes #38, #39 and #40.
But note that xactControlPointID can still be the PK and definitely should still have its own unique index. It just wouldn't be clustered. Also, we have to check on nonclustered indexes that might need xactControlPointID added. Remember, it's currently the clustering key, so it automatically appears in every index. Once it's no longer the clus key, it might have to be explicitly INCLUDEd in one or more nonclus indexes. You can review missing index stats in the future to help determine that.
Now, let's look at the other indexes, in alpha key order.
There's an existing index on "contr_id" that's not being used, #41, so it can be dropped. But SQL "says" it needed an index on contr_id, #126, 8 times but only if it included 5 other columns, one of which is "notes". Hmm, that sounds like a long column (min/avg/max len would be useful here). That's iffy. Let's keep these needs in mind as we look at other indexes.
That brings us to "xactControlPointTypeID". We've got 3 existing indexes on that column, #42, #43 and #44. One is the key only, and two are identical and thus redundant. So drop #42 and #43. We'll add to #44 only. Based on #127, we should INCLUDE contr_id on index #44. Based on #129 and #132, you could consider adding "username", depending on its length and how critical those 4 lookup occurrences might be. Based on #130 and #126 we also want to explicitly include xactControlPointID. I'd ignore #131 -- I'm not adding that many columns for only 4 lookup occurrences. Certainly not yet.
Finally -- see, this really does end! :doze: -- based on #133 and #134, we need an index on "xactTransactionIDValue" that includes call_id and xactControlPointID. Back to #126 / notes column, I'd still hold off on that for now. Make the other changes, then review index usage again.
Tuning indexes is definitely an iterative process!
Edit: Corrected index #10 to "#130 and #126" (in bold above). Also, naturally you'd normally want a lot more than just 3 days of index activity. If you think that usage is typical, proceed, else you might want to wait a few more days and re-analyze.
I need to study your analysis and the results on the report.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2015 at 12:52 am
Hi,
hope you dont mind me asking is this for an insurance company or a building maintenance company?
Just had a look at tblcall and that needs a bit of work, the problem is making the changes isnt going to be easy or simple as you have legacy code to deal with.
One thing to be aware of (if you arnt already) is the TEXT data type is noted as being depreciated DataType so use VARCHAR(MAX).
Would it be simpler and quicker to redesign the database and application from the ground up, normalising the schema, and then migrate the old data?
What is the appetite for change in the business, or can you make a recommendation that its time to bite the bullet and bring the DB upto date.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 1, 2015 at 4:59 am
Jason-299789 (12/1/2015)
Hi,hope you dont mind me asking is this for an insurance company or a building maintenance company?
Just had a look at tblcall and that needs a bit of work, the problem is making the changes isnt going to be easy or simple as you have legacy code to deal with.
One thing to be aware of (if you arnt already) is the TEXT data type is noted as being depreciated DataType so use VARCHAR(MAX).
Would it be simpler and quicker to redesign the database and application from the ground up, normalising the schema, and then migrate the old data?
What is the appetite for change in the business, or can you make a recommendation that its time to bite the bullet and bring the DB upto date.
I need at least a few items to make a case against partitioning.
The table structure is awful.
I need more than the Test Column. I will have to change the data type before upgrading to 2014, right?
Could you please provide with a few talking poinys do I can make my case?
Thanks.
Edit: rewriting the application and reports is not an option. I'm kinfa boxed in.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 46 through 60 (of 62 total)
You must be logged in to reply to this topic. Login to reply