Sql-server – Slow SQL Server Database (approx 2TB) – high CPU due to index fragmentation

Now, the best way to answer this is to set up a smaller test database of a few gigabytes. It will accurately predict the best technique.

Your technique (2) is horrible because step 1 leads to all NCI's being rebuilt to work together with the resulting heap table. Don't do that.

The good news is that DROP_EXISTING works with changing the index definition including columns and partition scheme:

The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.

And this is the reason why this feature exists. It is not equivalent to first dropping, then creating.

Here are the basic techniques (all of yours are horrible due to the intermediate heap table):

1. Drop all NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
2. Drop all non-aligned NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
3. Rebuild the CI WITH (DROP_EXISTING = ON)

(1) is very light on the transaction log. It is not maximally efficient. (3) is the simplest - it will cause all indexes to be rebuild as aligned indexes. This is not so good because you will have to rebuild those that you don't want to align.

(2) is probably best in all cases. I can't think of any case where it might be worse-off. It has the advantage of reusing existing indexes for a more efficient rebuild (this is possible because you didn't just drop all NCIs). NCIs which you want to be non-aligned don't benefit from this method and would be rebuild needlessly. So get rid of them before starting the procedure.

All of this actually works online although you said you don't need that.

Again, just try it out on a sample database. Certainly you shouldn't entrust the availability of your database to my advice. I suggest you take the ideas from this post and try them out.

Do the SQL Server track all the queries run so that the DMVs can access the information?

I would suggest taking a look at a free e-book Red Gate published here: Performance Tuning with SQL Server Dynamic Management Views. It is free and a good read. It will point out those DMVs that provide cumulative data versus point-in-time (what is currently in the buffer). As long as the server has not been rebooted you are in good shape to capture some good information.

What other sql snippets would be helpful to diagnose the problem?

You can also use sp_whoisactive that will provide much more information if you can run it during the hours noted. Returns CPU, Memory, and I/O usage of each query. You can even have it pull the execution plan of the offending query as well.

Do a basic Bing/Google search for SQL Server DMV and high CPU queries or something will likely bring up some other good scripts/blog post on researching your particular situation.

Just a note on your comment regarding moving the scheduled reports, I would also look at ad-hoc reports on those that users may run on their own. Especially if you are running SSRS on the same server as the database engine. I usually take a look at the ExecutionLog table in the SSRS database (default name ReportServer). There is a wealth of information in there on execution time and rendering time of the reports.