SQL 2008 -system queries

Execution Related Dynamic Management Views and Functions (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188068%28v=sql.105%29.aspx



Usage:

select * from sys.dm_exec_xxxxxx

Using Performance Monitor to Find a User-Mode Memory Leak

If you suspect there is a user-mode memory leak but are not sure which process is causing it, you can use Performance Monitor to measure the memory usage of individual processes.
Launch Performance Monitor. Add the following counters:

• Process-->Private Bytes (for each process you want to examine)
  
• Process-->Virtual Bytes (for each process you wish to examine)
  

Change the update time to 600 seconds to capture a graph of the leak over time. You might also want to log the data to a file for later examination.
The Private Bytes counter indicates the total amount of memory that a process has allocated, not including memory shared with other processes. The Virtual Bytes counter indicates the current size of the virtual address space that the process is using.
Some memory leaks appear in the data file as an increase in private bytes allocated. Other memory leaks show up as an increase in the virtual address space.
After you have determined which process is leaking memory, use the UMDH tool to determine the specific routine that is at fault. For details, see Using UMDH to Find User-Mode Memory Leaks.

Using UMDH to Find a User-Mode Memory Leak

The user-mode dump heap (UMDH) utility works with the operating system to analyze Windows heap allocations for a specific process. UMDH locates which routine in a specific process is leaking memory.
UMDH is included in Debugging Tools for Windows. For full details, see UMDH.

Preparing to Use UMDH

If you have not already determined which process is leaking memory, do that first. For details, see Using Performance Monitor to Find User-Mode Memory Leaks.
The most important data in the UMDH logs are the stack traces of the heap allocations. To determine whether a process is leaking heap memory, analyze these stack traces.
Before using UMDH to display the stack trace data, you must use GFlags to configure your system properly. GFlags is included in Debugging Tools for Windows.
The following GFlags settings enable UMDH stack traces:

• In the GFlags graphical interface, choose the Image File tab, type the process name (including the file name extension), press the TAB key, select Create user mode stack trace database, and then click Apply.Or, equivalently, use the following GFlags command line, where ImageName is the process name (including the file name extension):
  
  gflags /i ImageName +ust 
  

• By default, the amount of stack trace data that Windows gathers is limited to 32 MB on an x86 processor, and 64 MB on an x64 processor. If you must increase the size of this database, choose the Image File tab in the GFlags graphical interface, type the process name, press the TAB key, check the Stack Backtrace (Megs) check box, type a value (in MB) in the associated text box, and then click Apply. Increase this database only when necessary, because it may deplete limited Windows resources. When you no longer need the larger size, return this setting to its original value.
  
• If you changed any flags on the System Registry tab, you must restart Windows to make these changes effective. If you changed any flags on the Image File tab, you must restart the process to make the changes effective. Changes to the Kernel Flags tab are effective immediately, but they are lost the next time Windows restarts.
  

Before using UMDH, you must have access to the proper symbols for your application. UMDH uses the symbol path specified by the environment variable _NT_SYMBOL_PATH. Set this variable equal to a path containing the symbols for your application. If you also include a path to Windows symbols, the analysis may be more complete. The syntax for this symbol path is the same as that used by the debugger; for details, see Symbol Path.
For example, if the symbols for your application are located at C:\MySymbols, and you want to use the public Microsoft symbol store for your Windows symbols, using C:\MyCache as your downstream store, you would use the following command to set your symbol path:

set _NT_SYMBOL_PATH=c:\mysymbols;srv*c:\mycache*http://msdl.microsoft.com/download/symbols 

In addition, to assure accurate results, you must disable BSTR caching. To do this, set the OANOCACHE environment variable equal to one (1). Make this setting before you launch the application whose allocations are to be traced.
If you need to trace the allocations made by a service, you must set OANOCACHE as a system environment variable and then restart Windows for this setting to take effect.
On Windows 2000, in addition to setting OANOCACHE equal to 1, you must also install the hotfix available with Microsoft Support Article 139071. This hotfix is not needed on Windows XP and later versions of Windows.

Detecting Increases in Heap Allocations with UMDH

After making these preparations, you can use UMDH to capture information about the heap allocations of a process. To do so, follow this procedure:

1. Determine the process ID (PID) for the process you want to investigate.
   
2. Use UMDH to analyze the heap memory allocations for this process, and save it to a log file. Use the -p switch with the PID, and the -f switch with the name of the log file. For example, if the PID is 124, and you want to name the log file Log1.txt, use the following command:
   umdh -p:124 -f:log1.txt 
   

3. Use Notepad or another program to open the log file. This file contains the call stack for each heap allocation, the number of allocations made through that call stack, and the number of bytes consumed through that call stack.
   
4. Because you are looking for a memory leak, the contents of a single log file are not sufficient. You must compare log files recorded at different times to determine which allocations are growing. UMDH can compare two different log files and display the change in their respective allocation sizes. You can use the greater-than symbol (>) to redirect the results into a third text file. You may also want to include the -d option, which converts the byte and allocation counts from hexadecimal to decimal. For example, to compare Log1.txt and Log2.txt, saving the results of the comparison to the file LogCompare.txt, use the following command:
   
   umdh log1.txt log2.txt > logcompare.txt 
   

5. Open the LogCompare.txt file. Its contents resemble the following:
   + 5320 ( f110 - 9df0) 3a allocs BackTrace00B53 
   Total increase == 5320 
   

   For each call stack (labeled "BackTrace") in the UMDH log files, there is a comparison made between the two log files. In this example, the first log file (Log1.txt) recorded 0x9DF0 bytes allocated for BackTrace00B53, while the second log file recorded 0xF110 bytes, which means that there were 0x5320 additional bytes allocated between the time the two logs were captured. The bytes came from the call stack identified by BackTrace00B53.
   
6. To determine what is in that backtrace, open one of the original log files (for example, Log2.txt) and search for "BackTrace00B53." The results are similar to this data:
   00005320 bytes in 0x14 allocations (@ 0x00000428) by: BackTrace00B53
   ntdll!RtlDebugAllocateHeap+0x000000FD
   ntdll!RtlAllocateHeapSlowly+0x0000005A
   ntdll!RtlAllocateHeap+0x00000808
   MyApp!_heap_alloc_base+0x00000069
   MyApp!_heap_alloc_dbg+0x000001A2
   MyApp!_nh_malloc_dbg+0x00000023
   MyApp!_nh_malloc+0x00000016
   MyApp!operator new+0x0000000E
   MyApp!DisplayMyGraphics+0x0000001E
   MyApp!main+0x0000002C
   MyApp!mainCRTStartup+0x000000FC
   KERNEL32!BaseProcessStart+0x0000003D 
   

   This UMDH output shows that there were 0x5320 (decimal 21280) total bytes allocated from the call stack. These bytes were allocated from 0x14 (decimal 20) separate allocations of 0x428 (decimal 1064) bytes each.
   The call stack is given an identifier of "BackTrace00B53," and the calls in this stack are displayed. In reviewing the call stack, you see that the DisplayMyGraphics routine is allocating memory through the new operator, which calls the routine malloc, which uses the Visual C++ run-time library to obtain memory from the heap.
   Determine which of these calls is the last one to explicitly appear in your source code. In this case, it is probably the new operator because the call to malloc occurred as part of the implementation of new rather than as a separate allocation. So this instance of the new operator in the DisplayMyGraphics routine is repeatedly allocating memory that is not being freed.
   

in http://msdn.microsoft.com/en-us/library/windows/hardware/ff545410%28v=vs.85%29.aspx


http://msdn.microsoft.com/en-us/library/windows/hardware/ff545405%28v=vs.85%29.aspx

Windows Task Manager columns description

What do the Task Manager memory columns mean?

http://windows.microsoft.com/en-us/windows/what-task-manager-memory-columns-mean#1TC=windows-7

In Task Manager, you can monitor processes running on your computer by adding columns to the information displayed on the Processes tab. These columns display information about each process, such as how much CPU and memory resources the process is currently using.

1. Open Task Manager by right-clicking the taskbar, and then clicking Start Task Manager.
2. Click the Processes tab. Task Manager shows the processes currently running under your user account. To show processes running for all users, click Show processes from all users.  If you're prompted for an administrator password or confirmation, type the password or provide confirmation.
3. To add more columns, click View, and then click Select Columns. Select the check boxes for the columns you want to see, and then click OK.
   
   

Column	Description
PID (Process Identifier)	A number that uniquely identifies a process while it runs.
User Name	The user account under which the process is running.
Session ID	A number that identifies the owner of the process. When multiple users are logged on, each user has a unique session ID.
CPU Usage	The percentage of time that a process used the CPU since the last update (listed as CPU in the column heading).
CPU Time	The total processor time, in seconds, used by a process since it started.
Memory - Working Set	Amount of memory in the private working set plus the amount of memory the process is using that can be shared by other processes.
Memory - Peak Working Set	Maximum amount of working set memory used by the process.
Memory - Working Set Delta	Amount of change in working set memory used by the process.
Memory - Private Working Set	Subset of working set that specifically describes the amount of memory a process is using that can't be shared by other processes.
Memory - Commit Size	Amount of virtual memory that's reserved for use by a process.
Memory - Paged Pool	Amount of pageable kernel memory allocated by the kernel or drivers on behalf of a process. Pageable memory is memory that can be written to another storage medium, such as the hard disk.
Memory - Non-paged Pool	Amount of non-pageable kernel memory allocated by the kernel or drivers on behalf of a process. Non-pageable memory is memory that can't be written to another storage medium.
Page Faults	The number of page faults generated by a process since it was started. A page fault occurs when a process accesses a page of memory that's not currently in its working set. Some page faults require page contents to be retrieved from disk; others can be resolved without accessing the disk.
Page Fault Delta	The change in the number of page faults since the last update.
Base Priority	A precedence ranking that determines the order in which the threads of a process are scheduled.
Handles	The number of object handles in a process's object table.
Threads	The number of threads running in a process.
USER Objects	The number of USER objects currently being used by the process. A USER object is an object from Window Manager, which includes windows, menus, cursors, icons, hooks, accelerators, monitors, keyboard layouts, and other internal objects.
GDI Objects	The number of objects from the Graphics Device Interface (GDI) library of application programming interfaces (APIs) for graphics output devices.
I/O Reads	The number of read input/output operations generated by the process, including file, network, and device I/Os. I/O Reads directed to CONSOLE (console input object) handles aren't counted.
I/O Writes	The number of write input/output operations generated by the process, including file, network, and device I/Os. I/O Writes directed to CONSOLE (console input object) handles aren't counted.
I/O Other	The number of input/output operations generated by the process that are neither a read nor a write, including file, network, and device I/Os. An example of this type of operation is a control function. I/O Other operations directed to CONSOLE (console input object) handles aren't counted.
I/O Read Bytes	The number of bytes read in input/output operations generated by the process, including file, network, and device I/Os. I/O Read Bytes directed to CONSOLE (console input object) handles aren't counted.
I/O Write Bytes	The number of bytes written in input/output operations generated by the process, including file, network, and device I/Os. I/O Write Bytes directed to CONSOLE (console input object) handles aren't counted.
I/O Other Bytes	The number of bytes transferred in input/output operations generated by the process that are neither a read nor a write, including file, network, and device I/Os. An example of this type of operation is a control function. I/O Other Bytes directed to CONSOLE (console input object) handles aren't counted.
Image Path Name	The location of the process on the hard disk.
Command Line	The full command line specified to create the process.
User Account Control (UAC) Virtualization	Identifies whether User Account Control (UAC) virtualization is enabled, disabled, or not allowed for this process. UAC virtualization redirects file and registry write failures to per-user locations.
Description	The description of the process.
Data Execution Prevention	Whether data execution prevention is enabled or disabled for this process. For more information, see What is Data Execution Prevention?