A request that we receive is that CPSM is running slow or that there is a very high load on the SQL server. This can sometimes be attributed to ADSync sending so many requests that it causes an impact on the server.
Looking at provisioning logs will give you an good idea of how many successful or unsuccessful provisioning requests have been made for ADSync. However there could be many ADSync requests that are rejected before it is a provisioning request by the API due to validation issues.
The following article provides steps on configuring a SQL trace on the OLM database to trace the calls from the API made by an ADSync client for user requests.
When ADSync attempts to sync, the API does a SQL lookup using the remote users SID into the database. Knowing this, we can trace these lookups and find out how often a customer is querying the API.
It does not cover the tracing of contacts or distribution groups.
Setting up the profiler trace.
Connect to your SQL server.
Open SQL Server Profiler.
Click File > New Trace
Input the credentials for the SQL server/instance that contain the CPSM OLM Database
On the Trace Properties screen, click on Save to Table .
Select the OLM Database
In the Table field put in ZADSyncStats (this can be changed, just remember to change the query)
The Trace Properties page will look as reflect the change.
Click on “Show all Columns” on the bottom right
Scroll to the right and click on the heading “DatabaseName”
In the Like field put in OLM
Scroll down the filter field and find the filter called “TextData”
In the Like Field put in exec sp_UserSearch2 @Propertyvalue%
Run the trace for 5 minutes, and you will see data in the trace and it will also be populated within the ZADSyncStats.
Querying the data.
Open SQL Server Management Studio.
Connect to the Instance with the OLM database on it.
The following query will show you the amount of requests per customer.
You should alter the starttime to the range sample you are looking for.
WITH Customerinfo (customerid, starttime)
select Substring(CAST(textdata as nvarchar(max)),PATINDEX(‘%CustomerID=%’,CAST(textdata as nvarchar(max)))+11,PATINDEX(‘%,@PropertyName%’,CAST(textdata as nvarchar(max)))-(PATINDEX(‘%CustomerID=%’,CAST(textdata as nvarchar(max)))+11)) as Customerid,StartTime FROM ZADSyncStats
WHERE TextData is not null
SELECT ci.customerid,cu.name,cu.label, COUNT(*) requests FROM customerinfo ci
INNER JOIN customers cu
ON ci.customerid = cu.customerid
WHERE starttime between ‘2014-11-25 09:30:00’ and ‘2014-08-13 12:30:00’
GROUP BY ci.customerid,cu.name,cu.label
ORDER BY requests desc
In this example customer 29 sent 1237 requests during the same time customer 28 sent 3.
It does not mean that there is an issue occurring for customer 29, it could be they are adding additional users, forced a password change and users are logging in for the day to update.
While this provides an insight into the ADSync requests hitting your SQL server and provides you a good starting point to check which customers could have ADSync issues, it does not confer that a customer’s ADSync client is not functioning correctly.
They could have forced a password update for all their users or even on boarded new users.
In a future article I will discuss mitigating the potential load created by the ADSync clients and troubleshooting tips.