evenkiel

SQL Server Performance Tuning Class - Day 4

How to Measure Your Server

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

How Busy is Your Server

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

evenkiel

SQL Server Performance Tuning Class - Day 3

Denver 2015

-- SocietySkins (laptop skins)

Performance Tuning Indexes

• 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

evenkiel

SQL Server Performance Tuning Class - Day 2

Denver 2015

Finding Queries and Bottlenecks

• 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

evenkiel

Load Testing Tool

http://www.loadsterperformance.com

Stack Overflow Architecture

http://stackexchange.com/performance

evenkiel

SQL Server Performance Tuning Class - Denver 2015

Links

• http://brentozar.com/go/cleartrace
• http://brentozar.com/go/blitz
• http://brentozar.com/go/active
• http://brentozar.com/responder
• http://brentozar.com/blitzindex
• http://brentozar.com/askbrent
• http://brentozar.com/blitzcache

evenkiel

SQL Server Performance Tuning Class - Day 1

Denver 2015

Getting ready steps

• 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

evenkiel

//
// 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:

evenkiel

#
# 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

evenkiel

#
# 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.
#