Re: missing indexes

From: Steve Kass (skass_at_drew.edu)
Date: 05/18/04 

Date: Tue, 18 May 2004 02:55:35 -0400

Andre,

  For now, try

DROP STATISTICS SummaryPPVA2001 hind_c_1188199283_4A
and so on for the other names in the errors (be sure to drop themon the
remote server)

These may e hypothetical indexe used by the index tuning wizard.

I'll keep looking for something

SK

Andre wrote:

>I'm always glad to see you responding to my posts Steve; you're a huge asset
>to the SQL community.
>
>My server is running sql2k, sp3a on Win2k sp4. All the latest security
>updates have been applied. I've also applied all patches as recommended by
>the Baseline Security Analyzer. My sql version is: Microsoft SQL Server
>2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003
>Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195:
>Service Pack 4)
>
>The query that's causing the error is:
>select distinct
> o.systemnumber as SystemNumber,
> convert(char(4), o.showedon, 120) as ShowYear,
> convert(char(7), o.showedon, 120) as ShowMonth,
> convert(char(10), o.showedon, 120) as ShowDate,
> DATENAME(WEEKDAY, o.ShowedOn) as ShowDayOfWeek,
> sum(o.Records) as Buys
> from REP01.ARCHIVE.dbo.SummaryPPVA2001 o
> where o.systemnumber in
>(106,108,117,120,125,130,143,144,146,151,158,174,186,248,270,313,317,318,321
>,322,325,328,329,331,345,416,418,425,429,483,530,779)
> and o.networknumber = 4
> and o.networkchannelnumber = 1
> and o.price >= 0
> and o.showedon <= '03/31/2004 23:59'
> group by o.systemnumber, convert(char(4), o.showedon, 120),
>convert(char(7), o.showedon, 120), o.systemnumber, convert(char(10),
>o.showedon, 120), DATENAME(WEEKDAY, o.ShowedOn)
> order by o.systemnumber, convert(char(10), o.showedon, 120)
>
>I've simplified the query a little, but it's still causing the error in my
>original post.
>
>The only way i can run the query and have a resultset returned is to drop
>all the indexes on SummaryPPVA2001. You can probably tell that REP01 is a
>linked sql server. I'm running this from a sql box named SQL01. SQL01 has
>the exact same @@version as REP01. It also has the exact same security
>updates/patches as REP01.
>
>The ddl for SummaryPPVA2001 is:
>CREATE TABLE [dbo].[SummaryPPVA2001] (
> [CorpNumber] [smallint] NOT NULL ,
> [SystemNumber] [smallint] NOT NULL ,
> [ShowedOn] [smalldatetime] NOT NULL ,
> [ShowingNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [Price] [smallmoney] NOT NULL ,
> [Technology] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TitleKey] [int] NOT NULL ,
> [NetworkNumber] [smallint] NOT NULL ,
> [NetworkChannelNumber] [smallint] NOT NULL ,
> [Records] [int] NOT NULL ,
> [Revenue] [money] NOT NULL ,
> [RowID] [int] IDENTITY (1, 1) NOT NULL
>) ON [PRIMARY]
>GO
>
>ALTER TABLE [dbo].[SummaryPPVA2001] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>GO
>
>ALTER TABLE [dbo].[SummaryPPVA2001] ADD
> CONSTRAINT [CK_SummaryPPVA2001] CHECK (datepart(year,[ShowedOn]) = 2001)
>GO
>
> CREATE INDEX [IX_SummaryPPVA2001] ON
>[dbo].[SummaryPPVA2001]([SystemNumber], [ShowedOn]) WITH FILLFACTOR = 90 ON
>[PRIMARY]
>GO
>
> CREATE INDEX [IX_SummaryPPVA2001_1] ON
>[dbo].[SummaryPPVA2001]([CorpNumber], [ShowingNumber], [ShowedOn]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
>GO
>
> CREATE INDEX [IX_SummaryPPVA2001_2] ON [dbo].[SummaryPPVA2001]([TitleKey])
>WITH FILLFACTOR = 90 ON [PRIMARY]
>GO
>
> CREATE INDEX [IX_SummaryPPV2001_3] ON
>[dbo].[SummaryPPVA2001]([CorpNumber], [ShowingNumber], [TitleKey],
>[NetworkNumber], [NetworkChannelNumber], [Price]) WITH FILLFACTOR = 90 ON
>[PRIMARY]
>GO
>
> CREATE INDEX [IX_SummaryPPVA2001_3] ON
>[dbo].[SummaryPPVA2001]([NetworkNumber]) WITH FILLFACTOR = 90 ON [PRIMARY]
>GO
>
>Thanks for your help Steve.
>
>Andre
>
>
>
>

Relevant Pages

  • running parameter qry from form...
    ... I'm finding that running this query is sometimes deleting the first several ... What would be causing this? ... Here's the SQL for it (same mdb as my other posts, ...
    (microsoft.public.access.queries)
  • Re: Delete Query not working
    ... Here is the SQL code you asked for: ... > If the query contains only ONE table, ... >> I can't determine what is causing the problem. ...
    (microsoft.public.access.formscoding)
  • Re: OLE DB provider MSIDXS reported an error. Access denied.
    ... can you query the local IS server? ... Also what rights does the SQL Server agent have on the remote server? ...
    (microsoft.public.sqlserver.fulltext)
  • MILES ARE NOT SHOWING
    ... Below is the SQL View of my query. ... For some reason the miles are not showing ... up which is causing me a problem when I am running my report. ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)