August 3, 2007 at 9:20 am
I would attempt to use both the sql profiler and the windows system performance tools to monitor the cpu and disk usage during the day. correlating the slow times in each tools' report may show you the bottleneck.
Do you have any scheduled sql agent jobs? If not, then maybe the msde governor slowing you down, where SQL 2005 express does not have one, (but it does not have the sql agent job scheduler).
[font="Arial"]Clifton G. Collins III[/font]
August 3, 2007 at 9:52 am
I agree with the SQL and Windows monitoring, as this (as described) just screams that you have some external factor specific to that customer that is causing the slowdowns.
Is the database located on a dedicated machine? (And, is there more than one database/server)? Does the customer have a process that is running when they don't expect it to - like a system backup? It's interesting to note that your experiment with copying files while the application was running produced similar results. Obviously, this identifies a key possibility of what could be causing the problem.
Good luck!
Steve G.
August 3, 2007 at 1:15 pm
Could it be the rewriting of a clustered index because of exceeding fill? The rewriting would cause heavy disk i/o and perhaps 'pause' the application until the index was rebuilt.
August 3, 2007 at 3:32 pm
Thanks for the responses.
Clifton: We are contacting the customer to try to schedule some time to have PerfMon run on the machine(s). As far as MSDE governor being the cause, that is the same thing as concurrency violation which I explained we have been able to rule out.
Steve: We also believe that this is something unique to the customer's environment. However, the onus is on us to prove that. At the same time, we don't want to 'put the blinders on' and not look for potential causes in our own software. To answer your questions: Each machine has its own MSDE installation (a named instance - not default instance). For our solution, there is a primary database and an archive database. We 'own' both of those. We don't know much about our customer's environment but are trying to learn more.
Enthusiast: Our understanding is that the SQL engine will have the 'smarts' not to utilize all the system resources while other user initiated actions are in progress. We will do more testing with this to find out for sure.
Thanks for helping out,
Russell Brackett
August 6, 2007 at 4:05 pm
One question that I'm sure you've already considered: Is this happening on all the customers' databases, or is this localized to one machine?
My gut feel is that you're going to need to get to the machine that's causing this while it's happening and look at both MSDE and the windows system for running tasks. You're right that this is your problem - even if it's not. 😛
One final item - does the suspect machine have anti-virus installed?
Steve G.
August 6, 2007 at 11:43 pm
" that at some point in the day, for one particular customer "
-- check it out what time ( Any partucular time ) ?
-- What are the background job get involve during that time ?
-- Run SQL Profiler at that time and capture the log.
-- Inform us about the findings which will help us to take the right action.
Minaz
"More Green More Oxygen !! Plant a tree today"
August 7, 2007 at 12:01 am
What are the settings for auto update statistics, auto close and auto shrink? Any one of those could cause the scenario you've described.
August 9, 2007 at 8:48 am
I agree with Joe. MSDE by default sets Autoshrink ON, which is a good way to kill performance. Definitely make sure Autoclose and Autoshrink are OFF.
Auto update statistics can also be a problem if the database is growing. Consider setting this OFF and running a stats update job during a quiet time, such as the weekend.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply