Solved

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

Posted on 2014-07-31
9
120 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 250 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 83 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 83 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 250 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 250 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 69

Accepted Solution

by:
Qlemo earned 84 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 33
get_systemdrive info from tsql? 1 18
partitioning database after decade growth 8 25
SQL Database Restore 2008 R2 1 11
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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