Maybe you can answer this after the holiday buy why should I drop the indexes that you specified?

Also, I thought that the BeginDate was supposed to the first column in...

Scott,

Please see attach Excel file.

Unfortunately I had a hard time interpreting what you were telling me to do.

Thank you for all of your you help.

Dumb question.

How do you attach a file? :blush:

How can you read it?

identDb_Namecapture_datesize_rankalloc_mbrow_countTable_Nameequality_columnsinequality_columnsincluded_columnsuser_seeksuser_scansmax_days_activeunique_compileslast_user_seeklast_user_scanavg_total_user_costavg_user_impactsystem_seekssystem_scanslast_system_seeklast_system_scanavg_total_system_costavg_system_impactstatementobject_idindex_handle

1PrismData2015-11-25218277.02.4MtblCallNULL[Call_Date][Call_ID], [Insurer_ID], [Loss_Zip], [Type_ID], [Has_Been_Cancelled], [singleTradeListID]2007212015-11-20 16:30:33.803NULL20.776986285191274.100NULLNULL00[PrismData].[dbo].[tblCall]1301175981119485

2PrismData2015-11-25218277.02.4MtblCallNULL[Call_Date], [CC_CATCode][Call_ID], [Insurer_ID], [Loss_State], [CONTR_ID], [Contr_Assigned_Date], [PRISM_Frst_Est_Date], [Has_Been_Cancelled], [singleTradeListID], [CATAssignment]120792015-11-25 08:39:13.687NULL223.9263207206996.9900NULLNULL00[PrismData].[dbo].[tblCall]13011759815959

3PrismData2015-11-25218277.02.4MtblCallNULL[Call_Date], [CC_CATCode]NULL10712015-11-24 13:44:02.143NULL232.70978078034798.7100NULLNULL00[PrismData].[dbo].[tblCall]1301175981274312

4PrismData2015-11-25218277.02.4MtblCallNULL[Call_Date], [CSR_Name][Call_ID], [Insurer_ID], [CSR_ID]70772015-11-24 23:00:00.343NULL452.59593332283398.3800NULLNULL00[PrismData].[dbo].[tblCall]13011759812650

5PrismData2015-11-25218277.02.4MtblCallNULL[CallCenterRepAssigned][callDateFull]10712015-11-25 12:00:04.467NULL7.256623692121775.5400NULLNULL00[PrismData].[dbo].[tblCall]1301175981331537

6PrismData2015-11-25218277.02.4MtblCallNULL[CC_CATCode][Loss_State]3407102015-11-25 08:40:46.200NULL455.27458359421598.9500NULLNULL00[PrismData].[dbo].[tblCall]1301175981274407

7PrismData2015-11-25218277.02.4MtblCallNULL[Claim][Call_ID], [callDateFull]4831077402015-11-25...

The following is the structure of the table:

CREATE TABLE [dbo].[xactControlPoint](

[xactControlPointID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[xactControlPointTypeID] [int] NOT NULL,

[call_id] [int] NOT NULL,

[contr_id] [int] NULL,

[xactTransactionIDValue] [varchar](50) NULL,

[beginDate] [datetime] NOT NULL,

[userName]...

Sergiy (11/24/2015)

---

Welsh Corgi (11/24/2015)

---

I have three tables that are very large and have too many indexes.

I can easily archive the tables without running into the risk of having to tune...

ScottPletcher (11/24/2015)

---

For us, most tables don't really need partitioned. Archiving and partitioned views handles most of them.

When I do partition, I align indexes because I want to be able...

ScottPletcher (11/24/2015)

---

For us, most tables don't really need partitioned. Archiving and partitioned views handles most of them.

When I do partition, I align indexes because I want to be able...

I read a lot of articles but I'm not

sure what indexes need to be aligned or unaligned?

What factors do you take into consideration?

ScottPletcher (11/24/2015)

---

Make sure you have the best clustered index on every table. That is critical for best performance overall, and the wrong clustering key often leads to extra indexes....

Listed below is the entire script.

I suspect that it is flawed.

CREATE PARTITION FUNCTION Test_monthlyDateRange_pf (smalldatetime)

AS RANGE FOR VALUES

('2008-01-01', '2008-02-01','2008-03-01')

Create Partition Scheme test_monthlyDateRange_ps

As Partition test_monthlyDateRange_pf

...

GilaMonster (11/24/2015)

---

You're missing a space. The partition function is named Yearly_Date_Range_pf, and you left out...

The create Create Partition Scheme fails.

CREATE PARTITION FUNCTION Yearly_Date_Range_pf (Smalldatetime)

AS RANGE RIGHT FOR VALUES

(N'19301231',N'19401231',N'19971231',N'19981231',N'19991231', N'20011231',N'20021231',N'20031231', N'20041231',N'20051231',N'20061231',N'20071231',N'20081231',N'20091231',N'20101231',

N'20111231',N'20121231', N'20131231',N'20141231',N'20151231')

GO

Create Partition Scheme Yearly_Date_Range_ps

As PartitionYearly_Date_Range_pf

...

Thanks!

This is itL

CREATE PARTITION FUNCTION Yearly_Date_Range_pf (Smalldatetime)

AS RANGE LEFT FOR VALUES

(N'19301231',N'19401231',N'19971231',N'19981231',N'19991231', N'20011231',N'20021231',N'20031231', N'20041231',N'20051231',N'20061231',N'20071231',N'20081231',N'20091231',N'20101231',

N'20111231',N'20121231', N'20131231',N'20141231',N'20151231')

As Partition YearlyDateRange_pf

All To ([Primary]);

Create Partition Scheme Yearly_Date_Range_ps

...

As far as the following:

Second, the CREATE PARTITION SCHEME statement references a test_monthlyDateRange_pf function, not the YearlyDateRange_pf created by the first statement.

That was a copy and paste bolo.

I created the...