December 4, 2012 at 8:53 am
I think my powers of google-fu must be letting me down - can anyone help with the following:-
It has been suggested that a SQL 2005 server has started performing poorly for one of the applications running on it. The reason being that the SQL server is on a different domain to the client pc and that "Active directory will be slowing things down" (comment by internal support staff) - however the log on used is a SQL log on and not Windows.
Does anyone have any evidence (anecdotal or otherwise) one way or the other for this ?
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
December 5, 2012 at 6:25 am
I have received similar complaints indicating that authenicating with domain accounts takes minutes longer in some cases, connecting via the app and to SQL. However using the same account in an RDP session to the SQL server has no issues. All connections are GB and not all servers or apps exhibit this
December 5, 2012 at 6:37 am
SQL Server won't perform poorly just because its server computer is a member of the different domain than the client computer.
Authentication and hostname resolving might take a bit longer because information has to looked up from multiple domain controllers/DNS server but that does not affect query performance after authentication has completed.
In a complex forest with many domains when the two domains do not trust each other directly this might cause additional delays, but only for authentication and hostname resolving. The following article explains this: http://technet.microsoft.com/en-us/library/cc737939%28v=ws.10%29.aspx. (Link added for clarity, not as advice to implement this.)
There is probably another reason for slow performance. Follow standard procedures for performance troubleshooting. Start by running the application on a computer in same domain as the server to see if there is any difference.
December 5, 2012 at 7:32 am
Thanks for your feed back.
I'd done some initial checking before coming here (not in first post - sorry) the SQL box runs fine from either clients within the same domain or different applications. This is the server for several different applications, only one of which is producing problems for a limited number of users. Further investigations have revealed that they are in the same location - so this may well have more of a bearing on the problem.
I have also asked the users to keep a log of problems (which wasn't being done before) - so I might actually have something to help me find out what is going on in the future.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
December 5, 2012 at 8:50 am
Okay, I have a few questions for a getting a better view of the situation (not sure actually if they could be relevant):
- Is the user account running the client application a member of the workstation domain or the server domain? (Or perhaps a third domain?)
- Is the client application a normal windows application or a web site?
- How does the client application connect to SQL Server? Directly or via a server application?
- What kind of user account does the client application uses to connect to SQL Server? A domain account, local user account (in case the server application or web site runs on the same server as SQL Server), or a SQL user account?
- In the case of a domain account, which authentication scheme is used, NTLM or Kerberos? You can look this up in sys.dm_exec_connections (http://msdn.microsoft.com/en-us/library/ms181509.aspx)
December 6, 2012 at 2:03 am
- Is the user account running the client application a member of the workstation domain or the server domain? (Or perhaps a third domain?)
The are two domains Client pc is part of domain A this is the same domain that the user logs onto. The SQL Server is in domain B
- Is the client application a normal windows application or a web site?
Windows App - just to potentially muddy the waters, there are also web apps where the back end is on the same SQL server; these have not reported slow running to the same extent.
Other locations, it should be noted, don't experiance this issue to the same extent.
- How does the client application connect to SQL Server? Directly or via a server application?
Directly via ODBC
- What kind of user account does the client application uses to connect to SQL Server? A domain account, local user account (in case the server application or web site runs on the same server as SQL Server), or a SQL user account?
SQL account. Same account is used for all users.
- In the case of a domain account, which authentication scheme is used, NTLM or Kerberos? You can look this up in sys.dm_exec_connections (http://msdn.microsoft.com/en-us/library/ms181509.aspx)
SQL - don't forget this is a SQL 2005 server not 2012
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
December 6, 2012 at 2:22 pm
If you are using SQL accounts then slowdowns are unlikely to be caused by different domains.
You should follow standard performance troubleshooting procedure. Run a trace using the profiler. Use the tuning template and add the Application Name, Hostname, CPU, Reads, and Writes columns. Filter on the duration column, e.g. greater or equal than 10.000 ms. Also group by by the Hostname and Application Name columns. Start the trace and then try to reproduce the problems.
If you see high CPU, Reads, and or Writes value there is probably a SQL Server issue related to the number of queries, complexity of queries, missing indexes, incorrect usage of indexes, execution plan (re)compiles, or IO congesting that needs to be solved. Otherwise it might be a locking (waiting for resources locked by other sessions), networking, or otherwise external issue.
December 7, 2012 at 12:57 am
Arjen Krap (12/6/2012)
If you are using SQL accounts then slowdowns are unlikely to be caused by different domains.
Thanks that the bit I was looking for. To be honest I would have been surprised if the answer was any different.
I had already checked general performance of the server, and as I had said before, this seems to be impacting one site more than any other - I'll be handing this one over to the network team for them to look at.
Thanks for the help.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply