June 23, 2003 at 10:16 am
The current locking model in SQL server 2000 is very complex and all sorts of special types of activities have to be done by DBA's and developers to avoid blocking and deadlocking situations. In any enterprise level system, generally, all reads have to be done nolock. Updates need to be done rowlock but this hint is not always taken. I have encounted situations where 2 processes doing inserts to the same table block each other due to lock escalation. Inserts should never block each other.
The whole complicated system was developed to conserve resources on the server. It's less expensive to maintain a page lock or table lock than row locks. However, in today's systems where cpu, memory and disk are cheap, the cost of the complication of the locking mechanism is not worth it. In Oracle, you almost never have lock problems since they always track to the row level. SQL server should either be upgraded to do row level locking all the time by default or offer 2 versions of the server, 1 for lower power servers where resources are a big issue and another for enterprise level systems where the overhead of dealing with complicated locking is much more expensive than the cost of powerful hardware. The lower power system could retain the current locking scheme and the enterprise version should keep track of row locks and take the burden off of the dba's and developers.
June 24, 2003 at 3:15 am
Fix: Uninstall SQL Server. Install Oracle.
(I like these easy ones)
Cheers,
- Mark
June 24, 2003 at 5:47 am
Fix: Uninstall SQL Server. Install Oracle.
(I like these easy ones)
Cheers,
- Mark
June 24, 2003 at 8:31 am
quote:
Fix: Uninstall SQL Server. Install Oracle.(I like these easy ones)
Oracle has its own set of baggage. In general, it's more expensive. It requires a lot of diddling and tinkering by DBA's to work properly. Additionally, you have the issues of snapshot too old or inadequate rollback segments. SQL server also would seem to have an advantage in .NET integration.
June 24, 2003 at 3:34 pm
Maybe your post presents a case for a new SQLServerCentral forum: "Stuff we'd like to see in future SQL Server versions". I know there's a bunch of things I wouldn't mind being included. Eg. SELECT INTO table variables; SELECT INTO EXEC ....; Easy access to the complete latest error message, not just @@error; Hiding of existence of other databases from logins without access to those databases; Customisable fixed (semi-fixed?) db roles - such as "almost_dbo" with all db owner permissions except backup db. The list goes on.
Cheers,
- Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply