0

I am using Microsft SQL Server 2016. I have an SQL table with 140000 rows. It is used only for selecting data.

I am trying to increase it's selection performance by making it In-Memory table (it is worth to mention that this is my first time using In-Memory tables).

The results I am getting in SQL Server profiler are rather dissatisfying. Compared data of 'CPU', 'Reads' and 'Duration' columns: SELECT from In-Memory table has 'CPU' a bit higher, 'Reads' up to 10 times lower but unfortunately 'Duration' stays pretty much the same.

I've tried generating In-Memory OLTP migration checklist - the results were successful.

Could You please tell me if this is a good use of SQL server In-Memory tables?

Maybe I am doing something wrong since duration stays the same?

Thank You in advance!

closed as too broad by McNets, hot2use, Erik Darling, Mr.Brownstone, RLF Nov 20 '17 at 17:58

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What's the query you're running, and how long is it taking? In memory OLTP really is optimized for OLTP. It's single threaded. If you're using it to make reporting queries better, you're way off. – Erik Darling Nov 20 '17 at 12:10
  • I am just doing testing on SELECT QUERY which takes about 7-8 seconds. I might be a little bit off, however I found this blog mssqltips.com/sqlservertip/3340/… which shows a decent performance increase while only using SELECT statement. That's why I thought it was worth investigating. – Tautvydas Nov 20 '17 at 12:53
  • Okay, best of luck! – Erik Darling Nov 20 '17 at 12:54
  • 2
    I don't think that for such a tiny table this would make a difference. If you have a problem with a specific query, post that query – a_horse_with_no_name Nov 20 '17 at 13:25
0

SELECTs on a memory-optimized table are not single-threaded in SQL 2016 (but it was true for 2014).

So:

  1. testing performance with a single thread for on-disk and in-mem is not a valid proof of concept.

  2. In-Memory OLTP is not designed to make queries faster - it's meant to take advantage of modern hardware, and scale write workloads.

  3. With a large number of columns, I would expect selecting a subset of columns to be faster, as you have proven.

The real question here is whether or not your workload can benefit from In-Memory OLTP, and can you deal with the tradeoffs. And running profiler while testing is guaranteed to skew your results. I would not use profiler, I would only use SET STATISTICS TIME ON.

I would not expect a significant increase (or any increase) in performance for SELECT statements that touch memory-optimized tables vs. on-disk tables. The only possible exception is if you were using a columnstore index on memory-optimized tables, and not using one for on-disk tables, and you had analytical queries (but that's not really a fair comparison, and also in-mem CCI is light years behind on-disk CCI).

But for your queries to be 10x slower, it would suggest that something is not right with indexing, and/or your queries.

I also don't understand how reads can be 10x slower, but "duration" remains the same. Could use some clarity on that.

You note you're using MS SQL Server 2016, SP1. There have been 5 CUs released since SP1, and I strongly recommend that you patch to the latest CU. Many In-Memory bugs were fixed along the way.

If you are doing analytical indexes, I see no reason to use a HASH index. They are widely misunderstood, and would serve no purpose for queries that touch more than a single row, and their nuances can cause the uneducated to see exactly the performance decrease you are experiencing. One example would be having a multi-key column, but referencing only the leading column in your query. That will use a SEEK for on-disk tables, but will generate a table SCAN for memory-optimized tables.

The blog you linked to seems like it's doing a single-threaded POC, which is also meaningless for In-Memory OLTP, as it's meant to scale write-intensive and/or highly concurrent workloads.

Agree with others that in order to really help, we must see DDL and DML.

  • Do you mean that it will have no use for me to use SELECTs on a memory-optimized table in SQL 2016? Maybe I wasn't clear enough. My queries are not 10 times slower, it's just that read operations that can be seen on profiler are way lower (which is correct and should be like that). Now I did some testing and by far this is the results I got: if I write a select statement which selects all colums (290 to be exact) it takes almost the same time. However, if I select 10 colums, In-Memory table works faster (20 % +). – Tautvydas Nov 20 '17 at 15:02
  • My database is pretty much the same as in that blog which I mentioned in other comments, it's just that it has 290 columns instead of 3. My SQL Version: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 10240: ) – Tautvydas Nov 20 '17 at 15:14
  • Thank You for Your input, I will patch it as you advised. I will not use profiler anymore. I will do some more testing if my workload can benefit from In-Memory OLTP. I'm going to mark this answer as a correct one since it was the most helpful for me and gave me some more understanding about the issues I am facing. Thank You once again! – Tautvydas Nov 20 '17 at 15:22
  • The reason we keep asking to see the DDL, is that depending on the datatypes you chose, there can be performance impact. For example, LOB columns are stored as separate tables. A few LOB columns is ok, but 250 is likely to affect performance, depending on the query. Also, the blog you linked to does not use HASH indexes, but you mentioned earlier that you configured the bucket count properly. So obviously, you've changed the DDL, and depending on how you did that, there can be performance issues. We are shooting in the dark without seeing the DDL, and it limits our ability to help you. – NedOtter Nov 20 '17 at 15:23
  • Yes, I do understand. I do not have any LOB columns, and my database is pretty much like in the blog. When I talked about buckets I wanted to explain that I understand how they work (I experimented with many indexes, some of which where hash). On my final version I had all the indexes like in that blog. – Tautvydas Nov 20 '17 at 15:27
2

It is used only for selecting data.

...

The results I am getting in SQL Server profiler are rather dissatisfying.

That is because you did not read what issues memory-optimized tables do address.

They were not invented to "just keep your data in memory", if your table is relatively small and frequently used, it will be in memory after the first reading from disk without any "optimizing".

Memory-optimized tables are lock free, it means in highly concurrent environment instead of beeing blocking on modifying when someone reads the data, new versions of rows are inserted, so there is no blocking.

Your table is readonly, as you said, so what did you want to achieve? There is no blocking at all on this your table, so what did you want to resolve?

If you just did something without good understanding of what you are doing, I can suppose that your bucket count was also chosen wrong, and wrong bucket number slows down your SELECT.

  • I do understand what I am doing (at least I hope so). I've chosen a right bucket count (according to microsoft) and I do have a clear understanding how they work. I do understand that they were not invented to "just keep your data in memory". However, I read a blogpost (mssqltips.com/sqlservertip/3340/…) and tested it locally. The part that got me interested was SELECT statements which showed a decent increase of performance ONLY BY KEEPING MY DATA IN MEMORY. – Tautvydas Nov 20 '17 at 12:31
  • Please update your question with the repro of what was done. Only this way it will be possible to all of us reproduce your situation. I.e. we need CREATE TABLE code + some data to insert with data distribution similar to yours + SELECT queries to test – sepupic Nov 20 '17 at 13:29
0

Technology changing is great, I agree. However, for improved performance, you have exhausted indexes as an option? Have considered a primary key that facilitates your selection criteria? That's not a big number, and #tables with primary keys and indexes are cheap. Please include how you are doing (SQL statements) what you are doing so we can see?

I mention #table because I assumed it a temporary set to select from, but I might have got that wrong, in which case INDEX and correct PRIMARY KEY are even more important.

  • Yes, I have tried indexing and I do have a primary key. However, I still want a better performance. This table is not temporary, its a sort of warehouse-type table that is being filled by many other tables and stores a lot of data only for faster selection purpose. I tried a blog (mssqltips.com/sqlservertip/3340/…) example which worked as described in that blog. However, my table (it has more columns in it) still has a problem described in this question. – Tautvydas Nov 20 '17 at 12:24
  • time this: IF EXISTS(SELECT TOP 1 1 FROM [yourtable]) SELECT 1 This is how fast the set is created from you table. Response time. If all your apps need all the columns - u stuck. Maybe split the columns per app into seperate views, so less is returned - makes it more responsive – Alocyte Nov 20 '17 at 12:28
  • this new in-memory table is buggy if you don't have enterprise version, btw. – Alocyte Nov 20 '17 at 12:32
  • 'IF EXISTS(SELECT TOP 1 1 FROM [yourtable]) SELECT 1' returns higher value on In-memory table than my old one. I just tested my tables selecting only a few columns - it seems like In-Memory table now works faster. So the problem is about how fast the set is created from my table? I'm developing on Developer edition SQL server. – Tautvydas Nov 20 '17 at 12:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.