i have Huge SQL table like 800mil record.

it takes about 30gig of disk space without any indexing on it.

what is the best method to apply index in it?

also this table gets about 50,000 record insert in every 5 minute.

there is no delete or update on table. only select for getting results.

here what tables looks like:

 ID      |           column1             | column2  |   column3   |   column3   |   column4 |
 1(PK AI)|'2013-11-04 13:47:03'(datetime)|  A(int)  |    A(char)  |  A(Varchar) | A(Varchar)|
  • The best index(es) depend on what kind of selects are done. That is, what columns are returned and what kind of WHERE conditions are being used? Are there JOINs to other tables? – vonPryz Nov 8 '13 at 9:07
  • So, you have a small SQL Server table (less than 50% of the RAM of a lower end database server these days). With low activity (50.000 inserts per 300 seconds is not that much). And use an oudated SQL Server version (which one: express, standard or enterprise). – TomTom Dec 8 '13 at 9:53
  • Btw., you DO have an index - the primary key is an index. – TomTom Dec 8 '13 at 9:53

Ask your SQL Server DMV's about what indexes it might think you need, pick the biggest impact and have a look at what columns are used. If you have tons of inserts only feel free to add a fillfactor of 80-90, of course your mileage may vary

USE <YourDBName>
SELECT (avg_total_user_cost * avg_user_impact/100.0) * (user_scans +user_seeks ) Impact
, [Table] = [statement]
, [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX IX_' + sys.objects.name COLLATE DATABASE_DEFAULT  + '_' 
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_') + ' ON ' + [statement] 
+ REPLACE(' ( ' + IsNull(mid.equality_columns, '')
+ CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END + mid.inequality_columns END + ' ASC ) ' ,',',' ASC ,') + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END 
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
WHERE (migs.group_handle IN (SELECT TOP (500) group_handle FROM sys.dm_db_missing_index_group_stats WITH (nolock) ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1 
ORDER BY Impact DESC, [CreateIndexStatement] DESC ;

If you are writing queries, then you have idea about which fields to index, I can give some links you can check it to get an idea about SQL indexing.

SQL Indexing

SQL Indexing Tutorial

MySQL Indexing Tool

Create Index on Huge Tables

SQL Index Space

Also check this Huge SQL Server Table.

I hope this will give you some idea.

Not the answer you're looking for? Browse other questions tagged or ask your own question.