About myself and everyone else.
What is meant by a hot table, it might mean something completely different to people even when working on the same system.
When I worry about storage read performance I’d most likely be interested in high read tables as hot tables and vice versa for writes. If I’m concerned with the overall performance of IO on my system I can look at the combined value for reads and writes.
Where this is going, the concept of explorer is well-known, venture forth and discover new things, it’s also a risky business as early explorers might have learned with expeditions to Africa. You might get eaten by a lion or face the natives so pack a gun.
Understandably so, the risks of finding out what is happening with hot tables on your system as a DBA can lead to you having to face developers, SAN administrators and when both of those are consulted it might even lead to risky interaction and bartering for new hardware with the village chiefs who have been dancing around the fire for millennia and it has always worked in the past, so why change.
At this point, your GUN is a dmv and ammo the interpretation and facts validating the experience of your claims. The specific dmv I like to use for this is sys.dm_db_index_usage stats. This dmv indicates 4 key items when dealing with indexes and heaps. I’m only looking at the user stats for now; seeks, scans, lookups and updates.
If are not an explorer, if you are a DBA and you expect that you have disk problems and need more info you can also use this view. How do you know you have disk issues, there are tons of into on that, if you don’t know then you probably don’t have a problem in the first place, or not one big enough! (Until business acquires 5 new companies which will be consolidating into your database).
In my example I’m aware of limitations in terms of IO. I know there are no budget for upgrades and no room on the SAN to move anything around i.e. a rock and a hard place. So I want to find out what is IO intensive on my system. Assume I’ve already gone through sys.dm_os_wait_stats and see disk is an issue.
You have to know a bit about the dbs running on your system. If you run a retail store and the stock tables are constantly updated and sitting as the “hot” table. That would be normal. However in this same system if you find out most of IO goes toward working out the best distribution centre based on the usage of post codes. That may be abnormal.
This query provides you with “hot” tables on a server level.
select db_name(database_id) as db,
object_name(object_id,database_id) as obj,
rank() over (partition by @@servername order by user_seeks + user_scans + user_lookups + user_updates desc ) as rank,
Note: don’t run this when you just started your server. If you don’t know why read some more info on dmvs.
This is a simple query but you can get a vast amount of info from this.
1- First of all we see that database “a” is using quite a bit of resources at first glance. Is this meant to be the case. Is this your orders database (money generating) or is this a value add, maybe a database for your bosses wine list. If it’s the latter… go chat with your boss about wine.
2- Is the query is wrong why do I see 3 rows for the same object.? This dmv will have an entry for each index or heap. Don’t panic. I have not included the index id so you won’t be able to tell. We can see that for the dbchangelog table 3 indexes (or possibly 1 heap + 2 indexes if you want technical) appears. This is definitely a “hot” table. We can see from the name it’s a logging table for auditing. This may be expected. You need to note from the output that it is qualitative. This means it simply gives the number of times it was accessed and by using which access method. It does NOT tell you the amount of data read or writes. You could have a tiny lookup table with 5 rows sitting at the top of the “hot” table list.
3- Access method, scan or seek or lookup. We can see in the dbchangelog table that there is a scan, this is an index scan and the lookup is a lookup against that index. There is a good read in books online about what this means it’s under “Execution plans -> reading output”. We could modify an index to cover the column as it’s currently being looked up against another. The same goes for the index scan this can also be modified to increase performance.
4- The session table has no index seeks, only scans. We can have a look at the queries touching this table and perhaps add an index. You can use sp_depends session. And if it’s not a local proc you can use query plans dmv or sys.dm_exec_query_stats. You can do this by querying the xml and tie the planhandle back to exec_stats and get the SQL handle and get the code.
5- Our example doesn’t have any unused indexes and that is being maintained. This can be seen with no values in seeks,scans and lookups but a large value in updates. Those we would look at treating depending on how long the server has been up for. I always use a period of 1 month. This means that month end reports would have run and in a “normal” system should be indicative of which indexes are not being used.
I’ve stopped at five points but there is really no limit. If my system grew large enough I could use this to plan isolation for my tables such as moving them to separate files and file groups. By adding the index detail such as columns I can combine or remove indexes. When this happens the sum of operations will most likely decrease because of less maintenance on the unused or duplicate index.
You can also modify the query to be database specific or even table specific.
Cheers to exploration.