Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiloh
Starting Member
48 Posts |
 Posted - 2007-09-14 : 13:47:43
|
| Hi allWe are still testing SQL 2005. We changed all the CREATE INDEX jobs to ALTER INDEX. we ran the index jobs and noticed a lot of blocking. The jobs eventually completed successfully though. The spids were blocking themselves. And this was on and off. I qould query sysprocesses and see the blocking intermittently. I didnt notice this when we run the same indexing jobs in 2000. Is this the way ALTER INDEX works in 2005 or is this something we need to be concerned about? We have build 3186. I need to mention that the jobs finished rather quicker as compared to 2000. And the spids were blocking themselves not other spids.Thanks for the help.Don |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-14 : 13:51:00
|
| If it is only blocking itself, then that is normal. You should not worry about anything blocking itself.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
shiloh
Starting Member
48 Posts |
Posted - 2007-09-14 : 13:58:19
|
| Hi TaraThats what I thought too.. but my manager seems to be concerned.. is there any article/documentation anywhere that can support this? You know how managers are.. right?Don |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-14 : 14:05:22
|
| There is a ton of information on this site, I believe from Paul Randall. This all started with SQL Server 2000 service pack 4. They increased the visibility of blocking and now you'll see spids blocking themselves. He said this is normal and has always been present, it's just that sp4 now showed it to us. I assume that they put this same level of visibility into SQL Server 2005.I don't have a specific link for this information, but I know there was a lengthy thread on it here and we got answers from MS directly.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
shiloh
Starting Member
48 Posts |
Posted - 2007-09-14 : 14:15:31
|
| hmm will try to search for the same in these forums.. thanks once again Tara.. |
|
|
|
|
|
|
ALTER INDEX Blocking