• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

why is named instance faster in SSMS with a '\'

when the named instance is tried to be connected, it takes almost 10 seconds.

when you just leave the named part, but replace with \, it is much faster(1-2 seconds).. what could be the reason?

example:
server1\serv1
vs
server1\
0
25112
Asked:
25112
6 Solutions
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
If you try it the other way around, I e with a backslash first and secondly with the name. Is the results the same?

What port is your named instance answering on? Is it. 1433, if so it makes sense.. No roundtrip to SQL browser is needed!. Please check if this is the case?

Regards Marten
0
 
David ToddSenior DBACommented:
Hi,

My guess this test is not fair.

That is, the first time may involve a trip to the DNS server to resolve the hostname. The second time around the host name is already resolved. Running a ipconfig /flushdns from the command line before each connect attempt will level the playing field a little.

On the other hand, if major databases especially the one connected to are on autoclose, then the first time the server has to wake up so to speak, and the second its already awake.

Regards
  David
0
 
Greg BessoIT Solutions EngineerCommented:
Just thinking out loud, but what about...

1) what if you try server.fqdn.suffix\serv1 instead of just server\serv1?
2) do you have a default instance also running in addition to your named instance?
3) what protocols are enabled on your instance(s)? TCP and named pipes?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
25112Author Commented:
>> a backslash first and secondly with the name
this does not work...

>>No roundtrip to SQL browser is needed
can you explain how this is possible with just a backslash added?
0
 
25112Author Commented:
>>ipconfig /flushdns

will this affect production live database  performance?

>>On the other hand, if major databases especially the one connected to are on autoclose
thanksfullu.. that is not the case here... (no autoclose)
0
 
25112Author Commented:
>>server.fqdn.suffix\serv1
can you suggest how I can find the server's "fqdn.suffix" value?

>>do you have a default instance also running in addition to your named instance?
no

>>what protocols are enabled on your instance(s)? TCP and named pipes?
yes both and also shared memory.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Connstring sqlserver1/instance1
Client uses udp on port 1434 to query for the correct port, I e roundtrip
Connstring sqlserver1,1558 connects directly to SQL answering on port 1558.
Connstring sqlserver assumes default instance and connects directly to port 1433.
If you give a named instance it's own IP address and a A-record in the DNS, you can connect directly to named instances by using the a record. This works if the named instance is configured to answer at port 1433.

Regards Marten
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Determine FQDN
http://ima.jade-networks.com/support/regprob.html

The suffix is the part after the computer name.
0
 
QlemoC++ DeveloperCommented:
Correction to http:#a40264266:

Connstring sqlserver1\instance1
Client uses udp on port 1434 to query for the correct port, I e roundtrip
If there is no answer, 1433/tcp will be tried in addition => additional roundtrip.

Connstring sqlserver1,1558 connects directly to SQL answering on port 1558.

Connstring sqlserver [...] connects directly to port 1433 (whether named or default instance does not matter).

This does not make sense:
If you give a named instance it's own IP address and a A-record in the DNS, you can connect directly to named instances by using the a record. This works if the named instance is configured to answer at port 1433.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now