SQL Tuning

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