October 6, 2015 at 3:51 am
Hello,
(I just want to preface my question by saying that I'm not looking for a definitive answer on this: I just want to gauge peoples thoughts and experiences on tackling locking and blocking with hardware changes. Obviously code changes is the obvious choice, but with 3rd party databases that is hard to manage, especially when the developers are insistent that there is nothing they can do.)
I'm managing a customised database based off of the Microsoft Dynamics NAV CRM solution, and we are experiencing a lot of locking and blocking issues. A quick Google search will throw up plenty of results that locking/blocking is a known issue within NAV. Using the Redgate Monitor tool, we have 100's of locking/blocking alerts a day, and the company want to know what changes we can make at a hardware level to help reduce this: the 3rd party development team who customised NAV have recommended that the database is stored on SSDs.
However, I'm skeptical with this because the buffer cache hit ratio is rarely, if ever below 100%, and that latency for the read/write on the data disk is rarely above 25ms. The total memory available to the instance is 196GB, and the database itself is 525GB, so I know that SQL does have to retrieve data on occasion.
So essentially my question is:
Will putting the data file on SSD actually reduce locking blocking when all the data appears to be in memory anyway?
I'm pretty certain that the server itself is OK, it's the database itself that really needs an overhaul.
For what it is worth, MAXDOP is set to 1, the CPU's load (2*X5570) are never greater than 40%, and the CPU Length Queue is rarely above 0,005, and never greater than .025, so definitely not CPU bottleneck.
Any thoughts appreciated!
October 6, 2015 at 3:58 am
Probably not. It might even make the locking worse, as there will be less IO-related waits.
Buffer cache hit ratio's useless. A server can be under severe, sustained memory pressure and it'll still be sitting at 99%.
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
October 6, 2015 at 4:51 am
Thanks for the speedy response. I didn't think it would help (felt more like the devs washing their hands of the issue), but your point about BCHR being useless interested me and I found this article on Simple Talk that has helped me understand.
https://www.simple-talk.com/content/article.aspx?article=1426
October 6, 2015 at 6:26 am
I might help some, but probably not much, especially considering the amount of memory you have on the system.
Locking and blocking are primarily an issue related to code and data structure.
First thing I'd do is get the wait statistics on the database (if you're on one of the newer versions of SQL Monitor, it should be available). Show them what things are waiting on. If it's not I/O related waits, then the SSD is unlikely to help. Next thing I'd do is look at some of the default settings on the server. What is the cost threshold for parallelism? What are the MAXDOP settings? Are you using read committed snapshot isolation? Do you have optimize for ad hoc enabled? Get those adjusted off the default values and see how that helps the system. Again, drive it all first off the wait statistics.
"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
October 7, 2015 at 3:26 pm
Check the write times to the transaction log file. You'd like those at or under 1ms if possible. If you're pushing past 4ms, then you may want to look at other options.
If moving the transaction log to SSD decreases write times to it, then transactions will commit faster and locks will release sooner.
Eddie Wuerch
MCM: SQL
October 8, 2015 at 2:34 am
Thanks for the responses. I'm using version 4 of SQL Monitor so can see the top waits: PAGEIOLATCH_SH and PAGEIOLATCH_EX are in the top 10, however all the other waits are lock related.
With regards to server config, I enabled "optimise for ad hoc" some weeks back as the plan cache was bloated with ad-hoc caches. MAXDOP on the server level is set to 1. RCSI is not enabled, however I've read that NAVSQL does not support RCSI as it uses READ UNCOMMITTED for reads and SERIALISABLE for writes.
With regards to disk write times on the log file (of which there are three, thank you Mr Previous DBA) is under 4ms for most of the day. There are a few spikes over a 24 hour period to 10ms, but it actually looks ok.
I agree the issue is at the code level; I can see a lot of queries that are repeated throughout the day (and I haven't yet mentioned that SSRS queries this db directly, something I'm keen to move to a log shipped copy.)
Auto create statistics are off by default (which again is apparently NAVSQL standard practice) but yesterday I created a few manually to improve a query by 50%, so there's definitely scope for faster queries, and therefore less locks, without paying out for SSD.
Other than that it's just sifting through the 100's of locks to find the big offenders and the ones that are fixable. I've read everywhere that NAV blocks and that is the way it is, however I'd like to reduce the amount!
October 8, 2015 at 5:01 am
I'm not crazy about that configuration. I'd rather see the CPUs available for use instead of a MAXDOP of 1. Just make sure to change the cost threshold for parallelism to a high number. Some queries do benefit from parallel execution.
The read uncommitted locking is... well, dangerous. You can get missing or extra rows depending on how page splits are occurring on the system.
Other than that, yeah, it really does seem like query tuning, and indexing (and stats) are what are really needed, not so much hardware.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply