July 21, 2014 at 1:33 am
Hi all ,
I have just created an index but it grows so fast... in 3 hours it becomes 95% fragmented ... but the other indexes are fine ..
What about if i create a SPECIAL maintenance job for that index ONLY to rebuild or reorganize ( depends on the % fragmentation ) every 2 hours ??
Thanks
Cheers,
Me
July 21, 2014 at 1:50 am
If you are loading large data in that particular table where you have created the index, then you can use disable and restore approach which can increase your performance. When you created the index what FILLFACTOR did you mentioned? if you didn't explicitly set it, i would recommend you should take a look at this.
2ndly if you want to go with rebuild/reorganize index, you can use the Rebuild Online option to yes, and Sort_Tempdb option to YES, because it will give you a parallel execution and index could be built faster.
July 21, 2014 at 5:07 pm
twin.devil (7/21/2014)
If you are loading large data in that particular table where you have created the index, then you can use disable and restore approach which can increase your performance. When you created the index what FILLFACTOR did you mentioned? if you didn't explicitly set it, i would recommend you should take a look at this.2ndly if you want to go with rebuild/reorganize index, you can use the Rebuild Online option to yes, and Sort_Tempdb option to YES, because it will give you a parallel execution and index could be built faster.
thanks for your response... I would like to know what do you mean by " you can use disable and restore approach which can increase your performance" ?
I created that index without mention the fillfactor but I am going to rebuild it using fillfactor = 80 ..What do you think ?
I cant use rebuild online because my sql server edition is standard...
July 21, 2014 at 8:08 pm
murnilim9 (7/21/2014)
twin.devil (7/21/2014)
If you are loading large data in that particular table where you have created the index, then you can use disable and restore approach which can increase your performance. When you created the index what FILLFACTOR did you mentioned? if you didn't explicitly set it, i would recommend you should take a look at this.2ndly if you want to go with rebuild/reorganize index, you can use the Rebuild Online option to yes, and Sort_Tempdb option to YES, because it will give you a parallel execution and index could be built faster.
thanks for your response... I would like to know what do you mean by " you can use disable and restore approach which can increase your performance" ?
I created that index without mention the fillfactor but I am going to rebuild it using fillfactor = 80 ..What do you think ?
I cant use rebuild online because my sql server edition is standard...
What I think is that you need to first determine how the index is being used. If it's used for single row lookups, fragmentation almost won't matter.
Also, what is the first column of the index based on? If, for example, it's based on a column that only contains, say, 2 to some other low number of values and the table has a large number of new rows being added or that leading column suffers a lot of changes, then the index is going to get absolutely hammered in a very short period of time. Like I said, though... that might not matter.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2014 at 11:58 pm
murnilim9 (7/21/2014)
twin.devil (7/21/2014)
If you are loading large data in that particular table where you have created the index, then you can use disable and restore approach which can increase your performance. When you created the index what FILLFACTOR did you mentioned? if you didn't explicitly set it, i would recommend you should take a look at this.2ndly if you want to go with rebuild/reorganize index, you can use the Rebuild Online option to yes, and Sort_Tempdb option to YES, because it will give you a parallel execution and index could be built faster.
thanks for your response... I would like to know what do you mean by " you can use disable and restore approach which can increase your performance" ?
I created that index without mention the fillfactor but I am going to rebuild it using fillfactor = 80 ..What do you think ?
I cant use rebuild online because my sql server edition is standard...
If your index is getting trashed because of numerous changes, you might try a smaller fill factor, to create room for more changes without causing page splits. Try a very small one at first and see what happens, maybe like 20%. Your index will take up more disk space, but there will be more room for changes in place, instead of having to add them at the end.
As for the disable and restore, it is possible to 'turn off' an index, but leave it in place. If there are periods of time that are heavy on updates, you might turn off the index for that time, then turn it back on (rebuild it) when the database is more used for lookups. That way the fragmentation is less, since all the inserts and changes to the index are done at once, instead of on every user action.
July 22, 2014 at 12:15 am
pdanes (7/21/2014)
murnilim9 (7/21/2014)
twin.devil (7/21/2014)
If you are loading large data in that particular table where you have created the index, then you can use disable and restore approach which can increase your performance. When you created the index what FILLFACTOR did you mentioned? if you didn't explicitly set it, i would recommend you should take a look at this.2ndly if you want to go with rebuild/reorganize index, you can use the Rebuild Online option to yes, and Sort_Tempdb option to YES, because it will give you a parallel execution and index could be built faster.
thanks for your response... I would like to know what do you mean by " you can use disable and restore approach which can increase your performance" ?
I created that index without mention the fillfactor but I am going to rebuild it using fillfactor = 80 ..What do you think ?
I cant use rebuild online because my sql server edition is standard...
If your index is getting trashed because of numerous changes, you might try a smaller fill factor, to create room for more changes without causing page splits. Try a very small one at first and see what happens, maybe like 20%. Your index will take up more disk space, but there will be more room for changes in place, instead of having to add them at the end.
As for the disable and restore, it is possible to 'turn off' an index, but leave it in place. If there are periods of time that are heavy on updates, you might turn off the index for that time, then turn it back on (rebuild it) when the database is more used for lookups. That way the fragmentation is less, since all the inserts and changes to the index are done at once, instead of on every user action.
thanks for your response 🙂 appreciate it...
btw here is the sproc that use that index :
Create PROCEDURE [dbo].G]
@b-2 INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NoValue uniqueidentifier
SET @NoValue = NewId()
-- Return all data in Table R & their required M that need an update.
SELECTr.Id, r.M, r.D , m.Ma
FROMr WITH (NOLOCK)
INNER JOIN rs WITH(NOLOCK) ON rs.Id = r.Id
LEFT OUTER JOIN m WITH(NOLOCK) on r.M = m.Man
WHERE
(
(r.RNT = 1) AND
(r.Man IS NOT NULL) AND
(DATEDIFF(DAY, rs.LM, GETDATE()) <= @b-2)
)
END
the index is :
create nonclustered index IX_A on dbo.R
(RNT,Man)
include (Id,M,D)
Any comments are really appreciate it ....
I already set the schedule to rebuild this index along with other index in the database with fill factor 90, time : 2 AM
it means after running that job , I still need to rebuild special for that index ONLY at 4 AM probably with very low fillfactor ( maybe 20 or 30 ) ??
Cheers
July 22, 2014 at 12:29 am
I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.
Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?
By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.
July 22, 2014 at 1:00 am
twin.devil (7/22/2014)
I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?
By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.
Absolutely - the first question really should be: Do you even need this index? Here is an article on that to get started: http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/
If it is being used rarely, or perhaps not at all, you should probably simply drop it and forget it.
How big is the index? Small ones can show high fragmentation quickly, even immediately after being rebuilt. The algorithm that calculates fragmentation is not very accurate when the index is small. I experimented with that a few weeks back and found a small index that showed around 40% fragmentation IMMEDIATELY after being rebuilt, before ANY activity had taken place.
If it is being used, and it is substantial in size, look at the queries that are using it. Are they being hampered by the fragmentation? If not, let it be. A fragmented index is something to consider, but not -necessarily- a hindrance to performance.
Finally, if the queries are dragging, can they be re-coded to avoid that index? Can you add or remove fields from the index to make it behave better? Look at included columns - would they serve just as well, perhaps on another index, and avoid the problematic one? Could the order of columns in the index be changed?
There are many possibilities for tweaking, but you should first investigate whether you actually have a problem.
July 22, 2014 at 2:51 am
pdanes (7/22/2014)
twin.devil (7/22/2014)
I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?
By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.
Absolutely - the first question really should be: Do you even need this index? Here is an article on that to get started: http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/
If it is being used rarely, or perhaps not at all, you should probably simply drop it and forget it.
How big is the index? Small ones can show high fragmentation quickly, even immediately after being rebuilt. The algorithm that calculates fragmentation is not very accurate when the index is small. I experimented with that a few weeks back and found a small index that showed around 40% fragmentation IMMEDIATELY after being rebuilt, before ANY activity had taken place.
If it is being used, and it is substantial in size, look at the queries that are using it. Are they being hampered by the fragmentation? If not, let it be. A fragmented index is something to consider, but not -necessarily- a hindrance to performance.
Finally, if the queries are dragging, can they be re-coded to avoid that index? Can you add or remove fields from the index to make it behave better? Look at included columns - would they serve just as well, perhaps on another index, and avoid the problematic one? Could the order of columns in the index be changed?
There are many possibilities for tweaking, but you should first investigate whether you actually have a problem.
Btw I just wonder how to set the fillfactor it self .. I run a script that will rebuild all the indexes in all tables in a certain databse with fillfactor = 90 , for example ..but as a matter of fact ..there are some tables that are often updated but some are only used by READ
so i think we cant set all indexes with the same fillfactor ..dont you think so ?
so I just wonder that i can get a script that can solve my problem ?? ...
any idea ?
July 22, 2014 at 2:57 am
murnilim9 (7/22/2014)
pdanes (7/22/2014)
twin.devil (7/22/2014)
I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?
By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.
Absolutely - the first question really should be: Do you even need this index? Here is an article on that to get started: http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/
If it is being used rarely, or perhaps not at all, you should probably simply drop it and forget it.
How big is the index? Small ones can show high fragmentation quickly, even immediately after being rebuilt. The algorithm that calculates fragmentation is not very accurate when the index is small. I experimented with that a few weeks back and found a small index that showed around 40% fragmentation IMMEDIATELY after being rebuilt, before ANY activity had taken place.
If it is being used, and it is substantial in size, look at the queries that are using it. Are they being hampered by the fragmentation? If not, let it be. A fragmented index is something to consider, but not -necessarily- a hindrance to performance.
Finally, if the queries are dragging, can they be re-coded to avoid that index? Can you add or remove fields from the index to make it behave better? Look at included columns - would they serve just as well, perhaps on another index, and avoid the problematic one? Could the order of columns in the index be changed?
There are many possibilities for tweaking, but you should first investigate whether you actually have a problem.
Btw I just wonder how to set the fillfactor it self .. I run a script that will rebuild all the indexes in all tables in a certain databse with fillfactor = 90 , for example ..but as a matter of fact ..there are some tables that are often updated but some are only used by READ
so i think we cant set all indexes with the same fillfactor ..dont you think so ?
so I just wonder that i can get a script that can solve my problem ?? ...
any idea ?
Certainly, you should not set all fill factors the same, especially if you are having trouble with one. The default fill factor is a best guess, a starting point, but only that. If your table is primarily for reading, you can tighten it up to save space. If you have lots of insert and update activity, you should leave more room, to cut down on fragmentation. As always, experimentation is in order. Try various percentages and see what you get.
July 22, 2014 at 3:13 am
-- change the index to support a nested loops join between rs and r
-- with seeks to r on id and residual predicate of RNT and Man:
create nonclustered index IX_A on dbo.R
(Id,RNT,Man)
include (M,D)
-- change the non-sargable predicate in your WHERE clause
AND DATEDIFF(DAY, rs.LM, GETDATE()) <= @b-2
-- to something like this:
AND rs.LM > DATEADD(DAY,0-@B,GETDATE())
-- with no sample data to code against, you will have to tweak this yourself.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2014 at 7:24 pm
pdanes (7/22/2014)
murnilim9 (7/22/2014)
pdanes (7/22/2014)
twin.devil (7/22/2014)
I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?
By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.
Absolutely - the first question really should be: Do you even need this index? Here is an article on that to get started: http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/
If it is being used rarely, or perhaps not at all, you should probably simply drop it and forget it.
How big is the index? Small ones can show high fragmentation quickly, even immediately after being rebuilt. The algorithm that calculates fragmentation is not very accurate when the index is small. I experimented with that a few weeks back and found a small index that showed around 40% fragmentation IMMEDIATELY after being rebuilt, before ANY activity had taken place.
If it is being used, and it is substantial in size, look at the queries that are using it. Are they being hampered by the fragmentation? If not, let it be. A fragmented index is something to consider, but not -necessarily- a hindrance to performance.
Finally, if the queries are dragging, can they be re-coded to avoid that index? Can you add or remove fields from the index to make it behave better? Look at included columns - would they serve just as well, perhaps on another index, and avoid the problematic one? Could the order of columns in the index be changed?
There are many possibilities for tweaking, but you should first investigate whether you actually have a problem.
Btw I just wonder how to set the fillfactor it self .. I run a script that will rebuild all the indexes in all tables in a certain databse with fillfactor = 90 , for example ..but as a matter of fact ..there are some tables that are often updated but some are only used by READ
so i think we cant set all indexes with the same fillfactor ..dont you think so ?
so I just wonder that i can get a script that can solve my problem ?? ...
any idea ?
Certainly, you should not set all fill factors the same, especially if you are having trouble with one. The default fill factor is a best guess, a starting point, but only that. If your table is primarily for reading, you can tighten it up to save space. If you have lots of insert and update activity, you should leave more room, to cut down on fragmentation. As always, experimentation is in order. Try various percentages and see what you get.
Hmm the problem is I have a lot of tables ...so it will take a long time to set each table with certain number of fillfactor ... How is that ?
Hmm Have you used Ola.hallengren ' script ? I heard it is very good but when I checked the script ..it is so complicated and not easy to understand ... so that's why i havent used that script yet ....
Btw My team just reported last night that There was high load of CPU ..around 99% ... Would it be due to fillfactor = 90 to ALL the TABLES ??
July 22, 2014 at 11:05 pm
ChrisM@Work (7/22/2014)
-- change the index to support a nested loops join between rs and r-- with seeks to r on id and residual predicate of RNT and Man:
create nonclustered index IX_A on dbo.R
(Id,RNT,Man)
include (M,D)
-- change the non-sargable predicate in your WHERE clause
AND DATEDIFF(DAY, rs.LM, GETDATE()) <= @b-2
-- to something like this:
AND rs.LM > DATEADD(DAY,0-@B,GETDATE())
-- with no sample data to code against, you will have to tweak this yourself.
HI ..thx for your input ..much appreciate it !!
I have 1 heavy CPU time Sproc with high logical read on worktable as below ( any idea how to rewrite the query )
CREATE PROCEDURE [dbo].[GR]
@GN INT
AS
BEGIN
DECLARE @False bit, @True bit
SET @False = 0
SET @True = 1
SELECTR.RId,
iB.*,
CS.ContainerId,
CS.REPosition 'Position',
CS.StopIdStatus,
CS.[Route],
CS.LastConnected,
CS.LastStatusProcessed,
CS.LMR,
R.LT as LastTransactionBatch,
CS.LastStatusCreationTime,
B.RegNumber,
PJN.[Count] [PendingJobNumbers],
IssMarkedDoNotExport.[Count] [IssMarkedDoNotExport],
IssNotApproved.[Count] [IssNotApproved],
IssExported.[Count] [IssExported],
B.IsActive 'IsBActive',
GD.GName,
GD.GId,
G.GCode,
Iss.OutstandingIss,
LatestIsWithJobNumber.JobNumber,
OldestIs.TimeStatusStored 'OldestIs',
LatestInShiftMessage.IsInShift 'IsInShift',
(SELECT COUNT(*) FROM SMDE S INNER JOIN ROS I ON S.REOutstandingIsId = I.Id WHERE I.RId = R.RId AND DoNotExport = 0 AND IsExported = 0 AND Approved = 0) ReadyForApproval,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsOos = 1)
THEN @True ELSE @False END AS PossessOosFault,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsREFault = 1)
THEN @True ELSE @False END AS PossessREFault,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsSoftwareIs = 1)
THEN @True ELSE @False END AS PossessSoftwareIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsRemoteCommsIs = 1)
THEN @True ELSE @False END AS PossessRemoteCommsIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsLocalCommsIs = 1)
THEN @True ELSE @False END AS PossessLocalCommsIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsTimeSyncIs = 1)
THEN @True ELSE @False END AS PossessTimeSyncIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsDataTransferIs = 1)
THEN @True ELSE @False END AS PossessDataTransferIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsLocationInfoSuspect = 1)
THEN @True ELSE @False END AS PossessLocationInfoSuspect,
/* From 9459-67043 */
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 0)
THEN @True ELSE @False END AS PossessOysterNotOperationalIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 2)
THEN @True ELSE @False END AS PossessEmvNotOperationalIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 3)
THEN @True ELSE @False END AS PossessAutonomousModeIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 31)
THEN @True ELSE @False END AS PossessInvalidDateTimeIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 58)
THEN @True ELSE @False END AS PossessEmergencyAcceptanceIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 1)
THEN @True ELSE @False END AS PossessItsoNotOperationalIs,
OldestNotRaisedNotPendingSendableIs.OldestNotRaisedNotPendingSendableIsMins,
DT.DeviceType,
DT.DisplayName [DeviceTypeName]
FROMREs R
LEFT JOINRef_DeviceTypes DT ON DT.DeviceType = R.DeviceType
OUTER APPLY(SELECT TOP 1 *
FROM RECS
WHERE RId = R.RId) CS
OUTER APPLY (SELECT TOP 1 B.*
FROM BCurrentREs BCR
INNER JOIN Bes B ON B.Id = BCR.BId
WHERE BCR.RId = R.RId) B
OUTER APPLY(SELECT TOP 1 *
FROM Gs
WHERE GN = R.GN) G
OUTER APPLY (SELECT TOP 1 *
FROM GDs
WHERE GId = R.GN) GD
OUTER APPLY (SELECT COUNT(*) OutstandingIss
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
WHERE I.RId = R.RId) Iss
OUTER APPLY (SELECT TOP 1 CASE WHEN H.IsSet = 1 THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS IsInShift
FROM RSH H
INNER JOIN RSSRSS ON RSS.BitRef = H.BitRef
WHERE H.RId = R.RId
AND RSS.IsInShift = 1
ORDER BY H.UtsDate DESC, H.UtsTime DESC, H.Seconds DESC, H.MessageSequenceNumber DESC) LatestInShiftMessage
OUTER APPLY (SELECT TOP 1 I.*, H.TimeStatusStored
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
WHERE I.RId = R.RId AND I.JobNumber IS NOT NULL
ORDER BY H.TimeStatusStored DESC) LatestIsWithJobNumber
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND (I.JobNumber IS NULL OR I.JobNumber = '') AND X.Approved = 1) PendingJobNumbers
OUTER APPLY (SELECT TOP 1 DATEDIFF(MI, H.TimeStatusStored, GETDATE()) OldestNotRaisedNotPendingSendableIsMins
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND (I.JobNumber IS NULL OR I.JobNumber = '')
AND X.Approved = 0 AND X.DoNotExport = 0 AND LatestIsWithJobNumber.JobNumber IS NULL
AND PendingJobNumbers.[Count] = 0
ORDER BY DATEDIFF(MI, H.TimeStatusStored, GETDATE()) DESC) OldestNotRaisedNotPendingSendableIs
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND X.DoNotExport = 1) IssMarkedDoNotExport
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND X.Approved = 0 AND X.DoNotExport = 0) IssNotApproved
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND X.IsExported = 1) IssExported
OUTER APPLY (SELECT TOP 1 iB.[TimeStamp] [iBTimeStamp], iB.TripType, TripType.[Description] [TripTypeDescription]
FROM [iBStatus] iB
INNER JOIN Ref_TripType TripType ON iB.TripType = TripType.Id
WHERE iB.RId = R.RId
ORDER BY iB.TimeStamp DESC) iBDetails
OUTER APPLY (SELECT TOP 1 I.*, H.TimeStatusStored FROM ROS I INNER JOIN RSH H ON I.RSHId = H.Id WHERE I.RId = R.RId ORDER BY H.TimeStatusStored ASC) OldestIs
WHERER.GN = @GN OR (@GN = -1 AND G.GCode IS NULL)
ORDER BY
CASE WHEN OldestIs.TimeStatusStored IS NULL THEN 1 ELSE 0 END,
OldestIs.TimeStatusStored
END
July 23, 2014 at 12:01 am
murnilim9 (7/22/2014)
pdanes (7/22/2014)
murnilim9 (7/22/2014)
pdanes (7/22/2014)
twin.devil (7/22/2014)
I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?
By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.
Absolutely - the first question really should be: Do you even need this index? Here is an article on that to get started: http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/
If it is being used rarely, or perhaps not at all, you should probably simply drop it and forget it.
How big is the index? Small ones can show high fragmentation quickly, even immediately after being rebuilt. The algorithm that calculates fragmentation is not very accurate when the index is small. I experimented with that a few weeks back and found a small index that showed around 40% fragmentation IMMEDIATELY after being rebuilt, before ANY activity had taken place.
If it is being used, and it is substantial in size, look at the queries that are using it. Are they being hampered by the fragmentation? If not, let it be. A fragmented index is something to consider, but not -necessarily- a hindrance to performance.
Finally, if the queries are dragging, can they be re-coded to avoid that index? Can you add or remove fields from the index to make it behave better? Look at included columns - would they serve just as well, perhaps on another index, and avoid the problematic one? Could the order of columns in the index be changed?
There are many possibilities for tweaking, but you should first investigate whether you actually have a problem.
Btw I just wonder how to set the fillfactor it self .. I run a script that will rebuild all the indexes in all tables in a certain databse with fillfactor = 90 , for example ..but as a matter of fact ..there are some tables that are often updated but some are only used by READ
so i think we cant set all indexes with the same fillfactor ..dont you think so ?
so I just wonder that i can get a script that can solve my problem ?? ...
any idea ?
Certainly, you should not set all fill factors the same, especially if you are having trouble with one. The default fill factor is a best guess, a starting point, but only that. If your table is primarily for reading, you can tighten it up to save space. If you have lots of insert and update activity, you should leave more room, to cut down on fragmentation. As always, experimentation is in order. Try various percentages and see what you get.
Hmm the problem is I have a lot of tables ...so it will take a long time to set each table with certain number of fillfactor ... How is that ?
Hmm Have you used Ola.hallengren ' script ? I heard it is very good but when I checked the script ..it is so complicated and not easy to understand ... so that's why i havent used that script yet ....
Btw My team just reported last night that There was high load of CPU ..around 99% ... Would it be due to fillfactor = 90 to ALL the TABLES ??
I've not used Ola's script, although I remember looking at it a while back. It was interesting, but I didn't need it for anything I was doing.
You shouldn't need to rebuild all your indexes all the time. If you do, they're not designed properly. Again, I would investigate whether this one index you're so worried about is actually causing any problems. If it turns out that it is, in fact, the cause of a performance issue, work on that one, not all of them. Also, once you set a fill factor on a table, a script to rebuild the index that does not specifically set the fill factor should keep the fill factor that was previously set. So, if this index really is a problem, try setting the fill factor manually to something loose, like the previously suggested 20%, and run your rebuild script. Make sure your rebuild script ONLY rebuilds the index, but does NOT reset the fill factor itself.
A high CPU load can be caused by any number of things, sometimes quite obscure. I had a case last year when I was running a Python script against a database, with some file paths. I inadvertantly mixed up slashes, and put the wrong direction slash into a file path specification, and the machine had a fit. Dual CPUs, both tached at well over 90%, with occasional flatlines to 100%, for about five minutes. Eventually, something timed out and the load dropped back to normal. I wrestled with that for around a week, and never did solve it - a friend from another country that was helping on the project finally caught it for me. As soon as I put in the correct slashes, everything worked fine. From here, there is no telling what is causing your high workload, but an index rebuild is a lot of work - it could very well be that.
July 23, 2014 at 12:19 am
pdanes (7/23/2014)
murnilim9 (7/22/2014)
pdanes (7/22/2014)
murnilim9 (7/22/2014)
pdanes (7/22/2014)
twin.devil (7/22/2014)
I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?
By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.
Absolutely - the first question really should be: Do you even need this index? Here is an article on that to get started: http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/
If it is being used rarely, or perhaps not at all, you should probably simply drop it and forget it.
How big is the index? Small ones can show high fragmentation quickly, even immediately after being rebuilt. The algorithm that calculates fragmentation is not very accurate when the index is small. I experimented with that a few weeks back and found a small index that showed around 40% fragmentation IMMEDIATELY after being rebuilt, before ANY activity had taken place.
If it is being used, and it is substantial in size, look at the queries that are using it. Are they being hampered by the fragmentation? If not, let it be. A fragmented index is something to consider, but not -necessarily- a hindrance to performance.
Finally, if the queries are dragging, can they be re-coded to avoid that index? Can you add or remove fields from the index to make it behave better? Look at included columns - would they serve just as well, perhaps on another index, and avoid the problematic one? Could the order of columns in the index be changed?
There are many possibilities for tweaking, but you should first investigate whether you actually have a problem.
Btw I just wonder how to set the fillfactor it self .. I run a script that will rebuild all the indexes in all tables in a certain databse with fillfactor = 90 , for example ..but as a matter of fact ..there are some tables that are often updated but some are only used by READ
so i think we cant set all indexes with the same fillfactor ..dont you think so ?
so I just wonder that i can get a script that can solve my problem ?? ...
any idea ?
Certainly, you should not set all fill factors the same, especially if you are having trouble with one. The default fill factor is a best guess, a starting point, but only that. If your table is primarily for reading, you can tighten it up to save space. If you have lots of insert and update activity, you should leave more room, to cut down on fragmentation. As always, experimentation is in order. Try various percentages and see what you get.
Hmm the problem is I have a lot of tables ...so it will take a long time to set each table with certain number of fillfactor ... How is that ?
Hmm Have you used Ola.hallengren ' script ? I heard it is very good but when I checked the script ..it is so complicated and not easy to understand ... so that's why i havent used that script yet ....
Btw My team just reported last night that There was high load of CPU ..around 99% ... Would it be due to fillfactor = 90 to ALL the TABLES ??
I've not used Ola's script, although I remember looking at it a while back. It was interesting, but I didn't need it for anything I was doing.
You shouldn't need to rebuild all your indexes all the time. If you do, they're not designed properly. Again, I would investigate whether this one index you're so worried about is actually causing any problems. If it turns out that it is, in fact, the cause of a performance issue, work on that one, not all of them. Also, once you set a fill factor on a table, a script to rebuild the index that does not specifically set the fill factor should keep the fill factor that was previously set. So, if this index really is a problem, try setting the fill factor manually to something loose, like the previously suggested 20%, and run your rebuild script. Make sure your rebuild script ONLY rebuilds the index, but does NOT reset the fill factor itself.
A high CPU load can be caused by any number of things, sometimes quite obscure. I had a case last year when I was running a Python script against a database, with some file paths. I inadvertantly mixed up slashes, and put the wrong direction slash into a file path specification, and the machine had a fit. Dual CPUs, both tached at well over 90%, with occasional flatlines to 100%, for about five minutes. Eventually, something timed out and the load dropped back to normal. I wrestled with that for around a week, and never did solve it - a friend from another country that was helping on the project finally caught it for me. As soon as I put in the correct slashes, everything worked fine. From here, there is no telling what is causing your high workload, but an index rebuild is a lot of work - it could very well be that.
Btw I have just deleted that index that I am worried about LOL ...
I am working with other indexes that got fragmented fast as well .. there are 5 indexes in a database ..hmm I need to run the index maintenance script everynite for rebuild/reorganize ..otherwise The Solarwind ( the third party tool to monitor SQL performance ) will show that the indexes are in critical level ( above 90% fragmented ) if I dont rebuild/reorganize everynite...
So do u think it is OK to specially rebuild those fast fragmented indexes with low fill factor , 3 hours after the maintenance plan running for rebuild/reorganize for the whole indexes ??
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply