sql server performance

Posted by Sriharim under Sql Server on 7/10/2015 | Points: 10 | Views : 513 | Status : [Member] | Replies : 8
How to improve Sql server query performance. I have created already clustered index and non clustered index, still the query in store procedure is slow.

will index seek improve performance, if yes how to create index seek ?

Please suggest ?

---
Srihari



Responses

Posted by: Bandi on: 7/10/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Sriharim on: 7/10/2015 [Member] Starter | Points: 25

Up
0
Down
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.

---
Srihari

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

Posted by: Bandi on: 7/10/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Sriharim on: 7/10/2015 [Member] Starter | Points: 25

Up
0
Down
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 .

---
Srihari

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

Posted by: Bandi on: 7/10/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Sriharim on: 7/10/2015 [Member] Starter | Points: 25

Up
0
Down
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 .

---
Srihari

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

Posted by: Sriharim on: 7/13/2015 [Member] Starter | Points: 25

Up
0
Down
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 .

---
Srihari

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

Posted by: Manub22 on: 9/22/2015 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response