Confusion in creating indexes

[Aniruddh Joshi]

Hi All

This is regarding indexes in MSSQL.
I have a query which takes long time ( while populating data from ResultSet ) when large number of users are online.
It is a select query with some joins in it.

I wanted to index all columns in the joins and in the where clause.
When I open MSSQL studio, and see the indexes for a table it shows some of these columns in the indexes as a primary key.

My doubt is that are all the primary keys indexed by default or do I need to index the columns explicitly.

Please help me by clarifying it. Also, what are the probable reasons of a query taking long only when the number of concurrent users are higher.


The primary keys are composit... found this about clustered indexes

[Paul Sturrock]

My doubt is that are all the primary keys indexed by default or do I need to index the columns explicitly.

SQL Server will create an index by default when you define a primary key constraint so no.

Please help me by clarifying it. Also, what are the probable reasons of a query taking long only when the number of concurrent users are higher.

Your server is doing more work, simple as that. Have you run your query through the analyser to see if SQL Server suggests any indices?

[Fatih Keles]

Creating index on all join columns may not lead you to best results. An index is most useful when data in index column has a higher number of distinct values. RDBMS creates unique index on PK constraints. While creating an index if index values are as many as table rows which means that every column data is unique then it is mostly likely that you will not get your query time boosted. An index is a smart way of accessing rows faster than full table scan.

Most database -I know MySQL and Oracle does- provides analyze tools for sql optimization, this tool shows how RDBMS is joining tables and accessing rows, either using an index or full table scan and how much it costs. This is a general perception not specific to Ms Sql Server, hope it helps.

Regards,

Fatih.