1

I am using MS SQL Server 2014. Have some kind of DWH. Is there some way to find some kind of stats, which columns from database are used in select clause and how many times, and which columns are used in where clause and how many times ?

2
  • I'm very curious why you want to know this information. Jun 14 '17 at 8:38
  • I would like I can answer on your question. It's demand from my boss. Jun 14 '17 at 9:18
2

This specific information is not fully stored in SQL Server.

You do however have a few options to make a small analysis.

  1. You can check the index_usage stats ( SYS.DM_DB_INDEX_OPERATIONAL_STATS) and cross reference this to the columns included in the index. This will, however, not give you exactly which columns are used or selected or used in a where clause. But you can derive a lot of information here to check for more optimal indexes.

  2. You can use the following query from the exec_query_stats

     SELECT
         OBJECT_NAME(qt.objectid)
       , qs.execution_count AS [Execution Count]
       , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
       , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
       , qs.total_worker_time AS [TotalWorkerTime]
       , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
       , qs.max_logical_reads
       , qs.max_logical_writes
       , qs.total_physical_reads
       , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
       , qt.text
     FROM
         sys.dm_exec_query_stats AS qs
     CROSS APPLY 
         sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
    

This will give you the execution count of queries on you SQL server (not all, only cached). Here you can go through the query text field and cross reference to your tables but this will be a lengthy process. You can also check the execution stats for stored procedures & then cross reference this to the columns in the stored procedures, but this will again be a tough process.

  1. Use a third party tool which captures most of your queries and query through your queries (this will be slow and a lot of data).
-5

The most important resource to any SQL optimizer is the statistics collected for different tables within the catalog. Statistics is the information about indexes and their distribution with respect to each other. Optimizer uses this information to decide the least expensive path that satisfies a query. Outdated or missing statistics information will cause the optimizer to take a less optimized path hence increasing the overall response time. Following table lists SQL commands for different database that is used to update statistics. For further details, refer to the reference manual for your particular RBDMS

Oracle: ANALYZE command or DBMS_UTILITY package

DB2:    RUNSTATS command

MS SQL Server:  UPDATE STATISTICS

Informix:   UPDATE STATISTICS

Sybase ASE: UPDATE STATISTICS

You should read this it might help.

http://web.synametrics.com/top10performancetips.htm

2
  • Do not use analyze in Oracle any more. The only recommended way is to use dbms_stats (not dbms_utility) Jun 14 '17 at 8:28
  • 4
    This doesn't answer the question. The asker wants to know how often columns are used in select and where clauses of queries. Jun 14 '17 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.