July 30, 2013 at 6:11 pm
Dear Expert,
I would like to know if there is a query to findout if index is current rebuilding/reorganizing for table X so that I can make the program wait for it to complete before issue certain command against the table for example SqlBulkCopy as SqlBulkCopy is fails consistently if re-index is happening at the same time. I know drop/recreate indexes would be a work around but with the way my application works, I cant afford to drop indexes or stop the re-indexing process either.
Thanks for your help!
July 31, 2013 at 1:00 pm
You could do something with sys.dm_exec_requests and look for the ALTER INDEX command as a start. You could probably drill down more from that to get to the object that is being rebuilt.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2013 at 2:12 pm
I found the script below while googling it sort of working but it is really slow sometimes the result doesn't come back until the index rebuild is completed, so it is not effective for what it is written to do. Anyway I am looking at another way to handle my problem.
Thanks
;WITH cte AS
(
SELECT object_id,index_id,partition_number,rows,
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
--WHERE object_name(object_id)='YourTable'
--AND index_id < 10
)
SELECT
--object_name(cur.object_id) as TableName,
cur.index_id,
--cur.partition_number,
PrecentDone =
CASE WHEN pre.rows = 0 THEN 0
ELSE ((cur.rows * 100.0) / pre.rows)
END
--,pre.rows - cur.rows as MissingRows
FROM cte as cur
INNER JOIN cte as pre on (cur.object_id = pre.object_id)
AND (cur.index_id = pre.index_id)
AND (cur.partition_number = pre.partition_number)
AND (cur.rn = pre.rn +1)
--ORDER BY 4
July 31, 2013 at 3:39 pm
If I remember correctly from a previous thread, your application is doing all this reindexing craze. Thus, you are in control. Make the reindex operation take out an exclusive application lock for the table it is reindexing. Then do the same for your SqlBulkCopy operation. This will prevent both operations running simultaneousely.
Look up sp_getapplock and sp_releaseapplock in Books Online.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply