CODEWITHSUNDEEP
sqlsql-server
user2621858
user2621858

Reputation: 121

Indexing a table with duplicate records

I have a SQL Server table with around 50,000 rows. The table gets updated once in a day by some upstream process.

The following query has been fired from application:

SELECT * FROM Table1 where Field1 = "somevalue"

The "Field1" column contains duplicate values. I am trying to improve performance of the above query. I cannot modify the code in the application side. So limiting column instead of "SELECT *" is not possible. I am planning to index the table. Should I define a NON-CLUSTERED index on "Field1" column in order to improve performance? Or some other kind of indexing would help? Is there any other ways to improve performance from DB side ?

Upvotes: 1

Views: 6401

Answers (2)

Bryan
Bryan

Reputation: 3451

The best thing you can do is run SP_BlitzIndex by Brent Ozar to get a better picture of your entire database index setup (including this table).

http://www.brentozar.com/blitzindex/

If your table already has a clustered index (which it should - apply one following these principles), you should first look at the execution plan to see what it is advocating.

Further, if the table is only updated every day, and presumably during off hours, you can easily compress the table and given it has repetitive data mostly, you will save over 50% IO and space on the query and incur a small CPU overhead. Table compression has no effect on the data itself, only on the space it holds. This feature is only available in SQL Server Enterprise.

Last but not least, are your data types properly set, i.e. are you pulling from datetime when the column could easily be date, or are you pulling from bigint when the column could easily be int.

Asking a question as to how to make an index really isn't a proper question for Stack, i.e. 

CREATE NONCLUSTERED INDEX Idx_Table1_Field1 ON Table1 (Field1)

As it is already on MSDN and can even be created via SSMS via Create Index drop down right clicking on the index burst out section under a given table icon, the question you should be asking is how do I properly address performance improvements in my environment related to indexing. Finally, analyze whether or not your end query result really necessitates a select * - this is a common oversight on data display, a table with 30 columns is selected from a dataset when the developer only plans on showing 5 of the columns, which would be a 600% IO gain if the dataset only populated 5 columns.

Please also note the famous index maintenance script by Ole Hallengren

Upvotes: 1

Ruslan
Ruslan

Reputation: 2799

Yes, a non-clustered index on Field1 should serve your purposes...

For example,

CREATE NONCLUSTERED INDEX Idx_Table1_Field1 ON Table1 (Field1)

Upvotes: 2

Related Questions