Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2009-12-07 : 14:09:19
|
| Hi,We have a workflow application that has approximately 2000 users. Some of the core tables have ~2 million records while some tables have 30-40 million records (so not a huge database but not super small either). We are getting random timeout events/spans of slowness but I can not find the root cause. Perhaps over a span of 1 week, we'll encounter 2-3 spans that last a couple of hours where the site appears quite slow - especially searches. Here are some of the things we've looked into:1) Site activity: it is actually pretty consistent from day to day. We have 1000s of records modified on a daily basis and I don't see any real correlation between activity and slowness (obviously the site is quick in the middle of the night, but from day to day the activity is about the same and most of the time there is no slowness).2) AdHoc reports: Originally we thought that was the cause. We've added logging to these events and it does not appear to correlate with the random slowness events. Also the size of the reports are pretty small.4) Performance Testing: We ran fairly extensive performance testing on the system and never experienced these timeouts. Overall the searching functionality was fine. We would be able to max out the servers due to load, but in those events, the CPU on the server was pegged and everything started to fail, not just searching etc.3) Although I don't have direct access to the server (we're a vendor), I've been told the CPU on the database server is not spiked during these slowness events.I am running out of ideas. Any suggestions on what to look into? Note: there are other databases on this server but we've been told they aren't experiencing any issues. I don't necessarily buy that, but if anyone has suggestions on where to look next, I would appreciate it.In the end, I can't find any real variable in our server activity that would trigger these slowness events.Thanks,NicNic |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-07 : 14:13:29
|
| The DBA at the customer site should run SQL Profiler to capture any queries that exceed the application's specified timeout value. The DBA also should run the following query and provide it to you to determine what indexes should be added (only consider those over 1000000 impact: http://sqlserverpedia.com/wiki/Find_Missing_IndexesWhat also needs to be done is to verify that UPDATE STATS is being run regularly. We run sp_updatestats with the resampling every night. Their system should also include a job to rebuild the indexes when fragmentation exceeds 50%.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
|
|
|
|
|
timeout troubleshooting