|
|
Posted by Utahduck on 05/02/07 14:39
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!
Navigation:
[Reply to this message]
|