Running SQL query - getting timeout

SELECT TOP 1 * 
FROM URLForPosting WITH(nolock) 
WHERE stat开发者_运维技巧us = 0 
ORDER BY newid()

This is the query when I run in code I get timeout, even when I run it at SQL Server I get timeout.

However when I do this

SELECT TOP 1 * 
FROM URLForPosting WITH(nolock) 
WHERE status = 0 

It runs perfectly fine.

Also the first query was running fine until the records for the first 6 lacks records its has total 8 lack now it gives time out? I have created index on status.

Any suggestions?

An alternative way to return a random record is to use TABLESAMPLE. See how this performs:

SELECT TOP 1 * 
FROM URLForPosting TABLESAMPLE(1) WITH(nolock) 
WHERE status=0 
ORDER BY newid()

TABLESAMPLE is available in SQL Server 2005 and later versions.

Since it was running fine for when you had 600K records and is hanging at 800K it is likely that the inserts have caused your indexes to become fragmented. Get your DBA (or someone with db_owner rights) to run DBCC DBREINDEX(URLForPosting).

Or for versions 2005 upwards you can use ALTER INDEX REBUID.