Post reply

A strange performance issue

  • May 12, 2008 at 5:31 am

    #185394

    Hi,

    I have a web app, which gets hit about 600 times per minutes. In each hit it performs a DB update query. Normally, the time it takes to perform the query is very fast. However, almost like clockwork, about every 7 minutes, the performance takes a hit and the query can take a couple of seconds to complete.

    I have also noticed that if the rate of hits rises, then the interval of the "performance hit" decreases (i.e., if I get 1200 hits per minute, the performance problem shows up about every 3 minutes). Thus there seems to be a connection between the performance hit and the number of queries performed. Sort of like SQL Server is doing something, which downgrades performance very briefly after every 4000 queries.

    I suspected that the automatic update of index statistics might be kicking in, but turning that off made no difference.

    I'm open for ideas...

    Especially if there are any logs, perfmon counters that might be helpful, please let me know.

    Thanks,

    Jamie

  • May 12, 2008 at 5:56 am

    #813712

    Are there any jobs running regularly?

    Is the DB set to autoclose?

    Is the DB set to Autoshrink?

    At the times things go slow, do you see any unusual blocks or waits in sys.dm_exec_requests?

    Run profiler against the server for a while, see if you can see anything odd running when things go slow.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 12, 2008 at 6:41 am

    #813740

    Do these transactions include lots of data changes? You might be getting recompiles on your procedures. You'll need to follow Gail's advice and get the wait stats.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 12, 2008 at 7:41 am

    #813788

    Thanks for the ideas.

    AutoClose and AutoShrink are both set to False.

    The performance problem is very brief, so it's hard to look for anything while it's happening. I see the problem after the fact in my logs.

    I'm using SQL Server Workgroup Edition, so I think I don't have the Profiler tool.

    Ideally I would like to turn on some trace/logging feature of SQL Server, so I could compare these logs to my own logs. In other words, if my logs indicate that at a specific time (I'm talking about 'seconds' precision) a query took a long time, I would like to look at the SQL Server logs at the same time to see what was going on.

    Does anyone know of any such logging?

    Thanks,

    Jamie

  • May 12, 2008 at 7:54 am

    #813802

    You should have Profiler. You can capture that.

    You can also run Perfmon to capture the counters over time. Output them to a file and then you can use those with the Profiler output to really drill down on the issue.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 12, 2008 at 7:58 am

    #813808

    Does anyone know of any such logging?

    Profiler. 🙂

    Profiler should be included in workgroup. It's just Express that doesn't have it.

    You could also log the sys.dm_exec_requests to a table. Run in a loop with a couple seconds delay and maybe you'll see something. Shouldn't put too much of a load on the server

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 12, 2008 at 7:58 am

    #813809

    Please excuse my ignorance:

    1. How do I run the Profiler?

    2. What counters do you suggest I monitor with Perfmon?

    Thanks,

    Jamie

  • May 12, 2008 at 8:23 am

    #813824

    To open up Profiler you would normally go: Start > Programs > Microsoft SQL Server 2005 > Performance Tools > SQL Server Profiler.

    To Open up Performance Monitor (Which is where you would add the counters) you go: Control Pannel > Administrative Tools > Peformance.

    I hope that helps.

    @SQLvariant

  • May 12, 2008 at 10:07 am

    #813934

    Have you pre-sized the database files? If each hit is doing an insert (to an audit table say) and the db files are full, they will have to autogrow periodically.

  • May 12, 2008 at 2:27 pm

    #814084

    Thanks for the thought.

    The DB was set to an initial size of nearly 2GB and an autogrow rate of 1MB. I changed the autogrow rate to 10%.

    Unfortunately, this didn't make a difference. I'm still seeing a performance degradation every seven minutes.

    Some new info: perfmon shows that the Avg Disk Queue Length for Write spikes at the same time I get the performance problem. Maybe that's a clue...

    Jamie

  • May 12, 2008 at 2:37 pm

    #814089

    jamiejulius (5/12/2008)

    Some new info: perfmon shows that the Avg Disk Queue Length for Write spikes at the same time I get the performance problem.

    Sounds like a reasonable source of the problem. Is there something else writing to the disks at that time?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • May 12, 2008 at 3:05 pm

    #814112

    What about the autogrow for the log file? With that many writes the log file is likely to grow often and if on the same drive as the data files could potentially cause the problem.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 12, 2008 at 4:13 pm

    #814140

    Jamie,

    So then fire up Profiler.

    Get a Trace ready to go.

    about 30 seconds before the query is supposed to run, start the trace.

    Stop the trace as soon as the CPU goes back down.

    Finally go through the SQL Statements captured in the trace file and figure out which one is causing your spike.

    @SQLvariant

  • May 12, 2008 at 4:14 pm

    #814141

    could even be the Transaction logs being backed up or something.

    are your Transaction Logs on different disks than your data\indexes ?

    same thing with TempDB. is it on it's own logical drive ?

    Gregory A Jackson MBA, CSM

  • May 12, 2008 at 5:48 pm

    #814167

    Off the top of my head, I would suspect that you are seeing the checkpoint operation in action.

    See: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/98a80238-7409-4708-8a7d-5defd9957185.htm for additional information on what activities cause a checkpoint.

    How often do you backup your transaction log?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply