BT

New Early adopter or innovator? InfoQ has been working on some new features for you. Learn more

Brent Ozar on Improving Productivity with SQL Server

| by Anand Narayanaswamy Follow 0 Followers on Feb 26, 2013. Estimated reading time: 1 minute |

Brent Ozar, SQL Server Consultant, recently published seven techniques which every SQL Server developer should follow in their day-today programming tasks. According to Brent, developers should avoid the usage of ORDER BY clause as far as possible and advises developers to sort data by consuming all query results into the app memory.

If you database performs some work such as processing, sorting, loading, caching then you should ideally use a separate databases for each task. Brent suggests you to put these databases in simple recovery mode and backup it once daily.

He also suggest you to make effective use of Dynamic Management Views (DMVs) and advises you to use 3 different connection strings in your app for various scenarios such as connection for writes, reads, data which are 5-15 minutes old and for data as of yesterday.  According to Brent, the first connection string scenario is difficult to scale as we don't have a lot of options in SQL Server to scale out multiple servers that handle writes.

"Read-Committed Snapshot Isolation is a better choice than With(Nolock) for your app because it gets you consistent data with less blocking hassles," says Brent.

According to Brent, you should not refer to old editions of books and online articles as they are not regularly updated.  "When you read something that sounds like good advice, I like to try the Anti-Doctor-Phil strategy," says Brent.

Finally, Brent suggests you to make use of stored procedures and views instead of functions if you want to reuse code. He says that functions has huge performance drawbacks in the database tier.

Several developers doesn't agree to Brent's tips.

Tyler Burd

I disagree with #2 ("Avoid ORDER BY; sort in the app instead") without any stipulations.

Brent replied to Tyler's remarks:

why not consume the entire dataset down to the app tier and cache it there?

Few developers also posted comments against Brent's suggestion to avoid functions and make use of views or stored procedures.

Do you agree/disagree with Brent's tips and suggestions? Post your comments and InfoQ will make sure to reach out to Brent to address them?

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT