Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Confusing Query performance Expand / Collapse
Author
Message
Posted Thursday, May 22, 2014 2:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
jchapman (5/22/2014)
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.

Is it doing an index scan and RID lookup instead?

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?



Your columns would all be available in all of the files of the filegroups. Your rows would be in different files/filegroups for the partitioning.

Since you are partitioning, the partitions are probably aligned with the columns of that initial query. Adding a new column to the query now causes the QO to have to use a table scan because it is cheaper to execute that way.




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1573774
Posted Thursday, May 22, 2014 2:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 6:20 PM
Points: 25, Visits: 167
RID Index: Yes my all in one partition is...
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES[Bmk1000], [Expr1007]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT[VCUWarehouse].[dbo].[share_jc3].[idx_Share_Mbr]), SEEK[VCUWarehouse].[dbo].[share_jc3].[DL_LOAD_DATE]='2014-05-21 00:00:00.000'), WHERE[VCUWarehouse].[dbo].[share_jc3].[CLOSED]=(0)) ORDERED FORWARD)
|--RID Lookup(OBJECT[VCUWarehouse].[dbo].[share_jc3]), SEEK[Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

So in the absense of indexing a large chunk of the relevant 240 columns ... I can either ditch the partitions, or deal with poor query performance for non-index mentioned colums?

Would partitions in SQL 2008 behave the same?
Post #1573783
Posted Thursday, May 22, 2014 2:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
I would expect the behavior in 2008 to be more in line with what your single partition example was.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1573790
Posted Thursday, May 22, 2014 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 6:20 PM
Points: 25, Visits: 167
Your columns would all be available in all of the files of the filegroups. Your rows would be in different files/filegroups for the partitioning.


I am not 100% certain this is a true statement (but given your answer, it may not matter); so I'll toss this out for my general education. I intentionally put my index in a filegroup called P_FG_Default; there are no tables in this filegroup (it was actually empty until I created my index).

The Share table, broken into all those partitions all live in monthly derived/named partitions. The only columns in the default file group for the share table would be in my index... does that matter?

(Thank you very much once again for your time, and the education).
Post #1573793
Posted Thursday, May 22, 2014 2:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
Your question appeared to be about the partitioning and that is what my answer was in reference to.

Filegroups have no impact on table scans or index seeks when just splitting indexes out to different filegroups.

As an aside, I recommend not building your indexes in a separate filegroup. I know there is a lot of places on the internet that make the recommendation to split indexes from data. That was a well and good back in SQL 2000. With larger and larger databases and newer versions of SQL Server - it's not that great.




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1573797
Posted Thursday, May 22, 2014 2:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 6:20 PM
Points: 25, Visits: 167
I will start reading up on indexes with partitions. On the brighter side... I have convinced the DBA's here to start working up a plan to migrate to '08.

Thank you again for all your time today.

Post #1573799
Posted Thursday, May 22, 2014 3:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
You are welcome.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1573811
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse