Solved

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

Posted on 2014-07-31
9
123 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 70

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 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