DBA Julian Fletcher discusses some easy ways to improve database performance
As anybody who has worked on projects involving a large amount of in-database processing will know, performance is a top priority. Clients usually consider poor performance to be a bug in much the same way as ‘traditional’ bugs (unhandled errors, getting the numbers wrong, things simply not working, etc). So our product and custom development release cycles include tasks to ensure we get good performance out of new features and continually review existing code to find ways of improving performance.
Recently, a couple of simple approaches have identified various ‘slow bits’ and enabled us to make some reasonably significant performance improvements.
See what indexes SQL suggests
sys.dm_db_missing_index_details can be used to see what indexes SQL thinks would be helpful. The results obviously need to be taken with a pinch of salt but sometimes they highlight tables needing a bit of index care. To help with one of our projects, we wrote a stored procedure using sys.dm_db_missing_index_details along with sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats to give the following ouput:
So simply by running a bit of code and then seeing what SQL is suggesting, improvements to indexes can be made. The final two columns give an indication of the improvements which might result from each index.
See what’s been loaded into memory
Here, sys.allocation_units will tell you what’s currently in memory. As before, we used this as the basis for a stored procedure to return the information of interest or concern:
If a primary key (indicated by the prefix “PK”) has been fully loaded into memory (as indicated by the final column), this suggests that an index was missing. As a consequence, SQL had to revert to the table’s PK in order to get the rows it needed. This should generally be avoided – by using SQL’s index suggestions if appropriate. You just need to clear SQL’s data cache, run the code you want to assess and then see what’s in memory.