sql bit data type: Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--070605070403080308080508
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:
>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
--070605070403080308080508
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A SELECT statement will hold a shared (S) lock on the specific
resource (generally a KEY or RID) and an intent shared (IS) lock on the
escalated resources (usually an IS(PAG) and IS(TAB)).Â The data
browsing window in SQLEM will hold locks open for big tables while
you're scrolling around through the data (just like MSAccess).Â If
SQLEM happens to have a shared lock on a page (PAG), which is entirely
possibly, and some other process requests an intent exclusive (IX) lock
on that same page (for example it wants to delete a row on the page, so
it will acquire an X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then
the IX(PAG) will be blocked by the S(PAG), because they're incompatible
lock types on the same resource, until the S)PAG) is release. 
 
See BOL for more into on lock compatibility:
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>) ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>) 
 
The important thing to remember is that SQLEM is just another client
app running SELECT queries and issuing shared lock requests, sometimes
on pages of data at a time, and often doesn't release those locks (if
it's a bit enough table and you haven't reached the last page of it
yet) until you close the child window displaying that data (that issued
the SELECT statement). 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Mangesh Deshpande wrote:
<blockquote cite="mid5515F106-3E87-4255-9DD6-7CAA798E2526@.microsoft.com"
type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
</body>
</html>
--070605070403080308080508--|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have one user who uses SQL EM and run queries and modifies data.
> > We see that when the main blocking SPID is SQLEM and command is
> > "select"
> >
> > My question is
> > 1. How can select command from EM be a blocking spid.
> > 2. How can you cause blocling using EM?
> >
> > Please explain.
> >
> > Mangesh
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||This is a multi-part message in MIME format.
--060809080506050602090205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>>Mangesh Deshpande wrote:
>>
>>Hi
>>I have one user who uses SQL EM and run queries and modifies data.
>>We see that when the main blocking SPID is SQLEM and command is
>>"select"
>>My question is
>>1. How can select command from EM be a blocking spid.
>>2. How can you cause blocling using EM?
>>Please explain.
>>Mangesh
>>
>>I can't stress this enough when I say that you should never use SQL EM
>>for editing data, unless in development or off-hours.
>>SQL EM uses a server-side, firehose cursor which is good, but does not
>>fetch all data immediately (which is bad), like Query Analyzer or any
>>well-written application might. The benefit is that a large table can be
>>queried and viewed quickly, but most of the rows are sitting on the
>>server in the result set waiting for the user to scroll though the
>>results (at which point they are fetched). Until then, shared locks
>>remain on some pages on the table.
>>To avoid this, immediately issue a CTRL+END to move the end of the
>>results. That forces SQL EM to fetch all data, thereby releasing the
>>locks. If it's a large table, however, this process could be time
>>consuming.
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--060809080506050602090205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A firehose cursor is essentially a forward-only, read-only cursor.
From BOL: 
</tt>
<blockquote><a>Rows are sent to the client in the order they are placed
in the result set, and the application must process the rows in this
order. After executing an SQL statement on a connection, the
application cannot do anything on the connection other than retrieve
the rows in the result set until all the rows have been retrieved. The
only other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client. 
</a></blockquote>
<tt>See
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Hassan wrote:
<blockquote cite="midujrJPozZFHA.2420@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">What is a firehose cursor ?
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl">news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Mangesh Deshpande wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is
"select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
<pre wrap="">I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060809080506050602090205--

Sunday, March 11, 2012

Blocking issue in sql2000

No comments:

Post a Comment

sql bit data type

Blog Archive

About Me