Over time the amount of data stored by your Microsoft Dynamics GP system can get quite large. Without buying new hardware, there is a simple way to make your system faster and decrease the space used to store your databases.

This is where a couple of the features of the Database Module of GP Power Tools come in to help. This is article 2 of 3 in the series.

We need to start with a little theory first to explain the problems and then we can see how GP Power Tools solved them.

The Theory – Data Compression

Data Compression was initially added only to SQL Server 2008 Enterprise Edition but was later added to the Standard Edition for SQL Server 2016 SP1.

Row and page compression are primarily used for tables and indexes. Row compression reduces storage by optimizing fixed-length data types, while page compression adds prefix and dictionary compression for further savings. These methods are ideal for reducing I/O in I/O-intensive workloads but require additional CPU resources for compression and decompression. Row compression is lightweight, while page compression offers higher savings but at a higher CPU cost.

Compression reduces disk space and I/O, improving query performance for read-heavy workloads. However, it increases CPU usage, making it unsuitable for CPU-bound systems. By carefully selecting the appropriate compression method, SQL Server users can achieve significant storage savings and performance improvements.

Assuming that your SQL Server is not CPU-bound (if it is then you have bigger issues to resolve), then the performance gains by reading less data from drive storage will be much more than the small CPU performance cost to decompress the data. The overall result will be small storage requirements and faster performance.

The Problem – Data Compression

Microsoft Dynamics GP tables lend themselves to being able to obtain high compression ratios and therefore get better storage and performance improvements.

Dexterity (the development environment GP is built on) stores string fields using the CHAR datatype which is a fixed length datatype padding with spaces, meaning that strings take the space of the maximum number of allowed characters even when nothing is entered into the field. Part of the compression process is to internally store CHAR datatypes as VARCHAR so they only take up the space they are actually using. In a Dynamics GP system, most string fields are only partially filled or are completely empty, changing to VARCHAR storage will greatly reduce the storage requirements.

There are other compression techniques used as well such as looking for patterns and avoiding storage of the repeating data, but it is the change from CHAR to VARCHAR in the GP tables that will get the biggest value from compression.

Note: The length of a string field in SQL will always be an odd number of characters. This is because Dexterity, by default, will always store strings so they use a multiple of 16 bits (2 bytes, 1 word). This is because it was more efficient for a 16-bit processor (like the Intel 80×86 family) to move data around as words rather than bytes. When you include storing one byte for the length of the string, it means that “even” keyable lengths become “odd” storage lengths. For example: A 30 character string in GP will be 30 bytes + 1 length byte = 31 bytes. To make the total length fall on a word boundary, the storage length will be padded with one more character to 31 and so make the total 32 bytes. This can be seen at the SQL level as CHAR(31) even though the field in GP is actually 30 characters.

The Solution – Data Compression

The solution to this issue is an additional feature in GP Power Tools Database Tools Module Database Space Recovery. The tool also has the ability to apply row and/or page compression. Based on read and write statistics (if available from the SQL Server) and table type, Database Space Recovery will recommend either Row or Page compression. You can expand the window size to see other information.

The Database Space Recovery window (in Heap Tables or Clustered Tables mode) will display tables (with records) and allow you to select them for processing. When processed the window will display the space used before and after so you can see how much was compressed. The type of compression can be what is recommended or selected as row or page. You can also remove compression if desired.

Note: Build 31 of GP Power Tools supports Page Compression on Enterprise Editions of SQL Server 2008 (or later), when Build 32 adds support for both Row and/or Page Compression on Standard Editions of SQL Server 2016 SP1 (or later).

More Information

For more information see the following articles

• #GPPT #MSDynGP Making your Dynamics GP system faster and decreasing data space Part 1
• #GPPT #MSDynGP Making your Dynamics GP system faster and decreasing data space Part 2
• #GPPT #MSDynGP Making your Dynamics GP system faster and decreasing data space Part 3
• #GPPT GP Power Tools: What’s in the Database Tools module?
• GP Power Tools Portal: https://winthropdc.com/GPPT

Hope you find this information useful.

David

This article was originally posted on http://www.winthropdc.com/blog.