Solved

slow newid

Posted on 2004-08-23
10
788 Views
Last Modified: 2008-02-01
Hello,

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?

Thanks

Mike
0
Comment
Question by:marscpa
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11877746
Any reason you're using a GUID--there is a LOT of overhead for a GUID...

When the "timeout" appears, have you ran SP_WHO in query analyzer to see if there is any locking/blocking.

Also, make sure you don't have the "auto close" option enabled for the database.
0
 
LVL 12

Expert Comment

by:ill
ID: 11878873
are you using full SQL server or MSDE?
one more to @arberts hints. also check for memory reallocation.it can cause timeouts too, when database starts.
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 11881635
Yeah this is pretty unusual!! I've never experienced it and have used guids a lot.  You might want to make sure that you have the latest service pack for your SQL Server installation.

Dave Hilditch.
0
 

Author Comment

by:marscpa
ID: 11881844
I should have included the version information:

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:marscpa
ID: 11881935
I just grabbed the settings for the db in question:

off      ANSI NULL default
off      Recursive triggers
on      Auto Update statistics
on      Torn page detection
off      Auto close
on      Auto shrink
on      Auto create statistics
off      Use quoted identifiers


Database Compatability Level 80
0
 

Author Comment

by:marscpa
ID: 11882035
arbert:

i chose a GUID because i needed a identifier (token) that is held on the client and on the server to control licence /  login access.  It just seems the right choice, although in hindsight I see it has overhead and issues.  But they are the only client with this problem.  I have this app installed at 17 other companies, so 16 other SQL boxes deal with this fine.

i should have checked for locking / blocking, I'll check it out the next time it happens.

although I'm not familiar with auto close....it does appear "auto close" is off.  just licky i guess, probably the default?

ill:

this particluar server has 4 GB of ram and SQL getts everything it wants....SQL controlled memory allocation.  Is this what you mean.  Sorry, I'm not familiar with "memory reallocation".
0
 
LVL 4

Accepted Solution

by:
davehilditch earned 500 total points
ID: 11882080
I just ran:

select newid()
from master.dbo.sysobjects
cross join master.dbo.sysobjects b

which produces 1.5 million guids - did it in 19 seconds on my box so no question of poor speed for newid().  Your problem like you said is at first request, so it sounds to me like it's not specific to newid().  When it happens again, can you try opening query analyzer and running some other query like:

select rand()

and see if it comes back just as slowly. If so it's something to do with your setup - maybe low disk space, low memory, or some other setting.

Incidentally, if you really do believe that it's newID, you could always create your NewIDs in advance.


create table NewIDs (new_id varchar(36))
insert newids
select newid() from sysobjects
cross join sysobjects b

declare @newid uniqueidentifier

update NewIDs
set @newid = new_id,
new_id = ''
from newids where new_id in (select top 1 new_id from newids where new_id <> '')

print @newid

;-)

Hope you like it!

Dave Hilditch.

0
 
LVL 12

Expert Comment

by:kselvia
ID: 11889134
Any chance there is a trigger on users table?
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now