SQLMaestros’ Post

Transcript

Let's look at this query. Select start from sales, Sales order header. Let's go and execute this. Jump over to the execution plan and you will see a clustered index scan in action. And when you look at the messages tab, you will see that about 600 plus pages were read. All good. What we want to do is let's say seek upon the index. We don't want to scan and seek is supposed to be more efficient. We simply change the query a bit and we say. Give me all the orders where sales order ID is greater than one. If you execute this and jump over to the execution plan you will see that now we have a clustered index seek. But if you jump over to the messages tab you will see that the same number of pages where read again. Now here is a very classic example of an inefficient index seek operation. This is a common scenario in a multi tenant environment and you've got to introduce a tenant. To make seek more effective, in other words, you got to make your predicate more selective. Let's say we introduce the territory ID here along with sales order ID to make the overall predicate more selective. If you do this, let's go and execute. Jump over to the execution plan. You will see that SQL Server is still using clustered index seek because probably this is the only best index available. But it also recommends a non clustered index if you look at the. Nonclustered index recommendation. SQL Server is recommending territory ID and sales order ID in a multicolumn index but territory ID to be the first column. The question is why does SQL Server recommend territory ID as to be the first column in a multi column index? Huge discussion. Watch the full video. The link is somewhere around this post and hopefully you will have something new to learn. Happy SQL.