SQLDIAG and SQL Server 2012


SQLDIAG is a data collection utility that is used for collecting T-SQL script output, perfmon data and profiler traces in a consolidated manner. This allows the database administrators for collect a single output without having to configure multiple data collection utilities for capturing the required data.

SQLDIAG has been shipping with the SQL Server product since SQL Server 2005. Now the reason I am writing this post is to talk about a specific issue that you can encounter when you already have a previous version of SQLDIAG installed on your machine along with SQL Server 2012.

Using the command below I am trying to execute a SQLDIAG data collection using a specific SQL Server 2012 SQLDIAG configuration file. The command that I used was to specify the output folder and the default SQLDIAG configuration file available at C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML:

C:\>sqldiag /O "F:\Temp\SQLDIAG Output" /I "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML"

The output that I got was:

SQLDIAG Output path: F:\Temp\SQLDIAG Output\

SQLDIAG Invalid SQL Server version specified.  SQL Server version 11 is not supported by this version of the collector

SQLDIAG . Function result: 87. Message: The parameter is incorrect.

The reason for the above issue is that my environment PATH variable has the path for a previous version of SQLDIAG listed before the path of the SQL Server 2012 SQLDIAG. My path variable has the directory “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\” listed before “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\” which is the default location of the SQLDIAG utility. The PATH variable is updated with the SQL specific directories during a SQL Server installation. In my case, I have a SQL Server 2008 R2 instance installed on my box. So the configuration file which specifies a data collection for a SQL Server 2012 is failing because I am using a SQLDIAG from a SQL Server 2008 R2 installation.

If I executed the following command, then my SQLDIAG initialization will work correctly:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqldiag.exe" /O "F:\Temp\SQLDIAG Output" /I "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML"

To summarize, you need to full qualify your SQLDIAG executable path when collecting SQLDIAG data from a machine which has multiple versions of SQLDIAG installed on the machine.

Awesomesauce SQL Server 2012: Performance Dashboard


I had written an article on SSWUG on how to track performance problems using Performance Dashboard. So, I thought it would be a good idea to blog about the new enhancements to the toolset for SQL Server 2012. The Performance Dashboard has been enhanced for SQL Server 2012 and is available for download on the Microsoft Download site.

And with this I also start the Awesomesauce series on my blog where I will keep posting about new features of SQL Server 2012 which I think are just plain awesomesauce!!

Once you install the Performance Dashboard, you need to do the following:

1. Each SQL Server instance you plan to monitor must contain the procedures and functions used by the queries in the reports.  Using SQL Server Management Studio (SSMS), open the setup.sql script from your installation directory (default of %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard) and run the script.  Close the query window once it completes.

2. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports.  Browse to the installation directory and open the performance_dashboard_main.rdl file.  Explore the health of your server by clicking on the various charts and hyperlinks in the report.

3. All of the remaining reports are accessed as drill through operations from the main page or one of its children.  For a detailed explanation of all installation requirements and guidance on how to use the reports, please see the help file, PerformanceDashboardHelp.chm

Now that the nitty gritty details are out of the way, what will you get when you view the dashboard and you will notice the dashboard having a new entry for XEvent sessions currently active on the instance.

image

 

Continue reading

XML Plans Saga–Breaking Dawn Part 2


I just completed this series and now there are four posts which points out four different ways of tracking down XML plans for troubleshooting or monitoring query performance:

XML Plans Saga –Twilight – A gist of what graphical execution plans look like.
XML Plans Saga –New Moon – Getting your hands dirty with the actual XML document representing the query plan
XML Plans Saga – Eclipse – Retrieving information about query plans using profiler trace events
XML Plans Saga – Breaking Dawn Part 1 – Fetching information from the cached plans in the SQL Server procedure cache

In this blog post (Breaking Dawn Part 2), I shall talk about the latest release in the SQL family… SQL Server 2012. With the new avatar of the Management Studio, you now have an Extended Event (XEvents) configuration wizard. Even though Extended Events have been around since SQL Server 2008, there wasn’t a wizard available for the configuration of the same. I am going to explain how XEvents can be used to capture execution plans and the existing XEvent Viewer in SQL Server 2012 Management Studio (SSMS) gives you a nice view.

I configured a XEvent trace with an asynchronous ring buffer target and captured only the query_post_execution_showplan event. The XEvent defition is shown below:

CREATE EVENT SESSION [QueryPlanCapture]
ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlos.cpu_id, sqlos.scheduler_id, sqlos.worker_address, sqlserver.database_name, sqlserver.is_system, sqlserver.nt_username, sqlserver.plan_handle, sqlserver.request_id, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username))
ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF)
GO

image_thumb10I used the Watch Live Data option in SSMS (Screenshot 1) to view the data being captured by the configured XEvent session active on the server. Now the XEvent viewer gives you a lot of options to aggregate and group data collect by the XEvent session. There are three things that I did with the GUI:

1. I first used the Choose Columns option to pick the columns that I wanted to be available in the events pane.

2. Then I picked the Grouping option to group on the object_name column value. You can only group on events which are visible in the events pane.

3. Then I picked the Aggregation option to provide me with a SUM of duration and cpu_time values for each of the event occurrences.

You can see the final output in Screenshot 2 below.

You can now see that I have all the execution plans for the execution of the stored procedure sp_helpdb. The Details pane actually gives you the option to view the graphical query plan. Another tip is that if you double-click on any of the events in the details pane, then either open up in a new pop-up window or a new query window depending on the field (eg. showplan_xml).

image_thumb18

As you see from the Screenshot below, there is hordes of information I can pickup from the output without having to start a profiler trace. Since you get the plan_handle and the actual query plan, you can use the queries from the previous posts in the series to get the set options, missing indexes, compilation and execution statistics for the query as well. However, the aggregation option in the XEvent Viewer gives you a head start on a lot of this activity already. image_thumb12

Hope all of you have some fun reading these posts on XML plans and put all this info to good use in your SQL environments!

Till next time Ciao and happy query plan spotting!!