July 10, 2003 at 2:59 pm
I have looked high and low for definitions for the spinlock_name(s). In fact, I seem to be having trouble finding information on any of the SQL Server's internal's.
Is anyone aware of documentation and/or books on the subject?
Mark
Harder
Database Administrator
Technology Solutions Team - Information Services Corporation
Regina, SK, Canada
Phone: (306) 787-4546 Fax: (306) 787-4617
Mark Harder
IBM Canada
Regina, SK
mharder@ca.ibm.com
July 10, 2003 at 3:32 pm
See other people's discussion at http://dbforums.com/arch/70/2002/11/559754
July 11, 2003 at 1:30 am
Hi Mark (et all)
Currently I'm preparing a talk for a (the) DB Forum in Denmark partly about this subject.
As an Oracle performance guy I was challenged to get comparable data from SQLServer as Oracle can give you about this subject. Or actually about waiting for resources in general..
Indeed it's sort of impossible to find info on this. The best info I've found is by disassemble sqlservr.exe (using windbg) and do your own digging. Since SQL comes with debug symbols, it almost reads like a book (almost..) By relating the function names that call the spinprocedure, you can get an idea of what's going on (sort of) The call that does the spinning and accounting in the table exposed by SQLPERF, is called 'SpinlockBase::SpinToAcquire'. Before this is called there is at least one try to get the lock, so in >99.99% of the cases (on a healty system) no accouting is done to get the lock. Otherwise the procedure is called, you can see the spinlock# being pushed on the stack before the call. This # relates to the index in the SQLPERF table (index-1).
There's a lot of 'spinlocking' going on in the code. In some (important) cases you will notice that there's no counting of spinlocks at all, it just loops and retries to get the lock.
One of the first things that happen when entering the lockprocedure is incrementing the 'collisions' column (as seen in sqlperf)
Spinlocks are taken by doing a 'locked compare-exchange' instruction: which locks the memory access to the lock location and tests to see if the lock is not taken. If not, the take it (by writing some non-0 data in it, mostly the NT trhead_id). If it is taken, then the 'standard' code will loop 10000*10000 times (since SQL2000 spincount is not an option anymore but hardcoded appearently) In SingleCPU mode it doesn't spin, it sleeps for 1ms, and then retries. (it doesn't make sense to spin because that eats the CPU and the proc holding the lock cannot release it when you are spinning)
If the lock is finally acquired, the number of loops it took is added to the 'Spins' column.
If it doesn't get it after 10000*10000 loops, it will sleep for 5 seconds and you will get a warning message in the errorlog.
Note that SQL doesn't have a sofisticated implemention like Oracle does, and it's certainly hard to say something useful about the performance impact of this all: there is no timing information!
So I build my own, by instrumenting the sqlserver code. I tested this on CPU bound 8-way benchmarks (where an ERP application runs on the same box as the DB), unfortunately I was not very thrilled by the results, there was some spinning going on but I only lost about 1200 ms doing spinning in a 1 hour benchmark..
I love to have some feedback of you guys out in the real world on spinlock numbers. Since I'm an Oracle(Unix) guy so I don't see too much live SQLServer systems..
I hope will help you get get on speed..
Mario
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply