What's the least risky way to Select on production environment?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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