Over time SQL performance of the EmpowerID database can degrade due to the volatile nature of the inventory and RBAC data it contains. SQL Server continuously attempts to calculate the optimal execution plan for stored procedures based upon the estimated amount of data that will be returned. If you notice that the EmpowerID Identity Warehouse is running slow, you can improve performance by executing sp_updatestats. sp_updatestats is a stored procedure provided by SQL Server that recalculates optimal execution plans for stored procedures on a table or indexed view and ensures that queries compile with up-to-date statistics.
To optimize SQL performance
- Perform a full backup once per day
- T-Log backups once every 1-4 hours, depending on the level of usage
- Run sp_updatestats daily
- Rebuild indexes once per week
- Perform consistency checks once a week
Microsoft best practices should be followed for configuring the SQL Server "Maximum server memory" Server Property to allow Windows and other applications sufficient memory to run without paging to disk. For further information please see the following Microsoft articles: