SpinLocks
-
Hello,
Does anyone know what the following Spinlocks stand for?
name collisions spinsspins_per_collision sleep_timebackoffs
ALLOC_CACHES_HASH426181802324947377588762.4619 25544 154546374
SOS_CACHESTORE_CLOCK 13071 5429436964415380.4 0 17156
SOS_OBJECT_STORE 6559674 1912562500291.5637 294 204684
X_PACKET_LIST 2131448 665817250312.3779 64 88850
BLOCKER_ENUM 2039437 577101320282.9709 109 41931
CPU usage was high, on investigating that has lead me to Spinlocks and I can't find any info on the above. Any help welcome.
Thanking you in anticipation.
-
some may or may not be documented. I would do a web search on paul randal sql server spinlock and go from there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
kkanaya (2/8/2016)
Hello,Does anyone know what the following Spinlocks stand for?
name collisions spinsspins_per_collision sleep_timebackoffs
ALLOC_CACHES_HASH426181802324947377588762.4619 25544 154546374
SOS_CACHESTORE_CLOCK 13071 5429436964415380.4 0 17156
SOS_OBJECT_STORE 6559674 1912562500291.5637 294 204684
X_PACKET_LIST 2131448 665817250312.3779 64 88850
BLOCKER_ENUM 2039437 577101320282.9709 109 41931
CPU usage was high, on investigating that has lead me to Spinlocks and I can't find any info on the above. Any help welcome.
Thanking you in anticipation.
Read the article at the first link by Paul Randal that will pop up for the search that Kevin provided. You can skip over all the stuff to try to find out what the cause of the SpinLocks are and what the worst SpinLock is. Read the parts about the possible cause and some of the things you can do to resolve the matter.
To summarize...
Keep your transactions (implicit or explicit) short and effective. IMHO and personal experience, 99.9% of all contention and high CPU usage problems are caused by poorly written code, missing or poorly designed indexes, poorly designed tables, mistakes made by an ORM, and out of date statistics. Those things need to be fixed. Even something as simple as always using a 2 part naming convention can help a lot but you shouldn't quit there.
Find the code with the highest amounts of combined CPU and Logical Reads. A decent place to start is in SSMS. Right click the instance, select [Reports][Standard Reports], and then follow your nose in the performance related reports to find the reports based on "TOTAL". There are, of course, more sophisticated methods to find bad code but the standard reports are based on the same DMVs that those other methods frequently use.
My recommendation is to stop chasing symptoms and start chasing the fix. SpinLocks, waits, and a ton of other things are usually just symptoms of the larger problem. What is that larger problem? Having decent hardware helps and a hardware problem could be the cause but those are quite rare. The true performance is in the code. Find the worst performing, most resource intensive code, and fix it. Rewrite it if you have to but fix it! Continue for at least the TOP 10 for CPU and I/O.
You'll also be surprised at how quickly and easily some of the problems with code can be resolved. Obviously, the people that originally wrote the code probably won't be able to help because they wrote the troublesome code to begin with and are ill equipped to actually find problems with it or it wouldn't currently be a problem. If you really want to get out of the woods, hire someone like Kevin for a week and watch the magic. He'll even teach you a bit about how to prevent such performance problems in the future.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Have you got any evidence that spinlocks are the problem, as opposed to inefficient queries?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you for your replies Kevin, Jeff & Gail. Really appreciate the help and advice that you have given me.
This is a 3rd party application that I have recently started working on and found fundamental problems with its setup,
these have now been corrected and SQL is performing better than before but occasionally I see a spike in CPU usage which lasts for some time.
As suggested by Jeff I have started looking into code, missing indexes and statistics but the issue is that I will have to go back to the company who
originally wrote these queries, being 3rd party application I have no choice but to go to them or they won't support the system.
As I can't fix the 3rd party application myself, I was also looking at ways to ease the problem/issue temporarily and hence the chasing of symptoms.
Thanking you once again for all your advice.
Kailash.
-
Hi, I'm in the same boat. The vendor is working on fixing the performance issues. I too would like to be able to use spinlock information to understand what is "slow" even if it's a symptom. Yes, the queries are not optimized and could be a lot, lot better. However, that will take time - the code is complex and a mix of legacy and new. If I could find a spinlock that says "Your horrid queries might result in less user horror if the bandwidth to memory was not just 31 GB/sec...." Yep, you know it's bad when you consider RAM might be too slow.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Spinlocks, for the most part, are not going to be an indication or guide for why a query is slow. They're something that you can and should ignore inmost cases.
For identifying queries, look at your reads and cpu for the queries, from extended events or Query Store.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I agree that that queries are responsible. I have used extended events to help identify queries and cursors that need to be optimized. The vendor is currently working on fixing these performance problems. We have an average CXPACKET wait of 11ms. This plus the SOS_SCHEDULER_YIELD account for 50% of the waits on the system. The only latch that counts is ACCESS_METHODS_DATASET_PARENT. I know that spins are low level and are not normally considered, but I'm looking for anything that might help until the real issue is fixed.
The vendor is building a new system with a more recent version of SQL Server. Perhaps there is a SQL optimization or fix that helps. Or perhaps our VM has an issue. I don't know. Here are the top spins for 1 minute. (I used Paul's code for this output.) Spins are normal, but how normal are the spins?
If I knew more about spins, I might be able to look at this and say that I need apply a fix for a SPIN issue. Or perhaps I might be able to say that the bad queries are saturating the bandwidth to RAM. Then I'd have to wonder what my bandwidth is and how to compare it to other VMs where the issue does not appear to be as severe according to the vendor - oranges to pineapples.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Get the queries optimised. The chance that you have a spinlock problem is small.
Most performance problems are bad queries, bad indexing. Address those. Don't go looking deep into the internals until you've fixed the obvious issues (which, from the latch you mentioned is probably excessive table scans)And 'normal' for waits, latches, etc, is dependent on the hardware, data, table design and workload. So normal is for your system, and abnormal is different to what your system is normally. There's no number that's automatically bad.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Randy, I can virtually guarantee you that a competent consultant could quickly find a myriad of misconfigurations, suboptimal infrastructure, poor mx, bad queries, BAD indexing, etc. etc. etc. A fair bit of that would be considered low-hanging-fruit and fixable pretty quickly. And if the ISV uses sprocs or other in-database TSQL code you have access to there are things you can do that would be low-risk but offer huge performance (and concurrency) gains. Same for indexing, although with that it doesn't matter what the code is. I note that those two things will likely get you to an "unsupportable" state, but I have had any number of clients partake of them out of sheer necessity. Oh, plan guides are a possibility too, although I find them to be a bit too fragile. A consultant could also get rather direct with the ISV if necessary in showing them just how awful the system is designed, coded, indexed, etc.
Oh, and please don't post to a year old thread. Start a new one. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Our issues got fixed once the queries were optimized by the Vendor.
Also remember fixing incorrect SQL Server configuration issues such as maxdop, cost threshold, Tempdb etc. on the said server.
Thank you once again to Gail, Kevin & Jeff for their guidance.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply