Today I have uploaded the 8th SQL Server Quickie to YouTube. This time I’m talking about Hash Indexes in SQL Server 2014. You can find the script used during the demonstration here as a download.
Thanks for watching!
-Klaus
Myths and Misconceptions about Hekaton
November 30, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
Over the last weeks and months, I invested a lot of my time to get a better understanding of Hekaton – the new In-Memory technology that will be part of SQL Server 2014. I have done a lot of reading about the various internal data structures that Hekaton is using for its storage (mainly Hash Indexes, and Bw-Tree). And in additional I gave 5 Hekaton talks across Europe, and also attended various Hekaton sessions by other speakers.
But as I have seen several times, a lot of misinformation, myths, and misconceptions are presented, and people are thinking in the wrong way about Hekaton. Even from the questions that are asked from attendees you can see that a *huge* knowledge transfer is needed so that everyone gets a better understanding about the basic concepts of Hekaton, and when Hekaton is suitable.
Here are just a few highlights from the last weeks that I have heard from presenters and attendees when they have asked questions about Hekaton:
- “Hekaton is an In-Memory technology, means data is not persisted anymore”
- “Hekaton only runs on specific CPU architectures”
- “When you move to Hekaton, you gain a 100x improvement for your workload”
- “There is no Locking, Blocking, and Spinlocking in Hekaton”
Those are just a few highlights about misconceptions that I have heard in the past. So the goal of this weblog posting is give you an overview about the most prominent misconceptions and questions from the last weeks, and I am also telling you *why* they are just wrong. So lets get started with my top highlights (in no specific ordering)!
“Is Hekaton providing the ACID properties for transactions?”
When I start talking about Hekaton, this is the first thing that I am always clarifying: when you use Hekaton, you still have the ACID properties for your transaction! Transactions in Hekaton are always atomic, consistent, isolated, and durable. SQL Server is just using internally different concepts and implementations to ensure these 4 very important properties.
- Atomic: You are still dealing with transactions that are able to rolled forward and rolled back, even when your SQL Server crashes, and SQL Server has to go through crash recovery for your Hekaton enabled database (as long as you are persisting your data across SQL Server restarts – more on that later).
- Consistency: Hekaton also provides you *always* consistent data. This is currently very easy to achieve, because one of the biggest limitations of Hekaton is the ability to create Foreign-Keys, and constraints itself. So mainly you are getting a consistency within your Hekaton enabled table.
- Isolation: A few days ago, when I have delivered my Hekation talk in Bristol/UK during my SQLRelay tour, someone wanted to convince me, that Hekaton provides you Dirty Reads, because there is no Locking and Blocking anymore. WRONG! Hekaton just used an approach called “Multi Version Concurrency Control” (MVCC), that makes the magic of no locks possible. When you are reading data, you are getting back a version that was valid at the beginning of your statement/your transaction (depending on the used Transaction Isolation Level). So there are no Dirty Reads in Hekaton.
- Durable: It depends
When you create your Hekaton table with Schema and Data Durability, your data will also survive a SQL Server crash. It will be just recovered from the transaction log, and the Checkpoint Files that Hekaton writes. That is a very important point: when you want your data to be durable, Hekaton is still using your transaction log, which means that your transaction log will be one of your final bottlenecks in Hekaton. But Hekaton uses here a very efficient logging model, compared to traditional disk based table. One change here is, that only data modifications are logged, but not on the index level anymore. When you are doing an INSERT into a traditional disk based table, SQL Server has to log the INSERT for *every* index (Clustered, Non-Clustered). In Hekaton, SQL Server only logs once the INSERT, because all your Hekaton Indexes (Hash Indexes, Range Indexes) are recreated during the startup of SQL Server. Therefore the impact on the transaction log is as small as possible.
When you are using Hekaton tables that are only persisting the schema, but not the data, the durability aspect of your Hekaton transactions is just gone: when your SQL Server crashed, or you are performing a regular restart, all of the data in your Hekaton table is lost. So no durability. Therefore there is also no logging in the transaction log. Of course this approach only makes sense in specific use cases, for examples during the loading process for your data warehouse. If SQL Server crashes, just restart your ETL process. You are able to recreate your lost data.
“Hekaton is the new No-SQL approach by Microsoft”
That is also a very nice one. Someone in Bristol wanted to convince me that Hekaton is just a new approach by Microsoft of creating a No-SQL product. Come on, with Hekaton we are still talking about a relational database with all ACID properties (see previous point). Hekaton and No-SQL are just 2 different things, which have nothing in common. Hekaton uses internally just a more elegant and faster approach to implement the characteristics of a relational database – nothing more!
“Hekaton only works on specific CPU architectures”
Wow, I thought I was in a wrong dimension! Hekaton only works on specific CPU models/architectures, because internally Hekaton uses so-called Atomic CAS operations (Atomic Compare & Swap, or Atomic Compare & Exchange). That statement is more than completely wrong! Of course, the Bw-Tree uses internally CAS operations to make SMOs (Structure Modification Operations) in the tree as multiple atomic steps. Hekaton uses here internally the Win32 API function “InterlockedCompareExchange”, see http://msdn.microsoft.com/en-us/library/windows/desktop/ms683560(v=vs.85).aspx). This function just compares a value at a specific memory location against the old value, if both are the same, a new value is written to the memory location. The function itself is executed as *one* atomic assembly instruction on the CPU level, means no other thread can interrupt that assembly function. It is just executed as one atomic block from the beginning to the end.
The myth was here, that the needed assembly function is only supported on specific CPU architectures. That is true, but the assembly function is supported since the Pentium processor! The function itself is not available on 386 and 486 architectures… As you can also see from the requirements section of the above mentioned MSDN article, the minimum supported Windows OS is Windows XP! So when you are running SQL Server 2014 on a pre Windows XP installation, that myth would be correct.
“There is no Locking, Blocking, and Spinlocking in Hekaton”
In theory this statement is correct. This statement can be also debated from various aspects. Lets start with the first aspect. Hekaton *itself* is lock, latch, and spinlock free, but you are still dealing with the traditional relational engine of SQL Server. This means when you are leaving the Hekaton universe, you are still dealing with the old code base (sorry for that..) of SQL Server, e.g. the Transaction Log Manager. And this code still has latches and spinlocks to synchronize the access amoung the various threads. From that perspective the above statement is only partially true.
And the second aspect where you can still have a blocking in Hekaton is, when you are dealing with the atomic CAS operations. An atomic CAS operation can not be interruped by a different thread. Therefore SMOs (Structure Modification Operations) in Bw-Trees can be implemented in a very clever, elegant way. This also means when you want to perform at the same time a SMO on the same page in the same Bw-Tree, one thread will win the race, and the other thread has to retry his atomic CAS operation. What happens in the mean time? The thread has to spin, and try the atomic CAS operation again. From my basic understanding, the atomic CAS operation itself is just an assembly function that is wrapped by a synchronization concept like a Criticial Section. This means your thread has to spin, and you are loosing CPU cycles, and increasing the latency of your transaction. Of course, SMOs should be very, very rare, so that this is not a big deal – but it is still possible that threads are spinning, when there is contention for a low-level synchronization object (or assembly function).
This is my basic understanding on atomic CAS operations. If I am wrong on that, please feel free to correct me! 
“Because INT IDENTITY values are not supported in Hekaton, use Sequences”
This was also a nice statement that was done yesterday at the SQLRelay conference in London. I am not signing this statement in any way! Why? Because a sequence is a shared object in your SQL Server database, means the access to the current value has to be synchronized by SQL Server. And that synchronization ends in contention, which means you can not scale your workload, and Hekaton is all about scaling workloads.
I have done on CTP1 some tests with Sequence values, and as soon as you are running a huge amount of parallel threads against your Hekaton tables/stored procedures, you are hitting latch contention in the Sequence Generator. Of course, the Sequence Generator stores on some internal pages the current value, and when the Sequence Generator reads and writes to these specialized pages, they have to be latched. You are just ending up with Latch Contention in the Sequence Generator, and your Hekaton workload will not scale! In my tests it also does not made a difference, if I have requested a whole range of sequence values, nor it made a difference when I used caching. The Sequence Generator was always the bottleneck.
How can you overcome this specific problem? Use our good, old, bad friend UNIQUEIDENTIFIER. These values are generated completed indepedent from each other, which means there is no shared resource involved when you generate a new value, and therefore you will eliminate the bottleneck, and Hekaton will scale until you hit your CPUs at 100% usage (besides other bottlenecks like the transaction log, or your network).
“Hekaton is completely transparent to your application”
This statement is true, as long as you have no idea about database design. As I have already mentioned earlier, Hekaton provides in the first release no possibility to create Foreign-Keys and Check Constraints. I am dealing with a huge amount of worst performing databases during my various consulting engagements, but I have never ever seen any disk based table, which can be moved one-by-one to a Memory-Optimized table. You remember, INT IDENTITY values are currently not supported? When you move to Hekaton, you have to do more than switching a simple switch to get an performance improvement by 100x. Sorry.
“Hekaton uses a traditional B+ Tree structure for Range Indexes”
No! Range Indexes are using a so-called Bw-Tree, which is almost the same as a B+ Tree that is used currently used by SQL Server for Clustered and Non-Clustered Indexes. And the Bw-Tree is based on the B-Link Tree – just to confuse now everyone There are mainly 3 huge differences in a Bw-Tree, compared to a traditional B+ tree:
- The intermediate pages are storing the High Range Key value, instead of the Low Range Key value of the page in the level below. Because the High Range Key value is stored on a page, SMOs (like Page Splits) can be implemented in 2 atomic operations (performed by 2 atomic CAS). This is a concept coming from the design principles of the B-Link tree.
- Pages are *never ever* changed, because this will lead to CPU Cache Line Invalidations, which have to be propagated across the whole memory hierarchy, which is very expensive (in terms of wasted CPU cycles). When Hekaton has to change a page in memory, the original memory location is untouched, and Hekaton just creates a new Delta Record, which describes the performed modification. And a so-called “Page Mapping Table” points to the new delta record, and the delta record to the old unchanged page. Because of this approach CPU Cache Line Invalidations can be avoided.
- Page sizes are elastic, and are not always 8kb large.
“Hekaton provides you super fast business logic inside the database”
From a Microsoft perspective this is a valid statement, because SQL Server is licensed on a CPU core level since SQL Server 2012. The more CPU cycles you are using, the more CPU cores you need, the more you are paying to Microsoft for the CPU core licenses. But from an architectural perspective you are doing everything wrong! A database deals with stored data, and the retrieval of data, but a database is not an application server, that should perform business logic. Just think about that. When you have CPU contention, because you are running a huge amount of business logic within your database server, you should rearchitecture your database, so that you are moving your business logic to a dedicated application server, which is licensed completely different as SQL Server – you are just paying for the OS, and thats it!
“How can I move my complete SAP database to Hekaton. Is Microsoft providing tools for that task?”
When you are thinking about moving a whole database into Hekaton, please start thinking in the first step. Hekaton is there to solve specific problems, like Latch Contention. It only makes sense to move specific tables and stored procedures to Hekaton – but please – not the *whole* database! For every database object (table, stored procedure) SQL Server has to compile and link the corresponding DLL file (that is loaded afterwards into the process space of sqlservr.exe, and this also takes some time. The compilation and linking is also performed when you are restarting your SQL Server, or when you perform a cluster failover. So it directly affects your RTO (Recovery Time Objective) of your High Availability approach!
Conclusion
I hope that I was able to clarify some myths, misinformation, and misconceptions about Hekaton. As I am always saying in my Hekaton talks: Hekaton makes sense, if you have the *right* problems, if you just have the traditional problems (bad indexing strategy, bad performance storage) forget about Hekaton, and make your homework first. Hekaton is like a Formula 1 car: if you have a bad driving style, even a Formula 1 car can’t help you.
Thanks for reading, and I am looking forward to your comments.
-Klaus
How Hekaton will impact the RTO of your database
November 25, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
Over the weekend I have encountered a very interesting side effect of Hekaton in SQL Server 2014, which unfortunately impacts your RTO (Recovery Time Objective) of your database in a negative way. As you know, Hekaton creates for every native compiled Table and Stored Procedure a DLL, which contains the native implementation in C code. These DLL files are loaded into the process space of sqlservr.exe. You can see all the current loaded Hekaton tables from the DMV sys.dm_os_loaded_modules with the following query:
SELECT * FROM sys.dm_os_loaded_modules WHERE description LIKE 'XTP%'
The side effect that I have currently encountered in CTP2 is the fact that the loaded DLL files of Hekaton ARE NOT unloaded when you drop the corresponding Table or Stored Procedure. Imagine you are creating a Native Compiled Stored Procedure, and at some point in time you want to drop that Stored Procedure to recreate it afterwards to get a better Native Compiled Execution Plan (Recompilations during the runtime are currently not supported in Hekaton). In that case the old implementation of your Stored Procedure is still present in the process space of sqlservr.exe, and consumes additional memory. The same happens when you drop a Table, the DLL itself is still present in memory!
The only way to get rid of these additional unnecessary DLL files is to take your WHOLE database offline, and online again. When you afterwards query the DMV sys.dm_os_loaded_modules you will see that only the current implementation of your Native Compiled Table and Stored Procedure is loaded into sqlservr.exe. When you have no dedicated maintenance window available, this is the first big bummer that you encounter with Hekaton.
And now things are really getting nasty: when you are restarting SQL Server, SQL Server compiles and links EVERY DLL that you have generated in the past. Compiling and linking every Hekaton DLL takes some CPU time, and during that phase your database is in the RECOVERY phase, which means it is unaccessible from a users perspective! Even when you try to access a traditional disk based table, you are getting the following error message during that phase:
Msg 922, Level 14, State 1, Line 1
Database ‘HashCollisions’ is being recovered. Waiting until recovery is finished.
You can also see in the folder C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp that every old .c, .obj, and .dll is still present, because they are needed for the next startup of SQL Server.
Imagine you are creating and dropping immediately 50 Native Compiled Tables and Stored Procedures. In that case you are ending up with 100 DLL files, which are compiled and linked during the startup phase of SQL Server. In a test case that I was running against CTP2 of SQL Server 2014, my SQL Server database was online again after 69 seconds! See the following screenshot from the SQL Server Error Log:
So please be aware of these side-effects, because they are impacting your RTO (Recovery Time Objective) in a huge way! Just think you are performing a Cluster failover. In that case EVERY DLL must be compiled and linked on your other Cluster node until your database becomes ONLINE for your end users. As I have said earlier, this is the behavior that I have encountered on CTP2 of SQL Server 2014, so I’m hoping for some improvements in this area until the RTM version is released.
Thanks for reading
-Klaus
Choose your Hash Bucket Count very wisely in Hekaton!
November 23, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
Today I have encountered a very simple blog posting where someone was inserting 1 millions of rows into a Hekaton Hash Index of 2048 buckets. Therefore I was running some tests how Hash Collisions in the Hash Buckets will affect the throughput of your Hekaton workload – and the results are very, very interesting. In the first step I want to simple describe what a Hash Collision is.
As you might know (hopefully), Hekaton tables are implemented as Hash Indexes in SQL Server 2014. Wikipedia has a very nice page about the basics of Hash Tables in general, which are used as the basic foundation of Hash Indexes. In a nutshell, SQL Server is applying a Hash Function to the key value of your Hash Index, and the result of that Hash Function determines in which Hash Bucket your row will be stored finally. If multiple key values are hashed to the same value, SQL Server inserts multiple rows in that specific Hash Bucket, and just chains the multiple entries in that Hash Bucket. See the following picture (taken from Wikipedia):
As you can see from this picture, the keys “John Smith” and “Sandra Dee” are hashed to the same bucket – in our case, the bucket 152. This means that both rows are stored in the same Hash Bucket, which affects the INSERT performance, and also the SELECT performance of your queries. During the INSERT, SQL Server has to maintain the linked list, and during SELECT queries SQL Server has to scan the linked list.
After that introduction, let’s demonstrate the performance impact of Hash Collisions with a simple example. Let’s create a database with a simple Hekaton table:
-- Create new database CREATE DATABASE HashCollisions GO --Add MEMORY_OPTIMIZED_DATA filegroup to the database. ALTER DATABASE HashCollisions ADD FILEGROUP HekatonFileGroup CONTAINS MEMORY_OPTIMIZED_DATA GO USE HashCollisions GO -- Add a new file to the previous created file group ALTER DATABASE HashCollisions ADD FILE ( NAME = N'HekatonContainer', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\HashCollisionsContainer' ) TO FILEGROUP [HekatonFileGroup] GO -- Create a simple table CREATE TABLE TestTable ( Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1048576), Col2 INT NOT NULL, Col3 INT NOT NULL ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY ) GO
As you can see from the source code, I’m using here just a Hash Bucket count of 1024 – not too many buckets, when we insert afterwards 1 million of records. In the next step I’m also creating a native compiled Stored Procedure, so that I can go wicked fast with Hekaton:
-- Create a native compiled Stored Procedure
CREATE PROCEDURE InsertTestData
WITH
NATIVE_COMPILATION,
SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH
(
TRANSACTION
ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = 'us_english'
)
DECLARE @i INT = 0
WHILE @i < 1000000
BEGIN
INSERT INTO dbo.TestTable (Col1, Col2, Col3) VALUES (@i,@i, @i)
SET @i += 1
END
END
GO
As you can see, I’m just inserting here 1 million of records in a simple loop. When I’m running this Stored Procedure in a VM with 1 CPU, and 2 GB RAM, the execution time is around 24 seconds – awefully slow. When you are doubling the Hash Bucket count over and over again till you have 1048576 buckets, you can see very nicely that the performance increases as you create more and more Hash Buckets. With 1048576 buckets, the execution of the same Stored Procedure just takes around 600ms! This is huge differentiation compared to the first test run with just 1024 buckets. You can also check with the DMV sys.dm_db_xtp_hash_index_stats how many buckets you have in your Hash Index.
So the moral of this story? Choose your Hash Bucket count for Hekaton Hash Indexes *very, very* wisely, as they are really impacting the performance of your workload! The optimal number of buckets is just the number of distinct values that you are storing in the Hash Index – with some additional free space, just to be on the safe side You also have to make sure not to choose the bucket count too high, because otherwise you are just waisting memory. So again – as almost everything in SQL Server – it depends on your workload, besides Database Shrinks
Thanks for reading
-Klaus
My SQLrally Session about how to measure Index Impact Changes
November 8, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
During the current week I have spoken at 2 SQLrally conferences, once in Stockholm/Sweden, and once in Amsterdam/Netherlands. At both conferences I have presented my brand-new session titled “How to measure Index Impact Changes”.
The goal of the session is very easy: imagine you want to change your Indexing Strategy in for your production system, and before applying that change, you want to find out in a reliable way, if your change is good or bad one. In my session I am presenting a simple approach how to capture your production workload, how to replay it on a different isolated test environment, and how to measure the impact of the index changes itself.
The feedback from the audience was really awesome, because in that session I described an approach, for whom people where looking for a long time. And finally they found one
If you were not able to join one of my SQLrally sessions in Stockholm and/or Amsterdam, it is not a big deal, because I am also offering this session through my brand-new SQLpassion Online Academy. So if you are interested in this session, please feel free to check-out my online course about it.
Thanks for reading
-Klaus
Posted with BlogsyAnnouncing the SQLpassion Academy 2014 Course Schedule
November 2, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
Time flies by, and the year 2013 is almost over for me. So it’s time to announce the course schedule of the SQLpassion Academy for the next year.
I’m running in March 2014 my SQL Server Performance Tuning & Troubleshooting Workshop across 4 different locations in Europe. Besides the usual locations I’m also running my workshop in Stockholm/Sweden, and because of the huge demand I’m also rerunning the workshop in Kontich/Belgium:
- March 3 – 7 in Vienna/Austria
- March 10 – 14 in Kontich/Belgium
- March 17 – 21 in Stockholm/Sweden (NEW!!!)
- March 24 – 28 in Zurich/Switzerland
In addition to these events in March 2014, I’m also running my workshop in May 2014 in London/UK (May 13 – 16) in cooperation with TechniTrain.
If you are interested in attending, don’t hurry to sign-up, because there is an Early-Bird Price until the end of this year available.
Thanks for reading
-Klaus
Fixing Cardinality Estimation Errors with Filtered Statistics
October 29, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
Cardinality Estimation is one of the hidden gems in SQL Server. In general, Cardinality Estimation means that the Query Optimizer tries to find out during the query compilation, how many rows – on average – are returned from the various operators in the Execution Plan. This estimation is used to drive the plan generation itself and choose the right plan operators – for example a physical join operator like Nested Loop, Merge Join, or Hash Join. When these estimates are wrong, then the Query Optimizer will choose a wrong plan operator, and your query can be veeeeeeery slow – trust me!
The Query Optimizer uses for the Cardinality Estimation the so-called Statistic objects. Every time when you create an Index, SQL Server also creates an underlying Statistic object. That object describes the data distribution of that index. In addition, SQL Server is also able to create statistic objects during query execution, when necessary (Auto Create Stats). The data distribution itself (of the first column in a composite index key) is described in a so-called Histogram.
One of the main pains of the Histogram is it can only have up to 200 so-called Steps. A step describes the data distribution for a given part of your column data. The larger your table gets, the more inaccurate your histogram will be, because you can only have up to 200 steps (a histogram must be as compact as possible, it must fit onto one page of 8kb).
For additional columns in a composite index key, SQL Server stores in the statistics object a so-called Density Vector that describes how unique the values of the composite index keys are (in combination to each other). When you have – for example – 3 different values in a column, the density vector of that column is 0.33333 (1 divided by 3).
Beginning with SQL Server 2008, SQL Server also supports so-called Filtered Statistics (along with Filtered Indexes). With Filtered Statistics you can create a statistic objects across a subset of your data. For that subset of data, you are getting again a Histogram, and the Density Vector. If you have outliers in your data, you can create a Filtered Statistics object for that range to give the Query Optimizer a better estimation how many rows are returned, when that specific area of your data is queried. So you are just improving the Cardinality Estimation with Filtered Statistics that will give you directly a better performing Execution Plan. The following listing shows a simple example how a Filtered Statistics object is created in SQL Server 2008 and onwards:
CREATE STATISTICS Country_Austria ON Country(ID) WHERE Name = 'Austria' GO
As you can see from the listing above, you just restrict with the WHERE clause the subset of the table data, that should be described through the new Filtered Statistics object. But the Query Optimizer can only make use of this new statistics object, when you also include the *same* WHERE clause in your query, like:
SELECT SalesAmount FROM Country INNER JOIN Orders ON Country.ID = Orders.ID WHERE Name = 'Austria' GO
If the same WHERE clause is not included in your query, the Query Optimizer will use the default statistics object that comes along with the index that is accessed in the Execution Plan. If you enable the Trace Flag 9204 for the previous query, you can also see which statistics where used by the Query Optimizer during the Cardinality Estimation:
SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'Austria'
OPTION
(
RECOMPILE,-- Used to see the Statistics Output
QUERYTRACEON 3604,-- Redirects the output to SSMS
QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
)
GO
The query itself must be also compiled (hence the query hint RECOMPILE, if an Execution Plan is already cached), so that you can see in the messages window of SQL Server Management Studio the Statistics that were used for the Cardinality Estimation.
Based on this simple introduction to Filtered Statistics, I want to show you now a real world example how Filtered Statistics can improve the quality of the Execution Plan. In the first step we are creating 2 tables: Country and Orders. The Country table is a simple lookup table, where I have stored all the countries where I’m doing business. And the Orders table just tracks every order, and has a relationship to the Country table so that I know in which country that order was placed. In a Data Warehouse scenario you can also think about a fact and dimension table.
-- Create a new database CREATE DATABASE FilteredStatistics GO -- Use it USE FilteredStatistics GO -- Create a new table CREATE TABLE Country ( ID INT PRIMARY KEY, Name VARCHAR(100) ) GO -- Create a new table CREATE TABLE Orders ( ID INT, SalesAmount DECIMAL(18, 2) ) GO
In the next step I’m creating a supporting Non-Clustered index on the column Name of the table Country, and I’m also creating a Clustered Index on the columns ID, and SalesAmount of the table Orders:
-- Create a Non-Clustered Index CREATE NONCLUSTERED INDEX idx_Name ON Country(Name) GO -- Create a Clustered Index CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount) GO
And finally I’m inserting my initial data in both tables:
-- Insert a few records into the Lookup Table INSERT INTO Country VALUES(0, 'Austria') INSERT INTO Country VALUES(1, 'UK') INSERT INTO Country VALUES(2, 'France') GO -- Insert uneven distributed order data INSERT INTO Orders VALUES(0, 0) DECLARE @i INT = 1 WHILE @i <= 1000 BEGIN INSERT INTO Orders VALUES (1, @i) SET @i += 1 END GO
To make sure that all statistics are up-2-date, I’m updating the statistics with a full scan:
-- Update the Statistics on both tables UPDATE STATISTICS Country WITH FULLSCAN UPDATE STATISTICS Orders WITH FULLSCAN GO
Let’s run now the following query:
SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'UK'
OPTION
(
RECOMPILE,-- Used to see the Statistics Output
QUERYTRACEON 3604,-- Redirects the output to SSMS
QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
)
GO
When you look in the Execution Plan, you can see that we have a big problem with the Cardinality Estimation:
SQL Server estimates 501 rows, and in reality the Clustered Index Seek operator returns 1000 rows. SQL Server uses here the Density Vector of the Statistics Object idx_ID_SalesAmount to make that estimation: the Density Vector is 0.5 (we have only 2 distinct values in that column), and therefore the estimation is 501 (1001 * 0.5).
When you run the same query again, with a parameter value of “Austria”, SQL Server will again estimate 501 rows, but the query itself will only return 1 row… This behavior can have a huge negative side effect in the Execution Plan, when other operators are using these estimations for their operation. For example, a Sort and Hash Operator uses these estimations to size the needed memory grant accordingly. If you have an Under-Estimation, your query will spill over to TempDb, if you have an Over-Estimation, you are just wasting memory, which can also lead to contention problems, when you have a huge amount of concurrent queries (the maximum amount of Query Memory is restricted by Resource Governor…)
You can help in that specific scenario with a Filtered Statistics object. This will give SQL Server more information about the data distribution itself, and will also help in the Cardinality Estimation. For that specific scenario, I have created 2 different Filtered Statistics, for every country (where I had sales) I have created a separate Filtered Statistics object:
-- Fix the problem by creating Filtered Statistics Objects CREATE STATISTICS Country_UK ON Country(ID) WHERE Name = 'UK' CREATE STATISTICS Country_Austria ON Country(ID) WHERE Name = 'Austria' GO
When you are now rerunning the query, you can finally see that the Cardinality Estimation is correct:
When you have created Filtered Statistics on your tables, you also have to be aware about the maintenance. The automatic maintenance is again done by SQL Server when 20% of the underlying data has changed – from the *whole* table itself!!! Imagine you have a table with 10000 rows, and you have created a Filtered Statistics object on a subset of that table, let’s say 500 records. In that case, SQL Server will update the Filtered Statistics object when around 2000 data changes where done for the given columns. So you have to change 4x the data in the Filtered Statistics objects, until SQL Server will invalidate and update it (when you have no data changes outside the Filtered Statistics interval). So this is a big bummer, that you have to have in your mind, when you are working with Filtered Statistics.
I hope that this blog posting gave you a good overview about Filtered Statistics, and how you can use them in SQL Server to improve the Cardinality Estimation for given queries.
Thanks for reading
-Klaus
My upcoming Speaking Schedule
September 23, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
Over the next weeks and months I have a lot of various speaking engagements where I’m talking about various aspects of SQL Server Performance Tuning & Troubleshooting.
SQLSaturday #258, Istanbul/Turkey, October 5
It’s the first time that I’m in Istanbul, and even in Turkey, so I’m really looking forward to this event. I’m presenting here my famous “Advanced SQL Server Troubleshooting” session, where I’m talking about some advanced concepts and problems in SQL Server, and how you can troubleshoot them very easily with the tools and techniques provided by SQL Server.
SQLSaturday #260, Milan/Italy, October 8
Almost directly after Istanbul I’m flying to Milan in Italy where I’m presenting a session about Hekaton/XTP (Extreme Transaction Processing), the new In-Memory technology provided by SQL Server 2014. I will talk here about the challenges that we are facing currently in traditional databases like SQL Server, and how these problems can be solved by an In-Memory technology like XTP/Hekaton.
SQLRally Nordic Stockholm/Sweden, November 5 – 6
It’s my 2nd time that I’m invited to speak at SQLRally Nordic. Last year I have presented in Copenhagen about Extended Events, and this year in Stockholm I’m talking about how you can measure the impacts when you are changing your Indexing Strategy. Imagine you are adding a new Index, and then you want to know which queries are now faster (mainly your read workload), and which queries are now slower (mainly your write workload). I will show you here a proven technique that I’m already using for several years very successfully when I’m working on Indexing Strategies for my customers.
SQLRally Amsterdam/Netherlands, November 7 – 8
Immediately after SQLRally Nordic I’m flying directly to Amsterdam for the 2nd SQLRally in this week, where I’m speaking about the same subject as in Stockholm: Index Impact Analysis.
I’m very proud that I have been invited to SQLRelay, which is a community oriented conference in the UK, which takes place at several cities across the whole country. I’m presenting here in Norwich (November 27), Bristol (November 28), and finally in London (November 29). The topics itself are not determined exactly by now, but again I will deliver some sessions about performance tuning and troubleshooting. So stay tuned
Besides all these events, I’m also running my own various SQL Server Workshops around Europe in November. You can find further information about them on the SQLpassion Academy website. If you are a member of the Belgium SQL Server User Group, please indicate this during your registration for my SQL Server Performance Tuning & Troubleshooting Workshop, because you are eligible for a 10% discount!
And, if you can’t attend any of these in-person events, I highly recommend my new SQLpassion Online Academy, where I’m providing High Quality Online Video Trainings with Instant Access to you. Till the end of September 2013 you can also buy 3 online videos for the price of 2 – so don’t wait too long!
If you are attending one of these events, leave a comment, and let’s try to meet each other in person!
Thanks for reading & I’m looking forward to see you at one of these events in-person or even virtually
-Klaus
Announcing the SQLpassion Online Academy
September 16, 2013 By Leave a Comment
Over the last weeks it was a little bit silent on my Weblog for a very good reason: I’m very proud today to announce the SQLpassion Online Academy, where I’m providing in-deep SQL Server trainings directly to you!
Do you have enough from watching online Web Casts about SQL Server with a bad video quality? Do you have enough from looking a whole hour on a massive amount of PowerPoint slides? Do you have enough by just hearing the presenter in the background through a bad audio quality? Do you want to attend my famous SQL Server Workshops, but you are not able to join them physically? With the SQLpassion Online Academy you will get:
- HD Quality SQL Server Trainings, directly accessible on your PC, Mac, Tablet, and Smartphone!
- Learn about SQL Server where and when you want!
- A good mixture of PowerPoint Slides, intensive Flip-Chart Sessions, and Demos within SQL Server Management Studio
- Immediate download of all the Slides & Samples!
- 24/7 online access to the bought courses, up to 12 months!
- The trainings are presented by Klaus Aschenbrenner, a Microsoft Certified Master for SQL Server 2008. Your learning success is guaranteed!
To get a tasting and feeling about our offered Online Courses, and our Training Approach, visit our Youtube Channel, where you can watch our free SQL Server Quickies. If you want to see a short introduction video, that shows you how easy it is to signup and watch a bought SQLpassion Online Academy Course, you can watch our How-To Video.
By today I’m offering 3 training videos, that you can also buy until September 30 in a bundle where you are getting 1 video just for free!
- Parameter Sniffing – 1 hour, $ 24.90
- Index Impact Analysis – 1 hour, $ 24.90
- Choosing the right Clustered Key – 1 hour, $ 24.90
You can find further information about the SQLpassion Online Academy on my website, where you can also watch a Promotion video for every offered video.
Thanks for reading & enjoy learning about SQL Server in a completely new, unique fashion!
-Klaus
How to become a better, well-rounded SQL Server DBA
September 4, 2013 By Leave a Comment
(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)
People are sometimes asking me, how to become a better, well-rounded SQL Server DBA. Of course, the first reason with which I’m responding is “Experience, Experience, Experience”. Nothing helps you more than a daily experience with SQL Server. But on the other hand you also need to know how SQL Server works internally, how you can attack performance related SQL Server problems, and how to troubleshoot worse performing SQL Server installations. For that reason I’m providing with my company SQLpassion high quality SQL Server Workshops across Europe, with the goal to make YOU a better, well-rounded SQL Server DBA!
Source: http://de.wikipedia.org/wiki/Uncle_Sam
Besides the deep technical information about the internal working mechanism of SQL Server, the workshops are also flooding you with a huge amount of demos and approaches how to troubleshoot, and finally resolve SQL Server related performance problems. Approaches that I’m using on a daily basis in my various consulting engagements to hunt down SQL Server performance problems. People are also describing the information flood during the workshops like drinking continuously from a fire hose
Here are some feedbacks from previous attendees over the last years:
- “I have learned throughout the workshop more than in my whole SQL Server life!”
- “The workshop was like listening to a story, not just technical info. Even though it was demo based it had practical and application real-world applications.”
- “Although I already knew a lot of the principles that was gone over, I also learned a lot that I did not know. It will be good to take the additional knowledge back to work and see if I can improve our production environment.”
- “Klaus provided plenty of information and techniques for SQL Server performance troubleshooting that are not evident in standard SQL Server training and from available online information.”
- “My notes have a bunch of TODOs for follow up research and implementation. Mostly it’s a bunch of settings to check and probably tweak to suit our environment, but I also found a couple new tools to check out.”
- “Baselining is the key to troubleshooting. Solving a problem can lead to more opportunities and sometimes solving problems can create more problems, without a baseline you’ll never know.”
If you also want to try the experience about how it is to drink continuously from a fire hose to become a better, well-rounded SQL Server DBA, visit my SQLpassion Academy website, where the various workshops are listed, that I’m running over the next months in Europe. And if you want to get a tasting about my workshops, I also highly recommend to watch my various free videos available on my Youtube Channel.
I’m looking forward to see you & thanks for reading
-Klaus