Post reply

DB Performance is deteriorating over the time

  • February 25, 2011 at 5:32 am

    #232655

    Hi

    We are having an issue in our production database server. Here are the details of the server.

    OS version : Windows server 2003

    SQL server Edition : SQL server 2005

    System memory : 24 GB

    Max server memory : 20 GB

    I understand that 20GB is allocated to SQL server.

    After the server is rebooted, the initial days The DB server works fine and Application performance looks good.

    But after 2-3 weeks of reboot (or restart of Services) the performance for the application is deteriorating over time

    Currently we are restarting the services to address this issue. I feel that the memory is not released properly.

    What could be the reason for this? Please let know the steps to troubleshoot this issue.

    Also we have this issue exists on Cluster DB server. We are failing over the resources to the other node, when we see the performance goes down.

    Thank you very much for your help.

    Best Regards

    Nag

  • February 25, 2011 at 5:46 am

    #1291474

    Way too much unkown here. OK, so performance slows... in what way? Are queries running slower or are you getting fewer queries run? Have you looked at the wait statistics on the machine to understand what is slowing things down? Have you looked a blocking or other resource contention issues? Have you looked at performance tuning the queries? What kind of metrics on performance are you gathering and how are you gathering them? Do you have index maintenance routines running? Do you update statistics on a regular basis? Have you looked at the execution plans for the slowest performing queries? Are you doing crazy stuff like shrinking databases or logs every night or something?

    There's way too much not known to even start to point you in a direction. The fact that performance is better right after a reboot suggests you have some sort of contention. I'd check for long running queries or connections not being dropped and running out of resources that way. Those are just guesses. More data is needed.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 25, 2011 at 7:24 am

    #1291517

    Hi

    Thank you very much for your quick responses

    Actually we have a tool (like site scope) which connects to the application once in 2 hours from different locations. The tool checks the availability and measures the performance. We get the availability 100 % but the performance gets deteriorated over week by week.

    So the same queries are running all the days. As time goes on the response time increases

    There are blocks but they get cleared less than a minute. We rebuild the indexes and update the statistics on regular basis. There are no changes to the application for the last 2-3 months.

    We see some connections which are in sleeping mode.

    Are these sleeping processes impact the performance? How to address these connections issue

    As our application is 24X7 applications, we don't get the opportunity to reboot the services always. Please suggest a way to address this issue without rebooting the server.

    Thanks

  • February 25, 2011 at 7:42 am

    #1291534

    Grant said it all; you'll have to provide more details to get more relevant answers.

    the only thing i can offer is it is my experience that for some specific tables, "regularly" rebuilding statistics is not good enough; I've had busy tables that needed their statistics updated several times a day in order to keep performance optimal.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 25, 2011 at 8:25 am

    #1291562

    I'm not familiar with Site Scope, but it looks like it only monitors the request end, not SQL Server itself. If the tool you're using is the same, then you may not be seeing what's occurring within SQL Server.

    Again, I'm still stuck. You're saying it's running slow, but you don't say what is running slow or what you've done to determine why? Have you looked at execution plans for the slowest running queries? Do you know what the slowest running queries are?

    Sleeping connections just means connections that are there, but not currently active. I wouldn't worry about them unless you see more and more of them over time. It's normal to have some sleeping connections, especially with connection pooling, but it shouldn't be an ever increasing number.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply