January 22, 2009 at 6:39 am
We have a vendor application that has been giving us headaches. In fairness we do not know if the performance problems we are experiencing are due to the app code, app configuration, SQL Server or the SAN. In reviewing the technical documentation I ran across the following comment:
Deadlocks can frequently occur on SQL Server 2005 as SQL Server 2005 place locks on the page level rather than row level
Do you agree with this statement?
Thanks, Dave
January 22, 2009 at 6:52 am
DBADave (1/22/2009)
Deadlocks can frequently occur on SQL Server 2005 as SQL Server 2005 place locks on the page level rather than row levelDo you agree with this statement?
No.
All versions of SQL since 7.0 can lock at the row, page, extent or table level. Deadlocks are typically caused by bad code or bad indexing or, as is more normal, both. With the exception of a few very strange cases, deadlock can be completely eliminated by fixing code and fixing indexes
If that's in your vendor's docs, it sounds like they're trying to blame SQL for their bad code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 7:13 am
Me neither. Same things Gail said. Plus, 2005 offers a few things that improve performance, reducing contention, helping to prevent deadlocks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 22, 2009 at 7:46 am
That's what I wanted to confirm. They just added this statement to their technical document in January after we started complaining about deadlocks a month or two earlier. It almost sounds like they are trying to say the deadlocks have nothing to do with their application. Another statement they made in the doc is that we should identify the code causing the deadlocks and add indexes if necessary to resolve the problem. That makes sense if this is our code, but it's theirs. They should be fixing this issue, not offloading the problem to the customer. Ok. Now I'm venting. Sorry.
By the way, this is the same vendor who told us to turn off AUTO UPDATE STATS and AUTO CREATE STATS.
Thanks for the confirmation.
Dave
January 22, 2009 at 7:59 am
DBADave (1/22/2009)
Another statement they made in the doc is that we should identify the code causing the deadlocks and add indexes if necessary to resolve the problem.
Other problem with that statement is that indexes won't help at all if the problem's in the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 8:11 am
Please do let me know the vendors name sp that I know that we will never make bussiness ventures with them... 😀
Cant you trace the Dead locks by adding the parameter and then send it over to them and ask them to fix it??
-Roy
January 22, 2009 at 9:05 am
I don't want to say the name. I'm not sure the trouble that could get me or our company in. 🙂
We've traced the deadlocks and provided the vendor with the results. Some deadlocks they say are fixed in a newer version of the application, but others they've never addressed. Their latest response told us to consider turning on SNAPSHOT isolation, but I need to do some research regarding the impact of enabling SNAPSHOT Isolation. Personally I prefer they fix their code.
Thanks, Dave
January 22, 2009 at 9:11 am
I did write an article about Row level versioning that uses the SNAPSHOT Isolation. For basic idea you can take a look at that.
http://www.sqlservercentral.com/articles/SQL+Server+2005/62464/
-Roy
January 22, 2009 at 10:57 am
Let's see how you like this one. I just spoke with a technical contact for the vendor regarding various topics and on the subject of enabling AUTO_UPDATE_STATISTICS_ASYNC he said, even though their document says we can enable it, don't do it. His explanation was that if it is enabled and there is a lot of update/insert/delete activity, SQL Server will update the index statistics and as a result the Optimizer will also select a new plan. Ok. So what's wrong with that I said. His answer was that they have witnessed the Optimizer picking incorrect query plans that have lead to performance problems. What I find very strange is we run Update Statistics will FULLSCAN every morning. So based upon his logic how does he know SQL Server is choosing the correct plan when we run it manually. At that point I realized further conversation about statistics was going to lead nowhere. Maybe I completely misunderstand how statistics work, but I thought updated statics is what causes the optimizer to choose a good plan and that, while perhaps it is possible for it to occassionaly elect a bad plan, you would be worse off with it using an old plan that is no longer useful.
He also said we should delete any statistics created a while back when we first had AUTO UPDATE STATS on. Those statistics begin with _WA and they could be causing our update statistics job to run slower. Ok. Perhaps he could be correct, but I will find out the impact after deleting them.
He then talked about how 2005 uses more L2 cache on the processor and that we only have 1024KB, which could be why we are seeing some performance problems. I'm not too familiar with how much L2 processor cache a server should have for 2005 so I need to do some research. If anything it will help me understand the impact of L2 processor cache better.
Thanks, Dave
January 22, 2009 at 11:12 am
DBADave (1/22/2009)
Let's see how you like this one. I just spoke with a technical contact for the vendor regarding various topics and on the subject of enabling AUTO_UPDATE_STATISTICS_ASYNC he said, even though their document says we can enable it, don't do it. His explanation was that if it is enabled and there is a lot of update/insert/delete activity, SQL Server will update the index statistics and as a result the Optimizer will also select a new plan. Ok. So what's wrong with that I said. His answer was that they have witnessed the Optimizer picking incorrect query plans that have lead to performance problems. What I find very strange is we run Update Statistics will FULLSCAN every morning. So based upon his logic how does he know SQL Server is choosing the correct plan when we run it manually. At that point I realized further conversation about statistics was going to lead nowhere. Maybe I completely misunderstand how statistics work, but I thought updated statics is what causes the optimizer to choose a good plan and that, while perhaps it is possible for it to occassionaly elect a bad plan, you would be worse off with it using an old plan that is no longer useful.
Actually, more up to date statistics is a good thing, not a bad thing. That's why you should be running with AUTO UPDATE STATS & AUTO CREATE too. Even with up to date stats, depending on the parameter passed in to a given query, the optimizer might choose a bad plan, but the overhwelming majority of the time it's much more likely to get a good plan with up to date statistics. This guy at the vendor is making me nervous.
He also said we should delete any statistics created a while back when we first had AUTO UPDATE STATS on. Those statistics begin with _WA and they could be causing our update statistics job to run slower. Ok. Perhaps he could be correct, but I will find out the impact after deleting them.
The more statistics there are, the longer the FULL SCAN will run. However, I'd hesitate prior to deleting statistics. If they were created, it's because they were needed. You can drop them with no real impact, unless, they were needed in which case, you'll see performance drop.
He then talked about how 2005 uses more L2 cache on the processor and that we only have 1024KB, which could be why we are seeing some performance problems. I'm not too familiar with how much L2 processor cache a server should have for 2005 so I need to do some research. If anything it will help me understand the impact of L2 processor cache better.
Thanks, Dave
I don't know much of anything about L2/L3 cache & all that stuff, so I'll shut up now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 22, 2009 at 11:25 am
Grant Fritchey (1/22/2009)
DBADave (1/22/2009)
Let's see how you like this one. I just spoke with a technical contact for the vendor regarding various topics and on the subject of enabling AUTO_UPDATE_STATISTICS_ASYNC he said, even though their document says we can enable it, don't do it. His explanation was that if it is enabled and there is a lot of update/insert/delete activity, SQL Server will update the index statistics and as a result the Optimizer will also select a new plan. Ok. So what's wrong with that I said. His answer was that they have witnessed the Optimizer picking incorrect query plans that have lead to performance problems. What I find very strange is we run Update Statistics will FULLSCAN every morning. So based upon his logic how does he know SQL Server is choosing the correct plan when we run it manually. At that point I realized further conversation about statistics was going to lead nowhere. Maybe I completely misunderstand how statistics work, but I thought updated statics is what causes the optimizer to choose a good plan and that, while perhaps it is possible for it to occassionaly elect a bad plan, you would be worse off with it using an old plan that is no longer useful.Actually, more up to date statistics is a good thing, not a bad thing. That's why you should be running with AUTO UPDATE STATS & AUTO CREATE too. Even with up to date stats, depending on the parameter passed in to a given query, the optimizer might choose a bad plan, but the overhwelming majority of the time it's much more likely to get a good plan with up to date statistics. This guy at the vendor is making me nervous.
He also said we should delete any statistics created a while back when we first had AUTO UPDATE STATS on. Those statistics begin with _WA and they could be causing our update statistics job to run slower. Ok. Perhaps he could be correct, but I will find out the impact after deleting them.
The more statistics there are, the longer the FULL SCAN will run. However, I'd hesitate prior to deleting statistics. If they were created, it's because they were needed. You can drop them with no real impact, unless, they were needed in which case, you'll see performance drop.
He then talked about how 2005 uses more L2 cache on the processor and that we only have 1024KB, which could be why we are seeing some performance problems. I'm not too familiar with how much L2 processor cache a server should have for 2005 so I need to do some research. If anything it will help me understand the impact of L2 processor cache better.
Thanks, Dave
I don't know much of anything about L2/L3 cache & all that stuff, so I'll shut up now.
Grant - I don't either (know much about the L2 vs L3), but considering that this guy seems to be going down the checklist of "what can I blame my badly performing code on (preferrably something that's damn near impossible to disprove)", I wouldn't put much stock in these recent declarations.
Personally - I'd ask him to put his cards on the table, and PROVE his (IMHO) BS statements to you. Like Sergiy would say - a DBA must KNOW and not GUESS. In that light - have him show you what makes him think all of these "wonderfully novel" things. Vendor or no vendor - it's time in this conversation to put up or shut up.
Just my take on it of course...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 22, 2009 at 11:31 am
DBADave (1/22/2009)
His answer was that they have witnessed the Optimizer picking incorrect query plans that have lead to performance problems.
Not uncommon, when one has out of date statistics
He also said we should delete any statistics created a while back when we first had AUTO UPDATE STATS on. Those statistics begin with _WA and they could be causing our update statistics job to run slower. Ok. Perhaps he could be correct, but I will find out the impact after deleting them.
True, they could. It's more stats to update, but the optimiser will just create them again if it needs them (assuming that auto-create-stats is on). If it's not on, the optimiser makes a guess as the
He then talked about how 2005 uses more L2 cache on the processor and that we only have 1024KB, which could be why we are seeing some performance problems. I'm not too familiar with how much L2 processor cache a server should have for 2005 so I need to do some research. If anything it will help me understand the impact of L2 processor cache better.
Now that's sheer, absolute garbage.
The L1 and L2 caches are processor caches. They're there so that the data and the instructions that the processor(s) are busy with is right there, and don't have to be fetched from main memory (which is orders of magnitude slower than the on-board cache)
By the time it gets to dealing with that, SQL is out of the picture. The usage of the caches is up to the operating system (partially) and the microcode on the processor. The more something uses the cache, the better, because trips to memory will leave the processor idling for multiple executions. The way something is compiled might affect the usage of the cache, not sure.
Generally, the one thing that does play havoc with the caches is hyperthreading, because with hyperthreading, the L1 and L2 caches are shared between the two virtual cores. With dual core (and higher) the L1 cache is dedicated to a core, the L2 may or may not be shared (depends on the processor make and model)
Does he know what make and model processor you have in the server? Because that's the way you tell what the amount of cache is. 1MB for L2 sounds a bit low for a modern processor. The processor in my desktop (Core2 Quad Q6600) has 8MB of L2 cache.
For a joke, play ignorant and ask him what the L1, L2 and L3 caches are, how big they should be, how you can increase their size and why the size of the cache is causing problems
p.s. by this point, if it were me, I'd be looking for an alternative to this vendor and informing him that he was facing the choice of fix his code or lose a client.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 11:40 am
I was looking up at the articles about L1, L2 and L3 caches. Yes, like Gail said, Hyperthreading will make the L Caches get cleaned out faster thus making it slower. Also whenever the worker attempts to access hot data structure protected by spinlock it potentially might have a less chance of acquiring the spinlock which will cause the worker's spinning. This behavior will translate to high CPU utilization and significant drop in performance.
I think you should call this guys bluff out....
-Roy
January 22, 2009 at 11:49 am
Roy Ernest (1/22/2009)
Also whenever the worker attempts to access hot data structure protected by spinlock it potentially might have a less chance of acquiring the spinlock which will cause the worker's spinning.
Are you talking about a spinlock in the cache or within SQL? Within SQL there are (undocumented) ways to see the spinlock waits. (I think)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 11:54 am
Within the cache...
-Roy
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply