Big Trys and Small Trans

The catch phrase around the office these days is “Big Trys and small Trans.”  This came about as we were review stored procedure changes and I two suggestions.  First, that the entire procedure has a try…catch around it and second that the transaction be as small as possible.

Big Try

We’ve got standards in place [...]

May 20th, 2010 | Category: SQL SQLPerformance, SQL2005, SQL2008, Tuning | Leave a comment

SQL Performance Trouble Shooting – the basics from Microsoft

Check Activity Monitor

Anything running?
Blocking/wait times
Goto Locks and check lock strategy

SQL Error Log

Any errors? If so investigate

Resource utilizations

DMVs

sys.dm_exec_query_stats

Look at: Total physical reads, logical reads, number to executions, total time, (divide those two to get average)
Join with sys.dm_exec_sql_text

Performance Monitors

Processor Time
Memory
SQL Server Statistics

Batch requests/sec
Compilations
Recompilations – high CPU time (use fully qualified name to avoid recompiatoins)

SQL Wait stats
SQLServer [...]

May 12th, 2008 | Category: SQL, SQL SQLPerformance, Tuning | Leave a comment

Cognos 8 Performance Tuning

Cognos 8 Services Level:

Adjusting for peak vs. off-peak usage may benefit from changing the properties for:

• ReportService – used for interactive usage (user driven, on-demand)

• [...]

April 11th, 2007 | Category: Cognos, Tuning | Leave a comment