Understanding SQL Server Query Store

April 1st, 2016

Query Store was introduced in SQL Server 2016 and on Azure SQL DB v12 as a way to track query execution statistics. When Query Store is enabled it will save the query SQL text, the execution plan and execution stats like number of executions, elapsed time, logical reads etc. The information is persisted in the database and allows for later analysis. Query Store should be the first stop for anyone doing performance analysis on SQL Server. Developers need to know about Query Store in order to identify the problem spots early and focus on improving the application performance by improving the queries that will yield the bigger impact. DBAs need to know about Query store for similar reasons to understand the performance bottlenecks, but also to understand the actual workload executed on a server.

Read the rest of this entry »

Introducing DBHistory.com

March 18th, 2016

After 145 months of employment with Microsoft, at the beginning of March 2016 I quit the SQL Server team to pursue my dream of creating a better solution for SQL Server performance monitoring and configuration management. I am now dedicating all my time to building DBHistory.com.

What is DBHistory.com?

At first DBHistory.com will allow you to track the history of all configuration and schema changes on SQL Server. You will see when the change occurred, which user initiated it, on which server, in which database, what objects where affected, and the exact T-SQL text of the statement that triggered the change. Using DBHistory.com you will be able to quickly asses the history of an object, what changes occurred in a certain time interval, view all changes done by a particular user and so on.

There is no tool to purchase, no code to deploy, no storage to reserve, no contracts to sign. Only a configuration wizard to run, DBHistory.com uses SQL Server’s own Event Notifications mechanisms to push all change notifications from the monitored servers to the DBHistory.com service. The change notifications leave the monitored server immediately and are then safely stored with DBHistory.com.

You can use DBHistory.com if you are a software vendor that deploys a solution on-premise at your clients and need to know if any configuration or schema change occurred in your applications. You can use DBHistory.com if you are a remote DBA company that needs to know the history of changes on any of your client’s SQL Server databases, no matter who or when did the change. You can use DBHistory.com if you are a consultant that needs to know what changed since your last visit. You can use DBHistory.com if you have many employees with database access and want to know what changes were done by whom and when. You can use DBHistory.com if you are database development company that needs a history of changes done directly to the database in the development environment. You can use DBHistory.com if you distribute an application with free SQL Server Express Edition and want to understand how many times your application was deployed.

You will be able to set alarms to get notifications when certain changes occur, or when important objects are modified.

The road ahead will add more functionality to DBHistory.com, including performance monitoring

When can I try DBHistory.com?

If you are interested, please sign up now at DBHistory.com. Soon I will extend beta invites for those interested in trying out the service.

The cost of a transactions that has only applocks

March 6th, 2015

I recently seen a discussion on the merits of using SQL Server as an application lock manager. Application would start a transaction and acquire app locks, using sp_getapplock. No actual data would be queried or modified in this transaction. The question is whether using SQL Server as such, basiclaly as an application lock manager, can lead to problems in SQL Server.

sp_getapplock uses the same internals for acquiring and granting locks like SQL Server data locks

Locks are relatively cheap. The engine can acquire, grant and release millions of them per second. A lock cost a small amount of memory, but overall locking is such an important and critical part of SQL Server operations that everything about locks is highly optimized. sp_getapplock is basically just a shell around the lock manager features and as such it gets a ‘free ride’. Highly optimized, efficient, fast and low overhead locks. However, this is not the only cost associated with app locks. Transaction scoped app locks must also play by the rules of transaction rollback and recovery, and one such rule is that exclusive locks acquired by transactions must be logged, since they must be re-acquired during recovery. App locks are no different, and this is easy to test:

create database test;

alter database test set recovery  full;

backup database test to disk = 'nul:';

use test;

begin transaction
exec sp_getapplock 'foo', 'Exclusive', 'Transaction'
select * from sys.fn_dblog(null, null);

00000021:0000005b:0001	LOP_BEGIN_XACT   ... user_transaction
00000021:0000005b:0002	LOP_LOCK_XACT    ... ACQUIRE_LOCK_X APPLICATION: 5:0:[fo]:(ea53e177)

Sure enough our transaction that acquired an exclusive app lock has generated log records. This means that if the application is using SQL Server as a lock manager and holding long lived locks (for example while making a HTTP request) is also pinning the log, preventing truncation. Another side effect is log traffic, if the application is requesting lots of app locks it may result in noticeable log write throughput.

tempdb has a simplified logging model

I mentioned that the requirement to log the exclusive locks acquired in a transaction is a needed for recovery. Since tempdb is never recovered, tempdb has a more lax logging model. Specifically, it is not required to log exclusive locks acquired by transactions. So if you repeat the same experiment as above, but in tempdb you’ll see that no log record is generated:

use tempdb;

begin transaction
exec sp_getapplock 'foo', 'Exclusive', 'Transaction'
select * from sys.fn_dblog(null, null);

If you make sure the current context is set to tempdb when calling sp_getapplock then you get a high performance lock manager for your application, with little overhead for your SQL Server engine. This is actually trivial to achieve in practice:

use test

begin transaction
exec tempdb..sp_getapplock 'foo', 'Exclusive', 'Transaction'
select * from sys.fn_dblog(null, null)

By simply using the 3 part name tempdb..sp_getapplock I have effectively suppressed the logging of the app lock acquired! Of course a word of caution: please make sure that you are OK with app locks not being logged. The important thing to consider is how will your application behave when doing an online recovery. If app locks were not logged your app may acquire a an app lock while the data that was protected by that app lock is actually going through recovery. In practice this is seldom the case to worry, but you have to consider and acknowledge the trade offs.

SQL Server 2014 updateable columnstores Q and A

June 17th, 2014

Are INSERTs serialized because of row group locks?

No. If a trickle INSERT requires an OPEN row group and it cannot place a lock on it it will create a new one. any number of INSERT statements can proceed in parallel, each locking its own row group. While this results in lower quality row groups (possible multiple small OPEN row groups) the decision was explicitly to favor concurrency. This applies as well to BULK INSERTs that fail to create the minimum row group size (~100k rows).

Does Tuple Mover delete the rows in deltastore?

No. Surprised? Each OPEN/CLOSED row group has its own individual deltastore. When the Tuple Mover finishes compressing a row group the entire deltastore for that row group will be deallocated. This is done to avoid the explicit logging of each individual row delete from deltastores. This is the same difference as between TRUNCATE and DELETE.

Does Tuple Mover block reads?

No. No way. Absolutely no.

Does Tuple Mover block INSERTS?

No. Both trickle inserts and bulk inserts into clustered columnstores can proceed while the Tuple Mover is compressing a rowgroup. You only need to think about the fact that the Tuple Mover only has business with CLOSED row groups and INSERTs are only concerned with OPEN row groups. There is no overlap so there is no reason for blocking.

Does Tuple Mover block UPDATE, DELETE, MERGE?

Yes. Spooled scans for update (or delete) cannot re-acquire the row if the row storage changes between the scan and the update. So the Tuple Mover is mutually exclusive with any UPDATE, DELETE or MERGE statement on the columnstore. This exclusion is achieved by a special object level lock that is acquired by UPDATE/DELETE/MERGE in shared mode and by the Tuple Mover in exclusive mode.

Can Tuple Mover, or REORGANIZE, shift rows between row groups?

No. The Tuple Mover (and REORGANIZE) can only compress a row group but it cannot shift rows between row groups. Particularly it cannot ‘stich’ several small deltastores into one compressed row group. REBUILD may appear that it can shift or move rows, but REBUILD is doing exactly what the name implies: a full rebuild. It reads the existing columnstore and builds a new one. The organization (row group numbers and size) of the new (rebuilt) columnstore has basically no relation with the original organization of the old columnstore.

WindowsXRay is made public as Media eXperience Analyzer

May 30th, 2014

One of the most useful performance tools I used internally at Microsoft was the WindowsXRay. I’m pleased to find that it was released the new name of Media eXperience Analyzer. The tool was internally developed by the Windows media perf team and the release info is targeted toward the media developers. But rest assured, the tool can be used for all sort of performance analysis and I had successfully used in analysis of SQL Server performance problems. Using the Media eXperience Analyzer (XA) you typically start by collecting one or more ETL traces using the platform tools like the Windows Performance Recorder (WPR). XA is a visualization tool used to inspect these ETL traces, much like the Windows Performance Analyzer.

How to prevent conversation endpoint leaks

March 31st, 2014

One of the most common complains about using Service Broker in production is when administrators discover, usually after some months of usage, that sys.conversations_endpoints grows out of control with CLOSED conversations that are never cleaned up. I will show how this case occurs and what to do to fix it.

Read the rest of this entry »

How to read and interpret the SQL Server log

March 10th, 2014

The SQL Server transaction log contains the history of every action that modified anything in the database. Reading the log is often the last resort when investigating how certain changes occurred. It is one of the main forensic tools at your disposal when trying to identify the author of an unwanted change. Understanding the log and digging through it for information is pretty hard core and definitely not for the faint of heart. And the fact that the output of ::fn_dblog can easily go into millions of rows does not help either. But I’ll try to give some simple practical examples that can go a long way into helping sort through all the information and dig out what you’re interested in.

Read the rest of this entry »

How to analyse SQL Server performance

February 24th, 2014

So you have this SQL Server database that your application uses and it somehow seems to be slow. How do you troubleshoot this problem? Where do you look? What do you measure? I hope this article will answer enough questions to get you started so that you can identify the bottlenecks yourself, or know what to search for to further extend your arsenal and knowledge.

SQL Server clustered columnstore Tuple Mover

December 2nd, 2013

The updateable clustered columnstore indexes introduced with SQL Server 2014 rely on a background task called the Tuple Mover to periodically compress deltastores into the more efficient columnar format. Deltastores store data in the traditional row-mode (they are B-Trees) and as such are significantly more expensive to query that the compressed columnar segments. How more expensive? They are equivalent to storing the data in an uncompressed Heap and, due to small size (max 1048576 rows per deltastore rowset), they get little traction from parallelism and from read-aheads. It is important for your upload, initial seed and day-to-day ETL activities to achieve a healthy columnstore index, meaning no deltastores or only a few deltastores. To achieve this desired state of a healthy columnstore it is of paramount importance to understand how deltastores are created and removed.

Read the rest of this entry »

Using Hive local mode with YARN

November 19th, 2013

Most examples on how to run Hive in local mode mention using SET mapred.job.tracker=local. But with YARN this setting no longer has the desired effect, Hive queries are still executed with the M/R framework. Stepping through the Hadoop 23 shims reveals that the decision to split into local mode is driven by different configuration setting:

  public boolean isLocalMode(Configuration conf) {
    return "local".equals(conf.get("mapreduce.framework.name"));

Running Hive in localmode is important in debugging, is much easier to attach a remote to the local spawned Java process than to a real cluster, even a local one-node cluster. Thejas has a this short note about running hive in local mode, but following the advise there does not work on YARN clusters like the more recent Hortonworks sandboxes.