Our SQL 2014 server has a blazing-fast tempdb drive (2800 IOPS) but a much slower data drive (500 IOPS). Our application runs a few long-running reporting queries that are I/O-intensive and we'd like to avoid them starving our server for I/O capacity when they run.
Ideally we'd be able to limit these queries to 50% of available I/O capacity. Unfortunately SQL Server Resource Pool's IOPS throttling is not percentage-baed nor volume-specific. If I limit to 250 IOPS, then it will unnecessarily slow down performance of queries that make heavy demands on tempdb. Slowing down these long-running queries if the server is busy is OK, but slowing them down by 10x+ if they need lots of tempdb access is not OK.
So we're looking for workarounds that will defend other queries from these lower-priority, long-running queries, but without unnecessarily hurting performance of these long-running queries if they happen to use lots of tempdb. It's not practical to change the queries themselves to reduce tempDB usage-- these queries are generated by a custom reporting feature that may sometimes generate really complex query plans that spill results to tempdb.
So far the best idea I have is to remove IOPS throttling and instead use the "Importance" of a workload group to defend the rest of the server's I/O capacity from these queries. Is this a good solution to the problem I'm trying to solve? What are the pros and cons of using Importance?
Or is there a better way to achieve our goals?