Request a topic or
contact an Arke consultant
404-812-3123
February 2011

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments

Archive

Authors

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2024

DNN Event Viewer times out

Continuing in our theme of adjusting DNN sprocs…

The DNN Event Viewer first runs a purge sproc, then runs a get log sproc.

If you have a lot of events, the purge sproc is almost certain to time out. 

The get log sproc can benefit from a nolock.  But the purge sproc is where we saw most of our trouble.

The purge sproc in dnn 551:

    ;WITH logcounts AS
    (  
      SELECT 
        LogEventID, 
        LogConfigID, 
        ROW_NUMBER() OVER(PARTITION BY LogConfigID ORDER BY LogCreateDate DESC) AS logEventSequence
      FROM dbo.EventLog with(NOLOCK)
    )
    DELETE dbo.EventLog 
    FROM dbo.EventLog el 
        JOIN logcounts lc ON el.LogEventID = lc.LogEventID
        INNER JOIN dbo.EventLogConfig elc ON elc.ID = lc.LogConfigID
    WHERE elc.KeepMostRecent <> -1
        AND lc.logEventSequence > elc.KeepMostRecent 

 

This was failing for a few clients of ours.  They would end up with say 65k records in the event log table, and this would never complete. 

This should lock less rows, delete in 1000 record chunks, and put an upper bound on how many records it will tackle at once.  This helped this lookup quit failling for our client:

ALTER PROCEDURE [dbo].[PurgeEventLog]
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

create table #TLog (LogGUID uniqueidentifier not null primary key, LogCreateDate datetime)

;WITH logcounts AS
(  
  SELECT 
    LogEventID, 
    LogConfigID, 
    ROW_NUMBER() OVER(PARTITION BY LogConfigID ORDER BY LogCreateDate DESC) AS logEventSequence
  FROM dbo.EventLog with(NOLOCK)
)
insert into #TLog
 SELECT LogGUID, LogCreateDate
    FROM dbo.EventLog el with(NOLOCK)
        JOIN logcounts lc with(NOLOCK) ON el.LogEventID = lc.LogEventID
        INNER JOIN dbo.EventLogConfig elc with(NOLOCK) ON elc.ID = lc.LogConfigID
    WHERE elc.KeepMostRecent <> -1
        AND lc.logEventSequence > elc.KeepMostRecent 

declare @intRowCount int
declare @intErrNo int
declare @commiteveryn int
declare @maxloops int

set @commiteveryn=1000
set @intErrNo=0
set @intRowCount=1 -- force first loop
set @maxloops=20

WHILE @intRowCount > 0 and @maxloops > 0
    BEGIN
        set @maxloops = @maxloops - 1
        BEGIN TRANSACTION
        BEGIN TRY
        DELETE FROM EventLog WHERE LogGuid IN (select top (@commiteveryn) LogGUID from #TLog order by LogCreateDate DESC)
        SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT        
        DELETE FROM #TLog WHERE LogGuid IN (select top (@commiteveryn) LogGUID from #TLog order by LogCreateDate DESC)

        commit
        END TRY
        BEGIN CATCH
         rollback;
         set @maxloops=0
        END CATCH
    END

drop table #TLog

-- used to be
    --;WITH logcounts AS
    --(  
    --  SELECT 
    --    LogEventID, 
    --    LogConfigID, 
    --    ROW_NUMBER() OVER(PARTITION BY LogConfigID ORDER BY LogCreateDate DESC) AS logEventSequence
    --  FROM dbo.EventLog with(NOLOCK)
    --)
    --DELETE dbo.EventLog 
    --FROM dbo.EventLog el 
    --    JOIN logcounts lc ON el.LogEventID = lc.LogEventID
    --    INNER JOIN dbo.EventLogConfig elc ON elc.ID = lc.LogConfigID
    --WHERE elc.KeepMostRecent <> -1
    --    AND lc.logEventSequence > elc.KeepMostRecent 




GO



Posted by David Eison on Monday, February 28, 2011 2:26 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Sitecore: Links as Items Redux!

Previously I had posted on how to set up items in your content tree to act as external links to other pages (for use with Navigation mainly – for example if you have a blog elsewhere but still want it listed in the main navigation). However, Ivan Buzyka pointed out some issues with the simple implementation so I added creating a better redirect to my ‘to do’ list for the blog. The time has come!

Let’s pretend we are modifying an existing site, we don’t want to change the navigation so that won’t be covered here – we just want to update our layout to work a little more universally. Our new items need to be able to link to an internal, external or Media item reliably for display in our navigation. Our template will consist of similar things to last time:

Link: General Link

Nav Title: Text -> standard values: $name

In Navigation: Checkbox ->standard values: checked

Create the template, add in standard values for it with the above settings and now we can create our Layout which should be assigned to the standard values of the new template.

In my layout is the following (inside the page load):

String url;
Item extItem = Sitecore.Context.Item;
LinkField extLink = (LinkField)(extItem.Fields["Link"]);
if (extLink != null)
{
  if (extLink.IsInternal && extLink.TargetItem != null)
  {
    url = Sitecore.Links.LinkManager.GetItemUrl(extLink.TargetItem);
  }
  else if (extLink.IsMediaLink && extLink.TargetItem != null)
  {
    url = Sitecore.StringUtil.EnsurePrefix('/', Sitecore.Resources.Media.MediaManager.GetMediaUrl(extLink.TargetItem));
  }
  else
  {
    url = extLink.Url;
  }
}
else 
{
  Item homeItem = Sitecore.Context.Database.GetItem(Sitecore.Context.Site.StartPath);
  url = Sitecore.Links.LinkManager.GetItemUrl(homeItem);
}
if (!String.IsNullOrEmpty(url))
{
   Response.Redirect(url);
}

To step through it: we’re setting the default to bring the user back to the home page just in case something goes wrong. From there, we check to see if the Link field exists and what type of link it is.

For an internal link, we grab the url for the item itself, and for the media item, we’re grabbing the url for the Media item to be displayed (or pdf etc.), and if it’s external – we’re just redirecting them to the url they specified.

If we stopped here, everything would be working great as long as the content was entered appropriately, however, that doesn’t always happen and we’d like to avoid this going boom. To do that we can add a simple validator: open up the content tree within Sitecore and then head to your template, expanding out the children and select the Link field item.

Scroll down to Validation in the Data section.

We want to make sure that the Link field is Always one of the following: and Internal link, a Media item, or an external Link. Also, we want it to have Some value.

Within Validation you’ll need to put the actual content you want to validate, and ValidationText is what will appear if that is not met. This will pop up when the user tries to save the item with an improper value.

validation

Shown above is the Validation and our error message: linktype is how we can determine what sort of link it is, and is generated automatically when a user selects their link (unless they are editing raw values). Our validation just makes sure that the linktype text contains one of those three options (internal, media, or external) and as long as one of those match the text in the raw value for the field we have a valid link.

This helps prevent a scenario where the user has used one of the other options for an external link which would stop the page from going anywhere.

You can also add in some of the default validation options – I'd recommend adding the Required field validator as well.


Categories: Sitecore
Posted by Amy Winburn on Thursday, February 24, 2011 6:10 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Sitecore: Adding your own Icons

Items can be configured to have an icon – and Sitecore provides an extensive list of them. But you may want to add your own for whatever reason. To do this you’ll need an image suitable for making into an icon, and the ability to resize this image to the correct sizes (Paint.net, Photoshop, GIMP).logo

For our example we’re making an Arke Icon and we're going to say our largest icon will be 128x128, so we have a transparent png called logo.png to work with: 

The image you want to use should have a transparent background unless you want the icon to be a square, and ideally not overly detailed as it will be very small.

Next, we need to make this image into various sizes, and put logo.png into the appropriately named directories, the structure is as follows (if you have several icons, just do the same thing by putting multiple icons into each size folder):

  • ArkeIcon
    • 16x16
      • logo.png
    • 24x24
      • logo.png
    • 32x32
      • logo.png
    • 48x48
      • logo.png
    • 128x128
      • logo.png

Zip this all up with the same name as the containing folder, so ArkeIcon.zip

Upload this new zip file to /sitecore/shell/Themes/Standard/ and make sure the permissions are correct for your installation (check the other files such as Application.zip for a comparison).

Back within your content editor: each item has an Icon field and you can now enter in your custom icon by entering: ArkeIcon/16x16/logo.png

Once you do this, the new icon will show up in the list of recently used icons as well.

As for adding the new icon set to the list of usable icons – that’s a little more tricky since the list is specified statically (if you know a good way of changing this please share).

You can however modify the existing sets/zips of images, such as the aforementioned application.zip – just add your image to the appropriate directories and you can use it just like all the other icons!

Posted from: Amy's Sitecore Adventures


Categories: Sitecore
Posted by Amy Winburn on Friday, February 18, 2011 5:52 PM
Permalink | Comments (0) | Post RSSRSS comment feed

More DNN performance

Some DNN sites spend way too much time running the sproc dbo.GetSchedule.  This is probably worse if DNN is configured with its Scheduled Jobs in the default ‘Request’ mode (instead of ‘Timer’ mode).  Unfortunately that job is both slow and can deadlock on updates. 

The original job we had in our DNN 5.6.1 is doing:

SELECT
  S.*,
        SH.NextStart
    FROM dbo.Schedule S
        LEFT JOIN dbo.ScheduleHistory SH ON S.ScheduleID = SH.ScheduleID
    WHERE (SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID
                                        FROM dbo.ScheduleHistory S1
                                        WHERE S1.ScheduleID = S.ScheduleID
                                        ORDER BY S1.NextStart DESC)
                OR SH.ScheduleHistoryID IS NULL)
            AND (@Server IS NULL OR S.Servers LIKE '%,' + @Server + ',%' OR S.Servers IS NULL)

Here’s almost the same thing, but faster and less likely to deadlock:

    SELECT
        S.*,
        (SELECT TOP 1 NextStart FROM ScheduleHistory S1 with(nolock)
         WHERE S1.ScheduleID = S.ScheduleID
         ORDER BY S1.NextStart DESC) as NextStart
    FROM dbo.Schedule S  with(nolock)
    WHERE (@Server IS NULL OR S.Servers LIKE '%,' + @Server + ',%' OR S.Servers IS NULL)

Replacing this one query dropped one problematic DNN site from 100% sql server cpu utilization to more in the 30% range.


Posted by David Eison on Thursday, February 17, 2011 1:33 AM
Permalink | Comments (0) | Post RSSRSS comment feed

DotNetNuke PurgeScheduleHistory

DotNetNuke runs a task to purge it’s schedule history; however, the stored procedure that does this has performance problems that will cause deadlocks on a high traffic website.

The query that the PurgeScheduleHistory stored procedure ships with is:

DELETE FROM dbo.ScheduleHistory
FROM dbo.Schedule s
WHERE (
  SELECT COUNT(*)
  FROM dbo.ScheduleHistory sh with (nolock)
  WHERE sh.ScheduleID = ScheduleHistory.ScheduleID
  AND sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum
AND s.RetainHistoryNum <> -1
AND s.ScheduleID = ScheduleHistory.ScheduleID

Anytime you write a query that does a delete from a select you run the risk of deadlock.  Also, whenever you do a sweeping delete on a table you can escalate to a page lock, have trouble with locking indexes, and end up in a deadlock scenario again.  On the surface this doesn’t sound like a deadlock scenario, but you have to understand the way locks affect indexes and how locks escalate in SQL Server—you might be surprised to learn a simple SELECT query can deadlock under the right conditions also (http://stackoverflow.com/questions/661908/sql-server-deadlocks-between-select-update-or-multiple-selects).

The fix to this stored procedure follows the same approach Microsoft used to fix the DeleteExpiredSessions stored procedure that ships with ASP.NET (http://support.microsoft.com/kb/973849).

The following query should replace the PurgeScheduleHistory stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PurgeScheduleHistory]

AS

SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

create table #T (ID int not null primary key)

insert into #T
select schedulehistoryid from (
select s.ScheduleID, sh.schedulehistoryid, rank() over (partition by s.scheduleid order by sh.startdate) rn, RetainHistoryNum
from ScheduleHistory sh WITH (READUNCOMMITTED)
join Schedule s WITH (READUNCOMMITTED) on s.ScheduleID = sh.ScheduleID
where s.RetainHistoryNum <> -1) a
where rn > RetainHistoryNum

DECLARE ESC CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT ID FROM #T

declare @ID int

open ESC

fetch next from ESC into @ID

WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE FROM ScheduleHistory WHERE ScheduleHistoryID = @ID
        FETCH NEXT FROM ESC INTO @ID
    END

CLOSE ESC

DEALLOCATE ESC

drop table #T

Thanks to David Eison for finding this solution.


Categories: SQL Server | ASP.NET | DotNetNuke
Posted by Eric Stoll on Wednesday, February 16, 2011 1:13 AM
Permalink | Comments (0) | Post RSSRSS comment feed

Digging through Event logs

I haven’t found a tool that I love that parses Event Viewer well.

What I do these days I use psloglist from Windows SysInternals to dump the log to a tab delimited file, then I hack on strings in Excel using IFERROR, SEARCH, RIGHT and LEFT to get decently representative strings and then sort and subtotal.

Also today I needed to read a DNN error table while DNN was not behaving well.  DNN writes XML to the database; the easiest thing to do is to cast it to XML with

cast(LogProperties as XML)

Plug it into a temp table, then process the XML using Sql Server’s XML query command:

select cast(LogProperties as XML) as props, LogTypeKey, LogGUID, LogCreateDate into #tmptable from EventLog with(nolock)
WHERE LogTypeKey='GENERAL_EXCEPTION'

select count(*) as cnt,a.msg
from
(select cast(props.query('LogProperties/LogProperty/PropertyName[text()="Message"]/../PropertyValue/text()') as nvarchar(MAX)) as msg
  from #tmptable) a
group by a.msg
order by COUNT(*) desc

select cast(props.query('LogProperties/LogProperty/PropertyName[text()="Message"]/../PropertyValue/text()') as nvarchar(MAX)) as msg, *
  into #tmp2
  from #tmptable

select * from #tmp2 where msg like '%[interesting keyword]%' order by LogCreateDate desc

I test out xpath using Xpath Visualizer.


Posted by David Eison on Monday, February 7, 2011 7:12 PM
Permalink | Comments (0) | Post RSSRSS comment feed