Grant Fritchey (9/28/2016)
Going to a heap in the hopes that you get improved compression is not the way to tune queries. If that clustered index is in use, it needs to stay in place, regardless of whether you get optimal compression on it or not. Compression is an added benefit, not a purpose unto itself.
The clustered index, also PK, is "only" there because it's a small data type and sequencial..
If it was the table's natural key it would be a huge index for PK (more than 4 fields) that would imply all the FKs having the same fields (instead of just one), all table's "extra" indexes would also have it (huge key instead of 8 bytes - bigint) and fragmentation would also be a bigger problem...
But if it has lots of writes has an index it should also have lots of writes as "only data" (heap), so I'll just keep it has it is...
The storage, right now, has good response times... average 5ms...
@Grant: if I may ask another thing....
About performance tuning.
Weekly I analyse the workload and check what possible missing indexes should exist and what indexes have lots of writes and few reads.
I always get an index recommendation on a worker process that executes every 15 minutes, according to Glen Berry's DMV after two days it has an advantage of 400.000.
I create the index and 24h after I have a "recommendation" to drop the index... 150.000 writes and 200 reads...
What's the best approach on these cases?!
Thanks,
Pedro