当前位置:首页 > 开发 > 数据库 > 正文

Sybase Best Practices - Commands

发表于: 2013-01-01   作者:cloudzzqiu   来源:转载   浏览:
摘要: 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

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


 

Sybase Best Practices - Commands

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号