Recently we worked with a customer who reported a query that used to run a few seconds in SQL Server 2000 but it never finishes in SQL Server 2008 R2 following upgrade.
We went around and tried quite a few things but couldn't get SQL Server 2008 R2 to generate similar plan. Upon closer look at 2008 R2's query plan, we noticed something unusual. The plan has a warning "NO JOIN PREDICATE". What this means is that a cartesian product is introduced.
To illustrate the problem, let's use an example setup:
drop function dbo.myfunc
drop view v1, v2
drop table t1, t2
create table t1 (c1 int not null, c2 varchar(100))
create table t2 (c1 int not null, c2 varchar(100))
set nocount on
declare @i int
select @i = 0
while (@i < 1000)
insert into t1 (c1, c2) values (@i, 'a')
insert into t2 (c1, c2) values (@i, 'b')
select @i = @i + 1
drop function dbo.myFunc
create function dbo.myfunc (@c1 int)
return (@c1 * 100 )
create view v1 as select c1, c2, dbo.myfunc(c1) as c3 from t1
create view v2 as select c1, c2, dbo.myfunc(c1) as c3 from t2
Now, let's run the following query
set statistics profile on
-- But by pulling UDF above join in this query we actually introduce a cartesian product (NO JOIN PREDICATE)
-- UDF is called 1 million times instead of 1000 times each for the two views!
select count(*) from v1 as t1 join v2 as t2 on t1.c3 = t2.c3
set statistics profile off
The above query is very slow as illustrated in the query plan below. In the line 6 for the query plan, there is a warning "no join predicate". The join resulted in 1,000,000 rows (1,000 x 1,000 rows from each table).
In line 5, the myfunc is called 2,000,000 times (1,000,000 for computing t1.c1 and 1,000,000 for t2.c1).
This is because starting SQL Server 2005, optimizer has rule changes that will disallow non-deterministic scalar functions to be 'pushed down' in some situations (like this one).
Many times, you can simply make a function deterministic by adding schemabinding option. In the above example, re-write the function with schemabinding, it will be much faster.
From the query plan, you will no longer see that the "NO JOIN PREDICATE". The scalare UDF is pushed down right after table scan and applied only 100 times on each table.
drop function dbo.myFunc
create function dbo.myfunc (@c1 int)
return (@c1 * 100 )
Obviously, the function can be made deterministic. If you use following, the function will not be deterministic even you use schemabidning because of getdate(). In such cases, you will continue to see "NO JOIN PREDICATE" Cartesian product joins.
drop function dbo.myFunc
create function dbo.myfunc (@c1 int)
return (@c1 * 100 * datepart (mm,getdate()))
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support
Checking program code into source control is a daily ritual for most developers, but versioning database code is less well-understood. Grant Fritchey argues that getting your databases under source control is not only vital for the stability of development and deployment, but it will make your life easier when something does go wrong.
Anyone who has to manage database changes would love to be living the “continuous delivery” dream, but the reality of releasing database changes for many teams is rather more akin to praying nothing goes disastrously wrong. Here are 7 practical tips for anyone forced into a direct production upgrade.
SSIS was designed to be extensible. Although you can create tasks that will take data from a wide variety of sources, transform the data is a number of ways and write the results a wide choice of destinations, using the components provided, there will always be occasions when you need to customise your own SSIS component. Yes, it is time to hone up your C# skills and cut some code, as Saurabh explains.
I started working on this issue started by way of a Twitter conversation between myself, Jorge Segarra (@sqlchicken) and Matt Masson (@mattmasson). I then found out that Jorge was working with a customer that had opened up a Support Case. Also special thanks to Joey Seifert for letting me bounce some Active Directory questions off of him.
The issue we were troubleshooting was that when running an SSIS 2012/2014 Package from SQL Server A (Parent Domain) that connected to SQL Server B (Child Domain) it would fail with the dreaded “Login failed for user ‘NT AUTHORITY\ANONYMOUS’”
Whenever I see this error, I always start with configuration. That is typically the cause. Back in 2010 I wrote out my Kerberos Checklist for validating configuration. Jorge was aware of this and had gone through it. He also mentioned that they had ran the Kerberos Configuration Manager and it didn’t find any issues. Although in this scenario, it wouldn’t have as it doesn’t yet support cross domain topologies.
I was able to reproduce the issue they were seeing in a local environment on my end. Here is what it looked like.
So, I have two domains (battlestar.local & cylons.battlestar.local). The SQL Server in the Parent Domain (battlestar.local) is using a Service account from the child domain (cylons.battelstar.local). From a delegation standpoint, we are using full delegation. I’ll touch on Constrained Delegation later on. To make sure that everyone understand what I mean by full delegation, with the CYLONS\sqlservice AD Object, I have the following setting:
When you run a package that is hosted in the SSIS Catalog, it will cause a child process to get spawned from the SQL Service itself. This process is the ISServerExec.exe.
The other thing to note is that this process context is the context of the session that launched the package, not the SQL Server Process Context (service account). Here you can see that the ISServerExec is running as BATTLESTAR\asaxton where as the SQL Service is running as CYLONS\sqlservice.
This is the case regardless of how you execute the package. This could be through the SSMS GUI, via Stored Procedure or even by way of DTExec. If you want it to run under the context of the SQL Service account, you can “fake it” by doing a runas like operation on a process (Command Prompt, SSMS or SQL Agent Job security account).
I initially thought that this was the cause of the problem, however I later found that it is not. While I haven’t dug fully into that, I believe this to be due to the way we are launching the child process. My guess is it has to do with handle inheritance in the child process.The Single Hop
Before I even get into the SSIS package, I want to verify that the first leg of the journey is working. To do this, I connected with Management Studio from a remote machine to my SQL Server in the Parent Domain. I then used DMV’s to validated it I had connected via NTLM or Kerberos. Here is the query I used. depending on where you are doing this from, you may want to include a predicate to exclude the session you are running this from, if you are trying to find out a different sessions.
select c.session_id, s.login_name, c.auth_scheme, c.net_transport, st.text
from sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
JOIN sys.dm_exec_requests r ON c.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) as st
This showed NTLM which is not what we want. I had enabled Kerberos Event Logging, and I saw the following:
Log Name: System
Date: 6/26/2014 11:22:42 AM
Event ID: 3
Task Category: None
A Kerberos error message was received:
on logon session
Server Time: 16:22:42.0000 6/26/2014 Z
Error Code: 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
Server Realm: BATTLESTAR.LOCAL
Server Name: MSSQLSvc/captthrace.home:49375
Target Name: MSSQLSvc/captthrace.home:49375@BATTLESTAR.LOCAL
captthrace.home? What is that? This is because I’m working from home. At the office I saw a different domain. If we do a Ping on Captthrace, we will see the same result. In this case, I have my machines multi homed. This was picking up the external nic and not the internal which should have had an IP Address of 10.0.0.10.
We want this to resolve to captthrace.battlestar.local [10.0.0.10]. On my end I can do that multiple ways. I could disable the external nic, add an entry in the HOSTS file. In my case I decided to update the DNS Search suffix for the internal adapter. Making sure that battlestar.local and cylons.battlestar.local were listed. After doing that we get a properly result.
That looks better. retrying the connection to my Parent SQL Server shows that we are connected with Kerberos now.
Now when I try a single hop to the Destination SQL Server, I was seeing Kerberos as well for a single hop.The Double Hop
Now I wanted to run the package from the client machine off of the Parent SQL Server. When I did that, I got the error.
Looking at the the Parent SQL Server I saw the same issue as we had on the client box. So, I adjusted the DNS Suffixes on that machine as well. After that, the package connected successfully using Kerberos to the Destination server in the Child Domain.
One item I ran into the first time I was going through this was that I kept getting NTLM even though the name resolution was fine on the Parent SQL Server. It was using an account from the Child Domain though which had the SPN for the server. When Domains are within the same forest, the KDC should consult the GC (Global Catalog) and provide a referral if the account is in a different domain. If the account is not in the same forest you would need to define Host Mapping for the account, unless you are using a forest trust. Then you could define a Kerberos Forest Search Order.
What happened was that the Parent DC was not able to communicate with the Child DC. I discovered this when I tried to force Domain Replication. It errored out saying it couldn’t find the CYLONS domain. This could also lead to a failure as the SPN may not be noticed from the GC Perspective if replication wasn’t working. So, if you made a change in the Child Domain, the Parent Domain wouldn’t pick it up.
With the amount of work I do with SharePoint Integration, Constrained Delegation comes up a lot when we talk about Claims to Windows Tokens. This may force your environment to use Constrained Delegation. Before Windows 2012, this means that all Service Accounts and machines hosting the services all had to be in the same Domain. You were really restricted to one domain. Starting with Windows 2012, you can cross domain boundaries, but the configuration is different for Constrained Delegation from what it used to be. It is modified via PowerShell commands. If you want to read more about that, you can have a look at the following:
Kerberos Constrained Delegation Overview for Windows 2012
How Windows Server 2012 Eases the Pain of Kerberos Constrained Delegation, Part 1
I did get this to work in my environment and will look to get some posts specific to how to get this to work in the future.Takeaway – What’s in a name?
If you have verified your Kerberos configuration, be sure to validate your name resolution within your environment. It may not be resolving to the proper names. When we go to build out the SPN to use, we based on the DNS name that was resolved from the NETBIOS name. If DNS resolution isn’t working properly, then it can lead to all sorts of problems.
I’ve learned the hard way, over time, that DNS and Active Directory really blend together. If DNS has issues, then AD will more than likely have some issues. Hopefully this helps to at least show that it could be more than the normal Kerberos Configuration items that are causing an issue. Be sure to check out DNS Forwarders, Network Configuration (including Networking Binding/Order and DNS Suffix if needed).
A Ping command should return the proper name, or an NSLookup. If you have doubts, do an IPConfig /flushdns and try again. Verify the DC’s can talk/replicate to each other. As you can see from above, this should work for Full Delegation. Constrained Delegation would work with some modifications.
Adam W. Saxton | Microsoft SQL Server Escalation Services
Kerberos Configuration Manager was released back in May of 2013. It initially released with only SQL Server support. This was followed up with support for Reporting Services in November 2013. You can download the latest release from the following link:
Microsoft® Kerberos Configuration Manager for SQL Server®
This month we have released Version 3 of the Kerberos Configuration Manager which provides support for Analysis Services. This will work with Analysis Services 2005 and later (including 2014).
This release also includes support for SQL 2014 services.
If you happen to encounter something that I didn’t highlight above, you may be able to find additional information. Each time you run the tool, we will create a log file. The default location for this is the following: C:\Users\<user>\AppData\Roaming\Microsoft\KerberosConfigMgr.
The details of the log file will be flushed when you close the program. So, if it is blank, just close the tool and the log should populate. You may also find some details in the Event Log under the Source “Kerberos Configuration Manager”. If we encounter an error, it should be logged in the Application Event Log as well as the tool’s log file.Limitations Delegation
For Analysis Services, there is no Delegation check at this time. The scenarios for that are limited and may be looked at in a future release.SQL Browser DISCO SPN
This also does not do a validation on the SQL Browser SPN.Multiple Domains
Right now, the tool will only work in a single domain scenario. So, if you have the service installed in Domain A, but want to use a Service Account from Domain B, we won’t be able to discover and correct the issue appropriately. As long as the machine the instance is in and the Service Account are in the same domain, you should be good to go. This is true for Reporting Services and the SQL Server discovery.
Adam W. Saxton | Microsoft SQL Server Escalation Services
What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose all the data changes that have happened since then? Do you prepare rollback scripts to revert the changes whilst preserving the data? Do you branch by abstraction and toggle off the changes? Have you a blue-green deployment that can be switched? Do you quickly roll forward?
Although many professions, such as pilots, surgeons and IT administrators, require judgement and skill, they also require the ability to do many repeated standard procedures in a consistent and methodical manner. These procedures leave little room for creativity since they must be done right, and in the right order. For DBAs, standardization involves providing and following checklists, notes and instructions so that the results are predictable, correct and easy to maintain
Even in a technical role, communication is paramount. If you don't read widely and communicate well, your progression suffers. And one of the primary modes of communication is writing. I've written several books, articles and blogs (see "Publications" on this page), but it isn't just writing books that requires clear communication skills. You should learn to write well for something as trivial e-mails - or perhaps you should especially learn to write well for e-mails, since those might not be that trivial.
Over the years I've found a few books that have helped me write. This list contains references on grammar, structure and plot, all of which are important for technical writing as well as fiction. In fact, writing fiction helps you write better in every other area.
This isn't a complete list of all writing books by any means - it's been said that there are more books about writing than there are books, and a quick web search makes me believe that to be believable. Even so, I have a "core" set of books that I kept printed copies of, even though my other thousands (yes thousands) of books I keep on an e-reader. I recently gave these away to a friend's son who I think will make an amazing author some day:
- The Elements of Style - Small, concise, effective. It's on my desk all the time.
- Writers Inc - A very old book, originally designed for pre-teens to use in school. It's one of the best I've ever used, very readable, and a lot of fun.
- Elements of the Writing Craft - This is a great "read it and try it" book that has example texts and then what you can learn from that writing style.
- Handbook for Freelance Writing - At some point you might want to make money at writing. This book is the one I used to learn to do that.
- The Writer's Book of Checklists - I'm a checklist guy. A little outdated now, but I still refer to it when I write.
- The Observation Deck - Not actually a book, it's a series of cards that helps you come up with writing ideas when you're stuck. And I get stuck a lot.
- 20 Master Plots (And how to build them) - OK, this is my big secret. If you read this book and follow the plot contrivances in them, you can tell any kind of story to anyone. Spoiler alert: After you read this, you've seen every movie, TV show, read every book, and attended every play ever written. You have been warned.
I urge you to try these out - they've helped me tremendously. Do you have a favorite set of books that help you write well? Let us know in the comments.
Some of you may have seen a list of links that pop up when opening a case through our Microsoft support site. These resources are internally referred to as solution assets and are meant to be top bets for the problem category and are aimed at helping you solve the problems on your own.
We keep these updated on a regular basis especially for the top problem categories. The content is curated with help of our experts and based on support incident data. Currently these resources are only shown during case creation process except for the top 5 categories. For the top 5 categories these are also shared and maintained at the following blog:
Note: During case creation process only the top 3 are shown by default and the rest can be seen by clicking Show More. The blog links above does show the expanded list of these resources for top 5 categories.
For example here are the top solutions for various AlwaysOn problems:
As always please share any feedback you may have around these links. You can also refer to the following links for additional information and for finding more top solutions for other SQL topics and other Microsoft products:
SQL Server Support
Source control will allow you to maintain branches in the development of your database, but the subsequent merge isn't pain-free. How, from the practical perspective, can the database developer support the rapid development and delivery of features in an application? Versioning, branching and merging is part of the solution, but what about the rest of the solution?
There are some aspects of tables in SQL Server that a lot of people get wrong, purely because they seem so obvious that one feels embarrassed about asking questions. Robert Sheldon reckons that no questions about SQL Tables are off-limits, and deserve frank answers.
NEO4J, the graph database, can be used to provide answers that are very tricky for relational databases, including providing diagrams to show how SQL tables relate to each other, and the shortest chain of relationships between two tables, as David Poole demonstrates
A SQL migration script is similar to a SQL build script, except that it changes a database from one version to another, rather than builds it from scratch. Although they're simple in essence, it is worth knowing how to use them effectively for stress-free database updates and deployments. It takes care to get them right, but it is worth the trouble.
Over the past few months we noticed some of our customers struggling with optimizing performance when running SQL Server in a Microsoft Azure Virtual Machine, specifically around the topic of I/O Performance.
We researched this problem further, did a bunch of testing, and discussed the topic at length among several of us in CSS, the SQL Server Product team, the Azure Customer Advisory Team (CAT), and the Azure Storage team.
Based on that research, we have revised some of the guidelines and best practices on how to best configure SQL Server in this environment. You can find this collective advice which includes a quick “checklist” at this location on the web:
If you are running SQL Server already in Microsoft Azure Virtual Machine or making plans to do so, I highly encourage you to read over these guidelines and best practices.
There is other great advice in our documentation that covers more than just Performance Considerations. You can find all of these at this location:
If you deploy any of these recommendations and find they are not useful, cause you problems. or are not effective, I want to hear from you. Please contact me at firstname.lastname@example.org with your experiences