Can AUTO UPDATE STATISTICS hold an X-lock?

We are experiencing a non-deterministic (ND) problem. It is happening
regularly, but not consistently. We run a suite of tests for performance
evaluation. Some of the tests insert data into empty tables, and later these
tables are read. Each time we are able to reproduce the hang, it is because
one of the connections is shown as holding an X lock as described below on
one of the tables. The process holding the lock will always be shown as
sleeping by the SQL Server Activity Monitor. At the point that the hang
occurs, we will only be using a single connection to the database. We use a
connection pool, so there will be some connections shown as coming from the
JDBC driver (us) and sleeping. This is because of our connection pool.
However, there should be no outstanding active work for any of those
connections because we always end the transaction, either by commit or
rollback, before getting a new connection or at the end of an individual test
case.
My guess is that the X lock is being acquired by the autostats feature when
SQL Server is updating the statistics on a table during a SQL statement
because 1 of the thresholds (inserts, updates) has been exceeded and that the
lock is not being released as it should be. I am confident that we are not
explicitly updating the statistics on any table through our application until
well after this X lock becomes visible in the SQL Server Activity Monitor.
We are running of SQL Server 2005 SP1, and we experience the problem with
both the 1.0 and 1.1 CTP versions of the Microsoft JDBC driver. The database
has the default settings of AUTO_UPDATE_STATISTICS ON, and
AUTO_CREATE_STATISTICS ON, but AUTO_UPDATE_STATISTICS_ASYNC OFF.
Blocking process is holding an X lock (Type=Object, Subtype=UPDSTATS, Object
ID = 1157579163 â' cc_typekeydatadist, Description = NULL, Owner Type = TRANSACTION, Object = (internal))
Successfully processed 0 ops in 468.0069999694824 secs (running avg 0
ops/secs; last interval avg 0 ops/secs) Detailed Status:
Number of threads: 1, total number of tables: 634, number of tables
processed: 421, total number of commands: 846, number of commands processed:
492, number of command errors: 0
Index name synch started at: Mon Jul 31 17:00:58 PDT 2006, ended at: Mon Jul
31 17:00:58 PDT 2006, duration: 0 seconds
Thread #0: Active: true, number of tables processed: 421, number of commands
processed: 492, number of command errors: 0,
start time: Mon Jul 31 17:00:58 PDT 2006,
start command time: Mon Jul 31 17:01:09 PDT 2006, current command: UPDATE
STATISTICS cc_typekeydatadist WITH FULLSCAN
Successfully processed 0 ops in 478.021999835968 secs (running avg 0
ops/secs; last interval avg 0 ops/secs) Detailed Status:
Number of threads: 1, total number of tables: 634, number of tables
processed: 421, total number of commands: 846, number of commands processed:
492, number of command errors: 0
Index name synch started at: Mon Jul 31 17:00:58 PDT 2006, ended at: Mon Jul
31 17:00:58 PDT 2006, duration: 0 seconds
Thread #0: Active: true, number of tables processed: 421, number of commands
processed: 492, number of command errors: 0,
start time: Mon Jul 31 17:00:58 PDT 2006,
start command time: Mon Jul 31 17:01:09 PDT 2006, current command: UPDATE
STATISTICS cc_typekeydatadist WITH FULLSCAN
We are going to try running with all auto-stats options off, and with the
aync option on, to see if that works around the problem.
We do not issue a commit after a read-only operation, because we do not
expect to be holding any locks, but that we do return the connection to the
connection pool. Does the UPDATE STATISTICS operation occur in the same
transaction, and thus could be holding a lock unless we are running in
autocommit mode or do an explicit commit? Or does it occur on a separate,
server-initiated thread?
I've posted the same question on the Connect Feedback area."Wes Clark" <WesClark@.discussions.microsoft.com> wrote in message
news:85E8BB89-22E7-427A-B263-4155783F125A@.microsoft.com...
> We are experiencing a non-deterministic (ND) problem. It is happening
> regularly, but not consistently. We run a suite of tests for performance
. . .
> We do not issue a commit after a read-only operation, because we do not
> expect to be holding any locks, but that we do return the connection to
> the
> connection pool. Does the UPDATE STATISTICS operation occur in the same
> transaction, and thus could be holding a lock unless we are running in
> autocommit mode or do an explicit commit? Or does it occur on a separate,
> server-initiated thread?
>
In a simple test I did, I did not see any locks enlisted in a transaction
from automatically created statistics, but I suppose it's possible.
In any case, leaving transactions open when you return connections to the
pool is a dangerous and unusual thing to to. As you have discovered, you
have to be 100% sure you have not created any locks, and if one slips in,
you've got a big problem. Why not just commit? If you haven't written any
log records it doesn't really cost anything.
David|||Your suggestion sounds reasonable, and we shall consider it.
Do you know the answer to my questions: Does the UPDATE STATISTICS
operation occur in the same transaction, and thus could be holding a lock
unless we are running in autocommit mode or do an explicit commit? Or does it
occur on a separate, server-initiated thread?
"David Browne" wrote:
> "Wes Clark" <WesClark@.discussions.microsoft.com> wrote in message
> news:85E8BB89-22E7-427A-B263-4155783F125A@.microsoft.com...
> > We are experiencing a non-deterministic (ND) problem. It is happening
> > regularly, but not consistently. We run a suite of tests for performance
> .. . .
> > We do not issue a commit after a read-only operation, because we do not
> > expect to be holding any locks, but that we do return the connection to
> > the
> > connection pool. Does the UPDATE STATISTICS operation occur in the same
> > transaction, and thus could be holding a lock unless we are running in
> > autocommit mode or do an explicit commit? Or does it occur on a separate,
> > server-initiated thread?
> >
>
> In a simple test I did, I did not see any locks enlisted in a transaction
> from automatically created statistics, but I suppose it's possible.
> In any case, leaving transactions open when you return connections to the
> pool is a dangerous and unusual thing to to. As you have discovered, you
> have to be 100% sure you have not created any locks, and if one slips in,
> you've got a big problem. Why not just commit? If you haven't written any
> log records it doesn't really cost anything.
> David
>
>

Can anyone help with with datatype float

I have some engineering data in my table and the db designer is representing it with a float datatype. Here's what is happening. If I query on a record based on id num and get a row and put it in text boxes in my Windows App, min_riv_hd_dia (the float) is 0.026<14 zeroes>2. If I try to query and get that same record again but this time based on id num and min_riv_hd_dia equal to 0.026<14 zeroes>2, I get no row found. If I just do a select on this row based on id number, sql server displays it as 0.026. But if I query with 0.026 as my value, still the row is not found. If I query min_riv_hd_dia > 0.026, the record is found.

So my question is, how can I tell the exact value that must be input in my search criteria in order to find this row?

Thank you so much if you can help!well, you have discovered that float is not a percise data type and is only appropriate for some scientific and engineering calculations. Switch to decimal with the correct percision or scale or do not search on the float field.|||So my question is, how can I tell the exact value that must be input in my search criteria in order to find this row?That's the crux of it - it is not an exact value.

A couple of interesting links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71391
http://docs.sun.com/source/806-3568/ncg_goldberg.html|||Thank you both.

I like Thrasymachus' suggestion...do not search on the float field.|||If you have to compare floating-point values. Check that the difference between them is small compared to the size of the numbers, rather than look for exact equality (i.e. use rounding).

where round(min_riv_hd_dia,10)=0.026

float is an approximate numeric datatype and should not be used where absolute precision is required. This type is useful for applications that need large numbers but do not need precise accuracy. If you require very accurate numbers, use the decimal data type (e.g. in financial applications).

For more info see
What Every Computer Scientist Should Know About Floating-Point Arithmetic (http://docs.sun.com/source/806-3568/ncg_goldberg.html)

Also interesting to note is that floating point arithmetic is used in excel and many combinations of arithmetic operations may produce results that appear to be incorrect by very small amounts.
see http://support.microsoft.com/kb/214118/