Quick Tip #3: Think!

An important lesson for everyone that has adhoc access to SQL Server is that before you run any query in Management Studio is to think about the consequences first.

For example, if you want to view data in a table, your first thought is to SELECT * FROM Table, or to open it in Management Studio, which essentially performs the same function.  But before you run this, have a think about the possible consequences.

• If this is a large table, you might start reading 10 GB of data out of SQL Server
• If you’re reading 10 GB of data, you might destroy the buffer cache, causing queries to run slowly until everything’s cached again
• You’ll likely end up with a table lock that will last until the end of the transaction, assuming the default isolation level of read committed.  How long will the SELECT take to run?

It’s important as a DBA to do no harm.  In this situation, there’s a couple of things you can do.  You can just get a quick selection of rows if you just want to get an idea of what’s in the table:

• SELECT TOP 100 * FROM Table
• SELECT TOP 100 * FROM Table (nolock)

• SELECT * FROM sys.sysindexes WHERE object_id = object_id(‘Table’) 
• SELECT COUNT(*) FROM Table (nolock) — Not Recommended

The first option will give an approximate number of rows, but it is fast. (I like sysindexes as it works on both 2000 and 2005.)  Note that I don’t recommend the second option as, again, it loads all data pages into memory, destroying the buffer cache.

What about building an index, assuming you don’t have Enterprise Edition and can’t build the index online?  Let’s think:

• How many rows in the table, and how wide will each row in the index be?  If it’s small, I might be able to build it immediately.
• How active is the table?  If it’s very active, any blocking during an index build can be disasterous, and so should wait for a maintenance window.
• How long will the index take to build?  Can any users afford to be blocked for 20 seconds?  Some websites kill the query after 10 seconds, so users would see timeouts, whereas other applications might not mind if the query runs for up to 30 seconds (30 being the usual .NET timeout). 

By thinking of the possible consequences, you are more likely to keep the system running, and not accidentally get in the way of your users.  So, before hitting “Execute”, have a quick think of what could happen when you run that query (especially if you have an DELETE statement with no WHERE clause!)