May 12, 2008 at 5:31 am
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
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
May 12, 2008 at 6:41 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2008 at 7:41 am
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
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2008 at 7:58 am
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
May 12, 2008 at 7:58 am
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
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
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
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
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
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
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
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
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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