Sybase Best Practices - Commands

I have been using Sybase for years during my IT career. There are a few commands useful to measure, troubleshoot, tune your SQLs, table design in different perpectives, which are significantly helpful at work. Here are some notes.

 

Basic development considerations

• How often is the query run?
• How many indexes does the table have?
• Will data in the table be dynamic?
• Is the query complicated?
• How many rows will the related table contain?
• How many columns does the table have?

Useful commands in troubleshooting

• set statistics io
• set statistics time
• set showplan
• update statistics

set statistics io

• Use this command to find out the IO usage, including the cache hit ratio;
• IO us an indicative measurement of performance - estimating the work done;
• Shows the physical and logical IO used in the query;
• Includes any additional IO also from the tempdb.

Output contains

"Table" - The name of the table accessed

"scan count" - The number of times the table is accessed (index and table scan)

"logical reads" - The number of pages accessed from memory

"physical reads" - The number of physical reads

"Total writes for this command" - The number of pages written to disk

set statistics time

• Use this command to find out the SQL Server CPU usage and elapsed time;
• Use to see how much time is spent on CPU.

Output contains

"Execution Time" - Time taken to parse and compile the command

"CPU time" - Time taken to execute each step of the command

"elapsed time" - Total time taken to execute the query

set showplan on

• Use this command to find out what steps and internal settings SQL Server will use to execute the query;
• Indicate whether an index or a table scan is being used;
• Use of work tables (tempdb) is indicated;
• Table orders of joins;
• Use of indexes (full or partial).

Output

"Query plan" - Marks the beginning of each query plan

"Step n" - Sequential # for each step and each statement

"The type of query" - Type of query

"From table" - Indicate the table scan reads

"Nested iteration" - Indicate the execution of a data retrieval loop

"Table scan" - Report when the query performs table scan, or the name of the index if used

"Ascending scan" - Indicate the direction of the scan

"Position at start of table" - Indicate where the scan begins

update statistics

• Ensure run this command before start analyzing;
• Usually run in production for database maintenance.

Useful commands in gathering information

• sp_help
• sp_spaceused
• sp_helptext

sp_help

• Use this command to find out information on any object in the database;
• Detect missing indexes, inappropriate table locking schemes, datatype mismatch;
• Can be used against any objects that can be found in sysobjects table.

sp_spaceused

• Use this command to find out spacing information for an object or a database;
• Detect the volume of table in use