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

Advertisements

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.

Can I rely on SQL Server Identity values being in order?

It is best to not expect the identities to be consecutive because there are many scenarios that can leave gaps. It is better to consider the identity like an abstract number and to not attach any business meaning to it.

Basically, gaps can happen if you roll back INSERT operations (or explicitly delete rows), and duplicates can occur if you set the table property IDENTITY_INSERT to ON.

Gaps can occur when:
1. Records are deleted.
2. An error has occurred when attempting to insert a new record (rolled back)
3. An update/insert with explicit value (identity_insert option).
4. Incremental value is more than 1.

The identity property on a column has never guaranteed:
• Uniqueness
• Consecutive values within a transaction. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE* isolation level.
• Consecutive values after server restart.
• Reuse of values.

If you cannot use identity values because of this, create a separate table holding a current value and manage access to the table and number assignment with your application. This does have the potential of impacting performance.

https://msdn.microsoft.com/en-us/library/ms186775(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/ms186775(v=sql.110).aspx

*A word of caution, using SERIALIZABLE will severely limit concurrency.  I would definitely test thoroughly first before implementing in production.

SERIALIZABLE

Specifies the following:

  •  Statements cannot read data that has been modified but not yet committed by other transactions.
  •  No other transactions can modify data that has been read by the current transaction until the current transaction completes.
  •  Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

The following example sets the TRANSACTION ISOLATION LEVEL for the session. For each Transact-SQL statement that follows, SQL Server holds all of the shared locks until the end of the transaction.

USE AdventureWorks2012;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT *     FROM HumanResources.EmployeePayHistory;
GO
SELECT *     FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
GO

https://msdn.microsoft.com/en-us/library/ms173763.aspx

 

Missing SQL Server Performance Counters

8317

Server Error: 8317, Severity: 16, State: 1.
Server Cannot query value 'First Counter' associated with registry key 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance'. SQL Server performance counters are disabled.Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033) Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled.

We use the “First Counter” value in the registry to identify where the counters for this SQL instance start. The “First Counter” value should map to “MSSQL$Instance:Buffer Manager” as this is the first Perfmon counter in the ini file
If it doesn’t, we throw the error mentioned above in SQL error logs.

Does your registry key look like mine?

registry

If your registry key is missing ‘First Counter’, ‘First Help’, ‘Last Counter’, ‘Last Help’ then perform the following:

Re-add extensible counters
1). Open a Command Prompt window.
2). Run:  cd %Systemroot%\System32
3). Run:  findstr drivername *.ini
4). Note the name of the .ini file for each drivername in the list.
5). At the command prompt, type the following command, and then press ENTER:
lodctr axperf.ini
— use *.ini in the each drivername list obtained in step 4
6). Repeat step 5 for each .ini file in the list.

7). Rebuild all Performance counters including extensible and third-party counters:
cd\windows\system32
lodctr /R

8). Re-load SQL performance counters then restart SQL service in a DOS command window:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn> unlodctr MSSQL$MSSQLSERVER
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>lodctr perf-MSSQLSERVERsqlctr.ini
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>net stop MSSQL$MSSQLSERVER
C:\Program Files\M
icrosoft SQL Server\MSSQL.1\MSSQL\Binn>net start MSSQL$MSSQLSERVER

9). Check the SQL errorlog and confirm that there are no more errors

10). Check the registry key and confirm that ‘First Counter’, ‘First Help’, ‘Last Counter’, ‘Last Help’ key values are correct now
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$MSSQLSERVER\Performance

11). Check perfmon and confirm SQL Performance Counters are available

perfmon

How SQL Server has made being audited a little bit easier to bear…

Headbanging bear

The following server permissions were added in SQL Server 2014:

CONNECT ANY DATABASE Permission
Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. Does not grant any permission in any database beyond connect. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.

SELECT ALL USER SECURABLES Permission
When granted, a login such as an auditor can view data in all databases that the user can connect to. When denied, prevents access to objects unless they are in the sys schema.