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 EXEC sp_updatestats daily
Rebuild indexes once per week. Run EXEC z_RebuildAllIndexes
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:

Browser not supported