Solved

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

Posted on 2014-01-30
6
667 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 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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:ScottPletcher
ScottPletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now