Lowell (12/27/2013)

---

Banks1850 (12/27/2013)

---

statistics are updated daily and automatically for all tables right after the index maintenance is performed.

and there are none that are listed as out of date when I...

Gail, last wait type for all of them is SOS_SCHEDULER_YIELD

LutzM (12/28/2013)

---

Banks, do you have a detailed description what has been changed compared to the previous version (that seems to have performed as expected)? Especially in terms of communication between...

oh, forgot to add, the wait info is null on those statements.

oh, I see, yes, that's not my call, I'm not allowed to actually change the database schema at all, I'd have to go to our lead engineer and put in...

they're not redundant, 1st post is for the versionfileinfo table

and second is for the userfileinfo table

in terms of our application, one is for our block storage, and the other is...

I'll capture the wait types the next time this issue occurs, currently it's not happening on any servers, but that will surely change within a day or so. ...

and here are the indexes for the 2nd table

CREATE CLUSTERED INDEX [ixc_BackupSetId] ON [dbo].[UserFileInfo]

(

[BackupSetId] ASC

)

CREATE NONCLUSTERED INDEX [ix_BackupSetAndVersion] ON [dbo].[UserFileInfo]

(

[BackupSetId] ASC,

[FirstVersionId] ASC

)

INCLUDE ( [LastVersionId],

[VersionFileLocalNameId],

[UserFileInfoId],

[IsFinished])

CREATE NONCLUSTERED INDEX [ix_FileNameCRC]...

here's the indexes for the VersionFileInfo table (the first table)

CREATE CLUSTERED INDEX [ixc_BackupSetId] ON [dbo].[VersionFileInfo]

(

[BackupSetId] ASC

)

CREATE NONCLUSTERED INDEX [ix_Cleanup] ON [dbo].[VersionFileInfo]

(

[BackupSetId] ASC,

[VersionFileInfoId] ASC

)

INCLUDE ( [VersionFileLocalNameId],

[FirstUserFileInfoId],

[LastUserFileInfoId])

CREATE NONCLUSTERED INDEX...

GilaMonster (12/27/2013)

---

Can you post the queries that are slow (just one or two to start), the slow and fast execution plans (actual plans) along with table and index definitions?

What wait...

Gail,

Thanks for the reply, I'll grab all that info and post it.

the query itself is this:

SELECT TOP 1 ufi.UserFileInfoId

FROM userfileinfo ufi WHERE EXISTS

(

SELECT vfi.VersionFileInfoId FROM VersionFileInfo vfi

WHERE ufi.BackupSetId =...

I'm seeing the queries using sp_whoisactive already.

that's how I'm able to see the queries slowing down.

I've checked for locks (nothing unusual, these queries are not being blocked)

there's no latch waits...

statistics are updated daily and automatically for all tables right after the index maintenance is performed.

and there are none that are listed as out of date when I check.

We used doubletake forever at my old job, they use checksums to confirm a good write commit. And you can set it up to email or otherwise alert...

Thank you guys, in fact none of the resources really gave me a clear picture, so I called microsoft. Ironically, I had to go to support to get...