#sqlrally notes from Grant Fritchey’s pre-con

12 May

Baseline baseline baseline – collect system counters to verify a baseline performance metric so future problems are easier to debug

For memory important counters are:
Pages/sec – when windows is under memory pressure pages will be higher than 50
buffer cache hit ratio – OLTP should be > 90 but reporting or OLAP might be skewed
Page life expectancy – watch for flushing events

SSMS -> MGMT -> Data collection sets can gather and offer pre-caned reports

Wait Stats and Queues are most important – use DMOs to collect this data constantly

Profiler Tracing: don’t use GUI on prod (no SQL TV newbs)
-Traces can be exported to script server side traces which are much better

After a trace file is created open it and click the “import performance data” button to select time frames of both performance data sets

Microsoft RML utils

automate reporting off trace files and perfmon data

DBCC freeproccache (NO PROD) forces all recompiles for testing


-See header of DBCC show_statistics for age of stats
-plan re-use doesn’t update stats
-update stats with full scan intensive

-dmv physical stats
-foriegn keys and referential integrity help the optimizer (better performance)

Execution Plans
– %s aren’t all that accurate
– Pipe size counts data flow inbetween operators
– element properties contain estimated vs. actual row counts – if off could be stats prob
– far left most element properties gives you the grade of the plan
– different plans for same queries could mean SET operations are different
– go into scalar operator properties to see the logic that is being performed
– check execution counts on loops (1 row return isn’t always good)
– can view parameter values in properties

– constant scans are space holder for other operators
– multiple seeks could be checking for constraints

If optimization level is FULL focus first on other queries

covering indexes can remove need for costly key lookup

Security to DMO’s is granted by VIEW SERVER STATE and VIEW DATABASE STATE

sql_handle, plan_handle, and session_id are unique for joining dmo’s

these dmo’s contain aggregate data which is good for last 6hrs

[[[[DTA BAD]]]]

common problems
-slow queries
–check for large pipes
–check stats

parameter sniffing is good
-can cause fairly rare problems when data is skewed
—can use Optimize for commands

C.Column *2 = 240 <—-bad query
C.Column = 240/2 <—–good

TVP's report 0% in plan when they really do add cost, tune the query without the parameter

Profiling stmt completed can be very costly but includes more fine grain data
Profiling showplan xml is very cost but very convenient

Hints are bad: but can be used as last resort
– Plan guides
–sp_create_plan_guide — use with exact stmt (no spaces)
–can point a plan guide to an exact plan

Leave a comment

Posted by on May 12, 2011 in PASS, SQL Admin, SQL Dev


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: