Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

SQL Performance Trouble Shooting – the basics from Microsoft

  1. Check Activity Monitor
    1. Anything running?
    2. Blocking/wait times
    3. Goto Locks and check lock strategy
  2. SQL Error Log
    1. Any errors? If so investigate
  3. Resource utilizations
    1. DMVs
      1. sys.dm_exec_query_stats
        1. Look at: Total physical reads, logical reads, number to executions, total time, (divide those two to get average)
        2. Join with sys.dm_exec_sql_text
  4. Performance Monitors
    1. Processor Time
    2. Memory
    3. SQL Server Statistics
      1. Batch requests/sec
      2. Compilations
      3. Recompilations – high CPU time (use fully qualified name to avoid recompiatoins)
    4. SQL Wait stats
    5. SQLServer Memory Manager
    6. Physical Disk – Average Disk Read and write – Less than 10 ms on SAN
  5. Trace files – created default
  6. Event Viewer
May 12th, 2008 | Category: SQL, SQL Performance, 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 si
WHERE so.xtype = ‘U’
AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

May 12th, 2008 | Category: SQL Performance | 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. I’m still trying to get to the root cause.
2. Four SQL 2005 installs on non standard servers in one of our environments.
– one had reporting services with it
3. Log Shipping – I sent up two environments with Log Shipping on four primary servers.
- Two had native log shipping
- Two has SQL Lite Speed Log Shipping
4. A few performance issues that I will post about later.

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