The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When
MS looked into how
prohibiting these actions for 7.0, they realized that using the already pres
ent lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
Showing posts with label monitor. Show all posts
Showing posts with label monitor. Show all posts
Monday, March 19, 2012
Current Activity : Locks/Object : DB Level locks.
The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZ
Hi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>
|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ
|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>
|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ
|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZ
Hi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>
|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ
|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>
|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ
|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
Current Activity : Locks/Object : DB Level locks.
The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
Saturday, February 25, 2012
Cube History
I am trying to build a cube to monitor the sales performance. My measure is the sale amount. My dimensions include 1) Time 2) Sale hierarchy (salesman, sales office, region), and 3) Product Line. The problem I have is that when the salesman move from one office to another, the sales occured before this movement should continue to be credited to the old office. Only sales happen after the movement should be credited to the new office.
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.
Any inputs will be highly appreciated!
Thanks.You will need to Consider creating another dim for salesman.
you apear to need to collect when an employee was at a particular site for a particular sale so you will have to add a location value to each ORDER and not rely on the salesman's location. it has to be tied to the sale itself so the location attribute should be derived for the order
if this is a seldom problem you may want to make your cube holap (part relational and part cube)instead of pure olap and then you can use the drill down feature for a particular sale or set of sales to find the distinction between sites.|||Thank you very much for the information. I can see how that will fix the problem. However, I am not sure how to make the drilldown work using holap as you have suggested. Could you please elaborate it a bit about your last paragraph? Thanks. Sorry, I am new in the DWH field...|||check out those topics in BOL for analysis services
i will try to reply later
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.
Any inputs will be highly appreciated!
Thanks.You will need to Consider creating another dim for salesman.
you apear to need to collect when an employee was at a particular site for a particular sale so you will have to add a location value to each ORDER and not rely on the salesman's location. it has to be tied to the sale itself so the location attribute should be derived for the order
if this is a seldom problem you may want to make your cube holap (part relational and part cube)instead of pure olap and then you can use the drill down feature for a particular sale or set of sales to find the distinction between sites.|||Thank you very much for the information. I can see how that will fix the problem. However, I am not sure how to make the drilldown work using holap as you have suggested. Could you please elaborate it a bit about your last paragraph? Thanks. Sorry, I am new in the DWH field...|||check out those topics in BOL for analysis services
i will try to reply later
Subscribe to:
Posts (Atom)