How can re-indexing unblock a Server
-
Do you have ideas on how an administrator issuing a command to re-index all tables in a database could actually use that method to free up resources on a SQL Server that has been slowing down and who'se CPU utilization is nearing 100%?
It seems so strange to me, you'd think that re-indexing all tables would actually make things worse but its actaully acting as some kind of release valve. Unfortunately because its working I feel I should have a plausible explanation as to why and give him a better alternative.
All I can figure is that its doing table locks and causing deadlocks on stalled processes and they in turn get killed by deadlock processing. Any other ideas?
Thanks
...Ray
-
- wich version of sqlserver are you using ?
- does the table have a clustering index ? (indexid = 1)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I'm found this problem too. I use SQL Enterprise 2000 on Window 2000 Server.
-
When the problem occurs, could you run a profiler trace and turn on LOTS of stuff (eg, catching exceptions, outputting all sql statements executed within SPs, etc - both start & finish of statements). Then start the reindex process and see what logins might get killed off or deadlocks broken, etc... That should give some info as to what is causing it and why the reindex fixes the problem.
-
Do you mean after the reindex or during?
And is it DBREINDEX or INDEXDEFRAG he is using?
If it frees up resources AFTER and DBREINDEX then it's because the table is contiguous and optimised and statistics updated etc
INDEXDEFRAG will also improve the table layout, but is an on-line operation.
Do you actually get deadlocks? or are you guessing?
-
I'm guessing on the dealock thing. I'm not sure where I would get that info or whether its logged at all.
The performance is better after the re-indexing. I'm now looking for operations in the system that would cause important tables to get totally fragmented.
Oh, when are statistics updated? Are they updated after and index re-build or do you have to explicitly update statistics?
-
If your performance is better after, then your data is fragmented on disk and logically. Look at DBCC SHOWCONTIG. Hence your server gets better afterwards.
Deadlocks will be in the SQL Server event log. Error 1205.
Stats are updated by DBREINDEX. I honestly dont know about INDEXDEFRAG.
Oh, all statisitics are updated, not just stats across index columns
Use this script:
DBCC DBREINDEX ('table')
GO
SELECT
OBJECT_NAME([id]), [name], STATS_DATE([id], [indid]) AS StatDate
FROM
dbo.sysindexes
WHERE
INDEXPROPERTY([id], [name], 'IsStatistics') = 1
ORDER BY
StatDate DESC
GO
You will see what I mean.
Inserts, updates and deletes will fragment your tables as rows are added and deleted, and data pages full up and gaps are left, and updates change the row location (if clustered key changes)
Don't look for it, it happens, just run DBCC DBREINDEX every night/weekly etc.
-
if you want deadlock-explanation (involved spids and statements) add these parameters to your sqlserver startup parameters :
-T1204
and
-T3605
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Are you sure about 3605? Send trace output to the error log?
-T1205 and -T1204 are the usual ones for extra deadlock info.
-
That's how it works for me
All my sql7 and sql2k servers have these parameters.
I did forget to mention -T3605 makes sqlserver write this info in it's errorlog
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply