Ever need to build out a capability and the SQL query is your blocker? Use a SQL query Plan ‘howTo’ to figure out what’s taking query so long. My thanks to Dennis Zwahlen (a Data and AI CSA – LinkedIn ) helping me figure out what was causing a SCOM DW SQL query to render data VERY slowly!
Don’t get me wrong, the sheer volume of events is definitely part of the problem. Event rules are using expressions to further restrict collected event data.
SCOM DW Events ingested for DC Security Events when SIEM is a limit, and NOT using ACS feature. Will discuss the SCOM DW Event ingestion and additional XML authoring options to turn down the pressure.
Time to use the ‘SQL query Plan howto’ blog for SQL execution plan, to help to figure out why the DW Query takes so long. Using the execution plan, similar to SQL profiler, will provide insight to possibly speed up query, allowing PowerBI app/report rendering of data.
From SSMS > View > Add Display Estimated Execution Plan
SQL execution plan starting from the left documenting SQL query
SQL query plan starting from the left documenting SQL query
Sort is taking 4.5 minutes in this example of the SQL execution plan visual. You can see moving right from the Join lines documents how SQL behaves, and how each piece affects overall execution.
Hope this helps for another diagnostic SQL step in your tool box!