Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Sybase

Posted Mar 3, 2009

Finding slow running queries in ASE 15

By DatabaseJournal.com Staff

by Jeffrey Garbus

Introduction

Fixing a performance problem isn’t usually difficult, once you’ve found it.

According to studies, 80% of the mean time to resolution in performance is the identification of the root cause. When you consider that other studies have shown that 24% of IT staff time is devoted to performance issues, the math becomes very simple: eliminate 80% of the 24% and you get back 20% of your staff time.

There are several different places you have to look when you’re evaluating a performance problem. First, you have to verify that the problem is within the database / database server. In today’s multi-tier environments, this can be nontrivial. There is at least one good tool out on the market that will identify which tier is actually responsible for the most elapsed time, but for purposes of this article, we’ll assume you’re positively identified issues in the data tier. (Drop me an email for my personal take on application performance monitoring tools!)

If you are fortunate enough to know which queries are problems, most of the time you’ll use “set showplan” output to analyze the query, find out which index it is or isn’t using, and act accordingly. Analyzing showplan output is beyond the scope of this article (it would make a good series, though), but as with each new ASE release, ASE 15 showplan output has gotten yet easier to read (and if you like, you can even get it on xml format for later storage and parsing).

In environments though of even light-to-moderate complexity, sometimes all you know is that users are complaining of general slowness. In the past, it has been nontrivial to identify specifically what was slowing the users (or the system) down. Is it a single specific query? Is there one query, which is blocking a class of users? A query which is running in 1.2 seconds, which perhaps doesn’t register high enough on the pain scale to rate a complaint call, but which is running thousands of times per minute (or second!) and is using 43% of resources? If it’s specific user(s), what are they doing?

If you are responsible for tuning, you’ll first try to isolate the target query. Prior to doing this, though, you may want to start at a high level, look at sp_sysmon output to make sure it’s not a system issue, and then try to track down overall problem queries.

With the advent of mid-12.5.x, and enhance in ASE 15, Sybase introduced the MDA tables, which after a relatively simple install, enables the DBA to identify what queries are running, what they’re doing, what is holding them up / speeding them up, This has been an amazing advancement over prior tuning work.

In addition, for ASE 15, Sybase has introduced the ability to collect query metrics, which enables you to track queries and query performance for your session, and if you have the sa_role, for another login’s session.

Identify the problem – solve the problem – verify the solution. This will make friends fast.

Identifying problem queries

The first step in solving a problem is identifying the root cause. Sometimes your mission is vague: Everything seems slow today. You might start looking at sp_sysmon output (WAY beyond scope, but check out the Administration Guide or pick up “Administering ASE 15” from Wordware publishing for detail). The sp_sysmon output will validate the environment at the macro scale (i.e. no server-wide bottlenecks at CPU, IO, Network, Memory / Cache, etc.). The next step is to find the problem queries.

At that point, you may have specific information: “The order entry application is slow.” If you are very lucky, you’ve got only a few screens to look at, a few developers to interview regarding what SQL or which stored procedures are associated with the screens, and specific sets of queries to look at.

On other occasions, you hear, “Most of what I’m working on is slow,” and if you inquire further, you hear, “I think I was working in…” and wonder if you’re going to find the root of the problem.

ASE 15 now offers solutions to each problem: When you don’t know where the problem is, you can use the MDA tables to find out what’s taking up the time on your server. When you know which login is causing the problem, you can use query metrics to find out what the login is doing. In addition, once you’ve identified the problem queries, you can use the updated showplan to identify what the server is using as a query plan.

MDA tables

The Monitoring and Data Access (MDA) tables were added in ASE 12.5.0.3 to help identify what system resources are being used for. They are virtual tables in the master database. There was an install process and a bit of configuration which was required before they would be usable.

Monitoring has turned out to be so important that with ASE 15 the MDA tables are installed as part of the buildmaster script (i.e. at installation time).

The MDA tables enable you to identify what queries are running, what resources they are taking up, and what the queries are waiting for (for example, network, io, or locks to be released).

The complete list of MDA tables in 15.0.2 follows:

monCachePool monProcessLookup
monCachedObject monProcessNetIO
monCachedProcedures monProcessObject
monCachedStatement monProcessProcedures
monDataCache monProcessSQLText
monDeadLock monProcessStatement
monDeviceIO monProcessWaits
monEngine monProcessWorkerThread
monErrorLog monState
monIOQueue monStatementCache
monLicense monSysPlanText
monLocks monSysSQLText
monNetworkIO monSysStatement
monOpenDatabases monSysWaits
monOpenObjectActivity monSysWorkerThread
monOpenPartitionActivity monTableColumns
monProcedureCache monTableParameters
monProcedureCacheMemoryUsage monTables
monProcedureCacheModuleUsage monWaitClassInfo
monProcess monWaitEventInfo
monProcessActivity 

There are other, more exhaustive, dissertations available on the Sybase web site and across the internet on using these, but here are a few quick and easy things you can do with them. If you have trouble accessing the MDA tables, ask your DBA too add the “mon_role” to your login.

MDA tables at the application level

Two tables in particular will help identify the SQL running in the application: monProcessSQLText and monSysSQLText.

monProcessSQLText

shows currently executing SQL

monSysSQLText

shows recent, completed SQL

monSysStatement

More information on completed SQL statements

Let’s say access to a particular table is slow; what SQL accesses the table?

select * into #sqltext from monSysSQLText
select * from #sqltext where SQLText like '%pt_sample%'
/* Note: after you've selected the data once, ASE assumes that you don't want to see it again. 
A second select from the same table will get you successive data. If you want to access it again 
later, you may want to select into a temp table... and, this is good information to keep historically*/
SPID KPID ServerUserID BatchID  SequenceInBatch SQLText
26 1900573 1 27 1 'select * from pt_sample s  pt_tx t where t.id = s.id'

And, now how about some statistics on the statement?

SPID,KPID,DBID,ProcedureID,PlanID,BatchID,ContextID,LineNumber,CpuTime,WaitTime,MemUsageKB,PhysicalReads, LogicalReads,PagesModified,PacketsSent,PacketsReceived,NetworkPacketSize, PlansAltered,RowsAffected,ErrorStatus,HashKey,SsqlId,StartTime,EndTime

25, 1900573,1,1280004560,30,12,2,0,0,0,684,0,0,0,0,0,2048,0,0,0,0,0,2008-11-15 10:03:14.793,2008-11-15 10:03:14.793

You can see:

  • Number of logical I/Os
  • Number of physical I/Os
  • Number of network packets sent/received
  • Number of milliseconds of ‘waiting time’ during statement execution
  • Start time & end time of execution

There’s a wealth of other information you can glean from the MDA tables including unused indexes, table utilization, physical io, table activity, heavily utilized tables, and wait states (indicating what applications are waiting for). This is worth taking the time to understand.

Tip: There is a static configuration parameter, ‘max SQL text monitored’, which limits the size of the SQL statement that you can see (that is, it truncates past this limit). You should try 16384 as a value. This increases the amount of memory used by the server, but should give you the ability to look at your entire query most of the time.

Query Metrics

Query metrics allow you to measure performance for your session using the sp_metrics stored procedure. This allows you to track:

  • CPU execution time, elapsed time
  • Logical I/O (found in cache, cached by async prefetch)
  • Physical I/O (regular I/O, async prefetch)
  • Count - # of times query executed
  • Tracks min, max and avg (less count)

This is likely to replace the statistics io and statistics time session settings.

To capture metrics,

1)  You need to enable the metrics capture at the server level:

sp_configure "enable metrics capture", 1      /* note that this defaults to zero */

2)  You then need to enable metrics capture for your session:

set metrics capture on

3)  Then you’ll flush the metrics from the aggregation to the system view, sysquerymetrics using the sp_metrics stored procedure:

sp_metrics ‘flush’

4)  And finally, you can select from the sysquerymetrics table:

select * from sysquerymetrics

Using sp_metrics

The sp_metrics stored procedure is used to collect and retain groups of captured session metric data.

sp_metrics [flush | backup | drop | help], @arg1 [, @arg2]

Query metrics are captured in the default running group (the running group with a group id (gid) of 1) in the sysquerymetrics view. Note that the sysquerymetrics view contains a self join & decoding of the sysqueryplans table, which contains abstract query plan information (among other things).

sp_metrics has built-in help:

sp_metrics ‘help’ /* in case you forget syntax or the stored procedure arguments.*/

(Output: )

  • Command list:
  • help - get help on a command
  • flush - flush all metrics from memory to system tables
  • drop - drop a metric ID or a group of metrics
  • backup - move the metrics in the default group to a backup group
  • filter - delete metrics records based on filtering condition
  • show - display the number of records in each metrics group

You can move metrics to a different group using the “backup” parameter.

sp_metrics 'flush'

Flushes all metrics from memory to system tables. If you skip this step, the information you need may not yet be in the view

sp_metrics 'drop', '2', '5'

Drops the groups of saved metrics. @arg1 is an integer in char form representing the first metrics group to drop in the range (must be a valid group), @arg2 is an integer in char form representing the last metrics group to drop in the range

sp_metrics 'backup', '6'

Saves metrics for future perusal. This example moves the metrics in the default group (i.e. what is currently running) to group #6. @arg1 is an integer in char form representing group number. It must be higher than 1 (1 represents the current group)

Application tracing

You can trace all of the SQL text for an existing session to a physical file using the set tracefile session option. This is great for a situation when you are exploring complaint s from a specific user and want to find out what the user is doing. Use it like this:

Set tracefile FILENAME [for spid]

And disable it like this:

set tracefile off [for spid]

For example,

Set tracefile ‘/opt/Sybase/tracefiles/2008 1101.trc12’ for 12

... will write all SQL for server process ID 12 to the file at path ‘/opt/Sybase/tracefiles/2008 1101.trc12’. If you don’t specify a spid, you will record SQL for your own session.

This will store SQL as well as query plans (showplan output), statistics (statistics io output), show_sqltext (below!), set option show, and/or dbcc traceon (100) output.

You must either have the sa or sso role in order to run “set tracefile”, or you must have been granted “set tracefile” permission. Note that the tracefile will be on the server machine, not on your client.

set show_sqltext

You can print the SQL text for ad-hoc queries, stored procedures, cursors, and dynamic prepared statements using set show_sqltext. You do not need to enable the set show_sqltext before you execute the query (as you do with commands like set showplan on) to collect diagnostic information for a SQL session. You can do it when you get the “things are running slow” telephone call.

Before you enable show_sqltext, you must first use dbcc (dbcc traceon(3604)) to instruct the server to send the output to your terminal instead of to standard output (i.e. to the console of the session that started up the ASE instance).

The syntax for set show_sqltext is:

set show_sqltext {on | off}

For example, this enables show_sqltext:

set show_sqltext on

Once set show_sqltext is enabled, Adaptive Server prints all SQL text to standard out for each command or system procedure you enter. Depending on the command or system procedure you run, this output can be extensive. This is a good command to run in conjunction with the set tracefile option.

Summary

Every application encounters the occasional performance problem. (Some systems encounter problems more occasionally than others).

There are a variety of tools that have been around for a while (for example, sp_sysmon) which will enable you to understand server performance from a macro level. However, when it comes to identifying problem queries, or problem logins, it can be more difficult.

With the advent of ASE 12.5, Sybase introduced the MDA tables to identify all server activity. This has been enhanced with ASE 15. In addition, ASE 15 introduced a variety of ways to identify session metrics, SQL, and query plans to help identify issues as they crop up.

Jeff Garbus has 20 years of expertise in architecture, tuning and administration of Sybase ASE, Oracle, and Microsoft SQL Server databases with an emphasis on assisting clients in migrating from existing systems to pilot and enterprise projects. He has co-authored 15 books and has published dozens of articles on the subject. Mr. Garbus is the CEO of Soaring Eagle Consulting, an organization that specializes in assisting businesses maximize database performance. www.soaringeagle.biz



Sybase Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Sybase Forum
Topic By Replies Updated
DB Error Help Prepared Statements sjulian 2 July 5th, 04:58 AM
DB Error Help Prepared Statements sjulian 0 May 31st, 07:19 AM
Test, just a test XRumerTest 0 May 5th, 04:57 AM
Execute Procedure in SQL statement vcs1161 0 August 24th, 07:38 AM