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’.

TRUNCATE Log in SQL Server 2008, 2012, 2014

You have probably seen message like
‘Transaction log of myDatabase is full’.

Prior to version 2008 of SQL Server we will execute statement like

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

but if you tried to execute this on SQLServer 2008, 2008R2, 2012 and 2014 you will get and error like

?Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

Assuming that you know that TRUNCATE_ONLY is an dangerous option, not very usable in a live systems and that officialy and recomended way to do it is to change database recovery model from FULL to SIMPLE still you can save a lot of time, space and headaches on test systems, on SQL Server 2008 and later you need to execute

BACKUP LOG MyDatabase
TO DISK = 'nul:' WITH STATS = 10

and magic is done. Your database log is truncated.

.NET Parametrized queries and sp_executesql performance improvements

If you fetching performance problems by executing sp_executesql consider to use in your query hint RECOMPILE. Note, that sp_executesql is used all the times when you execute parametrized queries in .NET for example. So, the need for this hint is much bigger than you think.
Initally your query can be something like

select col1 from Test_table where col1 = @param1
order by col1

what is transfered to sql server like

exec sp_executesql N'select col1 from Test_table where col1 = @param1
order by col1', N'@param1 int'

So your query should looks like

select col1 from Test_table where col1 = @param1
order by col1 OPTION (RECOMPILE)

Notethat OPTION(RECOMPILE) has been added to the query. This option in some cases can boost your query performances.

Main problem here is well known in SQL Server and is called parameter sniffing, what means that SQL Server looks at the input parameters to build a query plan and if the parameters are fine for the first invocation of the query, than you are fine with the performances. But if parameters dramatically changes, than you might need a new execution plan which will not be any more index seek but index scan. With the option RECOMPILE you are requesting from SQL SErver ptimizer to generate a new plan for your query and to ignore the existing one. Thus, you ghet proper plan. Note, that using always option RECOMPILE will decrease query performances if the proper plan will be found in the cashed plans.

In my environement, this option increases some of the queries more than 100 times.

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

Applicationpoolidentity account – setting permissions in IIS for ASP.NET Application

If you have problems setting permissions on IIS 7 or higher that’S probably due to mis-understanding under which account your web site is running. By default, ApplicationPoolIdentity is used to run default application pool. When you create your new site, MyWebSite, in MyAppPool you will see in Task Manager that IIS process which runs your web site is run under MyAppPool user. Whenever a new application pool is created, the IIS management process creates a security identifier (SID) that represents the name of the application pool itself. The problem becomes real, when you need to set some permissions on your file system for this user. You won’t see it. So if you try to set permissions on file or folder by

  • Open Windows Explorer
  • Select a file or directory.
  • Right click the file and select “Properties”
  • Select the “Security” tab
  • Click the “Edit” and then “Add” button
  • Click the “Locations” button and make sure you select your machine.

if you search now for user “MyAppPool” you won’t find it. The trick is that you have to search for name “IIS AppPool\MyAppPool”. This user has been found and you can set desired permissions.

Add NOT FOR REPLICATION to the column

If you had a table defined something like

CREATE TABLE [MyTable](
	[IdCol] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY

)

and later you want to add this table in the replication but you want that replication agent treats it differently when performs an insert operation. With NOT FOR REPLICATION option you can generaly control if foreign keys, check constraints, triggers and identity colums are enforced on the subscriber or not.

Updating column from previos definition to not enforce IDENTITY constraint to subscriber can be done with

ALTER TABLE [MyTable] ALTER COLUMN [MyCol] ADD NOT FOR REPLICATION

After this update table definition will looks like

CREATE TABLE [MyTable](
	[IdCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL PRIMARY KEY

)

Using Events in .NET

Consuming events in .NET is possible on couple of ways. So, if object type Car contains an event SaySpeed it can be consumed on the following ways:

  • Usual way
    car.SaySpeed += car_SaySpeed;
    

    where car_SaySpeed is function which implements SaySpeed delegate like:

    void car_SaySpeed(object sender, CarEventArgs e)
    {
        ...
    }
    
  • Another method is by using anonymous methods as
    car.SaySpeed += delegate
    			{
    				...
    			};
    
  • And a clean one, by using lambda expressions
    car.SaySpeed += (sender, e) => { 
    				... 
    			};
    

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>

SQL Server: Get cached plans

If you ever wanted to see which SQL plans are cached and how many times called, you can use the following query:

SELECT [cp].[refcounts] ,
[cp].[usecounts] ,
[cp].[objtype] ,
[st].[dbid] ,
[st].[objectid] ,
[st]. ,
[qp].[query_plan], [cp].[plan_handle]
FROM sys.dm_exec_cached_plans cp
	CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
	CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
	INNER JOIN sys.[databases] d ON st.[dbid] = d.[database_id]
WHERE d.[name] LIKE 'YOUR_DATABASE_NAME'
ORDER BY [cp].[usecounts] DESC

Provided plan handle can be used in

DBCC FREEPROCCACHE

as parameter to remove just selected sql plan from the cache.