Tables accessed in a given week in a database?

  • 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.

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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 2 days, 10 hours ago by  homebrew01.

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

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