SQL queries running slowly or stuck after DBCC DBReindex or Alter Index

All,

SQL 2005 sp3, database is about 70gb in size. Once in a while when I reindex all of my indexes in all of my tables, the front end seems to freeze up or run very slowly. These are queries coming from the front end, not stored procedures in sql server. The front end is using JTDS JDBC connection to access the SQL Server. If we stop and restart the web services sending the queries the problem seems to go away. It is my understandning that we have a connection pool in which we re-use connections and dont establish a new connection each time.

This problem does not happen every time we reindex. I have tried both ways with dbcc dbreindex and alter index online = on and sort in tempdb = on.

Any insight into why this problem occurs once in a while and how to prevent this problem would be very helpful.

Thanks in advance,

Gary Abbott

When this happens next time, look into sys.dm_exec_requests to see what is blocking the requests from the clients. The blocking_session_id will indicate who is blocking, and the wait_type and wait_resource will indicate what is blocking on. You can also use the Activity Monitor to the same effect.

On a pre-grown database an online index rebuild will not block normal activity 9select/insert/update/delete). The load on the server may increase as a result of the online index rebuild and this could result in overall slower responses, but should not cause blocking.

If the database is not pre-grown though then the extra allocations of the index rebuild will trigger database growth events, which can be very slow if left default at 10% increments and without instant file initialisation enabled. During a database growth event all activity is frozen in that database, and this may be your problem even if the indexes are rebuilt online. Again, Activity Monitor and sys.dm_exec_requests would both clearly show this as happening.

I did take a look at blocking, these queries were not blocked, they were in a suspended state. I'm a very pro-active database architect, I have more then enough space to reindex in the data and log files. Now here is an ineresting twist, I'm told they have 5 threads open only 4 were frozen, one thread was still working. I believe that when the threads are still active during an index rebuild with the JDBC connections it may not recognize the query plans while the index is dropped or after the index is rebuilt. Is there a way to better deal with these JDBC connections?

DanceswithLightning 2010-02-09 19:18:07

Suspended would still show the suspend reason in wait_type and wait_resource in sys.dm_exec_requests. What are they? About query plans: when the index rebuild starts it will bump up the metadata version of the index, and this will invalidate all existing plans. New queries will have to be recompiled and a new plan generated (since the new plan must update both the old index and the online in-construction one). But all this is handled by the server, there is no reason for clients to block.

Remus Rusanu 2010-02-09 19:31:24

I did not look at the wait types, next time I will. I suspect that the existing JDBC connections were doing a table scan which would account for the poor performance. Is there a better way to handle existing JDBC connections? Even when doing the alter index online it will invalidate the query plans? Thanks for your insight.

DanceswithLightning 2010-02-09 20:11:41

I can honestly say I've never had this issue before, but I am certain we were full Microsoft shops using Microsoft drivers. Thats why I'm thinking it might be JDBC driver related. As stated earlier, if I have them stop and restart their webservices, everything flys along after the restart.

DanceswithLightning 2010-02-09 20:13:38

Invalidating a query plan cannot happen in the middle of execution. If a client is doing a scan, then the plan it executes has a Sch-S lock on the metadata of the table, and this lock prevents the ALTER INDEX from proceeding. In order to start the online index operation the ALTER *must* otain the Sch-M lock (schema modification) and this lock guarantees these is no other thread doind any activity in the table. When the next client query lands, it will find the table already modified and will get a new plan.

Remus Rusanu 2010-02-09 21:00:13

ansaurus

tags:

views:

answers:

SQL queries running slowly or stuck after DBCC DBReindex or Alter Index

related questions