Partition Level Locking: Explanations From Outer Space
'It’s not that I don’t like partitioning
It’s just that most of my time talking about it is convincing people not to use it.
They always wanna use it for the wrong reasons, and I can sort of understand why.
Microsoft says you can partition for performance.
Partitioning large tables or indexes can have the following manageability and performance benefits.
How?
You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.
Takeaway: PARTITION EVERYTHING
But…
When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time.
Takeaway: This was written before anyone had a SAN, I guess?
Ooh ooh but also!
In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. This can reduce lock contention on the table.
Takeaway: Except when it causes deadlocks
HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.'...
Trackbacks
Keine Trackbacks
Die Kommentarfunktion wurde vom Besitzer dieses Blogs in diesem Eintrag deaktiviert.
Kommentare
Ansicht der Kommentare: Linear | Verschachtelt