Impact of SELECTed colums on the execution plan

Hi guys,

I have something weird that I want to understand.

I have a big table, containing around 17 millions of rows. This table has been progressively indexed over time, mainly by following some of the recommendations of the DB tuning advisor. As a result, we have around ten indexes on this table, some of them are using the new "INCLUDE" feature of SQL2005 indexes with non-key data.

The problem I have is the following : I have a very simple query (SELECT <some columns> FROM that_table WHERE <simple clause conditions>) which should benefit from the indexes built on the table. But depending on the columns I select, the execution plan varies totally !!

With one column selected, the good index is used, and the query is fast.

With two columns selected, the execution plan becomes complicated (several different indexes seeked in parallel). I guess the optimization system is trying to get benefit from some non-key data included in some of the indexes ? But I'm not sure...

With a "SELECT *" no appropriate index is used. The excution plan shows it will do a "clustered index scan" over the primary key (which is not part of the where clause), resulting in a full scan of the table... OK, I know that "SELECT *" is not a good practice, but anyway, this result is very surprising.

(I tried to make it short, I can detail the queries and the construction of the indexes if needed, but the main info is there I think)

How can it be possible to have so many differences, just by changing the list of SELECTed columns ? It's beyond my understanding of what is an index...

Thanks for your help

Mathieu

Hi Mathieu,

There's a few things that could be going on here, and it's hard to make a definitive call without investigating the query plan, but...

If you include columns in the select list that are not served by the index the used in order to serve the where clause, the engine will need to perform what's known as a bookmark lookup in order to retrieve the column values that are not included in the index. It of course gets a bit more complicated if the optimiser chooses parallel indexes.

So, if your query was something like:

SELECT id, name, dob

FROM tblPerson

WHERE id = 993

You can also try execting the query with OPTION (MAXDOP 1) in order to determine if the generation of a parallel query plan is introducing unnecessary overhead.

You would create an index on the id column and list name and dob columns in the new INCLUDE clause. This would be a good starting point, but you'd need to take into account all other queries against this table in order to decide if indeed this. Indexing is a huge subject, so get googling! :)

Cheers,

Rob

|||

To be clearer :

1st case :

SELECT a FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

c AND d are covered by an index, and the column a is included (INCLUDE keyword) in that index. b, c, and d are regulars data columns (not involved in PK). The exec plan is fine, the query is fast.

2nd case : I just add a column in the SELECT, the where clause is left untouched

SELECT a, e FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

Here, the column e is not included in the index mentionned hereunder. The exec plan becomes complicated, involving others indexes in parallel. The query becomes slow.

3rd case : retrieving all columns, the where clause is still left untouched

SELECT * FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

This time, the exec plan is "clustered index scan" over the PK. But the PK does not appear in the where clause ! Result is a full scan of the 17millions of rows... catastrophic !

While writing this post, I'm getting convinced that SQL Server is perturbed by the INCLUDEd columns in the indexes... What's your opinion ?

|||

Hi Mathieu,

As mentioned, if a column in the select list is not included in the index, a bookmark lookup is used. So, in the 2nd case above, you would either include column e in the index used for the operation either as a key value or in the INCLUDE list.

In the 3rd case, it would appear the optimiser has decided that rather then performing lookups for all the columns not servicable by the index (ie the SELECT *), it has chosen to perform a full scan. I'd need to see the full query plan and the schema to provide more info, but it sounds like you need to revisit your indexing strategy from the ground up.

Although INCLUDE is new to 2005, I've never had nor heard of an issue directly related to its use (yet).

Cheers,

Rob

IIS and the web.config

Hi,

My application is acting weird and the research I've done leads me to an answer why it's breaking, but not how to fix it.

My web app is hosted by a remote company. I have the main website sitting in the root web directory and in a subfolder I have another application that will handle login and updating the content in the SQL 2K5 backend (not an Express DB).

The website itself is able to pull in informaiton from the DB without any trouble, so I know it's able to connect to the DB and everything is peachy. The login app though is acting as though it can't get to the DB. I've applied the ASPNET schema to the DB using the aspnet_regsql.exe from the framework and the login application is using the same connection string as the main website.

One thing to note here is that the website and the login app have seperate web.config files since they were developed as different projects.

With my customized provider defined in the web.config for the login app I get this error on page load:

"Parser Error Message:Itis an error to use a section registered asallowDefinition='MachineToApplication' beyond application level. Thiserror can be caused by a virtual directory not being configured as anapplication in IIS."

If I comment out the custom provider, the page loads but when I try to create a user it tells me that "...the SQL Server couldn't be contacted. This may be because it doesn't accept remote connections." Thing of it is that it's not a remote connection and it's the same connection string and db as the other site.

Now, the research I've done points at the two different web.config files, which makes sense. But how do I make the one web.config work for both the website and the subdirectory with the login app? Keep in mind that I can't create any new virtual directories or modify IIS since this is a remote hosting company.

Can someone help me understand the fix with a little more clarity?

Your admin application needs to be setup as a Virtual Directory. Your hosting company will have to do this for you. You know you can make it all one application and just control which Roles/Users have access to the admin folder in the web.config? :>

Iis & Vs.net

This is a weird question, and I think it might have something to do with my SQL server.
I am running SQL server 2000 Developer Edition on XP pro, and now have been trying to instal VS.Net 2003, yet when I try to install, the installer disk tells me I dont have IIS and FPSE running and configured on my computer... I run through the steps given and IIS still doesn't appear in "Computer Mangaement". The "Inetpub" is on my C: drive, but http://localhost doesnt exist.
I figure it has something to do with my SQL server but I don't know what to do. I need to be able to create Web projects on my computer with VS.NET.
Should I uninstal SQL Server? Do I do this in the registry? Should I instal VS.Net anyway? Do I reformat my hard drive and start from scratch?

Any help will be unbelieveably appreciated, time is short and I'm going nuts.Your PWS (Personal IIS) is not running. Start the service and/or check to see if you have the service installed at all.|||There is no PWS for XP Pro, or rather IIS is the PWS. I followed the simple instructions found everywhere online and on the vs.net CD. Nothing. IIS still doesn't appear under Administrative Tools. I can't figure out why.

*chucks computer out 2nd story window*|||I went and uninstalled it fully and installed it again, I did this like 3 times over the past week, but last night it actually took. huh. If I wasn't so happy it was running I would be pissed that it was so tempermental.

Now all I have to do is create a webservice, and database in time to turn in for my midterm next monday. wish me luck!