Umbraco query issue

Question

I have existing site on dev server win2k8 32bit sqlserver 2012 express iis7.5 running umbraco 6.1.6 all works fine. I have ported the site to live server this is win2k12 iis8 sql server 2012. The port involved ftping up files and taking a db backup from dev and restoring on live. The issue is on live when going to media section i get no media. After checking log files and running sql profiler i have identified the query that is causing the issue. The same query on dev runs fine but on live takes 11mins.

The query looks like

SELECT umbracoNode.id, umbracoNode.trashed, umbracoNode.parentID, 
        umbracoNode.nodeUser, umbracoNode.level, umbracoNode.path, umbracoNode.sortOrder, 
        umbracoNode.uniqueID, umbracoNode.text, umbracoNode.nodeObjectType, 
        umbracoNode.createDate, 
        COUNT(parent.parentID) as children, published.versionId as publishedVerison, 
        latest.versionId as newestVersion, contenttype.alias, 
        contenttype.icon, contenttype.thumbnail, 
        property.dataNvarchar as umbracoFile, 
        property.controlId
        FROM umbracoNode umbracoNode
        LEFT JOIN umbracoNode parent ON parent.parentID = umbracoNode.id
        INNER JOIN cmsContent content ON content.nodeId = umbracoNode.id
        LEFT JOIN cmsContentType contenttype ON contenttype.nodeId = content.contentType
        LEFT JOIN (SELECT nodeId, versionId FROM cmsDocument WHERE published = 1 GROUP BY nodeId, versionId) as published
        ON umbracoNode.id = published.nodeId
        LEFT JOIN (SELECT nodeId, versionId FROM cmsDocument WHERE newest = 1 GROUP BY nodeId, versionId) as latest ON umbracoNode.id = latest.nodeId
        LEFT JOIN (SELECT contentNodeId, versionId, dataNvarchar, controlId FROM cmsPropertyData INNER JOIN umbracoNode ON cmsPropertyData.contentNodeId = umbracoNode.id 
        INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid 
        INNER JOIN cmsDataType ON cmsPropertyType.dataTypeId = cmsDataType.nodeId 
        WHERE umbracoNode.nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'
        AND [umbracoNode].[parentID] = -1) as property
        ON umbracoNode.id = property.contentNodeId
        WHERE (umbracoNode.nodeObjectType = 'b796f64c-1f99-4ffb-b886-4bf4bc011a9c')
        AND ([umbracoNode].[parentID] = -1)
        GROUP BY umbracoNode.id, umbracoNode.trashed, umbracoNode.parentID, umbracoNode.nodeUser, umbracoNode.level, umbracoNode.path, umbracoNode.sortOrder, umbracoNode.uniqueID, umbracoNode.text, 
        umbracoNode.nodeObjectType, umbracoNode.createDate, published.versionId, latest.versionId, contenttype.alias, contenttype.icon, contenttype.thumbnail, property.dataNvarchar, property.controlId
        ORDER BY umbracoNode.sortOrder

Its same database indexes are present so i cannot see what is going on. Has anyone seen this before? Is it sql server 2012 issue on 64bit w2k12?

Regards

Ismail

Answer 1

Right solved this issue. In my site i updated iis app pool to enable 32 bit and its has not only brought down my memoery usage from 99% to 70% it has fixed the media load problem.

Ismail