Search results matching tag 'Hekaton'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Hekaton&orTags=0Search results matching tag 'Hekaton'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Geek City: My SQL Server 2016 RTM In-memory OLTP Whitepaperhttp://sqlblog.com/blogs/kalen_delaney/archive/2016/07/12/sql-server-2016-rtm-in-memory-oltp-whitepaper.aspxTue, 12 Jul 2016 20:52:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:61593Kalen Delaney<p>&nbsp;</p> <p>Finally, we have a download available.</p> <p>You can go to this page <a href="https://t.co/lvqwB8G8Q4" mce_href="https://t.co/lvqwB8G8Q4">microsoft.com/en/server-cloud/products/sql-server/</a>&nbsp; and scroll down to the section&nbsp; 'Technical Resources'. (NOT the section on the left called White papers).</p> <p><a href="http://sqlblog.com/blogs/kalen_delaney/image_2C97D4C8.png" mce_href="http://sqlblog.com/blogs/kalen_delaney/image_2C97D4C8.png"><img width="244" height="68" title="image" style="margin:0px;border:0px currentColor;border-image:none;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" alt="image" src="http://sqlblog.com/blogs/kalen_delaney/image_thumb_778DD648.png" border="0"></a></p> <p> <br>Click on “SQL Server 2016 In-memory OLTP technical white paper” and you can open or save.</p> <p>OR</p> <p>You can use this direct link: <a href="http://download.microsoft.com/download/8/3/6/8360731A-A27C-4684-BC88-FC7B5849A133/SQL_Server_2016_In_Memory_OLTP_White_Paper.pdf" mce_href="http://download.microsoft.com/download/8/3/6/8360731A-A27C-4684-BC88-FC7B5849A133/SQL_Server_2016_In_Memory_OLTP_White_Paper.pdf">http://download.microsoft.com/download/8/3/6/8360731A-A27C-4684-BC88-FC7B5849A133/SQL_Server_2016_In_Memory_OLTP_White_Paper.pdf</a>&nbsp;</p> <p>&nbsp;</p> <p>Enjoy!</p> <p><font color="#ff00ff" size="4">~Kalen</font></p> <p>&nbsp;</p> <p>P.S. And don’t forget I’ll be talking about In-memory OLTP for a whole day in San Antonio on August 12! <a title="http://www.sqlsaturday.com/550/EventHome.aspx" href="http://www.sqlsaturday.com/550/EventHome.aspx" mce_href="http://www.sqlsaturday.com/550/EventHome.aspx">http://www.sqlsaturday.com/550/EventHome.aspx</a></p>Geek City: The Naming of Thingshttp://sqlblog.com/blogs/kalen_delaney/archive/2016/05/27/the-naming-of-things.aspxSat, 28 May 2016 00:46:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:61348Kalen Delaney<p>&nbsp;</p> <p>As a teacher, and also a student of languages, one issue that’s always fascinating to me is how people choose to shorten a compound, or multi-part, name.&nbsp; This is very problematic when the word is from another language.&nbsp; For example, we use the word latte in English as a short form of caffè latte, but we shorten it to just the part that means ‘milk’, not coffee! My daughter was an exchange student in Spain and told me another example. Her host mother kept asking her if she had a ‘body’. The question was in Spanish, except for the word ‘body’ and my daughter just couldn’t figure out what she was talking about. The woman was confused thinking for sure my daughter should understand since it was an English word! Finally, they figured out the woman was referring to a bodysuit, but shortening it to something that really had no meaning on its own. In English, we have the words grape and grapefruit. I had a friend (a native Chinese speaker) who could just never remember which was which and frequently mentioned grapes when she meant grapefruit! And sometimes&nbsp;she did it the other way around and asked her native-English speaking husband to get her a bag of grapefruits, when she just wanted grapes. These are two very different things! I have more examples, but I’m getting a bit off track here. </p> <p>In SQL Server, we also have concepts that have compound names that are frequently shortened. For ‘isolation level’, you could just refer to ‘isolation’ and people would probably know what you mean. But if you just said ‘level’, your meaning might not be so obvious. </p> <p>And what about pairs of terms with different qualifiers, like clustered indexes and nonclustered indexes? If someone just says index, you usually need to know which kind. </p> <p>And there are other terms which have the same base, and only one of them has a qualifier. And that’s really why I decided to write this blog post.&nbsp; In SQL Server 2005, Microsoft introduced snapshot isolation for read operations. There were two different levels of snapshots. The terms we use are SNAPSHOT and READ COMMITTED SNAPSHOT. As a teacher, I always found this very confusing, as did many of my students. People would say SNAPSHOT, but it was not at all clear if they really meant READ COMMITTED SNAPSHOT. So I started using an additional qualifier, so that both terms would be qualified. I started saying TRANSACTIONAL SNAPSHOT, but that was too cumbersome, so then in class I just started calling it FULL SNAPSHOT. I just wanted both terms to have some qualifier, so if someone just said SNAPSHOT, I could ask which one they meant. </p> <p>Then in SQL Server 2014, Microsoft introduced In-Memory OLTP, with two new kinds of indexes. One is called NONCLUSTERED HASH, and the other is just plain old NONCLUSTERED. And again, I found this unbalanced. If someone just says NONCLUSTERED, am I sure they’re just not shortening NONCLUSTERED HASH inappropriately? So I started using the word RANGE as the qualifier for the non-HASH index. Then we could talk about NONCLUSTERED RANGE and NONCLUSTERED HASH, or just shorten the names to RANGE and HASH. And when I’m teaching or writing&nbsp;about these indexes, I am always careful to point out that the word RANGE is not part of the syntax, it’s just a descriptor I use. </p> <p>However, one of the engineers at Microsoft was not happy with the fact that my writings used the term RANGE index. He said it wasn’t part of the spec, so I shouldn’t use it. It would just confuse people. He claimed Microsoft never used that term and I shouldn’t either. I argued. I explained my pedagogical reasons. He finally just shrugged and decided that I probably wasn’t adding any additional obfuscation and grudgingly admitted I might actually be making things clearer. But he wasn’t happy about it. He just didn’t want me using the term RANGE. </p> <p>Now SQL Server 2016 is coming out. I’m doing research on some of the new storage structures and new metadata. And I’m looking at a DMV called <i>sys.dm_db_xtp_memory_consumers</i> with a column called <i>memory_consumer_desc</i>. And one of the possible values for this column is ‘Range index heap’. I just checked, and the view is also available in SQL Server 2014, but I just never got around to exploring it until now. And if I create a memory-optimized table called <i>OrderDetails2</i> on a SQL Server 2014 instance, with three RANGE indexes, and then run this query:</p> <blockquote> <p><font face="Consolas" size="2">SELECT object_name(object_id) as object_name, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; index_id, memory_consumer_desc <br>FROM sys.dm_db_xtp_memory_consumers; <br>GO</font></p> </blockquote> <p>I will get these rows of output (among others):</p> <p><a href="http://sqlblog.com/blogs/kalen_delaney/image_29BCE43F.png" mce_href="http://sqlblog.com/blogs/kalen_delaney/image_29BCE43F.png"><img width="491" height="144" title="image" style="border:0px currentColor;border-image:none;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" alt="image" src="http://sqlblog.com/blogs/kalen_delaney/image_thumb_03137954.png" border="0"></a></p> <p>So RANGE INDEX is not just a term I chose to use to make things easier for my students and my readers. Someone else, designing DMVs, also thought it was a useful term to use.</p> <p>I am just about done upgrading my In-memory OLTP Internals whitepaper for SQL Server 2016 RTM. Once that’s published, you’ll be able to read all the details of the various memory consumers used for memory-optimized tables. Hopefully, you won’t have to wait too long!</p> <p><font color="#ff00ff" size="4"><i>~Kalen</i></font></p>Geek City: SQL Server 2016 CTP3 In-Memory OLTP Internals Whitepaperhttp://sqlblog.com/blogs/kalen_delaney/archive/2016/01/19/sql-server-2016-ctp3-in-memory-oltp-internals-whitepaper.aspxWed, 20 Jan 2016 05:23:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:60465Kalen Delaney<p>&nbsp;</p> <p>Just a quick note that my CTP3 paper has finally been published, and now I can start working on the final version!</p> <p>Here’s the link:</p> <p><a href="http://download.microsoft.com/download/D/5/2/D52D374F-D442-4275-B570-0EB527102F4D/SQL_Server_In_Memory_OLTP_Internals_Overview_for_SQL_Server_2016_CTP3_EN_US.pdf" mce_href="http://download.microsoft.com/download/D/5/2/D52D374F-D442-4275-B570-0EB527102F4D/SQL_Server_In_Memory_OLTP_Internals_Overview_for_SQL_Server_2016_CTP3_EN_US.pdf">http://download.microsoft.com/download/D/5/2/D52D374F-D442-4275-B570-0EB527102F4D/SQL_Server_In_Memory_OLTP_Internals_Overview_for_SQL_Server_2016_CTP3_EN_US.pdf</a></p> <p>Enjoy!</p> <p><font color="#ff00ff" size="4">~Kalen</font></p>Geek City: Q&amp;Ahttp://sqlblog.com/blogs/kalen_delaney/archive/2015/10/15/geek-city-q_2D00_and_2D00_a.aspxFri, 16 Oct 2015 00:57:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:59780Kalen Delaney<p>&nbsp;</p> <p>I’ve done it again… I’ve neglected my blog so long that my name has been removed from the list of SQLBlog contributors on the right-hand side of the main page.&nbsp; So I need to fix that. </p> <p>I have been accumulating a list of questions that have come up during my 5-day SQL Server Internals classes, that I cannot immediately answer. Sometimes I can figure out the answer myself with just a bit of research or a simple test, and sometimes I can ask one of my Microsoft contacts for help. (Other times, I actually know the answer, but I just space out on it when asked, after being on my feet talking for 5 straight days. <img class="wlEmoticon wlEmoticon-smile" alt="Smile" src="http://sqlblog.com/blogs/kalen_delaney/wlEmoticon-smile_7D914A1D.png"> )</p> <p>I kept hoping that I would eventually get answers to ALL the questions on the list, but I realized I should wait to post what I have. And that will get me back up on this blog site.</p> <p>&nbsp;</p> <p>1. Can memory-optimized tables use BPE (Buffer Pool Extensions)</p> <blockquote> <p>No, BPE is only for data read from disk and stored in memory buffers. Memory optimized tables do not use pages or buffers.</p> </blockquote> <p>&nbsp;</p> <p>2. What if a plan guide references a table that is specified in a query without a schema name, and there are two different tables, in different schemas, with the same name? </p> <blockquote> <p>For a plan guide of type ‘SQL’, all that matters is the TSQL-statement that is included in the plan guide. The same plan guide could be invoked when accessing different tables with the same name.</p> </blockquote> <p>&nbsp;</p> <p>3. Why does the Native Compilation Advisor complain about the EXEC command?</p> <blockquote> <p>In SQL Server 2014, EXEC is never allowed in Natively Compiled Procedures. In SQL Server 2016, we can EXEC a Natively Compiled Procedure or function from within a Natively Compiled Procedure, but we can’t EXEC a non-native module, and we can never EXEC a string.</p> </blockquote> <p>4. Can we replicate a memory-optimized table?</p> <blockquote> <p>Memory-optimized tables can be subscribers for transactional replication, but that is the only supported configuration. See this MSDN page for more details: <br><a title="https://msdn.microsoft.com/en-us/library/dn635118.aspx" href="https://msdn.microsoft.com/en-us/library/dn635118.aspx" mce_href="https://msdn.microsoft.com/en-us/library/dn635118.aspx">https://msdn.microsoft.com/en-us/library/dn635118.aspx</a></p> </blockquote> <p>&nbsp;</p> <p>5. Does auditing work on memory-optimized tables?</p> <blockquote> <p>YES</p> </blockquote> <p>6.&nbsp; Are the values in <i>sys.dm_db_index_usage_stats</i> and <i>sys.dm_db_index_operational_stats</i> reset when an index is rebuilt?</p> <blockquote> <p>YES, just as if you had restarted your SQL Server Service. </p> <p>&nbsp;</p> </blockquote> <p>7. When do I need to use the option ‘scan for startup procs’?</p> <blockquote> <p>This configuration option is related to the procedure <i>sp_procoption</i>, that allows you to mark a stored procedure as a ‘startup’ procedure, to be executed every time your SQL Server service starts. However, the configuration option to tell SQL Server to scan for these procedures doesn’t see too useful.&nbsp; When the first procedure is marked for startup, this option is turned on automatically, and when the last one is unmarked, it is turned off. So there is really no need for the configuration option, unless you suspect a problem and want to inhibit the startup procedures. </p> </blockquote> <blockquote>&nbsp;</blockquote> <p>8. When are SQL Trace and the SQL Profiler going away? </p> <blockquote> <p>They’re still available in SQL Server 2016 CTP 2.4, so I assume they will still be in SQL Server 2016 RTM. After that, I think no one knows yet. </p> </blockquote> <p>&nbsp;</p> <p>That’s all I’ve got for now. </p> <p><font color="#ff00ff" size="4">~Kalen</font></p> <p><img src="http://www.sqlpass.org/LinkClick.aspx?fileticket=lrpIlyzAitA%3d&amp;tabid=32301&amp;portalid=428&amp;mid=49092"></p>Geek City: Point in Time Recovery for Operations on Memory—Optimized Tableshttp://sqlblog.com/blogs/kalen_delaney/archive/2015/04/22/point-in-time-recovery-for-operations-on-memory-optimized-tables.aspxWed, 22 Apr 2015 23:05:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:58511Kalen Delaney<p>In my class in Budapest last week, someone asked if point-in-time recovery was supported for transactions on memory-optimized tables.&nbsp; I couldn’t think of a reason why not, but I had never tried it. And just because there is not a technical inhibitor to allow certain functionality, it is often the case with a new technology that certain features aren’t included just because it just hasn’t been tested sufficiently. </p> <p>But as I thought about it a bit more, I realized there was no way that point-in-time recovery could work for disk-based tables unless it also worked for memory-optimized tables. After all, the transactions are all stored in the same log. </p> <p>But, I decided to verify anyway. </p> <p>So I created an in-memory database (IMDB) and a memory-optimized table. (If you want to run the code, you’ll need a folder on your C: drive called HKData and one called HKBackups.&nbsp; Or you can edit the code to change the paths.) I then did an initial database backup of the database with an empty table.</p> <blockquote> <p><font face="Consolas">USE master <br>GO <br>SET NOCOUNT ON <br>GO</font></p> <p><font face="Consolas">IF db_id('IMDB') IS NOT NULL DROP DATABASE IMDB; <br>GO</font></p> <p><font face="Consolas">CREATE DATABASE IMDB;&nbsp; <br>GO <br>----- Enable database for memory optimized tables <br>-- add memory_optimized_data filegroup <br>ALTER DATABASE IMDB <br>&nbsp;&nbsp;&nbsp; ADD FILEGROUP IMDB_mod_FG CONTAINS MEMORY_OPTIMIZED_DATA <br>GO</font></p> <p><font face="Consolas">-- add container to the filegroup <br>ALTER DATABASE IMDB <br>&nbsp;&nbsp;&nbsp; ADD FILE (NAME='IMDB_mod', FILENAME='c:\HKData\IMDB_mod') <br>&nbsp;&nbsp;&nbsp; TO FILEGROUP IMDB_mod_FG <br>GO</font></p> <p><font face="Consolas">USE IMDB; <br>GO <br>-- create a memory-optimized table <br>CREATE TABLE dbo.t_memopt ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1 int NOT NULL, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c2 char(40) NOT NULL, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c3 varchar(100) NOT NULL, <br>&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONSTRAINT [pk_t_memopt_c1] PRIMARY KEY NONCLUSTERED HASH (c1) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WITH (BUCKET_COUNT = 100000) <br>) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); <br>GO</font></p> <p><font face="Consolas">BACKUP DATABASE IMDB TO&nbsp; DISK = N'C:\HKBackups\IMDB-FULL.bak' <br>&nbsp;&nbsp;&nbsp; WITH NOFORMAT, INIT,&nbsp; NAME = N'IMDB-empty-table', SKIP, NOREWIND, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOUNLOAD,&nbsp; STATS = 10; <br>GO</font></p> </blockquote> <p>I then inserted a row into the table, waited a minute, grabbed the time, waited another minute, and inserted another row. I found that if I didn’t put the WAITFORs in, it would get a bit confusing about exactly where I wanted to stop restoring.</p> <blockquote> <p><font face="Consolas">INSERT t_memopt VALUES (1, 'a', REPLICATE ('b', 100)); <br>GO <br></font><font face="Consolas">WAITFOR DELAY '0:1:0'; <br>SELECT getdate(); <br>WAITFOR DELAY '0:1:0'; <br></font><font face="Consolas">INSERT t_memopt VALUES (2, 'c', REPLICATE ('d', 100)); <br>GO <br>SELECT * FROM t_memopt; <br>GO</font></p> </blockquote> <p>I then noted the time captured with the getdate(), saw the two rows returned,&nbsp; and then made a log backup. </p> <blockquote> <p><font face="Consolas">BACKUP LOG IMDB TO&nbsp; DISK = N'C:\HKBackups\IMDB-log.bak' <br>&nbsp;&nbsp;&nbsp; WITH NOFORMAT, INIT,&nbsp; NAME = N'IMDB-2-inserts-in-log' <br>GO</font></p> </blockquote> <p>Now I could test the restore. </p> <blockquote> <p><font face="Consolas">USE master; <br>GO <br>RESTORE DATABASE IMDB <br>&nbsp;&nbsp;&nbsp; FROM DISK = 'C:\HKBackups\IMDB-FULL.bak' <br>&nbsp;&nbsp;&nbsp; WITH REPLACE, NORECOVERY; <br>GO <br>RESTORE LOG IMDB <br>&nbsp;&nbsp;&nbsp; FROM DISK = 'C:\HKBackups\IMDB-log.bak' <br>&nbsp;&nbsp;&nbsp; WITH RECOVERY, STOPAT = '&lt;insert getdate() value noted above&gt;'; <br>GO</font></p> <p><font face="Consolas">USE IMDB; <br>GO <br>SELECT * FROM t_memopt; <br>GO</font></p> </blockquote> <p>When I saw just the one row row returned, I knew the point-in-time recovery had worked!</p> <p>So now I can start working on a real juicy bits and bytes type blog post. Hopefully I’ll have it up next week.</p> <p>&nbsp;</p> <p><b><font color="#ff00ff" size="4">~Kalen</font></b></p>Geek City: More Hekaton Detailshttp://sqlblog.com/blogs/kalen_delaney/archive/2014/12/01/more-hekaton-details.aspxTue, 02 Dec 2014 01:36:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:56882Kalen Delaney<p>I apologize to all you folks who attended my Hekaton precon at the SQLPASS Summit last month, who have been eagerly awaiting my answers to some of the unanswered questions. I had got most of the answers sorted out, but I was waiting for one last reply, and put the whole list on the back burner. I just realized today it was now December, and I still hadn’t published this. </p> <p>So here it is, six questions that I didn’t completely answer during my talk, although for some of them I did make a guess. </p> <p>1. Do SCHEMA_ONLY tables affect the upper limit of memory available for memory-optimized table?</p> <blockquote> <p><i>Remember, there actually is no hard upper limit for the amount of memory you can use for memory-optimized tables. The recommended maximum of 256 GB has to do with the number of CFPs (Checkpoint File Pairs) that can be supported. So in that sense you might be able to have more data in SCHEMA_ONLY tables because you don’t have to worry about the size of the CPFs, as the data in those tables is never written to the files. But you still need to be aware of the overall system memory limit and make sure your machine has enough memory for all the memory_optimized tables, both SCHEMA_AND_DATA and SCHEMA_ONLY) as well as memory for the buffer pool for your disk-based tables. Plus memory for other SQL Server and system needs. </i></p> <p><i>If you have bound a database to a resource pool with a fixed upper limit of memory, all your tables have fit within that memory limit.</i></p> </blockquote> <p>&nbsp;</p> <p>2.&nbsp; Is Point-in-time RESTORE possible for databases that have performed transactions on memory-optimized tables?&nbsp; (i.e. can we do a RESTORE LOG WITH STOPAT command?)</p> <blockquote> <p><i>Simple answer here: YES</i></p> </blockquote> <p>&nbsp;</p> <p>3. Can a database with memory-optimized tables be restored or attached onto a SQL Server 2014 instance using STANDARD Edition?</p> <blockquote> <p><i>No. Both restore and attach will require that all filegroups are available, including the filegroups for your memory-optimized tables. And once you have those special filegroups, the database cannot be restored or attached on STANDARD Edition and you get an error. So you really don’t even have to have any memory-optimized tables. As soon as you create a memory-optimized filegroup, the database will no longer be accepted.</i></p> <p><i>I wish I could tell you what the exact error would be, but I don’t have a SQL Server 2014 STANDARD Edition to test on. I have frequently requested that Microsoft provide a feature that allows us to ‘switch off’ Enterprise features if we are using EVALUATION or DEVELOPER Edition, so we can actually test using a base other than ENTERPRISE. But so far, Microsoft has not complied.&nbsp; There are two Connect items that seem to address this this need. The first one at </i><a title="https://connect.microsoft.com/SQLServer/feedback/details/331297/developer-edition-in-standard-workgroup-mode" href="https://connect.microsoft.com/SQLServer/feedback/details/331297/developer-edition-in-standard-workgroup-mode" mce_href="https://connect.microsoft.com/SQLServer/feedback/details/331297/developer-edition-in-standard-workgroup-mode"><i>https://connect.microsoft.com/SQLServer/feedback/details/331297/developer-edition-in-standard-workgroup-mode</i></a><i> is marked “Closed as WON’T FIX’ but the other is still active at </i><a title="https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version" href="https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version" mce_href="https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version"><i>https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version</i></a><i>, so you might want to go give it a vote. </i></p> </blockquote> <p>&nbsp;</p> <p>4. Can views on memory-optimized tables be accessed from within a natively compiled procedure? (I think the question was sort of hinting at a workaround where you could create a view on a disk-based table and access that through the native procedure.)</p> <blockquote> <p><i>The answer is NO. You can create a view on a memory-optimized table, but you cannot access it from a native procedure. No views at all can be referenced in a native procedure.</i></p> </blockquote> <p>&nbsp;</p> <p>5. With REPEATABLE READ Isolation, with which SQL Server needs to guarantee READ STABILITY, what happens if a column that wasn’t read is changed? (i.e. your query read col1 and col2 from Table1, what happens if another query changes col3?)</p> <blockquote> <p><i>It’s the row that is important. If Tx1 reads col1 and col2 from Table1 in REPEATABLE READ and doesn’t commit, then Tx2 updates col3 in Table1, when&nbsp; Tx1 commits it will then fail due to REPEATABLE READ violation.</i></p> </blockquote> <p>6. Can transactions on memory-optimized tables run in RCSI (READ COMMITTED SNAPSHOT Isolation)?</p> <blockquote> <p><i>RCSI is just a variant of READ COMMITTED Isolation. Operations on memory-optimized tables have to run in something higher than READ COMMITTED, so RCSI is moot.</i></p> <p>&nbsp;</p> </blockquote> <p>I hope this is useful!</p> <p>&nbsp;</p> <p><font color="#ff00ff" size="4"><b>~Kalen</b></font></p>Did You Know? My PASS Demo Scripts are Up (and other news)!http://sqlblog.com/blogs/kalen_delaney/archive/2014/11/10/my-pass-demo-scripts-are-up-and-other-news.aspxTue, 11 Nov 2014 01:48:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:56387Kalen Delaney<p>And so another PASS Summit passes into history. It was an awesome week, filled with old friends and new, and lots of superlative technical content! My <a href="http://www.red-gate.com/community/books/sql-server-internals-in-memory-oltp" mce_href="http://www.red-gate.com/community/books/sql-server-internals-in-memory-oltp">Hekaton book</a> was released just in time, and it was great seeing the excitement. Red Gate gave away all the copies they had, the bookstore sold all they had, and I gave away 20 in my sessions. </p><p>There's also a review of my book up already: <a href="http://www.i-programmer.info/bookreviews/21-database/7950-sql-server-internals-in-memory-oltp.html">http://www.i-programmer.info/bookreviews/21-database/7950-sql-server-internals-in-memory-oltp.html</a>&nbsp;&nbsp;</p> <p>My PASS Demo scripts are now available on my website. Just go to <a href="http://www.SQLServerInternals.com" mce_href="http://www.SQLServerInternals.com">www.SQLServerInternals.com</a> and click on Resources in the left-hand menu, and then choose Conference Content. </p> <p>The winners of my book giveaway were announced on Twitter, and email was sent out. I thought I was going to have to say that one of the winners had not yet responded, but just as I started writing, the sixth winner’s&nbsp; email arrived.&nbsp; I’m still scrambling to get caught up on everything that got put off while I was at the MVP Summit and PASS Summit last week, but I hope to have the books shipped by the end of this week.</p> <p>Here’s the list:</p> <p><a href="http://sqlblog.com/blogs/kalen_delaney/image_5130D3C8.png" mce_href="http://sqlblog.com/blogs/kalen_delaney/image_5130D3C8.png"><img width="477" height="195" title="image" style="border:0px currentColor;border-image:none;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" alt="image" src="http://sqlblog.com/blogs/kalen_delaney/image_thumb_2C6F9684.png" border="0"></a></p> <p>And now that PASS is over, my next project is updating my SQL Server Internals course to SQL Server 2014. The first public delivery will be in Stockholm in February.&nbsp; Check my schedule for more details: <a title="http://sqlserverinternals.com/kalens-schedule/" href="http://sqlserverinternals.com/kalens-schedule/" mce_href="http://sqlserverinternals.com/kalens-schedule/">http://sqlserverinternals.com/kalens-schedule/</a></p> <p>We are having a glorious Autumn here in the Beautiful Pacific Northwest. I hope you are enjoying your Autumn (or Spring in the Southern Hemisphere) as much as I am enjoying it here!</p> <p>&nbsp;</p> <p><font color="#ff0080" size="5">~Kalen</font></p>Did You Know? My Hekaton Book is at the Publishers!http://sqlblog.com/blogs/kalen_delaney/archive/2014/10/09/my-hekaton-book-is-at-the-publishers.aspxFri, 10 Oct 2014 01:38:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:55695Kalen Delaney<p>I am very excited to be able to report that my latest book is at the printers now, and should be ready in time for the PASS Summit!</p> <p><a href="http://sqlblog.com/blogs/kalen_delaney/Front-and-back_56A54060.png" mce_href="http://sqlblog.com/blogs/kalen_delaney/Front-and-back_56A54060.png"><img width="418" height="266" title="Front and back" style="border:0px currentColor;border-image:none;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" alt="Front and back" src="http://sqlblog.com/blogs/kalen_delaney/Front-and-back_thumb_128110AF.png" border="0"></a></p> <p>And since the last files were sent off to the publisher (Red Gate) last Friday, I could finally finish up the slides for my Pre-Conference Seminar, all about In-Memory OLTP. </p> <p>Check it out here: </p> <p><a title="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152" href="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152" mce_href="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152">http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152</a></p> <p>Although there will be some copies of the book at&nbsp; the Red Gate booth, along with many of their other wonderful titles, Red Gate will also be giving me a box full of books to give away during the session. There won’t be enough for everyone (unfortunately) but your chances of snagging one will be good!</p> <p>See you in November!</p> <p><font color="#ff0080" size="4"><b>~Kalen</b></font></p>Did You Know? I’ll be talking for a full day at the PASS Summit!http://sqlblog.com/blogs/kalen_delaney/archive/2014/09/18/Hekaton-precon-and-Q_2600_A.aspxThu, 18 Sep 2014 23:32:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:55296Kalen Delaney<p>&nbsp;</p> <p>I’ll be speaking on my new favorite topic… Hekaton! (aka In-memory OLTP)</p> <p><a title="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152" href="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152" mce_href="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152">http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152</a></p> <p>I was delighted to have the chance to give a taste of the presentation for the the 24 Hours of PASS preview on September 9th.&nbsp; I was in Germany at my daughter's house that day, so although my US audience heard me in the morning, for me, it was almost supper time.&nbsp; I am always impressed by the quality of the questions I get when giving online talks, and this was no exception. I was only able to address about a third of the questions that came in, but the organizers sent me a list of most of the submitted questions to address later.&nbsp; </p> <p>Note that I won’t be giving really detailed answers to most of the questions. After all, this session was just a preview of my precon at the PASS Summit. In addition, I am just finishing up a book on In-memory OLTP for Red Gate, that hopefully will be released at the Summit. And that will have lots more details! </p> <p>Also keep in mind that this is a brand new technology, so the best practices and use cases are still being explored and those people out there actually implementing In-memory OLTP solutions are discovering new things all the time!</p> <p>&nbsp;</p> <p><b>Q:&nbsp; Is the size of the table (or tables) that you want to use Hekaton on dependent on the amount of RAM you have available?</b></p> <p><i>Absolutely. You can’t have more data in memory than will fit in your memory. In addition, if not all your tables are in-memory tables, you’ll need to make sure there is enough memory for the buffer pool to hold needed pages from the disk-based tables. You also need to make sure you have enough memory for multiple versions of rows in your in-memory tables, based on how much updating you’re going to be doing. </i></p> <p>&nbsp;</p> <p><b>Q: How can I tell if a disk-based table has overflow rows, before I attempt to migrate it to Hekaton.&nbsp; Also, how would I workaround this?</b></p> <p><i>You can look for any objects that have rows in sys.allocation_units with a type description of anything other than IN_ROW_DATA. This will give tables with either ROW_OVERFLOW or LOB data. If you just want to know the tables with ROW_OVERFLOW data,&nbsp; look for where the type_desc is ROW_OVERFLOW_DATA. This query should get you started:</i></p> <blockquote> <p><font face="Consolas">SELECT OBJECT_NAME(object_id) FROM sys.partitions <br>WHERE index_id &lt; 2 <br>&nbsp; AND partition_id IN ( <br>&nbsp;&nbsp; SELECT container_id FROM sys.allocation_units <br>&nbsp;&nbsp; WHERE type_desc != 'IN_ROW_DATA' <br>&nbsp;&nbsp; AND total_pages &gt; 0)</font></p> </blockquote> <p><i>Working around this might take a table redesign (which is a little beyond the scope of this short Q&amp;A). </i></p> <p>&nbsp;</p> <p><b>Q:&nbsp; Is it a requirement that the memory should be double the amount of data to efficiently use the Hekaton engine?</b></p> <p><i>As mentioned in the first answer, you need to have room for all the table data for tables you want in-memory, and you need to have enough room for the indexes on those tables, and for the versions of rows as the data changes. In some cases you might end up needing more than twice the data size, but it completely depends on the volume of your data changes, and the indexes you’ve created. If you have insufficient memory, it won’t just be a matter of not efficiently using the Hekaton engine; you will not be able to do any more data modifications once you are out of memory. But, there is no hard and fast rule. You need to&nbsp; know your data and your operations, and you need to test thoroughly.</i> </p> <p>&nbsp;</p> <p><b>Q: What happens as unique row counts exceed allocated bucket sizes? <br>Q: Do buckets grow as data is inserted ?&nbsp;&nbsp; Can you manually alter the number of buckets?</b></p> <p><i>I will assume you are asking what happens when the number of unique values exceeds the predefined bucket count. Here, this does become a matter of efficiency. If there are lots fewer buckets that unique values, one bucket will be used for multiple values and your search operations will be slower.&nbsp; The number of buckets is fixed at the time the table is created and cannot be changed, either automatically or manually. You’ll have to drop and recreate the table.</i></p> <p>&nbsp;</p> <p><b>Q: When does the data write to disk, and do you have to compress the row history to free up memory space? <br>Q: The data file name does not need an extension (like 'mdf')?</b></p> <p><i>New rows of data are written to disk using a mechanism called ‘continuous checkpoint’ which uses SQL Server’s&nbsp; filestream technology to write to files on disk. There is no concept of a data file, so no .mdf is used for the in-memory data. The database containing the in-memory tables still needs a regular data file and a log file but the in-memory tables are not written there.&nbsp; The filestream files where the in-memory data is written, to be used only in case needed for recovery, do not use a file extension. You might want to read about the filestream technology, which was introduced back in SQL Server 2008.</i> </p> <p>&nbsp;</p> <p><b>Q: How do you calculate how much memory the table is going to need?</b></p> <p><i>The required space depends on the data in the rows, the number of rows, and the volume of updates. There is no simple formula for calculation because no one knows what your volume of changes will be. You can make a guess if you know your workload, and run some tests. There is good metadata available to tell you how much space you are using for your in-memory tables once you have created them. You can look at the memory used, run some of your workload, and then see how much more memory is used, to see how the memory requirements are growing.&nbsp; You might start with the DMV: sys.dm_db_xtp_table_memory_stats.</i></p> <p><i></i></p> <p><b>Q: I really like to use in memory OLTP but we frequently alter table . What is your recommendation on using In Memory OLTP for these table?</b></p> <p>As you’ve heard, once an in-memory table is created, it cannot be altered, not even to add more indexes, or to change the bucket count for any of the existing hash indexes. You’ll have to test whether dropping and recreating the table, or creating a new table and copying the data, can be done is a reasonable amount of time for your requirements. (Keep in mind that even with disk-based tables, some of the ALTER TABLE operations are not fast, efficient changes. Some ALTER TABLE options require that SQL Server completely recreate every row. )</p> <p>&nbsp;</p> <p>If you want even more details on Hekaton in SQL Server 2014, there are still seats in my precon (last I heard)! <br><a title="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152" href="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152" mce_href="http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152">http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6152</a></p> <p>&nbsp;</p> <p>And check out the Red Gate booth in the vendor area to find my new In-Memory OLTP Internals book!</p> <p>&nbsp;</p> <p>Have fun!</p> <p><font color="#ff0080" size="4"><b>~Kalen</b></font></p>AdventureWorks databases for SQL Server 2014http://sqlblog.com/blogs/sergio_govoni/archive/2014/08/08/adventureworks-databases-for-sql-server-2014.aspxSat, 09 Aug 2014 01:31:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54740Sergio Govoni<p>From CodePlex website, you can download the AdventureWorks databases (OLTP, Data Warehouse, Tabular and Multidimensional model) for SQL Server 2014.</p><p>Download links and installation's details are available <a href="https://msftdbprodsamples.codeplex.com/releases/view/125550?wc.mt_id=Social_SQLServer_Announce_DI&amp;WT.mc_id=Social_FB_OutgoingEvents_20140808_79750458_Microsoft%20SQL%20Server%20-%20sqlserver&amp;linkId=9197010" target="_blank" mce_href="https://msftdbprodsamples.codeplex.com/releases/view/125550?wc.mt_id=Social_SQLServer_Announce_DI&amp;WT.mc_id=Social_FB_OutgoingEvents_20140808_79750458_Microsoft%20SQL%20Server%20-%20sqlserver&amp;linkId=9197010">here</a>.</p><p>Enjoy with the new features of SQL Server 2014!</p>