Solved

slow newid

Posted on 2004-08-23
10
819 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 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