Steve Scheffler evenkiel

SQL Server Performance Tuning Class - Day 4

3 Key Numbers
- How busy is your server
- How hard is it working
- Ho wmuch data do you have

Perfmon SQLServer:SQL Statistics - Batch Requests/sec. Trend this on an hourly basis and break it out by Weekday/Weekend

-- SocietySkins (laptop skins)

sp_BlitzIndex
If you don't create a clustered index then data is stored in a structure called a 'heap'
Usage stats shows you what was in the plan, Op Stats will tell you what was acutally used.
A 'scan' isn't necessarily a full scan. You need to look at the actual execution plan to see

RECOMPILE hints will cause the sproc to not show up in the DMV (Dynamic Management Views)
DMVs and profiler will only catch perf issues for a window of time. They are passive tools that have to be running when the issue occurs
sp_Blitz will look across a wider window of time.
ClearTrace as scriptable (and more powerful) version of SQL Server Profiler.
Recommended practice is to create a Tools database to store scripts like ClearTrace and the BrentOzar scripts. May shops will not allow anything to be installed to master. Plus, master won't be restored typically during a rebuild

~~Write down 3 biggest pain points~~
- "IS" it the database that's slowing things down or environmental? Is perfmon the answer?
- Deadlocks / locking. Should we be running in a differnet isolation mode?
- Audit solution - triggers vs in-app, etc.
~~Gather SQL server stats~~
~~Gather Wait statistics~~

	//
	// Assumes that you are binding to a hidden input element.
	//
	// Bindings:
	// * select2 : passthrough options to the select2 javascript call
	// * selectedItem : object to initially select, or null
	// * itemMapper : defaaults to {id: 'id', text: 'text'}
	// * items : set of items to init the selection list with
	//
	// example:

-#
-# Given a root directory, perform a depth first recursive delete of all subdirectories and files.
-# Necessary b/c for some reason powershell won't always succeed in a recursive delete of folders which contain
-# subfolders
-#
-function RecursiveDelete($theroot) {
-  $children = Get-ChildItem -Path $theroot | where-object { $_.Attributes -eq "Directory"} |% {$_.FullName}
-  foreach($achild in $children) {
-  	if ($achild -ne $null) {
-    	    RecursiveDelete $achild

	#
	# Example powershell script to read in contents of an Excel XLSX spreadsheet using
	# the GemBox.Spreadsheet library (http://www.gemboxsoftware.com/)
	#
	# NOTE: Since GemBox.Spreadsheet is dependent upon .NET 4.0, and since the powershell 2.0
	# tooling is, by default, only .NET 3.5 capable, there's an extra step you need to
	# go through to get your environment ready. See the stackoverflow article here
	# http://stackoverflow.com/a/5069146
	# for more details on how to configure this.
	#