Would you like to know which indexes are used frequently or rarely? Which ones aren't used at all? Which tables and indexes are the largest? It's very easy to create visualizations for this. They're both visually appealing and practically useful.
Environment Setup
As always, we'll extract data from production where you likely can't install anything. Therefore, we'll do all the work on our local machine while transferring data from the server.
You'll need:
Python with Tkinter installed (relax, on your laptop, not on a production server)
Additional packages: plotly, numpy, pandas, and kaleido
Download heatmap.sql and heatmap.py from the repository: https://github.com/tzimie/sqlpig
The stored procedure from heatmap.sql needs to be installed on your target server in a "technical" database (the kind that exists everywhere under various names). Everything else is done on your personal computer.
Creating the Diagram
Execute the procedure on your target server. Save the results as CSV and transfer to your local machine. The Python program interface works as follows:
For example, if you have size.csv, run
You'll get in your browser a familiar TreeView visualization with a thermal color palette, where colors represent "temperature" based on the logarithm of one of the metrics (seeks, scans, updates). All values are displayed in tooltips.
The diagram is "live" and clickable. It retains these properties if you save the entire page from your browser as HTML file(s).
What Can You Discover with This Diagram?
Remember that data in sys.dm_db_index_usage_stats accumulates since server startup. After a restart, allow sufficient time to collect relevant statistics.
If you have AlwaysOn with secondary nodes that allow reads, you might find indexes that are useless on the primary node but valuable on secondaries (this can happen when, for example, the primary handles OLTP while secondaries handle OLAP/Reporting).
Key metrics interpretation:
Seeks show table activity levels. If an index appears black (seeks=0), check scans. If both values are near zero, the index is (almost) unused.
Scans indicate the opposite - if a table in an OLTP system shows "hot" scan activity, you likely need more indexes.
Updates show all index modifications - SQL Server must reflect data changes in all indexes, even unnecessary ones. Black, "cold" tables are likely archives.
