April 3, 2015 at 2:42 pm
I've been asked to look at using Clustered Columnstore indexes for one of my tables. The table contains about 5 million records with about 50 columns. The max field size is a NVarchar(MAX) with max field length currently of about 4k characters. It's only about a gigabyte's worth of data. The table is about 50% R/W operations. Currently, we have multiple indexes with no clustered index due to some performance issues that happened in the past. I've been attempting to determine if it's even really worth it to switch over. I feel that the table is still fairly small with minimal columns and don't believe there will be any noticeable improvement over traditional indexing.
Any thoughts?
April 3, 2015 at 3:55 pm
Really short answer, VARCHAR(MAX) is not supported. You can't put that table into columnstore.
Long answer. Columnstore indexes are extremely useful for aggregation queries. They also work ok for some types of scans. But, if you're doing point lookups on your table, they suck. They don't support traditional OLTP very well at all.
From the sounds of things, I'd focus on figuring out where you should have a standard clustered index on the table. Most tables (and my most I mean 99.x%) should have a clustered index. There are exceptions, but they're pretty exceptional. Most of the time when people have had performance problems from a clustered index it's because the index was in the wrong spot.
"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
April 3, 2015 at 3:57 pm
Thanks' Grant, I'll do that.
April 3, 2015 at 4:05 pm
NVARCHAR (MAX) is not supported. I mistyped that. Still, everything but my typo is what I meant to say.
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply