Posted by rshivaraman on 05/02/07 17:07

On May 2, 10:39 am, Utahduck <Utahd...@hotmail.com> wrote:
> On May 1, 6:29 pm, rshivara...@gmail.com wrote:
>
>
>
>
>
> > Hi :
>
> > I have a TableA with around 10 columns with varchar and numeric
> > datatypes
> > It has 500 million records and its size is 999999999 KB. i believe it
> > is kb
> > i got this data after running sp_spaceused on it. The index_size was
> > also pretty big in 6 digits.
>
> > On looking at the tableA
> > it didnot have any pks and hence no clustered index.
> > It had other indices
> > IX_1 on ColA
> > IX_2 on ColB
> > IX_3 on ColC
> > IX_4 on ColA, ColB and ColC put together.
>
> > Queries performed in this table are very slow. I have been asked to
> > tune up this table.
> > I know as much info as you.
>
> > Data prior to 2004 can be archived into another table. I need to run a
> > query to find out how many records that is.
>
> > I am thinking the following, but dont know if i am correct ?
> > I need to add a new PK column (which will increase the size of the
> > tableA) which will add a clustered index.
> > Right now there are no clustered indices
>
> > 2. I would like help in understanding should i remove IX_1, IX_2, IX_3
> > as they are all used in IX_4 anyway .
>
> > 3. I forget what the textbox is called on the index page. it is set to
> > 0 and can be set from 0 to 100. what would be a good value for it ?
>
> > thank you.
> > RS
>
> I'm afraid I might not be able to offer all the hints you are looking
> for, but here are some things I've found helpful:
>
> 1) LOOK AT YOUR QUERIES!! We've obtained huge performance boosts
> just by looking at the queries and stored procedures and optimizing
> them. Usually if a query is going extremely slow it is because of
> poor programming techniques.
> 2) LOOK AT YOUR QUERIES!! Are they sometimes filtering only on
> ColB? If so, IX_4 will be useless if you remove IX_2. IX_4 will kick
> in when you are filtering on several columns. But are there other
> columns that are being filtered on?
> 3) Are you dynamically parcing or calculating columns? For instance,
> if you have a column with EventDateTime of 01-01-2007 12:34PM but you
> are filtering on dates (ie WHERE CONVERT(varchar, EventDateTime, 101)
> = '01-01-07') then that is going to slow down your query. Create a
> new column of EventDate and calculate the event date from the event
> date and time and then stick an index on it. That will add some
> boosts as the sampled WHERE clause won't reference the index.
> 4) Do you look at the query execution plan? That can tell you if you
> are doing full table scans or hitting an index.
> 5) Have you used the query index wizard?
> 6) Breaking the table up will help a lot. At one company I consult
> for we have a similar table (a half million records added per day)
> with data going back to 2003. We create two (what we call) _Mini
> tables. TableName_Mini_Last45Days, TableName_Mini_Last6Months, and
> TableName_Mini_Last1Year. This does take up space but since 80% of
> the queries only want data from the last month the first mini table
> makes sense to hit. If they need to go further back further then they
> hit the appropriate table. The main table (TableName) is rarely
> queries except after its morning data load (off hours) to create the
> _Mini tables so if somebody does need information from it, it isn't
> bogged down by heavy usage.
> 7) I've never done this personally so I cannot attest to its success
> (though I should test it). I have heard you can recreate/rebuild an
> index and that should get rid of some of the fragmentation. I think
> I'll give it a test, but you might want to do that as well. But since
> it is re-indexing half a billion records you may want to do that off-
> hours.
>
> I hope one or more of these helps you out!- Hide quoted text -
>
> - Show quoted text -

Thank you Utahduck, your mail was helpful. Also am reading up on
indexes and that is helping me.
You idea of laying out mini tables by the year is a great suggestion i
hadnt thought of. I will be definitely using that
Thank you
RS

[Back to original message]