1.1
Indexes
A database index is a data structure that
improves the speed of operations in a table.
Description:
Maintaining indexes is a key factor in achieving
minimum disk I/O for all database queries. The database maintenance command
ALTER INDEX can help in defragmenting indexes in Microsoft SQL Server® and in
rebuilding one or more indexes for a specific table.
Current Scenario:
All indexes are configured as per CRM system out
of box feature. We need to find if there any requirement for the new indexes by
the usage.
Solution:
·
Use SQL Server Profiler to record usage. Analyze
recorded usage in the DBA Dashboard tools.
·
Individual Queries can also be analyzed by the
Database Engine Tuning Advisor.
·
Use SQL profiler traces with the Database Engine
Tuning Advisor.
Tip: For large databases, consider creating indexes
on separate file groups.
1.2
Tempdb
Description:
Isolation of the TEMPDB onto its own storage is
important and could improve performance. If the server is having a lot of
physical memory (64-bits) issues, then one approach that can be used is to move
tempdb to a RAM disk.
Solution:
Moving the tempdb primary data and/or transaction
log file must be performed in two steps: First, alter the database and move the
files; second, restart the SQL Server instance to complete the move. The
primary tempdb data file cannot be moved while the SQL Server instance is
running.
NOTE: tempdb and transaction log files may reside
on the same storage device. Refer to:
·
See
(Section G) for detailed instructions on how to move tempdb.
·
Microsoft SQL Server I/O subsystem requirements
for the tempdb database can be found at http://go.microsoft.com/fwlink/?LinkID=144148
1.3
Disc
Description:
·
Data should be spread across as much I/O capacity
as possible. This is generally done by striping volumes across physical disks
in a SAN configuration.
·
The data and logs for each of the
high-transaction databases should be on their own volumes. It is particularly
important to isolate the transaction logs on their own spindles because they
are written sequentially. Putting them on their own set of disks minimizes
disk-head movement.
Solution:
Transaction-log files creation can be
write-intensive during periods when there is a high volume of data being added,
changed, or removed from the application. For optimal performance, ensure that
database files and transaction-log files are on located on separate sets of
physical disks.
1.4
Statistics
Description:
SQL Server 2008 collects statistics about
individual columns (single-column statistics) or sets of columns (multi-column
statistics). Statistics are used by the query optimizer to estimate the
selectivity of expressions, and thus the size of intermediate and final query
results. Good statistics allow the optimizer to accurately assess the cost of
different query plans, and then choose a high-quality plan. All information
about a single-statistics object is stored in several columns of a single row
in the sysindexes table, and in a statistics binary large object (statblob)
kept in an internal-only table. In addition, information about statistics can
be found in the new metadata views sys.stats and sys.indexes.
Solution:
See the article titled “Statistics Used by the
Query Optimizer in Microsoft SQL Server 2008” at
http://msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx
for more information.
1.5
Transaction Isolation Level
Description:
Using these isolation levels can also require
higher levels of server processing to maintain the row versions in the temp
database. Use server sizing as necessary to offset any impact on performance.
Solution:
READ COMMITTED isolation level with the
READ_COMMITTED_SNAPSHOT:
·
SET TRANSACTION ISOLATION LEVEL (Transact-SQL):
See instructions at http://go.microsoft.com/fwlink/?LinkID=144151.
See instructions at http://go.microsoft.com/fwlink/?LinkID=144151.
·
Adjusting Transaction Isolation Levels:
See instructions at http://go.microsoft.com/fwlink/?LinkID=144152.
See instructions at http://go.microsoft.com/fwlink/?LinkID=144152.
·
Customizing Transaction Isolation Level:
See instructions at http://go.microsoft.com/fwlink/?LinkID=144153.
See instructions at http://go.microsoft.com/fwlink/?LinkID=144153.
This configuration does incur greater physical
resources which can be minimized by moving tempdb to a RAM disk.
1.6
Memory
Description:
Table 2: Memory list and descriptions
Counter
|
Definition
|
Comments
|
|
Memory->Available
MBs
|
Amount
of physical RAM immediately available for allocation for process or system
use.
|
No
specific recommendation, but under steady load, this counter should reach a
steady state and not continue decreasing.
|
|
Memory->Pages/sec
|
The rate
at which memory pages are written to or read from the system-paging file, to
resolve hard-page faults.
|
Excessive
memory pages (greater than~200 per second) indicate a system under memory
pressure.
|
Solution:
It is recommended that the following settings be
configured.
Table 3:
Memory settings
Settings
|
Recommendation
|
Comments
|
Windows
Server® boot.ini: /3GB
|
Enabled
|
It is
recommended that this is set on all SQL servers with 4GB to 16GB of
memory. This setting must be disabled on servers with more than 16GB.
|
Windows
Server boot.ini: /PAE
|
Disabled
|
It is
recommended that this is set on all SQL servers with more than 4GB of memory.
|
SQL
Server: AWE
|
Not Set
|
It is
recommended that this is set on all SQL servers with more than 4GB of memory.
|
To enable all the correct settings, the only change that is required is
the use of /3GB in the boot.ini file. If additional memory is added at a
later date then these settings will need to be changed.
This is
unlikely to have a significant impact initially due to the size of the Dynamics
CRM database. It is, however, expected to have a larger impact as the
database grows and more memory is required.
1.7
Processor
Table 5: Processor description
Counter
|
Definition
|
Actual Data
|
Comments
|
|
Processor->%
Processor Time
All CPUs
|
Percentage
of time that CPUs are executing threads other than the system idle thread.
|
At
levels greater than 80%,
you
should begin considering action.
|
||
System->Processor
Queue Length
|
Number
of threads that are ready to run and are queued up waiting on processor time.
|
A
sustained value greater than 2 usually indicates
a
processor bottleneck.
|
||
System->Context
Switches/sec
|
Number
of times that a processor switches the currently-executing thread.
|
Anything
less than 5,000 context switches per second
per
processor is ok, 5,000 to 15,000 is borderline,
and
anything over 15,000 indicates a bottleneck.
|
1.8
Reports (SSRS)
Report server performance can be affected by a
combination of factors including hardware, number of concurrent users accessing
reports, the amount of data in a report, and output format.
Description:
Rendering of all reports is currently slow. It
has been detected that the reporting service is installed on same server as the
Microsoft Dynamics CRM database.
Solution:
Host the report server and the report server
database on separate computers rather than hosting both on a single high-end
computer:
·
Planning for Scalability and Performance with
Reporting Services:
See instructions at http://go.microsoft.com/fwlink/?LinkID=70650.
See instructions at http://go.microsoft.com/fwlink/?LinkID=70650.
For custom designed reports try to abide by the
following tips:
·
Avoid reporting services group by use SQL Group
by.
·
Build your reports on the filter views.
·
If possible schedule reports to run on none peak
hours.
1.9
Direct queries to database
Description:
Potential source could be integration or any
custom extension.
Solution:
Analyze queries:
·
Designing Partitions to Improve Query
Performance:
See instructions at http://go.microsoft.com/fwlink/?LinkID=144154.
See instructions at http://go.microsoft.com/fwlink/?LinkID=144154.
·
Query database on filters not on the
[CustomEntity]Base table.
·
Use best practices on how to execute queries from
ADO.NET:
See instructions at http://go.microsoft.com/fwlink/?LinkID=144156.
See instructions at http://go.microsoft.com/fwlink/?LinkID=144157.
See instructions at http://go.microsoft.com/fwlink/?LinkID=144156.
See instructions at http://go.microsoft.com/fwlink/?LinkID=144157.
2.1
Server Roles in Application layer
Description:
Map each service in the table. For large
implementations, the recommendation is to split the application layer into a
Dynamics CRM application server and a platform server.
Solution:
Table 6:
Server Roles in Application Layer
Application
Server
|
Platform
Server
|
|
Web
Application Server
|
X
|
|
Help
Content Service
|
X
|
|
SDK
Service
|
X
|
|
Asynchronous
Service
|
X
|
|
Discovery
Service
|
X
|
|
Deployment
Service
|
X
|
ASYNC service is executing all workflows, and
bulk operations such as quick campaign and deletes. Perfmon can be used to monitor each specific item. If the
implementation is large, performance would be better if ASYNC service is
installed on a dedicated server.
Figure
1: Microsoft Dynamics CRM ASYNC Service: Adding Counters to perfmon for Analyze
2.2
Workflows
Description:
Workflows can be monitored by using perfmon and the counter of the
Microsoft Dynamics CRM ASYNC Service.
Figure 2: Microsoft Dynamics CRM ASYNC Service: Adding Counters to perfmon
for Analysis
Solution:
·
Try to limit the scope of workflow.
·
Review business rule for potential loopback
issues.
·
Long-running operations should be done in
asynchronous plug-ins (+1 sec to complete in warm mode).
·
If possible on larger implementations, run the
ASYNC service on a dedicated server.
·
See section Workflows in Optimizing and
Maintaining Performance White Paper for more information: http://go.microsoft.com/fwlink/?LinkID=144158.
2.3
Plug-ins
Description:
If the solution is customized with many plug-ins,
it can potentially slow down the system depending on how the plug-in is
designed to be executed—in a synchronized or a synchronized mode.
Solution:
If it is detected that the plug-in is causing
performance issues, try to disable it or have it redesigned to be executed in a
queued mode:
·
Try to limit the scope of the plug-in.
·
Review the plug-in for potential loopback issues.
·
Long-running operations should be done in
asynchronous plug-ins (+1 sec to complete in warm mode).
·
If possible run the plug-in in ASYNC mode.
A specific recommendation for any custom
application is to limit any columns and rows retrieved to those required to
achieve the application’s business goals. This technique is especially
important when Microsoft Dynamics CRM users access the data from a Wide Area
Network (WAN) with higher network latencies. You can limit the data returned by
custom applications by using Condition attributes to restrict the data that the
FetchXML and ConditionExpressions queries return, and by using paging to
restrict the number of rows returned by a custom application.
2.4
Customizations
Description:
Microsoft Dynamics CRM can easily be extended by
adding custom entities and relationships. Forms, security groups and views can
be customized.
Solution:
·
Remove unused fields from the forms.
·
Remove unused search fields from the views.
·
Remove unused columns from the search views, if
not needed.
·
Remove blank chars from onload, onsave and
onchange.
·
Do not use more security tokens as needed, trim
your security groups.
·
Consider activating <IFRAME> once the user
clicks on the IFRAME.
·
Consider not “over” using the duplicate
functionality.
·
Remove Form Assistant If not necessary
·
Reduce the Number of Columns to retrieve in CRM
Views
2.5
Memory
Description:
Table 7: perfmon memory
Counter
|
Definition
|
Comments
|
Memory->Available
MBs
|
Amount
of physical RAM immediately available for allocation for process or system
use.
64GB
|
No
specific recommendation, but under steady load, this counter should reach a
steady state and not continue decreasing,
|
Memory->Pages/sec
|
The rate
at which memory pages are written to or read from the system-paging file, to
resolve hard-page faults.
|
Excessive
memory pages (greater than~200 per second) indicate a system under memory
pressure.
|
Excessive memory consumption can result from
poorly-managed or unmanaged memory management. To identify this symptom, watch
the following performance counters:
·
Process\Private Bytes
·
.NET CLR Memory\# Bytes in all Heaps
·
Process\Working Set
·
.NET CLR Memory\Large Object Heap size
Look for stable memory consumption.
Figure 3: Perfmon – Stable Memory Consumption
Solution:
Review code or business rules in the following
areas:
·
Plug-ins
·
Workflows
·
Custom extensions
·
Third-party extensions
2.6
Processor
Description:
Table 8: Processor
Counter
|
Definition
|
Comments
|
Processor->%
Processor Time
All CPUs
|
Percentage
of time that CPUs are executing threads other than the system-idle thread.
2.27GHZ
2 processor
96GB
|
Customer
preference, but at greater than 80 percent you should begin considering
action.
|
System->Processor
Queue Length
|
Number
of threads that are ready to run and are queued up waiting on processor time.
24
threads
|
A
sustained value greater than 2 usually indicates a processor bottleneck.
|
System->Context
Switches/sec
|
Number
of times that a processor switches the currently-executing thread.
|
Anything
less than 5,000 context switches per second per processor is ok, 5,000 to
15,000 is borderline, and anything over 15,000 indicates a bottleneck.
|
Solution:
·
Upgrade a processor, if possible.
·
Move ASYNC service to another server.
3.1
Form logic
Description:
During the loading of a form, the Microsoft
Dynamics CRM application can be extended to run specific business rules. If
these rules interact with, for example, a Web service and that Web service
fails, the logic can hold until the error is raised.
Solution:
·
Disable form logic and verify if performance is
better.
·
Try to limit form logic when dealing with high
latency.
·
Can form logic be solved by using a workflow or
plug-ins?
·
Perform validation on the Web Services platform
and third-party extension should be carried out.
3.2
Latency
Description:
Ping time to Microsoft Dynamics CRM application
server.
Solution:
·
Configure Content Expiration.
Content expiration controls the Web objects cache
for the clients accessing Microsoft Dynamics CRM. By default, content
expiration is configured to three days. Any implementation with fairly-static
content or in a WAN environment with slower connections may benefit from
increasing the content expiration value to 15 days. Making this change
configures a client computer running the Microsoft Dynamics CRM Web application
or the Microsoft Dynamics CRM client for Office Outlook to download items into
the temporary Internet files without checking for newer files for a period of
15 days.
Note: To ensure the greatest performance benefit from
this optimization technique, combine it with modifications to the client-side
Web browser settings configuration.
Perform the following steps to configure the
content expiration:
1.
On the Microsoft Dynamics CRM server, in the
Internet Information Services (IIS) Manager administrative tool, right-click Microsoft Dynamics CRM Web Site, and
then click Properties.
2.
In the Properties
dialog box, on the HTTP Headers tab,
specify for the content to expire after 15 days, and then click OK.
Note: This setting change affects client systems when
the current interval expires (in less than 72 hours). Configuring the content
expiration period also impacts the length of time required to complete a Hot
Fix rollout.
3.3
Cache expiration time
Verify the cache expiration time in the client
Internet Explorer setting.
Description:
Client-side browser settings can greatly affect
the user experience over slower connections.
Solution:
Configure temporary Internet files to
“Automatically check for newer versions of pages” and use between 200 and 300
megabytes of disk space for temporary Internet files.
Figure 4: Cache Settings
3.4
Compression technology
Description: Compression techniques designed to optimize
network performance directly affect the size of the data files that are
transmitted over the network. Performance can be improved over networks with
low bandwidth because the size is smaller.
Solution: Verify if any compression technology is used in
the solution.
Note: There are third-party solutions available
to optimize data sent to and from Microsoft Dynamics CRM.
3.5
Bandwidth
Description: The amount/capacity of data sent to and from the
Dynamics CRM application.
Table 9: Bandwidth
Speed
|
Type
|
56 Kbit/s
|
Modem / Dialup
|
1.544 Mbit/s
|
T1
|
10 MBit/s
|
Ethernet
|
11Mbit/s
|
Wireless 802.11b
|
54 Mbit/s
|
Wireless-G 802.11g
|
100 Mbit/s
|
Fast Ethernet
|
300 Mbit/s
|
Wireless-N 802.11n
|
1000Mbit/s
|
Gigabit Ethernet
|
Microsoft Dynamics CRM will perform best over T1
and below.
Microsoft Dynamics CRM Light Mobile client will work with less bandwidth.
Microsoft Dynamics CRM Light Mobile client will work with less bandwidth.
Solution:
·
Access Microsoft Dynamics CRM by using Terminal
Services/Citrix.
Note: The Microsoft Dynamics CRM Office Outlook
offline client does not work over terminal services/Citrix.
4.1 TCP Port limit "Increase Ephemeral TCP Port Limit:
To increase the maximum number of ephemeral TCP ports
1. In the Registry Editor, navigate to the subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Click Parameters, and then, on
the Edit menu, click New.
2.
Create a
registry entry using the following information:
Value Name MaxUserPort
Value Type DWORD
Value data 65534
Valid Range 5000-65534 (decimal)
Default 0x1388 (5000 decimal)
Description Controls the maximum port number that is used when a program requests any available user port from the system. Typically, ephemeral (short-lived) ports are allocated between the values of 1024 and 5000, inclusive.
Value Name MaxUserPort
Value Type DWORD
Value data 65534
Valid Range 5000-65534 (decimal)
Default 0x1388 (5000 decimal)
Description Controls the maximum port number that is used when a program requests any available user port from the system. Typically, ephemeral (short-lived) ports are allocated between the values of 1024 and 5000, inclusive.
3.
Close the Registry Editor, and then restart the
computer to apply the new configuration."
4.2 Time
Period before Closed port becomes availableCreate the TCPTimedWaitDelay key at HKLM\System\CurrentControlSet\Services\TPCIP\Parameters as a REG_DWORD value of 30 decimal.
"Modify the machine.config file to reflect the following best practices:
(manually add this parameter and value to the file)
maxWorkerThreads - 100
maxIoThreads - 100
maxconnection - 12*n (where n is the number of CPUs)
minFreeThreads - 88*n
minLocalRequestFreeThreads - 76*n
minWorkerThreads - 50
4.4
Change the Deletion Service Timing to Off Peak
HoursmaxIoThreads - 100
maxconnection - 12*n (where n is the number of CPUs)
minFreeThreads - 88*n
minLocalRequestFreeThreads - 76*n
minWorkerThreads - 50
Note: This error has been taken care in the Rollup’s. But
if required we can follow this step
4.6
Run CleanUpScript manually in off-Peeck Hours.
This can be used to remove all system job and reduce the
data base size. In Otherway it will increase performance of Workflow as well.
4.7
Server to Server Network Bandwidth
Keep good connectivity in between
servers in case of multi-server deployment
4.8
Don’t use Application Host for any other
application apart from MS CRM4.9 Check Anit-Virus Program running on Client Machine creating some impact on performance
4.10 Switch off Tracing
4.11 Don’t switch on View state or Session State
