SQLServerCentral / Discuss Content Posted by G Vijayakumar / Article Discussions / Article Discussions by Author / Execution Plans / Latest Posts

RE: Execution Plans

ranjan.singha — Tue, 14 May 2013 06:06:02 GMT

Nice article.

RE: Execution Plans

komathi.radhakrishnan — Thu, 03 Jan 2013 05:16:12 GMT

Nice article :). Thank you for explaining with examples

RE: Execution Plans

Robert Purnell — Wed, 08 Sep 2010 20:21:53 GMT

Good basic introduction to execution plans and optimisation but poor English really lets this article down. In many places the real message is lost by poor explanation. Simple and clear communication is hindered by incorrect English and over laboured expressions. This article should be edited by someone with better English skills to help with clarity.Otherwise, good job.

RE: Execution Plans

bharathi8179 — Sat, 15 May 2010 08:26:53 GMT

Very nice article.

RE: Execution Plans

AK1516 — Tue, 04 May 2010 14:29:32 GMT

Really a great explanation.Thak you vjyKartheek Anumolu

RE: Execution Plans

AK1516 — Tue, 04 May 2010 14:28:56 GMT

Really a great explanation.Thak you vjyKartheek Anumolu

RE: Execution Plans

proactiveamit — Fri, 09 Oct 2009 03:35:19 GMT

I would like to know why non-clustered index on three or more columns does not work even when the columns are put in the same order as in the index and instead of that clustered index scan is called in the Execution plan.I found through search that Non clustered index is not called because my where clause was not enough selective but what if I need more data and retrieval should be fast.

RE: Execution Plans

nigel.meakins — Tue, 11 Aug 2009 17:28:11 GMT

I actually think Phoqueme has some valid points regarding the quality of the article and the need to be succinct. Okay, doing a SoundEx on his name may be a little sad, but not anywhere near as sadly pretentious as all that 'first steps to the set based thinking shift' and 'Rebar' twaddle. I mean, where did that RBAR term come from anyway? Someone banging on about how bad cursors are, for the umpteenth time no doubt.Oh to be a SSC champion... perchance to dream.

RE: Execution Plans

nigel.meakins — Tue, 11 Aug 2009 17:15:25 GMT

Very wordy verbose article that could have been summed up in a quarter of the space. The broken English made it less fluid to read although generally understandable. Some key points missed regarding the basics of the internals of the optimiser and why some methods are more favourable than others.

RE: Execution Plans

venkatasrinivas_donavalli — Mon, 10 Aug 2009 12:52:35 GMT

Excellent article, I really like your logical approach and elegant examples. Even I did the same kind of research as you have domne on this. Both are very matching. But i lerant few more points from this article.

RE: Execution Plans

Jeff Moden — Tue, 27 Jan 2009 20:30:13 GMT

[quote][b]phoqueme (1/27/2009)[/b][hr]When giving examples, there is no need to blunder through every possible case of Table Scans before "realizing" MS SQL will only use the index scan on the column that has been indexed. Although this is the way a beginner would do it while playing with MS SQL, there is no need to write it in your article. This only distracts the reader. It's nice to hear a different flavour of english but in many cases the meaning of your sentences was actually incorrect.[/quote]Heh... all that from someone with a handle like "phoqueme". Grow up.

RE: Execution Plans

a-848662 — Tue, 27 Jan 2009 17:16:52 GMT

When giving examples, there is no need to blunder through every possible case of Table Scans before "realizing" MS SQL will only use the index scan on the column that has been indexed. Although this is the way a beginner would do it while playing with MS SQL, there is no need to write it in your article. This only distracts the reader. It's nice to hear a different flavour of english but in many cases the meaning of your sentences was actually incorrect.

RE: Execution Plans

bharathi8179 — Fri, 07 Nov 2008 07:15:22 GMT

Very good article for a beginner who does not have any idea about the execution plan. Thank you:)

RE: Execution Plans

aspirant.dba — Thu, 15 May 2008 08:35:47 GMT

Good Job Vijay & appreciate your sharing attitude. Please continue posting such in future too.

RE: Execution Plans

chandrajit.samanta — Wed, 14 Nov 2007 04:32:04 GMT

Date: 14th November 2007Hi Vijay!I am Chandrajit Samanta, working in a product based s/w org (Metrica Systems Pvt. Ltd.) in Bangalore. We are using SQL Server 2000, C#, ASP.NET for our product. My work is mainly database intensive. Frequently I have to deal with heterogeneous database environment like Informix, Oracle, Sql Server simultaniously. I have to fetch records from Informix, oracle database to our SQL Server Database. For which I have to create several DTS.But the problem is, original tables (Informix,Oracle) has billions of records. For that to run one simple query like (Select * from Table_name where dtDate > '2007-11-01 17:00:00.000') in DTS it tooks 25min - 35min time. We have only "Read Only " access i.e. select priviledge to all those (Informix,Oracle) database. Please tell me how to improve such query so that this query running time is minimized.I am waiting for your response.Regards,Chandrajit Samantachandrajit.samanta@gmail.comMBM(Systems),B.E.(Electrical)9986044300, Bangalore

RE: Execution Plans

ganeshc27 — Sat, 21 Apr 2007 02:29:00 GMT

Nice article.

RE: Execution Plans

kannanchery — Thu, 12 Apr 2007 09:19:00 GMT

Vijay,

Good Explain and i like the way of explaining.

RE: Execution Plans

Mohan Mariyappa — Wed, 15 Nov 2006 19:07:00 GMT

As long as one understand what author is trying to explain, what language or for that matter grammer do not matter. One needs to understand, he is not teaching English. Great work VijayKumar.

RE: Execution Plans

kakarato — Thu, 26 Oct 2006 03:37:00 GMT

Good Thumbs UP. Must read for beginner.

RE: Execution Plans

vidhya-373059 — Tue, 10 Oct 2006 04:32:00 GMT

Goodone.Even Beginner can Understand from his explanation.Thanks Vijay!!!

RE: Execution Plans

Pankaj Khanna — Mon, 09 Oct 2006 22:41:00 GMT

Excellent article. It is really well explained.

RE: Execution Plans

J Wright — Mon, 09 Oct 2006 05:43:00 GMT

Good article. Used the exact names of the index methods many times(so it should be easy to find in the future).

However, who really needs help in tuning a simple (select somecolumn1, somecolumn2, somecolumn3 from sometable) query? Don't get me wrong, the article had value, but I never have perfomance troubles with the simple queries. The ones with 4 or more joins involved cause troubles for us to tune.

It's not just this article... I'd love to see more exaples that are closer to real world usage of a SQL server using realtional data...

I'd love to see a follow up article on this that would give us a deeper understanding of SQL tuning.

RE: Execution Plans

Dionisio T Manzo — Thu, 31 Aug 2006 09:40:00 GMT

A nice topic but it needs to explain what does the statistics is all about so that the execution plan can say the sql statement is optimized.

RE: Execution Plans

Dejan Stamenkovic — Sun, 14 May 2006 16:49:00 GMT

Found this article very average and a bit too self congratulating.

RE: Execution Plans

bijulsoni — Thu, 04 May 2006 04:31:00 GMT

hello sir,I read your article and found it was so much facinating.i was familiar with the word Execution plan but not knowing much about it.Your article give me brief information about How to utilize performance using clustered index seek Thanks a lot

RE: Execution Plans

shardul shah — Mon, 17 Apr 2006 18:22:00 GMT

Thanks so much ...

Article has cleared all the doubt.

RE: Execution Plans

Gilbert Grant — Thu, 03 Nov 2005 02:02:00 GMT

This article was overly laborious and not cutting to the point. It would be nice to see articles on this site rated on level of expertise - e.g. beginner, intermediate etc. in relation to the subjects they discuss - so that one has an an indication as to whether or not one will learn anything from reading them. This would be a beginner's article.

RE: Execution Plans

Anand Jeyapaul-229078 — Wed, 19 Oct 2005 04:36:00 GMT

Excellent article. Simple and Practical.Followup to this article necessary on when to use various scans etc.

Anand

RE: Execution Plans

Glenn-266811 — Wed, 12 Oct 2005 16:00:00 GMT

Good, informative and simple.

Cheers

Glenn

RE: Execution Plans

Amit Lohia — Wed, 12 Oct 2005 12:55:00 GMT

Nice article. Vijay, keep writing. Take this article to next level (explain different types of joins hints , covering index). I wish I get time to write on these lines.

RE: Execution Plans

T.P.ANNAPURNA — Mon, 10 Oct 2005 05:48:00 GMT

Excellant article. I think theme of the article is more important than his English. Hope to c some more articles from Vijay Kumar

RE: Execution Plans

SyedAli — Sun, 09 Oct 2005 01:59:00 GMT

I like this type of Article. Excellent.

RE: Execution Plans

Peter Kryszak — Fri, 07 Oct 2005 10:31:00 GMT

I felt that this could be confusing for a beginner because the author was trying to optimize a simple query that couldn't be optimized. Instead the author changed the requirements of the query to:1. put all the data into a "covering index", so you get another full table scan, but in this case the table is known as an index (it's still a table)2. put some constraints on the result set to allow the use of indexes that had been createdI really thought that this was backwards to change requirements to fit the technological implementation.

RE: Execution Plans

David Poole-249495 — Fri, 07 Oct 2005 10:10:00 GMT

You can force a Clustered Index Seek by adding a seemingly superfluous WHERE clause that searches for values in that index between two values.For example, if the clustered index was on a field called StateId and StateId was a TINY INT then we could write add a WHERE clause that says WHERE StateId BETWEEN 0 AND 255You can get a performance boost this way but a clustered index seek DOES NOT always result in a lower cost query. You have to experiment with your application to see what the affect is.

RE: Execution Plans

sushila — Fri, 07 Oct 2005 10:07:00 GMT

here's a link to Chris's article on cluster that index

RE: Execution Plans

William Kimball — Fri, 07 Oct 2005 09:41:00 GMT

In reply to: "I would also like to see some examples of when to use clustered index and when to use non-clustered index if possible."Start with the behavior of the two different index types and the answer starts to become clear.1. The "natural order" of records in a table is the physical order of the records in the table as they would be scanned from first to last without an index or sort order.2. A "clustered index" physically reorganizes every record in the table such that the "natural order" of the records in the table becomes the logical sort order of the clustered index. A seperate index records at which record the selected column value(s) change. This is why there can be only one "clustered index" per table.3. A "non-clustered index" leaves the records as they are in the table and creates a seperate lookup tree of only the column(s) for which the index is built. Each node of this tree points to the location in the table of the corresponding record value.In short:1. Use a "clustered index" only on values that are highly repetetive in a table and for which you often select sequential ranges of data. The "clustered index" will then very quickly return entire blocks of matching records.2. Use a regular index on values that are generally distinct and for which you often return individual records.Rule of Thumb: If you seek values from a table based on a WHERE clause of a single column, be sure to have an index on it. If the values of the column are highly repetitive, use a clustered index. A column that will have a distinct value for every record is a poor candidate for a clustered index.

RE: Execution Plans

buktseat — Fri, 07 Oct 2005 08:08:00 GMT

Clearly the writer is knowledgable, but this cat definitely needs a proofreader or a ghost-writer or something... Very hard to understand and follow at times.

RE: Execution Plans

Sameer Raval — Fri, 07 Oct 2005 07:10:00 GMT

Excellent Article.

I Like the way you explain, example helps more than 100' s of lines of description. Obviously the target audience is sql server dba/developer so it's always good not to start every article with how to create indexes. More articles are expected from you.

Thanks,

Sameer Raval

RE: Execution Plans

elaine-131617 — Fri, 07 Oct 2005 07:08:00 GMT

I opened this article because I "knew I should" but wasn't really looking forward to it. Sometimes these kinds of articles get dull and then very confusing (probably because I'm having difficulty paying attention). This article was clear and well thought out. The step by step examples with screenshots made it very easy to follow along. Thanks for a great article.

RE: Execution Plans

Mike Hinds — Fri, 07 Oct 2005 06:42:00 GMT

Excellent explanations. I appreciate that English is not his first language; however his knowledge of the topic and exhaustive examples provide a much clearer lesson than I have been able to get from BOL.

I look forward to reading more from this author.