Over the last few months, our team of DBAs has been developing various data collection routines for monitoring (and designing the tables to store said monitoring data). We are planning to remove a 3rd party SQL Server monitoring tool from our environment in the next couple of months, so most of the effort has been on making sure we don’t lose any critical monitoring/analysis functionality once we remove that product. This is all well and good, but in our haste to “get the data collecting”, we found a few problematic issues.
1) If you are going to collect some data set (wait stats, file io stats, etc) ALWAYS ALWAYS ALWAYS implement a deleting process as well. Set a ridiculous retention period (e.g. 10 years) if you don’t want to delete anything, but at least have the process in place to trim aging records off of your monitoring tables. I know it’s the most obvious, but it is often overlooked or deferred with the thought that “we’ll delete when it grows too big”.
2) DON’T FORGET a Clustered Index! In the vast majority of our routines, we execute a DMV query against a series of servers and then write the results to table on a central database server. Nothing fancy there, but I found myself “cloning” routines (copy/paste powershell code, quickly make a table struct with a SELECT INTO, etc.) and not taking the time to define at least a clustered index. In at least 2 cases, we had tables that were heaps (no clustered index) and over time their unused space grew to > 7 times the actual required space to house the rows. This was caused by us deleting aging records from the heap. A heap can’t reuse space allocated to it so it will always grow (unless you copy it to another heap [select into], runt he alter table rebuild command, or add a clustered index).
I added a few clustered indexes to the problem tables and the unused space dropped down to <1MB per table.
I’m sure the above is obvious to most readers, but I am hoping that by admitting to this sloppiness here, it will prevent me from overlooking this basic things in the future.