Query optimization

If you would like to Optimize your SQL Server Query, Tune slow queries in SQL Server and make them run faster  follow the steps in below blog A query in considered to be slow when it is executing for longer duration than expected. Total duration of the query can be broken in to compile time,…

0

Non-yielding IOCP Listener, Non-yielding Scheduler and non-yielding resource monitor known issues and fixes

  Do you see below errors in SQL error along with dumps and stuck? New version of this post in available in This Link   Non-yielding IOCP Listener * BEGIN STACK DUMP: *   05/06/12 03:54:59 spid 0 * Non-yielding IOCP Listener Non-yielding Scheduler * BEGIN STACK DUMP: *   04/16/12 10:09:58 spid 6256 * Non-yielding Scheduler…

10

ASYNC_NETWORK_IO or NETWORK_IO

What is ASYNC_NETWORK_IO  or  NETWORK_IO ? When a query is fired, SQL Server produces the the results ,place it in output buffer and send it to client/Application. Client/Application then fetch the result from the Output buffer, process data  and send acknowledgement to SQL Server. If client/Application takes long time to send acknowledgement then SQL Server…

0

Unable to open large profiler trace files

Profiler filling up space of my system drive……. When you run the profiler trace (GUI) or open the saved profiler trace file your System Drive gets out of space SQL Server profile uses ‘TMP’ Environment variable for storing the .Trc and trace replay files   By default TMP directory points to your system drive, so…

1

The process cannot access the file ‘C:Windowssystem32perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll’ because it is being used by another process.

  SQL Server CU’s update fails with error “ScoException: The process cannot access the file ‘C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll’ because it is being used by another process. —> System.IO.IOException: The process cannot access the file ‘C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll’ because it is being used by another process. ”   In Detail.txt you will find errors like one listed below Sco: Attempting…

1

I/O requests taking longer than 15 seconds to complete on file

SQL Server I/O Bottlenecks ( I/O requests taking longer than 15 seconds to complete on file )   New version of this post in available in This link   Do you see warnings like one below in your SQL Server error log? SQL Server has encountered  x occurrence(s) of I/O requests taking longer than 15…

2

A significant part of SQL Server process memory has been paged out

    A newer version of this post is available in THIS LINK   A significant part of SQL Server process memory has been paged out. This may result in performance degradation. A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working…

3

Troubleshooting SQL Server Memory

First step to troubleshoot SQL Server memory is to identify whether the whether the low memory condition appears to be in MemToLeave, or in the BPool. Note: If you do not know what is BPOOL or MemToLeave. Please read SQL Server Memory architecture before troubleshooting SQL Server memory. Newer version of this post is available…

2

What is Target Server Memory (KB)?

What is Target Server Memory (KB)?   To  super simplify  in conventional memory model  SQL Server calculates something like target1 and target2 pages using below formula                   Target1 = Current committed pages of SQL Server + ( Available Physical Memory – min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2))…

0