5

My goal is to use selects on production without affecting anything else.

When I was new to SQL, I just wrote simple selects, but later I learned this can cause locks.

If I don't care about dirty reads, I can use nolocks on every select or set transaction isolation level to read uncommited.

But I do care about dirty reads, so I went with isolation level snapshot. Isolation level snapshot writes into tempdb and I don't know if that's a problem or not (extra writing to database means slower DB and less space?)

So what is the least intrusive way to write a select on DB, where I cannot afford slowing it down or creating shared locks? Should I go back to isolation level read uncommited on every query?

This is asked from developer point of view. I don't know details about DB settings, but want to use selects in a way that will cause the least trouble.

  • 2
    Is setting up a replication server an option? – ypercubeᵀᴹ Jan 3 '17 at 10:05
  • I've edited the question but basically I don't want to change any settings or set up a replication server. Just trying to find a way to write my selects from developer point of view without affecting DB in significant way (speed, locks, space used). – Zikato Jan 3 '17 at 11:15
  • In a testing environment, write your select statements and performance tune them. Take advantage of existing indexes, and only return the data you are actually interested in. Once your queries are tuned and low-impact, then run them in production during off-peak hours, and kill them if they start blocking other processes. If the data is important, don't allow dirty reads. Just stick with the default. – datagod Jan 6 '17 at 14:47
4

The correct answer will depend on a number of things:

  • The size of your database.
  • The size of your buffer pool compared to your database.
  • The volume and nature of the background (ie: not you) query activity.
  • The nature of the queries you want to run.
  • What will you bottleneck on first (CPU, I/O, tempdb throughput).
  • How much extra latency you are willing to tolerate in your normal workload.

You should always get an estimated query plan and look for intensive operations, such as scans of large tables/indexes, or sorts of a large number of rows. One of the most important considerations when working with a live, heavily used database is to avoid driving pages out of the buffer pool by running a query that needs to bring a lot of data into cache.

If you are concerned about your select queries going parallel and consuming excessive CPU, you can limit your query to a single core by adding OPTION (MAXDOP 1) to the end of your query.

If you don't have Read Committed Snapshot enabled on your database, see if you can get that turned on. That will give you snapshot-like behaviour on your selects, so that you will not block writers. However if you are running long ad-hoc queries and there is a lot of write activity, you'll get a lot of data written to tempdb.

You could also investigate using Resource Governor to limit yourself.

3

If the database does not contain sensitive or personally identifiable information, you could restore yesterday's full backup onto your workstation, then restore any transaction log backups to the point in time of interest. This will give you free reign to stop worrying about your impact on production while still allowing you to query to your heart's content.

As a matter of principle, as a developer you should never query production.

Best practice is to setup a production support environment where you restore production databases nightly, and use that to run queries against "production". This ensures you know how to restore backups in the event of disaster recovery, ensures those backups are tested daily, and allows you to offload DBCC CHECKDB processing onto the production support instance.

0

You are going to impact

Start with a

select top (10) 

With no order by to be sure you are not feeding in a hard query

Start with (nolock) and only drop it when you need clean data

  • I prefer set transaction isolation level read uncommitted rather than with(nolock) but that is just my preference the result is the same – Marcello Miorelli Mar 22 '18 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.