Video Screencast Help

Slow performance through MSMQ, SQL Maintenance ?

Created: 25 Apr 2013 • Updated: 21 Jun 2013 | 2 comments
This issue has been solved. See solution.

I've been noticing real slow performance lately of A2 and Storage queue processing. 

I ran the following SQL Query

SELECT OBJECT_NAME(i.object_id) AS TableName, AS TableIndexName, phystat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
'DETAILED') phystat
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = 
WHERE phystat.avg_fragmentation_in_percent > 5 AND phystat.page_count > 
Which produced the following, which I'm assuming is not good and could explain my issue !

JournalDelete PK_JournalDelete 91.08
JournalDelete IX_VaultIdentity 88.91
JournalDelete IX_JournalDelete_IC_DD 74.44
JournalDelete IX_JournalDelete_DR_VI_VID 88.30
JournalDelete IX_JD_ArchivePointIdentity_ItemSeqNo2 76.28
JournalDelete IX_JD_DeletionStatus 92.05
Saveset PK_Saveset 57.85
Saveset UN_SavesetIdentity 21.27
Saveset IX_IdTransaction 99.19
Saveset IX_IdDateTime 99.18
Saveset IX_Saveset_CollectionIdentity 67.89
Saveset IX_Saveset_VaultIdentity 73.72
SavesetProperty PK_SavesetProperty 21.82
SavesetProperty IX_ItemId_Qualifier 22.15
Vault PK_Vault 90.78
Vault UN_VaultIdentity 9.15
Vault IX_Vault_ArchivePointIdentity 66.38
SISPart IX_SISPart_Sharing 99.11
Saveset_SISPart PK_Saveset_SISPart 29.30
Saveset_SISPart IX_Saveset_SISPart_SISPartIdentity 97.01
Collection PK_Collection 34.52
Collection IX_RefCountTotalCountIdPartitionFileFormatHSMType 96.32
Collection IX_RelativeFileName_IdPartition_RefCount 85.10

Due to a mix up between my and our DBA we have had no Maintenance plan for this SQL Server qhich explains the state these are in I guess.

So my question is, is it possible to rebuild the indexes over 30% online, and if so how do I go about it ? If its not possible online, can I target the most urgent tables first and schedule them after putting EV into backup mode ?

Any help appreciated !

Operating Systems:

Comments 2 CommentsJump to latest comment

GertjanA's picture

Hello Paul,

I assume you know this one:

Preferably you run the maintenance when EV is in backup. Depending on the size of your databases, you might have to run maintenance online.

I have a similar problem. We run maintenance on 1 database at the time, 1 per day. That more or less works. As soon as the databases are well maintained, we'll try running it every day.

I suggest you verify your database sizes, and if necessary, schedule a weekend with downtime for EV (shutdown services), then run the maintenance. Take in account logdisk size AND tempdb size!

That will grow quick

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS

Paul.E's picture

Thanks GertjanA,

I've created the maintenance tasks as per the Tech note now and I'm just deciding on when best to run them.

Out of interest if a task runs over into core hours can I manually cancel it without adversely affecting anything ?