Magic 7 Files

/*********************************************************/
/***              STRYK System Improvement             ***/
/***    Performance Optimization & Troubleshooting     ***/
/***   GNU General Public License (V3, 29 June 2007)   ***/
/***                   www.stryk.info                  ***/
/*********************************************************/

# # #  M A G I C  7  # # #
 
7 Generic Powerful TSQL Scripts to Troubleshoot SQL Performance Problems

PREFACE:

    <<It's not a tool, which fixes a problem, but the fool using it!>> (Jörg Stryk)

    The M7 bunch of scripts should help the EXPERIENCED Troubleshooter to quickly identify bottlenecks
    and to develop/implement appropriate solutions.
    Using these tools in a wrong way WILL cause SERIOUS PROBLEMS or even DAMAGE the database!
    You need to know what you are doing - handle with care!
    If you screw things up, you take the blame.

    <<With great power comes great responsibility>> (Uncle Ben)

    "Generic" means, that the scripts work (more or less) idenpendently of any SQL Server version or application;
    in other words: the M7 cannot care less about if you're running NAV, AX, SP, CRM or whatever; 
    so regard the specific requirements, DOs & DONTs of your database/application.
    Since the M7 scripts were firstly created for NAV[ISION] databases there are few extra things targeting this system,
    which could be ignored on any other database.

    The scripts are non-invasive; thus they are not changing anything in the server/database, 
    except for (M7/1_) which enables "show andvanced options" in "sp_configure" (lines 100-101), comment-out on demand.
    Though, some scripts are generating code to implement changes;
    this code must be copied to a "New Query" to be executed. Handle with care!

    Good Luck!

CONTENT:

	1_QuickCheck_generic.sql
	2_QEP_MissingIndexes_generic.sql
	3_ReScript_SSI_Indexes_generic.sql
	4_IndexUsage_generic.sql
	5_Current_Blocks_generic.sql
	6_Locks_Blocks_generic.sql
	7_TraceCheck_generic.sql
	<DIR> 7_Profiler Templates
	gpl-3.0.txt
	
REQUIREMENTS:

    Microsoft SQL Server 2005 - 2017 [2019]
    On some older Servers you will get errors from trying to read DMV which do not exist there;
    just comment out those sections then.

    Permissions:
    User must be member of SQL Server-Role "sysadmin"

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:

	No installation required.
	Extract "Magic7.zip" to any folder and open the scripts in SQL Server Management Studio.
	
DESCRIPTION:

	M7/1_
	Replace [DatabaseName] (line 17) with name of the database to be investigated.
	Script is reading all kinds of configuration data about the SQL Server Instance and the designated database.
	Further it lists problematic areas to be investigated.
	It enables the "show advanced options" in sp_configure (line 100/101), comment-out on demand.

	M7/2_
	First part is listing "Expensive Queries" (incl. "Execution Plans") which put pressure on RAM and CPU, 
	thus taking long time, causing bad User Experience.
	Change filters on demand (lines 73-76) 
	Second part is listing Missing Index proposals of all databases according to filters in lines 88-91.
	When adding Missing Indexes it is CRUCIAL to maintain a good balance between cost and benefit,
	e.g. Read- and Write-Performance. Indexes improve READ performance, but might degrade WRITE performance.
	Hence, you never should create too many indexes or too big indexes (beware of too many "Included Columns")!
	The generated CREATE INDEX code will name the indexes 'ssi_[HandleNumber]_[yyyymmdd]_[hhmmss]'

	M7/3_
	Replace [DatabaseName] (line 18) with name of the database to be investigated.
	This script is supposed to list/manage the indexes created with M7/2_, thus filtering on indexes named 'ssi%'.
	It will show the usage, comparing Reads and Writes etc. (based on Server Uptime) 
	and generate some code to manage these indexes:
	    - IF NOT EXISTS THEN CREATE INDEX
	    - IF EXISTS THEN DROP INDEX
	    - IF EXISTS THEN DISABLE INDEX
	    - IF EXISTS THEN ALTER INDEX REBUILD
	Be careful with DROP and DISABLE as this could severely DEGRADE performance!
	Regard the specific requirements of your database and application!

	M7/4_
	Replace [DatabaseName] (line 18) with name of the database to be investigated.
	Displays the Index Usage (based on Server Uptime).
	To identify "Unused Index" comment-in the filters in lines 117-119
	Be careful with removing "Unused Indexes" as picking the wrong candidates will DEGRADE performance!
	Regard the specific requirements of your database and application!

	M7/5_
	Displays current blocking conflicts on the whole SQL Instance; displaying "victims" and "culprits" etc..

	M7/6_
	Replace [DatabaseName] (line 8) with name of the database to be investigated.
	Displays cached Locks and Blocks on database objects, including potential "Lock Escalation" problems.

	M7/7_
	Investigate SQL Profiler trace files, which were generated with the templates in correspondig sub-folder.
	Install the appropriate Profiler template first (see Readme.txt), then run your Trace and save the output to file.
	(Basically you could use any Profiler template, in this case you might need to modify the script)
	Assign the Path/Filename to the script (line 17) and execute to group the data.
	Caution: SQL Profiler can have a remarkable "footprint" in the system, DEGRADING overall performance!
	Make sure to filter the trace properly and limit the amount of data recorded!
	Running SQL Profiler permantently could "kill" the Server!

	ERRORS AND OMISSIONS EXCEPTED

----------------------------------------------------------------------------------------------------

VERSIONS:

		0.1.0	Jul 2019	Jörg Stryk
		1.0.0	Aug 2019	Jörg Stryk

----------------------------------------------------------------------------------------------------
EOF