I want to become a SQL Server DBA. Do you have any books you recommend?

This is the #1 best book to learn about SQL. It isn’t “this is how to backup a database” or “how to administer sql server”, it is “this is how sql server works”
http://www.amazon.com/Microsoft-SQL-Server-2012-Internals/dp/0735658560/ref=sr_1_1?s=books&ie=UTF8&qid=1385590913&sr=1-1&keywords=sql+internals

This link is about SQL Server performance:
SQL Performance Whitepaper

Paul Randal used to work at Microsoft and, in fact, wrote a lot of the SQL Server code:
SQLSkills.com

This is a great Q&A website:
dba.stackexchange.com

This is a presentation I did at SQL Saturday Oklahoma City
Troubleshooting SQL Server Performance

Join SQL PASS. There are virtual chapters that meet every month and knowledgeable folks give presentations on various SQL topics::
Professional Association SQL Server

Extended Events Reader

Mike Wachal – MSFT

Check out What’s new for Extended Events in SQL Server Codenamed “Denali” CTP3 for an overview of all of the new functionality released in CTP3. This post covers the details of the “reader” API, which we call the XeReader, that you can use to programmatically access both XEL files and the near-live event stream from a running session. You can find information about the Extended Events object model in my earlier post: Introducing the Extended Events Object Model.

Event stream defined

An event stream is pretty much what it sounds like, a stream of events that comes out of a running session. You will not find an option to configure the event stream in the UI or Extended Events DDL, it is accessed using the API described in this post. All the session configuration is handled under the covers. There are a few things to understand about the event stream before I dive into some examples:

  • The event stream is asynchronous, not live, but to make the experience seem “more live” the event stream modifies the latency behavior of the source event session. When the event stream is hooked to an event session, the latency period specified by MAX_DISPATCH_LATENCY is changed to 3 seconds. When the event stream is disconnected, the latency is changed back to it’s original value. You may notice some odd behavior if you connect more than one XeReader to the same event session; the latency modification assumes only one connection so the first disconnect from the event session will revert the latency back to the original value (eg. no ref-counting).
  • The event stream aggressively follows the prime directive – if the application utilizing the event stream API gets far enough behind in processing events that it risks blocking the server, the application will be disconnected. This is to prevent a “client tracing” application from bringing down a server; a problem often documented with SQL Profiler. If the event rate from your event session is so high that it causes a disconnection, then the event file is a more appropriate target for you.

Exploring the API

From the point of view of the XeReader, there is no difference between an XEL file and an event stream aside from the mechanism used to obtain the data. The main object in the XeReader is the QueriableXEventData object, which returns an enumerable list of events.

Using the XeReader API

In order to code against the API you’ll need to add a reference to the following assembly:

Microsoft.SqlServer.XEvent.Linq.dll

You’ll also need to add the following namespaces to your classes.

using Microsoft.SqlServer.XEvent.Linq;

Note: By the time we release “Denali” you will also need to add a second namespace, Microsoft.SqlServer.Xevent, to your code. Just letting you know so it’s not a surprise.

Reading an event file (XEL)

The XeReader supports reading the legacy XEL/XEM from SQL Server 2008 & SQL Server 2008 R2 as well as the XEL file produced in SQL Server “Denali” CTP2 and above. There is no support for reading the files created in SQL Server “Denali” CTP1. There are three versions of the constructor that accept files:

  • A single string with a valid path.
  • A single string array containing a set of paths to XEL files.
  • Two string arrays containing sets of paths to XEL and XEM files respectively.

The paths can be fully qualified files or any valid representation of a path using wildcards. Here are a couple examples:

Read all XEL files from a specific location

QueryableXEventData events = new QueryableXEventData(@"C:\Temp\myFile*.xel");

Read a specified multiple of XEL files

string[] fileList = new string[2];
fileList[0] = @"C:\Temp\demo_trace_0_129418467298110000.xel";
fileList[1] = @"C:\Temp\demo_trace_0_129391767647570000.xel";

QueryableXEventData events = new QueryableXEventData(fileList);

Provide both XEL and XEM files

string[] xelFiles = new string[1];
xelFiles[0] = @"C:\Temp\demo_trace_0_129418467298110000.xel";
string[] xemFiles = new string[1];
xemFiles[1] = @"C:\Temp\demo_trace_0_129418467298110000.xem";

QueryableXEventData events = new QueryableXEventData(xelFiles, xemFiles);

You get the idea.

Reading an event stream

You can read a stream from a running session by providing a connection string and event session name to the constructor along with a couple extra options.

QueryableXEventData stream = new QueryableXEventData(
    @"Data Source = (local); Initial Catalog = master; Integrated Security = SSPI", 
    "alert_me", 
    EventStreamSourceOptions.EventStream, 
    EventStreamCacheOptions.DoNotCache);
  • EventStreamSourceOptions – EventStream is the only option that exists at this point. We’ll be adding one more option before we release but I’ll leave that for a later blog post when it’s relevant.
  • EventStreamCacheOptions – You can either choose to not cache any data (DoNotCache) or to cache the data (CacheToDisk). This option determines whether you can enumerate across the history of events that have come from the stream. The DoNotCache option will pull events from the server as fast as possible and keep them in memory as long as possible. It is possible for events to be ejected from memory before they are processed by the enumerator. The CacheToDisk option will store events to the disk until the disk is full.
Working with the Events

The constructors all return an enumerable collection of type PublishedEvent, so the code to work with the events is identical regardless of source. I’ll cover the basics here to get you started.

You can loop through the collection of events just like you would expect for an enumerable object so you can imagine using a foreach to process each event. Additionally, each PublishedEvent object, contains collections for Fields and Actions that return the PublishedEventField and PublishedAction objects respectively. Taken together it’s a straight forward operation to list out all the events along with their fields and actions using code similar to this:

foreach (PublishedEvent evt in events)
{
      Console.WriteLine(evt.Name);

      foreach (PublishedEventField fld in evt.Fields)
        {
            Console.WriteLine("\tField: {0} = {1}", fld.Name, fld.Value);
        }

      foreach (PublishedAction act in evt.Actions)
        {
            Console.WriteLine("\tAction: {0} = {1}", act.Name, act.Value);
        } 
}

You can perform conditional expressions as you would expect, say if you wanted to perform an action based on a specific value being returned from a field:

if (evt.Fields["field_name"].Value == "foo")

or

if (fld.Value == "foo")

It is worth pointing out some “weirdness” associated with map fields if you want to evaluate using the friendly text that you would find in dm_xe_map_values rather than the integer map_key. You have to explicitly cast the field to a MapValue:

if (((MapValue)evt.Fields["field_name"].Value).Value == "foo" )

Again, you get the idea. This is really the core of the XeReader and the part I expect you’ll be working with the most. To make your life a little easier, I’ve attached a files that contains all this code in a runnable format, just add a reference to the assembly and you should be ready to start experimenting.

Download the Extended Event Reader sample code

Happy Eventing

– Mike


SQL Saturday – Why I Speak

SQL Saturday

“Baton Rouge!? Do you know how long of a drive that is?!” That was my husband’s reaction when I told him I was selected to speak. “You’re gonna have to take Friday off and we’ll have to get a hotel room. Will you be able to expense it?” No, no I won’t, but I knew that when I submitted my abstract. “Tell me again why we’re doing this?” That’s a very good question…

Sure, it is an excellent way to get public speaking experience, but I still shake like a chihuahua beforehand, palms sweating, dry mouth, the whole nine yards. No, I don’t particularly enjoy public speaking.

It is also an excellent way to meet other like-minded people and build your network, but I’m an introvert so I’m going to boogie home immediately afterwards. No, I don’t do it to expand my social network.

Is it the swag? Well…I do get a t-shirt (or a jacket or hat) that says “SPEAKER” on it somewhere, but no, it isn’t the swag. I will tell you why I happily give up a vacation day in order to drive six and a half hours to talk about SQL Server to a bunch of strangers. It is hard to articulate, but I’m gonna try.

I was that accidental DBA scouring the internet, asking anybody technical for advice, mostly getting it right, but sometimes really messing things up, and I wished I had someone more senior with lots of experience to guide me, but I didn’t. It is a tremendous responsibility to have the care and feeding of a production environment on your hands. I present at SQL Saturday to see the light switch of understanding flip to the ‘on’ position in people’s eyes. My paycheck is people coming up to me afterwards and shaking my hand. There’s no better reward than someone telling me “I never understood until you explained it the way you did. It totally makes sense to me now.” If I can explain this really complicated thing so that someone who didn’t understand it before, now understands it, I am victorious and I am on cloud nine the whole ride home. That’s why I speak.

Async Auto Create Statistics Resolves Timeout

The following is based on an actual customer support case:

Customer facing web application executes a specific stored procedure that is set to timeout after 5 seconds. Most of the time the duration of this stored procedure is sub-second, but when the server load is high, this stored procedure will timeout roughly 5% of the time.

Top wait = latch non-buffer

Analyzed extended events capture of query executions filtering on sessions where stored procedure duration > 5 sec and found 4.9 seconds of the duration was spent updating qpstats on a > 100 million row table.

Enabled auto update stats async to resolve the issue:

ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC ON

Statistics updates are synchronous by default which means queries always compile and execute with the latest statistics.  When the threshold has been reached and statistics are stale, the query optimizer will wait for the statistics to be updated before compiling the query plan.  If there are many rows in the table, the wait can be significant. When statistics updates are asynchronous, the query optimizer doesn’t wait but goes ahead and uses the existing query plan (or compiles a new one) using the existing statistics. Now, it is possible that the query optimizer could choose a suboptimal query plan if statistics are out-of-date when the query compiles. Queries that compile after the asynchronous updates have completed will benefit from using the updated statistics.

You should stick with the default statistics updates when you perform operations that change the distribution of data, such as truncating a table or performing a bulk update of a large percentage of the rows. If you do not update the statistics after completing the operation, using synchronous statistics will ensure statistics are up-to-date before executing queries on the changed data.

You should use asynchronous update statistics to achieve more predictable query response times for the following scenarios:

  • Your application frequently executes the same query, similar queries, or similar cached query plans. Your query response times might be more predictable with asynchronous statistics updates than with synchronous statistics updates because the query optimizer can execute incoming queries without waiting for up-to-date statistics. This avoids delaying some queries and not others.
  • Your application has experienced client request time outs caused by one or more queries waiting for updated statistics. In some cases, waiting for synchronous statistics could cause applications with aggressive time outs to fail.

 

Manual Setup of an AlwaysOn Availability Group on SQL Azure Virtual Machines

Provision two SQL Server VMs.   Be sure to put the VMs on the existing domain’s VNET.  If you don’t have a domain, provision two domain controllers and create one.  Create a domain account to use that has enterprise and domain admin rights.

REMEMBER – the Azure uptime SLA is only applicable if the VMs are in an availability set.  This will prevent updates from occurring on both replicas at the same time and ensure they are run on separate hardware to prevent an outage in case of hardware failure. Also, it is a requirement for them to be in an Availability Set to add them to the Backend Pool of a Load Balancer which is required for an Availability Group Listener.

Join each of the VMs to the domain. This requires a restart – use your domain account to log in after they start back up:
clip_image002

clip_image004

Add the failover clustering feature to each VM:

Create the cluster with only one of the nodes.  UNCHECK THE BOX to add all available storage to the cluster.  Run all the validation tests:

Create a file share on another machine in the network.  The domain controller is a good choice:

Configure the file share witness:

Change the cluster IP address to a free, static ip within the subnet:

Add the other node to the cluster,  UNCHECK THE BOX to add all available storage to the cluster and run all the validation tests:

clip_image034

From SQL Configuration Manager, enable AlwaysOn Availability Groups on each node:

clip_image036

Change the SQL Server service account to your domain account.

Connect to SQL Server using the local account and add your domain account as a login and add it to the Sysadmin role.

Create a share for the database backups on the primary node.

Create a rule in Windows Firewall to open port 5022 on both nodes:

 

Use the wizard to create your availability group but don’t create the listener at this time.

Create an internal load balancer from the azure portal that is in the same resource group as your VMs.

  1. Static frontend ip
  2. Create health probe:  TCP Port: 59999
  3. Create load balancing rule: TCP Port: 1433 Backend port: 1433 Health probe: the one created in step 2.  Floating IP: enable.  Leave everything else at the default.
  4. Create backend pools.  Associate with the availability set of the VMs and the load balancing rule created in step 3.

Create the AlwaysOn Listener using the same ip address as the internal load balancer.

Test connectivity to the listener.  Failover and test again.

Congratulations, you now have an AOAG in Azure.

 

R Service R Server

I just had my first case on this and thought I would share what I learned:

What is ‘R’?  R is a programming language used for predictive models.  Think data mining code/data analysis/big data.

What is R Services and R Server? R Services and R Server install the same bits but R Services has a couple more items for SQL integration.  R Server is for a Data Scientist to use on their client machine. It is a stand-alone install (No SQL required).  R Services is a server side install. This allows you to use R scripts INSIDE sql via system stored procedure.

Do we need to install both?  No.

Is there a problem with installing both R Services and R Server Standalone during the same install run?
We have tried it ourselves and is seems to work, but one customer is getting ACCESS_DENIED on the following action:

RSETUP.LOG:

2016-06-16T23:35:34       INFO      Command invoked: C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016\x64\RSetup.exe /install /component SRS /version 8.0.3 /language 1033 /destdir C:\Program Files\Microsoft SQL Server\MSSQL13.INVDEV07_R\R_SERVICES /logfile C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20160616_192430\RSetup.log /instance INVDEV07_R
2016-06-16T23:35:34       INFO      Searching for cache dir: C:\Program Files (x86)\Microsoft SQL Server\130\Setup Bootstrap\\R_SERV_CACHE
2016-06-16T23:35:34       INFO      Using default cache dir: C:\Users\dsa1023\AppData\Local\Temp\1\
2016-06-16T23:35:34       INFO      Backing up source dir: C:\Program Files\Microsoft SQL Server\MSSQL13.INVDEV07_R\R_SERVICES\ => C:\Program Files\Microsoft SQL Server\MSSQL13.INVDEV07_R\R_SERVICES_84eb607f-4393-41da-ae19-b796ed29471c
2016-06-16T23:35:34       ERROR  Error renaming source dir: Access to the path ‘C:\Program Files\Microsoft SQL Server\MSSQL13.INVDEV07_R\R_SERVICES\’ is denied.
2016-06-16T23:35:34       INFO      Exiting with code 5</e

We found that when both are checked, one or the other install will fail. The first time we ran with both selected, R Services (in-database) installed, and we got access denied on the R Server install. The second time we ran, R Server installed without error, but R Services failed with Access Denied.  There really isn’t a reason to install both.

Are there any known issues?

When trying to start Launchpad we get the following:

SYSTEM EVENT LOG

The SQL Server Launchpad (MSSQLSERVER) service failed to start due to the following error:  The service did not respond to the start or control request in a timely fashion.

A timeout was reached (120000 milliseconds) while waiting for the SQL Server Launchpad (MSSQLSERVER) service to connect.

 

EXTLAUNCHERRORLOG file (from SQL Server Log directory)

2016-06-15 09:24:54.573 User Config data file: D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\SQLRConfigData
2016-06-15 09:24:54.573 User Config key file: D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\SQLRConfig
2016-06-15 09:24:56.194 LogonUser Failed for user MSSQLSERVER01. ErrorCode: 0x80070569.
2016-06-15 09:24:56.194 InitializePhysicalUsersPool Failed. ErrorCode: 0x80070569.
2016-06-15 09:24:56.194 Security Context Manager initialization failed. ErrorCode: 0x80070569.
2016-06-15 09:24:56.194 Satellite Session Manager initialization failed. ErrorCode: 0x80070569.
2016-06-15 09:24:56.194 Initialization of satellite session manager failed. ErrorCode: 0x80070569.

0x569 error code is

ERROR_LOGON_TYPE_NOT_GRANTED
Logon failure: the user has not been granted the requested logon type at this computer.

We have had a couple of customers run into the exact same error because they had denied local user accounts logon locally rights on their servers.  They made an exception for SQLRUsergroup “Allow logon locally” rights for the group.

There are two main considerations:

  1. Logon locally rights – Launchpad is running under low privileged service account and since we use local user accounts for isolation & execution of R processes, we logon as a user. The main privilege required is the ability to logon locally rights for the Windows user accounts we create during setup. This is granted by default but some companies have restrictive policies.
  2. Permissions – There is no specific permission per se. It depends on what the R script is doing. We create working directory with appropriate permission for each user so this automatically handled by the system. And outbound network operations from R will fail by default due to the firewall rules (and if Windows Firewall is enabled). However, if the R script is trying to access say a file on the SQL Server machine then this operation may or may not succeed depending on the permission set on that file. For these scenarios, the recommendation would be to give access to SQLRUsergroup for the resource – this is similar to say SQL Server service trying to backup database to a directory where the service cannot write.