Slow performance through MSMQ, SQL Maintenance ?

[Paul_E]

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,

i.name 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 = 

phystat.index_id

WHERE phystat.avg_fragmentation_in_percent > 5 AND phystat.page_count > 

1000

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 !

[GertjanA · Accepted solution]

Hello Paul,

I assume you know this one: http://www.symantec.com/docs/TECH74666
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

[Paul_E]

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 ?