Oct 7, 2018
1.3 (#62)
* 1.2.6841.38712 (#58)

* 1.2.6841.36365 (#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 #52

1.2.6839.42002

* fixes #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 (#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

@marcingminski marcingminski released this Oct 7, 2018

  • [fixed] issue #47

  • [added] interrogate system_health extended event session to get information about long waits including query text, workers and long io requests.

  • [added] new snapshot (6) for query text with default 3-day retention

  • [added] new performance dashboard with lots of new tabs: Query statistics, memory, database performance, logical file performance, logical disk performance and generic performance counter analyser

  • [removed] dacpac from branches. these are binary files causing merge problems. dacpacs are now available in releases.

Assets 3

@marcingminski marcingminski released this Sep 24, 2018 · 18 commits to master since this release

  • [fixes] #52 hardcoded database name in one of the job steps breaking deployment via dbatools.io
  • [fixes] #53 missing snapshot_type_id = 3 causing PK violation.
Assets 2
Sep 24, 2018
1.2.6841.36365 (#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 #52

1.2.6839.42002

* fixes #53

1.2.6841.36365
Sep 23, 2018
1.2.6839.42002 (#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

@marcingminski marcingminski released this Sep 17, 2018 · 21 commits to master since this release

  • [added] new PowerBI dashboard to monitor disk utilisation
  • [added] Win32_Volume logging via PowerShell
  • [added] [dbo].[ufn_time_intervals] to address poor performance of time interval generation
  • [removed] setup.sql as no longer supported
  • [fixed] sp_spaceused logging pre SQL2016 (the @oneresultset problem)
Assets 2

@marcingminski marcingminski released this Sep 16, 2018 · 25 commits to master since this release

Merge pull request #40 from marcingminski/development

Merge into master
Assets 2