Performance Issue
User was running batch for pushing 90000 rows in a database and batch was not moved from last 1.5 hrs.
On login in the server I found response of the server was not good, it was taking more time to execute a simple query as usual. In first glance, it was looking like user job is hogging the resources, as user job spid was in syslogshold and not moved from long time.
We do some analysis and finally found the cpu usage for server was 100%. ( I used sp_monitor). I concluded that this high cpu usage is slowing down the server performance.
The next task was finding the query which was taking more cpu time. As server was on 15 version, I ran the below sql querry for mon tables for getting the high cpu usage.
select top 10 s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStatement s, master..monProcessSQLText t where s.SPID = t.SPID order by s.CpuTime DESC
We asked application team to check the reported spid and if possible, please abort the tran. There was select queries which were taking maximum cpu . As they requested us to kill, we aborted/killed from data server.
After few seconds, data server cpu started fluctuating from 50 to 100% and finally it was below 50%.
Application batch of 10K inserts moved very quickly and finally issue resolved.
You can get full details on MDA queries @ http://sybaseblog.com/sybasewiki/index.php?title=Category:MDA_Table_Query
Thanks.
Check the Sybase Wiki @ sybasewiki.com
Anurag has more than 6+ years of experience in Sybase Database Development .His Area of expertise includes Performance, Query Optimization, Cost Optimization, TSQL Development. He is also involved in Consultancy to Financial Firms for Database Implementation and Maintenance. He has supported many Global Financial firms.