Compared to other performance issues, what is the performance impact of secondary extents, tables indexes? Is the impression correct that Load/Reorg leaves the free space (PCTFREE and FREE PAGES) unused and leave unused space at the end of the Primary Space (PQTY) free?

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

When inserts have filled near free space, they insert to far free space then to the end of PQTY. Then, they have to allocate SQTY and insert to it.

Is the impression correct that inserts to near and far free space are roughly the same for tables and indexes? But inserts to the end of Index PQTY and SQTY degrade performance more than inserts to PQTY and SQTY of tables? (Indexes are on a random dumb key, many millions of rows.)

Well, let me provide some general guidance. Secondary extents are probably at the bottom in terms of performance impact. There are many more issues that have greater importance such as proper indexing, efficient SQL, memory usage (buffer pools), database organization, workload balancing and so on.

That being said, extents will negatively impact performance. The chain from the primary to the extent must be read to get to the extent -- and that is additional work that does not need to be done when extents do not exist. But don't go overboard worrying about extents. The impact is small and I wouldn't worry about them until there were dozens (as opposed to just a few).

In terms of INSERTs, when a secondary extent must be created for the INSERT, performance will suffer.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy