By David Pless – dpless@microsoft.com
All,
I will be at the Space Coast User’s Group on February 14th covering SQL Server 2012 Performance Topics. Feel free to join us!
Reference:
Location:
Florida Institute of Technology Campus
150 West University Blvd.
Melbourne, FL 32901-6975
Contributors – David Pless - dpless@microsoft.com
Read-Only Routing with SQL Server 2012 Always On Database Availability Groups
In this blog I will go through step by step how to configure Read-Only routing for scale-out report workloads using SQL Server 2012 Always On Database Availability Groups.
In SQL Server 2012, AlwaysOn Availability Groups provides group level high availability for any number of databases on up to four secondaries known as ‘replicas’. The secondary replicas allow direct read-only querying or can enforce connections that specify ‘ReadOnly’ as their Application Intent. The secondaries primary use is to provide high availability or disaster recovery for the groups of databases being replicated. The secondaries can also be used to offset backup operations, DBCC checks, and to offload reporting workloads. The secondary copies of these databases known as secondary replicas have the ability to be read when they are in the secondary role. This is unlike Database Mirroring which requires a Database Snapshot in order to read a static view of the database at the Database Snapshot was created.
AlwaysOn brings in the strengths of Clustering, Log Shipping and Database Mirroring together where the method to transmit data is very similar to Database Mirroring with much more functionality and flexibility. AlwaysOn requires Windows Server 2008 clustering features, but does not require that SQL Server itself be clustered.
In AlwaysOn, Read-Only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available AlwaysOn readable secondary replica (http://msdn.microsoft.com/en-us/library/ff878253.aspx) that is, a replica that is configured to allow read-only workloads when running under the secondary role. To support read-only routing, the availability group must possess an availability group listener (http://msdn.microsoft.com/en-us/library/hh213417.aspx). Read-only clients must direct their connection requests to this listener, and the client’s connection strings must specify the application intent as “read-only.” That is, they must be read-intent connection requests.
This can be seen in the connection string, an example is shown below:
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=”";Initial Catalog=”";Data Source=AGListner;Initial File Name=”";Server SPN=”";ApplicationIntent=READONLY
Read-Only routing is a great new feature that can be leveraged to scale out reporting workloads such as SQL Server Reporting Services reports. All of your reports hosted in SharePoint or on a Native Mode installation of Report Server can specify read-only intent and can be serviced by your secondary replicas. This takes the heavy read workload that typically causes SQL Server blocking and consumes memory and CPU from your primary read/write database and frees up those resources.
Note: This document assumes that you are familiar with SQL Server 2012 Always On Availability Groups and how to setup AlwaysOn Database Availability Groups.
To facilitate Read-Only routing I setup three SQL Server 2012 instances and then setup a database availability group using two Adventure Works databases.
Always On Database Availability Group Diagram:
Diagram of Server Manager:
My Windows 2008 R2 server names are listed below (3 node cluster):
I have loaded a SQL 2012 named instance on each server with the following names:
Note: With AlwaysOn Availability Groups it is a good practice to make all of the named instances the same name for data synchronization.
Availability Group Listener (This is required to be setup for read-only routing):
Note: The Availability Group Listener is much like a virtual network name in traditional SQL clustering as it is a pointer to the SQL Instance that currently hosts the database availability group.
View from SQL Server Management Studio:
In this post we are using the new SQL Server Data Tools which replaces Business Intelligence Development Studio. SQL Server 2012 RTM has the correct client driver for .NET 4.0.2 that will expose the connection string property ApplicationIntent.
The syntax for a connection string depends on the client provider an application is using. The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing:
Server=tcp:SQL1,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
Read-Only routing requires that the availability replica be enabled for read access. This can be done at the time the Always On Availability Group is setup. You can also change and view this property by looking at the properties of the Availability Group:
Availability Group property dialogue:
Note: In the above screen shot you can see that the two replicas are marked to allow read connections.
To setup the Read-Only routing list you must be using an Availability Group Listener. In essence, you are essentially creating a SQL Server routing table for AlwaysOn that redirects connections to the secondary when the Application Intent is set to Read-Only. Below is the script that I used to setup the list for each replica based on whether it is in the Primary Role or the Secondary Role:
Note: For a deeper explanation and a VERY handy script that will generate the ROR URL for you please see Matt Neerincx’s blog post http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson.aspx
I have created a report in SQL Server Data Tools which is the new Business Intelligence Development Studio. Within SQL Server Data Tools I created a simple Report Project and a simple report to show how a reporting type of workload can be routed by SQL Server to a secondary read-only replica.
The above screenshot shows that I have two data sets. DataSet1 represents the result set that I am displaying in the report. DataSet2 is a simple “SELECT @@SERVERNAME” which returns the current instance name of the SQL Server for which the data source connection is currently connected to. I am using this to populate a text box with the SQL Server instance name so that I can show you which instance in my AlwaysOn environment executes the query for my report.
Now let’s take a look at how the SSRS report data source is configured for Read-Only Routing:
The above screen shot shows the properties of the shared data source in my report. Notice the use of the Availability Group Listener name SQL1 as the Data Source property and the ApplicationIntent=ReadOnly option. These are both required for SQL Server to use the Read-Only routing list I configured earlier.
Another view of the data source properties in SQL Server Data Tools:
Now that the data source is configured properly let’s run the report. Before I run the report I want to verify which instance is in the primary role.
Running the report:
As you can see in the above screenshot SQL Server received the connection and routed the report query to the secondary replica instance named SP-DENALI4\SQL1. This instance is the instance that we specified first in the Read-Only Routing List when the SP-DENALI1\SQL1 instance is in the Primary Role.
With the Read-Only Routing capabilities SQL Server is now more scalable than ever. Customers can utilize secondary replicas for scaling reporting type workloads, increasing the value of their SQL Server infrastructure while increasing performance by offloading heavy read workloads that commonly cause contention. Applications like SQL Server Reporting Services in SharePoint mode or Native mode can now easily take advantage of using secondary replicas for reporting.
Read-Only routing is yet another great feature of SQL Server 2012!
By Lisa Jackson-Neblett (PFE) – lisaneb@microsoft.com
There are many factors to consider when running SQL Server in a Hyper-V Virtual Machine, guest. Being proactive with the Virtual Machines memory settings can assist you
in avoiding several memory issues that could lead to performance degradation. Understanding how memory can impact SQL Server’s buffer pool is important. Dynamic changes to the size of the buffer
pool can negatively impact performance of SQL Server. In the event the buffer pool size changes due to external factors like memory being removed from the virtual machine it could
impact SQL Server’s performance. The performance may be decreased by the hypervisor requesting memory from SQL Server. Thus, SQL Server would perform
more physical disk reads vs. logical disk reads because the data is no longer in memory. SQL Server also uses memory for a plethora of other activities like storing cached plans. Therefore,
optimizing memory in SQL Server Virtual Machine is important to achieve optimal performance.
Hyper-V has two main memory management settings for virtual machines. The first is static memory. When static memory is used the virtual machine gets a fixed amount of memory.
If the virtual machine uses static memory, the virtual machine memory should remain constant.
The second memory setting is dynamic memory. Dynamic memory is used to allow the host to reclaim and redistribute memory to the virtual machines as needed. There are three main dynamic memory settings,
Startup RAM, Maximum RAM and Memory Buffer. Startup RAM is the amount the virtual machine is allocated on startup. If there is not enough memory allocated or available on the host server the virtual machine will fail to
start. I use the following formula for obtaining a starting point for configuring the Startup RAM memory.
Startup RAM = OS needs (typically 1-2GB but can vary) + min_server_memory (SQL Server Setting)
If the min_server_memory setting in SQL Server is set to its default of 0 you will want to have a minimum Startup RAM => OS needs (typically 1-2GB but can vary) + at least 1 GB which is the minimum requirement
for SQL Server. Theoretically you could run it with less but will most likely impact performance if not enough memory is allocated to SQL Server.
Maximum RAM is the maximum amount of RAM that will be available to the virtual machine. I use the following formula as a starting point for configuring the Maximum RAM memory.
Maximum RAM => OS needs (typically 1-2GB but can vary) + max_server_memory (SQL Server Setting) + + memory requirements for other applications running in the virtual machine + 5% (Memory Buffer allowance).
The last setting for dynamic memory is the Memory buffer. The memory buffer is used by Hyper-V to specify how much extra memory is needed for cache files to obtain peak performance. Since SQL Server already manages its own memory and it will use all it is allowed to you will want to make this setting as low as possible which is 5%.
Once you have determined how you are going to configure your memory settings it will be important to check performance counters on the host and virtual machine to see if the memory settings are good or if they need to be “tweaked” to obtain optimal performance. Other performance metrics should be measured from within SQL Server as well to make sure you have the optimal settings.
SQL Server has many ways to dig deep into diagnosing memory related problems. Today it is common to use Dynamic Management Objects (views/functions) to expose a large portion of this information. However, some useful data isn’t consumable by DMVs, so we must use DBCC MEMORYSTATUS to get what we need. The roadblock output from DBCC MEMORYSTATUS isn’t easily consumable and query-able. So, my goal of this blog post is to create a Powershell script to load the output from the DBCC command into a local database table so that you can query/report off of it.
Note: While you CAN dump the contents from DBCC MEMORYSTATUS into a temp table on the server using INSERT…EXEC, an important aspect of the data isn’t inserted into the temp table.
The script accepts 3 different parameters:
$FileLocation – if you have the DBCC MEMORYSTATUS output in a file, specify the location here. Otherwise the script will grab the output from DBCC MEMORYSTATUS from the instance you connect to.
$SQLServer – this is the SQL instance to connect and load the output from DBCC MEMORYSTATUS.
$DBName – this is the name of the database you want the DBCC MEMORYSTATUS output stored.
I’ve attached the file instead of pasting the output here as I couldn’t get the formatting how I wanted it to be. If I get it corrected I’ll make the change a later.
Once you’ve executed the script, you’ll have output very similar to the following, which will allow you to run any reports as needed.
I hope you enjoy, and I’d love to hear comments as to how to improve the script!
Note: Click below to get the script for Tim’s blog:
http://blogs.msdn.com/b/timchapman/archive/2012/08/16/how-to-parse-dbcc-memorystatus-via-powershell.aspx
Thanks,
Tim Chapman
The SQL Server 2012 Developer Training Kit is a great resource for developers, trainers, consultants and evangelists who need to understand the key improvements introduced in SQL Server 2012 from a developer perspective. It contains a rich set of presentations, demos, hands-on labs and videos that are perfect for self-paced learning or for conducting your own training event. The easiest way to get started with the training kit is to download it, install it, and browse the kit for the content that you are most interested in. Many of the presentations and demos in the training kit include a video that you can watch to familiarize yourself with the content. When you are ready for some hands-on experience, try installing one of the demos or hands-on labs. Each of them includes a dependency checker for installing software prerequisites. A web installer is also provided if you only want to download specific pieces of content or check for updated versions of existing content.
http://www.microsoft.com/en-us/download/details.aspx?id=27721
By David Pless – dpless@microsoft.com
On 07/16/2012, I will be covering SQL Server 2012 Availability Groups and Secondary Routing at the Space Coast .Net User Group. For anyone interested, please come join us.
Location: 3900 DOW ROAD. SUITE C. MELBOURNE FL, 32934
http://tinyurl.com/6q4junl
Start Time: 6:30 PM
For all local community events, see the following:
http://www.devfish.net/community/CommunityEvents.aspx
/********************************/
/***** EXTENDED EVENTS *****/
/*******************************/
USE MASTER
– GET A LIST OF EVENTS
SELECT * FROM sys.dm_xe_objects
WHERE object_type = ‘event’
ORDER BY name;
– EVENT I’M INTERESTED IN: WAIT_INFO
– WHICH PACKAGE DOES MY EVENT BELONG TO?
SELECT xo.name, xo.description, xp.name as ‘package’
FROM sys.dm_xe_objects xo
inner join sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = ‘event’
ORDER BY xo.name;
– PACKAGE THAT CONTAINS MY EVENT OF INTEREST: SQLOS
– WHAT COLUMNS CAN I LOOK AT FOR ‘WAIT_INFO’?
SELECT * FROM sys.dm_xe_object_columns
WHERE object_name = ‘wait_info’;
– USE WAIT_TYPE AND EVENT_OPCODE
– WHAT ARE THE DIFFERENT WAIT TYPES AND THE EVENT_OPCODE VALUES?
SELECT xmv.map_key, xmv.map_value
FROM sys.dm_xe_map_values xmv
INNER JOIN sys.dm_xe_packages xp
ON xmv.object_package_guid = xp.guid
WHERE xmv.name = ‘wait_types’
and xp.name = ‘sqlos’
ORDER BY map_key;
– 182 WRITELOG ( SQL 2012 )
— 178 WRITELOG ( SQL 2008 )
– LET’S CREATE OUR EVENT SESSION
— FIRST, IF IT EXISTS, DROP IT
if exists(
SELECT * FROM sys.server_event_sessions
WHERE name = ‘MonitorWriteLogWaits’
)
DROP EVENT SESSION MonitorWriteLogWaits on server;
– THEN, LET’S CREATE IT
— NOTE ENSURE THAT THE PATH IS VALID AND CORRECT
CREATE EVENT SESSION MonitorWriteLogWaits on server
add event sqlos.wait_info ( where wait_type = 182 )
add target package0.asynchronous_file_target (
set filename = ‘c:\mssql\monitorwritelogwaits.xel’, metadatafile = ‘c:\mssql\monitorwritelogwaits.xem’ )
with (
max_dispatch_latency = 1 seconds
);
– START THE SESSION
ALTER EVENT SESSION MonitorWriteLogWaits ON SERVER STATE = start;
– STOP THE SESSION
ALTER EVENT SESSION MonitorWriteLogWaits ON SERVER STATE = stop;
– DID WE HAVE ANY LOGGED EVENTS?
SELECT COUNT( * ) FROM sys.fn_xe_file_target_read_file (
‘c:\mssql\monitorwritelogwait*.xel’, ‘c:\mssql\monitorwritelogwait*.xem’, null, null
)
– EXTRACT OUR WAITS
SELECT data FROM (
SELECT CONVERT( xml, event_data ) as ‘data’
FROM sys.fn_xe_file_target_read_file (
‘c:\mssql\monitorwritelogwait*.xel’, ‘c:\mssql\monitorwritelogwait*.xem’, null, null
) ) entries;
– NOW EXTRACT IS A NICE, USABLE FORMAT
SELECT data.value (
‘( /event/@timestamp ) [ 1 ]‘, ‘datetime’ ) as ‘time’ ,
data.value (
‘( /event/data[ @name = ''wait_type'' ]/text )[ 1 ]‘, ‘varchar( 100 )’ ) as ‘wait_type’ ,
data.value (
‘( /event/data[ @name = ''opcode'' ]/text )[ 1 ]‘, ‘varchar( 100 )’ ) as ‘op’ ,
data.value (
‘( /event/data[ @name = ''duration'' ]/value )[ 1 ]‘, ‘bigint’ ) as ‘duration’ ,
data.value (
‘( /event/data[ @name = ''signal_duration'' ]/value )[ 1 ]‘, ‘bigint’ ) as ‘signal_duration’ ,
data.value (
‘( /event/data[ @name = ''completed_count'' ]/value )[ 1 ]‘, ‘bigint’ ) as ‘count’
FROM(
SELECT CONVERT( xml, event_data ) as ‘data’
FROM sys.fn_xe_file_target_read_file (
‘c:\mssql\monitorwritelogwait*.xel’, ‘c:\mssql\monitorwritelogwait*.xem’, null, null
) ) waitelements;
– LET’S DO SOME AGGREGATION FROM THE ABOVE
SELECT waits.wait_type AS ‘wait type’, COUNT( * ) as ‘wait count’ ,
SUM( waits.duration ) AS ‘total wait time in ms’ ,
SUM( waits.signal_duration ) AS ‘total signam wait time in ms’
FROM (
SELECT data.value (
‘( /event/@timestamp ) [ 1 ]‘, ‘datetime’ ) AS ‘time’ ,
data.value (
‘( /event/data[ @name = ''wait_type'' ]/text )[ 1 ]‘, ‘varchar( 100 )’ ) AS ‘wait_type’ ,
data.value (
‘( /event/data[ @name = ''opcode'' ]/text )[ 1 ]‘, ‘varchar( 100 )’ ) AS ‘op’ ,
data.value (
‘( /event/data[ @name = ''duration'' ]/value )[ 1 ]‘, ‘bigint’ ) AS ‘duration’ ,
data.value (
‘( /event/data[ @name = ''signal_duration'' ]/value )[ 1 ]‘, ‘bigint’ ) AS ‘signal_duration’ ,
data.value (
‘( /event/data[ @name = ''completed_count'' ]/value )[ 1 ]‘, ‘bigint’ ) AS ‘count’
FROM (
SELECT CONVERT( xml, event_data ) AS ‘data’
FROM sys.fn_xe_file_target_read_file (
‘c:\mssql\monitorwritelogwait*.xel’, ‘c:\mssql\monitorwritelogwait*.xem’, null, null
) ) waitelements
) AS waits
WHERE waits.op = ‘end’
GROUP BY waits.wait_type;
– CLEANUP – DROP EVENT SESSION
DROP EVENT SESSION MonitorWriteLogWaits ON SERVER;
– Article reference: http://blogs.technet.com/b/andyc_sql/archive/2012/03/08/having-some-fun-with-extended-events.aspx
By Joseph Pilov
SQL Server Performance Dashboard Reports for SQL Server 2012
You can download the reports here http://www.microsoft.com/download/en/details.aspx?id=29063
Description: The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.
Common performance problems that the dashboard reports may help to resolve
include:
The information captured in the reports is retrieved from SQL Server’s dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.
Reporting Services is not required to be installed to use the Performance Dashboard Reports.
Tampa Bay SQL Server User’s Group meeting – March 20th @ 6:00pm (6:30pm official start)
Meeting Location:
Juvenile Welfare Board
14155 58th Street North
Clearwater, FL 33760
For more information on the Tampa Bay SQL Server User’s Group, contact: Pam Shaw pshaw1129@live.com
Description:
David Pless will present SQL Server 2012: Implementing and Supporting AlwaysOn
SQL Server AlwaysOn is the new comprehensive high availability and disaster recovery solution in SQL Server 2012. Using AlwaysOn, you can increase application availability and get a better return on your hardware investments through a simplified high availability deployment and management experience.
We will talk about SQL Server 2012′s new feature SQL Server AlwaysOn and what it will mean for the DBA. We will cover how to set up AlwaysOn and the role of clustering in the new high availability / disaster recovery solution.
We will discuss Availability Groups, synchronization modes, the AlwaysOn dashboard, what to monitor with AlwaysOn, and other important support topics.
David Pless a Premier Field Engineer for Microsoft based out of Orlando, Florida will return to discuss some new availability features in SQL Server
2012.
SQL Server 2012 – RTM
REDMOND, Wash. — March 6, 2012 — Microsoft Corp. today announced that the latest version of the world’s most widely deployed data platform, Microsoft SQL Server 2012, has released to manufacturing. SQL Server 2012 helps address the challenges of increasing data volumes by rapidly turning data into actionable business insights. Expanding on Microsoft’s commitment to help customers manage any data, regardless of size, both on-premises and in the cloud, the company today also disclosed additional details regarding its plans to release an Apache Hadoop-based service for Windows Azure.
“Data is being generated faster than ever before, and organizations need a way to process and analyze all that data,” said Ted Kummert, corporate vice president at Microsoft. “Whatever the type or size of data, SQL Server 2012 delivers the platform and familiar tools to manage data, generate actionable insights and help drive business impact.”
Full Article: http://www.microsoft.com/Presspass/press/2012/mar12/03-06SQLServer12PR.mspx
Top SQL Server Solutions from Microsoft Support Most Recent KBs for SQL Server 2008 R2