Case study 29: Why Sybase Central freezes and how to solve it.
Platform: All
Sybase ASE version: All
Background story: On opening Sybase Central folders for tables, views and in my case for procedures, Sybase Central freezes. Since I had to detect why Sybase server is not responsive as I would expect, I use this example, to create a pattern for solving other Sybase slow queries problems.
Task: Describe what is happening on opening Sybase Central database procedures list, why it freezes and how to solve it.
Solution: When open Sybase Central, and if You want, for example, to see list of procedures in some database, as shown in following picture:
Sybase Central in that moment issues following query:
select proc_name = max(o.name), group_number = p.number, owner_name = max(u.name), creation_date = convert(datetime, max(o.crdate)), object_id = max(o.id), max(o.crdate)
from pubs3.dbo.sysobjects o inner join pubs3.dbo.sysprocedures p on p.id = o.id
inner join pubs3.dbo.sysusers u on u.uid = o.uid where o.type = 'P' and u.name like '%' and o.name like '%' and o.sysstat2 & 33554432 != 33554432 group by p.id, p.number order by 1
As long as this query takes to execute, Sybase Central will be frozen. It means, that, if Sybase Central is frozen for "unacceptable" time, it means that something is wrong.
In my example, before I solve the problem, Sybase Central, database procedures list, has been opening more then 5 minutes.
So, how I solve a problem? First of all, I had to capture which query is executing. I have been using monitoring tables to achieve that, but also there are one feature introduced in some 15.x release named application tracing, which allows user to capture incoming SQL queries for desired SPID.
Once I did it, I executed that statement through some Sybase client. While executing that query I have been examining plans, statistics (io,time), and noticed some abnormal numbers such as large number of logical reads, large number of scan counts and so on, and even physical I/O activity. Majority of these issues have been concentrated around sysprocedures table which holds near two thousand procedures. What leads me to the solution is the huge difference between actual and expected number of rows in query plan for that table. Although I have never been working with statistics of Sybase system tables,
this information learned me, and show me, that even Sybase system tables should be a subject of statistics updates. Once I updated statistics for sysprocedures table, and run the query, it finished in half of a second! Also, Sybase Central is opening database procedures list immediately.
Conclusion: As several papers about Sybase 15 are saying, "Up-to-date statistics are more critical for good performance in ASE 15 than ever before".
Also, second conclusion is that DBA should take care about statistics of Sybase system tables too.
