SQL Server 2012 Early Adoption Cook Book Content and Videos

Posted on: March 8, 2012 11:16 pm

Last week during the MVP Summit, Joe (Blog | Twitter) and I spent the days working at Microsoft teaching and recording SQL Server 2012 content for the Developer Platform Evangelism team. Earlier today, the videos we recorded of the SQL Server 2012 content were posted on the SQL Server 2012 Early Adoption Cookbook site along with all of the other content needed to present topics related to SQL Server 2012.

The training content can be downloaded from the following link:

SQL Server 2012 Developer Training Kit Content

A comprehensive list of all the content contained in the kit, as well as instructions for how to build all of the VM’s required for performing the training, can be found in the following link:

SQL Server 2012 Developer Training Kit BOM

It was a ton of fun developing and teaching this content and I am sure that you will find it useful for learning about the new features of SQL Server 2012.

Posted in: SQL Server 2012

Be the first to Reply!

SQL Server 2012 Extended Events Update – 3 – Viewing Target Data

By: Jonathan Kehayias

Posted on: March 5, 2012 11:00 am

In SQL Server 2012 there are number of new ways to view target data generated by Extended Events Sessions, including a live streaming view as the events actually generate from the server, similar to the way SQL Server Profiler functions. For this blog post, I am going to use the Query Detail Tracking template that ships by default with SQL Server 2012, and instead of using the default ring_buffer target, make use of the event_file target to show how to use the UI functionality to read and process through files that are generated by Extended Events.

The Event Session has been setup with a 10MB maximum file size and 5 rollover files. The Event Session is also configured to start automatically when the Wizard closes, and the option to Watch live data on screen as it is captured is also selected. When you close the dialog, the Live Data viewer will open and connect to the SQL Server instance to begin reading the event stream from the server.

Live Data viewer options

There are number of commands that exist for the Live Data viewer that are accessible through the Extended Events menu or through the toolbar that displays when the Live Data viewer is the active window inside of SQL Server Management Studio. The buttons on the toolbar are in the exact same order as the menu items in the Extended Events menu.

If you don't have enough screen real estate for the toolbar to display completely, some of the buttons may not display but they will still be available through the drop down at the end of the toolbar as shown below.

The default view for any new Event Session in the Live Data viewer only shows two columns in the gridview, the event name and the timestamp for when the event was generated in the server. The reason for this limited view initially is that there are to many columns available in Extended Events, and it is impossible to provide a globally useful initial display in the environment. Instead for each event, all of the columns are provided in the Details pane below the main gridview.

Choosing Columns

Columns can be added to the table view a number of ways. The Choose Columns menu item in the Extended Events menu or on the toolbar can be used, or you can also click on the column names on the gridview and select the Choose Columns menu item from the context menu.

Additionally you can add a single column from the Details pane by right clicking on the column and selecting the Show Column in Table menu item from the context menu.

The Choose Columns menu items will open up a column chooser dialog that allows you to add one or multiple columns to the gridview. You can also change the column order in the gridview by selecting a column and clicking the up and down arrows to position the column appropriately in the list.

One functionality that you only get inside of the column chooser, is the ability to create a Merged Column for display. This allows you to take columns with different names but similar meaning, for example the statement column produced by the sql_statement_completed event and the batch_text column produced by the sql_batch_completed event, and display them in a single column to maximize the available real estate on the screen and simplify analyzing the data.

After creating the merged column, and adding a number of additional columns to the gridview, we now have a much more recognizable view of the data being generated by our Event Session.

Saving the display for future use

If we close out the Live Data Viewer, Management Studio will remember the layout that we last used with this Event Session the next time we open it against this server. However, if this Event Session is something that we are going to regularly use across multiple servers in our environment, or if we have multiple session definitions that leverage similar sets of events with different parameters configured the gridview layout won't default back to our settings in every scenario. For this reason, you can save the display settings so that you don't have to customize the UI repeatedly. This can be accomplished from the Extended Events menu or toolbars Display Settings item.

Filtering Live Data

While the Event Session is running, the UI can be used to filter the event data in the grid view on the client side, allowing you to limit the amount of information that is currently being displayed without actually having to change what is being collected by the Event Session in the targets.

These are just some of the more basic features of using the Live Data view inside of SQL Server 2012 for Extended Events. In the next post we'll take a look at some of the additional features that exist for working with data stored in files or when the Live Data viewer is in a disconnected state.

Posted in: Extended Events, SQL Server 2012

4 Comments

Looking at External Disk Performance using USB 3.0 and eSATA with SSD

By: Jonathan Kehayias

Posted on: February 26, 2012 12:47 am

I recently did some drive upgrades in my personal laptops and as a result I had a couple of SSD drives sitting around that I wanted to make use of. A few weeks ago I purchased a new OCZ Agility 3 240GB SATA III SSD from Newegg when it was on sale and at the same time I also purchased two MassCool USB 3 external enclosures to make use of the two 120GB OCZ Vertex 2 SSD’s that I would have sitting around. The cost for the MassCool enclosures was only $14.99 so I figured it would be a good way to reuse the SSD’s at the time. When the package from Newegg arrived, I immediately installed the SSD into one of my personal machines and then later I went about testing the performance of the USB 3 external enclosures with the older SSD’s. Initially the performance was impressive, and I posted a few tweets that attracted some attention and a few people requested that I blog my results.

To make this blog post something worth looking at, I ran a number of additional tests over the last few days using SQLIO and a varying configuration of drives with external enclosures. Specifically I tested two different USB 3 2.5” SATA enclosures, a separate eSATA 2.5” SATA external enclosure, as well as two USB 3 HDD’s that I already owned to get some performance information from each of the setups.

The external USB 3.0 HDD’s being tested are as follows:

The external enclosures being tested in these tests are as follows:

The SSD’s used with the external enclosures listed above are:

At the time that I bought the 120GB drive I paid close to $2.42 per megabyte for it, and at the time this was a good deal. When I purchased the 240GB SSD I paid right around $1.08 per megabyte, showing how much the prices have decreased nearly 3 years later. I happen to own a number of OCZ drives at home and they are one of my favorite brands personally because of the overall reliability I’ve had with them the last three years as well as the performance to cost ratio I’ve experienced. Initially I was just testing the MassCool USB 3 enclosures that I purchased, and I was incredibly happy with the performance that I had from them. However, after tweeting about the results, my friend Jose Chinchilla (Blog | Twitter) mentioned that I should also try out eSATA because it performed significantly better in his own tests. Based on this tweet, I set out to my local MicroCenter computer store to purchase a eSATA enclosure and while I was buying it, one of the sales representatives, a guy named Chris, approached me and asked what I was planning to do with the enclosure because USB 3.0 should be faster for SSD’s. The numbers he quoted didn’t match my previous testing, so he made a recommendation for a specific USB 3.0 enclosure, and I figured, for $20 it was worth testing, so I bought an extra Hornettek Panther USB 3.0 device for comparison testing along with the Vantec NexStar CX eSATA enclosure I had already selected.

For the tests, I ran a short set of tests using SQLIO that I previously blogged about on my blog post about the Powershell parser for SQLIO output. The results from the tests are below:

The two external USB 3.0 HDD drives had very similar performance characteristics, and they beat their USB 2.0 counterparts performance wise significantly. I’ve been incredibly happy with my USB 3.0 HDDs overall for the last few years and I only purchase USB 3.0 HDD drives based on my performance tests a few years ago. However, when compared with the USB 3.0 external enclosures with the SSD’s, the performance difference is quite significant. One of the problems I’ve had lately is being able to fit all of the virtual machine hard drives, VHD’s for Hyper-V when using my dual boot Hyper-V host VHD, as well as my original VMware Workstation VMDK’s for classes, and even the VirtualBox virtual disk images (VDI) from my blog series on building a completely free playground. Using the SSD’s with USB 3.0 definitely makes storing my virtual hard disk files on an external array much more feasible performance wise, and my only real limitation from testing is the size of the external device.

However, the performance of the eSATA external enclosure is incredibly better, with the side trade off that I can only have 1 of them attached to my laptops at a time, and for my personal laptop, the fact that the eSATA enclosure requires not only the eSATA port for throughput, but also the additional USB 2.0 port for power really limits what I can do as far as having multiple disks connected to the laptop. To be honest, this is something that I can live with given that I also have swapped out my CD/DVD ROM bay for a replacement New Mode US second HDD conversion. This means that I can have two 240GB OCZ SSD’s in my E5420, which is a similar configuration to the dual 256GB Dell SSD configuration in my Precision M6500 for work, while being able to move VM images between the two machines using my older 120GB SSD’s with fantastic performance.

If you are looking at really high performance external hard disk configurations for scalability, I would highly recommend looking at either eSATA with an extra SSD drive or if you need multiple devices and you have USB 3.0, consider going that route. Either will outperform your existing options significantly. For the time being, I am going to stick with one eSATA device, which is compatible with both of my laptops, and one USB 3.0 device, which only works with my M6500 at USB 3.0 speeds. My E5420 only has USB 2.0 onboard, so it makes more sense for me to stick with eSATA for the main shared drive.

Posted in: Benchmarking, Building a Test Environment, General, Hardware

5 Comments

What a year it has been!

By: Jonathan Kehayias

Posted on: February 23, 2012 11:00 am

A year ago today, Paul Randal (Blog | Twitter) first blogged about my joining the SQLskills team as the first full time employee. I can still remember the first time that I met Paul at SQL PASS in 2008, and I am still amazed that I work with the best in the industry over a year after working out the details of my employment with SQLskills. I remember the first time that Paul and I discussed the potential for me joining SQLskills, and how hard it was to tell him at the time that there was no way it would work out for either of us. Joshua Harris once said, “The right thing at the wrong time is the wrong thing” and nothing could be further from the truth as far as my employment with SQLskills. When Paul and I first discussed the possibility of me joining the SQLskills team, I knew ahead of time that my Army Reserve unit was pending a year long mobilization in early 2011 so that became the first, and as it turned out, last point of discussion about employment feasibility. Legally there is no requirement for me to disclose this type of information when seeking employment, and further if disclosed this information can not be used as a part of the selection criteria, but I have always not only been proud of my service in the US Army Reserves, but have also been incredibly sensitive to the needs of potential employers when discussing job opportunities. The end result was that we mutually agreed that the timing wasn’t right and we’d keep open communications about changes should they occur, such that if an employment possibility reopened we’d discuss it again further. Fast forward a few months and the opportunity once again posed itself, but at this point it was clear the circumstances worked out to be mutually beneficial and we ultimate struck a deal that lead to me joining SQLskills full time as employee number three (it actually says this on my semi-annual review).

For nearly six years, Paul has been a fabulous mentor of mine, dating back to before he left the product group at Microsoft, based on our forums interactions. We actually have a very interesting background for solving problems related to SQL Server that have happened in very public venues, for example the Diskeeper 2010 issues that we figured out on Twitter late in 2010. One of the highlights of my career as a DBA was the first time that Paul asked me to call him out of the blue, when he first broached the topic of joining SQLskills as a consultant. I remember discussing things, and at the end of the call where we mutually agreed that it wasn’t a good fit at the time, wondering what in the world I had just done. Looking back, that had to be one of the best decisions of my life, though I didn’t realize it at the time. Over the ensuing months, I had the opportunity to grow as a SQL Server professional, working in one of the most challenging environments of my life, while learning more about the internals of SQL Server than I ever thought I would. Before I ever accepted a position with SQLskills, I privately took the written exam for the new SQL Server MCM and passed, and upon passing the written exam, I privately scheduled my lab exam with Joe Sack. The day after Paul first blogged about me joining SQLskills, I passed the Lab Exam for the SQL Server MCM and became the fourth, and youngest, SQL Server MCM under the new program.

In the year since the initial announcement I’ve made a number of changes. First off, being a consultant is not what it might seem from the outside looking in. This is without a doubt one of the hardest jobs I’ve ever had, and every day brings forth new challenges both technically and personally. Additionally I’ve had to learn how to be an efficient trainer as an instructor for not only the SQLskills Immersion Events, but also the Microsoft SharePoint MCM program as well. I definitely have the best mentors around for learning how to not only be an effective consultant, but also how to be an effective trainer as well. Since joining the SQLskills team I have had a top rated session at SQLRally in Orlando, FL, presented at multiple Immersion Events, both public and private, as well as doing my first pre-conference seminar at PASS 2011 on Extended Events. Along the way I’ve experienced both success and failure, but no matter what the feedback, I’ve been able to grow from the experiences in an incredibly positive manner.

Personally I can’t imagine having a better year than the last one overall, but I am sure going to try!

Posted in: General

6 Comments

Extended Events PerfObject Events

By: Jonathan Kehayias

Posted on: February 15, 2012 11:21 pm

SQL Server 2008 R2 Service Pack 1 provides a new set of Events in Extended Events to collect performance counter data from the Windows OS that would be really useful to monitoring SQL Server. The first place I can find that they were mentioned is on a blog post by Mark Weber, a PFE for SQL and SAP at Microsoft. However, a few weeks ago a question was asked about these counters one of the forums and the question was around how to use them. I looked at the Events and found out that they aren’t really useable in their current implementation, something that is disappointing since being able to collect the data provided by these Events would really benefit most DBA’s out there.

If you look at the Events and columns, these events collect information about the Logical Disk, Processor, Process for the SQL instance, and System at 15 second intervals and makes the data available through Extended Events.

SELECT name, description
FROM  sys.dm_xe_objects
WHERE name like 'perfobject_%'

name	description
perfobject_process	Returns a set of counters associated with the Process performance object. The event occurs once every 15 seconds for both the SQL Server and SQL Agent processes.
perfobject_system	Returns a set of counters associated with the System performance object. The event occurs once every 15 seconds.
perfobject_logicaldisk	Returns a set of counters associated with the Logical Disk performance object. The event occurs once every 15 seconds for each hard or fixed disk drive.
perfobject_processor	Returns a set of counters associated with the Processor performance object. The event occurs once every 15 seconds for each processor in the system.

If we look at the columns, we’ll see that the columns actually represent the individual counters under the categories exposed by the Event names. For example, the perfobject_logicaldisk event returns the following columns:

SELECT
    object_name, 
    name AS column_name, 
    description
FROM  sys.dm_xe_object_columns
WHERE object_name = 'perfobject_logicaldisk'
  AND column_type = 'data'
ORDER BY object_name, name

object_name	column_name	description
perfobject_logicaldisk	average_disk_bytes_per_read	Shows the average number of bytes transferred from the disk during read operations.
perfobject_logicaldisk	average_disk_bytes_per_transfer	Shows the average number of bytes transferred to or from the disk during write or read operations.
perfobject_logicaldisk	average_disk_bytes_per_write	Shows the average number of bytes transferred to the disk during write operations.
perfobject_logicaldisk	average_disk_queue_length	Shows the average number of both read and write requests that were queued for the selected disk during the sample interval.
perfobject_logicaldisk	average_disk_read_queue_length	Shows the average number of read requests that were queued for the selected disk during the sample interval.
perfobject_logicaldisk	average_disk_seconds_per_read	Shows the average time, in seconds, of a read operation from the disk.
perfobject_logicaldisk	average_disk_seconds_per_transfer	Shows the time, in seconds, of the average disk transfer.
perfobject_logicaldisk	average_disk_seconds_per_write	Shows the average time, in seconds, of a write operation to the disk.
perfobject_logicaldisk	average_disk_write_queue_length	Shows the average number of write requests that were queued for the selected disk during the sample interval.
perfobject_logicaldisk	current_disk_queue_length	Shows the number of requests outstanding on the disk at the time that the performance data is collected.
perfobject_logicaldisk	disk_bytes_per_second	Shows the rate at which bytes are transferred to or from the disk during write or read operations.
perfobject_logicaldisk	disk_read_bytes_per_second	Shows the rate at which bytes are transferred from the disk during read operations.
perfobject_logicaldisk	disk_reads_per_second	Shows the rate at which read operations are performed on the disk.
perfobject_logicaldisk	disk_transfers_per_second	Shows the rate at which read and write operations are performed on the disk.
perfobject_logicaldisk	disk_write_bytes_per_second	Shows the rate at which bytes are transferred to the disk during write operations.
perfobject_logicaldisk	disk_writes_per_second	Shows the rate at which write operations are performed on the disk.
perfobject_logicaldisk	free_megabytes	Shows the unallocated space, in megabytes, on the disk drive. One megabyte is equal to 1,048,576 bytes.
perfobject_logicaldisk	instance_name	The logical disk drive name
perfobject_logicaldisk	percent_disk_read_time	Shows the percentage of time that the selected disk drive is busy servicing read or write requests.
perfobject_logicaldisk	percent_disk_time	Shows the percentage of time that the selected disk drive is busy servicing read requests.
perfobject_logicaldisk	percent_disk_write_time	Shows the percentage of time that the selected disk drive is busy servicing write requests.
perfobject_logicaldisk	percent_free_space	Shows the percentage of the total usable space on the selected logical disk drive that is free.
perfobject_logicaldisk	percent_idle_time	The percentage of time during the sample interval that the disk was idle.
perfobject_logicaldisk	split_io_per_second	The rate at which I/Os to the disk were split into multiple I/Os.

This all seems good, until we actually use the Events in an Event Session and take a look at the data being returned.

CREATE EVENT SESSION [XE_PerfCounters] 
ON SERVER 
ADD EVENT sqlserver.perfobject_logicaldisk 
ADD TARGET package0.ring_buffer;
GO

Unfortunately, the counters are returning Raw values for the Event and the necessary Base counters that are required to give these values any useful meaning have been left out of the Events data. Looking at this in my test environment, it appears the counter values pulled for the perfobject_ Events are pulled directly from Win32_PerfRawData_PerfDisk_LogicalDisk, but if you look at the CookingType requirements for the counters in Win32_PerfFormattedData_PerfDisk_LogicalDisk the raw values have to be calculated by their base values for them to have meaning:

I’ve submitted Connect Item 725167 for this and I really hope that this one gets fixed in a future Cumulative Update or Service Pack.

Posted in: Connect, Extended Events, SQL Server 2008, SQL Server 2012

2 Comments

SQL Server 2012 Extended Events Update – 2 – The SSMS UI Part 2

By: Jonathan Kehayias

Posted on: February 13, 2012 10:00 am

In the first post in this series, SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface, we looked at how to use the New Session Wizard in SQL Server 2012 to define an Event Session. In this post we’ll compare the Wizard to the standard New Event Session dialog that can also be used for creating and Event Session in SQL Server 2012. The New Event Session dialog is the same dialog that is used for editing an existing Event Session on the server, and can be accessed from the Extended Events node in Object Explorer, just like the New Session Wizard can be.

Rather than opening up with an Introduction Page, the New Session dialog opens directly allowing you to begin configuring your session. I like to think of the New Session dialog as the power user method of creating a new session, and as we’ll see in this post, it can actually takes less steps to configure an Event Session using the New Session dialog over the New Session Wizard.

The General page of the New Session dialog allows you to specify a name for the Event Session, as well as to select a template to create the Event Session from. You also have the option to specify whether the Event Session should start automatically when SQL Server starts, whether to start the Event Session immediately after creating it with the dialog, and whether you want to immediate begin viewing the Event Session data in the Live Viewer. If you compare this page to the first three, and final pages of the wizard you will see that we have a much more concise configuration using the dialog so far. If you click on the Events page on the left hand side, the Events page will show in the dialog allowing you to customize the events being collected by the Event Session.

On first look, the Events page looks very similar to the Select Events To Capture page from the Wizard. The Event search functionality, and the ability to filter the events by Category and Channel, as well as how you add and remove Events from the Event Session is identical. However, in the New Session dialog, two additional buttons exist, a Configure button, (circled in red) that allows you to begin configuring the Events that have been added to the Event Session, and a Select button, (circled in green) that allows you to return to the Event selection screen from the configuration screen. When the buttons are clicked, the screen will collapse/expand left and right.

The biggest difference between the functionality provided by the New Session Wizard versus the New Session dialog is the level of granularity that you have with assigning Actions or Global Fields, and Predicates or filtering, to the Events that have been added to the Event Session. In the New Session Wizard, any Action or Predicate that is added to the Event Session, is added across the board to all of the Events in the session. The same functionality can be achieved using the New Session dialog by using the multi-select functionality of the UI to select all of the Events, and then adding the appropriate Actions and Predicates. However, typically we don’t actually need the Actions and Filters applied to every Event in the Event Session, and since Actions and global Predicates incur an overhead for data collection, even though it is incredibly small, as a performance best practice. By selecting a single Event, new Actions can be added to the the Event, or as shown below, filtering can be applied at the individual Event level, which allows the filtering definition to be against any column on the Event, not just the shared subset of columns, or global files, across all Events.

If multiple events share the same columns, for example, the sqlserver.sql_batch_completed and sqlserver.sql_statement_completed Events in our session, you can also multi-select those Events and define filtering specific to both of those Events.

Complex Predicates can be defined through the use of the right-click context menu in the Filter table. Keep in mind that Predicates in Extended Events allow short circuiting logic to occur, so the order of Predicates matters during evaluation time. The context menu will allow you to insert a new Predicate above or below the currently selected Predicate in the UI, add or delete additional clauses to the existing Predicate list, group subsets of clauses together so that they evaluate as a complete set, ungroup previously grouped sets of clauses, and to toggle the Not operator which evaluates for the negation of the clause being configured.

A really good example of a complex predicate configuration can be seen by looking at the system_health Event Session and the Predicate on the sqlserver.error_reported event.

The Event Fields tab, will allow you to turn on/off the collection of any customizable columns for the Event that is currently selected. For example, the sql_batch_completed Event has a customizable column for the batch_text, which is turned on by default. If you don’t need the batch text, for example, you may be collecting the tsql_frame action which is much smaller because you know that you will be able to get the batch information from the cache at the point you are analyzing the results, you can turn it off by unchecking the checkbox next to it.

The Data Storage page of the dialog, allows you to configure the targets for the Event Session. The biggest difference here is that you get full use of all of the targets available in Extended Events, not just the event_file and ring_buffer targets provided by the Wizard, though these will typically be the targets that you will use the most.

The Advanced page, allows you to customize the Event Session Options to define how the session will be setup in the Extended Events Engine when it starts.

Once all of the configuration for an Event Session has been completed, you can script the Event Session DDL using the standard SSMS Script button at the top of the UI, or you can create the Event Session immediately by clicking OK. If you need to change the Event Session definition after creating it, the Session Properties dialog can be opened from the right-click context menu in Object Explorer for the session. The Session Properties dialog is exactly the same as the New Session dialog.

In the next post we’ll look at the target data viewer in SLQ Server 2012 and how to use it for analyzing the captured Events from an Event Session.

Posted in: Extended Events, SQL Server 2012

Be the first to Reply!

Network Binding Order Warning during SQL Server Failover Cluster Setup

By: Jonathan Kehayias

Posted on: February 9, 2012 12:06 am

Yesterday I was asked by email about a problem that someone encountered associated with a SQL Server Failover Cluster configuration that I have run into a number of times myself, and I have had questions about repeatedly in the past. The problem is that during the SQL Server Setup Validation of the environment, a warning is raised stating that the Network Binding Order is incorrect for the environment. If you click on the specific Setup Validation Warning you will get a box like the following:

What this warning is telling you, is that you have multiple network interfaces configured on the server, and the default binding order for the interfaces places the current Domain access interface in a position that is not the first interface for the server. This can be changed by adjusting the network binding order for the server in the Network Properties. To make this change, open up the Network and Sharing Center by clicking on the network connection on the system tray and then clicking the Open Network and Sharing Center link.

When the Network and Sharing Center opens, click on the Change adapter settings link on the left hand side.

Then when the Network Connections window opens, you have to press the Alt key to open up the window context menu so that you can then click on Advanced and Advanced Settings.

When the Advanced Settings window opens, you will be on the Adapters and Bindings tab, which allows you to change the binding order for the network interfaces on the server.

If you click on the appropriate interface for the domain connection, you can move it up to the first position in the binding order which will eliminate the warning in SQL Server Setup.

If changing the binding order does not resolve the warning, there may be a disabled or ghost network adapter in the system as discussed in the following KB article. (http://support.microsoft.com//kb/955963)

Posted in: Database Administration, General, SQL Server 2008

3 Comments

SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface

By: Jonathan Kehayias

Posted on: February 6, 2012 1:00 pm

For the most part I have been relatively quiet about the coming changes in SQL Server 2012 with regards to Extended Events. Primarily this has been to allow the new features of the product to become fully baked to ensure that the information would continue to be applicable as the product lifecycle progressed, and there have been a number of major changes that have made this decision a really good one. With SQL Server 2012 in it’s RC0 phase, and based on the responses I have seen to a number of feedback items for bugs on Microsoft SQL Server Connect, like the recent one for the template issue I blogged about on my blog post, Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0, I’ve decided to go ahead and start a new series of posts that outline the new features of Extended Events in SQL Server 2012. I can’t think of a better way to start off a series on the new features in SQL Server 2012 for Extended Events than the new SQL Server Management Studio User Interface for Extended Events in a couple of blog posts.

For this initial post, we’ll take a look at some of the features that replace existing SQL Profiler functionality that most DBA’s tend to use in their day to day operations. To start off this topic, the first thing you need to know is that there is a new node for Extended Events under the Management folder in Object Explorer for SQL Server 2012.

If you happen to be connecting to a SQL Server 2008 server using SSMS from SQL Server 2012, this node will not exist. This is due to the fact that UI for SSMS in SQL Server 2012 are not backwards compatible with SQL Server 2008, even though Extended Events exist in SQL Server 2008. At some point when SQL Server 2012 actually releases to manufacturing (RTM), I will release an update to my SSMS Addin for Extended Events that back ports compatibility for SQL Server 2008 to Management Studio 2012, allowing full integration between SSMS 2012 and SQL Server 2008.

Within the scope of SQL Server 2012, the UI provides a lot of new functionality that should simplify the implementation and usage of Extended Events for most DBA’s. One of the best enhancements is the ability to create an event session using the New Session Wizard to define an event session based with the least number of steps possible, possibly using an template for the event session, or manually defining a custom configuration that is applied to all of the events configured for the session.

By opening the New Session Wizard, immediately a Introductory page is presented that can be bypassed by selecting the option to Do not show this page again:

Since this page will typically slow down the creation of an event session for use, I would typically check this option before clicking on Next. The Set Session Properties page will allow you to specify a name for the session as well as whether or not the event session will startup automatically when SQL Server starts. This can be very useful for troubleshooting an infrequent problem that does not predictably occur and you need to ensure that the session data is collected whenever the problem next occurs. Some examples of where this might be applied will be shown in future posts in this series.

The New Session Wizard provides the ability to create the new event session based on a previously created template, or one of the templates provided by default with SQL Server 2012. Keep in mind that in the RC0 build, and unfortunately the RTM release of SQL Server 2012, the Activity Tracking template has a bug in the XML definition that will cause this UI to error out. This was documented by Mike Wachal, the PM for Extended Events at Microsoft on his blog post Activity Tracking event session template is broken, but a fix for the problem in the XML is available in my blog post Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0. After replacing the broken template with the one attached to my previous blog post we can select it in the UI.

The alternative to using an existing template is to create a blank event session by selecting the option to Do not use a template. If a template is selected, once you click Next the events from the template will be displayed in the Select Events To Capture page, which also displays the events available in Extended Events and information about the data returned by the events. The event library can be searched a number of ways to simplify finding the correct events for the session. The most common way to search, once you start learning the events that are available, would be to start typing the event name in the textbox (green circle below) and the results will dynamically begin filtering out the events that don’t match the search text. If you click on a specific event in the table, the event description and information about the columns returned by the event will be displayed (purple box below).

However, if you don’t know the specific events that you want, but you know the general category that the events apply to you can make use of other search options in the UI as well. Events in Extended Events are broken down by two attributes, a Category (Keyword in the DMVs) and a Channel, that make them compatible with Event Tracing for Windows (ETW). The Category is similar to the trace category that the existing Trace events have and can be used for logically grouping events similar to the way SQL Profiler groups events in previous versions of SQL Server. The Channel aligns with the channels you would see with ETW. By default one of the Channels is excluded in the UI, the Debug Channel. Debug events are focused towards internal debugging tasks and are not of general purpose use by most DBAs. These events tend to be counting in nature, or can fire incredibly frequently. If you want to see the Debug events in the UI, you can click the drop down and check the checkbox for the Debug Channel and they will be available.

Additionally you can filter out specific categories by clicking the Category dropdown and unchecking specific Category names from the selection. To add an Event to the session, you can double click on the event, or you can select multiple events using Ctrl or Shift + click on the event names and then clicking the arrow that points to the right. Alternately, you can also remove events using a double click or by highlighting the event and clicking the arrow that points to the left.

After adding events to the session, the next page allows you to specify the Global Fields, known as actions in Extended Events, that you want added to each of the events in the session. If you look at the columns being returned by the events in Extended Events, there are significantly fewer data elements being returned at the individual event level. Many of the trace columns map to the Global Fields (actions) in Extended Events and can be added as needed to the events. The goal was to minimize the size of the firing events allowing additional information to be added as needed to maximize the performance of Extended Events firing.

After selecting the global fields to add to the Event Session and clicking Next, the Set Session Event Filters page is displayed where you can define filtering (known as predicates) on the events in the session. Any filters that were configured as a part of the template will be displayed in the upper table, while new filters that will be applied to all of the events in the session can be added to the bottom table. New filters can only be created on the columns that are available for all of the events in the event session, which typically there won’t be any if using multiple events, or on the global fields available to Extended Events.

This is a very restrictive functionality of the New Session Wizard that was put in place to provide a parity for session creation to what most users would expect from SQL Server Profiler. I’ll show more about how this is not the best thing when we look at the other parts of the new UI in SSMS in other posts. Once the filters have been created, the next step is to define the event storage. The New Session Wizard restricts you to the event_file and ring_buffer targets, which are going to be the most commonly used targets by most DBAs since they retain the full event data and do not apply additional processing to the events. For an event session that is going to be collecting data for a long period of time, or data that generates at a fast rate, the event_file target should be used, and similar to SQL Trace you can setup the maximum file size and whether or not file rollover should occur. If the event session is going to be collecting data for a short period of time or where the event predicates will restrict the session to only collecting a small amount of data, the ring_buffer target will generally be a good choice.

Once the data storage has been configured the session can be created by clicking Finish, or you can click Next to get to the Summary page which will allow you to review all the configured options for the Event Session and Script the session definition for further changes to the DDL if necessary.

Once the Event Session is created, the last page provides you the opportunity to start the event session and to open the Live Data Viewer for the event session which is similar to the SQL Server Profiler view from SQL Trace.

In the next blog post I’ll show the New Session dialog which is not a wizard based implementation and why it provides a much more robust method of creating an event session in SQL Server 2012.

Posted in: Database Administration, Extended Events, SQL Server 2012

Be the first to Reply!

New Article Online – Great SQL Server Debates: Buffer Cache Hit Ratio

By: Jonathan Kehayias

Posted on: January 19, 2012 4:28 pm

My latest article on Simple-Talk was published this morning. In this article I dig into the actual meaning of one of the performance counters I often see mentioned on the forums, but in a completely incorrect context.

Great SQL Server Debates: Buffer Cache Hit Ratio

Posted in: Articles Online, Database Administration, General

Be the first to Reply!

Does filing bugs on Microsoft Connect for SQL Server work?

By: Jonathan Kehayias

Posted on: January 16, 2012 10:29 pm

In a word; YES! In a lot more words, not always in the way that we want it to, but there are plenty of cases where it actually works and changes are made to the product as a result.

Now with that said, it doesn’t work all the time, and it helps to realize that what is important to us as an individual user might not be important to the product as a whole. Before you post comments, I am sure that there are plenty of cases out there where people can say that Microsoft Connect for SQL Server is broken. I have personally been irritated to the point of posting negative comments on Twitter about the whole Connect process. I feel that it is about time that I show the other side of the story as well and talk about some Connect successes that have occurred in the past year, and of course what better topic to do this with than Extended Events. Over the next few weeks, I’ll post a couple of different examples of Connect actually working and bringing about changes to the product that are beneficial to the community, starting with this post.

Extended Events does not track insert statements

This Connect item is actually incorrectly titled and is based on some confusion about what the sqlserver.plan_handle action actually returns when executed in the engine. I blogged about this with much more detail last year in my blog post; What plan_handle is Extended Events sqlserver.plan_handle action returning?

If we revisit the Connect item there is a note that the sql_statement_completed event in SQL Server 2012 now includes a parameterized_plan_handle customizable column that can be used to retrieve the parameterized plan handle for statements that are auto-parameterized by SQL Server during their execution. Taking the same original demo code from my previous blog post, we can now see how this Connect item has improved the ability to find information about plan caching in SQL Server 2012:

-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name='SQLStmtEvents')
    DROP EVENT SESSION SQLStmtEvents 
    ON SERVER;
GO
CREATE EVENT SESSION SQLStmtEvents
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    SET collect_parameterized_plan_handle = 1
    ACTION (sqlserver.client_app_name,
            sqlserver.plan_handle,
            sqlserver.sql_text,
            sqlserver.tsql_stack,
            package0.callstack,
            sqlserver.request_id)
--Change this to match the AdventureWorks, 
--AdventureWorks2008 or AdventureWorks2008 SELECT DB_ID('AdventureWorks2008R2')
WHERE sqlserver.database_id=9
)
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON)
GO
 
-- Start the Event Session
ALTER EVENT SESSION SQLStmtEvents 
ON SERVER 
STATE = START;
GO
 
-- Change database contexts and insert one row
USE AdventureWorks2008R2;
GO
INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
VALUES(getdate(),SYSTEM_USER,-1,-1,-1,'ErrorProcedure',-1,'An error occurred')
GO 10
 
-- Drop the Event
ALTER EVENT SESSION SQLStmtEvents
ON SERVER
DROP EVENT sqlserver.sql_statement_completed;
GO

-- Retrieve the Event Data from the Event Session Target
SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('xs:hexBinary((event/data[@name="parameterized_plan_handle"]/value)[1])', 'varbinary(64)') as parameterized_plan_handle,
    event_data.value('xs:hexBinary((event/action[@name="plan_handle"]/value)[1])', 'varbinary(64)') as plan_handle,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
FROM(    SELECT evnt.query('.') AS event_data
        FROM
        (   SELECT CAST(target_data AS xml) AS TargetData
            FROM sys.dm_xe_sessions AS s
            JOIN sys.dm_xe_session_targets AS t
                ON s.address = t.event_session_address
            WHERE s.name = 'SQLStmtEvents'
              AND t.target_name = 'ring_buffer'
        ) AS tab
        CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS split(evnt) 
     ) AS evts(event_data)

If we look at the output of this, we will get the parameterized plan handle for each subsequent call of the statement after the initial call caches the parameterized plan into the cache.

If we plug one of the original plan_handle values from the sqlserver.plan_handle action into a query of sys.dm_exec_cached_plans() it will return nothing, but using the new parameterized_plan_handle value from the customizable column will give us the appropriate cached plan for the statement from cache:

-- Use the plan_handle from one of the Events action to get the query plan
DECLARE @plan_handle varbinary(64) = 0x06000900DFC9DD12608B18EE0100000001000000000000000000000000000000000000000000000000000000
SELECT * 
FROM sys.dm_exec_query_plan(@plan_handle)
GO

-- Use the parameterized_plan_handle from the same Events to get the query plan
DECLARE @plan_handle varbinary(64) = 0x06000900DD8D6D08601E70EE01000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
SELECT * 
FROM sys.dm_exec_query_plan(@plan_handle)
GO

Now, you might point out the different lengths of the plan handles in the above two queries. If you look back at the source, the same code is being used to perform the xhexBinary conversion in the XML so the values are exactly the same as what was originally provided by the event and the action. The non-parameterized plan is not cached because it is not likely to be reused, which is why we have the auto-parameterized plan in cache.

Posted in: Connect, Extended Events, SQL Server 2012, SQL Server Denali

Be the first to Reply!

The Rambling DBA

Live Data viewer options

Choosing Columns

Saving the display for future use

Filtering Live Data