...
Slow
...
processing
...
and
...
locking
...
situations
...
Especially
...
when
...
using
...
multiple
...
connections
...
e.g.
...
by
...
different
...
in/output
...
steps
...
to
...
the
...
same
...
database
...
we
...
often
...
got
...
reports
...
about
...
slow
...
processing
...
and
...
even
...
locking
...
situations.
...
User
...
reported
...
performance
...
improved
...
significantly
...
by
...
disabling
...
the
...
connection
...
pooling.
...
(tested
...
with
...
MS
...
SQL
...
Server
...
2000)
...
Another
...
issue
...
is
...
found
...
together
...
with
...
the
...
Update
...
step,
...
it
...
has
...
two
...
statements:
...
one
...
for
...
looking
...
up
...
the
...
data
...
and
...
one
...
for
...
the
...
update.
...
This
...
can
...
lead
...
to
...
deadlock
...
situations
...
even
...
when
...
we
...
use
...
one
...
(since
...
version
...
3.x)
...
or
...
two
...
connections.
...
User
...
experienced
...
deadlocks
...
after
...
creating
...
indexes,
...
therefor
...
I
...
suspect
...
the
...
creation
...
/
...
updating
...
of
...
the
...
index
...
locks
...
the
...
table
...
for
...
a
...
certain
...
time.
...
I
...
looked
...
into
...
the
...
MS
...
SQL
...
documentation
...
and
...
found
...
the
...
reasons
...
for
...
this:
...
Fill
...
factor:
...
http://technet.microsoft.com/en-us/library/ms177459.aspx
...
especialy:
...
"Page
...
Splits
...
and
...
Performance
...
Considerations
...
When
...
a
...
new
...
row
...
is
...
added
...
to
...
a
...
full
...
index
...
page,
...
the
...
Database
...
Engine
...
moves
...
approximately
...
half
...
the
...
rows
...
to
...
a
...
new
...
page
...
to
...
make
...
room
...
for
...
the
...
new
...
row.
...
This
...
reorganization
...
is
...
known
...
as
...
a
...
page
...
split.
...
A
...
page
...
split
...
makes
...
room
...
for
...
new
...
records,
...
but
...
can
...
take
...
time
...
to
...
perform
...
and
...
is
...
a
...
resource
...
intensive
...
operation.
...
Also,
...
it
...
can
...
cause
...
fragmentation
...
that
...
causes
...
increased
...
I/O
...
operations.
...
A
...
correctly
...
chosen
...
fill
...
factor
...
value
...
can
...
reduce
...
the
...
potential
...
for
...
page
...
splits
...
by
...
providing
...
enough
...
space
...
for
...
index
...
expansion
...
as
...
data
...
is
...
added
...
to
...
the
...
underlying
...
table."
...
Also
...
see
...
"Creating
...
indexes"
...
on
...
http://technet.microsoft.com/en-us/library/ms190197.aspx:
...
"Performance
...
considerations:
...
[...
...
]
...
Creating
...
the
...
index
...
offline
...
or
...
online.
...
When
...
an
...
index
...
is
...
created
...
offline
...
(the
...
default),
...
exclusive
...
locks
...
are
...
held
...
on
...
the
...
underlying
...
table
...
until
...
the
...
transaction
...
creating
...
the
...
index
...
has
...
completed.
...
The
...
table
...
is
...
inaccessible
...
to
...
users
...
while
...
the
...
index
...
is
...
being
...
created."
...
-->
...
Due
...
to
...
these
...
two
...
informations
...
I
...
propose
...
to
...
try
...
to
...
use
...
an
...
online
...
index
...
first
...
and/or
...
change
...
the
...
fill
...
factor.
...
The
...
syntax
...
is
...
described
...
over
...
here:
...
http://technet.microsoft.com/en-us/library/ms188783.aspx
...
Options
...
for
...
creating
...
an
...
index
...
are:
| Code Block |
|---|
PAD_INDEX = { ON \| OFF }
\| FILLFACTOR = fillfactor
\| SORT_IN_TEMPDB = { ON \| OFF }
\| IGNORE_DUP_KEY = { ON \| OFF }
\| STATISTICS_NORECOMPUTE = { ON \| OFF }
\| DROP_EXISTING = { ON \| OFF }
\| ONLINE = { ON \| OFF }
\| ALLOW_ROW_LOCKS = { ON \| OFF }
\| ALLOW_PAGE_LOCKS = { ON \| OFF }
\| MAXDOP = max_degree_of_parallelism
|
We
...
look
...
forward
...
to
...
your
...
experiences
...
and
...
comments.