Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Confusing Query performance


Confusing Query performance

Author
Message
jchapman
jchapman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 216
I'm at a loss on query behavior for an environment I've inherited... and I'll admit now that there are parts about the environment I do not understand.

Here are the particulars... SQL 2005 running in a VCenter VM with a fibre attached Compellant SAN for physical storage. The box has 14g allocated to SQL and 20g allocated to the OS (I don't know why it is configured that way). MAXDOP is currently at Zero... I'm planning on changing that shortly.

Anyway... Here's the crazy part. I have a table with 92 million rows. The table is partitioned. The query is not complex and the index plan shows the index is being used.

Select load_date, account_nbr, open_date from share where load_date ='2014-05-21 00:00:00'



Load_date, account_nbr, open_date are indexed (not clustered).

This query returns the first row instantaneously, and fetches 400K rows in an impressive 45 seconds. I'm VERY happy with that.

Now, If I change the query to include a column outside the index (of any data type)... I won't see my first row for 3+ minutes, and it takes another 3 minutes to fetch the same 400K rows.

According to the Compellant guy... reads are occurring @ 400m/s. Processors on the box never get above 4%. There are no significant wait states visible in Activity Monitory

I'm at a loss as to where to look next... why would including a single column outside the index send me into such a tail spin.

Any ideas on what to try?
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
The query might be experiencing the bad type of parameter sniffing. Not sure if the table has a Clustered Index on it or not, but you are likely experiencing a key or RID lookup. The query is also probably going from a seek to a scan. That will add an extra table hit and extra cost.

But to really be able to help, we'd need to see the actual execution plans and table structure.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

david.gugg
david.gugg
SSC Eights!
SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)

Group: General Forum Members
Points: 932 Visits: 937
I agree with SQLRNNR, and to test this you can add the extra field you are selecting to the current nonclustered index as an included field and see if that improves performance.


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
jchapman
jchapman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 216
Thank you Jason. There are no clustered indexes... (there could be... I just don't know about clustered indexes with the partitions so I have not ventured down that road yet).

You are correct on the seek to scan (I checked the query plan for my "first" speedy version, and saw the index was being used, but never looked back after adding columns to see if it was still being used...turns out ... it's not).

My table is very wide... If seeing the layout of the table adds insight after this, I can post that as well. Based on your tip... here is what I see.

The fast version reports the following:

select sh.dl_load_date, member_nbr, share_nbr, open_date from share sh
where sh.dl_load_date = '2014-05-21 00:00:00'
and closed = 0

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Index Seek(OBJECTSad[VCUWarehouse].[dbo].[SHARE].[idx_Share_Mbr] AS [sh]), SEEKSad[sh].[DL_LOAD_DATE]='2014-05-21 00:00:00.000'), WHERESad[VCUWarehouse].[dbo].[SHARE].[CLOSED] as [sh].[CLOSED]=(0)) ORDERED FORWARD PARTITION IDSad(180)))



When I add a column to the select that is not present in the index... we end up doing a table scan... which I now know is on Tier 3 of the compellant (10K drives) for 92 million rows... no wonder it takes 3 minutes. The million dollar question I have now is... why does adding a non indexed column to the select statement cause the index plan to not use the index on the first 2 keys in one of the indexes (the one it used before)...


StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------
select sh.dl_load_date, member_nbr, share_nbr, open_date, share_type
from share sh
where sh.dl_load_date = '2014-05-21 00:00:00'
and closed = 0

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Scan(OBJECTSad[VCUWarehouse].[dbo].[SHARE] AS [sh]), WHERESad[VCUWarehouse].[dbo].[SHARE].[DL_LOAD_DATE] as [sh].[DL_LOAD_DATE]='2014-05-21 00:00:00.000' AND [VCUWarehouse].[dbo].[SHARE].[CLOSED] as [sh].[CLOSED]=(0)) PARTITION IDSad(180)))




Modifying the index to include Share_Type does cause the index to get used again...and things are all good. Then I add balance, and blamo, back to table scan.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
In this case you are going to a query that doesn't have a covering index. It also appears you do not have a clustered index because you are reverting to a table scan.

But this isn't the full execution plan.

Please post that so we can get the full picture.

Here is an article on how to do that.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

jchapman
jchapman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 216
Attached are the query plans...one that shows the index seek (when only indexed columns are included in the select statement), and the second with the table scan (a numeric column outside the index) is included in the select statement.

Thank you again for your time.

Does it make sense to you though, that the query plan would change to a table scan just because one of the 4 columns in the select is not in the index? That's the part that I'm hung up on.

P.S. The query plan with the table scan does recommend a new index... to add the "non indexed" column to a new index. But it does that for every column I add to the query, and it is unrealistic to add every column on the table to an index...
Attachments
QueryPlans.zip (2 views, 3.00 KB)
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
jchapman (5/22/2014)


Does it make sense to you though, that the query plan would change to a table scan just because one of the 4 columns in the select is not in the index? That's the part that I'm hung up on.


Yes it makes sense.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

david.gugg
david.gugg
SSC Eights!
SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)

Group: General Forum Members
Points: 932 Visits: 937
If the added field in the select list is not in the index, the query optimizer only has two options:
1. Look up the rows you need with the nonclustered index, then use the rowIDs to go back to the clustered index/data page and look up the additional field needed (called a key lookup)
2. Run through the clustered index/heap, which has all the data, and find all the rows needed.

The query optimizer will choose what it believes is the cheapest of those two options depending on stats.

Bottom line is that if you want to solely use a nonclustered index, you need to make sure any fields that are being used for filtering are part of the index, and any fields that are being returned are at least included.


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
So looking at the queries, it really is nothing more than the lack of a covering index. This is normal behavior. The query is returning enough results that a table scan in the absence of a covering index is more efficient.

If you create a covering index for the second of the two queries, both queries should be able to use (better yet, modify the first index to add the extra column(s)).



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

jchapman
jchapman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 216
I've been experimenting... riddle me this...

My misbehaving share table is partitioned (with LightSpeed, this is 2005 environment) into 183ish partitions.

Here is what I did I just copied my 92 million row table into a new table, all in a single partition... I cannot make it table scan regardless of the number of columns I add to my query... indexes are the same.

Does this make sense (is this maybe a sql 2005 partitioning issue?) could it be that when I include a column not in the index (forcing a merge of data from multiple file groups) ... I am forced into a table scan.

My non partitioned table is subsequently faster than the partitioned table.

Here is my theory... Since partitioning is not native to SQL2005... I believe the plan to use the index breaks down when columns in the query are not all in the same filegroup. (The index with my 4 columns is in the default file group, when I include more columns, then I'm bounced into one of the other partition file groups... and sql server decides to not use the index because it has to work from a different file group?) Does this make any sense at all?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search