sql local data: monitor

Showing posts with label monitor. Show all posts

Wednesday, March 28, 2012

Lock requests/sec

I have just played a little with the performance monitor on my SQL-server and tried to look at the lock request per second. I really don't know what an acceptable range for this is but the server is running a pretty advanced website with quite alot of transactions. With only a modest 20 users online I have an average of about 5500 locks per second, is this acceptable and what are limits I should look for? The server is a Win2k dual AMD 1,6Mhz 512MB SP4 with RAID1...I don't know if you can look at the number of locks and determine if hardware is adequate. One should look at the database design, queries and stored procedures that access and modify data. Look at the type of locks, are they shared or exclusive? Maybe 95% of the locks are shared and that is due to scanning or large datasets coming back. Maybe one mass update is aquiry exclusive locks and will escalate to a table lock.

You sholud look at the type of locks, locks/session and the queries aquiring the locks.|||I'm not really concerned with the hardware as it seemingly performs well, but I was wondering if this was a really high number of locks and if I should be concerned about it. And how do I issue exclusive locks for my inserts and updates?|||achorozy is absoolutely right, the number of locks is arbitrary, are you having deadlocks, blocking, table locks, etc are more important questions, not to repeat achorozy but these could be shared locks which could be just queries which is perfectly fine.

HTH

Monday, March 26, 2012

Lock Monitor

I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
for the Lock Monitor. The database is used by a Java application that
controls all update transactions. There are some instances where the app is
saving data, querying data and then saving more data inside a distributed
transaction. We are trying to remove those instances.
But I need tips and help on how to get lower the Lock Monitor CPU usage.
What is a average count?Hi
If you are not in control of the scope of the transaction, then you may be
limited to looking at the high duration of high volume statements and try an
d
tune the database to make them quick. You may also want to look at
distributing the I/O better such as adding discs to a raid system or
splitting filegroups into files over different spindles.
Use sp_locks to see lock information, check the isolation level and look for
query hints in SQL Profiler.
John
"JI" wrote:

> I have a sql server (Sql 2k Sp3) that has a very high CPU utilization coun
t
> for the Lock Monitor. The database is used by a Java application that
> controls all update transactions. There are some instances where the app i
s
> saving data, querying data and then saving more data inside a distributed
> transaction. We are trying to remove those instances.
> But I need tips and help on how to get lower the Lock Monitor CPU usage.
> What is a average count?
>
>

Lock Monitor

I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
for the Lock Monitor. The database is used by a Java application that
controls all update transactions. There are some instances where the app is
saving data, querying data and then saving more data inside a distributed
transaction. We are trying to remove those instances.
But I need tips and help on how to get lower the Lock Monitor CPU usage.
What is a average count?Hi
If you are not in control of the scope of the transaction, then you may be
limited to looking at the high duration of high volume statements and try and
tune the database to make them quick. You may also want to look at
distributing the I/O better such as adding discs to a raid system or
splitting filegroups into files over different spindles.
Use sp_locks to see lock information, check the isolation level and look for
query hints in SQL Profiler.
John
"JI" wrote:
> I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
> for the Lock Monitor. The database is used by a Java application that
> controls all update transactions. There are some instances where the app is
> saving data, querying data and then saving more data inside a distributed
> transaction. We are trying to remove those instances.
> But I need tips and help on how to get lower the Lock Monitor CPU usage.
> What is a average count?
>
>sql

Lock Monitor

I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
for the Lock Monitor. The database is used by a Java application that
controls all update transactions. There are some instances where the app is
saving data, querying data and then saving more data inside a distributed
transaction. We are trying to remove those instances.
But I need tips and help on how to get lower the Lock Monitor CPU usage.
What is a average count?
Hi
If you are not in control of the scope of the transaction, then you may be
limited to looking at the high duration of high volume statements and try and
tune the database to make them quick. You may also want to look at
distributing the I/O better such as adding discs to a raid system or
splitting filegroups into files over different spindles.
Use sp_locks to see lock information, check the isolation level and look for
query hints in SQL Profiler.
John
"JI" wrote:

> I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
> for the Lock Monitor. The database is used by a Java application that
> controls all update transactions. There are some instances where the app is
> saving data, querying data and then saving more data inside a distributed
> transaction. We are trying to remove those instances.
> But I need tips and help on how to get lower the Lock Monitor CPU usage.
> What is a average count?
>
>

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

lock blocks under SQLServer.Memory Manager

I'm trying to use the performance monitor on Win2K Adv Server to
troubleshoot performance issues I have on a MSSQL box.
I was trying to find out if there was a lot of blocking on my databases
creating the performance problems.
I starting tracing the counter called "lock Blocks" under "SQLServer.Memory
Manager" and found out that the average value was 4000 with picks at 8000.
I'm not too sure if I understand the exact meaning of this counter. Can
someone explain it to me and tell me if the values I'm getting are bad or
normal?
Thank youHow many rows do you get when you execute this:
select * from master..sysprocesses
where blocked > 0
I am a bit puzzled as to what the figure in lock blocks
actually means. I created a lock block and in perfmon I
got a figure of 1013. I will investigate further what this
figure means.
If you are concerned about blocking locks, then use the
above query and sp_lock to find out what is blocking. You
can also use DBCC INPUTBUFFER (SPID) to find out what was
submitted that caused the block.
Mark Allison
SQL Server MVP
>--Original Message--
>I'm trying to use the performance monitor on Win2K Adv
Server to
>troubleshoot performance issues I have on a MSSQL box.
>I was trying to find out if there was a lot of blocking
on my databases
>creating the performance problems.
>I starting tracing the counter called "lock Blocks"
under "SQLServer.Memory
>Manager" and found out that the average value was 4000
with picks at 8000.
>I'm not too sure if I understand the exact meaning of
this counter. Can
>someone explain it to me and tell me if the values I'm
getting are bad or
>normal?
>Thank you
>
>.
>|||i ran your query and I didn't get any rows back.. I guess blocking is not my
problem then...
thank you Mark
"Mark Allison" <mark@.allisonmitchell.c0m> wrote in message
news:080c01c3b427$576ab100$a001280a@.phx.gbl...
> How many rows do you get when you execute this:
> select * from master..sysprocesses
> where blocked > 0
> I am a bit puzzled as to what the figure in lock blocks
> actually means. I created a lock block and in perfmon I
> got a figure of 1013. I will investigate further what this
> figure means.
> If you are concerned about blocking locks, then use the
> above query and sp_lock to find out what is blocking. You
> can also use DBCC INPUTBUFFER (SPID) to find out what was
> submitted that caused the block.
> Mark Allison
> SQL Server MVP
> >--Original Message--
> >I'm trying to use the performance monitor on Win2K Adv
> Server to
> >troubleshoot performance issues I have on a MSSQL box.
> >I was trying to find out if there was a lot of blocking
> on my databases
> >creating the performance problems.
> >I starting tracing the counter called "lock Blocks"
> under "SQLServer.Memory
> >Manager" and found out that the average value was 4000
> with picks at 8000.
> >I'm not too sure if I understand the exact meaning of
> this counter. Can
> >someone explain it to me and tell me if the values I'm
> getting are bad or
> >normal?
> >
> >Thank you
> >
> >
> >.
> >

Friday, March 9, 2012

Local VB.NET app connection to remote SQL server

I know this is strickly not a website question, but dunno where else to post...

To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting server

I have the following code:

Dim StrSQLUNAs String ="[UN]"Dim StrSQLPWAs String ="[PW]"Dim StrServerAs String ="[IP]\[INSTANCE]"Dim StrDBAs String ="[DB]"Dim strTimeOutAs String ="Connection Timeout=0;"Dim pStrSQLConnAs String ="Server=" & StrServer &";Database=" & StrDB &";User Id=" & StrSQLUN &";Password=" & StrSQLPW &";" & strTimeOutDim sqlConnAs New SqlClient.SqlConnection(pStrSQLConn)If sqlConn.State = ConnectionState.ClosedThen sqlConn.Open()

This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.

I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?

Can they put restrictions in place for this specific sort of data access?

Does anyone have any suggestions on how to resolve this issue??

Thanks for any help

Adam.

does the new server allow external connections? A lot of hosts I've used in the past only allow you to connect to the SQL database from an Asp.Net app on their webservers.

|||

Yeah it does allow external access as I am using MSSQL Mgmt Studio.

Anyway I think I have found the cause of the problem - my Cisco router. I needed to put in IP Inspect rules in for the MSSQL. Thing that confuses me still is that I could use the Mgnt Studio and I thought it would be accessing it in the same way. Anyway - it still seems to work - albeit with random connection times (eg last night it was connecting in a second or to and now taking about 5mins!)

Anyway have experience in this area??

Thanks

Adam.

Monday, February 20, 2012

local administrator access for DBA's - is this required?

Hi
I need to manage a SQL cluster, monitor database and O.S performance and
apply database patches. Do I require local admin rights for this? If not
what is the workaround please?
Problem is, my organisation is very reluctant to grant local admin rights.
Is there a Microsoft article on this type of issue (I couldn't find one).
Thanks!
MilesHI
Use the SQL server account if you need local admin privilege on server.
Andras Jakus MCDBA
"Miles" wrote:

> Hi
> I need to manage a SQL cluster, monitor database and O.S performance and
> apply database patches. Do I require local admin rights for this? If not
> what is the workaround please?
> Problem is, my organisation is very reluctant to grant local admin rights.
> Is there a Microsoft article on this type of issue (I couldn't find one).
> Thanks!
> Miles
>

local administrator access for DBA's - is this required?

Hi
I need to manage a SQL cluster, monitor database and O.S performance and
apply database patches. Do I require local admin rights for this? If not
what is the workaround please?
Problem is, my organisation is very reluctant to grant local admin rights.
Is there a Microsoft article on this type of issue (I couldn't find one).
Thanks!
MilesHI
Use the SQL server account if you need local admin privilege on server.
Andras Jakus MCDBA
"Miles" wrote:
> Hi
> I need to manage a SQL cluster, monitor database and O.S performance and
> apply database patches. Do I require local admin rights for this? If not
> what is the workaround please?
> Problem is, my organisation is very reluctant to grant local admin rights.
> Is there a Microsoft article on this type of issue (I couldn't find one).
> Thanks!
> Miles
>

local administrator access for DBA's - is this required?

Hi
I need to manage a SQL cluster, monitor database and O.S performance and
apply database patches. Do I require local admin rights for this? If not
what is the workaround please?
Problem is, my organisation is very reluctant to grant local admin rights.
Is there a Microsoft article on this type of issue (I couldn't find one).
Thanks!
Miles
HI
Use the SQL server account if you need local admin privilege on server.
Andras Jakus MCDBA
"Miles" wrote:

> Hi
> I need to manage a SQL cluster, monitor database and O.S performance and
> apply database patches. Do I require local admin rights for this? If not
> what is the workaround please?
> Problem is, my organisation is very reluctant to grant local admin rights.
> Is there a Microsoft article on this type of issue (I couldn't find one).
> Thanks!
> Miles
>

Wednesday, March 28, 2012

Lock requests/sec

Monday, March 26, 2012

Lock Monitor

Lock Monitor

Lock Monitor

Friday, March 23, 2012

lock info

lock info

lock info

lock blocks under SQLServer.Memory Manager

Friday, March 9, 2012

Local VB.NET app connection to remote SQL server

Monday, February 20, 2012

local administrator access for DBA's - is this required?

local administrator access for DBA's - is this required?

local administrator access for DBA's - is this required?

sql local data

Blog Archive

About Me