December 18, 2019 at 7:10 pm
What would be several things to keep in mind regarding WHEN NOT TO CREATE/USE Columnstore indexes? Like for example my manager was asking this morning : 'What can possibly hurt us and how after we create a dozen of columnstore indexes on several tables? what are possible worst case scenarios or side effects that may happen?' (i also read somewhere that columnstore indexes are "not really for OLTP databases"?)
Also, Memory Optimized Tables may be a bit too much work and things to worry about/support for our DBAs (filegroups, database settings and a lot of things need to be changed in database in order to enable it for creating memory optimized tables, right?)
* Can we just create a few columnstore indexes on regular, not in-memory tables, and still expect a performance boost comparable with columnstore indexes on Memory Optimized tables? I am thinking about tables with more than 2 million rows and against which a lot of Aggregations are run.
Thanks.
Likes to play Chess
December 19, 2019 at 8:51 am
Well for Memory Optimized Tables you need to create a filegroup and one or more files for that filegroup per database. It's a good practice to setup resource governor for In-Memory enabled databases - otherwise you might end up running out of memory. I am supporting a DWH which I changed to take advantage of In-Memory because well the storage doesn't keep up with the demand for nightly load performance.
Just have to stress this again - you really have to manage your memory usage rather well, when I started migrating some tables to in-Memory I did end up with a memory usage by Memory Optimized Objects of around 100 - 120GB which mostly was unused memory. To my disadvantage in this regard: The DB does not run in full recovery mode which makes reclaiming free Memory a bit tougher.
Anyways after some tuning I'm down to 60 - 70 GB RAM usage on a 256 GB Box - I can happily skip Resource Governor this way but you'll unlikely have the chance to purge SCHEMA_ONLY durable tables once a day like I have so yes, there will be some work to manage in-Memory OLTP but that's mostly going towards Resource Governor imo.
December 19, 2019 at 1:17 pm
If you're not seeing page latch waits as your number one wait type, by an enormous margin, then you should not be using the In-Memory tables & indexes. Period. They solve a very narrow range of problems. They're not magic. They come with a lot of limitations and overhead.
The exception to this is the possible use of in-memory table variables. Now those can radically improve performance IF you're using lots of table variables. If not, again, no real win here.
The In-Memory indexes & tables are very much an edge-case tool. Don't get hung on the marketing hype. Now, if you're in the edge case, and you can deal with the limitations, they are incredible performance enhancement tools.
Now, on the other hand, Columnstore indexes are a great solution for a common set of problems. If you are running a data warehouse or similar type of reporting system where there are likely to be lots of aggregate queries, clustered columnstore indexes are going to be your best buddy. If, on your OLTP system, you're getting more than a few aggregation and analysis queries, you may see huge performance gains by adding nonclustered columnstore to your clustered index tables.
Columnstore indexes are not magic. Or, rather, like all magic comes with a price, so do columnstore indexes. They have a different set of maintenance requirements. They add overhead to inserts/updates/deletes, so you need to be cautious about when and where you add them, like regular indexes. They especially suffer in a very high volume of data modification queries. The way they work requires a delta store to be maintained until a threshold is reached upon which a rebuild occurs to move stuff from the delta into the compressed, pivoted data store. If you're doing that a lot, it's a massive performance hit on the system. Columnstore indexes are absolutely horrible at point lookups and limited range scans: WHERE ID = 42;. They can degrade performance there.
We've talked about this several times online now. It feels like you must be getting pressure to use these things without understanding why. The key to implementing any of these technologies is understanding their usage (I sure hope that's been explained, but happy to try again) and their limitations. Then, cautiously, with careful testing and evaluation (measure with Extended Events, check the execution plans), implement them in appropriate places.
"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
December 19, 2019 at 2:51 pm
Maybe you could explain what your actual use case is? As has been explained column store indexes are not a magical solution that by default replace regular indexes. How big are these tables, rows and width, how many aggregate reports are being run vs other types of queries and updates, how much of a problem are the reports that are being run?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply