Browse by Tags

• Batch Size
• Benchmark
• Best Practices
• Lookup tables
• Loops
• Optimizer Bug
• Performance
• Query Processing
• Sets
• sysprocesses
• Testing
• Top CPU comsumers
• T-SQL

• Performance impact: The cost of doing small lookups in a large batch update

  Lookup tables are widely used in database applications for good reasons. Usually, a lookup table has a small number of rows and looking it up with a join is fast, especially when the table is already cached. Recently, I needed to update every row in many Read More... Posted Monday, April 04, 2011 3:34 PM by Linchi Shea | 3 Comments Filed under: Testing, Best Practices, Performance tuning, Lookup tables Attachment(s): LookupCost.GIF

• Performance impact: Forget about sets and focus on loops!

  Now that I’ve got your attention, do I really mean that? Of course, I do in the context of T-SQL performance tuning and to a very large extent. Many in the SQL Server community have long preached a set-oriented approach to improving database query performance, Read More... Posted Wednesday, August 11, 2010 3:32 PM by Linchi Shea | 3 Comments Filed under: T-SQL, Performance tuning, Loops, Sets

• Revisiting the transaction batch size

  It’s a well known good practice to control the batch size when you perform large data changes. For instance, you may need to purge a large amount of data monthly, and if you delete them all in one shot, you may blow up your transaction log. Therefore, Read More... Posted Sunday, August 01, 2010 12:05 AM by Linchi Shea | 4 Comments Filed under: Performance, Best Practices, Performance tuning, Batch Size

• Would you optimize SQL for less performance?

  What do you mean? Okay, that does sound like an oxymoron, doesn't it? Let's say you are trying to optimize a stored procedure, and your proposed change results in the stored procedure running not faster, but perhaps a bit slower than it currently does. Read More... Posted Friday, January 08, 2010 9:56 AM by Linchi Shea | 19 Comments Filed under: Performance, Benchmark, Performance tuning

• Performance impact: UNION ALL views, ANSI_PADDING, and bad query plans

  Whether or not you specify it explicitly, ANSI_PADDING setting is there when you create a table, and can have an impact on the performance of some queries. If you are not careful, it can even hurt performance big time! Let’s demonstrate that with an extremely Read More... Posted Friday, December 04, 2009 3:43 PM by Linchi Shea | 6 Comments Filed under: Performance, Query Processing, Performance tuning, Optimizer Bug Attachment(s): Bad_plan_with_diff_ansi_padding_repro.zip

• Quick, dirty, but effective – finding top CPU consumers

  Despite all the DMVs, the sysprocesses table still offers a significant amount of information for identifying top CPU or I/O consumers. A great advantage is that it is SQL Server version neutral (so far anyway) and convenient to get to. So inevitably, Read More... Posted Friday, August 21, 2009 7:07 PM by Linchi Shea | 4 Comments Filed under: sysprocesses, Top CPU comsumers, Performance tuning