August 2, 2007 at 2:08 pm
Need some help with this one.
My company has developed a software suite that includes an MSDE database. We use a common 'meta data' schema for all our solutions and have many customers using this common schema. One (and only one) of those customers is experiencing intermitent issues executing SQL queries to the MSDE database. Before everyone says 'concurrency issue', let me say that we believe we have ruled that out. But first, let me describe the issue.
Our application has an flow progression similar to an ATM. For any given customer the application starts at point A and traverses through the application to point Z at which point the customer is finished and the application returns to point A and waits for the next customer. Through out this process multiple stored procedure calls are made to the MSDE database.
Our issue is that at some point in the day, for one particular customer, out of the blue our application will experience a dramatic decrease in the performance of these queries. Typically, the stored procedures will take 1 to 2 seconds to complete. During this period of slowness, the exact same queries will take 20 - 60 seconds to complete. We added logging into the source code of this customer's application to log the database call to prove that there is indeed a marked decrease in the query performance. This 'slowness' may continue for an hour to an hour and a half at then, just as quickly as it appeared, the 'slowness' will disappear and the speed of the queries will go back to normal completion times. We have not been able to identify any pattern regarding the slowness. The times it ocurrs are varied. The frequency on this issue is low, occuring only a few times per month. All of this is happening on more than one machine for this same customer. No other customer is experiencing this issue except for this one customer yet the one customer sees this issue on multiple machines.
Since our company sells the hardware and software as a solution, we can rule out any differences in hardware, database schema, or stored procedure. We have the same hard disk image that our customer uses and we are unable to reproduce this in our test lab.
My first reaction to this issue was that it was a concurrency violation. However, after receiving a call from the customer, we remoted in to the machine and ran the DBCC CONCURRENCYVIOLATION check via osql.exe. To my surprise the call reported zero concurrency violations across the board. Further we used the DBCC CONCURRENCYVIOLATION(STARTLOG) setting hoping to have the violations written to the Application log but even after a machine experieced the slow performance we found no entries in the Application log reporting concurrency violations. Lastly, we setup a test machine that had the same hardware and software configuration as the machines that exhibit the problem and we intentionally caused multiple concurrency violations so we could compare how the machine responded with what our customer has been experiencing. Even with more than 9999 concurrency violations where we were 1 and 2 over the limit our test machine did not experience the performance degradation to the point where it was as slow as what our customer has been experiencing.
The one test that we have conducted in our test lab that caused a somewhat similar experience was to copy a large group of files (a folder containing 500 MP3s) to a share on the test machine while executing our queries over and over via a test app on the test machine. When we did this, the performance of the queries dropped dramatically in a fashion very similar to what our customer experiences. However, we cannot prove that this type of action is ocurring on our customer's machine when the slowness ocurrs.
If anyone has a suggestion I would love to hear about it. Additionally if more info is needed, please let me know.
Russell Brackett
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply