Inefficient Query in SQLInstance.Databases.cs (plus suggested fix!)

[286fadf8]

Sorry for not doing a proper PR for this, but figured this would probably be faster. On some of our instances the query which runs from SQL\SQLInstance.Databases.cs@26 takes several minutes to complete (in SSMS - in Opserver, it times out) and chews up a ton of CPU. Here is a refactored version that completes in a second on the problematic instances (sorry, formatting has been changed to match my personal taste; please feel free to take the essentials and keep your preferred formatting). The basic idea is we roll up all the work previously done as separate joins to master_files into a single query, and also MIN the transaction log file_id to account for a situation where somebody is crazy enough to have added more than 1 log file to a database (the old query would've produced duplicate rows in that situation, although perhaps that was desired?)

SELECT /* SQL\SQLInstance.Databases.cs@26 */
          Id                        = db.database_id,
          Name                      = name,
          State                     = state,
          CompatibilityLevel        = compatibility_level,
          RecoveryModel             = recovery_model,
          PageVerifyOption          = page_verify_option,
          LogReuseWait              = log_reuse_wait,
          UserAccess                = user_access,
          IsFullTextEnabled         = is_fulltext_enabled,
          IsReadOnly                = is_read_only,
          IsReadCommittedSnapshotOn = is_read_committed_snapshot_on,
          SnapshotIsolationState    = snapshot_isolation_state,
          ReplicaId                 = replica_id,
          GroupDatabaseId           = group_database_id,
          Containment               = containment,
          LogVolumeId,
          TotalSizeMB               = (CAST(TotalSize AS float)* 8) / 1024,
          RowSizeMB                 = (CAST(RowSize AS float)* 8) / 1024,
          StreamSizeMB              = (CAST(StreamSize AS float)* 8) / 1024,
          TextIndexSizeMB           = (CAST(TextIndexSize AS float)* 8) / 1024,
          LogSizeMB                 = (CAST(LogSize AS float) * 8) / 1024,
          LogSizeUsedMB             = CAST(logu.cntr_value AS float)/ 1024

FROM      sys.databases                  db
LEFT JOIN sys.dm_os_performance_counters logu ON  db.name = logu.instance_name AND logu.counter_name LIKE N'Log File(s) Used Size (KB)%'
LEFT JOIN (   SELECT   database_id,
                       TotalSize     = SUM(CAST(size AS bigint)),
                       RowSize       = SUM(CAST(CASE Type WHEN 0 THEN size ELSE 0 END AS bigint)),
                       LogSize       = SUM(CAST(CASE Type WHEN 1 THEN size ELSE 0 END AS bigint)),
                       StreamSize    = SUM(CAST(CASE Type WHEN 2 THEN size ELSE 0 END AS bigint)),
                       TextIndexSize = SUM(CAST(CASE Type WHEN 4 THEN size ELSE 0 END AS bigint)),
                       MinLogFileID  = MIN(CASE Type WHEN 1 THEN file_id END)
              FROM     sys.master_files
              GROUP BY database_id)      st ON db.database_id  = st.database_id
OUTER APPLY (SELECT LogVolumeID = volume_id FROM sys.dm_os_volume_stats(db.database_id, st.MinLogFileID)) v