Karthick PK 's Blog

SQL Server memory - Internals

2013-03-17T05:56:00Z

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 SQL Server memory if you are planning to make configuration changes or troubleshooting SQL Server memory errors refer below blogs else continue reading this blog

Troubleshooting steps for all SQL Server Memory errors

Trouble shooting working set trim “A significant part of SQL Server process memory has been paged out”

SQL Server lock pages in memory should I use it?

SQL Server memory leak

What is new in SQL Server 2012 Memory

How to set max server memory and min server memory

Let us start with how SQL Server is designed to work on 32-Bit operating systems and then jump to 64-Bit memory model.

1. 32-Bit SQL Server memory architecture

In the Win32 memory architecture, each process has a 4 GB address space. By default, 2 GB of that address space is accessible from user mode(Application like SQL Server) and the remaining 2 GB is accessible from kernel mode

So in 32 Bit windows architecture 2 GB of memory is maximum available for SQL Server.
Note: When /3GB switch is enabled user mode address space becomes 3GB and kernel mode becomes 1 GB. When 32-Bit SQL Server is running on 64-Bit windows (WOW64) It gets 4GB of user address space .It can also leverage AWE on WOW64 mode and can use more than 4GB.

SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool

Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during start up as below.

MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space (By default 256 MB and can be controlled by -g).
Stack size =512 KB per thread for 32 Bit SQL Server
I.e = (256 *512 KB) + 256MB =384MB

Additional space to load Dll’s= 256 MB from SQLServer2000. This space is used to store

1. COM objects

2. Extended stored procedure

3. Memory allocated by linked servers (loaded in process ) or other Dll’s loaded in SQL Server proce

4. Memory allocated by SQL Server memory manger if the allocation size in greater than 8K and need’s contiguous memory (Multiple_pages_kb).

5. SQLCLR

Note: Additional space to load Dll’s can be modified using -g startup parameter.

on any machine with less than 4 processors the Maximum worker Thread’s is
always 256 by default (unless we change the value using SP_configure)

SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL- BUF structures”

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

To ensure MemToLeave allocations are contiguous SQL Server reserves MTL first, then all the buffer pool regions and finally free MemtoLeave region.

What is in BPOOL?
Data Pages/Index pages and Memory allocated by SQL Server memory manager, which is accounted for any of below memory clerk’s. If the memory

request is <= 8 KB

CACHESTORE_PHDR
CACHESTORE_XMLDBTYPE
CACHESTORE_EVENTS
MEMORYCLERK_SQLSTORENG
MEMORYCLERK_XE
CACHESTORE_XPROC
OBJECTSTORE_SNI_PACKET
CACHESTORE_BROKERRSB
OBJECTSTORE_SERVICE_BROKER
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT
MEMORYCLERK_XE_BUFFER
CACHESTORE_XMLDBATTRIBUTE
MEMORYCLERK_SQLOPTIMIZER
USERSTORE_OBJPERM
USERSTORE_TOKENPERM
CACHESTORE_FULLTEXTSTOPLIST
MEMORYCLERK_SQLGENERAL
MEMORYCLERK_SQLHTTP
CACHESTORE_NOTIF
CACHESTORE_XMLDBELEMENT
OBJECTSTORE_LOCK_MANAGER
MEMORYCLERK_SQLBUFFERPOOL
MEMORYCLERK_SQLSOAP
MEMORYCLERK_TRACE_EVTNOTIF
CACHESTORE_CONVPRI
MEMORYCLERK_QSRANGEPREFETCH
CACHESTORE_BROKERREADONLY
MEMORYCLERK_SQLCLRASSEMBLY
MEMORYCLERK_SOSNODE
CACHESTORE_STACKFRAMES
MEMORYCLERK_SQLCONNECTIONPOOL
MEMORYCLERK_SQLSERVICEBROKER
CACHESTORE_OBJCP
MEMORYCLERK_SQLQUERYPLAN
OBJECTSTORE_SECAUDIT_EVENT_BUFFER
OBJECTSTORE_LBSS
MEMORYCLERK_FULLTEXT
CACHESTORE_TEMPTABLES
CACHESTORE_BROKERTBLACS
MEMORYCLERK_SQLXML
USERSTORE_SXC
MEMORYCLERK_BHF
CACHESTORE_SQLCP
CACHESTORE_SYSTEMROWSET
USERSTORE_SCHEMAMGR
MEMORYCLERK_SQLQUERYCOMPILE
CACHESTORE_BROKERTO
CACHESTORE_BROKERKEK
MEMORYCLERK_SNI
MEMORYCLERK_FULLTEXT_SHMEM
CACHESTORE_BROKERUSERCERTLOOKUP
USERSTORE_DBMETADATA
CACHESTORE_VIEWDEFINITIONS
MEMORYCLERK_SQLQUERYEXEC
CACHESTORE_BROKERDSH
MEMORYCLERK_SQLSOAPSESSIONSTORE
MEMORYCLERK_SQLQERESERVATIONS
MEMORYCLERK_HOST
MEMORYCLERK_SQLCLR
MEMORYCLERK_SQLXP
MEMORYCLERK_SQLUTILITIES

What is in MTL(Non-Bpool)?

COM Objects
SQL Server CLR
Memory allocated by Linked Server OLEDB Providers and third party DLL’s loaded in SQL Server process
Extended Stored Procedures:
Network Packets
Memory consumed by memory managers. If the memory request is > 8 KB and needs contiguous allocation.

What is BUF structures?
SQL Server maintains a BUF structure for each page. This structure is used to track status information associated with each buffer, such as the buffer latch, a pointer to the actual 8 KB page, status bits that indicate whether the page is dirty, has an IO in progress etc.
Note: When AWE is enabled BUF structure is maintained for entire RAM to adjust Max server memory with out restarting SQL Server.

What is PAE?
PAE is the added ability of the 32 Bit processor to address more than 4 GB of physical memory. Enable /PAE
in boot.ini to make operating system take advantage of physical memory over 4GB in system.

What is AWE in SQL Server?

When AWE is enabled, SQL Sever 32-Bit will be able to address more than 4 GB of physical memory using AWE allocator API’s.

Note: In 32-Bit SQL Server Only data pages an index pages can be placed in AWE memory. So the memory available for other SQL Server memory objects is still limited to user address apace.

Memory allocated using AWE allocator API’s are not part of Process working set ,hence can not be paged out and not visible in as private bytes or working set in task manger and perfmon.
Lock pages in memory privilege is required for startup account of SQL Server to use AWE allocator API’s.
In 64-Bit systems sp_configure ‘awe enabled’ does not have any functionality, If you have LPM privilege for Startup account of SQL Server AWE allocator API’s are used to allocate memory.

What is /3GB Switch?

/3GB switch is used in the Boot.ini file.

When we enable /3GB. User address space of SQL Server or any application that uses IMAGE_FILE_LARGE_ADDRESS_AWARE will increase to 3GB restricting kernel-mode address space to 1GB.

When the physical RAM in the system exceeds 16 GB and the /3GB switch is used, the operating system will ignore the additional RAM until the /3GB switch is removed. This is because of the increased size of the kernel required to support more Page table Entries

How AWE works and What are AWE allocator APIS?

AWE API’s enables programs to address more memory than the 4 GB that is available through standard 32-bit addressing.

How AWE APIS’s are used ?
Allocate address space for mapping AWE pages.
ADD=VirtualAlloc(lpaddress,size,MEM_RESERVE | MEM_PHYSICAL,PAGE_READWRITE);
Allocate physical memory which can not to paged.
bResult=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap,aRAMPages);
MAP a view of that page into your address space,
bResult=MapUserPhysicalPages(ADD,sizemap,aRAMPages);

2. 64-Bit SQL Server memory architecture

In the 64-Bit windows each process gets up to 8 TB of address space, Hence there was no need for SQL Server to leave certain amount of addressable memory for Non-Bpool allocations.

There are three types of memory model’s in 64-Bit SQL Server.

1. Conventional – Normal physical page size (4 / 8KB),memory can be paged, dynamic
2. Locked – Normal physical page size (4 / 8KB), Bpool can not be paged, dynamic, Requires startup account of SQL Server to have "Lock pages in memory" privilege,Memory is allocated by using Address Windowing Extensions (AWE) API’s
3. Large – Large physical page size ( > = 2MB), Non-pageable, static, Memory is committed at startup,”Max server memory” is recommended, requires startup account of SQL Server to have "Lock pages in memory" privilege

Memory calculations in 64-Bit SQL Server are straight forward.
SQL Server calculates the size of RAM during the startup and reserve it , minimum of (reserved space, “Max server memory”) is used as Bpool.

Similar to 32-Bit SQL Server, there will be memory allocations outside Bpool in 64-Bit SQL Server , which is called as Non-Bpool allocations.

Who allocates memory outside Bpool?

1. COM Objects
2. SQL Server CLR
3. Memory allocated by Linked Server OLEDB Providers and third party DLL’s loaded in SQL Server process
4. Extended Stored Procedures:
5. Network Packets
6. Memory consumed by memory managers. If the memory request is greater than 8 KB and needs contiguous allocation.
7. Backup
8. Memory for threads (stack size is 2 MB in 64-BIT SQL )

Max server memory controls only the Bpool, it doesn’t control Non-Bpool allocations, this is the reason for SQL Server’s memory usage being greater than "Max Server memory".

Key points:

1. When “Lock pages in memory is used” operating system can not page out Bpool, Non-Bpool allocations can still be paged.

2. SP_configure “awe enabled” option doesn’t have any use in 64-Bit SQL Server.

3. “Max Server Memory” limits only Bpool, hence SQL Server memory usage will be greater than “Max server memory”

4. If your operating system is windows2003 (Windows2008 is your call) make sure you cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc.

Note: Above architecture is applicable till SQL Server 2008 R2. SQL Server 2012 (Denali) has made many changes to the memory manager to govern the SQL Server memory consumption in efficient way compared with earlier versions. To learn SQL Server 2012 Memory refer THIS LINK

Query optimization

2012-11-22T06:08:10Z

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, CPU time and Wait time.

Before you start troubleshooting the query which is running for longer duration, Identify if the query is slow because it is long waiting (or) Long running (or) Long compiling.

Compile time: Time taken to compile the query. compile time can be identified by looking at the

1. CompileTime="n" in XML plan

2. SQL Server parse and compile time when Set statistics time on is enabled.

CPU time: Time taken by the query in CPU (Execution time – (compile time+ wait time). CPU time can be identified by looking at the

1. CPU column in profiler.

2. CPU time under SQL Server Execution Times when statistics time on is enabled.

Execution time: Time taken by the query for complete execution ( Execution time +CPU time +Wait time). Total duration of the query can be identified by using the

1.Duration column in profiler

2. SQL Server Execution Times, elapsed times when statistics time on is enabled.

What is long waiting query?

A query is considered to be long waiting query, when it spend most of its time waiting for some resource.

How to identify if the query is long waiting?

Long running query can be identified by comparing the CPU and duration column in profiler (or) CPU and elapsed time when statistics time on is set .

When a query is waiting for a resource (such as lock, network I/O, Page_I/O Etc) it will not consume CPU. So if you see duration being higher than CPU (Difference between Duration and CPU is wait time),It indicates that the query has spent large amount of time waiting for some resource.

Let us see an example of long waiting query. I have collected profiler trace while executing the query.

set statistics io on

set statistics time on

--Place your query here

select top 10000 * from a

set statistics io off

set statistics time off

Look at the Duration and CPU column in the profiler Cpu=256 and duration =1920. So this query has spent majority of time waiting for some resource.

Look at the output of statistics time and statistics I/O in above image.

SQL Server has spent only 2 milliseconds compiling the query and 256 milliseconds on CPU, but the overall duration was 1920 milliseconds so the query has spent maximum time waiting for some resource.

Identify the resource in which this query is waiting on using one of the steps listed below.

1. Look at the wait type column of the sysprocesses for the spid which is executing query while the query is executing.

2. If there is no other activity on the server collect sys.dm_os_wait_stats output before and after the query execution and identify the wait (Will not help in tuning queries running for short duration)

3. Collect XEvent to gather the wait stats of individual query.

Once you identify the resource in which the query is waiting on tune the resource. Most of the times queries would be slow waiting for below resource.

PAGEIOLATCH_* or Write log: This indicates I/O resource bottleneck follow the detailed troubleshooting steps mentioned in This Link to fix the I/O bottleneck. If you find SQL Server spawning excessive I/O Create necessary indexes.

a. Logical reads + Physical reads in statistics I/O output (Refer above image) or Reads and writes in profiler will indicate the I/O posted by this query. If you see very high reads for query compared with the result rest retuned by query it is an indication of missing indexes or bad plan. Create necessary indexes (You can use DTA for index recommendations.).

PAGELATCH_*: This waittype in sysprocesses indicates that SQL Server is waiting on access to a database page, but the page is not undergoing physical IO.

a. This problem is normally caused by a large number of sessions attempting to access the same physical page at the same time. We should Look at the wait resource of the spid The wait_resource is the page number (the format is dbid:file:pageno) that is being accessed.

b. We can use DBCC PAGE to identify object or type of the page in which we have the contention. Also it will help us to determine whether contention is for allocation, data or text.

c. If the pages that SQL Server is most frequently waiting on are in Tempdb database ,check the wait resource column for a page number in dbid 2 Ex(2:1:1 or 1:1:2). Enable TF 1118 and increase the number of TEMPDB data files and size them equally (You may be facing tempdb llocation latch contention mentioned in http://support.microsoft.com/kb/328551)

d. If the page is in a user database, check to see if the table has a clustered index on a monotonic key such as an identity where all threads are contending for the same page at the end of the table. In this case we need to choose a different clustered index key to spread the work across different pages.

LATCH_*: Non-buf latch waits can be caused by variety of things. We can use the wait resource column in sysprocesses to determine the type of latch involved(KB 822101).

a. A very common LATCH_EX wait is due to running a profiler trace or sp_trace_getdata Refer KB 929728 for more information.

b. Auto Grow and auto shrink while query is executed.

c. Queries going for excessive parallelism.

Blocking (LCK*): Use the query in This Link to identify the blocking. Tune the head blocker.

Asynch_network_io (or) network IO: Keep the result set returned by the query smaller. Follow detailed troubleshooting refer This Link

Resource_semaphore waits: Make sure there is no memory pressure on the server Follow steps in This Link for detailed troubleshooting.

SQL Trace: Stop all the profiler traces running on the server. Identify the traces which are running on the server using the query in This Link

Cx packet: Set the Max degree of parallelism. But remember Cxpacket wait type is not always a problem.

a. For servers that have eight or less processors, use the following configuration where N equals the number of processors: max degree of parallelism = 0 to N .

b. For servers that use more than eight processors, use the following configuration: max degree of parallelism = 8.Refer This Link

SOS_SCHEDULER_YIELD : Identify if there is CPU bottleneck on the server. This waiting means that the thread is waiting for CPU.

a. SQL Server worker thread’s Quantum target is 4ms which means the thread(worker) Will ( is expected to) yield back to SQL Server scheduler when it exceeds 4ms and before it yields back it check if there are any other runnable threads, If there is any runnable threads then the thread which is in top of runnable list is scheduled and current thread will go to the tail of the runnable list and will get rescheduled when the other threads which are already waiting in SOS Scheduler (runnable list) finishes its execution or quantum. The time thread spends in runnable list waiting for its quantum is accounted as SOS_SCHEDULER_YIELD. You will see this type when multiple threads are waiting to get CPU cycle. Follow trouble shooting the steps mentioned This Link

Important: In SQL Server instances when there more than 1 CPU it is possible that the CPU is higher than the duration. Because CPU is sum of time spend by query in all the CPU’s when choosing a parallel whereas the duration is actual duration of the query.

What is long running query?

A query is considered to be long running query, when it spend most of its time on CPU and not waiting for some resource.

How to identify if the query is long running ?

Long running query can be identified by comparing the CPU and duration column in profiler (or) CPU and elapsed time when statistics time on is set . If the CPU and duration is close than the query is considered to be long running. If the query is long running identify where the query spend the time ,It could be for compiling or post compilation (For executing the query). compare the duration of the query with CompileTime (XML plan compile time (or) SQL Server parse and compile time when statistics time is on refer above image).

High Compile time:

Compare the duration of the query with Compile Time (XML plan compile time (or) SQL Server parse and compile time when statistics time is on).Compile time will normally be in few millisecond . Follow the below steps if you see high compile time

1. Identify if you have large token perm refer http://support.microsoft.com/kb/927396

2. Create necessary indexes and stats. Tune the query manually (or) in DTA and apply the recommendation

3. Reduce the complexity of query. Query which joins multiple tables (or) having large number of IN clause can taking a while to compile.

4. You can reduce the compile’s by using force parameterization option.

High CPU time:

Compare the duration of the query with Compile Time (XML plan compile time (or) SQL Server parse and compile time when statistics time is on). If the compile time is very low compared to the duration. Then follow the below steps.

1. Update the stats of tables and indexes used by the query (If the stats are up to date Estimated rows and estimated execution will be approximately same in execution plan .If there is huge difference stats are out dated and requires update) .

2. Identify if the query has used bad plan because of parameter sniffing (If the ParameterCompiledValue and ParameterRuntimeValue is different in XML plan). Refer THIS LINK to know more about Parameter Sniffing

3. If updating the stats and fixing the parameter sniffing doesn’t resolve the issue it is more likely optimizer is not able to create efficient plan because of lack of indexes and correct statistics. Run the query which is driving the CPU in database tuning advisor and apply the recommendations. (You will find missing index detail in xml plan but DTA is more efficient).

4. If the query which is running longer and consuming CPU is linked server query try changing the security of linked server to ensure linked server user has ddl_admin or dba/sysadmin on the remote server. More details regarding the issue in THIS LINK.

5. Ensure optimizer is not aborting early and creating bad plan. For details refer THIS LINK

6. Ensure the query which is spiking the CPU doesn’t have plan guides (xml plan will have PlanGuideDB attribute. Also sys.plan_guides will have entries) and query hints(index= or (option XXX join) or inner (Join Hint) join).

7. Ensure that SET options are not changed.

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group and post your questions.

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

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

2012-08-21T09:12:00Z

Do you see below errors in SQL error along with dumps and stuck?

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

Non-yielding Resource Monitor

* BEGIN STACK DUMP

* 01/22/09 19:11:16 spid 0

* Non-yielding Resource Monitor

Refer How to analyze Non-Yielding scheduler or Non-yielding IOCP Listener dumps for analyzing the Non-yielding Scheduler, Non-yielding IOCP Listener and Non-yielding Resource Monitor

If you are interested in just finding a quick resolution follow the below steps to get the Non-Yield stack from the dump and check if it is matching with any existing known issues in SQL Server.

To analyze the dump download and Install Windows Debugger from This link

1. Open Windbg

2. Choose File menu –> select Open crash dump –>Select the Dump file (SQLDump000#.mdmp)

3. on command window type
.sympath srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;

4. Type .reload /f and hit enter. This will force debugger to immediately load all the symbols.

5. Type .cxr sqlserver!g_copiedStackInfo+0X20 for SQL Server2005 and SQL Server2008/2008R2 (or) .cxr sqlmin!g_copiedStackInfo+0X20 for SQL Server2012.

6. Type kc 100 and look at the stack to see if it matches with any of the known issues in SQL Server.

Stack 1

sqlservr!COptExpr::DetachPointersIntoMemo

sqlservr!COptContext::PcxteOptimizeQuery

sqlservr!CQuery::Optimize

sqlservr!CQuery::PqoBuild

sqlservr!CStmtQuery::InitQuery

sqlservr!CStmtDML::InitNormal

sqlservr!CStmtDML::Init

sqlservr!CCompPlan::FCompileStep

sqlservr!CSQLSource::FCompile

sqlservr!CSQLSource::FCompWrapper

sqlservr!CSQLSource::Transform

sqlservr!CSQLSource::Execute

sqlservr!ExecuteSql

sqlservr!CSpecProc::ExecuteSpecial

sqlservr!CXProc::Execute

sqlservr!CSQLSource::Execute

sqlservr!CStmtExecProc::XretLocalExec

sqlservr!CStmtExecProc::XretExecExecute

sqlservr!CXStmtExecProc::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadStart

KB :2344600:FIX: "Non-yielding Scheduler" error may occur when you use the CONTAINSTABLE function together with many OR and AND predicates in SQL Server 2008 or in SQL Server 2008 R2

Stack 2

sqlservr!TMatchPattern

sqlservr!FMatchStrTxt

sqlservr!I8CharindexStrBhI8

sqlservr!CEs::GeneralEval4

sqlservr!CXStmtCond::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,0>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands 0x12a

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadInitThunk

ntdll!RtlUserThreadStart

2633357 FIX: "Non-yielding Scheduler" error might occur when you run a query that uses the CHARINDEX function in SQL Server 2008 R2

Stack 3

sqlservr!CItvlVal::Copy

sqlservr!CConstraintItvl::PcnstrItvlUnion

sqlservr!CConstraintProp::FBuildItvlFromOr

sqlservr!CConstraintProp::FBuildItvlFromPexpr

sqlservr!CConstraintProp::FAndItvlConstraint

sqlservr!CConstraintProp::AndNewConstraint

sqlservr!CConstraintProp::PcnstrDeriveSelect

sqlservr!CLogOp_Select::PcnstrDerive

sqlservr!CLogOpArg::PcnstrDeriveHandler

sqlservr!CLogOpArg::DeriveGroupProperties

sqlservr!COpArg::DeriveNormalizedGroupProperties

sqlservr!COptExpr::DeriveGroupProperties

sqlservr!COptExpr::DeriveGroupProperties 0xc6

sqlservr!COptExpr::DeriveGroupProperties

sqlservr!CQuery::PqoBuild

sqlservr!CStmtQuery::InitQuery

sqlservr!CStmtDML::InitNormal

sqlservr!CStmtDML::Init

sqlservr!CCompPlan::FCompileStep

sqlservr!CSQLSource::FCompile

sqlservr!CSQLSource::FCompWrapper

sqlservr!CSQLSource::Transform

KB: 982376 FIX: A non-yielding scheduler error or an error 8623 occurs when you run a query that contains a large IN clause in SQL Server 2005,SQL Server 2008, or SQL Server 2008 R2

Stack 4

sqlservr!COptExpr::AdjustParallelPlan

sqlservr!COptContext::PcxteOptimizeQuery

sqlservr!CQuery::Optimize

sqlservr!CQuery::PqoBuild

sqlservr!CStmtQuery::InitQuery

sqlservr!CStmtSelect::Init

sqlservr!CCompPlan::FCompileStep

sqlservr!CSQLSource::FCompile

sqlservr!CSQLSource::FCompWrapper

sqlservr!CSQLSource::Transform

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadStart

KB: 943060 FIX: A query that has many outer joins takes a long time to compile in SQL Server 2005

Stack 5

sqlservr!CXid::GetBlockingTask

sqlservr!SNode::SearchForDeadlock

sqlservr!DeadlockMonitor::SearchForDeadlock

sqlservr!DeadlockMonitor::SearchAndResolve

sqlservr!DeadlockMonitor::SearchTaskAndResolve

sqlservr!DeadlockMonitor::WorkLoop

sqlservr!lockMonitor

sqlservr!lockMonitorThread

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadStart

956854 Cumulative update package 10 for SQL Server 2005 Service Pack 2

Stack 6

ntdll!ZwQueryAttributesFile

ntdll!RtlDoesFileExists_UstrEx

ntdll!LdrpSearchPath

ntdll!LdrpCheckForLoadedDll

ntdll!LdrpLoadDll

ntdll!LdrLoadDll

kernel32!LoadLibraryExW

mswsock!SockLoadHelperDll

mswsock!SockGetTdiName

mswsock!SockSocket

mswsock!WSPSocket

ws2_32!WSASocketW

ws2_32!WSASocketA

sqlservr!CreateSocket

sqlservr!AcceptObject::AsyncAccept

sqlservr!Tcp::AcceptDone

sqlservr!SNIAcceptDoneWithReturnCode

sqlservr!SNIAcceptDoneWrapper

sqlservr!SNIAcceptDoneRouter

sqlservr!SOS_Node::ListenOnIOCompletionPort

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadStart

KB 2711549 FIX: An error message is logged when you start SQL Server 2008 R2 or when a client sends a request to SQL Server 2008 R2

Stack 7

ntdll!ZwOpenKey

advapi32!LocalBaseRegOpenKey

advapi32!RegOpenKeyExW

sqlservr!COledbConnect::GetProviderOptions

sqlservr!COledbConnect::SetClsidFromProvider

sqlservr!COledbConnect::Init

sqlservr!CStmtExecProc::XretRemoteExec

sqlservr!CRemoteProcExecLevel::Execute

sqlservr!CStmtExecProc::XretWrapRemoteExec

sqlservr!CStmtExecProc::XretExecExecute

sqlservr!CXStmtExec::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!CStmtExecProc::XretLocalExec

sqlservr!CStmtExecProc::XretExecExecute

KB2468047 FIX: Error code 17883 or "Non-yielding Scheduler" error may occur when you use the OPENQUERY function on SQL Server 2005

Stack 8

ntdll!ZwQueryVirtualMemory

psapi!QueryWorkingSetEx

sqlservr!BPool::Shrink

sqlservr!BPool::ReleaseAwayBufs

sqlservr!BPool::LazyWriter

sqlservr!lazywriter

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadStart

967908 Cumulative update package 13 for SQL Server 2005 Service Pack 2 or 970279 Cumulative update package 4 for SQL Server 2005 Service Pack 3

Stack 9

sqlservr!LatchBase::ReleaseInternal

sqlservr!XVB::GetRecord

sqlservr!RowsetVersionScan::GetData

sqlservr!CQScanRowsetNew::GetRowWithPrefetch

sqlservr!CQScanRowsetNew::GetRow

sqlservr!CQScanNLJoinNew::GetRowHelper

sqlservr!CQScanNLJoinNew::GetRow

sqlservr!CQScanNLJoinNew::GetRowHelper

sqlservr!CQScanNLJoinNew::GetRow

sqlservr!CQueryScan::GetRow

sqlservr!CXStmtQuery::InitForExecute

sqlservr!CXStmtQuery::ErsqExecuteQuery

sqlservr!CXStmtCondWithQuery::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!CXStmtDML::FExecTrigger

sqlservr!CXStmtDML::FExecAllTriggers

sqlservr!CXStmtDML::XretDMLExecute

sqlservr!CXStmtDML::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<0,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!CStmtPrepQuery::XretExecute

sqlservr!CExecuteStatement::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!CStmtExecStr::XretExecStrExecute

sqlservr!CXStmtExecStr::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadStart

KB : 949595 FIX: Error message when you run a query that uses a join condition in SQL Server 2005: "Non-yielding Scheduler"

Stack 10

sqlservr!SQLServerLogIter::LookupScanCache

sqlservr!SQLServerLogIterForward::GetNextBlock

sqlservr!SQLServerLogIterForward::GetNext

sqlservr!LsMgr::GetEndOfLog

sqlservr!LsMgr::ProcessInternalRollForward

sqlservr!LsWorkRequest::Execute

sqlservr!LsWorker::ThreadRoutine

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadStart

KB 970044 FIX: Error message when you try to break database mirroring between two servers that are running SQL Server 2008: "Non-yielding Scheduler"

Stack 11

sqlservr!CLinkedMap

sqlservr!CCheckReadersAndWriters::Release

sqlservr!CMainIlb::~CMainIlb

sqlservr!CBlobHandleFactoryMain::ReleaseILockBytes

sqlservr!CMainIlb::Release

sqlservr!CTraceRpcBinaryStream::~CTraceRpcBinaryStream

sqlservr!CTraceTvpData::~CTraceTvpData

sqlservr!CRpcTraceHelper::CleanUpTraceTvpData

sqlservr!CRpcTraceHelper::TracePostExec

sqlservr!CRPCExecEnv::OnExecFinish

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!endthreadex

kernel32!BaseThreadInitThunk

ntdll!RtlUserThreadStart

KB 2520808 FIX: Non-yielding scheduler error when you run a query that uses a TVP in SQL Server 2008 or in SQL Server 2008 R2 if SQL Profiler or SQL Server Extended Events is used

Stack 12

ntdll!ZwFreeVirtualMemory

KERNELBASE!VirtualFree

sqlservr!MemoryNode::VirtualFree

sqlservr!ReservedMemBlock::FreeMemory

sqlservr!MultiPageAllocator::FreePagesInternal

sqlservr!MultiPageAllocator::FreePages

sqlservr!MemoryNode::FreePagesInternal

sqlservr!MemoryClerkInternal::FreePagesInline

sqlservr!CVarPageMgr::Release

sqlservr!CMemObj::Free

sqlservr!CMemThread<CMemObj>::Free

sqlservr!LockBytesSS::~LockBytesSS

sqlservr!LockBytesHolder::`scalar deleting destructor'

sqlservr!LockBytesHolder::DestroyCallback

sqlservr!CacheLbss

sqlservr!LockBytesSS::Release

sqlservr!CQueryIlb::~CQueryIlb

sqlservr!CBlobHandleFactoryMain::ReleaseILockBytes

sqlservr!CMainIlb::Release

sqlservr!CTraceRpcBinaryStream::~CTraceRpcBinaryStream

sqlservr!CTraceTvpData::~CTraceTvpData

sqlservr!CRpcTraceHelper::CleanUpTraceTvpData

sqlservr!CRpcTraceHelper::TracePostExec

sqlservr!CRPCExecEnv::OnExecFinish

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!endthreadex

kernel32!BaseThreadInitThunk

ntdll!RtlUserThreadStart

KB 2520808 FIX: Non-yielding scheduler error when you run a query that uses a TVP in SQL Server 2008 or in SQL Server 2008 R2 if SQL Profiler or SQL Server Extended Events is used

Stack 13

sqlservr!CompareStringWEnglishNoCase

sqlservr!CTypeInfo::ICompW

sqlservr!CDefaultCollation::ICompW

sqlservr!CDependElem::ICompare

sqlservr!CDependList::Find

sqlservr!CDependList::Insert

sqlservr!CDependList::Concat

sqlservr!CDependList::CollectDependencies

sqlservr!FillSysdepends

sqlservr!CProchdr::CreateProc

sqlservr!CSQLSource::PerformPphFakeExecute

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadInitThunk

ntdll!RtlUserThreadStart

KB 2306162 FIX: Poor performance and some occasional non-yielding scheduler errors occur when you create a complex view that references a large amount of nested views or tables in SQL Server 2008 or in SQL Server 2008 R2

Stack 14

sqlservr!BaseSharedHoBt::GetHoBtId

sqlservr!HoBtFactory::GetDeferredDropCacheHobt

sqlservr!DropDeferredWorkTables

sqlservr!GhostRecordCleanupTask

sqlservr!CGhostCleanupTask::ProcessTskPkt

sqlservr!TaskReqPktTimer::ExecuteTask

sqlservr!OnDemandTaskContext::ProcessTskPkt

sqlservr!SystemTaskContext::ExecuteFunc

sqlservr!SystemTaskEntryPoint

sqlservr!OnDemandTaskContext::FuncEntryPoint

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SchedulerManager::FiberEntryPoint

kernel32!BaseFiberStart

kernel32!RtlCompareMemoryStub

KB 2505256 FIX: Poor performance when worktables that are marked for deferred drop are cleaned up in SQL Server 2008 R2

Stack 15

ntdll!ZwReadFile

kernel32!ReadFile

sqlservr!DiskReadAsync

sqlservr!FCB::AsyncRead

sqlservr!BackupIoRequest::StartDatabaseRead

sqlservr!BackupCopyMachine::CopyFileToBackupSet0

sqlservr!BackupCopyMachine::CopyFileToBackupSet

KB 960543 FIX: SQL Server 2005 or SQL Server 2008 may stop responding when you are performing a backup

Stack 16

sqlservr!Worker::ProfilingCPUTicks::ProfilingCpuTicksCallback

sqlservr!SOS_Scheduler::TaskTransition

sqlservr!SOS_Scheduler::Switch

sqlservr!SOS_Scheduler::SuspendNonPreemptive

sqlservr!SOS_Scheduler::Suspend

sqlservr!SOS_Task::Sleep

sqlservr!BTreeMgr::Seek

sqlservr!BTreeMgr::GetHPageIdWithKey

sqlservr!IndexPageManager::GetPageWithKey

sqlservr!GetRowForKeyValue

sqlservr!IndexRowScanner::EstablishInitialKeyOrderPosition

sqlservr!IndexDataSetSession::GetNextRowValuesInternal

sqlservr!RowsetNewSS::GetNextRows

sqlservr!CMEDScan::FGetRow

sqlservr!CMEDCatalogOwner::GetOwnerAliasIdFromSid

sqlservr!CMEDCatalogOwner::LookupPrimaryIdInCatalog

sqlservr!CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey

sqlservr!CMEDCatalogOwner::GetProxyOwnerBySID

sqlservr!CMEDProxyDatabase::GetOwnerBySID

sqlservr!GetDefaultSchemaIdCrossDb

sqlservr!GetCtxtSchemaId

sqlservr!CMEDAccess::GetMultiNameObject

sqlservr!CRangeObject::CImplName::FSameObject

sqlservr!CRangeObject::FCheckImplNames

sqlservr!CRangeObject::XretPostSchemaChecks

sqlservr!CRangeObject::XretSchemaChanged

sqlservr!CRangeTable::XretSchemaChanged

sqlservr!CEnvCollection::XretSchemaChanged

sqlservr!CXStmtQuery::XretSchemaChanged

sqlservr!CXStmtSelect::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!CStmtExecProc::XretLocalExec

sqlservr!CStmtExecProc::XretExecExecute

sqlservr!CXStmtExecProc::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

KB 2699013 FIX: SQL Server 2008 R2 or SQL Server 2008 stops responding and a "Non-yielding Scheduler" error is logged

Stack 17

sqlservr!CQScanNLJoinNew::GetRowHelper

sqlservr!CQueryScan::GetRow

sqlservr!CXStmtQuery::ErsqExecuteQuery

sqlservr!CXStmtCondWithQuery::XretExecute

sqlservr!CExecStmtLoopVars::ExecuteXStmtAndSetXretReturn

sqlservr!CMsqlExecContext::ExecuteStmts<1,0>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!CXStmtDML::FExecTrigger

sqlservr!CXStmtDML::FExecAllTriggers

sqlservr!CXStmtDML::XretDMLExecute

sqlservr!CXStmtDML::XretExecute

sqlservr!CExecStmtLoopVars::ExecuteXStmtAndSetXretReturn

sqlservr!CMsqlExecContext::ExecuteStmts<0,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!CStmtPrepQuery::XretExecute

sqlservr!CMsqlExecContext::ExecuteStmts<1,1>

sqlservr!CMsqlExecContext::FExecute

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

sqlservr!SchedulerManager::WorkerEntryPoint

sqlservr!SystemThread::RunWorker

sqlservr!SystemThreadDispatcher::ProcessWorker

sqlservr!SchedulerManager::ThreadEntryPoint

msvcr80!_callthreadstartex

msvcr80!_threadstartex

kernel32!BaseThreadInitThunk

KB 967169 FIX: When you run an UPDATE statement against a table that has a FOR UPDATE trigger that joins the DELETED and INSERTED tables, the query takes a long time to finish

Stack 18

msvcr80!memcpy

BackupString::vswcatf

BackupString::swcatf

BackupHistory::GenerateBackupDetails

sqlservr!BackupHistory::GenerateBackupSet

KB 917971 FIX: You may receive more than 100,000 page faults when you try to back up a SQL Server 2005 database that contains hundreds of files and file groups.

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

ASYNC_NETWORK_IO or NETWORK_IO

2012-07-27T12:12:00Z

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 waits on ASYNC_NETWORK_IO (SQL 2005/2008) or Network_IO (SQL 2000) before it produces additional results.

Impact of this wait type

1. SQL Server will not release the locks held by the query unless Acknowledgement is received from Client. Which might cause blocking.

2. Query duration increases so the query will be slow.

How to troubleshoot ASYNC_NETWORK_IO or NETWORK_IO wait type?

Limit the result set: Limit the number of rows you fetch from SQL Server. Many application designers fetch the data from whole table with out filtering (Where clause) and do the filtering on client side. This is very wrong approach there has to be filtering on server side.
If there is need to fetch huge number of rows from server and if other sessions are experiencing blocking because of ASYNC_NETWORK_IO or NETWORK_IO Then insert all the rows in to temp table and do the select from temp table.

Check the performance of client application: Check if client / Application system is experiencing system resource bottleneck. Application will not be able to process the result set faster if there system resource bottleneck

Network: Check the network speed between SQL Server and Client/Application system. If the network is slow application can not fetch result faster.

No lock hint or Snapshot isolation level: Check if you can use No lock hint or Snapshot isolation level

Few other SQL Server performance blogs :

Basics of SQL Server Memory Architecture

SQL Server I/O Bottlenecks

Troubleshooting SQL Server Memory

SQL Agent MaxWorkerThreads and Agent subsystem

Thank you,

Karthick P.K |Technical Lead | Microsoft SQL Server Support |

My Facebook Page |My Site| Blog space| Twitter

Unable to open large profiler trace files

2012-07-22T02:32:17Z

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 when we open a large profiler trace file or capture trace in GUI mode system drive gets full.

We can workaround this by changing the TMP environment variable. Changing TMP environment variable system wide can cause other issues so I would recommend changing TMP temporarily for the process

1. Open command prompt

SET TMP=G:\TMP

2. Open the profiler from command prompt

>Profiler90.exe -->SQL Server2005

>Profiler.exe -->SQL Server2008

>Mysavedtracefile.trc –>Open a saved tracefile

This would make profiler use G:\TMP for saving temporary data. So your c:\ would never get full.

Thank you,

Karthick P.K |Technical Lead | Microsoft SQL Server Support |

My Facebook Page |My Site| Blog space| Twitter

Linked server connection fails with “An error occurred during decryption”

2012-07-18T18:34:00Z

Problem Description

Linked server connection fails with below error

{

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

Msg 15593, Level 16, State 1, Line 1

An error occurred while decrypting the password for linked login ‘distributor_admin’ that was encrypted by the old master key. The error was ignored because the FORCE option was specified.

}

Cause

SQL Server service account was changed from services control manager (or) service master key was not backed up and restored when migrating SQL Server to another computer domain.

{

http://msdn.microsoft.com/en-us/library/ms187788.aspx

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.

}

Resolution

Regenerate the service master key using ALTER SERVICE MASTER KEY REGENERATE

If you receive the following error message when running ALTER SERVICE MASTER KEY REGENERATE.

{

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

}

We are left with only option to force regenerating service master key using “ALTER SERVICE MASTER KEY FORCE REGENERATE “.

Note:The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost.

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |Technical Lead | Microsoft SQL Server Support | E-mail: karthick.krishnamurthy@microsoft.com

My Facebook Page |My Site| Blog space| Twitter

The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll' because it is being used by another process.

2012-07-16T18:11:00Z

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 to normalize directory path 'C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll'
Error: Action "Microsoft.SqlServer.Configuration.ScoExtension.VerifiedCopyFileAction" threw an exception during execution.
Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: File C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\DllTmp64\sqlagentctr100.dll could not be copied to file path C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll. ---> Microsoft.SqlServer.Configuration.ScoExtension.CopyFileException: File C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\DllTmp64\sqlagentctr100.dll could not be copied to file path C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll. ---> Microsoft.SqlServer.Configuration.Sco.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.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite)
   at Microsoft.SqlServer.Configuration.Sco.SqlFile.CopyTo(String targetFileFullPath, Boolean bCopyAcl)
   --- End of inner exception stack trace ---
tion, ActionMetadata actionMetadata, TextWriter statusStream)
Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
Slp: The configuration failure category of current exception is ConfigurationFailure
Slp: Watson Bucket 1
Original Parameter Values

2012-07-25 11:01:36 Slp: Parameter 0 : SQL2008@RTM@KB2703282

2012-07-25 11:01:36 Slp: Parameter 1 : Microsoft.SqlServer.Configuration.ScoExtension.VerifiedCopyFileAction.ExecuteAction

2012-07-25 11:01:36 Slp: Parameter 2 : System.IO.__Error.WinIOError

2012-07-25 11:01:36 Slp: Parameter 3 : Microsoft.SqlServer.Configuration.ScoExtension.CopyFileException@1360@3

2012-07-25 11:01:36 Slp: Parameter 4 : System.IO.IOException@-2147024864

2012-07-25 11:01:36 Slp: Parameter 5 : CopyPerfCounterDllToSystemDir

2012-07-25 11:01:36 Slp: Parameter 7 : Microsoft.SqlServer.Configuration.ScoExtension.CopyFileException@CopyFileFailed

2012-07-25 11:01:36 Slp: Parameter 8 : sqlagentctr100.dll

2012-07-25 11:01:36 Slp:
Final Parameter Values

2012-07-25 11:01:36 Slp: Parameter 0 : SQL2008@RTM@KB2703282

2012-07-25 11:01:36 Slp: Parameter 1 : 0x31492EED

2012-07-25 11:01:36 Slp: Parameter 2 : 0x60797DC7

2012-07-25 11:01:36 Slp: Parameter 3 : 0xFDBE3E6D@1360@3

2012-07-25 11:01:36 Slp: Parameter 4 : 0x24C2C4E7

2012-07-25 11:01:36 Slp: Parameter 5 : CopyPerfCounterDllToSystemDir

2012-07-25 11:01:36 Slp: Parameter 7 : 0xCDC6E6DD

2012-07-25 11:01:36 Slp: Parameter 8 : 0xDD73CFED

Resolution:

Rename C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll and Re-run the setup.

Note: Name of the above mentioned file would change based on the instance name. so review the detail.txt to get the correct file name,rename it and then re-run the setup.

Similar SQL Server setup issues

Thank you,

Karthick P.K |Technical Lead | Microsoft SQL Server Support |

My Facebook Page |My Site| Blog space| Twitter

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

2012-06-26T15:23:00Z

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 seconds to complete on file .

The OS file handle is 0x000006A4. The offset of the latest long I/O is: 0x00000

This indicates SQL Server I/O Bottlenecks. SQL Server performance highly relies on the Disk performance. SQL Server I/O Bottleneck can be identified through

1. PAGEIOLATCH_xx or WRITELOG wait types in Sys.Sysprocesses and other DMV's

2. I/O taking longer than 15 seconds in SQL Server Error log.

{

SQL Server has encountered X occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [ ] in database [IOTEST (7). The OS file handle is 0x000006A4. The offset of the latest long I/O is:
0x000001

}

3. By looking at I/O latch wait statistics in sys.dm_os_wait_stats

{

Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%'
order by wait_type

}

4. By looking at pending I/O requests and isolating the disks,File and database in which we have I/O Bottleneck.

{

select database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address from sys.dm_io_virtual_file_stats(NULL, NULL)t1, sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

}

Following are common reasons for I/O Bottleneck in SQL Server.

1. SQL Server is spawning more I/O requests than what I/O disk subsystem could handle.

2 . There could be an Issue with I/O subsystem (or) driver/firmware issue (or) Misconfiguration in I/O Subsystem (or) Compression and so the Disks are performing very slow and hence SQL Server is affected.

3. Some other process on the system is saturating the disks with I/O requests. Common application includes AV Scan,System Backup Etc. So I/O requests posted by SQL Server becomes slow.

How to troubleshoot?

1. Exclude SQL Server files from antivirus scan.

2. Do not place SQL Server FILES on compressed drives.

3. Distribute SQL Server data files and transaction log files across drives.

4. If the “I/O request taking longer” warning is for tempdb , Enable trace flag 1118 and increase the tempdb data files refer:http://support.microsoft.com/kb/2154845

5. If none of the above resolves the issue collect the below perfmon counters.

Perfmon counters can help us in understanding “If disk is slow” or “SQL Server is spawning more I/O then what disk could handle” or “Some other process is saturating disk with I/O”

Note:It is important to get throughput of the disk subsystem in MB/SEC before we look at disk counters. Normally it will be more than 150 MB for SAN disk and greater 50 MB for Single disk .When you look at the perfmon counter look at Max value.

Avg. Disk sec/Transfer --> Time taken to perform the I/O operation

Ideal value for Disk sec/Transfer is 0.005-0.010 sec. If you consistently notice this counter is beyond 0.015 then there is a serious I/O bottleneck.

1. Look for Disk Bytes /sec when Avg. Disk sec/Transfer is greater than 0.015. If it is below 200 MB for SAN disk and Below 50 MB for Single disk then the problem is with I/O subsystem Engage hardware vendor.

2. If the Disk Bytes /sec is greater than 200 MB for SAN disk or greater than 50 MB for Single disk when the Avg. Disk sec/Transfer is greater than 0.015. Look at the Process:IO Data Bytes/Sec for the same time and identify which process is spawning I/O. If the identified process is not SQL Server involve the team which supports that process. If the the identified process is SQL Server tune SQL Server queries which are I/O intensive by creating dropping indexes etc.

Disk Bytes /sec --> Total read and write to disk per second in bytes.

Collect the values for each logical disks in which SQL Server files are placed and look at the Max value for this counter ideally it has to be greater than the throughput of the disk subsystem. If you don’t have the throughput for the disk then this value to be greater than 200MB for SAN or greater than 50 MB for single disk.

If it is below the expected value you can consider that your disks are not performing well. Involve the hardware vendor.

Important: Value for this counter will be low when there is no I/O happening on the drives. So you have to look at the this counter during the time you see I/O warnings or When Disk sec/Transfer >0.010 for the same drive.

Process:IO Data Bytes/Sec --> Total read and write to disk per second in bytes by each process.

Collect this counter for all the processes running on the server. This counter will help us understand if any other process is saturating the disk with excessive I/O.

Example: Let us consider a disk with max throughput of 250MB per second. If antivirus is spawning 200MB of I/O per second and if SQL Server data files are placed in same drive and SQL Server is spawning 150MB obviously there will be I/O waits.

Buffer Manager: Page Read/sec + Page Writes/sec -->Total read and write to disk per second in bytes by SQL Server process.

If (Disk sec/Transfer > ==0.015 Consistently) and ( (Disk Bytes /sec < 150MB (For San)) or (Disk Bytes /sec < 50MB (For Local) or (Disk Bytes /sec < Speed of disk as per Vendor ))

{

There is Issue with I/O subsystem (or) driver/firmware issue (or) Misconfiguration in I/O Subsystem.

}

If (Disk sec/Transfer > ==0.015 Consistently) and ( (Disk Bytes /sec >= 150 (For San)) or (Disk Bytes /sec >= 50MB (For Local) or (Disk Bytes /sec >= Speed of disk as per Vendor ))

{

Identify the process which is posting excessive I/O request using Process:IO Data Bytes/Sec.

If ( Identified process == SQLServer.exe )

{

Identify and tune the queries which is Spawning excessive I/O.

(Reads+Writes column in profiler, Dashboard reports or sys.dm_exec_query_stats and sys.dm_exec_sql_text

can be used to identify the query). Use DTA to tune the query

}

If ( Identified process != SQLServer.exe )

{

Engage the owner of application which is spawning excessive I/O

}

}

Many thanks to Joseph Pilov from whom I learned many techniques like the one above.

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

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

2012-06-22T19:39:00Z

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 set (KB): 2007640, committed (KB): 4594040, memory utilization: 43%.

What is working set: Memory allocated by the process which is currently in RAM.

Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)

Working Set trimming: Windows is moving the allocated bytes of the process from physical RAM to page file because of memory pressure. Memory pressure is most commonly caused by applications or windows components that are requesting more memory causing OS to start trimming working set of other processes to satisfy these new requests.

Before we step in to troubleshooting working set trimming warnings, here are few basics about how SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system.

SQL Server uses CreateMemoryResourceNotification to create a memory resource notification object and SQL Server Resource monitor threads calls QueryMemoryResourceNotification every time it runs to identify if there is any notification. If a low memory notification comes from Windows, SQL Server scales down its memory usage and when Windows sends the high memory notification, SQL Server Server can grow its memory usage target. Low memory notification is signaled by windows when the available physical memory drops approximately below 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value. As soon as the SQL Server resource monitor threads finds low-memory-resource notification it scales down SQL Server memory usage.

Why do I see “A significant part of sql server process memory has been paged out. This may result in performance degradation.” By SQL Server In spite of having above mechanism to detect the system level memory pressure and scale SQL Server memory?

There are couple of situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism.

1.If windows is not sending the correct notifications to all listening processes at the right moment and thresholds

2.If SQL Server is not responding fast enough to the low memory resource notification from Windows

3.Conditions in Windows where working sets of all processes are trimmed

4.Windows might decide to trim a certain percentage of working set of various or specific processes

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

How to set the LowMemoryThreshold value (in MB)?

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 1000

System Reboot is required to take effect.

Default values as per MSDN:

“The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.”

We can use the below query to extract information about the condition of OS memory and SQL memory using a query like the following. Looking at this query, you will be able to easily determine the various indicators that would have triggered the Windows to page various processes including SQL Server. Use the following query to obtain the memory notification-related information from the XML data of the ring buffer

SELECT

CONVERT (varchar(30), GETDATE(), 121) as runtime,

DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE()) AS Notification_time,

a.* ,

sys.ms_ticks AS [Current Time]

FROM

(SELECT x.value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],

x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'int') AS [MemoryUtilization %],

x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],

x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],

x.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],

x.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],

x.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],

x.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],

x.value('(//Record/MemoryNode/@id)[1]', 'int') AS [Node Id],

x.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],

x.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],

x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],

x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],

x.value('(//Record/ResourceMonitor/Indicators)[1]', 'int') AS [Indicators],

x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]

FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)) a

CROSS JOIN sys.dm_os_sys_info sys

ORDER BY DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE())

Common Side Effects of Working set Trimming

1. When OS starts trimming the working set of SQL Server we normally see non-yielding Resource Monitor dumps.

2. IS-alive check failures resulting in SQL Server resource failure.

3. Resource monitor thread can start Garbage collector if SQLCLR is enabled on this instance of SQL. When Garbage collector is kicked off during memory pressure all other threads in the process are suspended. So if Garbage collector is taking a long time reosurce monitor thread appears stuck and hence the non-yielding errors and dumps are generated. (Refer http://support.microsoft.com/kb/2504603)

How to troubleshoot?

1. Capture perfmon counters (Process: Private bytes and Working set ) to determine which applications / windows component are requesting memory and causing OS to start trimming the working set of processes including SQL Server.

2. Use This exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals low memory notification.

3. Cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc. Make sure you have adequate available physical memory even when the system is under heavy load.

4. We can consider using the Lock pages in memory privilege. Remember it protects only the BPool from paging and Non-Bpool allocations can still be paged out.

http://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

http://mssqlwiki.com/sqlwiki/sql-performance/windows-2008-and-windows-2008-r2-known-issues-related-to-working-set-memory/

http://mssqlwiki.com/2012/05/18/sql-server-performance-degraded-in-32-bit-sql-server-after-i-adding-additional-ram/

http://mssqlwiki.com/sqlwiki/sql-performance/troubleshooting-sql-server-memory/

http://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Troubleshooting SQL Server Memory

2012-06-15T15:39:00Z

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 in This link.

MemToLeave errors:

SQL Server 2000
                WARNING: Failed to reserve contiguous memory of Size= 65536.
                WARNING: Clearing procedure cache to free contiguous memory.
                Error: 17802 "Could not create server event thread."
                SQL Server could not spawn process_loginread thread.

SQL Server 2005/2008
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880

Buffer Pool errors:
BPool::Map: no remappable address found.

BufferPool out of memory condition

Either BPool (or) MemToLeaveerrors:
Error: 17803 “Insufficient memory available..”
Buffer Distribution: Stolen=7901 Free=0 Procedures=1 Inram=201842 Dirty=0 Kept=572…

Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.

Working set trim and page out

A significant part of SQL Server process memory has been paged out. This may result in performance degradation.

Jump toA significant part of SQL Server process memory has been paged out

Section 1 (MTL error):

If the Problem is with MTL we have to determine whether it is SQL Server or some non-SQL component that is using the most MemToLeave memory (Remember what is in MTL? section in SQL Server Memory architecture ) .

SQL Server 2000: OS Reserved and OS Committed counters in the DBCC memory status output will tell us how many pages SQL Server itself is using in MTL.

Note: Each page is 8192 bytes so Multiply OS Committed * 8192 bytes /1024 to get value in MB.

SQLServer2005/2008: Capture sum of MultiPage Allocator for all nodes (Memory node Id = 0,1..n)from DBCC memorystatus output printed immediately after OOM errors in SQL Server errorlog.

This will tell us how many KB SQL Server itself is using in MTL.

You can also take the sum of multi_pages_kb from sys.dm_os_memory_clerks

select sum(multi_pages_kb) from sys.dm_os_memory_clerks

If SQL Server itself is using majority of the memory in MemToLeave look at MultiPage Allocator values in DBCC MEMORYSTATUS output to determine which memory clerk is consuming the majority of the memory.

sys.dm_os_memory_clerks output will also indicate which memory clerk is consuming majority of memory in MTL. Use the below query. You can further break down using sys.dm_os_memory_objects

{

select * from sys.dm_os_memory_clerks order by multi_pages_kb desc

select b.type,a.type,* from sys.dm_os_memory_objects a,sys.dm_os_memory_clerks b
where a.page_allocator_address=b.page_allocator_address order by b.multi_pages_kb,a.max_pages_allocated_count

}

If SQL Server Owned memory is very less ,than determine if there are COM objects, SQL Mail, or 3rd party xprocs being used, and move them out of process if possible.

COM Objects:
COM objects can be moved out of process by taking advantage of the optional third
parameter ([context]) at each sp_OACreate call. If the int value 4 is passed as
the third parameter to sp_OACreate, SQL will attempt to instantiate that object out
of process in its own dllhost.exe process. More information on the [context]
parameter can be found in the “sp_OACreate” topic in SQL Books Online. Warning:
most COM objects will work fine when run out of process, but some will fail. We
should run a few functional tests with context=4 to make sure that their objects
can be successfully run out of process.

Linked Server OLEDB Providers:
Linked server OLEDB providers can be moved out of process by setting the
“AllowInProcess” OLEDB provider option for that provider to 0. Provider options
are stored in the registry for each SQL instance at the location below:

Default Instance: HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers
Named Instance: HKLM \SOFTWARE\Microsoft\Microsoft SQL
Server\<instance>\Providers

If the AllowInProcess reg value for the relevant 3rd party provider doesn’t exist,
create it as a REG_DWORD value and set it to 0. Some OLEDB providers cannot be
successfully run out of process, but most can.

Extended Stored Procedures:
Extended stored procedures always run in-process; there is no direct way to execute
them out of process. However, in some cases it is possible to host the xp’s in a
separate instance of SQL and execute them in the remote instance using
server-to-server RPCs. This technique is detailed in KB 243428.

sql server -g switch should be used as last option to resolve MTL errors.

Section 2 (BPOOL error):

If the Problem is with BPOOL

Capture sum of singlePageAllocator for all nodes (Memory node Id = 0,1..n)from DBCC memorystatus output printed immediately after OOM errors in SQL Server errorlog.

This will tell us how many KB each memory clerk is using in MTL.

sys.dm_os_memory_clerks output will also indicate which memory clerk is consuming majority of memory in BPOOL (single_pages_kb). Use the below query. You can further break down using sys.dm_os_memory_objects

{

select * from sys.dm_os_memory_clerks order by Single_pages_kb desc

select b.type,a.type,* from sys.dm_os_memory_objects a,sys.dm_os_memory_clerks b
where a.page_allocator_address=b.page_allocator_address order by b.single_pages_kb

}

sys.dm_os_memory_clerks can provide a complete picture of SQL Server memory status and can be drilled down using sys.dm_os_memory_objects

Note: single_pages_kb is Bpool and multi_pages_kb is MTL

Other views which can help to troubleshoot SQL Server memory issues are

select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables

Few queries which we use to troubleshoot SQL Server memory issues.

--Bpool stats

select (bpool_committed * 8192)/ (1024*1024) as bpool_committed_mb

, (cast(bpool_commit_target as bigint) * 8192) / (1024*1024) as bpool_target_mb,

(bpool_visible * 8192) / (1024*1024) as bpool_visible_mb

from sys.dm_os_sys_info

go

-- Get me physical RAM installed

-- and size of user VAS

select physical_memory_in_bytes/(1024*1024) as phys_mem_mb,

virtual_memory_in_bytes/(1024*1024) as user_virtual_address_space_size

from sys.dm_os_sys_info

go

--

-- Get me other information about system memory

--

select total_physical_memory_kb/(1024) as phys_mem_mb,

available_physical_memory_kb/(1024) as avail_phys_mem_mb,

system_cache_kb/(1024) as sys_cache_mb,

(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024) as kernel_pool_mb,

total_page_file_kb/(1024) as total_virtual_memory_mb,

available_page_file_kb/(1024) as available_virtual_memory_mb,

system_memory_state_desc

from sys.dm_os_sys_memory

go

-- Get me memory information about SQLSERVR.EXE process

-- GetMemoryProcessInfo() API used for this

-- physical_memory_in_use_kb

select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb,

locked_page_allocations_kb/(1024) as awe_memory_mb,

total_virtual_address_space_kb/(1024) as max_vas_mb,

virtual_address_space_committed_kb/(1024) as sql_committed_mb,

memory_utilization_percentage as working_set_percentage,

virtual_address_space_available_kb/(1024) as vas_available_mb,

process_physical_memory_low as is_there_external_pressure,

process_virtual_memory_low as is_there_vas_pressure

from sys.dm_os_process_memory

go

select * from sys.dm_os_ring_buffers

where ring_buffer_type like 'RING_BUFFER_RESOURCE%'

go

select memory_node_id as node, virtual_address_space_reserved_kb/(1024) as VAS_reserved_mb,

virtual_address_space_committed_kb/(1024) as virtual_committed_mb,

locked_page_allocations_kb/(1024) as locked_pages_mb,

single_pages_kb/(1024) as single_pages_mb,

multi_pages_kb/(1024) as multi_pages_mb,

shared_memory_committed_kb/(1024) as shared_memory_mb

from sys.dm_os_memory_nodes

where memory_node_id != 64

go

with vasummary(Size,reserved,free) as ( select size = vadump.size,

reserved = SUM(case(convert(int, vadump.base) ^ 0)  when 0 then 0 else 1 end),

free = SUM(case(convert(int, vadump.base) ^ 0x0) when 0 then 1 else 0 end)

from

(select CONVERT(varbinary, sum(region_size_in_bytes)) as size,

region_allocation_base_address as base

from sys.dm_os_virtual_address_dump

where region_allocation_base_address <> 0x0

group by region_allocation_base_address

UNION(

select CONVERT(varbinary, region_size_in_bytes),

region_allocation_base_address

from sys.dm_os_virtual_address_dump

where region_allocation_base_address = 0x0)

as vadump

group by size)

select * from vasummary

go

-- Get me all clerks that take some memory

--

select * from sys.dm_os_memory_clerks

where (single_pages_kb > 0) or (multi_pages_kb > 0)

or (virtual_memory_committed_kb > 0)

go

-- Get me stolen pages

--

select (SUM(single_pages_kb)*1024)/8192 as total_stolen_pages

from sys.dm_os_memory_clerks

go

-- Breakdown clerks with stolen pages

select type, name, sum((single_pages_kb*1024)/8192) as stolen_pages

from sys.dm_os_memory_clerks

where single_pages_kb > 0

group by type, name

order by stolen_pages desc

go

-- Get me the total amount of memory consumed by multi_page consumers

--

select SUM(multi_pages_kb)/1024 as total_multi_pages_mb

from sys.dm_os_memory_clerks

go

-- What about multi_page consumers

--

select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb

from sys.dm_os_memory_clerks

where multi_pages_kb > 0

group by type, name

order by multi_pages_mb desc

go

-- Let's now get the total consumption of virtual allocator

--

select SUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mb

from sys.dm_os_memory_clerks

go

-- Breakdown the clerks who use virtual allocator

--

select type, name, sum(virtual_memory_committed_kb)/1024 as virtual_mem_mb

from sys.dm_os_memory_clerks

where virtual_memory_committed_kb > 0

group by type, name

order by virtual_mem_mb desc

go

-- Is anyone using AWE allocator?

--

select SUM(awe_allocated_kb)/1024 as total_awe_allocated_mb

from sys.dm_os_memory_clerks

go

-- Who is the AWE user?

--

select type, name, sum(awe_allocated_kb)/1024 as awe_allocated_mb

from sys.dm_os_memory_clerks

where awe_allocated_kb > 0

group by type, name

order by awe_allocated_mb desc

go

-- What is the total memory used by the clerks?

--

select (sum(multi_pages_kb)+

SUM(virtual_memory_committed_kb)+

SUM(awe_allocated_kb))/1024

from sys.dm_os_memory_clerks

go

--

-- Does this sync up with what the node thinks?

--

select SUM(virtual_address_space_committed_kb)/1024 as total_node_virtual_memory_mb,

SUM(locked_page_allocations_kb)/1024 as total_awe_memory_mb,

SUM(single_pages_kb)/1024 as total_single_pages_mb,

SUM(multi_pages_kb)/1024 as total_multi_pages_mb

from sys.dm_os_memory_nodes

where memory_node_id != 64

go

--

-- Total memory used by SQL Server through SQLOS memory nodes

-- including DAC node

-- What takes up the rest of the space?

select (SUM(virtual_address_space_committed_kb)+

SUM(locked_page_allocations_kb)+

SUM(multi_pages_kb))/1024 as total_sql_memusage_mb

from sys.dm_os_memory_nodes

go

--

-- Who are the biggest cache stores?

select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb

from sys.dm_os_memory_cache_counters

where type like 'CACHESTORE%'

group by name, type

order by cache_size_mb desc

go

--

-- Who are the biggest user stores?

select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb

from sys.dm_os_memory_cache_counters

where type like 'USERSTORE%'

group by name, type

order by cache_size_mb desc

go

--

-- Who are the biggest object stores?

select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb

from sys.dm_os_memory_clerks

where type like 'OBJECTSTORE%'

group by name, type

order by cache_size_mb desc

go

select mc.type, mo.type from sys.dm_os_memory_clerks mc

join sys.dm_os_memory_objects mo

on mc.page_allocator_address = mo.page_allocator_address

group by mc.type, mo.type

order by mc.type, mo.type

go

Other performance blogs:

http://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

http://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/

http://mssqlwiki.com/sqlwiki/sql-server-agent/sql-agent-maxworkerthreads-and-agent-subsystem/

http://mssqlwiki.com/sqlwiki/sql-performance/async_network_io-or-network_io/

Thanks

Karthick

Karthick PK 's Blog

SQL Server memory - Internals

Query optimization

Disclaimer:

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

ASYNC_NETWORK_IO or NETWORK_IO

Unable to open large profiler trace files

Linked server connection fails with “An error occurred during decryption”

The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll' because it is being used by another process.

Service pack ,Hotfix and CU installation for SQL Server might fail with “Unable to install Windows Installer MSI file“

A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_PowerShell_Engine_CNS,SQL_PowerShell_Tools_ANS]. In order to apply this patch package (KB968369), you must resolve any issues with the previous operation that failed.

SQLServer2008: Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16

Installation of SQLserver2008 cluster fails on windows2008.(The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

SQLServer 2008 Fails to come online on cluster after upgrade

Installation of SQLServer2005/2008 Fails on Windows2008 Cluster.

Unable to start SQLServer agent resource on cluster after upgrading to 9.00.3186 or Higher

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

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

Troubleshooting SQL Server Memory