GRANT statements issue Sch-M locks....Why?

We are experiencing blocking situations with a application that issues GRANT
statements. It becomes blocked trying to take out a Sch-M lock while other
long running report processes are holding Sch-S locks.
I'm wondering:
1) Why is the non-DDL GRANT statement wanting to lock the schema?
2) Is there any way to prevent it, or to prevent the reporting processes
from taking out Sch-S locks?
Thanks!1) Because GRANT _is_ DDL.
2) You can run the reporting processes WITH(NOLOCK), but that might cause
them to fail occasionally.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
> We are experiencing blocking situations with a application that issues
> GRANT
> statements. It becomes blocked trying to take out a Sch-M lock while
> other
> long running report processes are holding Sch-S locks.
> I'm wondering:
> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
> 2) Is there any way to prevent it, or to prevent the reporting processes
> from taking out Sch-S locks?
> Thanks!|||Hi
Isn't GRANT DCL ?
"Jacco Schalkwijk" wrote:

> 1) Because GRANT _is_ DDL.
> 2) You can run the reporting processes WITH(NOLOCK), but that might cause
> them to fail occasionally.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
>
>|||What if someone tries to drop the object while you are in the process of
GRANT'ing ?

> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
Gopi
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
> We are experiencing blocking situations with a application that issues
> GRANT
> statements. It becomes blocked trying to take out a Sch-M lock while
> other
> long running report processes are holding Sch-S locks.
> I'm wondering:
> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
> 2) Is there any way to prevent it, or to prevent the reporting processes
> from taking out Sch-S locks?
> Thanks!|||Yes, good question. I could understand it needing to take a Sch-S to protec
t
the object while granting, but I don't see how it is a schema MODIFICATION t
o
grant permissions.
"gopi" wrote:

> What if someone tries to drop the object while you are in the process of
> GRANT'ing ?

Good SQL books for (SQL-server, oracle, DB2, Postgress, MySQL)

Hello All,

Anyone could suggest good books for writing SQL?
(and also optimise the performance of SQL statements).

I am planning to buy 2 or 3 SQL related books. I know the basic SQL (e.g. writing join, left join, nested join). I have been using SQL for 3 months. I am working as a data analyst/ data mining consultant. So I am NOT looking the DBA parts or the programming parts. Just writing adhoc SQL queries to extract data for data mining purpose. The queries have to be optimised and fine tuned.

Any advise would be very much appreciated.

Thanksif you are trying to cover the bases then SQL in a nutshell from O'Reilly is quite usefull. although it has dated very quicky as new verions have come out.

The version I have covers SQL Server, MySQL, Oracle & PostgreSQL.

As to to fine tuning tuning your weaponf fo choice should have suitable tools such as explain etc...|||Thanks, I bought this just now from amazon. Yes, one of them is SQL in a nutshell. Hopefully they are good choices.

-----------------
1 of: SQL Tuning [Paperback]
By: Dan Tow
$26.37


1 of: Data Modeling Essentials, Third Edition (Morgan Kaufmann Series in Data Management Systems) (The Morgan Kaufmann Series in Data Management Systems) [Paperback]
By: Graeme Simsion, Graham Witt
$35.93


1 of: SQL Performance Tuning [Paperback]
By: Peter Gulutzan, Trudy Pelzer
$28.49
-

1 of: SQL Pocket Guide [Paperback]
By: Jonathan Gennick
$9.95


1 of: SQL In A Nutshell, 2nd Edition [Paperback]
By: Kevin Kline, et al
$29.67

GO Statement

Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
Michael
Michael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:

> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>

GO Statement

Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:

> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>

GO Statement

Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>