I’ve been tinkering with SQL Server on Linux lately and noted a few things in regards to performance I thought I would share with you. SQL Server 2019 on Linux uses the same SQL Server database engine with many of the performance features and services you would find on Windows. There are more similarities than you would initially think. However, if you’re a Linux user who is new to SQL Server, I thought the following introduction to some performance features on SQL Server 2019 on Linux will be helpful.
As I’ve written about before in my 3-part blog series that you can find here, a columnstore index allows you to store and query large stores of data in a columnar data format, called a columnstore. A columnstore index eliminates the need to maintain separate systems for operational and analytics workloads. The columnstore index maintains a copy of the data so that OLTP and analytics workloads run against separate copies of the data. This minimizes the impact of both workloads running simultaneously.
Let’s take a look again at creating one. You’ll note it’s exactly the same as in Windows.
Create a columnstore index
Using TSQL to add a Columnstore index to the SalesOrderDetail table
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore] ON Sales.SalesOrderDetail (UnitPrice, OrderQty, ProductID)
Now let’s run a query to use our new index.
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM Sales.SalesOrderDetail GROUP BY ProductID ORDER BY ProductID
To verify that the columnstore index was used, we can view the execution plan which we can easily do just like in Windows or we can look up the object_id for the columnstore index and confirm that it appears in the usage stats for the SalesOrderDetail table:
SELECT * FROM sys.indexes WHERE name = 'IX_SalesOrderDetail_ColumnStore'
SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('AdventureWorks') AND object_id = OBJECT_ID('AdventureWorks.Sales.SalesOrderDetail');
So, here is something I haven’t yet written about. SQL Server’s In-Memory OLTP features can significantly improve the performance of your application systems. However, it’s not for everyone. Here is a couple example that a particularly good candidates for OLTP:
- Environments that are highly transactional with large numbers of SQL INSERTs concurrently
- Environments that process heavy calculations using TSQL
Some things In-Memory gives us, whether Linux or Windows are
- Memory-optimized tables—A memory-optimized table stores data of itself in active memory and a secondary copy on the disk. It does this in the event the data needs recovery after a shutdown-then-restart of the server or database. The memory-plus-disk duality is unseen and happens only behind the scenes.
- Natively compiled modules—A native module references memory-optimized tables only it does not pertain to the disk-based copy. Microsoft has seen native compilation result in durations that are 1/100th of the interpreted duration. You can read more about the performance here.
So how do we configure and use In-Memory OLTP
First step is to set the database compatibility level to at least 130, SQL Server 2016. If you don’t know your compat level you can check it by doing the below. If you find you need you change it I have given you the script to do so.
Use the following query to check the current compatibility level of AdventureWorks:
USE AdventureWorks GO SELECT d.compatibility_level FROM sys.databases as d WHERE d.name = Db_Name(); GO
If necessary, update the level to 130.
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130; GO
When a transaction involves both a disk-based table (regular table) and a memory-optimized table, the memory-optimized portion of the transaction must operate at the transaction isolation level named SNAPSHOT. Execute the following to turn on MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to enforce this level for memory-optimized tables in a cross-container transaction:
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON GO
Now let’s create a Memory Optimized FILEGROUP and a container for data files. Remember we are using Linux, so we use containers for the files, but its just a file group like in windows.
ALTER DATABASE AdventureWorks ADD FILEGROUP AdventureWorks_mod CONTAINS memory_optimized_data GO ALTER DATABASE AdventureWorks ADD FILE (NAME='AdventureWorks_mod', FILENAME='/var/opt/mssql/data/AdventureWorks_mod') TO FILEGROUP AdventureWorks_mod GO
It’s that easy. Now let’s talk about one of my favorite performance tuning tools, Query Store which is also available in Linux.
Query store collects detailed performance information about queries, execution plans, and runtime statistics. Query store isn’t active by default, so you need to enable it on database level for each database you want it. I could write many blogs on Query Store, this is only meant to let you know it exists on Linux too, so I am only scratch the very top surface for you here.
Enable query store with ALTER DATABASE
ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
Here is a query you can run to get information about queries and plans in the query store
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.* FROM sys.query_store_plan AS Pl JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id ;
Query dynamic management views
SQL Server on Linux as well as Windows gives us Dynamic management views that provide server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. This is no different then what we are used to in Windows.
Here is one for example. Query the dm_os_wait stats dynamic management view and you can see for yourself, there is nothing different.
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;
Here I just went over a few performance related things inside SQL Server 2019 in regards to SQL Server on Linux. I did this just to shed some light on the fact that Linux and Windows offer the same tools with just a different underlying OS. Hopefully you can put some of them to use in your own environment. To learn more about what you can do with Microsoft SQL 2019, Microsoft has a free Packt guide Introducing Microsoft SQL 19.