sql local data: named

Showing posts with label named. Show all posts

Friday, March 23, 2012

lock info

hi,
from Activity Monitor, i found that there's a process (suspended) holding
over a thousand locks on a table named tbl_BusinessRecordDetailed.
i have 2 questions:
#1 - is there a way to find out which row it's locking?
#2 - what exactly is causing the locks? since the mode is S, which means
it's read only'
much appreciated.
from activity monitor:
resouce_type is all KEY
request_mode is all S
request_type is all LOCK
request_status is all GRANT
resouce_lock_partition is all 0
request_reference_count is all 0
request_exec_context_id is all 0
request_request_id is all 0
request_lifetime is all 1
request_owner_type is all TRANSACTION
but resource_description are pretty much all different, some examples:
1:210617
1:289260
(8800ee1b2515)
(b000d565e6d8)
(af008d6eefc0)
below is the table definition and the indexes:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
[recordID] [int] IDENTITY(1,1) NOT NULL,
[rowVersion] [timestamp] NOT NULL,
[name] [varchar](200) NULL,
[address] [varchar](200) NULL,
[crossstreet] [varchar](200) NULL,
[neighborhood] [varchar](200) NULL,
[borough] [varchar](200) NULL,
[city] [varchar](50) NULL,
[zip] [varchar](20) NULL,
[state] [varchar](2) NULL,
[telephone] [varchar](200) NULL,
[fax] [varchar](10) NULL,
[url] [varchar](500) NULL,
[bookingUrl] [varchar](500) NULL,
[bus] [varchar](200) NULL,
[subway] [varchar](300) NULL,
[description] [varchar](max) NULL,
[hours] [varchar](2000) NULL,
[admission] [varchar](2000) NULL,
[otherinfo] [varchar](1000) NULL,
[specialDirection] [varchar](max) NULL,
[sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
DEFAULT ((0)),
[dateCreated] [datetime] NOT NULL CONSTRAINT
[DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
[copyright] [varchar](100) NULL,
[mappedVarchar200_1] [varchar](200) NULL,
[mappedVarchar200_2] [varchar](200) NULL,
[mappedVarchar200_3] [varchar](200) NULL,
[mappedVarchar200_4] [varchar](200) NULL,
[mappedVarchar2000_1] [varchar](2000) NULL,
[mappedVarchar2000_2] [varchar](2000) NULL,
[mappedVarcharMax_1] [varchar](max) NULL,
[dateModified] [datetime] NULL,
[email] [varchar](50) NULL,
CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
(
[recordID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name = N'idx_biz_datecreated')
CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateCreated] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name = N'idx_biz_datemodifed')
CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateModified] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]Hi
Your lock is a shared lock so it will not be stopping other processes from
reading this information see
http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
Using DBCC INPUTBUFFER on the SID for the process should indicate the last
statement issued by the client.
John
"tin" wrote:
> hi,
> from Activity Monitor, i found that there's a process (suspended) holding
> over a thousand locks on a table named tbl_BusinessRecordDetailed.
> i have 2 questions:
> #1 - is there a way to find out which row it's locking?
> #2 - what exactly is causing the locks? since the mode is S, which means
> it's read only'
> much appreciated.
>
> from activity monitor:
> resouce_type is all KEY
> request_mode is all S
> request_type is all LOCK
> request_status is all GRANT
> resouce_lock_partition is all 0
> request_reference_count is all 0
> request_exec_context_id is all 0
> request_request_id is all 0
> request_lifetime is all 1
> request_owner_type is all TRANSACTION
> but resource_description are pretty much all different, some examples:
> 1:210617
>
> 1:289260
>
> (8800ee1b2515)
>
> (b000d565e6d8)
>
> (af008d6eefc0)
>
>
>
> below is the table definition and the indexes:
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> BEGIN
> CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> [recordID] [int] IDENTITY(1,1) NOT NULL,
> [rowVersion] [timestamp] NOT NULL,
> [name] [varchar](200) NULL,
> [address] [varchar](200) NULL,
> [crossstreet] [varchar](200) NULL,
> [neighborhood] [varchar](200) NULL,
> [borough] [varchar](200) NULL,
> [city] [varchar](50) NULL,
> [zip] [varchar](20) NULL,
> [state] [varchar](2) NULL,
> [telephone] [varchar](200) NULL,
> [fax] [varchar](10) NULL,
> [url] [varchar](500) NULL,
> [bookingUrl] [varchar](500) NULL,
> [bus] [varchar](200) NULL,
> [subway] [varchar](300) NULL,
> [description] [varchar](max) NULL,
> [hours] [varchar](2000) NULL,
> [admission] [varchar](2000) NULL,
> [otherinfo] [varchar](1000) NULL,
> [specialDirection] [varchar](max) NULL,
> [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> DEFAULT ((0)),
> [dateCreated] [datetime] NOT NULL CONSTRAINT
> [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> [copyright] [varchar](100) NULL,
> [mappedVarchar200_1] [varchar](200) NULL,
> [mappedVarchar200_2] [varchar](200) NULL,
> [mappedVarchar200_3] [varchar](200) NULL,
> [mappedVarchar200_4] [varchar](200) NULL,
> [mappedVarchar2000_1] [varchar](2000) NULL,
> [mappedVarchar2000_2] [varchar](2000) NULL,
> [mappedVarcharMax_1] [varchar](max) NULL,
> [dateModified] [datetime] NULL,
> [email] [varchar](50) NULL,
> CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> (
> [recordID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => N'idx_biz_datecreated')
> CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateCreated] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => N'idx_biz_datemodifed')
> CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateModified] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>
>|||hmm, actually, there have been "somthing" that blocking some records in that
table that other process can't update them. as simple as an update like
"update tbl_businessrecorddetailed set name = 'whatever' where recordid =123" would just hang. just certain records are like that while most others
are fine. but after restarting the db service, it's fine. but the problem
would return after a while.
since the process is suspended, can i just kill it as a quick fix for now?
but i need to find out the cause going forward, any ideas'
thanks.
"John Bell" wrote:
> Hi
> Your lock is a shared lock so it will not be stopping other processes from
> reading this information see
> http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
> and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
> Using DBCC INPUTBUFFER on the SID for the process should indicate the last
> statement issued by the client.
>
> John
> "tin" wrote:
> > hi,
> >
> > from Activity Monitor, i found that there's a process (suspended) holding
> > over a thousand locks on a table named tbl_BusinessRecordDetailed.
> >
> > i have 2 questions:
> >
> > #1 - is there a way to find out which row it's locking?
> >
> > #2 - what exactly is causing the locks? since the mode is S, which means
> > it's read only'
> >
> > much appreciated.
> >
> >
> > from activity monitor:
> >
> > resouce_type is all KEY
> > request_mode is all S
> > request_type is all LOCK
> > request_status is all GRANT
> > resouce_lock_partition is all 0
> > request_reference_count is all 0
> > request_exec_context_id is all 0
> > request_request_id is all 0
> > request_lifetime is all 1
> > request_owner_type is all TRANSACTION
> >
> > but resource_description are pretty much all different, some examples:
> > 1:210617
> >
> >
> >
> > 1:289260
> >
> >
> >
> > (8800ee1b2515)
> >
> >
> >
> > (b000d565e6d8)
> >
> >
> >
> > (af008d6eefc0)
> >
> >
> >
> >
> >
> >
> > below is the table definition and the indexes:
> >
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> > BEGIN
> > CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> > [recordID] [int] IDENTITY(1,1) NOT NULL,
> > [rowVersion] [timestamp] NOT NULL,
> > [name] [varchar](200) NULL,
> > [address] [varchar](200) NULL,
> > [crossstreet] [varchar](200) NULL,
> > [neighborhood] [varchar](200) NULL,
> > [borough] [varchar](200) NULL,
> > [city] [varchar](50) NULL,
> > [zip] [varchar](20) NULL,
> > [state] [varchar](2) NULL,
> > [telephone] [varchar](200) NULL,
> > [fax] [varchar](10) NULL,
> > [url] [varchar](500) NULL,
> > [bookingUrl] [varchar](500) NULL,
> > [bus] [varchar](200) NULL,
> > [subway] [varchar](300) NULL,
> > [description] [varchar](max) NULL,
> > [hours] [varchar](2000) NULL,
> > [admission] [varchar](2000) NULL,
> > [otherinfo] [varchar](1000) NULL,
> > [specialDirection] [varchar](max) NULL,
> > [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> > DEFAULT ((0)),
> > [dateCreated] [datetime] NOT NULL CONSTRAINT
> > [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> > [copyright] [varchar](100) NULL,
> > [mappedVarchar200_1] [varchar](200) NULL,
> > [mappedVarchar200_2] [varchar](200) NULL,
> > [mappedVarchar200_3] [varchar](200) NULL,
> > [mappedVarchar200_4] [varchar](200) NULL,
> > [mappedVarchar2000_1] [varchar](2000) NULL,
> > [mappedVarchar2000_2] [varchar](2000) NULL,
> > [mappedVarcharMax_1] [varchar](max) NULL,
> > [dateModified] [datetime] NULL,
> > [email] [varchar](50) NULL,
> > CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> > (
> > [recordID] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > N'idx_biz_datecreated')
> > CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> > [dbo].[tbl_BusinessRecordDetailed]
> > (
> > [dateCreated] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > GO
> >
> > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > N'idx_biz_datemodifed')
> > CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> > [dbo].[tbl_BusinessRecordDetailed]
> > (
> > [dateModified] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> >
> >
> >
> >|||Hi
sp_who2 has a blk column that may show what spid is blocking a process. You
may also want to look at http://support.microsoft.com/kb/271509 and run the
sp_blocker_pss80 procedure.
John
"tin" wrote:
> hmm, actually, there have been "somthing" that blocking some records in that
> table that other process can't update them. as simple as an update like
> "update tbl_businessrecorddetailed set name = 'whatever' where recordid => 123" would just hang. just certain records are like that while most others
> are fine. but after restarting the db service, it's fine. but the problem
> would return after a while.
> since the process is suspended, can i just kill it as a quick fix for now?
> but i need to find out the cause going forward, any ideas'
> thanks.
>
>
> "John Bell" wrote:
> > Hi
> >
> > Your lock is a shared lock so it will not be stopping other processes from
> > reading this information see
> > http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
> > and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
> >
> > Using DBCC INPUTBUFFER on the SID for the process should indicate the last
> > statement issued by the client.
> >
> >
> > John
> >
> > "tin" wrote:
> >
> > > hi,
> > >
> > > from Activity Monitor, i found that there's a process (suspended) holding
> > > over a thousand locks on a table named tbl_BusinessRecordDetailed.
> > >
> > > i have 2 questions:
> > >
> > > #1 - is there a way to find out which row it's locking?
> > >
> > > #2 - what exactly is causing the locks? since the mode is S, which means
> > > it's read only'
> > >
> > > much appreciated.
> > >
> > >
> > > from activity monitor:
> > >
> > > resouce_type is all KEY
> > > request_mode is all S
> > > request_type is all LOCK
> > > request_status is all GRANT
> > > resouce_lock_partition is all 0
> > > request_reference_count is all 0
> > > request_exec_context_id is all 0
> > > request_request_id is all 0
> > > request_lifetime is all 1
> > > request_owner_type is all TRANSACTION
> > >
> > > but resource_description are pretty much all different, some examples:
> > > 1:210617
> > >
> > >
> > >
> > > 1:289260
> > >
> > >
> > >
> > > (8800ee1b2515)
> > >
> > >
> > >
> > > (b000d565e6d8)
> > >
> > >
> > >
> > > (af008d6eefc0)
> > >
> > >
> > >
> > >
> > >
> > >
> > > below is the table definition and the indexes:
> > >
> > >
> > > SET ANSI_NULLS ON
> > > GO
> > > SET QUOTED_IDENTIFIER ON
> > > GO
> > > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> > > BEGIN
> > > CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> > > [recordID] [int] IDENTITY(1,1) NOT NULL,
> > > [rowVersion] [timestamp] NOT NULL,
> > > [name] [varchar](200) NULL,
> > > [address] [varchar](200) NULL,
> > > [crossstreet] [varchar](200) NULL,
> > > [neighborhood] [varchar](200) NULL,
> > > [borough] [varchar](200) NULL,
> > > [city] [varchar](50) NULL,
> > > [zip] [varchar](20) NULL,
> > > [state] [varchar](2) NULL,
> > > [telephone] [varchar](200) NULL,
> > > [fax] [varchar](10) NULL,
> > > [url] [varchar](500) NULL,
> > > [bookingUrl] [varchar](500) NULL,
> > > [bus] [varchar](200) NULL,
> > > [subway] [varchar](300) NULL,
> > > [description] [varchar](max) NULL,
> > > [hours] [varchar](2000) NULL,
> > > [admission] [varchar](2000) NULL,
> > > [otherinfo] [varchar](1000) NULL,
> > > [specialDirection] [varchar](max) NULL,
> > > [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> > > DEFAULT ((0)),
> > > [dateCreated] [datetime] NOT NULL CONSTRAINT
> > > [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> > > [copyright] [varchar](100) NULL,
> > > [mappedVarchar200_1] [varchar](200) NULL,
> > > [mappedVarchar200_2] [varchar](200) NULL,
> > > [mappedVarchar200_3] [varchar](200) NULL,
> > > [mappedVarchar200_4] [varchar](200) NULL,
> > > [mappedVarchar2000_1] [varchar](2000) NULL,
> > > [mappedVarchar2000_2] [varchar](2000) NULL,
> > > [mappedVarcharMax_1] [varchar](max) NULL,
> > > [dateModified] [datetime] NULL,
> > > [email] [varchar](50) NULL,
> > > CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> > > (
> > > [recordID] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > > ) ON [PRIMARY]
> > > END
> > > GO
> > >
> > > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > > N'idx_biz_datecreated')
> > > CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> > > [dbo].[tbl_BusinessRecordDetailed]
> > > (
> > > [dateCreated] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > > GO
> > >
> > > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > > N'idx_biz_datemodifed')
> > > CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> > > [dbo].[tbl_BusinessRecordDetailed]
> > > (
> > > [dateModified] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > >
> > >
> > >
> > >

lock info

Wednesday, March 7, 2012

Local named pipes better than TCP/IP?

Is it generally true that local named pipes are better than TCP/IP? Are there
any drawbacks?
Regards, Per.
"PerFry" <PerFry@.discussions.microsoft.com> wrote in message
news:57138C0A-544C-435E-81A5-B2F7455C608B@.microsoft.com...
> Is it generally true that local named pipes are better than TCP/IP?
Not true.

> Are there any drawbacks?
Named pipes have been around for a long, long time. Therefore on a LAN,
named pipes may give slightly better performance. However, TCP/IP is far
more tolerant and resiliant with both LAN and WAN applications.
Occaisionally, you'll find applications (mostly legacy) that will require
named pipes. A good way to handle that configure the server side network
library utility to first use TCP/IP, then named pipes (in that order).
Steve
|||With SQL Server 2000 the default for local connections (connections made on
the server itself) is shared memory. Steve is correct that named pipes is
for the most part a legacy connection method. I would just use the default
of shared memory for local connections and TCP/IP for remote connections.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I'd like to stress that we're having the client and server on the same
machine, so I'm referring to _local_ named pipes. Books Online states that
"Local named pipes runs in kernel mode and is extremely fast" (
http://msdn.microsoft.com/library/de...dbc_g_5l9r.asp ).
"Rand Boyd [MSFT]" wrote:

> With SQL Server 2000 the default for local connections (connections made on
> the server itself) is shared memory. Steve is correct that named pipes is
> for the most part a legacy connection method. I would just use the default
> of shared memory for local connections and TCP/IP for remote connections.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||IMO -> In general you are not going to see huge differences in performance
using the different network library options such as named pipes, shared
memory or tcp-ip sockets.
The reason for this is the overhead network library performance is usually
insignificant compared to perf of the client application and SQL query
performance.
So you are better off spending time optimizing your SQL queries and your
client code.
Matt
"PerFry" <PerFry@.discussions.microsoft.com> wrote in message
news:161A939F-0102-46C2-80EB-6CC0B55DAD2F@.microsoft.com...[vbcol=seagreen]
> I'd like to stress that we're having the client and server on the same
> machine, so I'm referring to _local_ named pipes. Books Online states that
> "Local named pipes runs in kernel mode and is extremely fast" (
> http://msdn.microsoft.com/library/de...dbc_g_5l9r.asp )
> .
>
> "Rand Boyd [MSFT]" wrote:
|||It's about what I shall recommend all our customers. I want to recommend them
the best configuration options when they choose to install our application
server and SQL Server on the same machine. So when I have to recommend
something, would it be shared memory?
/Per
"Matt Neerincx [MS]" wrote:

> IMO -> In general you are not going to see huge differences in performance
> using the different network library options such as named pipes, shared
> memory or tcp-ip sockets.
> The reason for this is the overhead network library performance is usually
> insignificant compared to perf of the client application and SQL query
> performance.
> So you are better off spending time optimizing your SQL queries and your
> client code.
> Matt
>
> "PerFry" <PerFry@.discussions.microsoft.com> wrote in message
> news:161A939F-0102-46C2-80EB-6CC0B55DAD2F@.microsoft.com...
>
>
|||Yes in this case then shared memory for local connections and TCP-IP for
remote is a good recommendation. By default the driver will automatically
pick these methods, i.e. if the driver connects to local SQL it will use
shared memory and if the driver connects to remote SQL it will use TCP-IP.
One thing you can do as well is if you want local ONLY connections in this
case (for example to avoid remote users from trying to connect to the SQL
Server) then you could disable tcp-ip protocol for SQL and only use shared
memory. For example, to follow the standard security procedure of "reducing
the attack surface" to SQL you would do this.
Matt
"PerFry" <PerFry@.discussions.microsoft.com> wrote in message
news:A0F3FEED-C578-4558-A8C0-7FF0F7A4636E@.microsoft.com...[vbcol=seagreen]
> It's about what I shall recommend all our customers. I want to recommend
> them
> the best configuration options when they choose to install our application
> server and SQL Server on the same machine. So when I have to recommend
> something, would it be shared memory?
> /Per
> "Matt Neerincx [MS]" wrote:

Local named pipes better than TCP/IP?

Is it generally true that local named pipes are better than TCP/IP? Are ther
e
any drawbacks?
Regards, Per."PerFry" <PerFry@.discussions.microsoft.com> wrote in message
news:57138C0A-544C-435E-81A5-B2F7455C608B@.microsoft.com...
> Is it generally true that local named pipes are better than TCP/IP?
Not true.

> Are there any drawbacks?
Named pipes have been around for a long, long time. Therefore on a LAN,
named pipes may give slightly better performance. However, TCP/IP is far
more tolerant and resiliant with both LAN and WAN applications.
Occaisionally, you'll find applications (mostly legacy) that will require
named pipes. A good way to handle that configure the server side network
library utility to first use TCP/IP, then named pipes (in that order).
Steve|||With SQL Server 2000 the default for local connections (connections made on
the server itself) is shared memory. Steve is correct that named pipes is
for the most part a legacy connection method. I would just use the default
of shared memory for local connections and TCP/IP for remote connections.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I'd like to stress that we're having the client and server on the same
machine, so I'm referring to _local_ named pipes. Books Online states that
"Local named pipes runs in kernel mode and is extremely fast" (
http://msdn.microsoft.com/library/d...r />
_5l9r.asp ).
"Rand Boyd [MSFT]" wrote:

> With SQL Server 2000 the default for local connections (connections made o
n
> the server itself) is shared memory. Steve is correct that named pipes is
> for the most part a legacy connection method. I would just use the default
> of shared memory for local connections and TCP/IP for remote connections.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||IMO -> In general you are not going to see huge differences in performance
using the different network library options such as named pipes, shared
memory or tcp-ip sockets.
The reason for this is the overhead network library performance is usually
insignificant compared to perf of the client application and SQL query
performance.
So you are better off spending time optimizing your SQL queries and your
client code.
Matt
"PerFry" <PerFry@.discussions.microsoft.com> wrote in message
news:161A939F-0102-46C2-80EB-6CC0B55DAD2F@.microsoft.com...[vbcol=seagreen]
> I'd like to stress that we're having the client and server on the same
> machine, so I'm referring to _local_ named pipes. Books Online states that
> "Local named pipes runs in kernel mode and is extremely fast" (
> http://msdn.microsoft.com/library/d.../>
_g_5l9r.asp )
> .
>
> "Rand Boyd [MSFT]" wrote:
>|||It's about what I shall recommend all our customers. I want to recommend the
m
the best configuration options when they choose to install our application
server and SQL Server on the same machine. So when I have to recommend
something, would it be shared memory?
/Per
"Matt Neerincx [MS]" wrote:

> IMO -> In general you are not going to see huge differences in performance
> using the different network library options such as named pipes, shared
> memory or tcp-ip sockets.
> The reason for this is the overhead network library performance is usually
> insignificant compared to perf of the client application and SQL query
> performance.
> So you are better off spending time optimizing your SQL queries and your
> client code.
> Matt
>
> "PerFry" <PerFry@.discussions.microsoft.com> wrote in message
> news:161A939F-0102-46C2-80EB-6CC0B55DAD2F@.microsoft.com...
>
>|||Yes in this case then shared memory for local connections and TCP-IP for
remote is a good recommendation. By default the driver will automatically
pick these methods, i.e. if the driver connects to local SQL it will use
shared memory and if the driver connects to remote SQL it will use TCP-IP.
One thing you can do as well is if you want local ONLY connections in this
case (for example to avoid remote users from trying to connect to the SQL
Server) then you could disable tcp-ip protocol for SQL and only use shared
memory. For example, to follow the standard security procedure of "reducing
the attack surface" to SQL you would do this.
Matt
"PerFry" <PerFry@.discussions.microsoft.com> wrote in message
news:A0F3FEED-C578-4558-A8C0-7FF0F7A4636E@.microsoft.com...[vbcol=seagreen]
> It's about what I shall recommend all our customers. I want to recommend
> them
> the best configuration options when they choose to install our application
> server and SQL Server on the same machine. So when I have to recommend
> something, would it be shared memory?
> /Per
> "Matt Neerincx [MS]" wrote:
>

Friday, March 23, 2012

lock info

lock info

lock info

Wednesday, March 7, 2012

Local named pipes better than TCP/IP?

Local named pipes better than TCP/IP?

sql local data

Blog Archive

About Me