The second Tuesday of April 2016 is now upon us and you know what that means. Well, I hope you know what that means.

It is time for TSQL Tuesday. It is now the 77th edition of this monthly blog party. This month the host is Jens Vestergaard (blog | twitter) and he insists we do a little soul searching to figure out what about SQL Server really makes our hearts go pitter patter for SQL Server. Ok, so he didn’t really put it that way but you get the point, right? What is it about SQL Server that ROCKS in your opinion?

Well, I think there are a lot of really cool features in SQL Server that ROCK! It really is hard to pick just one feature because there are a lot of really good features that can make life so much easier as a database professional. Then again, there is that topic that bubbles to the top in my articles – a lot. If you haven’t followed my blog, here is a quick clue: click here.

Why is this feature so AWESOME?

Truth be told, there are a ton of reasons why I really like it. Before diving into the why, I need to share an experience.

A client using Microsoft Dynamics AX to manage the Point of Sale (POS) systems for their retail chain has been running into a problem with the POS database at each store. Approximately a year ago, this client had upgrade most of the store databases to SQL Server Standard Edition from Express due to the size restriction of the Express Edition. This SKU upgrade was necessary because the database had grown to exceed 10GB. Most of this growth was explicitly related to the INVENTDIM table consuming 3.5GB of space in the data file.

Right here, you may be asking what the big deal is. Just upgrade the SKU to Standard Edition and don’t worry about the size of the database. I mean, that is an easy fix, right? Sure, that may be perfectly acceptable in an environment with one or maybe even a handful of servers. Imagine a retail chain with more than 120 stores and a database at each store. No extrapolate standard edition licensing costs for all of those stores. Suddenly we are talking a pretty big expense to just upgrade. All of that just because one table chews up 35% of the size limitation of a data file in SQL Server Express Edition.

What if there was an alternative with SQL Express to mitigate that cost and maintain the POS functionality? Enter the SYNONYM! You may recall from a previous post a thing or two that I have said about synonyms in SQL Server. There is good and bad to be had with this feature and most of the bad comes from implementation and not the feature itself.

Using a synonym, I can extend this database beyond the 10GB limitation – or at least that is the proposed theory. To make this work properly, the plan was to create a new database, copy the INVENTDIM table from the POS database to this new database, rename the old INVENTDIM table in the POS database, create a synonym referencing the new table in the new database, and then select from the table to confirm functionality. Sounds easy right? Here is the script that basically goes with that set of steps.

CREATE DATABASE [AxRetailDIM];
GO
USE [AxRetailPOS];
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false;
GO
USE AxRetailPOS;
GO
DELETE  id
FROM    INVENTDIM id
WHERE   id.INVENTDIMID NOT IN (
        SELECT  INVENTDIMID
        FROM    INVENTDIMCOMBINATION idc
                LEFT OUTER JOIN dbo.INVENTTABLE it ON idc.ITEMID = it.ITEMID
        UNION
        SELECT  INVENTDIMID
        FROM    dbo.INVENTITEMBARCODE ibc );
SELECT *
INTO AxRetailDIM.dbo.[INVENTDIM]
FROM dbo.INVENTDIM;
GO
USE AxRetailDIM;
GO
ALTER TABLE [dbo].[INVENTDIM] ADD  CONSTRAINT [I_698DIMIDIDX] PRIMARY KEY NONCLUSTERED 
(
[DATAAREAID] ASC,
[INVENTDIMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
GO
USE AxRetailPOS;
GO
sp_rename 'dbo.INVENTDIM', 'dbo.INVENTDIM_OLD'; 
GO
/* after all is well, then inventdim_old can be dropped. */ 
USE [AxRetailPOS];
GO
CREATE SYNONYM [dbo].[INVENTDIM] FOR [AxRetailDIM].[dbo].[INVENTDIM];
GO

This seems to make a fair amount of sense. Querying the INVENTDIM synonym produces results just as would be expected. Notice that there is one additional step in the script which I did not mention. That step removes unnecessary rows from the INVENTDIM table based on an actual inventory item or barcode for the particular dimension variant related to the item. This helps to trim the table to specific rows related to the retail store available for purchase there. In addition, it serves as a failsafe to get the data down to less than 10GB in case of failure with the synonym.

Testing from within SQL Server proved very optimistic. The synonym was working exactly as desired. Next up was to test the change by performing various transactions through the POS.

The solution not only failed, it failed consistently and dramatically. It didn’t even come close. How is this possible? What is Dynamics AX doing that could possibly subvert the synonym implementation? Time to start troubleshooting.

I checked through the logs. Nothing to be found. I checked and validated permissions. No Dice! I checked the ownership chaining. Still no dice! What in the world is causing this failure?

What if I switch to use a view instead of a synonym? I created a view with cross database ownership chains in tact. Test the application again and still failed. What if I use the synonym pointed to a table in the same database? Test from the application and all of a sudden we have success. Now the head-scratching gets a little more intense.

It is time to get serious. What exactly is the Dynamics AX POS application doing that is leading to failure that does not happen when we query direct from within Management Studio? The means to get serious is to now implement that awesome tool I alluded to previously – Extended Events (XE or XEvents).

With no clues being available from any of the usual sources (including application error messages), XE or profiler is about the only thing left to try and capture the root cause of this failure. Since this happens to be a SQL Server 2014 implementation (yeah I omitted that fact), the only real option in my opinion was to use XE. Truth be told, even on SQL Server 2008 R2, my go to tool is XE. In this case, here is what I configured to try and catch the problem:

/* 2012 version */USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'UserStatementsAndErrors' )
DROP EVENT SESSION UserStatementsAndErrors 
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:\Database\XE';
GO
CREATE EVENT SESSION [UserStatementsAndErrors] ON SERVER
ADD EVENT sqlserver.sql_statement_starting (

ACTION ( 
sqlserver.database_id
, sqlserver.session_id
, sqlserver.client_app_name
, sqlserver.sql_text
, sqlserver.username

)
)
, ADD EVENT sqlserver.rpc_starting (

ACTION ( 
sqlserver.database_id
, sqlserver.session_id
, sqlserver.client_app_name
, sqlserver.sql_text
, sqlserver.username

)
)
, ADD EVENT sqlserver.sql_statement_completed (

ACTION ( 
sqlserver.database_id
, sqlserver.session_id
, sqlserver.client_app_name
, sqlserver.sql_text
, sqlserver.username

)
)
, ADD EVENT sqlserver.error_reported (

ACTION ( 
sqlserver.database_id
, sqlserver.session_id
, sqlserver.client_app_name
, sqlserver.sql_text
, sqlserver.username

)
WHERE ([error_number]<>(5703)) ) --2012 is error_number and 2008 is error
, ADD EVENT sqlserver.module_end (

ACTION ( 
sqlserver.database_id
, sqlserver.session_id
, sqlserver.client_app_name
, sqlserver.sql_text
, sqlserver.username

)
)
ADD TARGET package0.event_file
(SET FILENAME = N'C:\Database\XE\UserStatementsAndErrors.xel'
, METADATAFILE = N'C:\Database\XE\UserStatementsAndErrors.xem'
 )
WITH ( MAX_MEMORY = 4096 KB
--,EVENT_RETENTION_MODE = NO_EVENT_LOSS --not possible with error_reported in 2012 or later
, MAX_DISPATCH_LATENCY = 3 SECONDS
, MAX_EVENT_SIZE = 0 KB
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = ON
, STARTUP_STATE = OFF
);
ALTER EVENT SESSION UserStatementsAndErrors ON SERVER
STATE = START;

With the session running, I had the POS tests begin again. Bang! It failed again, but I expected it and wanted it to fail again. This time around, finding the problem turned out to be really easy. As soon as the error hit, I was able to check the trapped events and see what it was that had been missing and ultimately causing this string of failures.

Using the GUI (yeah rare occasion for me with XE), I filtered the events down for display purposes only to make it easier to see what was found by running these tests that was pertinent to the problem. Here is the highlighted text a little larger and easier to see:

Snapshot isolation transaction failed accessing database ‘AxRetailDIM’ because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

Wow! Light bulb shines bright and the clue finally clicks. The POS databases for this client are all set to allow snapshot isolation. Since this error is coming at the time when the failure occurs in the application, it stands to reason that this is the root cause. Time to test by changing the snapshot isolation setting.

ALTER DATABASE AxRetailDIM
SET ALLOW_SNAPSHOT_ISOLATION ON;

That is a quick change and easy enough to test again. With the XE Session still running, and the change in effect, it’s time to test via the POS application again. To my expectations the application is working now. This is good news! Time to test again and again and again to make sure it wasn’t a fluke that it worked and that it was only going to work just the once.

Not a single failure after the change to allow snapshot isolation. One small change with such a big impact and so few clues to be found except in that super Awesome Super Hero feature of SQL Server called Extended Events!

Being able to quickly find the root cause of so much pain is why I enjoy working with the Extended Events feature. It is an efficient way to find a ton of information while causing little overhead to the server.

The bonus here is that XE allowed us to pinpoint a problem with the proposed solution to help save costs while extending a database beyond the 10GB limitation of SQL Express.

Note: I left some notes in the XE session script. These notes help to point out differences between implementing this particular session on SQL Server 2012 (or later) and SQL Server 2008 (or R2).