Row locking and Table locking

Question

Row locking and Table locking

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Scans of the clustered index are table scans. They either indicate that a nonclustered index was not selective enough to be used, or there are nonclustered indexes missing.

How did the current config hold up against the cluster on pk, noncluster on date in your testing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andegre SSCertifiable Points: 5875 More actions · Answer 2

GilaMonster (1/19/2010)
Scans of the clustered index are table scans. They either indicate that a nonclustered index was not selective enough to be used, or there are nonclustered indexes missing.
How did the current config hold up against the cluster on pk, noncluster on date in your testing?

Here's is same days' report for production database:

ServiceMessageLogID (Clustered PrimaryKey) 128585 Seeks, 91063 Scans, 0 Lookups, 668218 Updates, 813272 Records

No other indexes on that table...

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Gregory, may I recommend that you get a performance tuning consultant in for a few hours or a few days to give your system a review? I suspect there are other areas where your company could benefit from that engagement, and you could learn a bit on how to do your own optimizations as well which will pay great ROI over time!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

gregory.anderson (1/19/2010)
GilaMonster (1/19/2010)
Scans of the clustered index are table scans. They either indicate that a nonclustered index was not selective enough to be used, or there are nonclustered indexes missing.
How did the current config hold up against the cluster on pk, noncluster on date in your testing?
Here's is same days' report for production database:
ServiceMessageLogID (Clustered PrimaryKey) 128585 Seeks, 91063 Scans, 0 Lookups, 668218 Updates, 813272 Records
No other indexes on that table...

Comparing a production system to a test system is like comparing apples to boats. There's no meaningful conclusion that can be drawn with different data volumes and different usage patterns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

oskargunn SSCommitted Points: 1951 More actions · Answer 5

What if I have a table that has a clustered index in the primary key and I put a non-clustered index on the date column. Would that also produce a row lock when I delete rows using the data column in the where clause ? or would that still have a table lock ?