August 22, 2014 at 10:43 pm
Hi everyone!
I'm running into a weird issue with SQL server that I am unable to explain and/or fix. When connecting to my local SQL server instance using (local) as server name, it takes at least a few seconds to establish the connection. I tried using both SSMS and sqlcmd and the symptoms are the same - long waiting time to open a connection, and with SSMS, it also takes time to open a new query window, or expand the "Databases" node in Object Explorer, or use the database drop down list. Querying the database after connection has been established is fast.
Scenario:
- Local installation of SQL Server 2008 R2
- Computer is a domain member
- Connecting both as "sa" and domain user yields similar results
Now, the interesting part is that when I try connecting using either of these:
- (local)\INSTANCENAME
- PCNAME\INSTANCENAME
the delay is no longer there. This affects any connection using exactly "(local)" as the server name only.
The following query:
select net_transport from sys.dm_exec_connections
where session_id = @@spid
returns "Named pipes" as protocol for all connections using any of the server names listed above. I cross checked that with any other working installation and it looks like the same query returns "Shared Memory" instead. I tried forcing Shared Memory usage by specifying lpc:(local) as server name but to no avail (the query would still show "Named Pipes" as the protocol being used).
Afterwards, I compared configurations of these servers and it looks like they are both the same. Shared Memory is enabled for all configurations; Pipe Name is set to "sql/query" for the native client configurations, and "\\.\pipe\sql\query" for network configuration.
I noticed one difference in behavior between "(local)" and "(local)\SQLEXPRESS" connections when I hooked up the profiler. When connecting using "(local)\SQLEXPRESS", all queries would display LoginName as "sa", and NTUserName would be blank; When connecting using "(local)", NTUserName would be set to my domain user name. Both connections are established using SQL Server Authentication and the "sa" user.
This is all the information I managed to gather. Any help with this would be appreciated!
Thanks!
September 9, 2014 at 12:33 pm
Hi Koxta,
Have you tried logging in with
.
That is just a period instead of (local) ?
Wondering if that produces the same delay ?
Regards,
Jeremy
September 9, 2014 at 12:36 pm
Koxta,
Something else that may be the culprit would be domain controller. Maybe you could trace traffic as well, there might be a request with domain credentials that is taking too long to resolve.
Just an additional thought!
Jeremy
September 9, 2014 at 7:37 pm
Thanks for the suggestion!
I will try connecting using "." tomorrow and report back.
What would you recommend to trace traffic? I've used wireshark/ethereal and iptraf on numerous occasions in the past, but that sounds like using a bazooka to kill an ant.
Cheers!
September 10, 2014 at 10:26 am
So, I tried connecting using "." as server name and the results are the same as for "(local)" - slow and painful.
September 10, 2014 at 11:09 am
I think I'm on to something. I tried connecting to my local sql server using (local) while running netstat. Consecutive refreshes (1 second intervals) of netstat show the following active TCP connections, in order:
TCP 10.172.85.106:59608 10.172.85.106:1433 SYN_SENT
TCP 192.168.200.1:59678 192.168.200.1:1433 SYN_SENT
TCP 192.168.160.1:59679 192.168.160.1:1433 SYN_SENT
TCP 10.172.85.107:59681 10.172.85.107:1433 SYN_SENT
TCP [fe80::181:b5f1:74aa:d040%11]:59682 [fe80::181:b5f1:74aa:d040%11]:1433 SYN_SENT
TCP [fe80::4861:6745:b9ac:61bd%24]:59683 [fe80::4861:6745:b9ac:61bd%24]:1433 SYN_SENT
TCP [fe80::95ae:6a3f:32b1:2e3a%25]:59684 [fe80::95ae:6a3f:32b1:2e3a%25]:1433 SYN_SENT
TCP [fe80::e860:ab60:4e9f:23c2%13]:59685 [fe80::e860:ab60:4e9f:23c2%13]:1433 SYN_SENT
These IPs are bound to my local interfaces (eth, wlan, vmware, etc.). It seems that the problem is these connections are in SYN_SENT state for around a second (and timing out, maybe?). That's causing severe delays. On top of that, in order to populate the Object Explorer window, this cycle is repeated at least once. So this results in 16-24 seconds spent waiting.
Now the question is, what might be causing this behavior and how come it's not present when connecting via (local)\SQLEXPRESS ?
September 10, 2014 at 12:11 pm
I managed to solve the problem... kinda...
The TCP port setting was blank in SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQLEXPRESS -> TCP/IP -> IP Addresses. I set it to 1433 and now the connection is being established instantaneously when using (local).
So now, my question is, why is TCP the protocol of first choice? Shouldn't it be shared memory or at least named pipes? I know for sure that connecting using (local)\SQLEXPRESS uses Shared Memory.
I will investigate this further, but any feedback will be appreciated.
September 10, 2014 at 4:19 pm
Sorry for the delay - it didn't send the updates through to my email! I was wondering about this so I logged on- sorry man!
Okay first - ya Wireshark is a great tool to do it all. Normally overkill, and generally another tool i use which is a little less weight is Fiddler.
More importantly, on install the Port is filled in on a default install. So the fact you didn't have that makes me wonder when/who turned it off. By default it allows multiple modes of connections. This is why generally even if you are using named pipes you wouldn't have a serious delay. It looks like though with removing that port, maybe something internal doesn't allow it to die quickly. It has to wait for NO, maybe - which makes it slow.
Glad to hear you have it figured out!
Now back to the real work right 😉
Jeremy
September 10, 2014 at 7:09 pm
Thanks for your feedback!
Yes, I've used a lot of sniffing tools. Weird part about it was that no tool was showing any outgoing packets, except for netstat, which was showing the connection. It is also weird that SQL Server wasn't simply rejecting connections, it's as if traffic was being filtered out completely.
I still do not know why can't I enforce "(local)" to use the shared memory protocol. Any ideas?
September 11, 2014 at 8:05 am
Hello,
Well the only thoughts I have there are as follows:
Shared Memory Protocol is going to be default/best practice IF and ONLY IF the application and database is on the same server. I think even when they are both on the same server Named Pipes can out perform shared memory protocol.
So my thought here - if you are burning for a clear answer I would imagine you need to dig into what the configuration types do, and verify in newer SQL versions it isn't trying to use something else by default if it thinks it will perform better.
I don't have anything specific to give a solid answer just direction/approach on digging... Sorry about that! Maybe someone else can answer this next question. ( maybe you could post this as a separate question and get a fresh audience? )
Well have a great day!
Jeremy
September 11, 2014 at 11:30 am
Thanks for your reply!
i_amhers (9/11/2014)Shared Memory Protocol is going to be default/best practice IF and ONLY IF the application and database is on the same server
Yes, this part is obvious. And yes, I am connecting to the local machine.
i_amhers (9/11/2014)I think even when they are both on the same server Named Pipes can out perform shared memory protocol.
This is questionable, but the point is moot. For some reason my connection prefers the TCP protocol as the default one.
i_amhers (9/11/2014)So my thought here - if you are burning for a clear answer I would imagine you need to dig into what the configuration types do, and verify in newer SQL versions it isn't trying to use something else by default if it thinks it will perform better.
Hmm, well, this is 2008 R2, and we have a bunch of workstations and server machines that also have 2008 R2, and my machine is the odd one out behaving like this.
I will consider creating a separate thread, thank you!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply