SQL Server Performance

Wait time on server replies - Issue with several statements

Discussion in 'ALL SQL SERVER QUESTIONS' started by foad reddahi, Mar 12, 2013.

  1. foad reddahi New Member

    [IMG]

    A problem on one of our production enviroment is the following:
    We are able to run a stament for several times in 13 of 14 seconds, after a coiuple of times the execution is suddenly 40 secs, afterwards we can execute the statement again in 13 to 14 seconds.

    It shows in trial 4 where the execution time is 49 seconds.
    Statements are being executed locally on the database server.

    - Indexes have been rebuild, stats have been updated, All data is being extracted from cache
  2. Shehap MVP, MCTS, MCITP SQL Server

    For such cases, it is usually related to what is called Stress powers of your T-SQL query where it can’t afford adequate no of concurrent users or in other words consume huge volume of IO or CPU resources ending up with a culprit point of performance just like you case if no adequate resources are available for its execution ..

    I do prefer to read more about that regard at my T-SQL performance Guidelines blogs:

    http://www.sql-server-performance.com/2012/sql-tuning-tvf-scalar-functions/
    http://www.sql-server-performance.com/2012/sql-server-t-sql-tuning-temp-table-union/
    http://www.sql-server-performance.com/2012/sql-server-t-sql-tuning-not-in-and-not-exists/
    http://www.sql-server-performance.com/2013/sql-server-index-tuning/

    Also if you need more help regarding your query, please share it here with its query execution plans and IO statistics by using SET STATISTICS IO ON at the beginning of your query
    foad reddahi likes this.
  3. foad reddahi New Member

    Thanks,

    i could pinpoint an issue. When the query is running 40 sec, i see that the page fault delta counter jumps up to 2000/3000, where in a normal state, the value is around 20
  4. Shehap MVP, MCTS, MCITP SQL Server

    This prompts more the opinion that the query consumes much IO resources which means it will be impacted tangibly if no enough IO resources are available for execution , I do recommend to check the last article regarding index Tuning as perhaps might the query needs for better indexes to mitigate IO cost exists within query execution plan

    Let me know if any further help is needed
    foad reddahi likes this.
  5. foad reddahi New Member

    Thanks, i think i found the issue or at least a solution.

    The page file was server managed, i changed the configuration, divided the page file over the 4 available partitions, rebooted and everything is running fine again.

Share This Page