/*********************************************************/
/*** STRYK System Improvement ***/
/*** Performance Optimization & Troubleshooting ***/
/*** GNU General Public License (V3, 29 June 2007) ***/
/*** www.stryk.info ***/
/*********************************************************/
# # # I X A D D E R # # #
Smart Stored Procedure to automatically add indexes to fix "Expensive Queries" in SQL Databases
PREFACE:
<<Why Did It Have to Be Snakes?>> (Indiana Jones)
This "Ix Adder" is deadly venomous for "Expensive Queries"!
It taps from SQL Server's "Missing Index" proposals and cleans them up sufficiently before installing them automatically.
IXA balances the Cost/Benefit of an Index to significantly improve READ performance while not degrading WRITE performance.
But take care: used in a wrong way this viper could also bite your very own butt!
"Ix Adder"'s little pal is "Ix Strictor" which disposes inefficient indexes to improve WRITE performance.
Sending out both - the viper and the boa - periodically will balance cost & benefit of indexing, keeping Query Performance good!
CONTENT:
ix_adder.sql
ix_strictor.sql
examples.sql
gpl-3.0.txt
REQUIREMENTS:
Microsoft SQL Server 2005 - 2017 [2019]
Permissions:
User must be member of SQL Database-Role "db_owner"
LICENSE:
Published under GNU General Public License (V3, 29 June 2007)
"gpl-3.0.txt"
https://www.gnu.org/
DISCLAIMER:
USE AS IS, AT OWN RISK, NO WARRANTY, NO GUARANTEE, NO SUPPORT
INSTALLATION:
Extract "IxAdder.zip" to any folder and open the scripts in SQL Server Management Studio.
Replace the tag [DatabaseName] with the name of the db to which you want to install the procedure to;
this is where "Ix Adder" and "Ix Strictor" will take a bite.
Execute scripts "ix_adder.sql" and "ix_strictor.sql" to install the Stored Procedures; then use SPs in SQL Agent Jobs or Maintenance Plans as required.
DESCRIPTION:
Ix Adder:
The procedure collects the "Missing Index" proposals - which were generated by SQL Server - based on the defined filter-thresholds.
It is crucial to maintain a good balance between "Cost" (e.g. number and/or size of indexes) and "Benefit" (e.g. reduction of Query Costs, CPU and RAM consumption).
"IX Adder" is supposed to automatically create/install those indexes, here avoiding redundancies, overlaps etc. to maintain the aforementioned balance.
The goal is to fix "Expensive Queries" which occur relatively often, thus improving READ performance while not over-indexing the table and not affect WRITE-performance!
"Ix Adder" ...
... avoids Duplicates; example:
Ix1: [ [Field1], [Field2] ], [Field2]
Ix2: [Field1], [ [Field2], [Field2] ]
==> IxAdder: [Field1], [Field2], [Field2]
... avoids Overlaps; example:
Ix1: [Field1], [Field2], [Field3]
Ix2: [Field1], [Field2], [Field3], [Field4]
==> IxAdder: [Field1], [Field2], [Field3], [Field4]
... merges Includes; example:
Ix1: [Field1], [Field2], [Field3] INCLUDE ([Field4])
Ix2: [Field1], [Field2], [Field3] INCLUDE ([Field5])
==> IxAdder: [Field1], [Field2], [Field3] INCLUDE ([Field4], [Field5])
And of course combining all those steps; example:
Ix1: [Field1], [Field2] INCLUDE ([Field3])
Ix2: [Field1], [Field2], [Field3] INCLUDE ([Field4])
Ix3: [Field1] INCLUDE ([Field5])
==> IxAdder: [Field1], [Field2], [Field3] INCLUDE ([Field4], Field5])
The indexes are named following this convention: [ixadder_N_yyyymmdd_hhmmss]
To manage (in addition to "Ix Strictor") the indexes, which were generated by "Ix Adder", I recommend the "Magic 7" script #3;
as this is already pre-configured to check on indexes named "ixadder%"!
Check it out:
https://sourceforge.net/projects/magic7/
SYNTAX:
EXEC [ix_adder]
{
{ @min_uptime } -- Min. SQL Service Uptime; default: 30 (days)
{ ,@fillfactor } -- Fillfactor (0 - 100 percent), default 98 (caution: do never set this lower than 70%, keep above 90%)
{ ,@script_only } -- Show TSQL only (0 = no, 1 = yes); default: 0
{ ,@online } -- ONLINE Indexing (0 = no, 1 = yes); default: 0 - Enterprise etc. Edition only!
{ ,@maxdop } -- Max. Degree of parallelism; default 64
{ ,@min_reads } -- Min. number of proposals at avg. per day; based on Uptime; default: 100 (times per day)
{ ,@min_impact } -- Min. index-impact rate (0 - 100); default: 90 (percent)
{ ,@max_rows } -- IF Records <= @max_rows THEN ADD INDEX; default: 1000000
{ ,@max_age } -- Max. period of last index-proposal (based on uptime); default: 14 (days)
{ ,@max_includes } -- Max. number of INCLUDE columns; default: 10
{ ,@filegroup } -- Filegroup for index; default: NULL (= default filegroup)
}
Ix Strictor:
The procedure checks on the indexes created by "Ix Adder". If an index is considered inefficiant/disposable - according to definable parameters -
the index would be either DISABLED or DROPPED.
Caution: by default this SP only deals with indexes named "ixadder_%"! DO NOT CHANGE THIS! Else you might kill the wrong indexes, causing severe performance problems!
SYNTAX:
EXEC [ix_strictor]
{
{ @min_uptime } -- Min. SQL Service Uptime; default: 30 (days)
{ ,@method } -- values: 'DROP'|'DISABLE'; default: 'DISABLE'
{ ,@script_only } -- Show TSQL only (0 = no, 1 = yes); default: 0 (no)
{ ,@min_reads } -- IF READ-Ops <= @min_reads THEN DISPOSE; default: 0 (no Reads = Index unused)
{ ,@max_writes } -- IF WRITE-Ops >= @max_writes THEN DISPOSE; default: 10000
{ ,@max_size } -- IF Idx-Size >= @max_size THEN DISPOSE; default: 512000 (KB)
{ ,@max_records } -- IF Records >= @max_records THEN DISPOSE; default: 1000000
{ ,@last_usage } -- Days since last-index usage (read); default: 30 (days)
}
Have a look into the "examples.sql" file!
ERRORS AND OMISSIONS EXCEPTED
----------------------------------------------------------------------------------------------------
VERSIONS:
1.0.0 Sep 2019 Jörg Stryk
----------------------------------------------------------------------------------------------------
EOF
Download Latest Version
IxAdder.zip (52.6 kB)
