Locks on a table with nonclustered index

If i have a query such as
delete from table1
where col1 = 200
Say i had a non clustered index on col1 and thats the only index that i
have...
1) Can the execution plan include a non-clustered index scan if the
selectivity is low ?
2) If it does use a nonclustered index scan and maybe start of with row
locks and then escalates to table lock, how do you find out if the lock is
on the index pages or on the data pages or on both although in sp_lock it
would show an exclusive table lock ?
3) Even it it just row locks, what info can we get from the resource column
in sp_lock to tell us whether a row in the data page or index page is being
locked.. Would it just be data pages or would it be holding multiple row
locks ..some to delete its rows in data pages and some to delete the rows in
its index pages1. I doubt it would do an index scan in this case, but if the selectivity
were high enough, it could do an index seek.
2. I'm not sure exactly what you're asking. If there is a table lock X,
other processes cannot access the indexes on that table, so whether the
index pages are locked is irrelevant.
3. The resource column doesn't directly tell us, but if you have a page
number, you can use dbcc page to tell what kind of page it is. Also, you can
use the indid column, and if it is >1, then the page is from a nonclustered
index. You can have key locks (which are row locks in an index) on both
index keys and rows/keys in the table itself. In fact, if the table has nc
indexes, you'll usually see both.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"sql" <sql@.hotmail.com> wrote in message
news:u47QCA9hDHA.460@.TK2MSFTNGP12.phx.gbl...
> If i have a query such as
> delete from table1
> where col1 = 200
> Say i had a non clustered index on col1 and thats the only index that i
> have...
> 1) Can the execution plan include a non-clustered index scan if the
> selectivity is low ?
> 2) If it does use a nonclustered index scan and maybe start of with row
> locks and then escalates to table lock, how do you find out if the lock is
> on the index pages or on the data pages or on both although in sp_lock it
> would show an exclusive table lock ?
> 3) Even it it just row locks, what info can we get from the resource
column
> in sp_lock to tell us whether a row in the data page or index page is
being
> locked.. Would it just be data pages or would it be holding multiple row
> locks ..some to delete its rows in data pages and some to delete the rows
in
> its index pages
>
>