One of the all-time greatest and most beloved applications among DBAs happens to be Sharepoint. Most of us would be lying if we said that we loved Sharepoint and the kind of performance issues it can cause on a SQL Server.
When you have an application that comes with a notoriety of causing performance pain, you will want to have some tools or find some tools that can help you find the pain points. Lucky for the savvy DBA, there are tools to be able to capture this kind of information. Hint: some of those tools can be found in this series.
Tools
One of the best tools for capturing performance impacting data is Extended Events. Extended Events is simply a tool that everybody needs to learn. And in this case, it provides an opportunity to support and troubleshoot Sharepoint as well as SQL Server.
Truth be told, the default XE session for Sharepoint does not come installed when you install Sharepoint. But, there is a session that gets installed when you configure SPDiag for your Sharepoint needs. I am not going to dive deeply into that tool or the Sharepoint tooling. That would be well outside the use and scope of this post. Here is the default session created by that tool.
CREATE EVENT SESSION [SharePoint_Diagnostics_ca78065af3e84e70bd3f5cf8ce6d7dfc_1] ON SERVER ADD EVENT sqlserver.module_end ( ACTION ( sqlserver.client_app_name , sqlserver.plan_handle , sqlserver.session_id , sqlserver.sql_text ) WHERE ( [package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))) , ADD EVENT sqlserver.rpc_completed ( ACTION ( sqlserver.client_app_name , sqlserver.plan_handle , sqlserver.session_id , sqlserver.sql_text ) WHERE ( [package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))) , ADD EVENT sqlserver.sql_batch_completed ( ACTION ( sqlserver.client_app_name , sqlserver.plan_handle , sqlserver.session_id , sqlserver.sql_text ) WHERE ( [package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))) , ADD EVENT sqlserver.sql_statement_completed ( ACTION ( sqlserver.client_app_name , sqlserver.plan_handle , sqlserver.session_id , sqlserver.sql_text ) WHERE ( [package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name], 'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))) ADD TARGET package0.ring_buffer (SET max_memory = ( 49152 )) --KB WITH ( MAX_MEMORY = 98304KB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 1 SECONDS , MAX_EVENT_SIZE = 0KB , MEMORY_PARTITION_MODE = NONE , TRACK_CAUSALITY = ON , STARTUP_STATE = OFF ); GO
As you can see, there is not much special about the session. This is a basic session to capture statements queries executed against the server. In fact, you will probably note it is similar to both of the default XE Profiler sessions I discussed here. The first caveat here is the application filter for Sharepoint apps. That said, if you are running multiple Sharepoint sites and configure SPDiag on both, and both share the same backend database server, guess what happens? Yup, you guessed it – you get two Sharepoint sessions that capture the exact same data.
The second caveat here is a fun thing about the service that gets created to support the Sharepoint XE Sessions. The SPDiag will alter each of your XE (sharepoint related) sessions many times every few seconds. Every alter of the sessions will be to either stop or restart the session. This seems rather unnecessary and huge miss. There are not many misses with XE, but there can be a misuse of XE similar to all of the abuses of profiler and this is one of those abuses in my opinion. You can easily discover the constant changes to these sessions via an XE session that I will be sharing in a future post. I would recommend you stay tuned for that article and also watch the 60 Day Series for an update that includes that post.
The next caveat here is that this particular session is only configured to go to the ring_buffer. What is the problem there? Well, the ring_buffer means the trace data is volatile and you can easily lose it. In addition, this particular session has been known to cause some memory issues on many installations. You can check for a memory issue by watching for a growing MEMORYCLERK_XE memory count. You can watch it with a query like the following.
SELECT type, SUM(pages_kb)/1024/1024.0 as ClerkMemUseGB
FROM sys.dm_os_memory_clerks
WHERE pages_kb <> 0
and type in ('USERSTORE_TOKENPERM','MEMORYCLERK_XE')
GROUP BY type
ORDER BY SUM(pages_kb) DESC;In my opinion, I see no good value in running the session as currently built. I would just rather build a better XE session and leave it running instead of constantly stopping and starting the XE session.
The Wrap
In this article I have introduced you to a quick session setup that comes from using the SPDiag tool that could plausibly be useful in the troubleshooting of various different problems (most probably performance related) with Sharepoint. This assuredly is in the absence of a better alternative such as a custom session that you write yourself.
If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.
If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.
