Currently Browsing: Index Usage Report Project

SQL Server / Index Usage Report Project / Most Expensive Query Plans

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments
SQL Server / Index Usage Report Project / Most Expensive Query Plans
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...
read more

SQL Server / Index Usage Report Project / Most Expensive Queries

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments
SQL Server / Index Usage Report Project / Most Expensive Queries
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...
read more

SQL Server / Index Usage Report Project

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 5th, 2017 | 0 comments
SQL Server / Index Usage Report Project
 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)...
read more