April 17, 2012 at 2:52 am
Hi,
In one of our production server I found some missing indexes using certain DMVs. I created those and it resulted performance benefit for search operations but it resulted more blockings to appear. Now I deleted those indexes which I created but still I get blocking( not as much when those indexes exists) and CPU load also increases that the initial postion. I guess the DB should be as it was before but this does not happen. Is it due to the query plan changes or something else? How to get the DB as it was before?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 17, 2012 at 3:14 am
What do you mean by BLOCKING?
According to Microsoft the following is BLOCKING:
Is that what you mean??...
Anyways you can get the Database back to that state if you have a Database backup from before the time you created the indexes.
I don't think whatever is happening is a result of your Indexing. Indexes just take up some disk space. They may or may not be used by the query depending upon how you created them. You said you found missing index values. Maybe you should try Rebuilding or Reorganizing the index for better performance as follows:
Reorganize and Rebuild Indexes
Please do elaborate on what you mean by Blocking.
April 17, 2012 at 6:06 am
I think a bit more information would be useful.
For example, what statements are running on the server when the "blocking" appears?
Have you tried EXEC SP_WHO to view the SIDs?
From my understanding (and I'm open to correction) UPDATE, INSERT and DELETE statements will lock affected rows for the duration of the query and a COMMIT or ROLLBACK; this may in turn cause deadlocks, which in turn will cause the database to appear as if it's performance has degraded.
The RESOURCE MONITOR is another good tool to look at although it will have it's own overhead.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 17, 2012 at 6:08 am
As for getting the DB back, well, that's restoring a backup! You may be able to restore to a point in time depending on the DB's recovery model.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 17, 2012 at 6:48 am
vinu512 (4/17/2012)
What do you mean by BLOCKING?According to Microsoft the following is BLOCKING:
Is that what you mean??...
Anyways you can get the Database back to that state if you have a Database backup from before the time you created the indexes.
I don't think whatever is happening is a result of your Indexing. Indexes just take up some disk space. They may or may not be used by the query depending upon how you created them. You said you found missing index values. Maybe you should try Rebuilding or Reorganizing the index for better performance as follows:
Reorganize and Rebuild Indexes
Please do elaborate on what you mean by Blocking.
Blocking occurs, when a spid is trying to attain a lock on a resource(table) which already has an exclusive lock by another spid.
Regards,
TA
Regards,
SQLisAwe5oMe.
April 17, 2012 at 6:55 am
SQLCrazyCertified (4/17/2012)
vinu512 (4/17/2012)
What do you mean by BLOCKING?According to Microsoft the following is BLOCKING:
Is that what you mean??...
Anyways you can get the Database back to that state if you have a Database backup from before the time you created the indexes.
I don't think whatever is happening is a result of your Indexing. Indexes just take up some disk space. They may or may not be used by the query depending upon how you created them. You said you found missing index values. Maybe you should try Rebuilding or Reorganizing the index for better performance as follows:
Reorganize and Rebuild Indexes
Please do elaborate on what you mean by Blocking.
Blocking occurs, when a spid is trying to attain a lock on a resource(table) which already has an exclusive lock by another spid.
Regards,
TA
Thanx...I know what Blocking means. What you said is also written in the link on Blocking in my last post. I just wanted to make sure what the OP meant and what I was thinking is the same thing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply