Skip to main content
Tweeted twitter.com/StackDBAs/status/1258683083022270469
deleted 5 characters in body; edited tags; edited title
Source Link
Paul White
  • 90.2k
  • 30
  • 423
  • 663

MSSQL switching Switching to RCSI

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.

MSSQL switching to RCSI

The company I work for currently uses mssql databases (latest enterprise version usually) for a product we develop. I would describe it as an OLTP database that is somewhat equally write and read intensive with a lot of time critical apps. In 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 nolock 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 awhile 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 nolock hints at it.

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 looing for a good way to show concrete metrics to our team to finally convince them we should potentially move to this methodology.

Switching to RCSI

The company I work for currently uses SQL Server databases (latest Enterprise version usually) for a product we develop.

I would describe it as an OLTP database that is somewhat equally write and read intensive with a lot of time critical apps. In 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 nolock 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 a 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 nolock 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?

I'm looking for a good way to show concrete metrics to our team to finally convince them we should potentially move to this methodology.

Source Link
Cole W
  • 251
  • 1
  • 4

MSSQL switching to RCSI

The company I work for currently uses mssql databases (latest enterprise version usually) for a product we develop. I would describe it as an OLTP database that is somewhat equally write and read intensive with a lot of time critical apps. In 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 nolock 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 awhile 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 nolock hints at it.

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 looing for a good way to show concrete metrics to our team to finally convince them we should potentially move to this methodology.