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

Get Fairly Accurate Table Counts

This is the fast way to get a count of all the data in the tables in a database…

SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)FROM sysobjects so, sysindexes siWHERE so.xtype = ‘U’AND si.id = OBJECT_ID(so.name)GROUP BY so.nameORDER BY 2 DESC

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

Weekly Challenges 5/5/08

Here’s this week’s challenges….

1. Ran SQLIOStress test on all servers in one environment – here’s an overview of what it does – I did graph the results and found that although the configurations where nearly the same on two of our top end servers – the speed was slightly better on one. [...]

May 6th, 2008 | Category: SQL, Weekly Challenges | Leave a comment