MDGP WhitePaper Performance
MDGP WhitePaper Performance
MDGP WhitePaper Performance
Resources .......................................................................................................................... 36
Partners:
https://mbs.microsoft.com/partnersource/northamerica/support
If you would like assistance applying the recommendations to your system or would like assistance with
continued performance issues following the review of the white paper, please contact your Partner or e-
mail [email protected] to arrange for an advisory services engagement.
GP 2010
Customers:
https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-
requirements/MDGP2010_System_Requirements
Partners:
https://mbs.microsoft.com/partnersource/northamerica/deployment/documentation/system-
requirements/MDGP2010_System_Requirements
GP 2013
Customers:
https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-
requirements/MDGP2013_System_Requirements
Partners:
https://mbs.microsoft.com/partnersource/northamerica/deployment/documentation/system-
requirements/MDGP2013_System_Requirements
2. Windows Updates
Install all required and recommended Windows Updates on the server operating system.
The article below explains one of the performance issues addressed with a hotfix that is included
in Service Pack 1.
http://blogs.msdn.com/b/repltalk/archive/2010/10/27/unexplained-slowness-in-sql-2008-on-
windows-2008-r2.aspx
4. Hyper-threading
Hyper-threading is a technology used to improve parallelization of computations (doing multiple
tasks at once) performed on a machines processors. It is generally recommended to turn hyper-
threading off in the BIOS of the operating system. While there may be times when enabling
hyper-threading can actually aid in performance there have been cases where it has significantly
http://support.microsoft.com/kb/322385
"The performance of hyper-threaded environments varies. For example, applications that cause
high levels of contention can cause decreased performance in a hyper-threaded
environment."
http://technet.microsoft.com/en-us/magazine/2007.10.sqlcpu.aspx
"On SQL Server systems, the DBMS actually handles its own extremely efficient queuing and
threading to the OS, so hyper-threading only serves to overload the physical CPUs on systems
with already high CPU utilization. When SQL Server queues multiple requests to perform work on
multiple schedulers, the OS has to actually switch the context of the threads back and forth on the
physical processors to satisfy the requests that are being made even if the two logical processors
are sitting on top of the same physical processor. If you are seeing Context Switches/sec
higher than 5000 per physical processor you should strongly consider turning off hyper-
threading on your system and retesting performance."
Refer to the article below for more information regarding the benefits of an x64 environment:
http://blogs.msdn.com/b/mssqlisv/archive/2007/04/30/will-64-bit-increase-the-performance-of-
my-sql-server-application.aspx
If you are using a 32bit operating system with SQL Server you can optimize the amount of
memory available to SQL Server via the Physical Address Extension (PAE), /3GB switch, or the
Addressing Windowing Extensions (AWE) options. Refer to the chart below for recommendations
based on the amount of memory available on a 32bit server. The information below does not
apply to a x64 operating system.
The optimal cutoff for the /3GB switch can vary between 8 GB and 16 GB thus the 12 GB is
generally a good rule of thumb. Refer to the following article for more information:
http://blogs.msdn.com/b/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx
Refer to the articles below for more information regarding enabling the options above:
6. Anti-Virus Software
The following exclusions must be configured if anti-virus software is running on the SQL Server:
Exclude the location where the database files and log files are stored from the anti-virus
software scan.
Configure exceptions to exclude *.ldf, *.mdf, *.ndf and SQL Backup files from the anti-
virus software scan.
Some anti-virus software may have scanning within the tcp/ip stack, if enabled performance may
degrade. Contact your Anti-Virus Software Vendor for more information regarding this option.
7. BIOS
Install the latest BIOS update available for the server operating system from your Hardware
Vendor.
GP 2013
Customers:
https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-
requirements/MDGP2013_System_Requirements
Partners:
https://mbs.microsoft.com/partnersource/northamerica/deployment/documentation/system-
requirements/MDGP2013_System_Requirements
Refer to the article below for the SQL Server Storage Top 10 Best Practices
http://msdn.microsoft.com/en-us/library/cc966534.aspx
3. Latency
Latency from the disk subsystem is one of the most common causes for performance
issues. Proper placement of the database files across correctly sized RAID volumes will ensure a
solid base to troubleshoot against. The following disk latency counters provide the best evidence
of how well the disk subsystem is handling the requests from SQL Server.
http://technet.microsoft.com/en-us/library/cc966412.aspx
“On well-tuned I/O subsystems, ideal values would be:
1–5 ms for Log (ideally 1 ms on arrays with cache)
4–20 ms for Data on OLTP systems (ideally 10 ms or less)”
While the amount of memory can mask the disk I/O latency it cannot completely conceal it, as
there will always be a point in time when SQL Server will need to save/access data from the disks.
When the Average Disk Sec/Write and Average Disk Sec/Read values are high the focus should be
on the following:
Reducing the I/O load through tuning queries (assuming it is high and thus causing
the latency).
Make the appropriate changes to the disk subsystem to handle the I/O load.
Verify the latency makes sense for the volume of I/Os being requested. When the
I/O volume is much lower than what the disks can handle and high latency is
occurring, then further investigation of hardware, firmware, or potentially even
operating system patches is warranted since the physical disks themselves cannot be
the bottleneck, rather something between SQL Server and the physical disk.
5. Disk Controller
Install the latest firmware/driver for the disk/host bus adapters (HBA) controllers. Contact your
Hardware Vendor for assistance if needed.
10
3. Backup Strategy
Implementing a backup strategy is essential for disaster recovery and to protect critical data in
SQL Server. Database backups can be scheduled and configured through a database maintenance
plan. Plan your backup strategy based on the transaction volume and how much down time you
can afford if a disaster should occur. Refer to the article below for disaster recovery options:
Description of disaster recovery options for Microsoft SQL Server
http://support.microsoft.com/kb/822400
PartnerSource:
https://mbs.microsoft.com/partnersource/northamerica/deployment/documentation/how-to-
articles/MSD_GPRecommendedMaintenanceSQLDatabases
5. Priority Boost
The "Boost SQL Server Priority" option in the SQL Server Management Studio SQL Server
Properties window should not be enabled. If enabled, this option can starve other processes
including basic kernel I/O activity, thus defeating the purpose of trying to enhance SQL Server
performance.
http://support.microsoft.com/kb/319942
“Based on actual support experience, you do not need to use priority boost for good
performance. If you do use priority boost, it can interfere with smooth server functioning
under some conditions and you should not use it except under very unusual circumstances.
For example, Microsoft Product Support Services might use priority boost when they investigate a
performance issue.”
11
SQL Max Degree of Parallelism (General Recommendation for Microsoft Dynamics GP)
Summary of reasons behind the recommendation to set Max Degree of Parallelism = 1:
Microsoft Dynamics GP is an online transaction processing (OLTP) application as such we
want SQL Server to balance the concurrency workload.
All users are less affected by sporadic high cost queries.
In rare cases a parallel query can run very slow compared to the execution plan with 1
CPU.
Deadlock situations may occur for a single SPID across multiple ECIDs (CPUs).
Execution plan analysis is simplified.
http://technet.microsoft.com/en-us/library/cc966401.aspx
“Given the high volumes of OLTP, parallel queries usually reduce OLTP throughput and should
be avoided.”
http://technet.microsoft.com/en-us/library/cc966540.aspx
“Running with a parallel plan is not inherently bad and should provide the fastest response time
for that query. However, the response time for a given query must be weighed against the overall
throughput and responsiveness of the rest of the queries on the system. Parallel queries are
generally best suited to batch processing and decision support workloads and might not be
desirable in a transaction processing environment.”
http://support.microsoft.com/kb/329204
“Note The MAXDOP option does not limit the number of processors that SQL Server uses. Use the
affinity mask configuration option to configure the number of processors that SQL Server uses.
Use the following guidelines when you configure the MAXDOP value:
For servers that use more than eight processors, use the following configuration:
MAXDOP=8.
For servers that have eight or less processors, use the following configuration where N
equals the number of processors: MAXDOP=0 to N.
12
7. Update Statistics
Update statistics on a table or indexed view is crucial to ensure the query optimizer for SQL Server
uses the most up to date information for the query optimization process.
To ensure the statistics are up to date, it is recommended to keep the Auto Create Statistics and
Auto Update Statistics database setting enabled for all Microsoft Dynamics GP databases in the
SQL Server Management Studio Database Properties Options window. These options by default
are enabled.
8. Blocking
Blocking occurs when one connection to SQL Server locks one or more records, and a second
connection to SQL Server requires a conflicting lock type on the record or records locked by the
first connection. This causes the second connection to wait until the first connection releases its
locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go
away. Short duration blocking is normal for database consistency. However, if one process is
holding long duration locks, it has the potential to block several other processes/users for
extended periods of time. Long duration blocking should be investigated and resolved if
possible. Refer to the article below for more information regarding blocking:
Understanding and resolving SQL Server blocking problems
http://support.microsoft.com/kb/224453/EN-US
9. Deadlocking
A deadlock occurs when two or more tasks permanently block each other by each task having a
lock on a resource which the other tasks are trying to lock. At which point, SQL Server will choose
to terminate one of the blocking processes. Deadlocking is more prevalent in a poorly
performing environment due to longer transaction completion time. Refer to the articles below
for more information regarding deadlocking:
Deadlock Explanation
http://msdn.microsoft.com/en-us/library/ms177433.aspx
Detecting and Ending Deadlocks
http://msdn.microsoft.com/en-us/library/ms178104.aspx
11. Indexes
Default indexes on Microsoft Dynamics GP tables should not be changed or removed. Careful
planning is necessary when implementing new indexes to avoid performance decreases. Adding
13
Network Considerations
1. TCP Chimney
It is recommended to disable TCP Chimney at all servers and workstations for best network
performance. TCP Chimney must be disabled in the following areas:
Disable at the operating system level
Disable various Offload options at the network interface card (NIC) level
TCP Chimney is designed to offload certain tasks that the CPU typically would handle to the NIC
card. Having these settings enabled may cause database connections to be dropped in turn
causing various communications errors, such as "DBMS 12" or "## Object Does Not Exist" in
Microsoft Dynamics GP. It can also show up as performance issues due to network delays such as
noted in the following blog article:
http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-
possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx
“We’ve also identified situations where TCP Chimney has impacted transaction throughput and
caused delays between when a statement has been completed by the SQL engine and the
time to receive the begin event of the next statement. This impact can be significant
especially in application workloads that have throughput requirements to execute a series of
statements within a certain time boundary.”
14
2. Network Adapter
It is recommended to configure a single dedicated network adapter card for the SQL Server.
Ensure the network adapter firmware/driver is up-to-date.
GP 2010
Customers:
https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-
requirements/MDGP2010_System_Requirements
Partners:
https://mbs.microsoft.com/partnersource/northamerica/deployment/documentation/system-
requirements/MDGP2010_System_Requirements
GP 2013
Customers:
https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-
requirements/MDGP2013_System_Requirements
Partners:
https://mbs.microsoft.com/partnersource/northamerica/deployment/documentation/system-
requirements/MDGP2013_System_Requirements
15
3. Anti-Virus Software
The following exclusions must be configured if anti-virus software is running on the client
workstations or the Terminal Server:
Configure exceptions to exclude *.tmp, *.idx, *.dat, TNT*.* files in the user’s profile temp
folder from the anti-virus scan.
In some anti-virus programs, an exception for the Dynamics.exe process may be
necessary.
4. RemoteApp
If using the RemoteApp feature with Windows Server 2008, the latest operating system updates
must be installed to ensure Microsoft Dynamics GP will function properly with RemoteApp.
6. Terminal Server
Do not limit the amount of time that active, disconnected, and idle (without user input) sessions
remain on the server. It is important to leave any active Microsoft Dynamics GP clients running
remotely intact. Data corruption can occur if Microsoft Dynamics GP is abruptly shut down as
several windows have code on the window close event to complete data processing. Refer to the
article below for more information regarding Terminal Server timeout and reconnection settings:
http://technet.microsoft.com/en-us/library/cc754272(WS.10).aspx
7. BIOS
Install the latest BIOS update available for the operating system from your Hardware Vendor.
Network Considerations
1. TCP Chimney
It is recommended to disable TCP Chimney at all servers and workstations for best network
performance. TCP Chimney must be disabled in the following areas:
Disable at the operating system level
Disable various Offload options at the NIC level
TCP Chimney is designed to offload certain tasks that the CPU typically would handle to the NIC
card. Having these settings enabled may cause database connections to be dropped in turn
16
http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-
possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx
“We’ve also identified situations where TCP Chimney has impacted transaction throughput and
caused delays between when a statement has been completed by the SQL engine and the
time to receive the begin event of the next statement. This impact can be significant
especially in application workloads that have throughput requirements to execute a series of
statements within a certain time boundary.”
How to Disable TCP Chimney in Windows 2003 Server and Windows Vista
http://support.microsoft.com/kb/942861
1. Service Packs
It is recommended to install the latest service pack, hotfix, or compliance update for Microsoft
Dynamics GP to ensure you have the most recent performance fixes. Refer to the links below to
download the latest patch releases for Microsoft Dynamics GP:
GP 2010
Customers
https://mbs.microsoft.com/customersource/northamerica/gp/downloads/MDGP2010_PatchReleas
es
Partners
https://mbs.microsoft.com/partnersource/northamerica/deployment/downloads/hot-
fixes/MDGP2010_PatchReleases
17
3. ODBC Configuration
It is recommended to use the ODBC driver that correlates to the SQL Server version used with the
Microsoft Dynamics GP databases. Using the same version will take advantage of potential
enhancements and ensure stability. The SQL Native Client driver correlates to Microsoft SQL
Server 2005 and the SQL Server Native Client 10.0 driver correlates to Microsoft SQL Server 2008
or Microsoft SQL Server 2008 R2. Refer to the links below to download the appropriate ODBC
driver:
SQL Server Native Client (SQL Server 2005)
http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=24793
SQL Server Native Client 10.0 (SQL Server 2008 or SQL Server 2008 R2)
http://www.microsoft.com/download/en/details.aspx?id=16177
Refer to the article below for the steps to configure an ODBC DSN for use with Microsoft
Dynamics GP:
Customers:
https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/how-to-
articles/MDGP_HOWTO_SETUP_ODBC
18
OLE Path
If the OLE Path is placed on a shared location and the network share is on a slow connection or
the network share path is non-existent, users may encounter performance issues opening
windows and retrieving records if an OLE note exists for that window or record. This is due to
Microsoft Dynamics GP trying to retrieve the OLE note information from the OLEPath= path
specified in the Dex.ini.
Login Performance
There are many areas to take into consideration when the login to Microsoft Dynamics GP is performing
slowly.
1. Reminders
All custom reminders in Microsoft Dynamics GP are based on SmartList queries. If the SmartList
query that the reminder is based on runs slow in SmartList, the query will also run slow upon login
into Microsoft Dynamics GP. If the user would like to improve login performance, they can
remove unnecessary or slow running custom Reminders. In Microsoft Dynamics GP, click
Microsoft Dynamics GP, click User Preferences and then click Reminders. Remove the reminders
from the Custom Reminders area.
2. Internet Access
In certain environments, revoking access to the Internet may be a security policy requirement.
When launching Microsoft Dynamics GP, there are calls to view certain sites on the Internet. If the
Internet is unavailable, the login into Microsoft Dynamics GP may appear to be slow.
19
4. Shortcuts
Verify all external shortcuts on the Shortcut bar in Microsoft Dynamics GP are valid links. At login,
Microsoft Dynamics GP will validate the external links and if the links are not valid the login may
slow down.
5. Metrics
Metrics that have slow performing queries may increase login times. If the user would like to
improve login performance, they can remove unnecessary or slow running Metrics. In Microsoft
Dynamics GP, click the Change Details icon on the Metrics Title Bar. Remove the Metrics from the
Metrics to Display area.
Reporting Performance
1. SQL Server Reporting Services
Utilize SQL Server Reporting Services reports if possible. The logic is SQL based and reports are
generally faster than Dexterity based Report Writer reports.
3. Offloading Reporting
For complex reporting needs, running reports from a separate SQL server is a good practice. This
eliminates the reporting load on the production database server. Several options are available to
configure the SQL Server databases on a reporting server. Refer to the article below for
requirements for Microsoft Dynamics GP:
20
Posting Performance
1. PJOURNAL jobs
The PJOURNAL table captures records as posting occurs in Microsoft Dynamics GP. This table can
grow to be very large depending on posting volume. The Microsoft Dynamics GP installation
creates a SQL Server Agent Job to truncate the PJOURNAL table in each company. Verify the SQL
Server Agent service is running in the SQL Server Management Studio. Expand SQL Server Agent
in the SQL Server Management Studio and verify the "Remove Posted PJOURNALs From All
Companies" job exists and is enabled under the Jobs folder. By default, the table is cleared every
30 minutes. If the "Remove Posted PJOURNALs From All Companies" job does not exist, execute
the PJJOB.SQL script in the SQL Server Management Studio to create the jobs. The PJJOB.SQL
script can be found in the Microsoft Dynamics\GP\SQL\Util folder.
SmartList Performance
1. SmartList Usage
SmartList and SmartList Builder are intended to be used as a query tool only. If you decide to
increase the maximum record count, be aware that query processing will be slow and you may
experience errors. Therefore, we recommend that you use the default record count limit of 1,000
when you execute queries.
21
22
Other Performance
1. Process Servers
If the process server configuration is defined with a non-existent process server machine, sporadic
delays may occur every 5-10 minutes due to the handshake with process server. Click Microsoft
Dynamics GP, click Tools, click Setup, click System and then click Process Servers. Click Server and
verify all process servers are valid. Click Services and verify all process servers are valid.
Once changes have been made, run PA Recreate Periodic with the Option: Delete Periodic
Records Outside Fiscal Years to remove those stranded periodic records that you no longer need.
We would also recommend that your fiscal years be consecutive. For more information, see KB
article 858768. https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-
US;858768
23
b. If you experience delays when creating Purchase Orders through PO Generator, and you are
using a 3rd party product from Trinity Myridas called Advanced Inventory Replenishment,
verify that the version of that code is compatible with your version of Microsoft Dynamics
GP.
c. If you experience a delay when you print the Received/Not Invoiced report consider moving
Closed and Cancelled POs from Work to History. This will reduce the number of records to
be reviewed in the POP10500 table when the report is generated.
To move POs to history, first make sure you are keeping history by marking the Maintain
History: Purchase Order checkbox in Purchase Order Processing Setup. Then use the
Remove Completed Purchase Orders routine to move as many POs as possible to history.
To view this historical POs, make sure to mark "Historical Purchase Orders" in the Purchase
Order Processing Document Inquiry and Purchase Order Processing Item Inquiry windows.
3. Inventory Performance
Be aware that if you override the quantities of your items in inventory that you will experience
performance degradation when you run Reconcile Quantities in Inventory. This is due to the fact
that each override has at least 2 records (one in the IV10200 and another in the IV10201) that
need to be verified, instead of just the one.
The Field Service reconcile has been added to the core Inventory reconcile starting with GP 10
SP2. If you are using Field Service, you can expect the IV Reconcile to take as long as running the
separate IV Reconcile and the Field Service Reconcile did previously.
Also unless you have recently made changes to the decimal places of your items, there is no
benefit in marking the Include Item History checkbox in the Reconcile Inventory Quantities
window. Leaving this box marked can slow down the reconcile process.
24
Analysis Tools
Analysis Tools for Troubleshooting Performance
Additional Performance Overhead When Running the SQL Server Profile Trace Using a Client-Side
Trace
http://support.microsoft.com/kb/929728
2. Performance Monitor
Use Performance Monitor to monitor the utilization of system resources. Collect and view real-
time performance data in the form of counters, for server resources such as processor and
memory use, and for many Microsoft SQL Server resources such as locks and transactions.
Performance and Reliability Monitoring Step-by-Step Guide for Windows Server 2008
http://technet.microsoft.com/en-us/library/cc771692(WS.10).aspx
25
4. SQL Diag
The SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console
application or as a service. You can use SQLdiag to collect logs and data files from SQL Server and
other types of servers, and use it to monitor your servers over time or troubleshoot specific
problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information
gathering for Microsoft Customer Support Services.
SQLdiag Utility
http://msdn.microsoft.com/en-us/library/ms162833.aspx
5. Process Monitor
Process Monitor is an advanced monitoring tool for Windows that shows real-time file system,
Registry and process/thread activity. It combines the features of two legacy Sysinternals utilities,
Filemon and Regmon, and adds an extensive list of enhancements including rich and non-
destructive filtering, comprehensive event properties such session IDs and user names, reliable
process information, full thread stacks with integrated symbol support for each operation,
simultaneous logging to a file, and much more.
Process Monitor v2.96
http://technet.microsoft.com/en-us/sysinternals/bb896645
26
8. Network Monitor
Network Monitor is a protocol analyzer that allows the capture of network traffic and the ability to
view and analyze it.
Microsoft Network Monitor 3.4
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4865
1. Blocking
Performance Analyzer Version 1.01 for Microsoft Dynamics
SQL Server Profile Trace with Blocked Process Report
2. Deadlocking
SQL Server Profile Trace including the following events:
o Lock: Deadlock Graph
o Lock: Deadlock
o Lock: Deadlock Chain
Troubleshooting Deadlocks in SQL Server
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20D
eadlocking%20in%20SQL%20Server&referringTitle=Home
How to troubleshoot Deadlock in SQL Server 2005
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20D
eadlocking%20in%20SQL%20Server%202005%20using%20Profiler&referringTitle=Home
Analyzing Deadlocks in SQL Server 2008 R2 with SQL Server Profiler
http://msdn.microsoft.com/en-us/library/ms188246.aspx
27
4. Hardware Bottlenecks
Performance Monitor
PAL
5. Networking
Netmon
Microsoft Dynamics GP Client Logging Tools
6. External Influences
Process Monitor
Common Bottlenecks
The following table lists the common bottlenecks to investigate using the Performance Monitor tool.
Monitor the objects below and ensure your environment is at the recommended thresholds as indicated
below.
If you have questions regarding the bottleneck information below or would like assistance in reviewing
Performance Monitor, please contact your Partner or e-mail [email protected] to arrange for an
advisory services engagement
Note: If the disk has say 20 disks and it is RAID 10 then no.
of spindles = 20/2 = 10. If it is RAID 5 then the no. of
spindles = no of disks = 20.
Physical Disk Current Disk Queue > 2 * number Current Disk Read Queue Length is the current number of
Length of spindles requests that were queued for the selected disk during the
sample interval.
More Info:
< (2+ no of spindles) Excellent
28
Note: If the disk has say 20 disks and it is RAID 10 then no.
of spindles = 20/2 = 10. If it is RAID 5 then the no. of
spindles = no of disks = 20.
Physical Disk Avg. Disk > 10-20ms Avg. Disk sec/Transfer is the average time, in seconds, of a
Sec/Transfer read or write of data from the disk. (Latency)
Physical Disk Avg. Disk Sec/Read > 10-20ms Avg. Disk sec/Read is the average time, in seconds, of a
read of data from the disk. (Latency)
Physical Disk Avg. Disk Sec/Write > 10-20ms Avg. Disk sec/Write is the average time, in seconds, of a
write of data to the disk. (Latency)
29
30
Memory
Memory Available Mbytes <100-300 MB Amount of Free Memory (less than 10 MB would be
reaching critical stage where crash of OS may occur)
Memory Pages/Sec >100 Rate at which pages are written to disk to resolve hard
page faults
MSSQL : Buffer cache hit ratio <90% Percentage of pages found in the buffer pool without
Buffer Manager having to read from physical disk
MSSQL : Free Pages <640 Total number of Free pages
Buffer Manager FYI - 640 pages ~ 5 Meg
MSSQL : Memory Grants =0 Number of processes waiting for a memory grant
Memory Manager Pending
MSSQL : Target Server ~Physical Total amount of dynamic memory the server is willing to
Memory Manager Memory Memory consume
MSSQL : Total Server Memory ~Target Total amount of dynamic memory the server is currently
Memory Manager Server consuming
Memory
31
Miscellaneous
SQLServer : Full Scans/sec >1 If we see high CPU then we need to investigate this counter,
Access Methods otherwise if the full scans are on small tables we can ignore
this counter. Values greater than 1 or 2 indicate that we are
having table / Index page scans. We need to analyze how
this can be avoided.
SQLServer : User Connections The number of users currently connected to the SQL Server.
General Statistics Note - Gradual increase might indicate connections are not
being closed properly
SQL Server : Batch Requests/sec Rough estimate on number of Batch requests / sec. Good
SQL Statistics for trend data
SQL Server : Locks Number of =0 The number of lock requests that resulted in a deadlock.
Deadlocks/sec
SQL Server : Locks Average Wait Time > 200-500 This is the average wait time in milliseconds to acquire a
(ms) lock. Lower the value the better it is. If the value goes
higher then 500, there may be blocking going on; we need
to run blocker script to identify blocking.
SQL Server : Locks Lock Timeouts/sec Number of locks requests that timed out
SQL Server : Latches Total Latch Wait Total latch wait time for latch requests that had to wait in
Time(ms) the last second
32
If you have questions regarding the scenarios below, please contact your Partner or e-mail
[email protected] to arrange for an advisory services engagement.
Troubleshooting Techniques:
Capture and review long duration blocking information using the SQL Server Profile Trace or the
Performance Analyzer for Microsoft Dynamics to determine lead blocker.
o Identify the application/code calling the lead blocker statement.
o From a development perspective, the following are ways to reduce long duration
blocking:
Attempt to use shorter TRAN logic if possible
Ensure statements inside the TRAN are tuned appropriately
Potentially use (nolock) hint on reporting to minimize report blocking other
processes
Capture and review Performance Monitor counters to validate sufficient hardware.
o Focus on disk latency
o Validate CPUs are not saturated
o Check for network issues
Capture and review long running queries using the SQL Server Profile Trace.
o If Disk latency is high and I/O is high attempt to tune high cost queries
Issue 2 - Random performance issues that affect various users/processes at various times
Possible Cause: This issue is most likely related to hardware infrastructure issue, however, also
recommend looking into a potential blocking issue.
Troubleshooting Techniques:
Verify the latest hardware, operating system, SQL Server and Microsoft Dynamics GP patches are
installed.
Verify anti-virus software has the required exclusions configured as recommended in this
document.
Capture and review Performance Monitor counters for potential bottlenecks and to validate
sufficient hardware
o Focus on Disk latency
o Validate CPUs are not saturated
o Check for network issues
o Check for antivirus interference
33
Issue 3 - SQL Server Profile Trace shows single insert statements taking 50 - 500ms
Possible Cause: This issue can usually be attributed to high latency on the disks where the *.LDF files are
located. Generally this is seen when the *.LDF files are not on a dedicated RAID volume, rather on the
same volume as the *.MDF file. Thus when *.MDF files have heavy read I/O and the drives are already
saturated the *.LDF files are not written to as quickly as they should.
Troubleshooting Techniques:
Capture and review Performance Monitor counters to validate sufficient hardware
o Focus on Disk latency
o If high latency and low IO activity
Check if shared RAID group on SAN
Ensure disk controllers have the latest firmware / drivers
Verify anti-virus software has the required exclusions configured as recommended in this
document.
Isolate the *.LDF files to a dedicated RAID group.
Issue 4 - SQL Server Profile Trace shows simple statements taking 500ms or higher
Possible Cause: This issue can usually be attributed to long duration blocking.
Troubleshooting Techniques:
Verify the latest hardware, operating system and SQL Server patches are installed.
Verify anti-virus software has the required exclusions configured as recommended in this
document.
Verify TCP Chimney is disabled and other network considerations as recommended in this
document.
Capture long duration blocking information using SQL Server Profile Trace or the Performance
Analyzer for Microsoft Dynamics to determine the lead blocker.
o Identify the application/code calling the lead blocker statement.
o From a development perspective, the following are ways to reduce long duration
blocking:
Attempt to use shorter TRAN logic if possible
Ensure statements inside the TRAN are tuned appropriately
Potentially use (nolock) hint on reporting to minimize report blocking other
processes
34
Troubleshooting Techniques: When a specific process is consistently slow, recreate the issue while
capturing performance logs. Review the logs and tune if possible.
Capture detailed SQL Server Profile Trace
Capture Dexterity Script.log and Profile.txt
Screenshots and steps detailing the issue recreation process.
Troubleshooting Techniques:
Capture long duration blocking information using SQL Server Profile Trace or the Performance
Analyzer for Microsoft Dynamics to determine the lead blocker.
o Identify the application/code calling the lead blocker statement.
o From a development perspective, the following are ways to reduce long duration
blocking:
Attempt to use shorter TRAN logic if possible
Ensure statements inside the TRAN are tuned appropriately
Potentially use (nolock) hint on reporting to minimize report blocking other
processes
Capture long running queries using the SQL Server Profile Trace.
o If disk latency is high and I/O is high attempt to tune high cost queries
Capture and review Performance Monitor counters for bottlenecks
35
Partners:
https://mbs.microsoft.com/partnersource/northamerica/deployment/documentation/white-
papers/gp2010_architecturewhitepaper
4. Refer to the article below for the SQL Server Storage Top 10 Best Practices
http://msdn.microsoft.com/en-us/library/cc966534.aspx
5. Trace flag 4199 is added to control multiple query optimizer changes previously made under
multiple trace flags
http://support.microsoft.com/kb/974006/en-US
(c)2011 Microsoft Corporation. All rights reserved. This document is provided "as-is." Information and
views expressed in this document, including URL and other Internet Web site references, may change
without notice. You bear the risk of using it.
36
37