April 29, 2021 at 2:40 am
if I make 2 largest tables in our most transactional database a memory-optimized table with a columnstore index.
or just a memory-optimized table -- how much additional DBA tasks/maintenance/awareness (of what?) should be expected? Compared to regular tables.
And 2nd scenario: if we do same to all 50 tables (smallest is 2 million rows) in our other DB (as opposed to just a couple of selected tables only) -- how much additional DBA MH and MK is required? (Man-Hours and Man-Knowledge)
Thank you, and sorry for my unintelligent and/or primitive question.
Likes to play Chess
April 29, 2021 at 11:55 am
for a standard normal DBA no - for you and based on your interaction here over last few years I would say it will add a lot of work
April 29, 2021 at 2:09 pm
My opinion - each "feature" of SQL that you enable requires knowledge and skills on how to work with it. For example, if you turn on memory optimized tables, do you know what new DBA related things you need to watch for? Does your server have enough free memory to handle memory optimized tables? Do you know the risks of having them enabled?
Before enabling ANY feature in SQL Server, my advice is to ask yourself "what problem am I trying to solve?" Once you know the problem, the next step is to determine solutions. Are memory optimized tables the solution to your problem? Are memory optimized tables the ONLY solution to your problem?
What I mean by that last question is if performance is the problem and you narrowed it down to performance on those 2 specific tables (the 2 largest ones), what other options do you have to improve performance? Is performance slow on reads or writes? If it is reads, adding an index may get you a "good enough" performance boost and then you don't need to worry about memory optimized tables at this time. If it is writes, maybe you have too many indexes and could remove some. Or maybe you have inefficient indexes on the table so an INSERT results in a lot of page splits.
As for new DBA duties related to them, they need maintenance like anything else in SQL. On top of that, do you know what to look for or where to look when something goes wrong with them? Do you know how to fix them? Are you aware of the limitations of them?
My opinion - before turning ANY feature in SQL Server that you don't know much about (columnstore indexes would fall into this bucket for me too), I would research it to see what problem it solves and how it solves it and try to find a few articles on how to fix them when they break, what maintenance needs to be done, and limitations of them. Asking on a forum MAY be helpful, but I generally try to find the solution via bing/google before I post. If I can find a trusted source online, they are generally more reliable than a "Mr. Brian Gale" showing up on a random SQL Server forum. And there are a LOT of trusted names in the SQL Server world.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 29, 2021 at 2:22 pm
I would think if your table is large enough for a column store index it's probably not a good candidate for memory optimized?
April 29, 2021 at 3:11 pm
If your number one wait statistic is anything except IO_LATCH*, don't mess with memory optimized tables. OK, maybe, MAYBE, for some table variables, they do pretty cool things there. However, otherwise, if you're not getting tons if IO latching, meaning you're bottlenecked on getting pages from disk, into memory, then memory optimized is not the way to go. It changes the way you build tables, the way you maintain them over time, database settings, and some queries. It's involved, but only worth it if you're going to see benefits.
When it comes to columnstore, yeah, those can go into memory optimized tables, but again, what are your waits like? Same question. Then, add on the question, what kind of queries are you running? You can't just throw columnstore at a table and see improvements in queries unless you're running the kinds of queries that columnstore will help with. Analytical style queries that involve aggregations, MIN, MAX, and stuff like that, or, very large scans, benefit a lot from columnstore indexes. Most other types of queries, OLTP-style queries especially, are not simply un-helped by columnstore, they can perform much worse.
And neither of these is about data size. It's all about how your data is moving and how your system is dealing with those moves. The style of queries, impacts of the queries on the system, these are the driving force behind picking columnstore or memory optimized storage. In fact, just going off some arbitrary size, is likely to cause you problems. "Well, we've hit 1 million rows, time to implement columnstore" is not the way to go about this stuff at all.
"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 29, 2021 at 9:59 pm
Have you reviewed page compression for the tables? If not, that's the first thing to look at. Unless the rows just keep having lots of updates through their entire history. Generally, rows are active when new and then don't change after a certain period of time. For some tables, that's a day, for others a week, or a month, etc., but if the rows settle down then you should seriously review page compressing the data.
Luckily MS provides a stored proc to help with that:
EXEC sys.sp_estimate_data_compression_savings
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply