Here's another case where you might see intermittently poor performance that is "by design". Suppose you see that a delete, insert, or update query in a stored proc usually runs quickly, but occasionally the query takes much longer to complete. You captured...

To set up this scenario, run the script below: USE tempdb GO IF OBJECT_ID ('test1') IS NOT NULL DROP TABLE test1 GO CREATE TABLE test1 (c1 tinyint, c2 smallint) DECLARE @x int DECLARE @msg varchar(1000) SET @x = 1 SET NOCOUNT ON BEGIN TRAN WHILE...

Everyone that has worked with databases for long enough has run into situations where the query optimizer doesn't select the best possible plan. You may find that you can force SQL to use an index, choose a different join algorithm or join order, or use...