about indexes
-
I have a problem in performance tunning.
I have one table which consists of 50 thousands rows. In this table, there is a column whose size is 1000 bytes (datatypes is nvarchar). This column appears in different join statments and where clause of different long running queries. This column is not any index. When i start to create non clustered index on this column, query analyzer gives a warning i.e. size is too long.
How can I solve this problem ?
I think this makes slow in queries but how can i make it index. The size of the column cannot reduce due to some kind of requirements of clients.
Can anyone help me soon please ?
Ashok
-
Index tuning is an art, not a science. A cursory look at your example indexes, however, would have me dropping the single column index on column a has you have 2 other indexes that SQL could use for a query that was dependent only on column a being indexed. This would also reduce the overhead on SQL when updating indexes.
I am sure if you do a search on this site regarding index tuning, you will find several helpful responses and links to other sites that can provide you with more detailed information.
-
I agree with Lynn...
SQL server 2005 has good options... you can find easlily unsed indexes and required indexes too...
http://www.sqlservercentral.com/columnists/aingold/2770.asp
MohammedU
Microsoft SQL Server MVP -
It's all to do with io, pages, index width/column width, selectivity and locks, there's not a "golden solution" to effective indexing and as Lynn correctly states it's more of an art.
If you can find Kalen Delaney or Kimberley Tripp doing a course/presentation near you get along to it and gain an understanding of how indexes work ( or don't )
As a very very very rough rule of thumb ( so not exact ) make sure your first index column is as selective as you can make it. you can then build an index of say 6 columns which satisfies a number of queries e.g.
index g,a,b,c,d,e would satisfy queries g,c,e g,a,b g,a,b,e.
The order of the columns in the index can be critical , as can the order in the query, the first index used may not always be the best .. sorry but as said it's an art and "it just depends"
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
You can also create a computed column where you will Checksum(your original column), then create a index on it.
-
did this post get changed?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
use the new Included Columns for creating indexes in sql 2005, look in books online for more info
-
Ever consider putting a full text index on it?
Consider adding an integer key to the table, all tables that have the join column......IE normalize the data. I have had systems increase performance by 1000 times by making this type of table change.
my 2 cents
Eric
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply