SQL Server 2008 Timouts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL Server 2008 Timouts

Author

Topic

astralis
Yak Posting Veteran

62 Posts

Posted - 2011-10-11 : 14:34:27

I'm using SQL Server 2008 Express. It's on a Pentium D computer with 2GB of RAM. Operating system is MS 2008 R2. 64 bit. This server is only used for the SQL Server.

I've run websites on this type of system for years with few problems. But now I'm receiving more and more SQL Server Timeout errors, especially when I'm updating a record.

My largest DB is 676MB. I have about 10 DBs on the server, with none of them exceeding 200MB besides the largest one.

The memory is using 1970MB with 0 memory free. This is no doubt when I get timeout errors.

The CPU usage averages 23 percent.

Is it normal for MSSQL Server to consume 100% of the memory so that it can't even run queries?

Any advice?

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-10-11 : 18:17:39

You can set max server memory and free some memory for os and application.

Kristen
Test

22859 Posts

Posted - 2011-10-11 : 18:33:21

"Is it normal for MSSQL Server to consume 100% of the memory"

Yes

" so that it can't even run queries?"

That's not related - unless there are other applications on that server which need memory (but you said its a dedicated server).

Most likely cause is that you have a slow-running query that needs tuning, or housekeeping (in particular index rebuilds and/or updating statistics) is not being done regularly.

astralis
Yak Posting Veteran

62 Posts

Posted - 2011-10-11 : 21:24:35

Thanks for the replies.

I have not touched the indexes (or statistics -- I don't think I have those??) for ages (if ever). I looked for rebuilding the indexes but I don't find an option to do that besides deleting them and recreating them.

But I have a large table (actually a Snitz forum "replies" table) that has 160k rows while my most active table has 64k rows.

The Snitz "replies" table runs like lightning with few timeouts while the most active table often gives timeouts.

I'm not sure if this is helpful information.

Please advise.

Thanks in advance!

Sachin.Nand

2937 Posts

Posted - 2011-10-12 : 01:15:17

2GB memory for 10DB's ? Is memory so expensive ?

Anyways there are memory usage limitation for Express edition.. 4GB if I am not mistaken.

PBUH

astralis
Yak Posting Veteran

62 Posts

Posted - 2011-10-12 : 01:19:16

Sachin.Nand, I'm asking the same question you're asking.

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-12 : 01:32:05

Timeouts are usually set at client level. There are few things you can do now. Rebuild all your indexes. This will also Update statistics for all indexes. See if this improves performance.
Besides this, There are few things you can do.
- Check out queries taking highest amount of CPU and IO using sys.dm_exec_query_stats
- find which queries are taking longer to execute. query sys.dm_exec_requests during peak hours (session_id>50). Try to optimize poor performing queries.

Sachin.Nand

2937 Posts

Posted - 2011-10-12 : 01:52:37

quote:
Originally posted by astralis

Sachin.Nand, I'm asking the same question you're asking.

What I wanted to say was why are you not increasing the memory ?2 GB to less.

PBUH

astralis
Yak Posting Veteran

62 Posts

Posted - 2011-10-12 : 01:59:48

While rebuilding indexes on the larges tables, I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Rebuild failed for Index 'idcategory'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rebuild+Index&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

astralis
Yak Posting Veteran

62 Posts

Posted - 2011-10-12 : 02:07:13

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by astralis

Sachin.Nand, I'm asking the same question you're asking.

What I wanted to say was why are you not increasing the memory ?2 GB to less.

PBUH

I am not decreasing the memory to less than 2GB.

Sachin.Nand

2937 Posts

Posted - 2011-10-12 : 02:16:01

what does this return ?


SELECT 
        t1.resource_type,
        t1.resource_database_id,
        t1.resource_associated_entity_id,
        t1.request_mode,
        t1.request_session_id,
        t2.blocking_session_id
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS t2
    ON t1.lock_owner_address = t2.resource_address;

PBUH

astralis
Yak Posting Veteran

62 Posts

Posted - 2011-10-12 : 02:45:37

Sachin.Nand,

It returns 0 rows.

Sachin.Nand

2937 Posts

Posted - 2011-10-12 : 02:47:35

Did you run the query on the database where you were trying to rebuild the index ?

PBUH

Sachin.Nand

2937 Posts

Posted - 2011-10-12 : 02:48:20