SQLTeam.com | Weblogs | Forums

SQL causing spikes


#1

One of our customers is seeing CPU spikes on the database machine

We were able to isolate it to one specific query

SELECT /* +LEADING(s pstamp acct) */ DISTINCT acct.island_anchor_uid
FROM SUB_TABLE subscr,P_TIMESTAMP pts,ACCT_TABLE acct
WHERE subscr.app_id = ':FTS_SYNC' AND pts.pdbts > subscr.lpd
AND pts.pdeleted = 0
AND acct.exp_obj_uid = pts.puid
AND acct.app_id = subscr.app_id
AND acct.state = '2'
AND pts.pdbts > acct.led;

The tables involved are small (from a few rows in subscription table to about 30,000 rows in ACCT_TABLE and 91,000 rows in the P_TIMESTAMP table)

What is also interesting is that there seems to be an inflexion point beyond which the spikes are seen. The same query was being executed without issues for an hour and after that, it started showing the spikes. We were also able to reproduce the spike by stopping the sync and manually executing the query.


#2
  1. is this for Microsoft SQL server?
  2. is this query part of a stored procedure?
  3. do you have indexes on the columns you are joining on?
  4. do you have indexes on the columns you are filtering on?
  5. Why are you filtering before joining the tables?

#3

\6. What's the rate of change of data in the two tables?