While performing health checks for customers, I don't hesitate to borrow quality tips and knowledge.
As part of an IMAG health check, I will usually ask a customer's DBA resource to join the customer's IDM team to validate performance and any possible concerns with indexes.
IM Health Checks - Examples - Outline and Processes - Including Outputs (Presentation & Doc)
One of my customers was using MS SQL Server as their IMAG objectstore for both IM and IG solutions.
Using MS SQL Profiler to monitor, the IDM team executed peak use-cases, to try and "push" the I/O to the objectstore. We monitored for any transactions/queries over 10 msec.
I was amazed at the performance that this database provided. One of the reasons offered by the DBA was use of SQL Server Maintenance Scripts/Process that were leveraged from a known SME for SQL Server.
The author of these SQL scripts has abstracted them, so they may be used with any database. Only one variable is needed to be adjusted to execute the primary sql script to build four (4) stored procedures.
There are excellent examples of how these stored procedures may be called within MS SQL GUI or via command line via sqlcmd process (that could be wrapped with MS Win Scheduler or other scheduler tool, e.g. SQL Agent job scheduler).
Main SQL script to build the stored procedures.
Change this single value between the [ ] with your real DB name. Ex. USE [master] -- Specify the database in which the objects will be created.
Note: For IM, this would be one (1) or six (6) database names (depending if the 6 db were split);
For IG, this would be five (5) database names.
I will be adding these scripts as a recommend post-installation performance enhancement for long term run-n-operate lifecycle of the solution.
Have you used these or other DB performance/tuning scripts on a scheduled basis?
See below for pics for IG use of these scripts.