Showing posts with label working. Show all posts
Showing posts with label working. Show all posts

Monday, March 26, 2012

index question

We have a stored procedure that stopped working. I put it in the debugger and
discovered that the update statement takes forever - I stopped it after 2
minutes. This is the update statement: UPDATE tbltelemarketing SET download =
'2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
column. When I do an execution plan on the query, it doesn't use the index on
the id column. It does a clustered index scan using an index on several
columns. The table keeps growing so I guess at some point, it stopped using
the ID index.
Is there a way to force it to use the ID index? Any other ideas on how I can
speed up the query. The table has about 11 million rows. Here is the schema
and indexes.
CREATE TABLE [dbo].[tbltelemarketing] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL ,
[prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_mail] [int] NULL ,
[bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[drop_date] [smalldatetime] NULL ,
[hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pest_cncl] [smalldatetime] NULL ,
[term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[term_cncl] [smalldatetime] NULL ,
[promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[download] [smalldatetime] NULL ,
[recd_date] [smalldatetime] NULL ,
[mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[recd_week] [int] NULL ,
[live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_lns] [int] NULL ,
[branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reassign_date] [datetime] NULL ,
[company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_date] [smalldatetime] NULL ,
[update_cd] [int] NULL ,
[update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
[dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
[PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
[promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
[promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
[hh_score2]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_3] ON
[dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
[delete_cd], [live], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
ON [PRIMARY]
GO
Thanks,
Dan D.
1) Are your statistics on the table are current
2) Why i don't see the unique keywork on ID index and other schema question
if ID is identity why can't it be primary key with non-clustered index
"Dan D." wrote:

> We have a stored procedure that stopped working. I put it in the debugger and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I can
> speed up the query. The table has about 11 million rows. Here is the schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.
|||You can use an index hint when you want to force SQL Server to use an index.
You can search Books Online (within the SQL Server program group) for INDEX
HINT. The general usage is:
..
FROM tbltelemarketing WITH (INDEX (nci_tbltelemarketing_ID)) WHERE ID =
10617138
I assume that ID is the primary key on the table...yet you don't define it
as a primary key constraint. Perhaps you should define a primary key on the
table.
Keith
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2A769066-CBE1-4391-8900-02E825BE30EA@.microsoft.com...
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET
download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped
using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I
can
> speed up the query. The table has about 11 million rows. Here is the
schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date])
ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON
[dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON
[PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON
[dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.
|||Dan,
The current index on ID is non-unique. If the ID column is in fact the
identifying column, then make sure you create a unique index on it. Or
even better, to create a Primary Key constraint on it (which will
automatically create a unique index on it).
HTH,
Gert-Jan
Dan D. wrote:
> We have a stored procedure that stopped working. I put it in the debugger and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I can
> speed up the query. The table has about 11 million rows. Here is the schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
<snip>
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.
|||I did update the statistics. You don't see the "unique" keyword because I was
trying different indexes with different names and the one you see was the
last one I tried. At one time it was primary with non-clustered index.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema question
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
|||It was a primary index at one time. When it stopped working I changed it in
the process of testing.
"Gert-Jan Strik" wrote:

> Dan,
> The current index on ID is non-unique. If the ID column is in fact the
> identifying column, then make sure you create a unique index on it. Or
> even better, to create a Primary Key constraint on it (which will
> automatically create a unique index on it).
> HTH,
> Gert-Jan
>
> Dan D. wrote:
> <snip>
>
|||Problem solved. I was looking at the development server but querying the
production server. There was no index on production. Now that there is, it
works. Wonders never cease.
Thanks to all for your help.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema question
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:

index question

We have a stored procedure that stopped working. I put it in the debugger an
d
discovered that the update statement takes forever - I stopped it after 2
minutes. This is the update statement: UPDATE tbltelemarketing SET download
=
'2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
column. When I do an execution plan on the query, it doesn't use the index o
n
the id column. It does a clustered index scan using an index on several
columns. The table keeps growing so I guess at some point, it stopped using
the ID index.
Is there a way to force it to use the ID index? Any other ideas on how I can
speed up the query. The table has about 11 million rows. Here is the schema
and indexes.
CREATE TABLE [dbo].[tbltelemarketing] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[update_date] [smalldatetime] NULL ,
[prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[id_mail] [int] NULL ,
[bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[drop_date] [smalldatetime] NULL ,
[hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[pest_cncl] [smalldatetime] NULL ,
[term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[term_cncl] [smalldatetime] NULL ,
[promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[download] [smalldatetime] NULL ,
[recd_date] [smalldatetime] NULL ,
[mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[recd_week] [int] NULL ,
[live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
[propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[id_lns] [int] NULL ,
[branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
[active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
[reassign_date] [datetime] NULL ,
[company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_date] [smalldatetime] NULL ,
[update_cd] [int] NULL ,
[update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
[dbo].[tbltelemarketing]([delete_cd], [live], [promo_cod
e], [drop_date]) ON
[PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing
]([live],
[promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing
]([branch],
[promo_code], [drop_date], [delete_cd], [live], [prop_zi
p5], [prop_zip4],
[hh_score2]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_3] ON
[dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [prom
o_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing
]([branch],
[delete_cd], [live], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketi
ng]([ID])
ON [PRIMARY]
GO
Thanks,
--
Dan D.1) Are your statistics on the table are current
2) Why i don't see the unique keywork on ID index and other schema question
if ID is identity why can't it be primary key with non-clustered index
"Dan D." wrote:

> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET downloa
d =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped usin
g
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I c
an
> speed up the query. The table has about 11 million rows. Here is the schem
a
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_C
I_AS NULL ,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_C
I_AS NULL ,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_C
I_AS NULL ,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_c
ode], [drop_date]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarket
ing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarket
ing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_
zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [pr
omo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarket
ing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemark
eting]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||You can use an index hint when you want to force SQL Server to use an index.
You can search Books Online (within the SQL Server program group) for INDEX
HINT. The general usage is:
.
FROM tbltelemarketing WITH (INDEX (nci_tbltelemarketing_ID)) WHERE ID =
10617138
I assume that ID is the primary key on the table...yet you don't define it
as a primary key constraint. Perhaps you should define a primary key on the
table.
Keith
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2A769066-CBE1-4391-8900-02E825BE30EA@.microsoft.com...
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET
download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped
using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I
can
> speed up the query. The table has about 11 million rows. Here is the
schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], &#
91;drop_date])
ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarket
ing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON
[dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_
zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code
]) ON
[PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON
[dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemark
eting]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||Dan,
The current index on ID is non-unique. If the ID column is in fact the
identifying column, then make sure you create a unique index on it. Or
even better, to create a Primary Key constraint on it (which will
automatically create a unique index on it).
HTH,
Gert-Jan
Dan D. wrote:
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET downloa
d =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped usin
g
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I c
an
> speed up the query. The table has about 11 million rows. Here is the schem
a
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
<snip>
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemark
eting]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||I did update the statistics. You don't see the "unique" keyword because I wa
s
trying different indexes with different names and the one you see was the
last one I tried. At one time it was primary with non-clustered index.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema questio
n
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
>|||It was a primary index at one time. When it stopped working I changed it in
the process of testing.
"Gert-Jan Strik" wrote:

> Dan,
> The current index on ID is non-unique. If the ID column is in fact the
> identifying column, then make sure you create a unique index on it. Or
> even better, to create a Primary Key constraint on it (which will
> automatically create a unique index on it).
> HTH,
> Gert-Jan
>
> Dan D. wrote:
> <snip>
>|||Problem solved. I was looking at the development server but querying the
production server. There was no index on production. Now that there is, it
works. Wonders never cease.
Thanks to all for your help.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema questio
n
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
>sql

Friday, March 23, 2012

index question

We have a stored procedure that stopped working. I put it in the debugger and
discovered that the update statement takes forever - I stopped it after 2
minutes. This is the update statement: UPDATE tbltelemarketing SET download = '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
column. When I do an execution plan on the query, it doesn't use the index on
the id column. It does a clustered index scan using an index on several
columns. The table keeps growing so I guess at some point, it stopped using
the ID index.
Is there a way to force it to use the ID index? Any other ideas on how I can
speed up the query. The table has about 11 million rows. Here is the schema
and indexes.
CREATE TABLE [dbo].[tbltelemarketing] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL ,
[prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_mail] [int] NULL ,
[bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[drop_date] [smalldatetime] NULL ,
[hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pest_cncl] [smalldatetime] NULL ,
[term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[term_cncl] [smalldatetime] NULL ,
[promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[download] [smalldatetime] NULL ,
[recd_date] [smalldatetime] NULL ,
[mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[recd_week] [int] NULL ,
[live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_lns] [int] NULL ,
[branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reassign_date] [datetime] NULL ,
[company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_date] [smalldatetime] NULL ,
[update_cd] [int] NULL ,
[update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
[dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
[PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
[promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
[promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
[hh_score2]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_3] ON
[dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
[delete_cd], [live], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
ON [PRIMARY]
GO
Thanks,
--
Dan D.1) Are your statistics on the table are current
2) Why i don't see the unique keywork on ID index and other schema question
if ID is identity why can't it be primary key with non-clustered index
"Dan D." wrote:
> We have a stored procedure that stopped working. I put it in the debugger and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET download => '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I can
> speed up the query. The table has about 11 million rows. Here is the schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||You can use an index hint when you want to force SQL Server to use an index.
You can search Books Online (within the SQL Server program group) for INDEX
HINT. The general usage is:
.
FROM tbltelemarketing WITH (INDEX (nci_tbltelemarketing_ID)) WHERE ID =10617138
I assume that ID is the primary key on the table...yet you don't define it
as a primary key constraint. Perhaps you should define a primary key on the
table.
--
Keith
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2A769066-CBE1-4391-8900-02E825BE30EA@.microsoft.com...
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET
download => '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped
using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I
can
> speed up the query. The table has about 11 million rows. Here is the
schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date])
ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON
[dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON
[PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON
[dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||Dan,
The current index on ID is non-unique. If the ID column is in fact the
identifying column, then make sure you create a unique index on it. Or
even better, to create a Primary Key constraint on it (which will
automatically create a unique index on it).
HTH,
Gert-Jan
Dan D. wrote:
> We have a stored procedure that stopped working. I put it in the debugger and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET download => '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I can
> speed up the query. The table has about 11 million rows. Here is the schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
<snip>
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||I did update the statistics. You don't see the "unique" keyword because I was
trying different indexes with different names and the one you see was the
last one I tried. At one time it was primary with non-clustered index.
"harvinder" wrote:
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema question
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
> > We have a stored procedure that stopped working. I put it in the debugger and
> > discovered that the update statement takes forever - I stopped it after 2
> > minutes. This is the update statement: UPDATE tbltelemarketing SET download => > '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> > column. When I do an execution plan on the query, it doesn't use the index on
> > the id column. It does a clustered index scan using an index on several
> > columns. The table keeps growing so I guess at some point, it stopped using
> > the ID index.
> >
> > Is there a way to force it to use the ID index? Any other ideas on how I can
> > speed up the query. The table has about 11 million rows. Here is the schema
> > and indexes.
> >
> > CREATE TABLE [dbo].[tbltelemarketing] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [update_date] [smalldatetime] NULL ,
> > [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [id_mail] [int] NULL ,
> > [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [drop_date] [smalldatetime] NULL ,
> > [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [pest_cncl] [smalldatetime] NULL ,
> > [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [term_cncl] [smalldatetime] NULL ,
> > [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [download] [smalldatetime] NULL ,
> > [recd_date] [smalldatetime] NULL ,
> > [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [recd_week] [int] NULL ,
> > [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [id_lns] [int] NULL ,
> > [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [reassign_date] [datetime] NULL ,
> > [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [delete_date] [smalldatetime] NULL ,
> > [update_cd] [int] NULL ,
> > [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> > [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
> > [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> > [promo_code]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
> > [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> > [hh_score2]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_3] ON
> > [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
> > [delete_cd], [live], [promo_code]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> > ON [PRIMARY]
> > GO
> >
> > Thanks,
> > --
> > Dan D.|||It was a primary index at one time. When it stopped working I changed it in
the process of testing.
"Gert-Jan Strik" wrote:
> Dan,
> The current index on ID is non-unique. If the ID column is in fact the
> identifying column, then make sure you create a unique index on it. Or
> even better, to create a Primary Key constraint on it (which will
> automatically create a unique index on it).
> HTH,
> Gert-Jan
>
> Dan D. wrote:
> >
> > We have a stored procedure that stopped working. I put it in the debugger and
> > discovered that the update statement takes forever - I stopped it after 2
> > minutes. This is the update statement: UPDATE tbltelemarketing SET download => > '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> > column. When I do an execution plan on the query, it doesn't use the index on
> > the id column. It does a clustered index scan using an index on several
> > columns. The table keeps growing so I guess at some point, it stopped using
> > the ID index.
> >
> > Is there a way to force it to use the ID index? Any other ideas on how I can
> > speed up the query. The table has about 11 million rows. Here is the schema
> > and indexes.
> >
> > CREATE TABLE [dbo].[tbltelemarketing] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> <snip>
> >
> > CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> > ON [PRIMARY]
> > GO
> >
> > Thanks,
> > --
> > Dan D.
>|||Problem solved. I was looking at the development server but querying the
production server. There was no index on production. Now that there is, it
works. Wonders never cease.:)
Thanks to all for your help.
"harvinder" wrote:
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema question
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
> > We have a stored procedure that stopped working. I put it in the debugger and
> > discovered that the update statement takes forever - I stopped it after 2
> > minutes. This is the update statement: UPDATE tbltelemarketing SET download => > '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> > column. When I do an execution plan on the query, it doesn't use the index on
> > the id column. It does a clustered index scan using an index on several
> > columns. The table keeps growing so I guess at some point, it stopped using
> > the ID index.
> >
> > Is there a way to force it to use the ID index? Any other ideas on how I can
> > speed up the query. The table has about 11 million rows. Here is the schema
> > and indexes.
> >
> > CREATE TABLE [dbo].[tbltelemarketing] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [update_date] [smalldatetime] NULL ,
> > [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [id_mail] [int] NULL ,
> > [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [drop_date] [smalldatetime] NULL ,
> > [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [pest_cncl] [smalldatetime] NULL ,
> > [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [term_cncl] [smalldatetime] NULL ,
> > [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [download] [smalldatetime] NULL ,
> > [recd_date] [smalldatetime] NULL ,
> > [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [recd_week] [int] NULL ,
> > [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [id_lns] [int] NULL ,
> > [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [reassign_date] [datetime] NULL ,
> > [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [delete_date] [smalldatetime] NULL ,
> > [update_cd] [int] NULL ,
> > [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> > [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
> > [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> > [promo_code]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
> > [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> > [hh_score2]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_3] ON
> > [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
> > [delete_cd], [live], [promo_code]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> > ON [PRIMARY]
> > GO
> >
> > Thanks,
> > --
> > Dan D.

Monday, March 12, 2012

Index Maint DBCC assistance.

I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
Do you have a test environment where you can load a copy of prod? If so,
generate the defrag script there but execute it against prod. This way, you
won't be locking up the table in prod with a share lock by running DBCC
SHOWCONTIG.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
|||The way to really solve this is to upgrade to 2005 as the equivalent of
Showcontig does not block like in 2000. But the code you have now seems to
be the fastest and lightest way to do this in 2000. I never recommend on
larger dbs to reindex blindly so your approach is what I would recommend
anyway. You can keep a history of the fragmentation by index and use that to
determine which to defrag. Chances are if the usage is roughly the same week
to week the indexes will be roughly the same in terms of fragmentation. So
collect samples for a few weeks and base your rebuilding off of those
numbers for x many future weeks.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>I am working with multiple servers, and numerous databases. I have
> some large databases, some tables with 100's of millions of rows.
> Some of the DB's are 24/7 so recreating the indexes is not really an
> option. I am trying to come up with a "smart" index plan. I have
> seen many examples of this, and I have one that does seem to work,
> except that the DBCC showcontig seems to take forever! I am using a
> variation on the following. The DBCC Showcontig seems to lock the
> table, is this true?
> Is there a faster way to get the DBCC fragmentation information?
> Is it really worth the work identifying and defragmenting those
> indexes that fall below a threshold or should I just defrag every
> index?
> Any other help on a "smart" maint plan for index maint would be
> appreciated!
> OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> I am on SQL 2000, some at SP3a / SP4.
> Sample of code below:
> http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> -- Declare variables
> SET NOCOUNT ON;
> DECLARE @.tablename varchar(128);
> DECLARE @.execstr varchar(255);
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.frag decimal;
> DECLARE @.maxfrag decimal;
> -- Decide on the maximum fragmentation to allow for.
> SELECT @.maxfrag = 30.0;
> -- Declare a cursor.
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE';
> -- Create the table.
> CREATE TABLE #fraglist (
> ObjectName char(255),
> ObjectId int,
> IndexName char(255),
> IndexId int,
> Lvl int,
> CountPages int,
> CountRows int,
> MinRecSize int,
> MaxRecSize int,
> AvgRecSize int,
> ForRecCount int,
> Extents int,
> ExtentSwitches int,
> AvgFreeBytes int,
> AvgPageDensity int,
> ScanDensity decimal,
> BestCount int,
> ActualCount int,
> LogicalFrag decimal,
> ExtentFrag decimal);
> -- Open the cursor.
> OPEN tables;
> -- Loop through all the tables in the database.
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> END;
> -- Close and deallocate the cursor.
> CLOSE tables;
> DEALLOCATE tables;
> -- Declare the cursor for the list of indexes to be defragged.
> DECLARE indexes CURSOR FOR
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
> -- Open the cursor.
> OPEN indexes;
> -- Loop through the indexes.
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')';
> EXEC (@.execstr);
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE indexes;
> DEALLOCATE indexes;
> -- Delete the temporary table.
> DROP TABLE #fraglist;
> GO
|||And perhaps this "history of the index fragmentation" may show that some
indexes are fragmented too soon and maybe a new fill factor will be needed.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Andrew J. Kelly" wrote:

> The way to really solve this is to upgrade to 2005 as the equivalent of
> Showcontig does not block like in 2000. But the code you have now seems to
> be the fastest and lightest way to do this in 2000. I never recommend on
> larger dbs to reindex blindly so your approach is what I would recommend
> anyway. You can keep a history of the fragmentation by index and use that to
> determine which to defrag. Chances are if the usage is roughly the same week
> to week the indexes will be roughly the same in terms of fragmentation. So
> collect samples for a few weeks and base your rebuilding off of those
> numbers for x many future weeks.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David Hay" <david.hay@.gmail.com> wrote in message
> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>
|||Yes good point Ben, I should have noted that as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:93BADA3C-FD11-414E-9044-21E3D63B622B@.microsoft.com...[vbcol=seagreen]
> And perhaps this "history of the index fragmentation" may show that some
> indexes are fragmented too soon and maybe a new fill factor will be
> needed.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Andrew J. Kelly" wrote:
|||Thanks to all for the information. I did run a test in Prod, and it
ran much faster. (Much larger box). I saw a script that loaded a
history table and I'll work on building that.
Thanks again to all responding.
David

Index Maint DBCC assistance.

I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GODo you have a test environment where you can load a copy of prod? If so,
generate the defrag script there but execute it against prod. This way, you
won't be locking up the table in prod with a share lock by running DBCC
SHOWCONTIG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO|||The way to really solve this is to upgrade to 2005 as the equivalent of
Showcontig does not block like in 2000. But the code you have now seems to
be the fastest and lightest way to do this in 2000. I never recommend on
larger dbs to reindex blindly so your approach is what I would recommend
anyway. You can keep a history of the fragmentation by index and use that to
determine which to defrag. Chances are if the usage is roughly the same week
to week the indexes will be roughly the same in terms of fragmentation. So
collect samples for a few weeks and base your rebuilding off of those
numbers for x many future weeks.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>I am working with multiple servers, and numerous databases. I have
> some large databases, some tables with 100's of millions of rows.
> Some of the DB's are 24/7 so recreating the indexes is not really an
> option. I am trying to come up with a "smart" index plan. I have
> seen many examples of this, and I have one that does seem to work,
> except that the DBCC showcontig seems to take forever! I am using a
> variation on the following. The DBCC Showcontig seems to lock the
> table, is this true?
> Is there a faster way to get the DBCC fragmentation information?
> Is it really worth the work identifying and defragmenting those
> indexes that fall below a threshold or should I just defrag every
> index?
> Any other help on a "smart" maint plan for index maint would be
> appreciated!
> OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> I am on SQL 2000, some at SP3a / SP4.
> Sample of code below:
> http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> -- Declare variables
> SET NOCOUNT ON;
> DECLARE @.tablename varchar(128);
> DECLARE @.execstr varchar(255);
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.frag decimal;
> DECLARE @.maxfrag decimal;
> -- Decide on the maximum fragmentation to allow for.
> SELECT @.maxfrag = 30.0;
> -- Declare a cursor.
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE';
> -- Create the table.
> CREATE TABLE #fraglist (
> ObjectName char(255),
> ObjectId int,
> IndexName char(255),
> IndexId int,
> Lvl int,
> CountPages int,
> CountRows int,
> MinRecSize int,
> MaxRecSize int,
> AvgRecSize int,
> ForRecCount int,
> Extents int,
> ExtentSwitches int,
> AvgFreeBytes int,
> AvgPageDensity int,
> ScanDensity decimal,
> BestCount int,
> ActualCount int,
> LogicalFrag decimal,
> ExtentFrag decimal);
> -- Open the cursor.
> OPEN tables;
> -- Loop through all the tables in the database.
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> END;
> -- Close and deallocate the cursor.
> CLOSE tables;
> DEALLOCATE tables;
> -- Declare the cursor for the list of indexes to be defragged.
> DECLARE indexes CURSOR FOR
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
> -- Open the cursor.
> OPEN indexes;
> -- Loop through the indexes.
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')';
> EXEC (@.execstr);
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE indexes;
> DEALLOCATE indexes;
> -- Delete the temporary table.
> DROP TABLE #fraglist;
> GO|||And perhaps this "history of the index fragmentation" may show that some
indexes are fragmented too soon and maybe a new fill factor will be needed.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Andrew J. Kelly" wrote:

> The way to really solve this is to upgrade to 2005 as the equivalent of
> Showcontig does not block like in 2000. But the code you have now seems to
> be the fastest and lightest way to do this in 2000. I never recommend on
> larger dbs to reindex blindly so your approach is what I would recommend
> anyway. You can keep a history of the fragmentation by index and use that
to
> determine which to defrag. Chances are if the usage is roughly the same we
ek
> to week the indexes will be roughly the same in terms of fragmentation. So
> collect samples for a few weeks and base your rebuilding off of those
> numbers for x many future weeks.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David Hay" <david.hay@.gmail.com> wrote in message
> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>|||Yes good point Ben, I should have noted that as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:93BADA3C-FD11-414E-9044-21E3D63B622B@.microsoft.com...[vbcol=seagreen]
> And perhaps this "history of the index fragmentation" may show that some
> indexes are fragmented too soon and maybe a new fill factor will be
> needed.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Andrew J. Kelly" wrote:
>|||Thanks to all for the information. I did run a test in Prod, and it
ran much faster. (Much larger box). I saw a script that loaded a
history table and I'll work on building that.
Thanks again to all responding.
David