Request a topic or
contact an Arke consultant
404-812-3123
SQL Server

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 2017

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

Undoing Sql Server Database Engine Tuning Advisor

Trying out a set of recommendations from database engine tuning advisor on a test CRM system, I saw one particular query go from 1 second to 25 seconds.  Obviously I need to remove the recommendations and see if it’s a fluke and see if any other queries changed.  But there is no ‘unapply’ command. I can’t find anything better than rewriting the sql file or restoring from a backup.  Rewriting the sql file is easier since there are only two types of statements in it – create index and create statistics.  Regular expressions to the rescue…

First off, in tuning advisor, always always always pick ‘Save Recommendations’ before (or instead of) apply recommendations. This gives you a SQL file with all the intended changes.

Since it’s a CRM system I was only tuning by adding indexes and statistics.  Index creates are very easy to replace – change CREATE to DROP, get rid of all the extra info after the first line.  The hard part is finding a regular expression tool that can handle multi line replace – sql server and visual studio and notepad++ don’t appear to be up to the task.  I ended up using this visual studio add-in which adds a proper regex parser to visual studio, but it has some bugs so I wouldn’t rely on it.  Make one find/replace, save, reload the file, make the second find/replace, save the file, then put the tool away. 

I just want to find:

^CREATE .*?(INDEX.*)$[^$]*?$?(go)

and replace:

DROP $1

go

regex[7]

 

Statistic drop syntax is slightly different than create syntax.  But not multi-line, so a bit easier, just needs groups.

Find:

CREATE STATISTICS (.*) ON (.*)\(.*\)

Replace With:

DROP STATISTICS $2.$1

And now I have a SQL file that undoes the changes my tuning advisor made.


Categories: SQL Server
Posted by David Eison on Tuesday, September 14, 2010 6:55 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Truncate_Only no longer supported in SQL Server 2008

If you've ever used the Truncate_Only to shrink logs in SQL Server 2000/2005, it may come as a surprise to you that its use has been discontinued in SQL Server 2008.

Instead, you can use the following commands to get SQL Server to do essentially the same thing:

 
Alter Database %databasename% Set Recovery Simple

 And then

Alter Database %databasename% Set Recovery Full

You can then shrink the log file as normal.


Categories: SQL Server
Posted by Wayne Walton on Monday, March 16, 2009 3:04 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Unable to load client print control – SSRS and CRM

After some Googling, I came across this forum post: http://social.microsoft.com/Forums/en-US/crm/thread/b86740b6-6418-4e1c-9020-1d6c9c630b7b/

Basically a hotfix KB956391 broke the client control for SSRS.  We had already installed all the latest automatic updates on all the servers, but the following update isn’t presented with Windows Update.

http://www.microsoft.com/downloads/details.aspx?FamilyID=82833f27-081d-4b72-83ef-2836360a904d&DisplayLang=en

Installing this fix on ALL* related servers, then rebooting fixed the problem.  (*You can’t just install it on the SSRS machine, you need to install it on any server that allows a client to connect to reporting services.) 

Basically, this fix forces the browser to download the latest client viewer and install it.  I had found a number of other solutions that required a manual installation on the client’s computer, but this one works from the server side.


Categories: CRM | SQL Server | SSRS
Posted by Trenton Adams on Wednesday, March 11, 2009 5:53 PM
Permalink | Comments (0) | Post RSSRSS comment feed