Table with only one record

Question

Table with only one record

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

dant12 (11/8/2009)
just shooting out options here, the only one who knows his entire environment is himself so the decision is up to him, just wanted to expose more options

Absolutely. Me too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

dant12 (11/8/2009)
if your using SQL 2008 you can disable lock escalation on the problematic tables and enforce rowlocking on the table, that should solve the concurrency problem
if your using 2005 you could try implementing some sort of loop to run the updates in smaller batches in an effort to avoid table locks

Dan: Yes, this is 2005. I already tried to implement small batches for updates, they reduced number of blockings, but did not completely eliminate them.

I also tried sp_rename method. While application is accessing working table, I load another one in the background, and when it finishes I rename it with sp_rename. But unfortunately our DBA's prohibit using sp_rename in production environment.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

SQL Guy-482125 (11/9/2009)
But unfortunately our DBA's prohibit using sp_rename in production environment.

Then use a synonym and alter it to where it's pointing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)