Tables accessed in a given week in a database?

Question

Tables accessed in a given week in a database?

Stephen Rybacki

Hall of Fame

Points: 3402
More actions
October 12, 2021 at 5:21 pm
Go to Answer
#3939886

I am not sure if this is the right sub forum, but I need to do this to estimate the size of our cloud space.
What I need is to be able to get a listing of all the tables access in a given week for a database (two actually) that are via procedure calls from the other databases. Has anyone ever done something like this? I have SQL toolbelt Essentials, SolarWinds DPA and soon I'll also have SQL Sentry as a toolset outside of SQL Server itself. Any help would be deeply appreciated.
Grant Fritchey

SSC Guru

Points: 398882
More actions
October 13, 2021 at 1:12 pm
Answer
#3940174

There really is no easy way to about defining table access. The tools you have can show you the queries that have been run and/or the procedures that have been called. You can only tell tables though by querying those queries, and even then, you can't really tell to what extent they were accessed? One row or 10k rows. However, you have the queries, so you can at least put something together from that.
The other option is to capture the output of sys.dm_index_usage_stats. With the understanding that this DMV is reset several different ways, so the data within it is potentially less than accurate. I linked to the documentation which will explain when and how the data gets reset. With that, you can get reads and scans on each index. That can be combined to show reads and scans on a table/clustered index. That can get you closer to what you're looking for.
This is a very common request, but there really isn't an easy way to go about it, primarily because people don't access a table in SQL Server. Instead, queries are run that access sets of tables, possibly reading only from a nonclustered index instead of the table storage itself, or reading from a materialized view or all sorts other strange things. Usually, if you focus on queries and their behavior, which the tools you've referenced do, you get what you really need instead of what you're asking for.
I hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 1

Thank you Grant for this. I thought as much really, so I pursued a different track; I used date info in the largest tables (billions of rows) to get year over year growth stats. Our Director of Business Intelligence (and a darned good one that) is concerned our estimates for Snowflake space are going to be inaccurate without this kind of insight. I work at a hospital and COVID-19 has seriously skewed or growth rates on certain tables.

I think with the right mix of tables, I can average the growth rates and recommend a percentage of buffer space to plan on.

I love my job some days.

This reply was modified 3 years, 1 month ago by Stephen Rybacki.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 2

I'm just curious... what are you going to use Snowflake to determine? Or is this one of those things where they might yet be sure but will do more with when they learn to use the tool better?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 3

Actually Jeff, this is to estimate the size of Snowflake real estate we need to plan for as we transition to a warehouse. We have two major Health Care EMRs that are to be archived and reported on as we transition to Epic (a really comprehensive EMR for both inpatient and outpatient records combined).

Currently reporting is done from the existing SQL 2012 servers and it's a nightmare. These systems literally have thousands of tables (many unused) and this is an opportunity to clean that up, plus archive things in one fell swoop.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 4

Thanks for the reply, Stephen. You should keep track of how things go along the way. This migration would make an awesome article .

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

If you're really wanting to examine growth rate, maybe you can look at backup history and see the size of the database backups over time.

Backups are usually smaller than the actual DB, but it will show you the trend.

This reply was modified 3 years, 1 month ago by homebrew01.