0

We are using SQL Server 2012 EE but currently do not have the option to run queries on a R/O mirror though that is my long term goal, though am concerned I may run into the below issue in that scenario as well since the mirror would also be updating data I am querying.

I have a view that joins across several tables from two databases and is used for invoicing from existing data. Three of these tables are also actively updated by ongoing transactions. Running a report that used this view did not used to be a problem but now our database is getting much larger and I have run into some timeout problems. First the query was timing out so I set command timeout to 0 and reran the query which pegged all 4 CPUs 100% for 90 minutes and then I killed it. There were no problems with active transactions during that time. I reviewed the query and found a field I was joining on that was not indexed so created an index on that field, reran the report, which then finished in three minutes and all the CPUs were busy but not at all pegged out. Same data amount queried both times. I figured problem solved. Of course later, my boss ran a similar query, perhaps with some more data but probably not a lot more, and our live transactions started timing out 100% while his query was running. I did not get a chance to see the CPU usage during that time.

So my questions are two:

  1. Given I have to use the live and active database, what is the proper way to run a long R/O query so that active transactions can still continue? I am considering NO LOCK but am hoping there is a better standard practice.

  2. And what might cause sqlserver to peg out 4 CPUs with 100% busy and not cause live transaction timeouts, yet when my boss ran his query, after I added the index and my query ran much better, the live update transactions start timing out 100%?

I know this is not a lot of info to go on. I'm not very familiar with sql profiling and performance monitoring yet this behavior seems rather odd and am hoping a best practice would be the correct workaround.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Dave
  • 1,822
  • 2
  • 27
  • 36
  • Does this view happen to join to another view? – HLGEM Oct 03 '14 at 14:58
  • Have you looked at an execution plan of the underlying queries? – Dave C Oct 03 '14 at 15:02
  • @HLGEM - no, just tables but from two databases on the same server. – Dave Oct 03 '14 at 15:11
  • @JiggsJedi - No but I'm going to look into how to do that. Not sure how that would explain the different behavior though. – Dave Oct 03 '14 at 15:13
  • Get an execution plan, and show us the query -- maybe we can offer some suggestions. But in the db, statistics can get "stale" over time, and cause performance degradation, variables not converted/handled correctly can impact it, and nulls in an indexed column can cause it. – Dave C Oct 03 '14 at 15:16
  • @JiggsJedi - I have a sqlplan file that suggested I add a new index with the include clause so am going to try that. Still, I'd love some advice on how one should run long queries on an active DB. Thx. – Dave Oct 03 '14 at 18:05
  • To the person that down voted but would not show their name or why, I'm a developer, not a DBA but am in a position where I must to what I can DBA wise. I can and will work through the indexing requirements but mostly I'd like some advice on how one should run long queries on an active DB. – Dave Oct 03 '14 at 18:08

2 Answers2

2

The default behavior of SELECT queries in the READ_COMMITTED transaction isolation level is to acquire shared locks during query execution to provide the requested data consistency (read committed data only). These locks are typically row-level and released quickly during query execution immediately after each row is read. There are also less granular intent locks at the page and table level prevent concurrent updates to data as it is being read. Depending on the particulars of the execution plan, there may even be shared locks held at the table level for the duration of the query, which will prevent updates to the table during query execution and result in readers blocking writers.

Setting the READ_COMMITTED_SNAPSHOT database option causes SQL Server to use row versioning instead of locking to provide the same read consistency. A row version store is maintained in tempdb so that when a row requested by the query has changed since the query began, the most recent committed row version is returned instead. This row-versioning behavior avoids locking and effectively provides a statement-level snapshot of the database at the time the query began. Readers do not block writers and writers do not block readers. Do not confuse the READ_COMMITTED_SNAPSHOT database option with the SNAPSHOT isolation level (a common mistake).

The downside of setting the READ_COMMITTED_SNAPSHOT is additional resource usage. An additional 14 bytes of storage overhead for each row is incurred once the database option is enabled. Updates and deletes will generate row versions in tempdb. These versions require tempdb space for the duration of the longest running query and there is overhead in maintained the version store. Also consider whether you have existing applications that depend on readers-block-writers locking behavior. Despite this overhead, the concurrency benefits may yield better overall performance depending on your workload, while providing read integrity. See http://technet.microsoft.com/en-us/library/ms188277.aspx for more information.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
1

Actually I decided to create a snapshot at the beginning of each month for reporting to run against. Then delete when no longer needed for reporting. This seems to work fine. I could do something similar with a database restore but slightly more work. This allows not needing a second SQL EE license, and lets me run reports w/o locking tables for live transactions.

Dave
  • 1,822
  • 2
  • 27
  • 36
  • If your concern is to avoid a long-running select query blocking other queries, consider turning on the READ_COMMITTED_SNAPSHOT database option so that row-versioning instead of locking is used for read consistency. – Dan Guzman Oct 04 '14 at 01:28
  • @Dan - thx. I thought queries all did shared locks. I'm concerned with blocking short transactional multi table updates as those were what were timing out. I will look into if that option can be used in that scenario similar to taking a whole snapshot. If it does and you want to propose as the solution, I'll mark as such. Really, I'm trying to just find the best practice for running long reports off a database that does active transactional updates. – Dave Oct 04 '14 at 12:55
  • I'll go ahead and post an answer with more details of the read READ_COMMITTED_SNAPSHOT option. – Dan Guzman Oct 04 '14 at 13:59