O R G A N I C / F E R T I L I Z E R: tracking inefficient queries...

Aug 23, 2005

tracking inefficient queries...

update: a fellow reader suggested i check out this article from tony murray. it's good stuff, so i thought i'd drop the link here: logging ldap searches: ad & adam.

so... a couple of domain controllers had runaway lsass processes today. i began to look further into the issue and figured out where excessive LDAP queries were being issued from. unfortunately, it didn't amount to anything... but the process in tracking them was pretty useful. the first thing i should point you to is Server Performance Advisor. just a fyi, as it turns out, there's a management pack that you can use with SPA... :) it's located here.

alright, so spa... you're on your own. it's a little kludgy, but once you have it down, it's extremely useful for providing information. i'm not really happy about the fact that it has to leave a footprint (installed) versus just running from an executable... but what do you do? anyway, the stuff i realized in spa is that it doesn't capture long-running or inefficient queries. i did some more digging and found that if you raise the 15 Field Engineering level to 4 or 5, you get logging down to the query. you can read the whole article here. here's a snippet from the article:

Tracking Expensive and Inefficient Searches

Expensive searches are searches that visit a large number of entries. The efficiency of a search is measured by the number of entries returned against the number of entries visited. For example, a search that goes through 500 entries could be considered an expensive search. If the search returns 500 entries after searching through 500 entries, then you have an efficient search. An inefficient search returns five entries after searching through 500 entries.

To track searches, you can enable the diagnostic event logging for Active Directory Services. Event logging allows you to determine if you have expensive or inefficient searches.

These event log messages are logged in the Directory Services event log using the Field Engineering category. The Directory Services event log is generated every time the garbage collector runs.

The following is an example of an event log message of an inefficient search:

Windows 2000 Server log

The Search operation based at DC=MyTest,DC=microsoft,DC=com 
using the filter:
(attr(0xd)=<substr>)
visited 237 entries and returned 6 entries.

Windows Server 2003 log

Internal event: A client issued a search operation with the following options.

Client:
127.0.0.1
Starting node:
 DC=MyTest,DC=microsoft,DC=com
Filter: 
  (objectCategory=<val>) Visited entries: 237 Returned entries: 6

This search is considered an inefficient search because only six entries are returned after going through 237 entries.

Potentially, there can be numerous event log messages, so the messages are masked by using a severity level other than the default:

  • DS_EVENT_SEV_VERBOSE
    To log a message about the number of expensive and inefficient search operations performed in the last collection period, set the Field Engineering logging severity level to 4 (DS_EVENT_SEV_VERBOSE).
  • DS_EVENT_SEV_INTERNAL
    To log a message about the number of expensive and inefficient search operations performed in individual searches, set the Field Engineering logging severity level to 5 (DS_EVENT_SEV_INTERNAL). This event logs the exact filter used for each search operation that was expensive or inefficient, immediately after any expensive or inefficient search completes.

You can set the severity levels by setting the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTDS\
Diagnostics\15 Field Engineering

For information about how to enable diagnostic event logging for Active Directory Services, see the Microsoft Knowledge Base article Q314980 How to configure Active Directory diagnostic event logging in Windows Server Services.

To categorize search operations as expensive or inefficient, two DWORD registry keys are used:

  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTDS\Parameters\Expensive Search Results Threshold
  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTDS\Parameters\Inefficient Search Results Threshold

These DWORD registry keys have the following default values:

  • Expensive Search Results Threshold: 10000
  • Inefficient Search Results Threshold: 1000

Using the default values, a search is considered expensive if it visits more than 10,000 entries. A search is considered inefficient if the search visits more than 1,000 entries and the returned entries are less than 10 percent of the entries that it visited.