Queries Hung on Memory Request

Question

Queries Hung on Memory Request

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16251 More actions · Answer 1

Glad it worked.

Just as an additional check as it may also affect this (and other) queries, how many CPU's you got allocated to this VM and how many tempdb files do you have?

And if more than 1 file (which you should have) are they all set the same size?

In case you are willing to try out these variations may or not improve it.

Delete l
From ACSLog.dbo.AuditLog l
where l.AuditLogID in (select ao.AuditLogID
        from #archive_output ao
        where exists (select top 1 1
             from ACSLog_Archive.dbo.AuditLog b
             where Ao.AuditLogID = B.AuditLogID
            )
        )

Delete l
From ACSLog.dbo.AuditLog l
where l.AuditLogID in (select ao.AuditLogID
        from #archive_output ao
        )
and exists (select top 1 1
    from ACSLog_Archive.dbo.AuditLog b
    where Ao.AuditLogID = B.AuditLogID
    )

For these variations you will most likely need to create a index on archive_output

Stamey SSCertifiable Points: 5600 More actions · Answer 2

This VM has 6 CPUs. I have at least two data files for TempDB on all servers. This server has 4 data files, of 3.2G, 3G, 36.8G, and 10G.

Thanks,
Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

I see that someone else has gotten to this before I was able to. Glad you have some improvements.

BTW, the code and especially data structures are, I'll be REALLY nice here, suboptimal. There is no doubt in my mind that there are all kinds of opportunites to improve this application. Hopefully you can convince management to get someone in to help you fix it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Mike Good SSCertifiable Points: 7415 More actions · Answer 4

Chris, this has been pretty interesting thread. Now that things are better, would you mind summarizing or showing what was the change that actually seemed to make a difference? I've gathered it's change from join to where in (..) which does not make sense to me, hope I am missing something.

Stamey SSCertifiable Points: 5600 More actions · Answer 5

Code change was:
Delete A From ACSLog.dbo.AuditLog A With (Rowlock) Inner Join ACSLog_Archive.dbo.AuditLog B On A.AuditLogID = B.AuditLogID OPTION (MAXDOP 1)

To:
    Delete A
     From ACSLog.dbo.AuditLog A With (Rowlock)
     Inner Join #Archive B On A.AuditLogID = B.AuditLogID
     Where Exists(select top 1 1
            from ACSLog_Archive.dbo.AuditLog C With (NOLOCK)
            where A.AuditLogID = C.AuditLogID) OPTION (MAXDOP 2)

I am open to all ideas for improving my code.

Thanks,
Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

Mike Good SSCertifiable Points: 7415 More actions · Answer 6

I reckon the join to #Archive was the critical improvement factor. That makes sense to me. Thanks for posting this.