An optimizing experience

My blog has moved.
You can view this post at the following address:
http://www.osherove.com/blog/2003/12/25/an-optimizing-experience.html
Published Thursday, December 25, 2003 11:19 PM by RoyOsherove
Filed under:

Comments

Thursday, December 25, 2003 11:23 AM by TrackBack

# My first stored procedures optimization lesson(and resources)

Thursday, December 25, 2003 11:27 AM by TrackBack

# My First SQL *real* optimization (and some good resources)

Friday, December 26, 2003 10:33 AM by Kyle Cordes

# re: An optimizing experience

Good tips above, nice post.

A source of SQL Server frustration for me has been getting SQL Server to use any kind vaguely good query plan for not-all-that-complex queries (3-5 tables joined), in which several of the tables have millions of records. For some reason, for our particular tables (which have indexes in all the right places), SQL Server really likes to do 10-million-row table scans. It's often required some query rearrangement (and someimes even a much-maligned index hint) to get good results.

I wish there was an option to tell SQL Server "No matter what, never table scan a table with more than a million rows; if you come up with a plan that does that, punt and throw an error message instead, so a DBA can fix it, rather than pound on a production database for 10 minutes straight"

Saturday, December 27, 2003 12:59 PM by Roy Osherove

# re: An optimizing experience

Hey Kyle. Yes I bet I didn't even come close to the sort of problems you have been facing. Howevenr this aticle:
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
has lots of tips on that matter. Highly recommended.
Sunday, December 28, 2003 7:07 AM by Michal

# More links

more links about SQL optimalization
Sunday, December 28, 2003 1:25 PM by TrackBack

# more SQL optimization resources and a bonus tip

Sunday, December 28, 2003 8:12 PM by Darrell

# re: An optimizing experience

Kyle, there is something similar to what you are asking for. Check out the Query Governor Cost Limit Option for SQL Server configuration: (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_73u6.asp).
Sunday, December 28, 2003 8:15 PM by Darrell

# re: An optimizing experience

Kyle,

Joins are also extremely expensive. I don't know what you are doing the joins for (are they lookup tables?), but sometimes it is easier to do several SELECTs from a single table and then combine the results together.
Friday, January 02, 2004 11:26 AM by Oskar Austegard

# re: An optimizing experience

Would ISNULL be any faster than COALESCE?
Saturday, January 03, 2004 10:56 AM by Roy Osherove

# re: An optimizing experience

Probably not, since ISNULL() is also a function. The omptimizing rule says that you can't use a cluster search when there is any sort of function on the name of the column.
Tuesday, February 10, 2004 6:51 PM by Marc Wallace

# re: An optimizing experience

I've been finding weird behaviour around isnull() and coalesce(), in terms of query optimization.

In one case, replacing all my coalesce() calls with isnull() dropped a 2hr+ query down to 10secs. However, in another case, it had the opposite effect.

And it does look like SQL server treats the two differently, almost as if isnull() is more of a builtin function.
Monday, May 10, 2004 11:49 PM by Roger Willcocks

# re: An optimizing experience



> I've been finding weird behaviour around isnull() and coalesce(), in terms of query optimization

Me too. I've got a query that with coalesce returns 42 rows, and with isnull instead returns 38 rows. Even though as far as I can determine the return values are the same.