A customer asked recently if there is a way to find SQL that references objects that are a certain size. They are evaluating historic queries on tables to find possible candidates for conversion to columnstore indexes.
One option is to use the "Scan SQL" option in the SQL Optimizer. It will allow you to add "source code" that contains SQL statements. These can be stored procedures in a database schema, SQL embedded in files, or other collections like a Profiler trace file. It will then scan the code, extract SELECT, INSERT, UPDATE and DELETE statements, and get the query plan from the connected database. The "magic" happens with the classification rules. There are some default ones, and additional ones can be added. For example, flag a statement as "problematic" if there is a Clustered Index Scan that references an index with more than "n" estimated rows.
After the scan is complete, it will show you the number of statements found that match one or more rules.
Drilling down to an object shows the classification, the extracted SQL, and the query plan. From there, the SQL statement could be optimized to verify if there is another way to write the query that generates a "better" query plan. These can even be run at scale (via Benchmark Factory) to validate if the new SQL is indeed "better."
A free trial of SQL Optimizer can be included with your trial of Spotlight on SQL Server Enterprise. Get to it!
