How do you gather statistics from SQL Server?

Question

sys.dm_exec_query_stats seems to be a very useful function to gather statistics from your database which you can use as a starting point to find queries which need to be optimized. selecting * gives somewhat cryptic results, how do you make the results readable? What type of queries do you get from it? Are there other functions or queries you use to gain performance statistics?

Answer 1

Why don't you first use 'set pagesize 0'.

Answer 2

To make the results useful, you need to cross reference the information with a few other DMV's and also concentrate your analysis and tunning efforts on the most poorly performing queries.

Here is (one I made earlier) an example of using the DMV you have mentioned to identify the most costly SQL Server queries.

How to identify the most costly SQL Server queries using DMV’s

You can easily extend this to look at other metrics too.

If you want to make performance tuning a breeze for yourself, you should consider installing the freely available SQL Server Performance Dashboard Reports.

These can be used to identify SQL Server Waits, the queries that consume the most I/O, the longest running queries by duration etc.