Ok gurus here is somethign that I cant explane. I am haveing serious eratic responses from my SQL 7.0 Server. I can take a stored procedure and run it in a query analyzer window and it works fine, I go to rerun the same code in that window a second time and get no return set. The temp table is null. open a new window and it works fine. This code rand like clock work two weeks ago and there have been no updates to the code. I am on an old server wich has given me many hardware head aches in the past....am I having a hardware falure or is this a SQL issue?
JimRE:
I am haveing serious eratic responses from my SQL 7.0 Server. I can take a stored procedure and run it in a query analyzer window and it works fine, I go to rerun the same code in that window a second time and get no return set. The temp table is null. open a new window and it works fine. This code ran like clock work two weeks ago and there have been no updates to the code. I am on an old server wich has given me many hardware head aches in the past....
Q1 Am I having a hardware falure or is this a SQL issue? Jim
A1 From the information / background given, it may be a resource stressed sql server, sick / dying hardware, connectivity issues, possibly workstation issues, or worse, some combination of ills. It may be helpful to characterize the issue further in multiple ways, e.g.(test for the issue from other workstations, monitor with profiler, check server array and other hardware health, etc., etc., etc.).|||Originally posted by DBA
A1 From the information / background given, it may be a resource stressed sql server, sick / dying hardware, connectivity issues, possibly workstation issues, or worse, some combination of ills. It may be helpful to characterize the issue further in multiple ways, e.g.(test for the issue from other workstations, monitor with profiler, check server array and other hardware health, etc., etc., etc.).
the network admin isnt willing to test the box, he says it sql
the problem persists on multiple workstations, this is quad processer with 4 gig of ram and the resorce monitor says that the processors and mem are not near maxed out.........I think I may have some courupt indexes. I had a maintenance plan boom on me in the middle of this db and I think that hurt my indexes. Is that something that can happen?
Jim
PS Fixed the above mentioned stored procedure now just haveing horendious respons time
Jim|||RE:
the network admin isnt willing to test the box, he says it sql
My that's helpful, what if it is actually both?
RE:
Q1 the problem persists on multiple workstations, this is quad processer with 4 gig of ram and the resorce monitor says that the processors and mem are not near maxed out.........I think I may have some courupt indexes. I had a maintenance plan boom on me in the middle of this db and I think that hurt my indexes. Is that something that can happen? Jim PS Fixed the above mentioned stored procedure now just haveing horendious respons time Jim
A1 Consider running dbcc checkdb; them repair (or drop / recreate) any corrupt indicies found. Corrupting indicies would depend on the nature of "boom". Corrupt indexes may account for long response times.|||Originally posted by DBA
My that's helpful, what if it is actually both?
A1 Consider running dbcc checkdb; them repair (or drop / recreate) any corrupt indicies found. Corrupting indicies would depend on the nature of "boom". Corrupt indexes may account for long response times.
Running dbcc checkdb now...can you give me a guess to how long it will run on a 60ish gig db?
Jim|||Originally posted by JDionne
Q1 Running dbcc checkdb now...can you give me a guess to how long it will run on a 60ish gig db?
Jim
A1 Wouldn't want to hazard a guess (depends on to many variables specific to the environment such as disk access rates, how much corruption exists, number of indexes, etc., etc., etc.).
It is possible to get an estimate of how much tempdb space may be required to checkalloc and chekctables via:
dbcc checkdb With EstimateOnly
Hopefully some of this was helpful (any hardware / corruption or other issues identified)?|||Originally posted by DBA
A1 Wouldn't want to hazard a guess (depends on to many variables specific to the environment such as disk access rates, how much corruption exists, number of indexes, etc., etc., etc.).
It is possible to get an estimate of how much tempdb space may be required to checkalloc and chekctables via:
dbcc checkdb With EstimateOnly
Hopefully some of this was helpful (any hardware / corruption or other issues identified)?
as of now the checkdb showed no errors, and there supposidly is no hardware problems...we have rebuilt a few tables and indexes and It seems to be a bit better...rewriten known code a diff way and that has seemed to help a bit to. We also found a corupt data load file and we are hopeing to get that fixed soon. Thanx for your help...It has been invaluable.
Jim
Showing posts with label serious. Show all posts
Showing posts with label serious. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
Blocking issue
We are experencing some serious locking issue on sql server (sql2000). Basically a method in VB component opens a recordset which loops through a table A (about 100,000 times), inside the loop, it calls a stpred proc and does an update on table A. Each update in the loop is quite small, a few records at the most. Table A has an update trigger which insert the updated records to a history table then delete this record from table a. There are some f other queries running on table a. The big update loop on table A causes locking issues. With every update in the loop, there are hundred of locks on the delete job when it's blocking other jobs. I can't fingure out why continuous small updates opens so many locks(by looking at sp_lock) therefore blocks other process that query the same table. Any help is appreciated. thanks.Ouch. That's just asking for locking problems... Why do you this using a
cursor? Have you tried doing it in a set-based fashion?
Andrés Taylor
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.|||Tom,
Sounds like a very poor way in general to do updates and you should probably
look into redoing the task using as much set and server based approaches as
possible. But in this case I think the real issue might be that there is a
BEGIN TRAN issued (either implicitly or explicitly) at the beginning and
thus the whole operation is wrapped in one large transaction. You can use
sp_who2, sp_lock and profiler to see for sure what is going on.
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.
cursor? Have you tried doing it in a set-based fashion?
Andrés Taylor
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.|||Tom,
Sounds like a very poor way in general to do updates and you should probably
look into redoing the task using as much set and server based approaches as
possible. But in this case I think the real issue might be that there is a
BEGIN TRAN issued (either implicitly or explicitly) at the beginning and
thus the whole operation is wrapped in one large transaction. You can use
sp_who2, sp_lock and profiler to see for sure what is going on.
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.
Subscribe to:
Posts (Atom)