sys.sp_MSenumgenerations90 and high CPU problem

This article presents a solution of a problem with high CPU in environment with merge replication which are caused by a huge number of calls sys.sp_MSenumgenerations90 system stored procedure like

declare @p6 bigint
set @p6=9992683
exec sys.sp_MSenumgenerations90 9779693,'56F9A1C8-8A0A-4DB3-A1B8-C04FEB475A90',0,500,9893895,@p6 output
select @p6

First thing you can do is to create a missing index on [dbo].[MSmerge_genhistory]

CREATE NONCLUSTERED INDEX <index name> ON [dbo].[MSmerge_genhistory](generation, guidsrc, art_nick, pubid, [nicknames], [genstatus], [changecount])

With this index, execution will be faster, high CPU interval shorter, but your problem will not disappear.
You might try to reduce “Retention period” for your replication (default value is 14 days) but that will just drop number of records in replication system tables like MSmerge_contents, MSmerge_tombstone, MSmerge_genhistory, MSmerge_partition_groups,MSmerge_current_partition_mappings and MSmerge_past_partition_mappings
Still you will have a huge number of calls sys.sp_MSenumgenerations90.

SOLUTION:

The real fix of the problem is to update genstatus column of [dbo].[MSmerge_genhistory] for all rows which have genstatus equal to 4 to 0. So the statement should be:

UPDATE [dbo].[MSmerge_genhistory] SET [genstatus] = 0 WHERE [genstatus] = 4

Although, that genstatus 4 is not documented in BOL, it appears in the table and all of problematical records have this status. After updating them to genstatus = 0 system will pick up them on the next synchronization and your system will work fine without high CPU and without a lot of calls to the sys.sp_MSenumgenerations90.

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again…

If you fetched error like this

Msg 10314, Level 16, State 11, Line 4
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
  System.IO.FileLoadException: Could not load file or assembly 'myAssembly, Version=2.0.0.490, Culture=neutral, PublicKeyToken=5963130873dd3a75' or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException:
  at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
  at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
  at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
  at System.Reflection.Assembly.Load(String assemblyString)

then your solution can be really simple. I know that you tried to see if SQL CLR is enabled, you tried to set database THRUSTWORTHY with

ALTER DATABASE [<dbanme>]  SET TRUSTWORTHY ON

but nothing worked. A magic can be in the following statement

ALTER AUTHORIZATION ON DATABASE ::[<dbanme>] TO sa;

Looks like that the problem here is that ‘dbo’ cannot be mapped to valid login, what means login with the SID equal to the the owner_sid value in sys.[databases] system view.
Why this happen?
Database owner is usually the principal who created database unless AUTHORIZATION clause was not specified, and the most times Windows SID of the database creator is used. And here is a problem when database is copied on another server by different users. Their SIDs are different.
Definitely your database can work just normally unless some component requires EXECUTE AS statement such as assembly validation, explicit EXECUTE AS in your code, code signing and service broker message delivery.

ALTER AUTHORIZATION statement just force owner_sid to a valid login of ‘sa’.

SQL Server WAIT Statistics

To Analyze waits encountered by threads that executes on SQL Server we can use query bellow. Note, that this query shows the time for waits that have completed and does not show current waits. Here is the query:

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95;

If you need to reset the content of sys.dm_os_wait_stats, to get accurate data, you should use

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

WordPress Permalinks Error

Have you tried to run WordPress with the permalinks on IIS without success? After choosing any of the custom permalinks, yu got 404 page not found? Than your problem is in the web.config file. As it is not writable from the web, you should manually update it by adding the following section under system.webServer node:

<rewrite>
		<rules>
			<rule name="wordpress" patternSyntax="Wildcard">
				<match url="*" />
				<conditions>
					<add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
					<add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
				</conditions>
			<action type="Rewrite" url="index.php" />
			</rule>
		</rules>
</rewrite>