In- Memory OLTP Engine Performance . With CPU speeds hitting a plateau, Microsoft has been working at increasing performance through memory optimization. This trend began with the Verti. Paq technologies that Microsoft first released with Power Pivot and the SQL Server 2. Related: The In- Memory Revolution. Power Pivot used memory compression techniques that let Microsoft Excel perform business intelligence (BI) analyses over multi- million- row data sets. These in- memory technologies later evolved into the columnar index technology that Microsoft released with SQL Server 2. Related Video: What Is SQL Server 2. In- Memory OLTP Database Engine? Unlike the original Verti. Paq technology that was hosted in Excel, the SQL Server 2. SQL Server instance itself. Designed for data warehousing implementations, the columnar index provides performance improvements by compressing column data, moving it into memory, and running columnar queries over that data. With SQL Server 2. Microsoft has continued to evolve its in- memory technologies with the all- new In- Memory OLTP engine, which lets you move select tables entirely in memory for high- performance, low- latency data access. Oracle Technology Network is the ultimate, complete, and authoritative source of technical information and learning about Java. I'll walk you through the In- Memory OLTP engine's basic architecture so that you can see how it differs from SQL Server 6. SSDs). In addition, I'll tell you about the requirements and limitations for using the In- Memory OLTP engine, as well as how you can get started using it to rev up application performance. Microsoft built this query processing engine from the ground up, using an all- new lock- free design. It doesn't use any locks or internal latches to maintain transactional data integrity. The lock- free design is one of the key points that differentiates the In- Memory OLTP engine from pinning tables with DBCC PINTABLE or from putting databases on SSDs. DBCC PINTABLE holds a table in SQL Server's buffer pool, but it uses the same relational engine with its locks and latches, so it doesn't offer the scalability found in the In- Memory OLTP engine. The same is true for SSDs, which can provide higher I/O but use the same relational engine. In contrast, the In- Memory OLTP engine uses a new optimistic multi- version concurrency control mechanism in conjunction with algorithms that have been optimized for memory- resident data.
When a row in a shared buffer is modified, the In- Memory OLTP engine makes an entirely new version of that row and timestamps it. This process is very fast because it's done entirely in memory. The engine then analyzes and validates any updated rows before committing them. This design is faster and more scalable than the traditional locking mechanism used by SQL Server's relational database engine because there are no locks or other wait states that prevent the processor from running at full speed. Because this optimistic processing creates a lot of different row versions, it leaves a number of discarded rows in memory. To handle the discarded row versions, Microsoft implemented a new lock- free garbage collection process as part of the In- Memory OLTP engine. The garbage collection process periodically cleans up all the unneeded rows. In conjunction with the new lock- free design, Microsoft introduced a stored procedure compilation process that takes interpreted T- SQL code and compiles it into native Win. The goal is to reduce the number of instructions that the CPU must execute to process the query. A shorter code path equates to faster code execution. The combination of the new query processing engine and the compiled stored procedures are the primary factors driving the high- performance In- Memory OLTP engine. Figure 1 shows the basic architecture of the In- Memory OLTP engine. As you can see, the engine doesn't work in isolation. Instead, it interoperates with the SQL Server relational engine, allowing you to combine memory- optimized objects and standard database objects in the same queries. Tables are copied wholly into memory and are made durable by transaction log writes to disk. The lock- free engine processes transactions for the memory- resident tables. Stored procedure performance is improved by compiling the stored procedures into native code. Typically, T- SQL stored procedures are interpreted, which adds overhead to the execution process. That's not such a big issue when the stored procedure is processing data from disk. However, when the data is memory- resident, it can hold back performance. Compiling the stored procedures to native Win. As you might expect, indexes for memory- resident tables are different. They don't use the B- tree structures used by on- disk indexes. SQL Server 2. 01. Hash indexes are efficient for item lookups but don't perform the best for range values. Memory- optimized nonclustered indexes are best at retrieving ranges of values. They also support retrieving the table rows in the order that was specified when the index was created. Each memory- optimized table must have at least one index. Memory- optimized indexes must be created as a part of the CREATE TABLE statement. You can't use the CREATE INDEX statement to create an index for a memory- optimized table after the table has already been created. Memory- optimized indexes exist only in memory. The index structures aren't persisted to disk, and index operations aren't logged in the transaction log. Essentially, all memory- optimized indexes are covering indexes and all columns are included in the index. It's also important to know that memory- optimized tables can only support a maximum of eight indexes. You can find more information on memory- optimized indexes in Microsoft's Guidelines for Using Indexes on Memory- Optimized Tables. All modern x. 64 processors support this instruction. The only time this might be a problem is if you implement the In- Memory OLTP engine in a 6. VM) that's using an older virtual processor. If that's the case, you'll need to update the VM's virtual processor. In addition, SQL Server needs enough memory to store all the memory- optimized tables and indexes. To make sure there's enough room for row versions, Microsoft recommends that you provide an amount of memory that's two times the on- disk size of the memory- optimized tables and indexes. The recommended maximum size for memory- optimized tables is 2. GB. In addition, Microsoft recommends that you have an amount of free disk space that's two times the size of your memory- optimized tables. The In- Memory OLTP engine is supported on Windows Server 2. R2, Windows Server 2. Windows Server 2. R2 SP2. It requires a 6. OS. In addition, you need to be using the Enterprise, Developer, or Evaluation edition of SQL Server 2. The engine isn't supported in the SQL Server 2. Standard edition. When you install one of the supported SQL Server 2. Database Engine Services to install support for the In- Memory OLTP engine. Notably, the In- Memory OLTP engine supports several high availability technologies, including failover clustering, Always. On Availability Groups, and log shipping. First, not all of the SQL Server 2. The following data types aren't supported by memory- optimized tables: Datetimeoffset Geography Hierarchyid Image Ntext Sql. Here are some of the most important database and table limitations: Database mirroring isn't supported. In- Memory OLTP tables must be dropped and re- created. SQL Server will delete all unused checkpoint files when you back up a database or log. If you don't perform backups, the available space on your hard disk storage will decrease. If you use In- Memory OLTP, Microsoft recommends that you enable Instant File Initialization, which is a Windows Server policy. To enable it, you need to grant the SQL Server Service startup account the SE. Note that the only way to remove a memory- optimized file group from a database is to drop the database. For a complete list of In- Memory OLTP limitations, see the Transact- SQL Constructs Not Supported by In- Memory OLTP and Supported SQL Server Features web pages. Like its name suggests, the AMR tool can help you identify the tables and stored procedures that would benefit by moving them into memory. In addition, it can help you perform the actual migration of those database objects. The AMR tool is installed when you select the Management Tools, Complete option with the SQL Server 2. You access the AMR tool through SQL Server Management Studio (SSMS). The AMR tool bases its recommendation on your actual system workload, so using it is a multi- step process. Essentially, the process requires seven steps: Establish your baseline performance. However, the longer you run the tool, the more representative your captured statistics are likely to be. When the AMR tool is monitoring performance, it collects data every 1. It stores the data in two collections named Table Usage Analysis and Stored Procedure Analysis. Before you use the AMR tool, you must first configure MDW. To configure it, open Object Explorer in SSMS, expand Management, right- click Data Collection, select Tasks, and choose Configure Management Data Warehouse. To start collecting data with the AMR tool, open Object Explorer, expand Management, right- click Data Collection, select Tasks, and choose Configure Data Collection. This will start the Configure Data Collection Wizard. The wizard displays a dialog box that lets you select the current SQL Server instance and an MDW database on that instance, as Figure 2 shows. After those selections are made, select the Transaction Performance Collection Sets check box and click Next. After verifying the selections, click Finish to start the data collection process. You can generate an AMR Transaction Performance Analysis Overview report by right- clicking the MDW database, selecting Reports, choosing Management Data Warehouse, and selecting Transaction Performance Analysis Overview. Sponsored Live Streaming Video. To save this item to your list of favorite Information. Week content so you can find it later in your Profile page, click the.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
August 2017
Categories |