Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts

Friday, March 30, 2012

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

Impact of "Rows per batch" on OLE DB Destination?

OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?

I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:

For each row:

begin transaction
insert row
commit transaction
thanks,
Nitesh

Nitesh Ambastha wrote:

OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?

I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:

For each row:

begin transaction
insert row
commit transaction
thanks,
Nitesh

Hi Nitesh.
Why not do some benchmarking for it? Find out the answers. It'd be really useful info for the community.

-Jamie|||Mmmmmhmmmmm, I'd like to see that! :)|||OK I thought I'd have a go at this. Where is this "rows per batch" parameter/property?

I can't see it. Am I being really dumb?|||

Jamie,

It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.

Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).

Thanks
Sutha

|||

Sutha Thiru wrote:

Jamie,

It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.

Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).

Thanks
Sutha

OK, I'm glad I'm not heading for the loony bin. I was looking at an IDW15 instance which doesn't have it. I've checked my IDW16 VM and its there.

I wonder why it (and many other properties) were removed on IWD15?

And here's another question. Why are they (Rows per batch, keep nulls, table lock, check constraints, keep ID, max insert commit size) not available via the preoperties pane?
cheers Sutha

-Jamie|||

Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.

Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?

Thanks
Sutha

|||

Sutha Thiru wrote:

Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.

Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?

Thanks
Sutha

As an aside...I don't think they are new properties. I'm *sure* I remember seeing them way back when. It seems to me like they disappeared for IDW15 and now they've reappeared again. Weird!!

-Jamie|||

I did test way back when on the perf of loading batches per transaction. I generally found that 2000 was a good figure. However I supsect this depends greatly on,
the wdith of the rows being inserted,
the io configuration
memory in the system

Interestingly, why is there no batch rows setting for the SQL destination?

|||I'm guessing cos it uses bulk insert which doesn't allow you to specify batch sizes (does it?)

-Jamie|||

In SQL server 2005, it is our decision to remove properties "RowsPerBatch" and "BatchSize" from SQL server destination adapter.

Reason is SQL server dest adapter is not written to utilize the functionalities the two properties provide - e.g. query plan optimization.

We will consider whether to enable them in the next version.

|||

"Rows per batch" is a Fast Load Option. In the OLE DB Destination, it is only visible (along with some other options like Keep Identity, Keep Nulls etc.) if you choose the Data Access Mode "Table or View - Fast Load". If you choose the data access mode "Table or view", they won't show up. They never disappeared from IDW15, my guess is you are looking at it with the non fast load option, and therefore are not finding it there.

They are available from the properties window, but in a different way. You can specify them in the FastLoadOptions. For example,

FastLoadOptions : TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 1000

sql