Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-30
6
Medium Priority
?
741 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
  • 3
  • 2
6 Comments
 
LVL 1

Assisted Solution

by:kevwit
kevwit earned 1000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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 1000 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

885 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