Solved

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

Posted on 2014-07-31
9
115 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:whoajack
whoajack 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 68

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to import 7 54
using BEGIN END with IF statement.. 12 40
Selecting specific rows 3 36
Delete Trigger in SQL Server2008R2 5 13
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now