NUL or NULL?

Ever since the old MS-DOS days, the Windows OS family has supported a special ‘null device’ – NUL. Notice the single ‘L’. This is a ‘reserved’ device identifier which you can use as a path. The special thing about this null device (NUL) is that it essentially swallows whatever is sent to it.

NUL and SQL

So what does this have to do with let’s say SQL? Many of us use the null device to test raw backup throughput. The correct way to do this is to specify DISK = ‘NUL’ in the backup command. In that case, you will see an entry similar to the below in the error log, and no physical destination file will be created:

2014-09-20 21:47:43.890 Backup       Database backed up. Database: master, creation date(time): 2014/09/18(16:33:26), pages dumped: 459, first LSN: 234:304:73, last LSN: 234:352:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘nul’}). This is an informational message only. No user action is required.

Now, in some cases, you might accidentally or unknowingly type in NULL (with the double L) instead of NUL. What that does is actually end up with a physical file called NULL Smile You can see proof of that here:

2014-09-20 21:47:03.480 Backup       Database backed up. Database: master, creation date(time): 2014/09/18(16:33:26), pages dumped: 460, first LSN: 234:208:128, last LSN: 234:280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQL14\MSSQL\Backup\null’}). This is an informational message only. No user action is required.

Conclusion

So if you are using the NUL device in your tests, make sure you spell it as NUL (without the double L!)

Have a good weekend!

Data Quality Services (DQS) and Failover Cluster instances

One of my customers made the observation that it is not possible to uncheck the Data Quality Services component in the Feature Selection screen in SQL Server 2012 setup. The interesting thing was that she clearly recalled that earlier it was possible to select it (the default was unchecked), and we wondered what changed that it now mandated the selection of the DQS installation:

image

FYI, she was now running SQL Server 2012 with the Product Update enabled using the command line switches /Action=InstallFailoverCluster /UpdateSource=<path to SP1 EXE>. This means the setup support files are those from SP1.

Analysis

It then came to our attention that in CU1 there was a fix released to ensure DQS compatibility with failover cluster. The issue is documented in KB article 2674817.

Now as per the KB, there is no way to apply the fix (other than a re-install of the FCI Sad smile) to an already-installed FCI which had the DQS components previously selected (but which were otherwise not working due to the issue described in the KB.) So to prevent users from running into the same problem if they were ever to use DQS after the FCI installation, the product team decided that post CU1 the selection of DQS is mandatory.

The other angle on this is, what if you are never planning to use DQS, is this selection going to add services, databases or files which are unused? The good news is that the impact is not much; the selection of DQS in the above feature list just drops a DQSInstaller.exe into the SQL BINN folder, which then has to be (optionally) executed to actually complete the DQS server installation. So, if you never run the DQSInstaller.exe, it will not create the DQS databases on the FCI.

Conclusion

To summarize, though the feature cannot be unchecked, in reality it does not add too much to the footprint of the installed FCI. Hope this helps!

SQL 2014 In-Memory OLTP ‘Hekaton’: training videos and white papers

Personally, the feature I am most excited about in SQL Server 2014 is the In-Memory OLTP (codenamed ‘Hekaton’) feature. Since SQL Server CTP1 was released, a lot of my interactions with colleagues and customers have revolved around this new feature and the one question everyone has is, how do I get started? Well, here’s a readiness roadmap I have put together to help you!

Videos

Sunil Agarwal, Principal Program Manager from the SQL Server product team, has been one of the key people steering the work towards its eventual public unveiling in CTP1. I’ve been fortunate enough to interact with Sunil on a few occasions and I marvel at his passion and energy. He recently presented three sessions at TechEd 2013 North America, which are now available online, complete with slides and video!

A related set of deep sessions were delivered by Jos de Bruijn (also a key member of the Hekaton PM team) at TechEd Europe 2013. Jos is a PhD and his technical depth and passion to share information is clearly evident – take a look for yourself!

 

In-Memory OLTP Code Samples

Here are some useful samples from MSDN.

 

General Reading

 

Research Papers

All the inside scoops from the team which made it happen!

If you find more resources which you would like to share, please post a comment or contact me through this blog to let me know, I will gladly link them up in this roadmap!

The Top 10 Issues uncovered by the SQL Server Risk Assessment Program (SQLRAP)

Firstly – Happy New Year, everyone! In my job as a Principal Premier Field Engineer at Microsoft Services, I am a regional lead for a proactive risk identification program called the SQLRAP. For over 7 years now we have evolved this from a manually conducted ‘Health Check’ offering to a highly automated and comprehensive Risk Assessment platform. I have the honor of sharing the top 10 most common patterns (a.k.a. Issues) we uncover as part of the program – you can read more about this at our MSPFE team blog.

Extra T-SQL checks to complement SQL Upgrade Advisor

Background

More and more customers are looking to upgrade to SQL Server 2012, which offers some exciting features like AlwaysOn Availability Groups, ColumnStore Indexes and much more. As part of my job in the Microsoft Premier Field Engineering team, we are called upon by customers to assess their readiness to upgrade to the new version.

Normally, when we perform an upgrade readiness assessment, we also review the T-SQL code that the customer is running. This is done to uncover breaking changes and other patterns which might cause poor performance or such behavioral change post the upgrade. For breaking changes, we normally recommend customers to use the publicly available SQL Server Upgrade Advisor (SSUA) tool. However, not all breaking changes can be detected by the current SSUA toolset.

Stuff outside of SSUA scope

Here are some examples of T-SQL breaking changes which cannot be uncovered by SSUA. Some of these are boundary scenarios, but I feel it is important to list them so that you are aware of the issues. Also, some of these are known to the SSUA team but they would not incorporate checks for them in SSUA due to their ship timeline and priority ratings.

Issue Conflicting locking hints
Description A specific issue in SQL Server 2000 caused locking hints in a FROM clause for an UPDATE statement, to be ignored. While this bug was fixed in SQL 2005, it potentially gave rise to upgrade issues if the locking hint in the FROM clause was different from the locking hint for the UPDATE source table.
Example See this Connect bug for an example.
Recommendation If at all used, locking hints need to be consistent between the table source and the FROM clause of the UPDATE.

 

Issue Objects in the FROM clause must have distinct exposed names
Description In SQL 2000, identically named tables from different databases did not require an alias
Example This code will work in compatibility mode 80 while it will break on compatibility 90 and above:

select *
from DB1.dbo.sometab
join DB2.dbo.sometab
on DB1.dbo.sometab.name = DB2.dbo.name

Recommendation It is generally a good practice in any case to alias all tables in a JOIN. That is the recommended solution in this case as well.
Additional Reading You can read more about this at Nacho’s blog.

 

Issue Subqueries are not supported in GROUP BY clause
Description The GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. In earlier versions of SQL Server, this was allowed. In SQL Server 2008, error 144 is returned.
Recommendation Be aware of this breaking change and revise the query logic accordingly.
Additional Reading There are some Connect articles which describe this. Read them here, here and here.

 

Issue SETUSER statement usage
Description SETUSER may not be supported in a future release of SQL Server.
Recommendation We recommend that you use EXECUTE AS instead.

 

Issue ORDER BY clauses in views
Description The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
Recommendation Specify the ORDER BY clause only in the outermost query and not inside views.

 

Issue FASTFIRSTROW table hint usage
Description The usage of FASTFIRSTROW as a table hint has been disallowed in SQL 2012. We recommend that hints be used only as a last resort by experienced developers and database administrators.
Recommendation You can evaluate the query hint OPTION (FAST 1) instead.

 

Issue COMPUTE clause is not allowed in database compatibility 110
Description The COMPUTE clause generates totals that appear as additional summary columns at the end of the result set. However, this clause is no longer supported in SQL Server 2012.
Recommendation Use the ROLLUP operator instead.
Additional Info A Connect request has been logged to include this check in SSUA. Vote for it!

 

Conclusion

So, now you know the most common patterns which you need to check for in addition to what SSUA already uncovers. However let’s say you have a huge T-SQL code base and cannot do this manually. Our PFE team are working on some offerings: SQL Server Upgrade Assessment and also a T-SQL Patterns and Practices Review, which among other things will also cover the above deprecation / upgrade checks. If you need more information, you can contact me for more details.

Also if you have any comments, additional cases which are not detected by SSUA, please leave a feedback item in the Comments section below. I read each one of them!

Where can I download SQL 2012 Upgrade Advisor?

This post is a really quick one… Let’s say you are planning to check your SQL Server and T-SQL code for any potential breaking changes in SQL 2012. So you use Upgrade Advisor. Let’s say you want to download it, rather than get it from the installation media (the MSI can be found under ServersredistUpgrade Advisor path.)

If you monkey around with your favorite search engine (mine is Bing!) you may not find a download link which directly says ‘SQL 2012 Upgrade Advisor’. That is because the link to download SQL UA is actually under the SQL 2012 Feature Pack. Once you get to that page, look for SQLUA.MSI. Select the one as per the architecture of the machine where you are going to execute the tool (x86 or x64.) 

You may also need to install the Windows Installer 4.5 and the .NET Framework 4.0 if they are not already installed. Then install the SQLUA.MSI file… and you are done!

AlwaysOn Availability Groups Connectivity Cheat Sheet

Today we were discussing the AlwaysOn connectivity options, and the various combinations therein of Readable Secondary setting and the connection strings became very complex. So I have summarized all the possible outcomes in the pivot tables below.

For convenience, we have two tables. The first table covers the cases when the Primary Replica is set to allow all types of connections (i.e. those with and without the ApplicationIntent=ReadOnly attribute.)

Case 1: Primary Replica allows ALL connections

Readable Secondary Setting

 

Additional Connection Parameters

Initial Database

No

Yes

Read-intent

 

Through Listener

None

Non-AG

Primary

Primary

Primary

ReadOnly

Primary

Primary

Primary

None

AG

Primary

Primary

Primary

ReadOnly

Not allowed (5)

Secondary

Secondary

Direct to Primary

None

Not applicable / don’t care

Primary

Primary

Primary

ReadOnly

Primary

Primary

Primary

Direct to Secondary

None

Not allowed (4)

Secondary

Not allowed (1)

ReadOnly

Not allowed (4)

Secondary

Secondary

The second table covers the cases when the Primary Replica is set to allow Read/Write (i.e. those without the ReadOnly attribute.)

Case 2: Primary Replica allows only Read/Write connections

Readable Secondary Setting

 

Additional Connection Parameters

Initial Database

No

Yes

Read-intent

 

 

Through Listener

None

Non-AG

Primary

Primary

Primary

ReadOnly

Not allowed (2)

Not allowed (2)

Not allowed (2)

None

AG

Primary

Primary

Primary

ReadOnly

Not Allowed (3)

Secondary

Secondary

Direct to Primary

None

Not applicable / don’t care

Primary

Primary

Primary

ReadOnly

Not allowed (2)

Not allowed (2)

Not allowed (2)

Direct to Secondary

None

Not allowed (4)

Secondary

Not allowed (1)

ReadOnly

Not allowed (4)

Secondary

Secondary

For some cases, the access to the database is not allowed (either at connection time or at run time, when the database is tried to be accessed) with the following error messages:

1. Error message: The target database (‘salesdb’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

2. Error message: The target database (‘SalesDb’) is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online.

3. Error message: Unable to access the ‘SalesDB’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

4. Error message: The target database, ‘salesdb’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online

5. Error message: Unable to access the ‘SalesDB’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

This table should help you predict how the connectivity will behave in each of these combinations, and therefore setup your Availability Group replica properties correctly.

More information:

I hope you enjoyed this post as well. If you would like to see more, please leave a comment!

AlwaysOn Availability Groups Listener: connectivity details

Today in some discussions with my colleagues we were looking at the AlwaysOn Listener which allows SQL Server client applications to be abstracted from the details of the AlwaysOn Availability Group implementation. On the surface, The Listener essentially is a cluster resource pair – a Virtual IP and a Virtual Network Name. But on deeper examination, there are some details which are worth knowing. This blog post will help you discover those!

Firstly, here is an example of how the Listener looks like in the Cluster Management. You can see the network name resource (mylisten) and the virtual IP as well (192.168.1.78.)

image

And in Management Studio, you can see the same details as well:

image

In many diagrams the Listener is shown as a separate entity, distinct from the SQL database engine. In some ways this is correct, as you might interpret from the above virtual IP and network name. However, we have to consider the endpoint as well. If you think deeply, the question which might come up in your mind is, fine, this is a network name and a virtual IP; the cluster service helps ‘online’ these on the active node (it actually ‘brings online’ the virtual IP by registering it against the network interface of the active node) – but where is the listener port being bound and listened on? The cluster service cannot do that for us.

So here’s how you can investigate the actual mechanism. Firstly, do a netstat –ano on the primary replica (which is where the listener is active) and find out which process is listening on the listener’s IP and port. In our case these dummy address and port values are 192.168.1.78 and 1433 respectively. On checking, we see:

C:>netstat -ano

Active Connections

  Proto  Local Address          Foreign Address        State           PID

  TCP    192.168.1.78:1433      0.0.0.0:0              LISTENING       1232

The PID 1232 is actually the PID of sqlservr.exe! That means the listener functions are actually also implemented in the database engine. FYI, here is how we can check which process is PID 1232:

C:>tasklist /fi “pid eq 1232”

Image Name                     PID Session Name        Session#    Mem Usage
========================= ======== ================ =========== ============
sqlservr.exe                  1232 Services                   0    227,208 K

So this means that the ‘listener’ endpoint is actually being bound to inside of SQL database engine! We can confirm this easily by looking at the SQL Server errorlog:

2012-09-12 03:40:14.820 spid17s      The availability group database “salesdb” is changing roles ….
2012-09-12 03:40:14.830 Server       Server is listening on [ 192.168.1.78 <ipv4> 1433].

2012-09-12 03:40:14.830 Server       Started listening on virtual network name ‘mylisten’. No user action is required.

So this is good information – the listener actually ‘lives’ inside the sqlservr.exe of the primary replica. So any connection from the client will first reach this SQL instance and then potentially be redirected to the secondary read-only replica (if configured, which it is in my case.)

To test this, I ran a NetMon with a filter on the TCP ports for the listener (1433) and the replicas (1450). From this trace, it is obvious that there is an initial connection to the primary replica (which is actually the listener endpoint), and then the client is ‘redirected’ and reconnects to the actual secondary read-only replica:

image

In the above NetMon trace, 192.168.1.78 is the listener IP (which is actually bound to the primary replica’s node) and you will see initially a set of login and TDS packets flowing to and from the workstation to this listener. At the end of the TDS exchange, you will see that the client on SQLWORKSTN has picked up the 192.168.1.101 (which is the secondary replica instance in my test setup) and is talking to the secondary replica.

So to summarize:

  1. The listener IP is physically registered on the bublic network interface of the active node which hosts the AG listener cluster resource
  2. The cluster resource DLL for AlwaysOn (hadrres.dll) communicates with the primary replica SQL instance
  3. The primary replica in turns binds to the listener IP and port
  4. The read-write client which connects to the listener is actually directly connected, without any additional overhead
  5. Read-only clients may be redirected (depending on the configuration, which is indeed the case in my testing above)
  6. This redirection needs support from the client library, which is implemented in .NET 4.5 and SQL Client 2012 respectively (links below)

Some additional reading on this subject:

I hope this gives you a deeper insight how the AG Listener connectivity works. If you enjoyed reading this as much as I enjoyed creating this walkthrough, please take a minute and leave a comment and rate this post!

Thank you!

Arvind.

‘No Disks suitable for cluster disks’: one more possible cause

Today in the lab I was working on a test 3-node cluster. I was using Hyper-V for this. We normally use the iSCSI Software Target for testing, so I had set that up on a central VM and then published the target to all the VMs.

The disks were seen in the disk management of Node1, and I brought them online, partitioned and formatted them. However when I used Cluster Management to add them to the Storage group, it complained:

‘No Disks suitable for cluster disks were found, For diagnostic information about disks available run the Validate config wizard’

I did run the cluster validation (storage only) and got no further inputs. Shortly after some troubleshooting I realized I had forgotten to configure the volumes at the iSCSI initiator on each node. The easiest thing to do for this is to click the ‘Auto-configure’ button:

image

Due disclaimer: This is just ONE, but not the ONLY reason for the above error message! This is somewhat specific to the iSCSI initiator, so only check it if you are using iSCSI volumes in your cluster and if you get the above message.

T-SQL Anti-pattern of the day: ‘all-in-one’ queries

Scenario

A common requirement for enquiry queries on an OLTP database is to have search criteria which are very specific (‘get me details for for OrderID = NNNN’) and also the occasional reports which ask for all the orders (‘get me all the orders, no questions asked’.) Here is a sample from AdventureWorks which illustrates the problem:

CREATE PROCEDURE RptOrder(@OrderID int)
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader
    WHERE (SalesOrderID = @OrderID OR @OrderID IS NULL)
END

What is the meaning of the underlined predicate in the above WHERE clause? It is actually a ‘special case’ where the developer intends to get back all the rows, regardless of the OrderID. This ‘special case’ is triggered by passing in a value of NULL for the @OrderID parameter.

Problem

So while this construct looks good in theory, it lends itself to very poor performance. Take a look at the 2 cases where this procedure is executed.

Case A: with specific OrderID

EXEC RptOrder 43672

Case B: asking for all records

EXEC RptOrder NULL

The plan, it turns out, is the same for both cases and a scan is used! This is despite a seekable index being present on SalesOrderID column for the SalesOrderHeader table:

image

The reason the optimizer chooses to scan the SalesOrderHeader (in this case it chooses a non-clustered index scan) is because it has no way to determine at compile and optimization time, as to what the specific value of @OrderID would be. Hence it has no way to ‘fold’ the (@OrderID IS NULL) expression and therefore has no option but to look at all the records.

Workarounds

‘IF-ELSE’ Workaround: The straightforward workaround in simple cases like the one above is to separate out the 2 cases into an IF-ELSE block:

ALTER PROCEDURE RptOrder(@OrderID int)
AS
BEGIN
    IF (@OrderID IS NOT NULL)
    BEGIN
        SELECT *
        FROM Sales.SalesOrderHeader
        WHERE (SalesOrderID = @OrderID)
    END
    ELSE
    BEGIN
        SELECT *
        FROM Sales.SalesOrderHeader
    END
END

Now, the 2 test cases work as expected. Here are the execution plans:

EXEC RptOrder 43672

image

EXEC RptOrder NULL

image

Dynamic SQL Workaround: However, as the number of predicates in the WHERE clause increase, and if all those predicates (or most of them) have such ‘catch-all’ handling then the IF – ELSE construct becomes unviable. In those cases, a dynamic SQL construct should be considered. Of course, when dealing with dynamic SQL, we must consider security first, including the possibility of SQL Injection and also the Execution Context of the dynamic SQL statement. But that is a topic for another post. Right now, here is how we could handle something like that:

— NOTE: This code is highly simplified and does not provide for any screening

— or protection against SQL injection!!! Provided as-is, confers no warranties.

ALTER PROCEDURE RptOrder(@OrderID int)
AS
BEGIN
    DECLARE @sDynamicSQL nvarchar(4000)
    SELECT @sDynamicSQL = ‘SELECT * FROM Sales.SalesOrderHeader ‘

    IF (@OrderID IS NOT NULL)
    BEGIN
        SELECT @sDynamicSQL = @sDynamicSQL + ‘ WHERE (SalesOrderID = @OrderID)’
    END

    EXEC sp_executesql @sDynamicSQL, N’@OrderID int’, @OrderID = @OrderID
END

Different Code Paths: The cleanest way of course is to consider having separate procedures for each kind of query. For example we can have a procedure called RptSpecificOrder for the case where we are searching by specific OrderID, and another one called RptAllOrders for the ‘get-me-everything’ case. This does have the advantage of clean isolation, but it does not scale easily when the number of predicates are larger. But is does also have the advantage that if we are querying for specific orders 99% of the time, that code path is simplified and optimized accordingly.

Conclusion

Beware of this T-SQL anti-pattern as it is one of the most common ones we see and it does have a huge (negative) impact on query performance. As you can see, if they are not done with these patterns in mind, application design and reporting requirements can have a detrimental effect on OLTP query execution. Separating reporting and OLTP workloads could be the key to solving these kinds of issues. But if separation is not possible, then clever use of separate code paths and stored procedures could help ensure that the most efficient execution plan is selected for each case. For complex queries, dynamic SQL may offer the simplest way out, but due care has to be taken to ensure that permissions and SQL injection issues are kept in mind when dealing with dynamic SQL statements.