| Author |
Topic |
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
 Posted - 2011-09-23 : 07:19:45
|
| hi all, I have a table which has roughly 4 million records.. and it has 1) clustered index on the unique column col12) non clustered index in column col2 in int datatype 3) non clustered index in column col3 in int datatypenow i am using this table with several other table to fetch data..mostly all tables joined with join on condition on col2 and col3 with this table...shall I create a compound index on these two col2 and col3 so as to increase the query performance.. ???or any other sugestions so as to increase its performance???Karthikhttp://karthik4identity.blogspot.com/ |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-23 : 07:27:16
|
| Probably. Look at the execution plan. Then create the index over (col2, col3) and look again. |
 |
|
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
Posted - 2011-09-23 : 07:29:32
|
quote:
Originally posted by russell
Probably. Look at the execution plan. Then create the index over (col2, col3) and look again.
there is a index scan on this table and as per plan its the costliest operation as shown in Execution plan.. Karthikhttp://karthik4identity.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-23 : 07:31:10
|
| before you add it,see whats the current execution plan its giving.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-23 : 07:31:44
|
| Replace the Col2 (only) index with a Col2, Col3 indexYou will also need a Col3 index if you use that in criteria (i.e. the Col3 from the "Col2, Col3" index is very unlikely to be used for a query that only needs Col3) |
|
|
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
Posted - 2011-09-23 : 07:32:20
|
quote:
Originally posted by visakh16
before you add it,see whats the current execution plan its giving.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
the current plan shows index scan on this table.. so will it work if i add a compound index on these two columns???Karthikhttp://karthik4identity.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-23 : 07:33:33
|
| I don't know about anyone else, but I like to look at the I/O STATISTICS, Before and After, when trying a change like this.also to look at the Query Plan to see which index was used... and then whether any other columns should be INCLUDED to help the index cover the query (or any other queries) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-23 : 07:34:31
|
quote:
Originally posted by Kristen
I don't know about anyone else, but I like to look at the I/O STATISTICS, Before and After, when trying a change like this.also to look at the Query Plan to see which index was used... and then whether any other columns should be INCLUDED to help the index cover the query (or any other queries)
I agree. I usually look at I/O stats b4 exec plan. |
|
|
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
Posted - 2011-09-23 : 08:06:26
|
| one intresting fact i came across this performance tunning..when i took the sql part out of the stored procedure and ran, it returns data less than 15 secs...but when i run the same inside a procedure the same code takes more than a 1 min...so i thought existing exec plan cache for the proc will be a problem like that and ran DBCC FREEPROCCACHE for that proc...but after that also same time.. more than a min it takes......any idea why is it taking more time inside a proc and not outside of it.???Karthikhttp://karthik4identity.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
Posted - 2011-09-23 : 08:33:55
|
quote:
Originally posted by visakh16
perhaps parameter sniffing?http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
that's an excellent article.. and i am having the similar problem.. but i used.. both WITH RECOMPILE.. and DBCC FREEPORCCAHE...but still no use....Karthikhttp://karthik4identity.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-23 : 10:16:17
|
| hmm...what else can be solution?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-23 : 13:16:44
|
| Did you add the index?Statistics are up to date?How selective are the columns that are indexed? |
|
|
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
Posted - 2011-09-24 : 05:20:04
|
| in a particular table which has about 4 million record...when run outside a proc the Plan took Clusterd Seek...but when run inside a procedure the plan took index scan.. which is only making the diff..so i tried the query hint with(FORCESEEK).. but it back fired... it took more time than normal slow.....Karthikhttp://karthik4identity.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 07:05:45
|
| EXEC sp_recompile 'MySproc'just before doing your Query Plan on the Sproc route (I know you tried WITH RECOMPILE and DBCC FREEPORCCAHE - just looking for a possible route to finding a solution ...)Is the code in your SProc IDENTITCAL to what you ran "outside" the Sproc? No temporary tables or anything like that? |
|
|
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
Posted - 2011-09-25 : 02:00:00
|
quote:
Originally posted by Kristen
EXEC sp_recompile 'MySproc'just before doing your Query Plan on the Sproc route (I know you tried WITH RECOMPILE and DBCC FREEPORCCAHE - just looking for a possible route to finding a solution ...)Is the code in your SProc IDENTITCAL to what you ran "outside" the Sproc? No temporary tables or anything like that?
I will be back to office Monday.. will check this EXEC sp_recompile 'MySproc'and I am very much sure, both code inside proc and outside are identical.. i just replaced the procedure parameters to declare variable and assigned value to it... thats it...the Indexes were up to date...I ran UPDATE STATISTICS as well...Karthikhttp://karthik4identity.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-25 : 02:06:12
|
quote:
Originally posted by karthik_padbanaban
quote:
Originally posted by Kristen
EXEC sp_recompile 'MySproc'just before doing your Query Plan on the Sproc route (I know you tried WITH RECOMPILE and DBCC FREEPORCCAHE - just looking for a possible route to finding a solution ...)Is the code in your SProc IDENTITCAL to what you ran "outside" the Sproc? No temporary tables or anything like that?
I will be back to office Monday.. will check this EXEC sp_recompile 'MySproc'and I am very much sure, both code inside proc and outside are identical.. i just replaced the procedure parameters to declare variable and assigned value to it... thats it...the Indexes were up to date...I ran UPDATE STATISTICS as well...Karthikhttp://karthik4identity.blogspot.com/
I feel it might be caused due to bad plan created------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-09-25 : 12:59:45
|
| Could you post the stored procedure up here? |
|
|
|
|
Performance tunning..