Posted by Barac in Index Usage Report Project, SQL Monitoring, SQL Server
on Oct 8th, 2017 | 0 comments
I created this ETL to fetch and save expensive execution plans daily. When ETL process runs again firstly it creates directory and copy all plans from previous day to that directory, and only after that fetch new execution plans. This way I can look old plan later if I like. SSIS Package Description. Control Flow Screen I am using File System Task to create new directory using the name of day before, to store my yesterday’s execution plans. As you can see I am using variable FolderPath to create folder name with day before date. This is Expression I used From the valuated value...
Posted by Barac in Index Usage Report Project, SQL Monitoring, SQL Server
on Oct 8th, 2017 | 0 comments
Index Usage Report Project / Expensive Queries Table I created this ETL process (SSIS) to populate my WH Expensive Queries table, so I can later join this table using plan_handle to determine if an index is used within those expensive queries. Every time I run this ETL I am truncating this WH table and inserting new rows. Control flow > SQL Task SQL Task After that under the Data Flow, I created data source and destination of my ETL process For OLE DB Source I used SQL command SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; WITH XMLNAMESPACES (DEFAULT...
Posted by Barac in Index Usage Report Project, SQL Monitoring, SQL Server
on Oct 5th, 2017 | 0 comments
Index Usage Report Project The general project idea was to create the report about every index usage within DB. To be able to see a daily number of scans, seeks, page splits for all indexes or key lookups for clustered. You also can change the date range to see how this index behaved in past. Also, to be able to see all expensive queries using that index (red one are expensive). To see and save their execution plans as well. Project concept Index Usage Stats system views [sys].[indexes] – Basic index information (name, type, etc)...