Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?
It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

location coordinates query

Hi All,

I've been struggling with how best to define a query to get a list of unique
coordinates from a database table and my SQL skills are not good enough to
work out a good solution. The basic table structure is as follows:

CREATE TABLE [Readings Data Table] (
CY DOUBLE,
PY DOUBLE,
C1X DOUBLE, C2X DOUBLE,
P1X DOUBLE, P2X DOUBLE)

Each record in the table represents a geophysical measurement for a sensors
at the four locations (P1X,PY), (P2X,PY), (C1X,CY) and (C2X,CY). There can
be many repeat readings (ie with the same locations) and many overlapped
readings (ie which share at least one sensor location) and an example set of
data is:

CY PY C1X C2X P1X P2X
0 0 0 100 200 300
0 0 0 100 200 300
0 0 0 100 300 400
0 0 0 100 400 500
0 0 100 200 300 400
0 0 100 200 500 600
100 0 0 100 200 300

The distinct locations for this set are:

(0,0), (100,0), (200,0), (300,0), (400,0) (500,0), (600,0), (0,100),
(100,100), (200,100) and (300,100) ie there are 11 unique locations out of
the 28 (=7*4) sensor measurement locations.

My problem is to create a table with a list of just the distinct sensor
locations. I have implemented a brute-force method where I build a table by
looping through each of the four coordinate combinations to create all
possible locations and then getting just the distinct locations, but this is
quite slow with even just a moderate number of readings. Can anyone suggest
a more efficient way to get the distinct sensor locations using SQL?

Many thanks
John Paine[posted and mailed, please reply in news]

John Paine (jpaine@.bigpond.net.au) writes:
> I've been struggling with how best to define a query to get a list of
> unique coordinates from a database table and my SQL skills are not good
> enough to work out a good solution. The basic table structure is as
> follows:
> CREATE TABLE [Readings Data Table] (
> CY DOUBLE,
> PY DOUBLE,
> C1X DOUBLE, C2X DOUBLE,
> P1X DOUBLE, P2X DOUBLE)
> Each record in the table represents a geophysical measurement for a
> sensors at the four locations (P1X,PY), (P2X,PY), (C1X,CY) and (C2X,CY).
> There can be many repeat readings (ie with the same locations) and many
> overlapped readings (ie which share at least one sensor location) and an
> example set of data is:

In general, for this type of queries it is a good advice to post:

o CREATE TABLE statement for the involved table(s). (Which you did,
thank you.)
o INSERT statements with sample data.
o The desired output given the sample data. (Which you did, thanks!)

This makes it easy for anyone who takes a stab at your problem to post
a tested solution, because it's easy to cut and paste into Query
Analyzer.

Since there is no INSERT statements for the data, this is an untested
solution:

SELECT P1X, PY FROM [Readings Data Table}
UNION
SELECT P2X, PY FROM [Readings Data Table}
UNION
SELECT C1X, CY FROM [Readings Data Table}
UNION
SELECT C2X, CY FROM [Readings Data Table}

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95907781F7757Yazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> John Paine (jpaine@.bigpond.net.au) writes:
> > I've been struggling with how best to define a query to get a list of
> > unique coordinates from a database table and my SQL skills are not good
> > enough to work out a good solution. The basic table structure is as
> > follows:
> > CREATE TABLE [Readings Data Table] (
> > CY DOUBLE,
> > PY DOUBLE,
> > C1X DOUBLE, C2X DOUBLE,
> > P1X DOUBLE, P2X DOUBLE)
> > Each record in the table represents a geophysical measurement for a
> > sensors at the four locations (P1X,PY), (P2X,PY), (C1X,CY) and (C2X,CY).
> > There can be many repeat readings (ie with the same locations) and many
> > overlapped readings (ie which share at least one sensor location) and an
> > example set of data is:
> In general, for this type of queries it is a good advice to post:
> o CREATE TABLE statement for the involved table(s). (Which you did,
> thank you.)
> o INSERT statements with sample data.
> o The desired output given the sample data. (Which you did, thanks!)
> This makes it easy for anyone who takes a stab at your problem to post
> a tested solution, because it's easy to cut and paste into Query
> Analyzer.
> Since there is no INSERT statements for the data, this is an untested
> solution:
> SELECT P1X, PY FROM [Readings Data Table}
> UNION
> SELECT P2X, PY FROM [Readings Data Table}
> UNION
> SELECT C1X, CY FROM [Readings Data Table}
> UNION
> SELECT C2X, CY FROM [Readings Data Table}
>
Hi Erland,

Thanks for the tips on posting to this group. I haven't used newsgroups much
in the past, so I'll keep them in mind next time I post here.

Thanks especially for the answer as it has helped speed the process
significantly. In a small sample database with 4028 readings, there are 528
unique locations and the change from brute-force to the use of the UNION
code produced a speed-up of about 4 times. Which is enough to eliminate an
annoying wait of 20 seconds when recalculating the location coordinates.

John Paine