Indexes losing their effectiveness - Frequent REBU

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Indexes losing their effectiveness - Frequent REBU

Author

Topic

greg.wollman
Starting Member

5 Posts

Posted - 2011-05-20 : 16:08:57

Here is my situation.

We are a company that provides software to the Staffing Industry. One of our applications that queries the database is a Sales Analysis Gross Margin report. Below is a snippet of the SELECT / INSERT (1st snippet). Over the years we noticed some degradation of the speed of the query as the database became larger and larger. Usually we the query up to 5000 rows of information. The following steps and resources were implemented:

1.) Set up SQL Database Maintenance Plans at the customer site to REBUILD the indexes

2.) Invoke the SQL Tuning and Optimizing Plans through the SQL Server Profiler and Database Engine Tuning Adviser

Based on those recommendations we applied the following Indexes to our Clients Database (see 2nd snippet). The results were spectacular. Queries that were taking 30 minutes went down to 20-30 seconds.

Here is the problem. For some reason these indexes lose their effectiveness during normal daily operations at our Clients. At some clients we run the Database Maintenance Plans every night. Then 2 to 3 hours into a normal work day the query reverts back to its original 30 minute timing. The only way to correct the problem is run the SQL query stand alone (see 2nd snippet) during the day. That solves the problem immediately but it is not a solution. The problem happens on SQL 2005, SQL 2008 and SQL 2008 R2. Servers are top of the line. More and more of these servers are Server 2008 R2 with 16 Gig of RAM. My concern is when customer buys new hardware the performance reverts back to before the upgrade. If anything is should be much faster.

The questions are as follows:

1.) What could be causing the Index degradation so early in the day when the Maintenance Plans were just complete the night before?

2.) Why do we need to run the Maintenance Plans so often. Is this common?

3.) What file checks could we be missing that I can implement to solve this problem?

4.) Are there any settings I can put in SQL and that we have overlooked?

5.) In the Database Maintenance Plans should we select both the REBUILD and REORGANIZATION option or just the REBUILD option? I read that if you select both it is self defeating. Is that correct?

6.) Can something be setup in SQL that can automatically correct the index if it detects a problem with the integrity of that Index instead having to run the script stand alone or wait until the Database Maintenance Plan kicks in at its appropriate time.

Any ideas or suggestions would be greatly appreciated
Thanks
Greg

insert INTO SAGrossMargin (
Company_Number,
Company_Name,
Customer_BillToNumber ,
AsgnBranch_Code_Detail ,
Job_Office ,
Invoice_Number ,
Invoice_Date ,
Check_Number ,
Check_Date ,
void,
Invoice_Type ,
Transaction_Type ,
Pay_Code_Type ,
Bill_Hours ,
Bill_Rate ,
Pay_Hours ,
Pay_Rate ,
Transaction_Cost ,
Transaction_Taxable ,
Transaction_Billable ,
Deduction_Amount_Employer ,
US_FICA_Burden ,
US_MED_Burden ,
US_FUI_Burden ,
STATE_SUI_Burden ,
WC_Amount ,
Job_Id ,
T_APPLICANT_ASSIGNMENT ,
JOB_PC ,
CUST_TPC ,
DET_RPC ,
DET_TPC ,
Assignment_PeriodEndingDate ,
Period_StartDate ,
Period_EndDate ,
Job_Position ,
PO_Number ,
T_INVOICE_COMPANY_NAME ,
Applicant_Name ,
CUST_SALESPC ,
AsgnLineOfBusiness_Detail ,
Bill_To_Sort ,
Bill_Amount ,
Pay_Amount ,
Federal_Burden ,
Pay_Code_TaxableType ,
Additional_Burden ,
Applicant_Empl_Number ,
Start_Date ,
End_Date ,
Col1_Name ,
Burden ,
Margin ,
Markup ,
PC ,
PC_Name ,
Branch_Name,
Payroll_Cost ,
Gross_Margin,
DiscountAmount_Detail,
WC_Code ,
WC_State ,
Vendor1099 ,
VendorOnPremises,
AsgnLineOfBusiness_Desc
)

SELECT TOP 100 PERCENT
dbo.uboT_TB_PAY_HEADER_ARCHIVE.Company_Number,
Company_Name = (SELECT top 1 COMP_NAME FROM dbo.T_COMPANY WHERE dbo.uboT_TB_PAY_HEADER_ARCHIVE.Company_Number = dbo.T_COMPANY.Comp_Number),
dbo.uboT_TB_PAY_HEADER_ARCHIVE.Customer_BillToNumber,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.AsgnBranch_Code_Detail,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.Job_Office,
dbo.uboT_TB_ARCHIVE_DETAIL.Invoice_Number,
dbo.uboT_TB_ARCHIVE_DETAIL.Invoice_Date,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Check_Number,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Check_Date,
void=isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0),
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Invoice_Type,
--dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Transaction_Type,
Transaction_Type=
CASE
when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Record_Status,0)='CHECK_VOID' then rtrim(ltrim(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Transaction_Type)) + ' void'
when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Deduction_Amount_Employer,0)<>0 then rtrim(ltrim(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Deduction_Code))
else dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Transaction_Type
end,

dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Code_Type,
--Bill_Hours=isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Hours,0),
Bill_Hours=
case
when isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Rate,0)=0 then 0
when isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Pay_Code_Type,'')='H' then isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Hours,0)
end,

Bill_Rate=isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Rate,0),

--Pay_Hours = CASE
--when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 then 0
--else isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Hours,0)
--end,

Pay_Hours = CASE
when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 then 0
when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0)=0 then 0
when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Code_Type,'')='H' then isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Hours,0)
else 0
end,

Pay_Rate = CASE
when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 then 0
else isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0)
end,

dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Transaction_Cost,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Transaction_Taxable,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Transaction_Billable,
Deduction_Amount_Employer=isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Deduction_Amount_Employer,0),
US_FICA_Burden=ISNULL(round(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Employer_Federal_Tax,2),0),
US_MED_Burden=ISNULL(round(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Employer_Local_Tax,2),0),
US_FUI_Burden=ISNULL(round(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Employee_Federal_Tax,2),0),

STATE_SUI_Burden = CASE
WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 THEN 0
ELSE ISNULL(round(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Employer_State_Tax,2),0)
END ,

WC_Amount = CASE
WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 THEN 0
ELSE isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.WC_Amount,0)
END,

dbo.uboT_TB_PAY_HEADER_ARCHIVE.Job_Id,
dbo.T_APPLICANT_ASSIGNMENT.ASS_PC,
dbo.T_JOB_ORDER.JOB_PC,

--dbo.T_CUSTOMER.CUST_TPC,
--dbo.T_APPLICANT_DETAILS.DET_RPC,
--dbo.T_APPLICANT_DETAILS.DET_TPC,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.CUST_TPC,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.DET_RPC,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.DET_TPC,

dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Assignment_PeriodEndingDate,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Period_StartDate,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Period_EndDate,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.Job_Position,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.PO_Number,
dbo.T_BILL_CUST.T_INVOICE_COMPANY_NAME,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.Applicant_Name,
--dbo.T_CUSTOMER.CUST_SALESPC,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.CUST_SALESPC,
dbo.uboT_TB_PAY_DETAIL_ARCHIVE.AsgnLineOfBusiness_Detail,
( dbo.T_BILL_CUST.T_INVOICE_COMPANY_NAME )+ str(dbo.uboT_TB_PAY_HEADER_ARCHIVE.Customer_BillToNumber) As Bill_To_Sort,

--Bill_Amount = CASE
--WHEN isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Pay_Code_Type,'H')='F' then isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Rate,0)-isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0)
--else round(((isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Hours,0)*isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Rate,0))-isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0)),2)
--END,

Bill_Amount = CASE
--WHEN isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Pay_Code_Type,'H')='F' then isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Rate,0)-isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0)
WHEN isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Pay_Code_Type,'H')='F' then isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Rate,0)-(isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0)+isnull(dbo.uboT_TB_ARCHIVE_DETAIL.VolDiscAmount,0))

else
round(cast(
isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Hours,0)
*
isnull(dbo.uboT_TB_ARCHIVE_DETAIL.Bill_Rate,0)
as money
)
-- -isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0)
-(isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0)+isnull(dbo.uboT_TB_ARCHIVE_DETAIL.VolDiscAmount,0))
,2)
END,

--Pay_Amount = CASE
--WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 THEN 0
--WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Code_Type,'H')='F' then isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0)
--else round(isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Hours,0)*isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0),2)
--END,

Pay_Amount = CASE
when isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 then 0
WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Code_Type,'H')='F' then isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0)
else round(cast(isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Hours,0)*isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0)as money),2)
END,

Federal_Burden = CASE
WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 THEN 0
ELSE round(isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Employer_Federal_Tax,0)+isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Employer_Local_Tax,0)+isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Employee_Federal_Tax,0),2)
END,

Pay_Code_TaxableType=isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Code_TaxableType,''),

Additional_Burden=case
WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Void,0)=1 THEN 0
WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Hours,0)=0 and isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Code_TaxableType,'T')='T' then round((@AddBurden *isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0)),2)
WHEN isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Code_TaxableType,'T')='T' then round(@AddBurden *(isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Hours,0)*isnull(dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Pay_Rate,0)),2)
else 0
end,

dbo.uboT_TB_PAY_HEADER_ARCHIVE.Applicant_Empl_Number,
--Empl_Count = DBO.ufabd_TEST_EMPL_COUNT(1, '01/01/05', '12/31/05'),
Start_Date=@FromPerEnddate,
End_Date=@ToPerEnddate,
Col1_Name=dbo.uboT_TB_PAY_HEADER_ARCHIVE.Applicant_Name,
Burden=@AddBurden,
Margin=0,
Markup=0,

--WHEN (LEN(RTRIM(LTRIM(dbo.uboT_PR_CHECK_DETAIL_MASTER.Pay_Code))) >0) THEN (RTRIM(LTRIM(dbo.uboT_PR_CHECK_DETAIL_MASTER.Pay_Code)))

PC=case
when @PC='C' then (RTRIM(LTRIM(isnull(dbo.uboT_TB_PAY_HEADER_ARCHIVE.CUST_TPC,''))))
when @PC='S' then (RTRIM(LTRIM(isnull(dbo.uboT_TB_PAY_HEADER_ARCHIVE.CUST_SALESPC,''))))
when @PC='T' then (RTRIM(LTRIM(isnull(dbo.uboT_TB_PAY_HEADER_ARCHIVE.DET_TPC,''))))
when @PC='R' then (RTRIM(LTRIM(isnull(dbo.uboT_TB_PAY_HEADER_ARCHIVE.DET_RPC,''))))
when @PC='A' then (RTRIM(LTRIM(isnull(dbo.T_APPLICANT_ASSIGNMENT.ASS_PC,''))))
when @PC='J' then (RTRIM(LTRIM(isnull(dbo.T_JOB_ORDER.JOB_PC,''))))
END,

PC_Name=case
when @PC='C' then (SELECT top 1 REF_DESCRIPTION FROM T_REF_ALL_CODES WHERE REF_CATEGORY = 'PC' AND REF_CODE = dbo.uboT_TB_PAY_HEADER_ARCHIVE.CUST_TPC)
when @PC='S' then (SELECT top 1 REF_DESCRIPTION FROM T_REF_ALL_CODES WHERE REF_CATEGORY = 'PC' AND REF_CODE = dbo.uboT_TB_PAY_HEADER_ARCHIVE.CUST_SALESPC)
when @PC='T' then (SELECT top 1 REF_DESCRIPTION FROM T_REF_ALL_CODES WHERE REF_CATEGORY = 'PC' AND REF_CODE = dbo.uboT_TB_PAY_HEADER_ARCHIVE.DET_TPC)
when @PC='R' then (SELECT top 1 REF_DESCRIPTION FROM T_REF_ALL_CODES WHERE REF_CATEGORY = 'PC' AND REF_CODE = dbo.uboT_TB_PAY_HEADER_ARCHIVE.DET_RPC)
when @PC='A' then (SELECT top 1 REF_DESCRIPTION FROM T_REF_ALL_CODES WHERE REF_CATEGORY = 'PC' AND REF_CODE = dbo.T_APPLICANT_ASSIGNMENT.ASS_PC)
when @PC='J' then (SELECT top 1 REF_DESCRIPTION FROM T_REF_ALL_CODES WHERE REF_CATEGORY = 'PC' AND REF_CODE = dbo.T_JOB_ORDER.JOB_PC)
END,
Branch_Name=(SELECT top 1 OFF_DESCRIPTION FROM dbo.T_COMPANY_OFFICE WHERE COMP_NUMBER = @CmpNum AND OFF_NUMBER = dbo.uboT_TB_PAY_DETAIL_ARCHIVE.AsgnBranch_Code_Detail),

Payroll_Cost=0 ,
Gross_Margin=0 ,
--DiscountAmount_Detail=isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0),
DiscountAmount_Detail=round(isnull(dbo.uboT_TB_ARCHIVE_DETAIL.DiscountAmount_Detail,0)+ isnull(dbo.uboT_TB_ARCHIVE_DETAIL.VolDiscAmount,0),2),

dbo.uboT_TB_PAY_HEADER_ARCHIVE.Job_WC_Code,
dbo.uboT_TB_PAY_HEADER_ARCHIVE.Job_WC_State,
Vendor1099=isnull(dbo.uboT_PR_EMPLOYEE.Vendor1099,0),
VendorOnPremises=isnull(dbo.uboT_PR_EMPLOYEE.VendorOnPremises,0),
AsgnLineOfBusiness_Desc=(select top 1 LineOfBusinessDesc from dbo.uboT_REF_LINEOFBUSINESSCODES where dbo.uboT_TB_PAY_DETAIL_ARCHIVE.AsgnLineOfBusiness_Detail=LineOfBusinessCode)

FROM dbo.uboT_TB_PAY_DETAIL_ARCHIVE

LEFT OUTER JOIN
dbo.uboT_TB_ARCHIVE_DETAIL ON dbo.uboT_TB_PAY_DETAIL_ARCHIVE.GUID = dbo.uboT_TB_ARCHIVE_DETAIL.GUID

LEFT OUTER JOIN
dbo.uboT_TB_PAY_HEADER_ARCHIVE on dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Parent_GUID = dbo.uboT_TB_PAY_HEADER_ARCHIVE.GUID
LEFT OUTER JOIN
dbo.T_APPLICANT_ASSIGNMENT ON dbo.uboT_TB_PAY_DETAIL_ARCHIVE.Assignment_Id = dbo.T_APPLICANT_ASSIGNMENT.ASS_ID
LEFT OUTER JOIN
dbo.T_JOB_ORDER ON dbo.uboT_TB_PAY_HEADER_ARCHIVE.Job_Id = dbo.T_JOB_ORDER.JOB_ID
LEFT OUTER JOIN
--dbo.T_CUSTOMER ON dbo.uboT_TB_PAY_HEADER_ARCHIVE.Customer_Id = dbo.T_CUSTOMER.CUST_ID
--LEFT OUTER JOIN
--dbo.T_APPLICANT_DETAILS ON dbo.uboT_TB_PAY_HEADER_ARCHIVE.Applicant_Id = dbo.T_APPLICANT_DETAILS.APPL_ID
--LEFT OUTER JOIN
dbo.T_BILL_CUST ON dbo.uboT_TB_PAY_HEADER_ARCHIVE.Customer_BillToNumber = dbo.T_BILL_CUST.T_BILL_TO
LEFT OUTER JOIN
dbo.uboT_PR_EMPLOYEE ON dbo.uboT_TB_PAY_HEADER_ARCHIVE.Applicant_Empl_Number = dbo.uboT_PR_EMPLOYEE.EmployeeNumber

-- Title : 9931_sql_prime_database_GrossMargin_2005.sql
-- Author : Greg Wollman - Automated Business Desgins, Inc.
-- Date : 9/18/2008 7:58 AM
-- Purpose : Used to make Gross Margin Reports more efficient
--

/****** Object: Index [_dta_index_uboT_TB_PAY_DETAIL_ARCHIVE_6_852198086__K5_K38_K1_K2_K39_6_7_9_10_11_12_14_15_18_19_20_28_29_31_49_50_51_62_63_65_] Script Date: 09/10/2008 13:17:07 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[uboT_TB_PAY_DETAIL_ARCHIVE]') AND name = N'_dta_index_uboT_TB_PAY_DETAIL_ARCHIVE_6_852198086__K5_K38_K1_K2_K39_6_7_9_10_11_12_14_15_18_19_20_28_29_31_49_50_51_62_63_65_')
DROP INDEX [_dta_index_uboT_TB_PAY_DETAIL_ARCHIVE_6_852198086__K5_K38_K1_K2_K39_6_7_9_10_11_12_14_15_18_19_20_28_29_31_49_50_51_62_63_65_] ON [dbo].[uboT_TB_PAY_DETAIL_ARCHIVE] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_uboT_TB_PAY_DETAIL_ARCHIVE_6_852198086__K5_K38_K1_K2_K39_6_7_9_10_11_12_14_15_18_19_20_28_29_31_49_50_51_62_63_65_] ON [dbo].[uboT_TB_PAY_DETAIL_ARCHIVE]
(
[Assignment_PeriodEndingDate] ASC,
[AsgnBranch_Code_Detail] ASC,
[Parent_GUID] ASC,
[GUID] ASC,
[AsgnLineOfBusiness_Detail] ASC
)
INCLUDE ( [Invoice_Type],
[Transaction_Type],
[Period_StartDate],
[Period_EndDate],
[Pay_Hours],
[Pay_Rate],
[Bill_Rate],
[Employee_Federal_Tax],
[Employer_Federal_Tax],
[Employer_State_Tax],
[Employer_Local_Tax],
[Check_Number],
[Check_Date],
[Record_Status],
[Transaction_Taxable],
[Transaction_Billable],
[Transaction_Cost],
[PO_Number],
[Pay_Code_Type],
[Pay_Code_TaxableType],
[Deduction_Code],
[Deduction_Amount_Employer],
[Void],
[WC_Amount]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_uboT_TB_ARCHIVE_DETAIL_6_2110630562__K2_13_14_25_26_43_63_87] Script Date: 09/10/2008 13:19:05 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[uboT_TB_ARCHIVE_DETAIL]') AND name = N'_dta_index_uboT_TB_ARCHIVE_DETAIL_6_2110630562__K2_13_14_25_26_43_63_87')
DROP INDEX [_dta_index_uboT_TB_ARCHIVE_DETAIL_6_2110630562__K2_13_14_25_26_43_63_87] ON [dbo].[uboT_TB_ARCHIVE_DETAIL] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_uboT_TB_ARCHIVE_DETAIL_6_2110630562__K2_13_14_25_26_43_63_87] ON [dbo].[uboT_TB_ARCHIVE_DETAIL]
(
[GUID] ASC
)
INCLUDE ( [Bill_Hours],
[Bill_Rate],
[Invoice_Number],
[Invoice_Date],
[DiscountAmount_Detail],
[Pay_Code_Type],
[VolDiscAmount]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_uboT_TB_PAY_HEADER_ARCHIVE_6_820197972__K1_K6_K63_K4_K18_K28_K2_K27_19_20_21_26_29] Script Date: 09/10/2008 13:21:04 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[uboT_TB_PAY_HEADER_ARCHIVE]') AND name = N'_dta_index_uboT_TB_PAY_HEADER_ARCHIVE_6_820197972__K1_K6_K63_K4_K18_K28_K2_K27_19_20_21_26_29')
DROP INDEX [_dta_index_uboT_TB_PAY_HEADER_ARCHIVE_6_820197972__K1_K6_K63_K4_K18_K28_K2_K27_19_20_21_26_29] ON [dbo].[uboT_TB_PAY_HEADER_ARCHIVE] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_uboT_TB_PAY_HEADER_ARCHIVE_6_820197972__K1_K6_K63_K4_K18_K28_K2_K27_19_20_21_26_29] ON [dbo].[uboT_TB_PAY_HEADER_ARCHIVE]
(
[GUID] ASC,
[Customer_BillToNumber] ASC,
[Company_Number] ASC,
[Customer_Id] ASC,
[Job_Id] ASC,
[Applicant_Empl_Number] ASC,
[Assignment_Id] ASC,
[Applicant_Id] ASC
)
INCLUDE ( [Job_Position],
[Job_WC_Code],
[Job_WC_State],
[Job_Office],
[Applicant_Name]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_uboT_PR_EMPLOYEE_6_1714873226__K5_K1_59_62] Script Date: 09/10/2008 13:22:48 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[uboT_PR_EMPLOYEE]') AND name = N'_dta_index_uboT_PR_EMPLOYEE_6_1714873226__K5_K1_59_62')
DROP INDEX [_dta_index_uboT_PR_EMPLOYEE_6_1714873226__K5_K1_59_62] ON [dbo].[uboT_PR_EMPLOYEE] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_uboT_PR_EMPLOYEE_6_1714873226__K5_K1_59_62] ON [dbo].[uboT_PR_EMPLOYEE]
(
[EmployeeNumber] ASC,
[GUID] ASC
)
INCLUDE ( [Vendor1099],
[VendorOnPremises]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_T_APPLICANT_DETAILS_6_2105058535__K1_K8_K36] Script Date: 09/10/2008 13:24:11 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T_APPLICANT_DETAILS]') AND name = N'_dta_index_T_APPLICANT_DETAILS_6_2105058535__K1_K8_K36')
DROP INDEX [_dta_index_T_APPLICANT_DETAILS_6_2105058535__K1_K8_K36] ON [dbo].[T_APPLICANT_DETAILS] WITH ( ONLINE = OFF )

CREATE NONCLUSTERED INDEX [_dta_index_T_APPLICANT_DETAILS_6_2105058535__K1_K8_K36] ON [dbo].[T_APPLICANT_DETAILS]
(
[APPL_ID] ASC,
[DET_TPC] ASC,
[DET_RPC] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_T_APPLICANT_ASSIGNMENT_6_2041058307__K1_K22] Script Date: 09/10/2008 13:25:33 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T_APPLICANT_ASSIGNMENT]') AND name = N'_dta_index_T_APPLICANT_ASSIGNMENT_6_2041058307__K1_K22')
DROP INDEX [_dta_index_T_APPLICANT_ASSIGNMENT_6_2041058307__K1_K22] ON [dbo].[T_APPLICANT_ASSIGNMENT] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_T_APPLICANT_ASSIGNMENT_6_2041058307__K1_K22] ON [dbo].[T_APPLICANT_ASSIGNMENT]
(
[ASS_ID] ASC,
[ASS_PC] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_T_JOB_ORDER_6_725577623__K2_K6] Script Date: 09/10/2008 13:27:16 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T_JOB_ORDER]') AND name = N'_dta_index_T_JOB_ORDER_6_725577623__K2_K6')
DROP INDEX [_dta_index_T_JOB_ORDER_6_725577623__K2_K6] ON [dbo].[T_JOB_ORDER] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_T_JOB_ORDER_6_725577623__K2_K6] ON [dbo].[T_JOB_ORDER]
(
[JOB_ID] ASC,
[JOB_PC] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_SAGrossMargin_6_778042849__K41_K4_K55_1_3_14_16_43_44_48_58_59] Script Date: 09/10/2008 13:28:52 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SAGrossMargin]') AND name = N'_dta_index_SAGrossMargin_6_778042849__K41_K4_K55_1_3_14_16_43_44_48_58_59')
DROP INDEX [_dta_index_SAGrossMargin_6_778042849__K41_K4_K55_1_3_14_16_43_44_48_58_59] ON [dbo].[SAGrossMargin] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_SAGrossMargin_6_778042849__K41_K4_K55_1_3_14_16_43_44_48_58_59] ON [dbo].[SAGrossMargin]
(
[AsgnLineOfBusiness_Detail] ASC,
[AsgnBranch_Code_Detail] ASC,
[PC] ASC
)
INCLUDE ( [Company_Number],
[Customer_BillToNumber],
[Bill_Hours],
[Pay_Hours],
[Bill_Amount],
[Pay_Amount],
[Applicant_Empl_Number],
[Payroll_Cost],
[Gross_Margin]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_SAGrossMargin_6_778042849__K44_K43] Script Date: 09/10/2008 13:29:57 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SAGrossMargin]') AND name = N'_dta_index_SAGrossMargin_6_778042849__K44_K43')
DROP INDEX [_dta_index_SAGrossMargin_6_778042849__K44_K43] ON [dbo].[SAGrossMargin] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_SAGrossMargin_6_778042849__K44_K43] ON [dbo].[SAGrossMargin]
(
[Pay_Amount] ASC,
[Bill_Amount] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_T_CUSTOMER_6_309576141__K1_K12_K14] Script Date: 09/10/2008 13:30:52 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T_CUSTOMER]') AND name = N'_dta_index_T_CUSTOMER_6_309576141__K1_K12_K14')
DROP INDEX [_dta_index_T_CUSTOMER_6_309576141__K1_K12_K14] ON [dbo].[T_CUSTOMER] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_T_CUSTOMER_6_309576141__K1_K12_K14] ON [dbo].[T_CUSTOMER]
(
[CUST_ID] ASC,
[CUST_TPC] ASC,
[CUST_SALESPC] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_T_BILL_CUST_6_213575799__K1_K2] Script Date: 09/10/2008 13:32:09 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T_BILL_CUST]') AND name = N'_dta_index_T_BILL_CUST_6_213575799__K1_K2')
DROP INDEX [_dta_index_T_BILL_CUST_6_213575799__K1_K2] ON [dbo].[T_BILL_CUST] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_T_BILL_CUST_6_213575799__K1_K2] ON [dbo].[T_BILL_CUST]
(
[T_BILL_TO] ASC,
[T_INVOICE_COMPANY_NAME] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/****** Object: Index [_dta_index_T_REF_ALL_CODES_6_869578136__K1_K2_3] Script Date: 09/10/2008 13:33:43 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T_REF_ALL_CODES]') AND name = N'_dta_index_T_REF_ALL_CODES_6_869578136__K1_K2_3')
DROP INDEX [_dta_index_T_REF_ALL_CODES_6_869578136__K1_K2_3] ON [dbo].[T_REF_ALL_CODES] WITH ( ONLINE = OFF )
go

CREATE NONCLUSTERED INDEX [_dta_index_T_REF_ALL_CODES_6_869578136__K1_K2_3] ON [dbo].[T_REF_ALL_CODES]
(
[REF_CATEGORY] ASC,
[REF_CODE] ASC
)
INCLUDE ( [REF_DESCRIPTION]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 16:41:32

Indexes typically don't "lose" their effectiveness. The query optimizer looks at statistics, and based on that, uses indexes it determines are more efficient (or not). What is more likely, especially on 64-bit servers, is your procedure cache is getting bloated with ad-hoc or other query plans and is taking away from data cache. You can check for ad-hoc plans with Glenn Berry's query:

http://sqlserverperformance.wordpress.com/2010/04/15/a-dmv-a-day-%E2%80%93-day-16/

You can confirm this in 2 ways:

1) it rarely or never happens on 32-bit SQL Server instances, or is not as severe
2) run DBCC FREEPROCCACHE on the SQL Server and see if performance improves, then degrades again (usually takes 15-30 minutes to notice)

If you're not using stored procedures or sp_executesql, you should consider rewriting your code to do so. This will improve execution plan reuse and reduce the number of ad-hoc plans in cache. There's a server setting on SQL 2008 called "optimize for ad-hoc workloads" which should help if you absolutely can't rewrite your SQL.

If it turns out NOT to be procedure cache bloat, see if running UPDATE STATISTICS on the affected tables improves performance. Also check if auto-update statistics is turned on for your database, as well as auto_stats_update_async. I would suggest just doing an index reorg rather than a rebuild, at least nightly. You can do weekly or monthly index rebuilds if you want to be safe, but nightly is overkill.

Granted, there's some SQL tuning that can be done with your query, but I think you should check the other things I mentioned first.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-20 : 18:30:18

quote:
/****** Object: Index [_dta_index_

Oh no, someone used DTA, and I bet they just blanket accepted recommendations. Sure looks like it.
You probably have way too many, way too wide indexes as a result. Anyone there who knows index tuning? Any budget to get a consultant in to fix that mess?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources