index and key: update

Showing posts with label update. Show all posts

Friday, March 30, 2012

index statistics are created when?

If the auto create stats and auto update stats is off (actually the value is null) at the db level - are stats generated when an index is recreated?

Additionaly, running the sp_autostats on the above db shows that the auto update stats are on. Where is the option to set it on for an object rather than the db?

Mike

I just read by Kalen that if the options are false (off) this overrides the table level. Still not clear on what occurs when options are <null>True when you run DBCC DBREINDEX those stats will be updated too.

SP_AUTOSTATS is used to display or change the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.

Index statistics and a primary key

I have a question regarding updating statistics for a primary key.

Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.

Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?

Hope someone can help Smile

Thanks
James

If you have not changed anything statistics will be automatically updated by SQL Server after a number of modifications have been made to the table. SQL Server 2005 updates the counter that checks the number of modifications for BULK INSERT too. When autostats kicks in only a sample of the data is used to calculate them.

Doing a full scan requires a lot of I/O so with 80 million rows it might be slow if your disk subsystem is not fast enough..

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

sql

Monday, March 26, 2012

index question

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

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 19, 2012

Index on high update column

I have a column in my table that is being used in a select query. I would
like to index the column but it gets updated frequently. What is the best wa
y
to determine the cost benifit of indexing a column that is changed frequentl
y?Test it?
You can avoid some problems by specifying a lower FILLFACTOR when you create
the index -- I usually stay around 60 for most scenarios like what you
describe, but that's really just an arbitrary number I use -- and by
defragmenting it regularly.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:E860256A-CAC1-473C-80AC-E07919459765@.microsoft.com...
> I have a column in my table that is being used in a select query. I would
> like to index the column but it gets updated frequently. What is the best
way
> to determine the cost benifit of indexing a column that is changed
frequently?|||Frank
You could use profiler to capture the activity and then use the index tuning
wizard to see what recommendations you get. Do some testing as well, don't
always trust the wizard to get it right, but it is usually pretty good.
Regards
John
"Adam Machanic" wrote:

> Test it?
> You can avoid some problems by specifying a lower FILLFACTOR when you crea
te
> the index -- I usually stay around 60 for most scenarios like what you
> describe, but that's really just an arbitrary number I use -- and by
> defragmenting it regularly.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:E860256A-CAC1-473C-80AC-E07919459765@.microsoft.com...
> way
> frequently?
>
>

Index on high update column

I have a column in my table that is being used in a select query. I would
like to index the column but it gets updated frequently. What is the best way
to determine the cost benifit of indexing a column that is changed frequently?
Test it?
You can avoid some problems by specifying a lower FILLFACTOR when you create
the index -- I usually stay around 60 for most scenarios like what you
describe, but that's really just an arbitrary number I use -- and by
defragmenting it regularly.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:E860256A-CAC1-473C-80AC-E07919459765@.microsoft.com...
> I have a column in my table that is being used in a select query. I would
> like to index the column but it gets updated frequently. What is the best
way
> to determine the cost benifit of indexing a column that is changed
frequently?
|||Frank
You could use profiler to capture the activity and then use the index tuning
wizard to see what recommendations you get. Do some testing as well, don't
always trust the wizard to get it right, but it is usually pretty good.
Regards
John
"Adam Machanic" wrote:

> Test it?
> You can avoid some problems by specifying a lower FILLFACTOR when you create
> the index -- I usually stay around 60 for most scenarios like what you
> describe, but that's really just an arbitrary number I use -- and by
> defragmenting it regularly.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:E860256A-CAC1-473C-80AC-E07919459765@.microsoft.com...
> way
> frequently?
>
>

Index on high update column

I have a column in my table that is being used in a select query. I would
like to index the column but it gets updated frequently. What is the best way
to determine the cost benifit of indexing a column that is changed frequently?Test it?
You can avoid some problems by specifying a lower FILLFACTOR when you create
the index -- I usually stay around 60 for most scenarios like what you
describe, but that's really just an arbitrary number I use -- and by
defragmenting it regularly.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:E860256A-CAC1-473C-80AC-E07919459765@.microsoft.com...
> I have a column in my table that is being used in a select query. I would
> like to index the column but it gets updated frequently. What is the best
way
> to determine the cost benifit of indexing a column that is changed
frequently?|||Frank
You could use profiler to capture the activity and then use the index tuning
wizard to see what recommendations you get. Do some testing as well, don't
always trust the wizard to get it right, but it is usually pretty good.
Regards
John
"Adam Machanic" wrote:
> Test it?
> You can avoid some problems by specifying a lower FILLFACTOR when you create
> the index -- I usually stay around 60 for most scenarios like what you
> describe, but that's really just an arbitrary number I use -- and by
> defragmenting it regularly.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:E860256A-CAC1-473C-80AC-E07919459765@.microsoft.com...
> > I have a column in my table that is being used in a select query. I would
> > like to index the column but it gets updated frequently. What is the best
> way
> > to determine the cost benifit of indexing a column that is changed
> frequently?
>
>

Monday, March 12, 2012

Index is not faster any more

After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.

Thank you
Raj SankarOriginally posted by raj_sankar
After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.

Thank you
Raj Sankar
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex

Joe|||Originally posted by mkg_1232000
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex

Joe

The index is ok, Some how execution plan changed. Now every this ok after uodating the statistics. how ever the foloowing problem remains.

from query analyser.
select * from table_name where store = '3' --is faster and using index scan.

Declare @.store as int
set @.store = '3'
select * from table_name where store = @.store --is very slow and using table scan.

I don't how to fix this, it was ok before.

Sunday, February 19, 2012

Index Building

First use, sometimes doing an update statistics will help.
Other things I've done at some sites, with 7.0 is execute
the proc after its built before my users get in. Not the
cleanest way, but it worked. I haven't had similar
problems in 2000, but you never know.
Gary Abbott
MS-SQL Database Architect

>--Original Message--
>Does SQL Server 2000 optimize indexes differently than
SQL 7
>It seems that SQL 2K doesn't (re)build the index until
you execute a
>query that will utilize the index. This is causing the
first time you
>execute the query to be slow. The second time it is
executed it runs
>fast.
>We have maintenance plans to rebuild the indexes each
night. This
>doesn't seem to help. Instead if it seems like the index
is rebuilt
>when our users are executing the query which is
undesirable. Does
>anyone know if this is what SQL Server does? If so, how
can I can this
>behavior?
>Greg
>.
>UPDATE STATISTICS shouldn't be needed after DBCC DBREINDEX because the
distribution data is updated with the index rebuild...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:1213e01c3f5b8$534208d0$a401280a@.phx
.gbl...
> First use, sometimes doing an update statistics will help.
> Other things I've done at some sites, with 7.0 is execute
> the proc after its built before my users get in. Not the
> cleanest way, but it worked. I haven't had similar
> problems in 2000, but you never know.
> Gary Abbott
> MS-SQL Database Architect
>
> SQL 7
> you execute a
> first time you
> executed it runs
> night. This
> is rebuilt
> undesirable. Does
> can I can this

Friday, March 30, 2012

index statistics are created when?

Index statistics and a primary key

Monday, March 26, 2012

index question

index question

Friday, March 23, 2012

index question

Monday, March 19, 2012

Index on high update column

Index on high update column

Index on high update column

Monday, March 12, 2012

Index is not faster any more

Sunday, February 19, 2012

Index Building

index and key

Blog Archive

About Me