SQL indexed views are incompatible with Dynamics GP

Trying to use indexed views with Dynamics GP? – well I’ve been there and I have seen others attempt the same. The Dynamics GP database and application are not really compatible with SQL indexed views. To help out those searching around this subject I thought I should write up our experience.

What is an indexed view?

An indexed view is a SQL database view that has had an index applied to it. This sounds obvious, but the important thing to realise is that the index will be materialised as an index on disk. Think of it as another table created on disk representing the data held in the view, this is why it is quick to get the data if it is summary data. This index "table" is maintained whenever the data that it covers is altered. So it is easy to imagine that indexed views are great for creating summaries, filtered views of data that cross multiple tables, as they will keep track of changes automatically and update the index "table", thus keeping the data "in-sync" with the data in the various tables that it derives from. As the data that represents the SQL view has been pushed to disk and persisted there, querying that summary data is lightweight and quick, because the work has already been done to process the data when it was stored, pre-joined between tables and/or summarised, rather than the database engine having to do all that work on the fly for each query as the query is ran.

However, there is a downside. When (any) index is created, you pay a price for the benefit of fast data recall when reading, by suffering with slightly longer writes times and those longer writes lead to longer living locks on the data and then this can lead to blocking. This may cause issues with performance in some circumstances. The extra writing is because the data held in the index "table" that represents the view needs to be maintained (updated) whenever any of the data underpinning that view changes. Depending on the workload sizes of tables, IO performance etc, this may be significant work if large numbers of records are updated at once. Again as with any SQL index, it takes space on disk too. For large wide views over large tables this may be a consideration too bloating storage and the knock on consequences that brings.

One of the most frustrating matters when working with indexed views is that there are a whole heap of constraints and restrictions around what is permitted in the query that forms them. For example, when using GROUP BY, it must contain the function BIG_COUNT(*) as a column and various database settings restrictions can apply too (there are many, many more). This means when designing the simplest views SQL compile errors warning that it is not possible to do "this and that” frequently cause annoyance. It is very obvious why this is the case when you think about it. It is due to the fact the data is persisted to disk, so it needs to be unchanging to store it, thus any SQL function used by the view has to be deterministic -aha, no “getdate()” function! It is surprising how often this will catch people out. I could go on and on about the restrictions and requirements of indexed views, but just go try making one and you’ll discover the pain yourself, then go read up the documentation to realise how much there is to it!

Another example below are the SQL SET OPTIONS that are required by SQL indexed views…

IndexViewSettings

Using indexed view with Dynamics GP

Professionally we use indexed views a lot in our applications for the speed of access and real time integrity of summary data they offers, so sooner or later a GP admin or GP developer decides they would quite like to use an indexed view... -then it all ends in tears, let us see why…

WITH SCHEMABINDING

This is the first problem. To build an index view the view must use SCHEMABINDING. This locks the database schema and the view together. Unfortunately this may then cause issues with application updates. When Dynamics GP is updated for a service pack or upgrade, sometimes the tables or other database objects may need to be dropped and recreated. This happens during the upgrade process, however if the object that is to dropped is schema bound, then the upgrade script will not be allowed to do what it wants, causing the upgrade to fall over. Hence if indexed views have been bound to the GP tables this is a real risk when a site comes to do an upgrade. Obviously if the person performing the upgrade was aware, then they could drop the view and recreate it after the upgrade, but in real life the knowledge is lost as employees leave or contractors move on causing upgrade pain. 

The second problem is more terminal. Let's try it and see what happens if we create a view over the sales order header and lines to speed up the number of sales lines by country that and not voided in historical transactions. Create the view on a non-production database like this…

 

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
-- We must schema bind
IF OBJECT_ID ('SalesVoidedLinesSummary', 'view') IS NOT NULL
    DROP VIEW SalesVoidedLinesSummary ;
GO
CREATE VIEW SalesVoidedLinesSummary
    WITH SCHEMABINDING
    AS 
       SELECT  COUNT_BIG(*) Cnt,
               SOP30200.SOPTYPE,
               SOP30300.CCODE as Country
       FROM 
       dbo.SOP30200
       JOIN
       dbo.SOP30300 ON SOP30200.SOPTYPE=SOP30300.SOPTYPE AND SOP30200.SOPNUMBE=SOP30300.SOPNUMBE
       WHERE
       SOP30200.VOIDSTTS=0 --Not Voided
       AND SOP30200.SOPTYPE IN (1,2)
       GROUP BY  SOP30200.SOPTYPE, SOP30300.CCODE
GO

 

Then create the indexed view by adding the index. - REMEMBER I SAID NO PRODUCTION DATABASES WITH THIS LITTLE EXPERIMENT!


--We materialise the view by creating an index on it
CREATE UNIQUE CLUSTERED INDEX IX_SOP30200SOP30300VoidSummary
    ON SalesVoidedLinesSummary (SOPTYPE, Country);
GO

 

We can now query the view data like so..

SELECT  * FROM SalesVoidedLinesSummary where SOPTYPE=1 AND Country='PT'

and directly before we created the view with this…

SELECT  COUNT_BIG(*) Cnt,
               SOP30200.SOPTYPE,
               SOP30300.CCODE as Country
       FROM 
       dbo.SOP30200
       JOIN
       dbo.SOP30300 ON SOP30200.SOPTYPE=SOP30300.SOPTYPE AND SOP30200.SOPNUMBE=SOP30300.SOPNUMBE
       WHERE
       SOP30200.VOIDSTTS=0 --Voided
       AND SOP30200.SOPTYPE = 1
       AND SOP30300.CCODE='PT'
       GROUP BY  SOP30200.SOPTYPE, SOP30300.CCODE

 

...and we find that from not having the view to having the view we have dramatically reduced the query time as shown below. I’m not sure about the integrity of those figures but this isn’t a discussion around query optimisations, just accept that indexed views solve performance problems.

    Total execution time without view:  17390   
     Total execution time with view:        505   

 

In production

So developer or IT pro after testing that out on the test company database says, “great, now lets create it on the production database as that worked like a charm”, and then goes to login to GP and create a sales order having created the view…

...to then get the dismay of the error that follows...

“An edit operation on table ‘SOP_Master_Number_SETP’ failed. A record was already locked'.”

IndexViewGPError1

and

INSERT failed because the following SET options have incorrect settings ‘QUOTED_IDENTIFIER, CONCAT_NULL_YEALS_NULL,ANSI_WARNINGS’. Verify that SET options are correct for use with indexed views and”…

GPIndexViewError2

… then starts the support calls flowing in from the users! 

So why we can’t have nice things?

Go back up in this post, as you saw in the graphic, that there are certain SQL SET options required to make indexed views work. Sadly these are not compatible with the SET options required to make Dynamics GP work shown below! -thus the application breaks, simple as that! If those phones are still ringing from your users right now, then just simply drop the view and your users will be happy again.

We did play around a bit but never found a solution to this issue, but if you have a work around do let me know.

References

Mariano Gomez has a nice post about why this is required by Dexterity to make GP run correctly in this post:

Microsoft SQL Server DSN Configuration

Microsoft - Creating Indexed Views

Gavin wrote this which inspired me to document the issue before others fall into it...

Brief overview and comparison of how summary values are stored and calculated in Dynamics GP, Dynamics NAV and Dynamics 365 Business Central

 

Yes you can - SQL Server Table Compression and Dynamics GP


2019-03-01_23-12-19

Today I attended SqlBits in Manchester UK, where there was a session “Performance tuning SQL server on crappy hardware” by Monica Rathbun.

Monica has a the fast and punchy presentation style I enjoyed. Although I had already experienced or knew most of what was covered it was still a good presentation. There was one take away I noted in my notebook to comeback to later. Now back at the hotel I’m having a look.

Row/Page compression - More Data in MEMORY

Monica was promoting the use of COMPRESSION – not just backup compression but ROW/PAGE database compression in the database engine itself.

By compressing the data in the database, the theory goes that you reduce I/O required to move the data around and allow much more relevant data to be held in SQL server’s caches and perhaps the underlying storage system’s caches too. Having more data in memory leads to a more performant system.

For some reason the existence of compression in the database as was something that had slipped under my radar, perhaps because it used to be an Enterprise feature but now its available to me in our SQL2016 Standard Edition.


This is particularly interesting to Dynamics GP users as our database is full of padded CHAR data types, has very wide tables full of only partially used data (depending on modules used) or repeating data in the case of settings flags. Dynamics GP also has many tables full of decimal columns that are all zero, again due to configuration or options in how GP is set up or what modules are active. So from the outset it feels like Dynamics GP would benefit.

“Enabling compression only changes the physical storage format of the data that is associated with a data type but not its syntax or semantics”. This means the compression occurs inside the SQL engine but is transparent to the application interacting with SQL server. There are two levels of compression of interest and available to us. ROW compression takes each data row in the table,

  • It uses variable-length storage format for numeric types (for example integer, decimal, and float) and the types that are based on numeric (for example datetime and money).

  • It stores fixed character strings by using variable-length format by not storing the blank characters.

So imagine how much room can be saved when you consider the fields in Dynamics GP are fixed length!

What is more there is another option, PAGE compression that looks at repeating data within the pages of data stored on the filesystem and compresses that data. As this is over an entire page its more heavy on CPU resources but is great where there is a lot of repeated data down the rows of a table. Wait, repeating data down rows of a column? – That is what we get lots of due to status flags and little used fields in the GP tables that vary little from top to bottom of the table.

Just look at something like Item Master table IV00101 or one of the pricing tables etc. There are distributions and settings that are the same, repeated for all items and are ripe for compression as this leads to repeated content in the pages.

Data repeated down from table IV00101 Item Master


So both the nature of the data in the tables and the use of compressible data types by Dynamics GP sure makes it look good for compression.


Compression does cause more CPU load, but unless you are pulling millions of rows then it seems insignificant, see more here:

https://sqlperformance.com/2017/01/sql-performance/compression-effect-on-performance where it is proved it has little effect.


We can run EXEC sp_estimate_data_compression_savings 'dbo', 'IV00101', NULL, NULL, 'PAGE' ;

This will show us, by sampling a subset of the table, much as the statistics does, how much space should be saved by compressing the table, without having to actually do it. Let try with Item Master in Dynamics GP.

SELECT COUNT(*) from IV00101


EXEC sp_estimate_data_compression_savings 'dbo', 'IV00101', NULL, NULL, 'PAGE' ;

Item Master compression test

So we can see the item master table goes from 81,944KB to 16,304KB that is only 20% of what it was!

No trying it with IV00108  that has SELECT COUNT(*) FROM IV00108 = 6,107,169 rows and we get 779,816 going down to 139,440, that is only 19% of what it was before.

Compression testing with IV00108

So you can see how much saving can be achieved this way, imagine the reduced I/O from having 20% of what used to be read.

Even going down to ROW compression gives you only slightly less compression but less overhead too:

2019-03-02_00-31-51

Only 46% of what it was with row level compression.


Downside

There are not many downsides. The first is technical, compressing the data take up CPU, most SQL servers are not CPU bound in terms of resources, so this should not be an issue. Typically 10-30% increase, so check your current CPU load. As this is a table by table selection, you could tackle only the main most sizable tables in GP to get the majority of the benefit without having to apply compression to every table and index. When the data is written you take a hit on compressing it to reap the rewards later. So tables and indexes with great numbers of inserts per second may cause issues (have to be big loads).

The article below has some good scripts to see what will work and what will not…

https://thomaslarock.com/2018/01/when-to-use-row-or-page-compression-in-sql-server/


Upside

Much smaller data means less I/O more in cache. And more data in memory to make for more efficient queries.


Summary

I am going to gradually add tables to compression and see what happens to CPU usage. The benefits should be substantial in terms of reads so it seems well worth pursuing.


Support

This article would indicate its supported for Dynamics GP, although the tool referenced for choosing tables to compress is no longer available, however it is possible to manually work with the database to turn on compression.

https://blogs.msdn.microsoft.com/nav/2011/07/21/sql-server-data-compression-and-microsoft-dynamics/


This is the reason going to conference is so worth while, this is only one of many things I leant or got reinforced today in the various sessions I attended.

Failed to grant permission to execute error and Zen Barcode for SSRS 2016 Reporting Services

Copy assemblies into the correct reporting services folder

When migrating to a new SQL server the barcodes were causing an issue, firstly because we lacked the barcode assemblies in the reporting services bin folder for the new version of Reporting Services that came with SQL server 2016.

Finding the dlls still in the path for the old Reporting Services server service, they were copied to new active location as shown:

Copy the Zen.Barcode.* files into the bin directory of currently active reporting services server

Permissions Error

After moving the required .dll into the correct location for this server, we then got a permission error.

Failed to load expression host assembly. Details: Could not load file or assembly 'Zen.Barcode.SSRS, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b5ae55aa76d2d9de' or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418) (rsErrorLoadingExprHostAssembly)

 

rssrvpolicy.confg

Setting permissions to the assembly for reporting services is a matter of adding some grants to the policy file. Insert the following block after the last </CodeGroup> tag in that file.

"Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config"

<CodeGroup class="UnionCodeGroup" Name="BarcodeControl" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission."> 
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.SSRS.dll"/>
</CodeGroup>
<CodeGroup class="UnionCodeGroup" Name="BarcodeControl2" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission.">
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.Core.dll"/>
</CodeGroup>
<CodeGroup class="UnionCodeGroup" Name="BarcodeControl3" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission.">
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.Design.dll"/>
</CodeGroup>
<CodeGroup class="UnionCodeGroup" Name="BarcodeControl4" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission.">
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.SSRS.Design.dll"/>
</CodeGroup>

 

At this point we were able to start using the barcodes again in reporting services.

 

Reminder on setting up barcode in a report

 

Create references in the Report>>Properties for the .dll files as shown

Zen.Barcode SSRS references

 

In the code tab of the report properties set a function that will return an image binary  that will be used as an expression for the image source in the report.

2019-02-27_09-45-13

 

Drop an image into the report that will be the barcode and set the expression for the image MIME type as shown.
2019-02-27_09-43-33

 

Example of the expression for the image, in this example, using the value of another field as the source for generating the barcode.
2019-02-27_09-43-20

 

If you found this helpful- then do comment, it helps motivate me to document more of these kind of things.

 

Reference: https://www.barcoderesource.com/configurereportingservices.shtml

SQL server Optimize for Ad hoc Workloads setting in server advanced properties window

TL;DR Turn this option on, from false to true

Whilst looking at a problem SQL server instance, I was on the diagnostic journey looking at why the query plan cache was getting totally cleared every few minutes. It turned out the server had bad memory setup and SQL server was suffering some bad memory pressure. However I did learn about a setting that I find hard to think of a reason why you’d not want to use it in a typical (what install is typical tim?) setup. This option Optimize for Ad hoc Workloads tells SQL server to not cache the query plan for a query until it sees it twice.

Server properties window - highlighting Optimize for Ad hoc Workloads setting

 

This is important where the SQL query work load is very varied, particularly where ad-hoc, non-stored procedure queries are being ran as they can bloat the query cache. The query cache is used to store the compiled execution plan required to execute a particular query. When a new query is encountered, the plan is calculated and put in the cache to save having to compute the plan again, if the query is seen again. The problem is that with ad-hoc queries they are unlikely to be seen again, thus SQL server is using up memory unnecessarily that could be used for better things.

I understand, but need to verify, that this is also true of ORMs such as Entity framework, that although it does create parameter based SQL to execute, in the SQL it sends to the server, the length of those parameters can vary depending upon the length of the values in those parameters. Thus this can create a large number of query plans for what are essentially very similar queries. (OK technically the length of the searched text can vary the query plan but run with this).

The setting will greatly reduce the number of plans and memory used for them on the server as only if they the query is seen twice will it be fully cached. The first time it is seen a stub is created that is enough to spot if the query is seen a second time, only then will the query plan be cached. Truely ad-hoc queries wont be seen again and space in the cache is saved. The compile time is not really worth worrying about because having to compile the query twice is no big deal as after the second compile, the further 100,000k executions can come from the cache, so proportionally its an efficiency worth having for a tiny, tiny hit of compiling the query plan twice.

There is a good article here on how start looking into if you have bloat,http://www.sqlservercentral.com/articles/SQLServerCentral/91250/

There is a similar setting of Forced Paramatization for details of this setting see this post by Brent Ozar,  https://www.brentozar.com/archive/2018/03/why-multiple-plans-for-one-query-are-bad/ basically this setting forces the server to look more carefully at the plans and infer where parameters would exist if you were to paramatize the query. This is great for reducing plans in the cache but increases processor work as it has to examine queries a lot more to work out where the parameters lie. It can also lead to bad parameter sniffing as before the plans would have been totally bespoke, now they will be shared and the exact parameter can change the optimal plan.