The company I work for currently uses mssqlSQL Server databases (latest enterpriseEnterprise version usually) for a product we develop. I
I would describe it as an OLTP database that is somewhat equally write and read intensive with a lot of time critical apps. InIn addition to this, a lot of reporting and graphical data is displayed from information in this same OLTP database (separate problem) against a lot of the same tables that are being read from and written to at a frequent rate.
We commonly experience problems where blocking occurs and it usually ends up slowing down time critical apps or even causing issues because of deadlocks in these apps. The common solution to this problem often seems to be to throw nolocknolock
hints at the problematic queries. I honestly hate this solution and I've felt for a long time that this is the wrong way to try and tackle this problem and from everything I've read I come to the same conclusion.
I've been trying to convince my team for awhilea while that RCSI is something that we could definitely benefit from especially given our type of database. They seem to think this is a big risk and often put it off because of the risk factor but we continue to run into performance problems where we just throw nolocknolock
hints at it.
- How can I help prove that our database can greatly benefit from using RCSI?
- Are there performance tests I can run based on an actual production database that we convert to RCSI in a test environment?
So my question is how can I help prove that our database can greatly benefit from using RCSI? Are there performance tests I can run based on an actual production database that we convert to RCSI in a test environment? I'm just looinglooking for a good way to show concrete metrics to our team to finally convince them we should potentially move to this methodology.