Solved

How to Track How Often a SQL Server Table is Queried?

Posted on 2014-01-30
6
712 Views
Last Modified: 2016-02-10
Is there a way in SQL Server 2012 to track "how often" a Table is queried?

I'm in the process of analyzing "long-running ETL" processes (SSIS) to try and identify if any of these processes can be optimized. As part of my initial analysis requirement, I need to pull a list of long-running ETL Packages, which I've done. Now I need to see if I can identify which of these long-running ETL Packages contain Tables Updates for TABLES that are "highly utilized" within our Company.

And so, I want to know if there is a way to track how often a certain Table is Queried as this will tell me which ETL's contain Tables that are most widely used in our organization.
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 1

Assisted Solution

by:kevwit
kevwit earned 250 total points
ID: 39821417
For your initial analysis you could use the SSIS Dashboard to drill into the executions. Depending on the number of packages you've identified this might be helpful.

You can change the amount of performance data you gather by checking you SSIS Catalog (usually SSISDB) settings and changing the server logging level to "Performance".

This script would also do the above:
EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'SERVER_LOGGING_LEVEL', @property_value=2
GO

Tracing anything from the SQL DB side likely isn't going to be valuable for you because you need to know which package is executing the queries. However, if you can look at package execution timing and compare to a SQL trace including query execution times (filtered by the execution account for the SQL job) that may also work depending on how many packages are running at the same time.

The last option is the most complex, but the most powerful. SSIS 2012 has a new feature called "Data taps". These would have to be programmed and ran as part of the package execution (instead of just executing a job that runs a package there would have to be a script created that executes the package and initiates data taps as the package is executed). Here is one blog that describes an overview of this process::

http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html
0
 
LVL 17

Author Comment

by:MIKE
ID: 39821441
Thanks for the expert input.

Related to your comment:
"...Tracing anything from the SQL DB side likely isn't going to be valuable for you because you need to know which package is executing the queries...."

I want to clarify my requirement. I actualy need to know or find-out which literal DB TABLES are being utilized within our Database..."outside and separate" from any SSIS Package. What we are trying to analyze is:

1. Which of the "long-running ETLs" effects the "most utilized DB Tables" based on SQL Queries processed outside of SSIS. Basically I need to know what are the most widely used Tables by our Reporting Processes that are consumed by our Customers.

The reason for this requirement is so that we can Prioritize, which Long-Running ETL/SSIS Packages to begin analyzing for optimization and refinement. In this way, we will get the most bang for our buck as we'll be working to optimize the long-running ETL packages that effect the most widely used table(s) in our system....

Thanks
0
 
LVL 1

Expert Comment

by:kevwit
ID: 39821610
Ok, I think I understand now. But to be sure, you want to identify most widely used tables before going further?

In an effort to better understand your goal I'll highlight two ways to identify queries using performance info.

You can download and install the SQL Server 2012 Performance Dashboard reports:
http://www.microsoft.com/en-us/download/details.aspx?id=29063

This would be the easiest and there is a report named "Top Queries by CPU time" and another named "Top Queries by I/O". This may meet your requirements?

Another option is to use DMVs. Check out this blog post and look at all the related posts on the bottom:

http://sqlserverplanet.com/dmvs/find-queries-taking-most-cpu-processor

These posts highlight the problem. How are you looking to identify what tables or views are "most utilized DB Tables"? By the shear number of times the entity is accessed? If this is true I'll have to tackle this much differently.

Does this help at all?


This blog post likely won't help you, but I'll include it in case your interested:

http://www.chilledsql.com/welcome/tip_category_dmvqueries/tip_detail_dmvqueries_gettopnlongrunningqueriesonserver
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39821730
You can use view:
sys.dm_db_index_usage_stats
to see table and index usage (for tables w/o an index, i.e. "heaps", the index_id will be 0).

That is a cumulative view, from the time the SQL instance started.  Only tables/indexes that were actually used will be in this view; thus, any table/index not in the view has not been used since SQL started.  

Also, you can calculate usage for a selected period(s) of time by capturing the view results at those times and comparing/subtracting them from the previous view results.

For example, if you captured the view results every hour, you could determine read activity for each hour.
0
 
LVL 1

Accepted Solution

by:
kevwit earned 250 total points
ID: 39821847
Good point Scott.

Here's some sample code that should work in your environment and you can build on that:

USE /*DBNAME*/
SELECT DB_NAME([database_id]) AS [database name] ,
OBJECT_NAME([object_id]) AS [Table name] ,
*
FROM [sys].[dm_db_index_usage_stats]
WHERE OBJECT_NAME([ddius].[object_id]) IS NOT NULL
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39822151
Since that view actually contains rows from all databases on the instance, you'll need to include the database_id to get the object name.  However, because that is a lot of overhead, I'd do that after you capture the view data into your own table.

That is, create your own table based on the view, insert the raw view data into it periodically, on whatever schedule you want.  Then, later, update your table containing the view data with the correct object names.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question