存档
Sybase ASE自V12.5.0.3引入了MDA表来监控Sybase数据库的性能，ASE15以来MDA的监控能力越来越强。本博客中有关于如何配置MDA表的文章：
MDA中的代理表monProcessSQLText包含当前所有会话正在执行的SQL语句。如果已经配置了MDA，可以通过查询该代理表monProcessSQLText来获得正在执行的SQL语句。
在没有配置MDA监控的环境中，可以使用更加通用的命令来查询当前会话正在执行的SQL语句。dbcc sqltext命令需要sybase_ts_role角色才能执行，过程sp_showplan需要sa_role角色才能执行。
下面提供一个存储过程来查询当前Sybase服务器内正在执行的所有SQL语句。思路是：获得当前使用锁的会话ID(spid)，利用游标遍历得到每个spid的正在执行的SQL语句。
Adaptive Server version 15.0.2 uses row-level locking on system tables to enhance performance.
Versions of Adaptive Server earlier than 15.0.2 used exclusive table locks on system tables while executing data definition language (DDL) and utility commands. The set of system tables Adaptive Server locked depended on the type of DDL operation you executed. If another DDL running concurrently tried to take a conflicting exclusive table lock on the same system table, this DDL had to wait to acquire the lock on any system catalogs. These DDL operations were executed serially.
This methodology impeded performance in temporary databases, where their DDL activity is very high also, and consequently their catalog contention is very high. This limited the Adaptive Server throughput for applications using temporary tables.
Adaptive Server version 15.0.2 uses row-level locking to resolve these issues:
*
System-table contention, caused a bottleneck for many DDLs and utilities.
*
tempdb contention. Because the system tables are locked at the row level, Adaptive Server 15.0.2 eliminates tempdb contention.
*
Shared or exclusive table-level locks while executing DDLs and utilities. Earlier versions converted most system tables to data-only locking (DOL), but still created shared or exclusive table-level locks while executing DDLs and utilities. Using row-level locks for system tables eliminates this contention.
Adaptive Server sets intent locks on catalogs only, which removes potential contention (An intent lock indicates that page-level or row-level locks are currently held on a table.).
*
DDLs and utilities blocking each other. Adaptive Server 15.0.2 allows DDLs and utilities to run in parallel.
Earlier versions of Adaptive Server used table locks to achieve system catalog synchronization. Adaptive Server 15.0.2 uses intent locks for table-level synchronization and row locks for row-level synchronization. Earlier releases of Adaptive Server locked the entire system catalog while performing operations on the object, so a single lock request was made. However, Adaptive Server version 15.0.2 requests locks for all applicable rows while performing operations on the object if there are multiple rows corresponding to an object in a system catalog.
This change means that Adaptive Server 15.0.2 requests more locks to perform the same operation than earlier releases, and increases the number of lock resources the system needs. Consequently, you may need to change the number of locks configuration option after you upgrade Adaptive Server.