Blocking Problems

We have a stored proc that is causing alot of blocking
problems. Basically, it selects a field based on the PK
and returns the value (0 or 1) as an output parameter:
SELECT @.Anonymous_Bool = Anonymous_Bool
FROM Member_Properties
WHERE GUID = @.GUID
The estimated execution plan shows that it does a
clustered index seek. When we tested it out it was fine,
but when it's in production, it looks like it starts
causing massive blocking problems. I don't see how the
proc itself could be a problem since it is very
efficient.
Has anyone else encountered a similar problem? could it
possibly be something in the front end code? or maybe
connection pooling? any ideas on a direction to look
would be much appreciated...Is @.Guid a UniqueIdentifier datatype? If not and the GUID column is it may
have an issue actually using the index properly. But I suspect you are
seeing the results of the connection changing the isolation level to
serializable. Or you can be seeing the effects of page splitting due to the
Guid is clustered. Check to see what is going on with profile on the
connections doing the blocking. In my opinion Guid's are a horrible PK and
especially if you cluster on them. There are few if any alternatives that
can perform worse than a Guid in a clustered index on a table with new rows
being inserted.
--
Andrew J. Kelly
SQL Server MVP
"A. Sugrue" <sugruea@.hotmail.com> wrote in message
news:058701c356ec$ec57e640$a401280a@.phx.gbl...
> We have a stored proc that is causing alot of blocking
> problems. Basically, it selects a field based on the PK
> and returns the value (0 or 1) as an output parameter:
> SELECT @.Anonymous_Bool = Anonymous_Bool
> FROM Member_Properties
> WHERE GUID = @.GUID
> The estimated execution plan shows that it does a
> clustered index seek. When we tested it out it was fine,
> but when it's in production, it looks like it starts
> causing massive blocking problems. I don't see how the
> proc itself could be a problem since it is very
> efficient.
> Has anyone else encountered a similar problem? could it
> possibly be something in the front end code? or maybe
> connection pooling? any ideas on a direction to look
> would be much appreciated...|||Yes it is a uniqueidentifier and is the PK with a
clustered index and it is also the rowguidcol for merge
replication.
>--Original Message--
>Is @.Guid a UniqueIdentifier datatype? If not and the
GUID column is it may
>have an issue actually using the index properly. But I
suspect you are
>seeing the results of the connection changing the
isolation level to
>serializable. Or you can be seeing the effects of page
splitting due to the
>Guid is clustered. Check to see what is going on with
profile on the
>connections doing the blocking. In my opinion Guid's are
a horrible PK and
>especially if you cluster on them. There are few if any
alternatives that
>can perform worse than a Guid in a clustered index on a
table with new rows
>being inserted.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> We have a stored proc that is causing alot of blocking
>> problems. Basically, it selects a field based on the PK
>> and returns the value (0 or 1) as an output parameter:
>> SELECT @.Anonymous_Bool = Anonymous_Bool
>> FROM Member_Properties
>> WHERE GUID = @.GUID
>> The estimated execution plan shows that it does a
>> clustered index seek. When we tested it out it was
fine,
>> but when it's in production, it looks like it starts
>> causing massive blocking problems. I don't see how the
>> proc itself could be a problem since it is very
>> efficient.
>> Has anyone else encountered a similar problem? could it
>> possibly be something in the front end code? or maybe
>> connection pooling? any ideas on a direction to look
>> would be much appreciated...
>
>.
>|||No, but since we already had a uniqueidentifier there as
the PK, there was no need to add another column to be the
rowguidcol.
>--Original Message--
>Having a Guid for merge replication purposes is fine but
that does not mean
>it has to be the PK.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:053f01c357ae$41b2aaa0$a301280a@.phx.gbl...
>> Yes it is a uniqueidentifier and is the PK with a
>> clustered index and it is also the rowguidcol for merge
>> replication.
>> >--Original Message--
>> >Is @.Guid a UniqueIdentifier datatype? If not and the
>> GUID column is it may
>> >have an issue actually using the index properly. But I
>> suspect you are
>> >seeing the results of the connection changing the
>> isolation level to
>> >serializable. Or you can be seeing the effects of page
>> splitting due to the
>> >Guid is clustered. Check to see what is going on with
>> profile on the
>> >connections doing the blocking. In my opinion Guid's
are
>> a horrible PK and
>> >especially if you cluster on them. There are few if
any
>> alternatives that
>> >can perform worse than a Guid in a clustered index on a
>> table with new rows
>> >being inserted.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>> >news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> >> We have a stored proc that is causing alot of
blocking
>> >> problems. Basically, it selects a field based on
the PK
>> >> and returns the value (0 or 1) as an output
parameter:
>> >>
>> >> SELECT @.Anonymous_Bool = Anonymous_Bool
>> >> FROM Member_Properties
>> >> WHERE GUID = @.GUID
>> >>
>> >> The estimated execution plan shows that it does a
>> >> clustered index seek. When we tested it out it was
>> fine,
>> >> but when it's in production, it looks like it starts
>> >> causing massive blocking problems. I don't see how
the
>> >> proc itself could be a problem since it is very
>> >> efficient.
>> >>
>> >> Has anyone else encountered a similar problem?
could it
>> >> possibly be something in the front end code? or
maybe
>> >> connection pooling? any ideas on a direction to look
>> >> would be much appreciated...
>> >
>> >
>> >.
>> >
>
>.
>

Blocking issue in DTS package running on 2005

I'm testing an existing 2000 DTS package in 2005. A Data Transformation Task selects from a view and loads a table that was just truncated. The view definition contains a subselect selecting data off the table,

View: select ... from a, (select ... from b) b2 ON ...

Table to insert into: b

There is blocking going on during the execution of the package. The select * from view (Source) and the insert bulk into the table (Destination) are blocking each other. This does not happen in 2000.

Is there a command that I can put in the view definition subselect that will allow me to just grab the data that it can without worrying about blocking? Would READCOMMITTED or READUNCOMMITED work without impact on the view? Other recommendations?

Has anyone ever run into this?

I found that when you uncheck the Table Lock box of the Data Transformation Task, that this blocking does not occur. Does anyone know why this is required in 2005 but works fine in 2000 with the box checked?

Blank vs. Space

Hello, it seems to me that T-SQL is having a hard time
distinguishing a blank from one or more spaces. When I run
the statements below, the SELECTs always give 1
as a result. This seems completely wrong to me.. or am I
missing something?
CREATE TABLE TempTable (Value VARCHAR(10))
INSERT INTO TempTable (Value) VALUES ('')
SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
using Query Analyzer on SQL Server 2000 sp3a
Thanks,
SteveSQL Server does not take in mind spaces in the right side when using operato
r
= to do the comparison.
Example:
select 1 where space(0) = space(10)
go
try:
SELECT COUNT(*) FROM TempTable WHERE datalenght(Value) = 0
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
go
AMB
"Steve Deering" wrote:

> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||This is, I believe, according to the ANSI SQL definition. You can use LIKE i
nstead, as LIKE is
sensitive to trailing spaces:
SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Trailing spaces are dropped so this is working as advertised.
Try this and you'll see what I mean.
declare @.value varchar(10)
set @.value = ' '
SELECT len(@.value)
...returns 0
This:
set @.value = ' b '
SELECT len(@.value)
...returns 2, the leading space and 'b'
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Tibor,
I think it is right except for the first one.
Example:
select 1
from
(
select space(0) union all select space(1) union all select space(2)
) as t(colA)
where colA like space(0);
AMB
"Tibor Karaszi" wrote:

> This is, I believe, according to the ANSI SQL definition. You can use LIKE
instead, as LIKE is
> sensitive to trailing spaces:
>
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Deering" <SteveREMOVE@.vin.com> wrote in message
> news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
>
>|||Indeed. Thanks Alejandro... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CCBDF7B5-B5B2-459D-9DA8-339EFC53AA59@.microsoft.com...
> Tibor,
> I think it is right except for the first one.
> Example:
> select 1
> from
> (
> select space(0) union all select space(1) union all select space(2)
> ) as t(colA)
> where colA like space(0);
>
> AMB
> "Tibor Karaszi" wrote:
>|||Huh. Apparently, it's similar for JOINs, as if the values get RTRIM'd:
USE Northwind
GO
CREATE TABLE TempTable (Value VARCHAR(10))
GO
INSERT INTO TempTable (Value) VALUES ('abc') -- Insert a ZLS
INSERT INTO TempTable (Value) VALUES ('abc ')
INSERT INTO TempTable (Value) VALUES ('abc ')
GO
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' -- ZLS
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 1 space
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 2 spaces
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' + Space(5) -- 5 spaces
GO
SELECT * FROM TempTable t inner join
(select value from TempTable where Value='abc ') t1 on t.value = t1.value
WHERE t.Value = 'abc '
DROP TABLE TempTable
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
One of the other code monkeys here posed an interesting question, though we
can't devise an example: What if in some implementation, the trailing
spaces have significant meaning, so that "bob" should not match "bob "?
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||My name isn't "bob", it's "bob ".
Do you think that you can legally change your name to add a few trailing
spaces. :-)
When is a door not a door?
When it's a "door ".
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:euQJpVGQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> One of the other code monkeys here posed an interesting question, though
> we
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob
> "?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||You can use LIKE operator in a join condition.
Example:
select
*
from
(
select 'Microsoft'
) as t1(colA)
inner join
(
select 'Microsoft' + space(1)
) as t2(colA)
on t1.colA like t2.colA
go
AMB
"Mike Labosh" wrote:

> One of the other code monkeys here posed an interesting question, though w
e
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob "
?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>

Blank parameters

Hi,

In my report, a parameter has list of values in a drop down list (3 static values - Yes, No, Both). If user selects "Both" the results should contain all values with status Yes and No. My stored proc acheives this as -

ColumnName = @.Parameter + '%' in where clause.

I created Non-Queried list of values with Both as "" (empty string). When I run the report, the report is not getting any values. The matrix is empty where as the dataset and stored procedure are picking values for the same. ("" , empty string in the status parameter)

Is there any way the RS considers the empty values ? How do I achieve this ?

Regards,

Chiro

Ok,

Let me make it clear. The stored proc has a default parameter "Status" for which if you neither give "YES" nor give "NO" it gets results for all YES and NO status. This is acheived as

1. I defined stored procedure as - CREATE PROCEDURE ProcName @.Status = '%' varchar(10), @.Param2 varchar(10).......

2. In where clause - ColumnName = @.Status + '%'

Now if you just execute query without any parameters, it gets all values for status YES as well as NO. This is working fine.

I want to display all these results in a report. I created a list of values parameter with values YES, NO, BOTH for status. I allowed Null and Blank for the parameter.

I am binding the data to a matrix. When I run the report without giving any value to status, it is not displaying results in the matrix at all. Ideally it should display all values with status YES and NO.

Can any one tell me if there is any thing which I am missing here.

bit data type

I run a simple select statement to a table that selects
data. One of the column's data type is "bit" size "1" set
to default to ((-1)).
Here is the problem, when I run a select statement in
query analyzer, all data from that column is "1". When I
run the same query and use DTS to export the data to a
text file, all the data from that column is "true". What
does that mean and how can I get the data to just be "1"?
Please help.
Thanks. John.The valid values of a BIT column are 0, 1 and NULL, so there's no good
reason to default the column to -1. The -1 will be implicitly cast to 1,
which is one of the peculiarities of the BIT datatype.
DTS assumes that you want BIT columns to be treated as Boolean values and
chooses to export them as the strings "True" and "False". You can avoid this
by casting the value as an INTEGER in your transformation. Alternatively,
choose another numeric type for the column instead of BIT.
--
David Portas
SQL Server MVP
--|||Thank you very much. That makes sense.
John
>--Original Message--
>The valid values of a BIT column are 0, 1 and NULL, so
there's no good
>reason to default the column to -1. The -1 will be
implicitly cast to 1,
>which is one of the peculiarities of the BIT datatype.
>DTS assumes that you want BIT columns to be treated as
Boolean values and
>chooses to export them as the strings "True" and "False".
You can avoid this
>by casting the value as an INTEGER in your
transformation. Alternatively,
>choose another numeric type for the column instead of BIT.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||Hi
VB: -1 = True
All other languages: +1 = True.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:18ba01c47be5$ba2b8cc0$a601280a@.phx.gbl...
> Thank you very much. That makes sense.
> John
>
> >--Original Message--
> >The valid values of a BIT column are 0, 1 and NULL, so
> there's no good
> >reason to default the column to -1. The -1 will be
> implicitly cast to 1,
> >which is one of the peculiarities of the BIT datatype.
> >
> >DTS assumes that you want BIT columns to be treated as
> Boolean values and
> >chooses to export them as the strings "True" and "False".
> You can avoid this
> >by casting the value as an INTEGER in your
> transformation. Alternatively,
> >choose another numeric type for the column instead of BIT.
> >
> >--
> >David Portas
> >SQL Server MVP
> >--
> >
> >
> >.
> >