September 29, 2003 at 10:20 am
You mentioned that this table has 26+ million records in it, and that is receives a
lot of activity. If you are getting a lot of inserts and updates, make sure you DO NOT have a
clustered index on the table. Clustered indexes physically sort data in the table. So everytime
a person adds a record, the system will need to re-organize your data. Ouch! If you have a clustered
index, change it to a NON-CLUSTERED index.
September 29, 2003 at 12:54 pm
quote:
You mentioned that this table has 26+ million records in it, and that is receives alot of activity. If you are getting a lot of inserts and updates, make sure you DO NOT have a
clustered index on the table. Clustered indexes physically sort data in the table. So everytime
a person adds a record, the system will need to re-organize your data. Ouch! If you have a clustered
index, change it to a NON-CLUSTERED index.
Not quite true. What you are saying would only be a factor if the clustered index key were sequential (like an identity column). And even if this were the cause of the bottleneck it would manifest itself as an I/O problem, probably not CPU.
In addition a clustered index does NOT sort the data physically on disk. It sorts it logically within the data pages by rearranging the pointers if necessary, not by moving data. The cost of keeping the data in sort-order on disk would be prohibitave. That is the function of the DBCC DBReindex command.
As for this particular problem, it may not be a "problem" at all. The high CPU utilization stats may just prove that you need to buy faster/more processors...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 29, 2003 at 2:50 pm
quote:
From glively: If you are getting a lot of inserts and updates, make sure you DO NOT have a clustered index on the table. Clustered indexes physically sort data in the table. So everytime a person adds a record, the system will need to re-organize your data. Ouch! If you have a clusteredindex, change it to a NON-CLUSTERED index.
As since 7.0 tables without clustered indexes are intelligent heaps in that they reclaim space, if you have a lot of deletes,insert, it would be a worse idea to NOT have a clustered index.
quote:
From dcpeterson: What you are saying would only be a factor if the clustered index key were sequential (like an identity column).
Wouldn't a clustered index on an identity column be the only time it would NOT re-organize your data? As new inserts will always fall at the bottom page of the leaf level.
quote:
In addition a clustered index does NOT sort the data physically on disk. It sorts it logically within the data pages by rearranging the pointers if necessary, not by moving data.
I assure you a clustered index does reorganize them physically.
From BOL: In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the index key values.
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 30, 2003 at 3:21 am
quote:
quote:
From glively: If you are getting a lot of inserts and updates, make sure you DO NOT have a clustered index on the table. Clustered indexes physically sort data in the table. So everytime a person adds a record, the system will need to re-organize your data. Ouch! If you have a clusteredindex, change it to a NON-CLUSTERED index.
As since 7.0 tables without clustered indexes are intelligent heaps in that they reclaim space, if you have a lot of deletes,insert, it would be a worse idea to NOT have a clustered index.
quote:
From dcpeterson: What you are saying would only be a factor if the clustered index key were sequential (like an identity column).Wouldn't a clustered index on an identity column be the only time it would NOT re-organize your data? As new inserts will always fall at the bottom page of the leaf level.
quote:
In addition a clustered index does NOT sort the data physically on disk. It sorts it logically within the data pages by rearranging the pointers if necessary, not by moving data.
I assure you a clustered index does reorganize them physically.
From BOL: In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the index key values.
Ray Higdon MCSE, MCDBA, CCNA
I agree that the physical order of the rows will be changed. Then, isn't is better to have a non clustered index so that all that physical ordering doesn't take place. We mostly have only inserts in the table and no deletes.
The clustered index is also on a combination of four fields which is also a bad design in this case.
September 30, 2003 at 4:51 am
A composite clustered index is not a good idea if you mainly have inserts as this will cause unpredicatable page splits, a clustered index on an ID column would be fine though. However he mentions long running select statements and in that case a composite non clustered index may help.
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
October 6, 2003 at 3:25 pm
quote:
quote:
I assure you a clustered index does reorganize them physically.
From BOL: In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the index key values.
Ray Higdon MCSE, MCDBA, CCNA
I agree that the physical order of the rows will be changed. Then, isn't is better to have a non clustered index so that all that physical ordering doesn't take place. We mostly have only inserts in the table and no deletes.
The clustered index is also on a combination of four fields which is also a bad design in this case.
That entry in BOL is the source of much confusion. However, what actually happens is that SQL Server keeps the order of the page chain intact through the updating of the linked list, rather than physically ordering the pages on disk. While it may sound like a small distinction, it is an important one.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
October 7, 2003 at 2:33 am
The CPU usage has come down to normal levels now between 20-40%.
There were a few areas which were particularly very slow and all these were using temp tables in the procedures. Also the webpage which was calling these procedures was calling the procedure twice instead of calling it once only because of some incorrect logic.
After removing the temp. tables from the procedures and replacing them with derived tables plus updating the page to call these once only brought the CPU usage down.
Thanks for the help of all the people who participated in this discussion.
August 12, 2008 at 3:49 am
Hi all....
The above methods wats mentioned are good in finding out the CPU utilization.Can anyone advice me how i can monitor all the processes consuming CPU usage continuously (by the use of some job scheduled to execute a procedure which determines the CPU usage)and can keep some threshold like 70% and if CPU usage crosses that threshold I should be able to get an alert.
Thanks in Advance...
Vinay
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply