sql server performance

index seek would be better than Index scan or table scan

1. May be try out by creating proper indexes on WHERE condition columns...
2. try to refactor query to code in better way
3. try to find out on which table/query the SP is taking more time...

But we cannot sugest on this with out looking into the query/stored procedure...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

For below table,

create table te(a int  identity(1,1),b varchar(10),c int,d varchar(10))

after inserting records with duplicates records in column b and c, I created nonclustered index

create nonclustered index te_non on te(b)

In table properties (select tablename only and enter Alt+F1) , it showing

index_name     index_description                              index_keys
te_non	       nonclustered located on PRIMARY           	b

In index description, why it showing nonclustered located on PRIMARY , i have added only nonclustered and identity but not primary key.

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

PRIMARY is the default file group in SQL Server.. while defining Nonclustered index you didn't mention the file group. So it has taken default PRIMARY file group....

As you doesn't have PRIMARY KEY, the data will be stored in the HEAP....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Generally, for nonclustered index column, index scan will happen. If no index on column, then table scan (data will be in heap) will happen.

Index seek search of rows will be only for clustered index column ?

For nonclustered index column, will index seek search happen ??

Please correct if statement is wrong and please clarify for the 2 question .

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

there will be index seek for NonClustered(NC) indexes also..... from index seek of NC indexes it gets the pointer of actual data, which is in heap in your case. Because you doesn't have clustered index......

Clustered Index will be more useful as it sorts the data than HEAP....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Index seek search for NC indexes is not happening all the time. some index times scan and some times index seek.

Please tell me, at what situations, there will be index seek for NonClustered(NC) indexes .

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Index seek search for NC indexes is not happening all the time. some time index scan and some times index seek.

Please tell me, at what situations, there will be index seek for NonClustered(NC) indexes .

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Just creating Clustered or Non-Clustered index won't increase your Query performances.

You need to check the columns is the SELECT list and WHERE clause, also if JOINS then columns involved in JOIN ON condition.

There are other factors like stale STATISTICS, so you need to update your table stats.

Seek/Scans depends upon the Query WHERE clause and columns selectivity and statistics. Either index Clustered on non-Clustered can do seek or scan depending on the column values.

Your question is very blunt, if you can give any example then people can help you creating correct indexes for you.

~Manoj
SQLwithManoj.com

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator