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.
"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.
"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).
"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
- Ensure run this command before start analyzing;
- Usually run in production for database maintenance.
Useful commands in gathering information
- 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.
- Use this command to find out spacing information for an object or a database;
- Detect the volume of table in use
Sybase Best Practices - Commands