We recently released SQL Sentry v6.2, and it introduces an exciting new product: Event Manager for SharePoint. v6.2 contains several other new features as well, including SSAS usage metrics, performance dashboard event overlay, enhanced console security, and a streamlined setup process. SharePoint support and SSAS usage in particular have been heavily requested, so this release should make quite a few customers very happy!
SharePoint Timer Job Monitoring
If you already use SharePoint, you may be familiar with the concept of timer jobs. SharePoint 2010 has its own scheduler service that carries out various background tasks on an ongoing basis – over 100 timer jobs out of the box, ranging from health checks to data imports to history cleanup. These jobs can run on any server in the farm, and they can access resources on other servers. Most of the jobs are set to run on predefined recurring schedules, and the net result is a significant amount of timer job activity going on pretty much all day and night. Since much of this activity is touching SQL Server, it’s critical for the database administrator to have visibility into exactly when the jobs are running, and to be able to ascertain the impact they are having on performance.
Using the Event Manager for SharePoint calendar, here’s a shot of a typical SharePoint timer job schedule over two days:
![sharepoint_cal_2day_hist_thumb[3]](%24sharepoint_cal_2day_hist6.png)
The instances in orange indicate a runtime overlap of at least 20 seconds, so it’s apparent that many timer jobs run at the same time – that’s right, the dreaded schedule collisions! One of the primary reasons for the existence of the Event Manager calendar is of course to be able to detect and resolve schedule collisions, so even if you’ve already cleaned up your SQL Agent schedules, you’ll be able to put it to good use again for timer jobs. 😉
Here’s a shot of the 12am slot with timer jobs only:
We have no less than 20 timer jobs running concurrently at midnight every night. The calendar view can be easily expanded to include other events to see if these jobs are colliding with SQL Agent jobs, for example.
Here’s a closer view of the 4pm slot, with SQL events from the timer jobs included. You can distinguish the timer jobs from the SQL events at a glance by the small glyphs in the upper left of each instance:
I know, this is pretty ugly, so I’ll apply a quick filter to show only events with duration >3 seconds:
Again, these are all out of the box jobs and schedules! In multi-server farms, not all jobs may be running on the same server, so the server on which a job ran is shown in its tooltip along with many other details.
Timer job schedules can work quite differently than SQL Agent, Windows Scheduler, or other schedulers you may have seen. They can use the concept of “ranges” – you set possible start and end times, and the job can run any time during that range. Supposedly the randomness will help avoid job collisions… but note the “SharePoint BI Maintenance” job in the middle of this shot with odd start time of “12:00:40 PM:”
![sharepoint_collision_thumb[1]](%24sharepoint_collision2.png)
Yep, you guessed it, it’s using a range! If I right-click the job and select Properties, I’m launched into the job schedule in the SharePoint Central Administration web interface:
We can see that this job is set to run hourly, at any time during the hour. So the randomness didn’t help much here – it ended up running concurrently with 4 other jobs. Personally, I like to control exactly when my jobs run and not to leave that up to chance, so I changed both range values to 30, which effectively tells the job to run only at 30 minutes past the hour. (NOTE: You need to be setup as a farm administrator to access the schedule properties.)
Can timer jobs impact SQL Server performance? You’d better believe it! I’ll demonstrate using another new feature, Dashboard Event Overlay (more on this below). By right-clicking a longer running timer job and selecting Jump To > Performance Advisor > Dashboard…
…I’m taken right into the Performance Advisor dashboard where the event duration is overlaid on the X-axis of each chart:
We can see that there is a strong correlation with this job and several metrics, including:
- Network inbound
- CPU
- Windows hard faults
- Disk latency
- Transactions and batches
- Bookmark lookups
- Disk waits
- Log flushes
Since this particular job runs every 30 minutes, the real question becomes, how will it impact user activity against SharePoint databases on the same server throughout the day? More on this in a future post.
SSAS Usage Totals
The next major new feature is the SSAS Usage Totals tab, which is part of Performance Advisor for SSAS:
Previously, SSAS usage information was only available at the query level, and you were unable to see the actual attribute members requested. This made it difficult to get a server-wide perspective on data access patterns. You can now see this server-level data aggregated three ways – by attribute, aggregation, or partition – over any date range. If you spot a particular attribute combination that is heavily requested by many queries, you can right-click the row and copy the attribute vector to the clipboard, then paste it into Agg Manager to create the aggregation. (Look for this capability to be fully integrated in a future release.)
Enhanced Console Security
A common need, especially in larger, more segmented environments, is the ability to restrict visibility and access to certain servers to specific users. Previously, in the SQL Sentry Console you could use groups to effectively hide servers from unauthorized users, and we’ve always leveraged native security to ensure that there are no escalation of privileges risks – just because someone could “see” a server didn’t mean they could do anything with it. Although this was fine for many environments, in others, unauthorized users aren’t even allowed to know that certain servers exist! This was a problem.
Hence the need for this new capability, which allows a SQL Sentry administrator to truly restrict SQL Sentry users to specific SQL Servers via their Windows login:
Once associated, you simply select the servers that the user can access:
For existing environments, nothing changes automatically when upgrading to v6.2. However, once a user has been associated with at least one SQL Server, from that point forward they can no longer access any other SQL Servers monitored by SQL Sentry in any way. This goes for any new servers that may be added later – the user must be explicitly associated with those servers before they are accessible via the console.
Dashboard Event Overlay
A powerful new feature is the ability to overlay events across the Performance Advisor dashboard charts (see screenshots above). Previously, you might know that a SQL statement ran between X time and Y time, and you suspected that it was impacting performance, but when you flipped over to the dashboard there were no visual cues as to exactly when the event ran – you had to perform the correlation in your head. Now, if you use the Jump to Dashboard context menu from either the Event Manager calendar or the Top SQL tab, you’ll notice new horizontal indicators on the X-axes representing the selected event(s). When coming from the calendar, any event highlighted using the new enhanced highlighting options will have a corresponding indicator on the dashboard. This means that you can easily see what impact a particular user, application, or even SPID, is having on performance!
Streamlined Setup
Last but not least, we took a hard look at the installation process and initial setup wizard. Several screens were eliminated and/or consolidated, and the process was simplified overall. In addition, we’ve added a new Start Page which gives you quick access to some of the more commonly used functions, every time you launch the console:
We’ll be adding more to this page as time goes on.
Take v6.2 for a spin and let us know what you think. I’m at the PASS Summit this week, so stop by for an in person demonstration, as well as a sneak peak at some exciting new features coming in v6.5!
