SQL Server memory – Internals

SQL Server memory works in different ways in 32-Bit and 64-Bit operating systems.  Let us have a deep dive in this blog on how SQL Server memory works in 32-Bit and 64-Bit systems. We will also see how SQL Server memory managers behave differently on different memory models. Before we jump in to Architecture of…

2

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