I have written an application that keeps track of users logged in. Eash user is assigned a id (GUID type) via a stored procedure which uses the NEWID function. Once a day or so now my application does not allow anyone to login, the reason is a timeout error on the following code:
set nocount on
declare @id as uniqueidentifier
set @id = newid()
insert into users (userGuid) values (@id)
It is that simple. The reason for the timeout is that my code would like to see a return of the above within 30 seconds, when the login problem occurs the above can not finish within 30 seconds. To fix it, I open query analyzer and run "SELECT NEWID()". That takes the server about 40+ seconds the first time, the next time it returns in less than a second. Once it runs (the 40+ second time) users can then login again.
I'll correct my app so that it waits and does not time out, but does anyone know why SQL server is taking so long to generate the GUID at certain times?