APM Database management and troubleshooting tips

	Product(s):	APM Implementation and Performance Management
	Version(s):	All
	Environment:	N\A
	Area:	Database performance
	Subarea:	N\A

Procedure for when DB block is observed:

Run the stored procedure sp_who on the database. This will look at all DB sessions and record what sessions are blocking. Save the results as they will be needed to review sessions on the application server
Using these results, link the user sessions on the app server to the db sessions by using the blocked_by value in the sp_who results.
Investigate what the user was doing to when the block was created and report it to support.

Monitor Database For:

Fragmentation - Particularly keep an eye on the application lock table (oq.app_lock). Rebuild the table when fragmentation is greater than 30%. Review all table fragmentation on a weekly basis and rebuild any index with fragmentation over 30%. Have a formal defrag plan and routine.
Monitor Database Space - Do not rely on SQL Servers auto grow functions. Observe the growth rate of the database and manually grow it based on the growth rate. Auto growth can be turned on after manually growing the DB, but it should be set to very small chunks of growth – 2-4 megs. Default is 10%, better to set a larger size and grow in small increments; however, make sure it is not auto growing all the time.
Monitor the transaction log file size - Make a determination on how big this will grow. Pre-allocate generous the log file size to the size determined, based on transaction space needed. Auto growth can be used if the size to grow is set to 1-2MB.
Review Temp DB files - There should be one temp db file per CPU on the database.

Monitor Database and Application Server for:

Monitor Application Server for :

Purging

Purge records that are no longer needed can be purged using purging rules

eg - processed upload transactions, incorrect readings, processed integration records.