July 23, 2014 at 1:54 am
murnilim9 (7/22/2014)
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,
.
.
.
Beginner coding. There are opportunities here for huge performance improvements with little effort. Fix this first:
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,
Alias 'B' isn't defined.
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 23, 2014 at 3:22 am
murnilim9 (7/23/2014)
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 ??
Again, a fragmented index is NOT NECESSARILY a problem. It is something to look at, but does not automatically require fixing.
You should not need to constantly rebuild indexes, certainly not all of them. If you have a maintenance plan that performs such a task, the first thing I would do is disable the plan. You may very well find that your database continues to perform the same. Only if performance suffers after turning the maintenance plan off should you start looking at these indexes.
If performance suffers, find which indexes are being used and try tuning them, including things like a looser fill factor. Do them one at a time, so you know which change has an impact - if you make many changes at once, you may not know what change had what effect. In fact, you could wind up with ridiculous situations where one change helps and another hurts, giving no change overall, leading to the impression that your changes didn't do anything. You may need to rebuild indexes from time to time, but you should not need it every day, and it should not be your first move.
Constantly rebuilding all indexes is a bit like changing the air in your tires. It's a lot of work to very little effect, and can mask a real problem, like a slow leak, that should be repaired. In general, don't routinely do mass repairs like this, unless you have a real problem and have determined that such a repair is the correct way to deal with it.
July 23, 2014 at 8:24 pm
While I agree with and subscribe to the basic concept that indexes might not need to be defragmented to perform well and that rebuilding an index that is quickly fragmented can actually cause a bit of a slow down because it does remove splits that are like having a low fill factor in all the right places, there are larger problems associated with fragmented indexes... those same page and extent splits. They can take huge amounts of room on disk and in the database. While that won't affect the size of backups, it will affect the size a restore will require and it will affect the size of the mdf file on the database. There's a tradeoff to be had and you need to consider it carefully on the larger indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2014 at 7:59 pm
pdanes (7/23/2014)
murnilim9 (7/23/2014)
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 ??
Again, a fragmented index is NOT NECESSARILY a problem. It is something to look at, but does not automatically require fixing.
You should not need to constantly rebuild indexes, certainly not all of them. If you have a maintenance plan that performs such a task, the first thing I would do is disable the plan. You may very well find that your database continues to perform the same. Only if performance suffers after turning the maintenance plan off should you start looking at these indexes.
If performance suffers, find which indexes are being used and try tuning them, including things like a looser fill factor. Do them one at a time, so you know which change has an impact - if you make many changes at once, you may not know what change had what effect. In fact, you could wind up with ridiculous situations where one change helps and another hurts, giving no change overall, leading to the impression that your changes didn't do anything. You may need to rebuild indexes from time to time, but you should not need it every day, and it should not be your first move.
Constantly rebuilding all indexes is a bit like changing the air in your tires. It's a lot of work to very little effect, and can mask a real problem, like a slow leak, that should be repaired. In general, don't routinely do mass repairs like this, unless you have a real problem and have determined that such a repair is the correct way to deal with it.
Thanks for your response..Much appreciate it !
Btw I am confused about fillfactor now because I rebuild Indexes with Fillfactor 90 last nite but when I check The index properties using SQL Management Studio ( Right click on the index -> properties->Option )
I noticed that " Set Fill Factor = 0% " withot marking in the checkbox ...
So I thought It supposed to be 90% right ?? because I run the rebuild Script using Fill Factor 90 ...
I want to set a low fillfactor number for most frequent update table ...How to set it ?
Thanks
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply