May 11, 2016 at 12:41 pm
Hello SSC Members,
I am not sure if many SSC members have started to use Column Store indexes but I have a question on if this is possible or perhaps another way to write the SQL.
We currently have a column store fact table setup for Analytic Queries. This is a clustered column store table. I have the table partitioned by TimeDimID.
How we have structured our stored procs is we get the #TimeDim information at the top of our stored proc (users can run 1, 5, 10 year charts). The data is stored in a temp table and we later join to it.
My question is, currently when I write the query as below (Screen 1 and Screen 2 Images), the execution plan is displaying no segment elimination or not able to locate the actual partition through using the Temp Table. Can I achieve SQL Server finding the actual partition through temp table?
I tried an index on TimeDimID but still no luck. I also rewrite using Cross APPLY from ( #TimeDim TD CROSS APPLY (SELECT 1 FROM FACT CS Table). This also did not work.
What I was hoping to achieve is the same behavior as the last 2 screen shots (Screen 3 and Screen 4) shows with hard coding the values in the WHERE clause. This does show SQL Server was able to locate the partition.
Any ideas or perhaps it is not possible without the WHERE clause?
Screen 1 - Query that does join on #TimeDim table, which does find the actual partition.
Screen 2 - Properties of the Column Store index on Execution Plan
Screen 3 - Query that does join on #TimeDim table. I also added WHERE clause with hard coded values for TimeDimID. This does find the actual partition.
Screen 4 - Properties of the Column Store index on Execution Plan
Screen Plan Compare - Comparing the two SQL Statements together.
Hopefully this makes sense.
Thanks,
Brad
May 12, 2016 at 3:19 am
Try capturing the segmentation elimination with this extended event. It might prove more informative than the execution plan in this situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply