Sharepoint Diagnostics and XE

Comments: 2 Comments
Published on: January 3, 2018

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.

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.

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.

Dynamics AX Event Session

Many moons ago, I wrote about how to enable the context info for Dynamics AX. Having the context info enabled is a pretty important thing when dealing with Dynamics AX. The amount of time you can save yourself and the DAX Admins is astounding – if you have the context info enabled. If you are curious, read more about that here.

It is not an uncommon thing to need to know who is causing performance problems within AX. In addition, you will probably want to know what they are doing. But since that information is obfuscated due to the default setup of AX, you can find yourself beating your head against the desk in frustration.

Once you have the context info enabled, however, how are you going to trap that information? How are you going to figure out what queries are related to the context info passed from AX to the database? In addition, how are you going to parse that context info into something useful? Hint: some of those answers can be found in the aforementioned article – here.

Context

While the previous article goes into a good amount of detail in answering some of those questions, it does not divulge what to do to capture the context info. Capturing the context info is rather easy to do – if you choose to use Extended Events (as I prefer to do). And since this is the giving season, why not just give the session details and then let you play with the shiny new trace for a few days?

All in all, this is not a tremendously difficult session. The session is looking for statement completed events. When there is a qualifying event that fires, then I attach the additional payload data from the several actions (e.g. sql_text, database_id, server_principal_name and context_info) to the event payload so I can query it later.

Will I really need all of those actions? Not necessarily but the data can be useful here or there – especially if you are trying to familiarize yourself with a new DAX environment.

The Wrap

There are many many uses for Extended Events. In this article I show a quick session setup that can be useful in the troubleshooting of various different problems (most probably performance related) with Dynamics AX.

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.

Correlate SQL Trace and Actions

Comments: No Comments
Published on: January 1, 2018

I have recently written about the pains of correlating SQL Trace events to Extended Events (XE) events. That article can be found here. At that time, I did not dive into another facet of trace that truly needs converting as well. That component is known as “Actions” in the world of XE.

In the world of profiler and SQL Trace, these actions were an actual part of the payload for the Trace event. In XE, these actions are more like add-ons. I go into further depth about what an action in XE is – here. I recommend reading that article. Due to that, I won’t be diving into great detail here about what an Action is.

Actions

That said, there is one thing in regards to actions in XE that I think would be helpful in translating Traces to XE. In XE, we sometimes see an action referenced as a global field. This is in stark contrast to what the Trace equivalent would be – a local field. Rough translation is that the actions in Trace were localized to every event that needed that particular data. In XE, the field has become more like a function and can be applied across many different events without being tied to every event specifically. Thus – it is renamed to “global field” in many different places.

Knowing that we migrate from localized fields to global fields is helpful in trying to map the old world of SQL Trace to the new and improved world of XE. In addition to that, we have some objects to introduce that will help us translate. Those objects are:

  • sys.trace_event_bindings – a catalog view introduced in 2005 that contains a list of all possible usage combinations of events and columns.
  • sys.trace_columns – a catalog view introduced in 2008 which contains a list of all trace event columns.
  • sys.trace_xe_action_map – a table that contains a row for each XE action that is mapped to a SQL Trace Column.

I will use the data from these objects to map to the XE actions – with a few caveats. In order to map everything properly, each of these objects is required because they have just a tiny bit of the needed information in order to eventually get to the big picture. When I bring it all together, I will have a script something like this.

You may have noticed here that I am excluding some results. I am not interested in anything that does not map to an action in the world of XE. So I am chucking those empty action results to the side. In addition, I am tossing anything that is Audit related. If it is Audit related, I really need to be using the SQL Audit feature (though it is built fundamentally on XE) instead of XE. So anything that is SQL Audit related just needs to be discarded from these results.

This will yield a whopping 2145 actions that are mapped between the local SQL Trace fields and the global XE fields called actions.

If I contrast that to a count of available actions in XE, you will likely see that there is a significant difference and that a lot of inefficiency has been removed.

Depending on version of SQL Server you will get a slightly different result. Here is a quick cheat sheet I worked up for different versions.

As you can see from the image, none of the versions of SQL Server with XE exceed 55 actions (global fields). Compare that to over 2000 results in the old trace world with only 180 events, and I think you would agree it was highly inefficient.

The Wrap

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.

Failed to Create the Audit File

Categories: News, Professional, SSC
Comments: No Comments
Published on: December 31, 2017

One day while checking things for clients, I happened across a fun little error message – “SQL Server Audit failed to create the audit file“. It just so happens that the audit had been working and then suddenly stopped and started flooding the error logs with this message.

Why would it suddenly stop working? Well, it says in the error that the disk might be full or that there may be a permissions issue. So, at least there are some possibilities provided by the message. Granted – neither of these options is very settling for a DBA.

FAILED

While reading the first occurrence of this error message, you notice that the log is bombarded with 20 more messages of the same nature. Before you can even scroll to catch up with the error messages, another 30 have appeared. It looks like the server is starting to get busier with the business users starting to run through their daily routines. You need a fix and you need it quick. You copy and paste the error to another screen for reference and then close the log to remove that distraction. Here is a copy of that error message.

SQL Server Audit failed to create the audit file ‘C:\Database\XE\DBA_Server_Audit_906B13C3-8F3F-4CFC-A391-20C5F7CAD698.sqlaudit’. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file.

Let’s try the suggestions from the error message starting with security. The SQL Server Service account needs to have permissions to the directory where the audit is being stored. Looking in that directory, you can see that the audit was obviously successful at one point because there are audit files in the directory. In addition, the audit just barely stopped working and you are certain nothing has changed.

Regardless of that, you proceed to investigate the permissions settings on the directory. Looking at the directory permissions, you are able to fully confirm that the service account does indeed have adequate permissions to the folder.

So we can rule out the permissions having changed as being a viable contender for causing this problem. As you start to proceed to investigate the next option, you start to worry that users are being prevented from doing their jobs because of the flood of errors. For sanity sake, you run a few quick checks to verify things look normal on the server from an activity standpoint. You also check your ticket queues and find there is nothing alarming in there.

Phwew!

Still Trying

After checking the ticket queues and server activity, you bounce right back to your next check point – disk space. This is an easier check than the permissions. You have no mount points and you can verify the disk space with a quick glance in windows explorer. You look in windows explorer and can see that your C drive where the audits are being stored has 50{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170} free space (or roughly 200GB).

Well, that is obviously not the problem either. You know the audit was working as recently as 30 minutes before you started troubleshooting and the errors did not start until almost immediately before you started checking the problem. What could it be? Afterall, you have 35 audit files in the audit folder for the trace. Then, suddenly, it hits you. There are 35 files. The trace was configured for 35 files with no rollover.

In this case, the easy fix is to move a bunch of files to an archive folder. As soon as that is done, a new message will appear in the error logs:

Message
Audit: Server Audit: 65536, State changed from: TARGET_CREATION_FAILED to: STARTED

Now to go change the audit process to make it a little more robust.

When dealing with SQL Audit, max_files is an important setting. Here is what msdn has to say about the setting.

MAX_FILES =integer

Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated will fail with an error.

This article is just one of several audit related articles on this blog. You can read more about some of the different ways to audit along with different perspectives from this link.

Wrap

Auditing is a necessity but it doesn’t need to be alarming or scary. Sometimes, we can become a little bit alarmed when an error occurs. We just need to keep our cool and trust our skills and abilities to troubleshoot in the event an audit fails. This article will hopefully show some of that process and help to provide a cool demeanor.

An Introduction to Templates

Comments: No Comments
Published on: December 30, 2017

Templates are a powerful tool in so many trades and crafts. From decals and stickers all the way up to the largest cruise ships in the world, templates can be found everywhere in just about everything that do.

In SQL Server, templates are readily available for your use in so many different ways that I am sure we are unaware of most of them.

Templates

A good example of a template in SQL Server, that we clamor about frequently, is the unattended install. I am sure you have probably used that kind of template as you work to save yourself some hair and brain cells trying to remember the exact setup needed for your environment. With an unattended install, you build it once and then use the script to build all future installs.

Even though we may not call the unattended install script a template, it is. Think about the definition(s) of a template for a minute:

  1. something that serves as a model for others to copy.
  2. a preset format for a document or file, used so that the format does not have to be recreated each time it is used.

Looking at this definition, we can easily see that the unattended install routine fits perfectly as a “template”. This is just an example of the availability of templates within SQL Server. There are many different kinds of templates – many we probably don’t ever think about as being “templates” and many that we just flat out don’t bother to think about at all.

I have one specific kind of template in mind that I want to introduce and discuss. This template type comes with Extended Events. I actually introduced the concept very briefly in a recent post about the XE Profiler feature. That article is just one of many articles about Extended Events that I have written. You are welcome to catch up on the series of articles here.

XE

Extended Events has had templates available for quite some time. If you recall from the article about XE Profiler, templates were not introduced as a part of the XE Profiler feature – the templates were introduced quite some time prior to that.

There are really three distinct areas of focus for templates that I would like to discuss. I have them somewhat illustrated in this next screenshot.

In this image, you will see that I have three color coded regions. In green, I have the option to create a template from file. In blue, I have the XE specific templates that have no correlation to profiler. And then in red I have the templates that were created to simulate the behavior of the Profiler based templates while providing some sort of comfort zone for the profiler fanatics out there.

Today, I will focus on the templates that are readily available (red and blue zones). In a future article, I will cover the creation of a custom template (green zone). That said, this is not going to be a traditional article with the technical geekery showing the ins and outs of all of those templates. Oh no, this article will be an entirely different flavor of geekery. Today, I will be focusing on something that is more about fun. I will be exploring the available templates strictly via script (no XE GUI).

Exploratory Surgery

If you are like me, you sometimes just don’t care to pick up the mouse to browse the file system or even browse the SSMS hierarchy to find the exact screen that has some specific information that you want. For me, I ran into this when thinking about the templates in XE. What templates are there in XE again? Crap, now I have to open the GUI to figure that out. I wanted a quick way to be able to look at all of the templates – and not just the templates available to me in the version of SSMS that I am using, I want to see everything out there on the system.

The question is how to do this. The templates are not listed in any system table or view that I could find. The list of templates is specific to the version of SSMS that you are using and the templates are all xml files stored in a directory on the OS. A thought occurred to me while contemplating this dilemma – can I maybe use the registry?

The registry does give me some options so maybe, I can use xp_regread. I won’t be able to use xp_instance_regread since this is not a registry key that appears at the instance level, rather it is an SSMS related reg string. One problem that comes to mind is that I would need to find the SQLPath for each of the versions of SSMS installed (red arrows indicate each of the registry strings I might have to query). I start to think to myself after realizing this that I do not like the looks of this option – but it could be done.

I then thought of another option before going too far down the registry route. When I install an application, that application may modify my “Path” environment variable for the OS. This sounds a little promising. I still have some of the drawback with multiple versions of SSMS installed – but I think I can manage that more easily from the “Path” environment variable.

Let’s take a crack at a script.

Ouch, my eyes hurt already! What have I done here? I have written multiple loops. Never fear! Loops are not all evil. Sometimes, it makes sense to use a looping mechanism. This is one of those times where it makes some sense. This script is a limited use script for the eyes of the DBA only.

I can hear the moans about the use of xp_cmdshell and xp_dirtree and so forth. I am not listening to those grumbles. Again, this script is for fun and on my machine. The discussion on securing the server and xp_cmdshell is a discussion for another time.

In this script, I use the path variable to determine all of the flavors of SSMS that have been installed. Since the path for SSMS is all the way to the BINN folder, I need to do a little replace to get the correct paths for the xe templates. I also added a bit of a union in there because I had to duplicate the results – sorta. You see there are templates for AzureDB that, depending on your Azure Subscription or lack thereof, you may never see. I wanted to include those in my result set!

Looking at these results on a machine with SSMS 14 and SSMS 17, I would see something like the following.

I highlighted a few different sections of the results to help quickly show some of the templates available between different SSMS versions as well as those for Azure DB versus a local instance of SQL Server. Things are a tad different if we have SSMS 16 installed. Here is a sample result from a machine with SSMS 16 installed.

The main point of interest here (at least for me) is the removal of a template from SSMS 16 to SSMS 17. This is probably a template you don’t necessarily want to run on a production system and that is likely why it is gone from the available templates that get installed.

The Wrap

I am sure you can probably find an alternate routine to query these templates via TSQL that may be more efficient. I would be interested to hear about it.

What’s next now that I can get these templates? Well, a thought occurred to me to use this type of routine to create a means to use TSQL to read one of those template files to create the XE session from pure TSQL statements and eliminate the need to use the GUI. I think that would be a lot of TSQL/XE Geekery based fun.

«page 4 of 117»

Calendar
June 2018
M T W T F S S
« May    
 123
45678910
11121314151617
18192021222324
252627282930  

Welcome , today is Friday, June 22, 2018