I gave a presentation this week on T-SQL Window Function Performance. You can view the presentation here. You can also download the code and the slides.
I had just a couple of questions during this presentation. If you watch the recording and have a question, be sure to post the question as a comment, and I'll be sure to answer it here.
Q. Please explain how to determine which columns should be used in the POC index.
A. The "P" stands for Partition. If you are using a Partition By clause, then that column is your P column. The "O" stands for ORDER BY. If you have an Order By clause inside your OVER clause, that is your O column. If there are other columns in your SELECT list, these are the covering or "C" columns. These "C" columns should be INCLUDED in the index, not necessarily part of the index key.
In addition, if the table is involved in a join or has a WHERE clause, those columns would be at the begining of the index.
Q. How can you tell if the worktable is created in tempdb or in memory?
A. When looking at the execution plan, if you see a TABLE SPOOL operator, the worktable is always created in tempdb. If you see a WINDOW SPOOL operator, then you have to take a look at the STATISTICS IO information. When the logical reads for the worktable are 0, then the worktable has been created in memory. Otherwise, it is created in tempdb.
I will be presenting my Writing Better Queries with T-SQL Window Functions again next month. Think of this presentation as part 1 and my performance presenation as part 2.