Tags: aro2003/sqlwatch
Tags
1.3 (marcingminski#62) * 1.2.6841.38712 (marcingminski#58) * 1.2.6841.36365 (marcingminski#57) * Capture Missing Indexes Missing Index table + Stored Procedure to capture missing indexes * Fixed Error * More error fixing Added snapshot_type_id and also removed constraints * few tweaks to the original procedure 1. If we remove table_name from the [create_tsql] we will also be able to remove sc.[name], so.[name] from the cursor. 2. as we no longer need to join on JOIN ' + QUOTENAME(@database_name) so we can remove the cursor entirely. This will improve the execution time on servers with large number of dbs and simplify the code. 3. as we no longer need a cursor, we also do not need #database_list 4. for the sake of simplicity, we also do not need #missing_indexes, we can insert directly into the target table 5. even though I originally suggested it, I am going to remove column [benefit] as it is a simple calculation based on existing columns which can be done during reporting (PowerBI) 6. the final table [dbo].[logger_missing_indexes] does not have some of the useful columns that #missing_indexes had so I am going to bring them in 7. the CREATE INDEX statement has ONLINE=? which only works in the Enterprise edition so I am going to add a check for it 8. the CREATE INDEX contains table name and column lists in the name I am not against it but something it can create long names. as I have removed table_name for the sake of simplicity I am happy to make a compromise and remove table and column list entirely but add index_id, timestamp and "SQLWATCH" into the name so we know where the index has come from and when. 9. I am also going to modify table [dbo].[logger_missing_indexes] and remove [snapshot_type_id] TINYINT NULL DEFAULT 1 and give it its own snapshot_id with its own retention and schedule 10. I am going to create necessary PKs and FKs 11. I am going to rename @date_snapshot_current to @snapshot_type to make it consistent with other procedures. The snapshot _current and _previous only apply to cumulative snapshots where we calculate deltas. 12. I am going to change CAST(avg_user_impact as nvarchar) + '%' [Impact] to simply avg_user_impact as it is much more efficient to store raw numerical value in the databases and format in the presentation tier. 13. I am going to add servername in preparation for the future central repo. 14. I am also goint to NOT exclude SQLWATCH from the database list because why would we not capture missing indexes in SQLWATCH :) 15. I am going to remove FILLFACTOR=100 as this is the default anyway. Some DBAs may have different preference and different default FILLFACTOR and I wouldnt want to force any config different to what they prefer. Co-Authored-By: ColinDouglas92 <colindouglas92@users.noreply.github.com> * PowerBI Index Advisor Dashboard * added agent job 1.2.6839.42002 * fixes marcingminski#52 1.2.6839.42002 * fixes marcingminski#53 1.2.6841.36365 * 1.2.6841.38712 force rebuild Dac to include new changes -previous Dac seems have gotten out of sync due to conflict. * xe sessions (marcingminski#61) * fixes 47 PowerBI test fix of issue 47 * wait_info system_healh xe session logger * fixed query stats collection * added IO_SUBSYSTEM XE session * changed default query retention to 3 days * removed dacpac from repo as they will now be available in releases. * Performance Dashboard 1.1
1.3 (marcingminski#62) * 1.2.6841.38712 (marcingminski#58) * 1.2.6841.36365 (marcingminski#57) * Capture Missing Indexes Missing Index table + Stored Procedure to capture missing indexes * Fixed Error * More error fixing Added snapshot_type_id and also removed constraints * few tweaks to the original procedure 1. If we remove table_name from the [create_tsql] we will also be able to remove sc.[name], so.[name] from the cursor. 2. as we no longer need to join on JOIN ' + QUOTENAME(@database_name) so we can remove the cursor entirely. This will improve the execution time on servers with large number of dbs and simplify the code. 3. as we no longer need a cursor, we also do not need #database_list 4. for the sake of simplicity, we also do not need #missing_indexes, we can insert directly into the target table 5. even though I originally suggested it, I am going to remove column [benefit] as it is a simple calculation based on existing columns which can be done during reporting (PowerBI) 6. the final table [dbo].[logger_missing_indexes] does not have some of the useful columns that #missing_indexes had so I am going to bring them in 7. the CREATE INDEX statement has ONLINE=? which only works in the Enterprise edition so I am going to add a check for it 8. the CREATE INDEX contains table name and column lists in the name I am not against it but something it can create long names. as I have removed table_name for the sake of simplicity I am happy to make a compromise and remove table and column list entirely but add index_id, timestamp and "SQLWATCH" into the name so we know where the index has come from and when. 9. I am also going to modify table [dbo].[logger_missing_indexes] and remove [snapshot_type_id] TINYINT NULL DEFAULT 1 and give it its own snapshot_id with its own retention and schedule 10. I am going to create necessary PKs and FKs 11. I am going to rename @date_snapshot_current to @snapshot_type to make it consistent with other procedures. The snapshot _current and _previous only apply to cumulative snapshots where we calculate deltas. 12. I am going to change CAST(avg_user_impact as nvarchar) + '%' [Impact] to simply avg_user_impact as it is much more efficient to store raw numerical value in the databases and format in the presentation tier. 13. I am going to add servername in preparation for the future central repo. 14. I am also goint to NOT exclude SQLWATCH from the database list because why would we not capture missing indexes in SQLWATCH :) 15. I am going to remove FILLFACTOR=100 as this is the default anyway. Some DBAs may have different preference and different default FILLFACTOR and I wouldnt want to force any config different to what they prefer. Co-Authored-By: ColinDouglas92 <colindouglas92@users.noreply.github.com> * PowerBI Index Advisor Dashboard * added agent job 1.2.6839.42002 * fixes marcingminski#52 1.2.6839.42002 * fixes marcingminski#53 1.2.6841.36365 * 1.2.6841.38712 force rebuild Dac to include new changes -previous Dac seems have gotten out of sync due to conflict. * xe sessions (marcingminski#61) * fixes 47 PowerBI test fix of issue 47 * wait_info system_healh xe session logger * fixed query stats collection * added IO_SUBSYSTEM XE session * changed default query retention to 3 days * removed dacpac from repo as they will now be available in releases. * Performance Dashboard 1.1
1.2.6841.38712 force rebuild Dac to include new changes -previous Dac seems have gotten out of sync due to conflict.
1.2.6841.36365 (marcingminski#57) * Capture Missing Indexes Missing Index table + Stored Procedure to capture missing indexes * Fixed Error * More error fixing Added snapshot_type_id and also removed constraints * few tweaks to the original procedure 1. If we remove table_name from the [create_tsql] we will also be able to remove sc.[name], so.[name] from the cursor. 2. as we no longer need to join on JOIN ' + QUOTENAME(@database_name) so we can remove the cursor entirely. This will improve the execution time on servers with large number of dbs and simplify the code. 3. as we no longer need a cursor, we also do not need #database_list 4. for the sake of simplicity, we also do not need #missing_indexes, we can insert directly into the target table 5. even though I originally suggested it, I am going to remove column [benefit] as it is a simple calculation based on existing columns which can be done during reporting (PowerBI) 6. the final table [dbo].[logger_missing_indexes] does not have some of the useful columns that #missing_indexes had so I am going to bring them in 7. the CREATE INDEX statement has ONLINE=? which only works in the Enterprise edition so I am going to add a check for it 8. the CREATE INDEX contains table name and column lists in the name I am not against it but something it can create long names. as I have removed table_name for the sake of simplicity I am happy to make a compromise and remove table and column list entirely but add index_id, timestamp and "SQLWATCH" into the name so we know where the index has come from and when. 9. I am also going to modify table [dbo].[logger_missing_indexes] and remove [snapshot_type_id] TINYINT NULL DEFAULT 1 and give it its own snapshot_id with its own retention and schedule 10. I am going to create necessary PKs and FKs 11. I am going to rename @date_snapshot_current to @snapshot_type to make it consistent with other procedures. The snapshot _current and _previous only apply to cumulative snapshots where we calculate deltas. 12. I am going to change CAST(avg_user_impact as nvarchar) + '%' [Impact] to simply avg_user_impact as it is much more efficient to store raw numerical value in the databases and format in the presentation tier. 13. I am going to add servername in preparation for the future central repo. 14. I am also goint to NOT exclude SQLWATCH from the database list because why would we not capture missing indexes in SQLWATCH :) 15. I am going to remove FILLFACTOR=100 as this is the default anyway. Some DBAs may have different preference and different default FILLFACTOR and I wouldnt want to force any config different to what they prefer. Co-Authored-By: ColinDouglas92 <colindouglas92@users.noreply.github.com> * PowerBI Index Advisor Dashboard * added agent job 1.2.6839.42002 * fixes marcingminski#52 1.2.6839.42002 * fixes marcingminski#53 1.2.6841.36365
1.2.6839.42002 (marcingminski#51) * Capture Missing Indexes Missing Index table + Stored Procedure to capture missing indexes * Fixed Error * More error fixing Added snapshot_type_id and also removed constraints * few tweaks to the original procedure 1. If we remove table_name from the [create_tsql] we will also be able to remove sc.[name], so.[name] from the cursor. 2. as we no longer need to join on JOIN ' + QUOTENAME(@database_name) so we can remove the cursor entirely. This will improve the execution time on servers with large number of dbs and simplify the code. 3. as we no longer need a cursor, we also do not need #database_list 4. for the sake of simplicity, we also do not need #missing_indexes, we can insert directly into the target table 5. even though I originally suggested it, I am going to remove column [benefit] as it is a simple calculation based on existing columns which can be done during reporting (PowerBI) 6. the final table [dbo].[logger_missing_indexes] does not have some of the useful columns that #missing_indexes had so I am going to bring them in 7. the CREATE INDEX statement has ONLINE=? which only works in the Enterprise edition so I am going to add a check for it 8. the CREATE INDEX contains table name and column lists in the name I am not against it but something it can create long names. as I have removed table_name for the sake of simplicity I am happy to make a compromise and remove table and column list entirely but add index_id, timestamp and "SQLWATCH" into the name so we know where the index has come from and when. 9. I am also going to modify table [dbo].[logger_missing_indexes] and remove [snapshot_type_id] TINYINT NULL DEFAULT 1 and give it its own snapshot_id with its own retention and schedule 10. I am going to create necessary PKs and FKs 11. I am going to rename @date_snapshot_current to @snapshot_type to make it consistent with other procedures. The snapshot _current and _previous only apply to cumulative snapshots where we calculate deltas. 12. I am going to change CAST(avg_user_impact as nvarchar) + '%' [Impact] to simply avg_user_impact as it is much more efficient to store raw numerical value in the databases and format in the presentation tier. 13. I am going to add servername in preparation for the future central repo. 14. I am also goint to NOT exclude SQLWATCH from the database list because why would we not capture missing indexes in SQLWATCH :) 15. I am going to remove FILLFACTOR=100 as this is the default anyway. Some DBAs may have different preference and different default FILLFACTOR and I wouldnt want to force any config different to what they prefer. Co-Authored-By: ColinDouglas92 <colindouglas92@users.noreply.github.com> * PowerBI Index Advisor Dashboard * added agent job 1.2.6839.42002
Merge pull request marcingminski#45 from marcingminski/development v1.1.6833.36316 PowerBI Disk utilisation and performance tweaks
Merge pull request marcingminski#40 from marcingminski/development Merge into master