SQL Server Performance Forum – Threads Archive
Indexes puzzle
Hi, I need some information about Indexes. Q1. Is this nice (performance wise) to create composite Indexes? Sure this depends upon the requirement. But I think this actually hampers performance. M I correct? Q2. Which of the following scenario is better (performance-wise)?Sc1) Creating indexes and then inserting data into the tables.
SC2) Inserting data into the tables and the creating indexes.
NB: In both scenario data size is more than 10 GBs. Regards,
Ashish
Ashish Johri
http://www.sql-server-performance.com/clustered_indexes.asp
http://www.sql-server-performance.com/nonclustered_indexes.asp 1) Indexes creation is purely on requirement and you need to be careful before chosing such requirement, based on benchmarking of your environment and optimized code should get you the performance even though the normalization is bit out of standard. 2) Sc1 is always better and make sure to keep the load transactions in smaller batch to maintain the optimum performance of sizes. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
You may just need composite indexes to cover your queries. In most cases a composite index is not the ideal candidate as the clustered index for a table.
ThanksSatya. I have some comments for your answer. 1) There is one command DBCC show_statics(Tablename,indexname) which displays density. I am not sure whether density should be high or low?
2) My PM told me that if you make indexes before you insert any data into this then this is an overhead. For each row you insert data Server needs to arrange this. I am confused now as your opinion is at 180 degree to that of my PM.
Ashish Johri
1) BOL states: The results returned indicate the selectivity of an index (the lower the density returned, the more selective the index is) and provide the basis for determining whether an index is useful to the query optimizer. The results returned are based on distribution steps of the index. 2) This one it is purely dependant on the transaction batch and how you are inserting the data, I have a proven procedure in this regard at our place which is running since last 2 years on a 20 million rows table. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks a ton. Actually higher the density better your index is.
I just created indexes on table sales in my DB where sales_id is the identity column. I want to share my findings with all of you. select count(distinct sales_id) from sales
–sales_id = 9450
select count(distinct purchaser_name_id) from sales
–purchaser_name_id = 9450
select count(distinct tmp_purchaser) from sales
–tmp_purchaser – 7691
select count(distinct sale_dt) from sales
–sale_dt – 4654
create clustered index index_sales_id on sales(sales_id)
dbcc show_statistics(sales,index_sales_id)
–density = 1
drop index index_sales_id on sales
create clustered index index_purchaser_name_id on sales(purchaser_name_id)
dbcc show_statistics(sales,index_purchaser_name_id)
–density = 1
drop index index_purchaser_name_id on sales
create clustered index index_tmp_purchaser on sales(tmp_purchaser)
dbcc show_statistics(sales,index_tmp_purchaser)
–density = 0.8332041
drop index index_tmp_purchaser on sales
create clustered index index_sale_dt on sales(sale_dt)
dbcc show_statistics(sales,index_sale_dt)
–density = 0.5477067
drop index index_sale_dt on sales –Outcome
–1) You can’t create more than one clustered index in one table.
–2) Greater the density better the index Now my question is whether density is only parameter to usefulness of index? Regards,
Ashish
Ashish Johri
]]>