You are here: Re: Need to tune a table for performance gains « MsSQL Server « IT news, forums, messages
Re: Need to tune a table for performance gains

Posted by Erland Sommarskog on 05/02/07 22:10

(rshivaraman@gmail.com) writes:
> 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.

Could you post the exact output from sp_spaceused and also from
DBCC SHOWCONTIG on the table? (The latter will take some time to
run on a table this size.)

> On looking at the tableA
> it didnot have any pks and hence no clustered index.

Maybe the table has neither, however there is no connection between
the two. A table could have a clustered index, but no primary key or
vice versa. Most or rather all tables should really have a PK. And
the very most tables should have a clustered index. But far from all
tables should have their clustered index on their primary key.

Tables without a clustered index are known as heaps. Heaps are more
prone to fragmentation for various reasons, and given the size of your
table, I suspect yours is victim to that.

> 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.

There are all reasons to investigate whether the queries align with
the index. Utahduck made a very good point about queries hiding the
column in an expression.

> Data prior to 2004 can be archived into another table. I need to run a
> query to find out how many records that is.

Since we don't know the table definiton, we cannot help you with that.

> 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.

Better investigate if there are any columns for which there are
typically range queries, like a datetime column.

You should also investigate if there are columns in the table that in
fact constitute a key, but someone has forgotten to define that key.
After all, adding an IDENTITY column as a PK, makes in practice very
little difference to not having a key at all.

> 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 .

That is impossible to say without knowing the queries. But a query
like

SELECT .... FROM tbl WHERE colB = @value

will not be helped much by the index on (ColA, ColB, ColC). Possibly,
the index on ColA is redudant, but there are queries that will run
faster with this index in place, for instance:

SELECT ColA, COUNT(*) FROM tbl GROUP BY ColA

> 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 ?

That is the fillfactor. We would need to know more about the table to
be able to make recommendations about it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация