Post reply

SQL SERVER DELAYED RESPONSES

  • February 26, 2017 at 7:39 pm

    #401695

    Good day . I am currently experiencing a SQL Server 2008 related problem. I have a production DB that users interact with via web interface. On the same DB there is background processing happening via a windows service. My issue is that when the windows service goes through significant processing it tends to lock up user requests on the WEB UI. Below is a screenshot of what i believe to be performance problems of my SQL SERVER.
    Problem seems to be access methods page splits and Lock requests / second. Additionally the windows service also maxes out CPU usage.

    Can anyone advise on what options i have to resolve this (ie run the windows service on a replicated dataset) 

     

  • February 27, 2017 at 2:15 am

    #1930428

    Generally, when trying to troubleshoot performance issues, you start with identifying where the issue is coming from. You've done that. Next, before we start talking solutions, we need to understand why there's an issue. Rather than immediately say, "Yes, move that to a second server" which means setting up an Availability Group or replication or buying a 3rd party solution like SIOS, and all the added overhead, costs, maintenance, etc., that this entails, we need to understand what these processes are doing? Further, is what they're doing in and of itself problematic, or are there other tuning opportunities? Most of the time, poor performance comes from problematic code, incorrect data structures, bad/missing/incomplete indexing, or even simply out of date statistics. 

    Before you start setting up secondary servers, I'd recommend drilling down on what each of your sets of processes is doing and if there are tuning opportunities within those sets of processes. The best way to do this would be to capture your query metrics to identify the most frequently called and/or highest cost/longest running queries. Identify if there are tuning opportunities within those queries. Then, possibly look to offloading processing to a secondary server if no tuning opportunities present themselves.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

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