SQl code performance

1. What measurement do you know if this sql code is efficient based on execution plans?

2. How do you know if this sql code is efficient based on execution plans?

SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers] 
WHERE [Country] = 'Germany'
ORDER BY [CompanyName]

SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers] 
WHERE [Country] = 'Germany'

If you’re asking why one of these plans is more expensive than the other one:

Your first plan performs a sort on a column that is not indexed (or not part of the index that is being used to retrieve the data). Therefore once the data is retrieved, because you’ve asked for an ORDER BY, the results have to be re-sorted (since the clustered index will return the data in a different order).

An index that will help this specific query with the sort be more efficient might be (and I’m not suggesting this would be ideal):

CREATE NONCLUSTERED INDEX cou_com ON dbo.Customers(Country, CompanyName)
  INCLUDE (CustomerID, City, Region);

But please don’t go create that index without much more proper investigation. An index might help this one query but if your system is DML-bound (meaning more write activity than read activity) that could just make it worse. If this query is not slow and you are just concerned that it has a higher relative cost than some other query, this is not where you should start optimizing IMHO.

If you’re asking generically how to analyze execution plans:

This is not really the purpose of the site. For some resources (books and e-books) on performance tuning in general, I would check out this question and its answers. In particular, Grant Fritchey’s free e-book SQL Server Execution Plans.

You may also be interested in the free SentryOne Plan Explorer. It presents a lot more information – information that, in Management Studio, is hidden in Properties panels, incorrect in some cases, or simply not available at all. I blogged about how to present the benefits of this tool to other users, but it could be a tutorial of sorts as well.