User activity vs. System activity on the Index Usage Statistics report

Question

I recently decided to crawl over the indexes on one of our most heavily used databases to see which were suboptimal. I generated the built-in Index Usage Statistics report from SSMS, and it's showing me a great deal of information that I'm unsure how to understand.

I found an article at Carpe Datum about the report, but it doesn't tell me much more than I could assume from the column titles.

In particular, the report differentiates between User activity and system activity, and I'm unsure what qualifies as each type of activity.

I assume that any query that uses a given index increases the '# of user X' columns. But what increases the system columns? building statistics?

Is there anything that depends on the user or role(s) of a user that's running the query?

Answer 1

But what increases the system columns? building statistics?

SQL Server maintains statistics on an index (it's controlled by an option called "Auto Update Statistics", by default it's enabled.) Also, sometimes an index grows or is reorganized on disk. Those things come in under System Activity.

Is there anything that depends on the user or role(s) of a user that's running the query?

You could look into using SQL Server Profiler to gather data about which users use which indexes. It allows you to save traces as a table. If you can include index usage in the trace, you could correlate it with users. I'm sure the "showplan" would include it, but that's rather coarse.

This article describes a way to collect a trace, run it through the index tuning wizard, and analyze the result.