I have a portal web site that shows the state of about 20 client computers in real time.
I use SQL Server Express 10.50.4000.0 that runs on Microsoft Windows NT 5.2 (3790) server as a back end. Client computers (win 7) are connected to the network through wireless connections.
I have a table on the server with one row per client computer. Each client computer updates its own row every second with the latest data. There should be no locking issues because each machine updates its own row.
CREATE TABLE [dbo].[Table_Name](
[col1] [char](12) NOT NULL,
[col2] [varchar](20) NULL,
[col3] [int] NULL,
[col4] [varchar](20) NULL,
[col5] [int] NULL,
[col6] [int] NULL,
[col7] [int] NULL,
[col8] [char](1) NULL,
[col9] [int] NULL,
[col10] [int] NULL,
[col11] [int] NULL,
[col12] [int] NULL,
[col13] [int] NULL,
[col14] [int] NULL,
[col15] [datetime] NULL,
[col16] [int] NULL,
[col17] [int] NULL,
[col18] [int] NULL,
[col19] [int] NULL,
[col20] [int] NULL,
PRIMARY KEY CLUSTERED
The portal web site runs a select of all the rows every 2 seconds. The machines and the site run 24/7.
Everything works fine, except I am getting timeout errors: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Sometimes I get an additional A severe error occurred on the current command. The results, if any, should be discarded.
I am getting about 10-15 errors every day, at random times.
My assumption is that because the table is so small, it fits into one page, and when sql server places the lock for an update, it locks the page, and that page being the only page in the table, it locks the whole table.
Since this application is just a portal, losing a random update is not a big deal - it only means that one of the little squares on the portal web site will 'skip a beat'.
But I would really like to get rid of the timeout errors.
I know that i can an update with NOLOCK, but that I would like to know ahead of time if this is what I need to do to get rid of timeouts. On every sql forum everyone advises against the NOLOCK, so I would like to hear from an expert that this is what I need.
Could anyone please help me out.