MSSQL 2008 sudden and drastical slow down – IT telephony

15 pts.
Tags:
MSSQL
SQL 2008
SQL Server
Hi,
I'm a newbie, greeting everybody from Hungary, and thanks for your time :) Generally the SQL system runs smoothly. There is a SP which runs two times daily, and it logs the begin/end times. It takes about 20 minutes each time, normally. Suddenly I see that it takes 3-4 hours. Each time the same degradation. Other SPs which have nothing to do with the tables/indexes involved in this SP, suffer from the same degradation, 5-10 times slower, and each slower by the very same factor.
All I could figure out is that this phenonemon has something to do with the IP telephony. When this phenomenon popped up first, the VOIP specialist was installing the VOIP system. Occasionally, when the slow down would come up, somebody would reset the VOIP device, and then the speed goes back to normal. But there are times when this doesn't help. This was the case last Friday, for two days the system was almost unusable. And nobody does know what caused the speed up. 
I'm just a programmer, so I can't be more specific. And I'm sitting at 120 miles from the network, so can't investigate. 
Have you heard/experienced something like that?
Thank you in advance,
Alex


Software/Hardware used:
Windows SBS 2008 6.0 build 6002 Service Pack 2, Windows Server Standard FE , Intel Xeon X3430 2.4 Ghz 4 proc. , RAM 8 Gb

Answer Wiki

Thanks. We'll let you know when a new response is added.

I realize that this question was posted a while ago and that you have probably already corrected this issue, but in case you have not or others are having a similar problem, you should try re-indexing the database tables and updating statistics on tables which generally addresses the type of problem you have described. There is an excellent article with many reader comments at this link (http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables) which describe the steps to take and the benefits.

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    It could be a network issue. VoIP applications tend to use high bandwidths, and a faulty or incorrectly configured device could be saturating your network.
    70,220 pointsBadges:
    report
  • Setirobert
    if it's not a network issue, try recomputing the statistics on the database that is causing the slow down > sp_updatestats
    30 pointsBadges:
    report
  • Kccrosser
    A stored procedure should not be affected by a network issue unless it is accessing files across a network. Usually an SP is performing local database processing. Things to check: 1. What is the CPU and disk usage on the database server? If this server is suddenly saturated due to a new/bad query or other transactions, that could cause performance issues. 2. Are there any virus scanners or other 3rd party products that might be trying to access/scan the database files on the server? (This happens a lot - people forget to configure the server virus scanners to exclude the SQL Server files, and then SQL and the scanners are fighting over file accesses.) 3. Updating the statistics is a good suggestion. However, this usually manifests itself in a persistent slowdown once it occurs. Also, recent SQL Server versions should be automatically updating statistics. 4. One possibility is that your stored procedure is updating/adding so many records in the table that the statistics are actually becoming invalid (or stale) while the procedure is running. This can happen if the procedure is calling many other procedures and running independent transactions. If the index values are non-discriminant (i.e., if you plot the index values on a histogram you get a very spiky graph), sometimes the query optimizer may decide an index is no longer valid until all the stats are updated. It is possible that the VOIP is affecting something, but I think that would only happen if there are VOIP processing elements actually running on the SQL Server box. (Is it perhaps a virtual that is running on the same physical box as the VOIP?) Something that is very odd is that when I go to this page to enter the discussion, I am actually getting some voice traffic coming in over my computer speakers... ??? It has a Northern European voice describing a computer system in what sounds like a podcast.
    3,830 pointsBadges:
    report
  • Denny Cherry
    Does the query use a linked server to access another server on the network?
    66,360 pointsBadges:
    report
  • Sandy60
    Thanks all the answers so far. I see I should write one more thing, as you are -quite naturally- pushing the SQL performance side of the story. Update stats : yes, when the slow down first popped up -and actually lasted for four days- I created an SP, in which I collected all the tables' update statistics. No use, which is not surprising. This whole system had been working for 4 full years prior to the moving to a new place, new server. And even after the beginning to work at the new place, there were no problems from Monday to Wednesday morning. The SP, which I mentioned lasted usually for 20 minutes, that morning still lasted 20 minutes, BUT that evening already 2 hours and 30 minutes. And from on till Saturday evening this lasted. In the meantime with another not so long lasting SP I was experiencing, to see what was the effect of my actions like update stats, so on. NOTHING, guys. Even Saturday evening I was testing, doing something, testing, doing something, when at about 7pm suddenly I could see that the SP ran with good speed. This is a company selling wines in a shop, so they were still working. I called them immediately, asking what the hell was happening, who did something. And they told me that they had had to exit all the programs because the IP telephone specialist had arrived not long ago, and began to do something with the network. Then I immediately called him asking when had he installed the IP tehephone, and he told that on Wednesday at about noon. Maybe that's ridicolous for you, but for me it's quite obvious that there IS connection, just we can't find out what actually is. That's why I ask you, if you have some idea.
    15 pointsBadges:
    report
  • Kccrosser
    You didn't describe what the SP was doing. If the SP was like *most* SPs, it is manipulating data in a database, so it should only be affected by conditions in the database OR conditions on the database server. Ergo - we are thinking database. If your SP was accessing external file systems, or linked external databases (as Mrdenny asked), then the network comes into play. If the SP was solely accessing/updating a local database on the db server, then network/telephony should only affect it if it causes the physical db server CPU, memory, and/or disk to be consumed. That was my question #1 - what is the CPU and disk usage on your server? (That is the first place I look when I hear "slowdown".) If the SP was crossing server network lines or doing any kind of communications, then the telephony could have done almost anything. If it was really screwed up, it could saturate your network routers/bridges causing all network traffic to slow down. (A classic problem on IBM Series 1 systems was that network router configuration parameters were reversed between the GUI and the command-line interfaces. If you used the same parameter order but switched from the GUI to command-line or vice-versa, you could wind up forwarding all internal network traffic through a modem and bring the whole system to a crawl very quickly. Ask me how I know - oops.)
    3,830 pointsBadges:
    report
  • Sandy60
    [...] 4. Carlosdl, Setirobert, Kccrosser, and Mrdenny are helping a member explore an MS SQL 2008 sudden and drastic slow down. [...]
    0 pointsBadges:
    report
  • Sandy60
    [...] 1. Carlosdl, Setirobert, Kccrosser and Mrdenny weigh in on a member’s SQL 2008’s sudden and drastic slow down - IT telephony. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following