Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-07-31
9
Medium Priority
?
127 Views
Last Modified: 2014-09-08
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
Comment
Question by:25112
9 Comments
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 1000 total points
ID: 40233497
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 332 total points
ID: 40233905
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
 
LVL 15

Assisted Solution

by:Greg Besso
Greg Besso earned 332 total points
ID: 40234160
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 5

Author Comment

by:25112
ID: 40263732
>> 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
 
LVL 5

Author Comment

by:25112
ID: 40263739
>>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
 
LVL 5

Author Comment

by:25112
ID: 40263743
>>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
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 1000 total points
ID: 40264266
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
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 1000 total points
ID: 40264759
Determine FQDN
http://ima.jade-networks.com/support/regprob.html

The suffix is the part after the computer name.
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 336 total points
ID: 40264804
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question