GilaMonster (3/25/2013)

---

Probably because SQL is taking row locks on every single...

GilaMonster (3/25/2013)

---

This is seriously exactly what you have in your application, a select with a...

Table definition

USE [desarrollo]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[reque_pa](

[CEREQ] [decimal](4, 0) NOT NULL,

[CENTR] [decimal](4, 0) NOT NULL,

[SUCUR] [decimal](4, 0) NOT NULL,

[EMPRE] [decimal](4, 0) NOT NULL,

[NOMBR] [char](45) NOT NULL,

[TINFO]...

I have a parameter table to number invoices for example, with around 20 records, this...

Lock escalation is when SQL takes row or page locks to start and part way through running the query converts those to table locks because it's taking too many locks...

Look at this image,

connection 57 has this query

update reque_pa with (updlock) set MINIMO = 2 WHERE CEREQ = 1

connection 58 has this query

SELECT * FROM Reque_pa WHERE...

GilaMonster (3/25/2013)

---

You can. So you'd prefer SQL to run out of...

McSQL (3/25/2013)

---

You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;

ALTER INDEX...

Thanks McSQL

Will read the thread!

I try (with updlock) but dont work in my case, cause transaction 1 has a page lock that prevents transation 2 to takes a row

thanks TheSqlGuru for your reply.

We dont use auto update. And is hard to find a "slow/off time" .. you know. We are thinking the best way to schedule to run...

thanks all for help me.!!

Yesterday we test UPDATE STATISTICS in our dev server (almost size of production). Now we are planing the best way to do in our production server.

Thanks...

thanks !!!

in addition, we never see in logs an error about index damage,

we will try scheduling statics,

btw, how often is recomended statics update?

Thanks for all your replies, we decided to reinstall and upgrade to windows server 2008 R2 and make some configures changes on the storage.

thanks a lot

Thanks,

We tested the server in many aspects, and find bottlenecks with disk. If we put less memory than 16Gb the performance decrease.

However recreating a 24 million row table with...

Thanks a lot for the example! we will use it that way if there no other solution.