Connection: "(local)" vs "ComputerName"

Hi,
I was reading in a book about SQL 2005 about how connections are made to SQL
2005. Assume I have a SQL Server instance named SQL1 and I have a client app
installed on SQL1. The book claimed that if the app's connection string
sets the datasource to "(local)" then the connection uses Shared Memory, and
if it set the datasource to the computer's name, "SQL1", that it uses the
TCP/IP stack which is slower than Shared Memory.
Reading BOL for SQL 2000, it seems to indicate that a local client will
always use Shared Memory, regardless of how the datasource is specified.
Can someone provide a definitive clarification and indicate whether it is
different for SQL 2000 and SQL 2005?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel,
I am afraid that the book description is not correct. By default any
connection with the following names from local computer will use Shared
Memory protocol:
1. "<computer_name>"
2. "<computer_name>\<instance_name>" for a named instance
3. "(local)"
4. "(local)\<instance_name>" for a named instance
5. "Localhost"
6. "localhost\<instance_name>" for a named instance
7. A single period "."
8. ".\<instance_name>" for a named instance
A local connection may use another protocol under the following
circumstances:
1. Connect to a client alias that specifies a protocol.
2. Prefix the computer name with the protocol (for example,
"np:<computer_name>" or "tcp:<computer_name>").
3. Connect to the IP address which results in a TCP/IP connection.
4. Connect to the fully qualified domain name (FQDN) which results in a
TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
You can run the following statement to check the current protocol for the
local connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
For more information, you may refer to:
Default Client Connection Behavior
http://msdn2.microsoft.com/en-us/library/ms190204.aspx
One interesting thing is that for the forms of <computer_name> and
<computer_name>\<instance_name>, they do not always use Shared Memory
protocol. They just by default use Shared Memory protocol.
Actually in registry there is a value that records which protocol is used
in the latest connection. For the next time local connection, it will use
the latest protocol.
For example:
--This results in Shared Memory connection
sqlcmd /SCharlesXP /E
--This results in Named Pipes connection
sqlcmd /Snp:CharlesXP /E
--This keeps using Named Pipes connection
sqlcmd /SCharlesXP /E
--This results in Shared Memory connection
sqlcmd /Slpc:CharlesXP /E
--This keeps using Shared Memory connection
sqlcmd /SCharlesXP /E
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles;
Have you tried it on SQL2005 SP2 running on XP? I tried on two such
instances. Even with Named Pipes disabled, the following query:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
still returns Named Pipes for many of the server name settings in your first
group, which is supposed to use Shared Memory.
On W2K3 servers, I didn't see this behavior.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Linchi,
Did you restart your SQL Server service after you disable Named Pipes
protocol on your server by using SQL Server Configuration Manager? or did
you just disable the Named Pipes in SQL Native Client Configuration on your
computer? Per my test, it is no use of disabling Named Pipes on client;
but Shared Memory will be used if you disable the Named Pipes protocol on
server.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thank you very much for the detailed response.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:kdagNzOnHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>|||Positive, I did restart the SQL Server service. In fact, I just tried a few
more times to make sure I wasn't hallucinating.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Linchi,
> Did you restart your SQL Server service after you disable Named Pipes
> protocol on your server by using SQL Server Configuration Manager? or did
> you just disable the Named Pipes in SQL Native Client Configuration on you
r
> computer? Per my test, it is no use of disabling Named Pipes on client;
> but Shared Memory will be used if you disable the Named Pipes protocol on
> server.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>
>
>
>|||Hi Linchi,
It is strange. I have checked that on my computer and I could not reproduce
your issue. My OS is also Windows XP. I also checked that on a Windows 2K
x64 computer. It seemed that the Named Pipes was not disabled on your SQL
Server. Could you please check your SQL Server error logs to see if the
Named Pipes protocol was not disabled during the service startup? or you
may check if you can reproduce your issue on another computer.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============