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 ?
This specific information is not fully stored in SQL Server.
You do however have a few options to make a small analysis.
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.
You can use the following query from the
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.
- 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).
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.