Part 1 of 2 - Microsoft SQL Server performance boosting settings for Microsoft Dynamics GP.
Disclaimer: this is not a "one-size-fits-all" recipe for improving Microsoft SQL Server performance and you will hear me say numerous times that you must consult with your Microsoft SQL Server administrator to understand how these settings may (or may not) apply to your environment.
In addition, I have recommended (and implemented) some of these changes in different customer environments on a case by case basis, but have never had to make them all at the same time, so please don't take this as a "Mariano said I must make all these changes to have my Microsoft Dynamics GP SQL Server fly" message. To the contrary, this post is intended to present some settings that may contribute to improve SQL Server performance, based on your specific environment. So here is my list:
Minimum memory per query
Increasing the value of this option can improve the performance of queries that use hashing or sorting operations, like those find in SmartList and SmartList Builder. Also, most table auto generated stored procedures -- the stored procedures prefixed with zDP, see David Musgrave's article, What do the zDP_ Auto Generated Stored Procedures do?, for more information-- will benefit from this change if your SQL Server has a lot of memory available and there are many queries running concurrently on the server.
SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default value for this option is 1024 KB.
Network Packet Size
Use the network packet size option to set the packet size (in bytes) used across the entire network. Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. If an application does bulk copy operations, or sends or receives large amounts of text data, a packet size larger than the default may improve efficiency because it results in fewer network reads and writes. If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers.
The default packet size set by Microsoft SQL Server is 4096 bytes. Microsoft does not recommend changing the packet size unless you are certain that it will improve performance.
Default Index Fill Factor
The Default Index Fill Factor option specifies how full each index page will become. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page.
The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed, for example like Master tables), you can set the 'fill factor' option to 100. When the table's data modified very often, for example like MRP tables or SOP and GL transaction tables, you can decrease the 'fill factor' option to 70 percent, for example.
Recovery Interval
The 'Recovery Interval' option specifies the maximum number of minutes per database that SQL Server needs to complete its recovery procedures. The default value of this option is 0. It means that SQL Server will automatically configure this option. SQL Server issues a checkpoint using the 'Recovery Interval' option. Microsoft does not recommend changing this option in general case, but sometimes you can improve performance by changing this option. You can monitor disk-write activity on the data files, and if you see periodic spikes that send disk utilization to 100 percent, you can increase the recovery interval. In this case, Microsoft suggests setting the 'Recovery Interval' option to 5 and continuing monitoring.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/
Disclaimer: this is not a "one-size-fits-all" recipe for improving Microsoft SQL Server performance and you will hear me say numerous times that you must consult with your Microsoft SQL Server administrator to understand how these settings may (or may not) apply to your environment.
In addition, I have recommended (and implemented) some of these changes in different customer environments on a case by case basis, but have never had to make them all at the same time, so please don't take this as a "Mariano said I must make all these changes to have my Microsoft Dynamics GP SQL Server fly" message. To the contrary, this post is intended to present some settings that may contribute to improve SQL Server performance, based on your specific environment. So here is my list:
Minimum memory per query
Increasing the value of this option can improve the performance of queries that use hashing or sorting operations, like those find in SmartList and SmartList Builder. Also, most table auto generated stored procedures -- the stored procedures prefixed with zDP, see David Musgrave's article, What do the zDP_ Auto Generated Stored Procedures do?, for more information-- will benefit from this change if your SQL Server has a lot of memory available and there are many queries running concurrently on the server.
 |
| Server Properties | Memory |
SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default value for this option is 1024 KB.
Use the network packet size option to set the packet size (in bytes) used across the entire network. Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. If an application does bulk copy operations, or sends or receives large amounts of text data, a packet size larger than the default may improve efficiency because it results in fewer network reads and writes. If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers.
 |
| Server Properties | Network Packet Size |
The default packet size set by Microsoft SQL Server is 4096 bytes. Microsoft does not recommend changing the packet size unless you are certain that it will improve performance.
Default Index Fill Factor
The Default Index Fill Factor option specifies how full each index page will become. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page.
 |
| Server Properties | Database Settings |
The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed, for example like Master tables), you can set the 'fill factor' option to 100. When the table's data modified very often, for example like MRP tables or SOP and GL transaction tables, you can decrease the 'fill factor' option to 70 percent, for example.
Recovery Interval
The 'Recovery Interval' option specifies the maximum number of minutes per database that SQL Server needs to complete its recovery procedures. The default value of this option is 0. It means that SQL Server will automatically configure this option. SQL Server issues a checkpoint using the 'Recovery Interval' option. Microsoft does not recommend changing this option in general case, but sometimes you can improve performance by changing this option. You can monitor disk-write activity on the data files, and if you see periodic spikes that send disk utilization to 100 percent, you can increase the recovery interval. In this case, Microsoft suggests setting the 'Recovery Interval' option to 5 and continuing monitoring.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/
