Get your log shipping restore to run faster

Recently I was working on a customer environment where the requirement was to use a log shipped secondary database for reporting purposes. The catch here was that the restore of all the transaction log backups needed to complete within a fixed time.

Right at the outset, I would like to point out that the restore of transaction logs are dependent on a number of parameters like disk subsystem performance, number of transactions present in the log backup, resource availability on the server where the restore is occurring. Considering that all the factors remain constant, there is a basic difference between a secondary log shipped database in No Recovery and in Standby mode. In Standby mode, a partial recovery is done with the help of a transaction undo file to get the database into a read-only state. This partial recovery operation and undo that occurs while performing the next log restore (with the use of the transaction undo file) requires time and resources and can slow down the time of restore for each transaction log backup that is restored on the secondary database, especially if there are resource contention issues on the server. In this blog post, I shall outline how you can workaround the same in case you have to meet strict restore time SLAs.

Continue reading

Awesomesauce: Deprecated Features usages

SQLServer:Deprecated Features is a performance object that is available since SQL Server 2005. The number of deprecated features have definitely increased since SQL Server 2005. In SQL Server 2012, there are a number of deprecated features whose use on your SQL Server 2012 instance can be determined through the use of this performance object.

Some of the common features that you might be interested in finding out the usage are DBCC REINDEX, DBCC SHOWCONTIG, DBCC INDEXDEFRAG. Once you have found the usage of these, you can find out which application uses this by looking up the SQL Server default trace.

Example:

declare @tracename nvarchar(4000)

select @tracename = path from sys.traces where id = 1
select TextData, StartTime, EndTime, spid, ServerName, HostName, error, SessionLoginName ,ApplicationName, TransactionID
from fn_trace_gettable(@tracename,default)
where EventClass = 116
and (TextData like '%showcontig%' or TextData like '%reindex%' or TextData like '%indexdefrag%') 

Download the T-SQL script to find out the deprecated features being used on your SQL Server 2012 instance using the script below.

Script name: Deprecated_Features.sql

The above is a good way to determine the use of deprecated features and which requires a change in your SQL Server environment.

References
Discontinued Engine Features in SQL Server 2012
http://msdn.microsoft.com/en-us/library/ms144262.aspx
Deprecated Engine Features in SQL Server 2012
http://technet.microsoft.com/en-us/library/ms143729(SQL.110).aspx

Awesomesauce: Perf Dashboard on Native SSRS

I had been asked the question about using the Perf Dashboard reports from a Reporting Services instance multiple times. Though this was not the intention of launching the SQL Server Performance Dashboard Reports but there is a compelling need at times to have these accessible from a web URL. In today’s age, you will find a lot of DBAs monitor their SQL Server instances remotely. Sometimes, there is another layer of complexity added to this when they want to just look at the health of the SQL Server instance by accessing a URL exposed through a corporate server in their environment. In such cases, you wouldn’t want to jump through a few hoops of setting up your VPN connection and blah blah.

So with all that in mind, let’s talk about how you can get your SQL Server 2012 Performance Dashboard to your existing SQL Server Reporting Services instance.

  • First you need to create a new Reporting Services Project using SQL Server Data Tools (SSDT).
  • Use the solution explorer to add all the existing performance dashboard reports from the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard folder.
  • Modify the project properties to ensure that the data source gets written to the same folder as the reports.
  • Create a shared data source pointing to your SQL Server instance.
  • Open each report that was imported into the project and modify the data source properties to use the shared data source that you created in the above step.
  • Modify the project deployment properties as shown below.

image

Now you are ready to deploy your Performance Dashboard SSRS project to your reporting services instance.

What can you do next?

You can now set up Report Server subscriptions for the set of reports that you wish to receive via email. You can get a status report of your SQL Server instance without even having to lift a finger and that to right in your mailbox! Smile

Note that Performance Dashboard is a tool which is provided “AS-IS” by Microsoft. The steps mentioned above will help you deploy the existing Performance Dashboard reports to a SQL Server Reporting Services instance. However, the failure to deploy these reports as mentioned above is not liable for support by Microsoft SQL Server Support team.

Awesomesauce SQL Server 2012: System Health Session

There have been a spate of new features and enhancements to existing functionality in SQL Server 2012. The System Health Extended Events session is one of the benefactors. I had previously blogged about the usage of the System Health Session data using custom SSMS Reports in a four part series. Now it is time to talk about the enhancements to the System Health Session’s predecessor.

The SQL Server 2012 health session adds additional events to the default extended event health session running on the instance which is listed below to collect data for the following:

  • Out of memory errors for CLR and the database engine using the events:
    • sqlclr.clr_allocation_failure
    • sqlclr.clr_virtual_alloc_failure
    • sqlos.memory_broker_ring_buffer_recorded
    • sqlos.memory_node_oom_ring_buffer_recorded
  • Scheduler health issues using the events:
    • sqlos.scheduler_monitor_deadlock_ring_buffer_recorded
    • sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded
    • sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded
    • sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded
    • sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded
    • sqlos.scheduler_monitor_system_health_ring_buffer_recorded
  • The results of the sp_server_diagnostics component
  • Security and connectivity errors encountered using the events:
    • sqlserver.connectivity_ring_buffer_recorded
    • sqlserver.security_error_ring_buffer_recorded

imageAdditional wait types are also tracked which adds to the existing list of waits captured by this session. Since SQL Server 2012 Management Studio allows you to view the configured Extended Events session on the SQL Server 2012 instance, you can get to the system_health session using the following steps from Object Explorer (see Screenshot 1):

Expand Management –> Extended Events –> system_health.

The Script Session as option now provides you with a way to view the session definition. Another enhancement made to this session is the target for this session is now also a .xel file which by default is located in your SQL Server LOG folder. The target definition for the System Health session are now:

a. Ring Buffer (as was in SQL Server 2008) 
b. XEL file – With a maximum size of 5 MB and a file retention policy of 4 files.

So now you can leverage the management studio XEvent UI to view the results. You can right-click on the ring buffer target in the UI and click on the View Target Data option which will give you an XML document. image

The coolest thing that I like here is the viewer’s capability to show you all the data from all the .xel files. Once you have the events loaded, you should see a view as shown in Screenshot 2. This gives you a list of all the events that were generated. You can sort on the event columns by clicking on them. The progress of the sorting will be visible towards the top right hand corner of SSMS as shown in Screenshot 3. Now that you have the basic view, let’s find out what more can we do with the System Health Session data displayed.

image

You can click on the Choose Columns option in the toolbar, select the columns that you want to view additionally apart from the name and timestamp by using the Available Columns list. See Screenshot 4 for an example.

image

Now that you have selected the additional columns, you can also set grouping (using the Grouping button) to view each of the event name categories, the number of events reported and aggregations on the column values (using the Aggregations button). So finally, I landed up with a view as shown below in Screenshot 5. What’s more, you can save the view settings using the Display Settings button!

image

I am currently writing a series on SSWUG for designing SSRS reports for viewing the events reported by the System Health Session. You can following the series here. If you have SQL Server 2012 RTM installed, then you need to download the T-SQL script attached to Mike Wachal’s blog post to address the wait type information captured.

Monitoring just got a whole lot easier!!

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.