In an article on SQL Server, a DBA/developer attempted to solve the challenge of finding unused tables in SQL Server and while I applaud this author's attempt at trying to do this, we have to consider that this may be as straight forward as we think. In the least, this effort may involve significant coordination with other teams unless we're absolutely sure that all teams follow a standard set of practices. In addition, we want to consider how table usage might differ in an OLTP/OLAP/Hybrid environment. This could mean that some of our assumptions are completely incorrect. The author did good analysis, but I would be careful about jumping to conclusions and eliminating tables that are actually important in your environment.
Some questions that are answered in the video:
- How have we designed our testing to validate whether this is possible - this is by far the most important question to ask?
- Can we assume that our testing isn't out of date? How often do we update and validate that our testing involves using what we need?
- What are some concerns about using creation and modified dates as an indicator that we can remove a table? This also applies to objects.
- What are some concerns about using row counts as an indicator that we can remove a table?
- What are some concerns about using index reads (and usage) as an indicator that we can remove a table?
- What are some concerns about querying the code for object references as an indicator that we can remove a table?
- What might be an issue if we have an environment where some developers are using entity framework while other developers are using procedures when it comes to identifying unused tables?
- What should we note about the size of tables and how we should proceed with removing unused tables?
The cost of making these assumptions could be huge. The alternative cost? Storing possibly unused data may not be as big. We may want to do as much analysis and discussion as possible before we start removing objects that have data. In the least, we want to know ahead of time who may be possibly impacted by removing data.
For mastering data transformation from one form to another form, check out the highest-rated Automating ETL course on Udemy. For a coupon to the course, check out the trailer video on the channel SQL In Six Minutes.
There are design mechanisms that we can use to simplify this process. If we don't have those in place, this can be a costly activity - if we're in a situation where we need to free up data. One easy recommendation in general: I recommend that DBAs and developers consider restricting the size of databases to their need over removing objects, if they haven't done proper due diligence. For an example, many environments will have databases sized too large for their needs - it's much simpler to re-size these to their needs to free space over removing tables with data that may have costly effects. This also applies to "live" data versus "archive" data, where the latter may be provided with more space than the former. Of course, these are basic principles that all environments should have in place. Other design mechanisms are much more effective and greatly reduce the amount of hours this takes to resolve.
Unfortunately, looking at metadata and making assumptions isn't the best way to approach this. For instance, consider the below query and what wrong assumptions we could make from it. This is true with all of the things we discuss in the video as well.
---- What are some possible wrong assumptions we can make from these two columns?
SELECT
create_date
, modified_date
FROM sys.tables
One possible answer: it's possible that the create date doesn't actually reflect the original creation date because the table was dropped erroneously at one point and then re-created.
Are you looking for tech consultants that can assist with design and development? As we can see in the above example, not considering the costs may have huge impacts to your company and clients. From building custom applications to working with existing infrastructure that's causing you trouble, we can get you connected to consultants who can assist. You can contact for assistance.