Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Tip 80: SQL Server Performance Tips

• Introduction
• Devices And Databases
• Run sp_who
• Run sp_lock
• Run sp_monitor
• Run sp_sysmon
• Optimize SELECTs
• Set Showplan On

This is a work in progress. It is not a "how to", just a "quick tips" or "idea list". It is also NOT exhaustive.

[Top]

Devices And Databases

• Make a separate data device for each database.
• Make a separate log device for each database.
• Put each device on its own drive.
• Do all that for tempdb as well as your application databases.
• At a minimum, put each log device on a drive separate from the data device.
• Mirror the log device for up to the minute recovery.
• Use named caches and buffer pools for 16K I/O.
• The log works best with 4K I/O... see sp_logiosize.

[Top]

Run sp_who

• spid - server process identification number (use this with the kill command)
• status - state of this task:
  • recv sleep - waiting to read data from the network
  • send sleep - waiting to write data to the network
  • alarm sleep - waiting for an alarm (e.g., the waitfor command)
  • lock sleep - waiting to acquire a lock, or sleeping on a semaphore
  • sleeping - waiting for client input or disk activity
  • running - only one task per engine can be running
• loginame - login associated with this task
• origname -
• hostname - descriptive string passed from the client
• blk - zero, or spid of other task blocking this one
• dbname - name of database currently used by this task
• cmd - command this task is currently running

[Top]

Run sp_lock

• fid -
• spid - server process identification number (use this with the kill command)
• locktype - kind of lock owned by this task:
  • Sh_intent - shared intent, which may be implied by Sh_page
  • Sh_page - shared page: a page is being read and cannot be changed
  • Sh_table - shared table: nothing in the table can be changed
  • Update_page - a lock that is being upgraded from shared to exclusive
  • Ex_intent - exclusive intent, which may be implied by Ex_page
  • Ex_page - exclusive page: a page is being updated
  • Ex_table - exclusive table: a whole table is locked
• table_id - object identification number of the affected table
• page - zero, or number identifying the page that is locked
• dbname - database containing page or table that is locked
• class - information about the cursor holding the lock
• context -

[Top]

Run sp_monitor

sp_monitor

last_run                       current_run                    seconds     
--------                       -----------                    ----------- 
Oct 21 1998  2:17PM            Oct 22 1998  4:17PM                  93606 

cpu_busy                  io_busy                   idle                      
--------                  -------                   ----                      
26(20)-0%                 155(153)-0%               18983(18955)-20%          

packets_received          packets_sent              packet_errors             
----------------          ------------              -------------             
22756(22315)              10711(10332)              2(2)                      

total_read          total_write         total_errors        connections        
----------          -----------         ------------        -----------        
15855(15312)        35308(30443)        0(0)                0(0)

[Top]

Run sp_sysmon

sp_sysmon begin_sample
go

[run your test script here, or just wait while other stuff runs]

sp_sysmon end_sample
go

[Top]

Optimize SELECTs

• Leave unnecessary columns out of the select list.
• Include all possible table joins in the where clause.
• Avoid unnecessary expressions in the where clause.
• Use joins instead of subqueries.
• Use exists and in instead of not exists and not in.
• Use >= instead of >.
• Use procedure parameters instead of local variables in where clauses.
• Use exists instead of count(*).
• Use union instead of or.
• Use indexes to optimize max() and min().
• Avoid data conversions in where clauses that prevent the use of indexes.
• Watch out for NULL versus NOT NULL causing character data conversions in where clauses.
• Use convert() to control where data conversions occur.
• Avoid data conversions involving procedure parameters in where clauses.
• Use set-oriented SQL statements instead of cursor fetch loops.

[Top]

Optimize Cursors

• Check cursor plans using the cursor, not the standalone version of the select.
• Use union instead of or clauses or in lists.
• Explicitly specify for update or for read only.
• Include the of column_name_list when using for update.
• Use the default set close on endtran off to keep cursors open across transactions.
• Avoid multiple connections, open multiple cursors on a single connection instead.
• 
• 
• 

[Top]

Set Showplan On

• For standalone SQL statements that you don't want to execute, use this in SQL Advantage:

  set showplan on
  go
  set noexec on
  go
     ...your SQL statement(s) here
  go
  set showplan off
  go
  set noexec off
  end
  

• To see the plan for trigger logic, you have to execute the SQL statements:

  set showplan on
  go
     ...your insert/update/delete SQL statement(s) here
  go
  set showplan off
  go
  

• To see the plan for procedures called from triggers, you have to copy and paste the procedure logic into SQL Advantage as if it was a standalone SQL batch.

[Top]