August 24, 2018 at 8:19 pm
Hi everyone,
1. My table has 8mil+ rows and 350 columns. But our query does not include calculation. It's just to join the tables and extract only. Do you think Clustered Columnstored index is suit for us?
2. In my laptop I always get insufficient memory error whenever I tried to create clusted columnstored index. Any idea how to resolve? Below is my laptop resource.
| Object_Name | Cntr_value |
| SQLServer:Memory Manager | 3645440 |
| SQLServer:Memory Manager | 3625208 |
| Database Name | Database_Size | Unallocated space |
| DB_NAME | 77518.00 MB | 9548.98 MB |
| reserved | data | index_size | unused |
| 43842448 KB | 43161632 KB | 453040 KB | 227776 KB |
| Physical memory |
| 16657508 |
Thank you,
Best Regards,
August 27, 2018 at 4:40 am
If the queries are primarily point lookup (one or only a limited set of rows, say hundreds or less), then no, columnstore is a very bad solution for you. If, on the other hand, they're returning very large data sets (at least thousands), it's possible that columnstore might help.... maybe, but probably not. You'd have to test it to be sure. Columnstore really is primarily meant for analytical style queries with aggregation and the like.
As to your laptop, make sure you're on the latest SP/CU first. However, you might not have enough memory to create a large index on a laptop. You might need to do that on a bigger machine, then move it to your laptop. Here are a few suggestions you can try to work around this.
"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
August 28, 2018 at 2:01 am
Dear Grant,
Thank you so much for your information.
Best Regards,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply