Mscorsvw.exe high CPU usage on Windows Server 2012

I sometimes really hate Windows. When you do obligatory updates sometimes it takes hours for operating system to settle down and stop using CPU and memory like crazy.

One of these processes are mscorsvw.exe and ngen.exe which do some .NET assemblies optimization. I read about a lot of angry posts about these processes and that it takes hours “to optimize” and during these hours your server and services on it are suffering (and you need to be clever when finding explanations to your clients).

Follow these steps:

  1. Navigate to the following location in Windows Explorer: C:\Windows\Microsoft.NET\Framework\v4.0.30319
  2. Run an elevated command prompt and change the current directory to the following directory
  3. Type ngen.exe executequeueditems, and press ENTER
  4. Wait until you receive the following message at the command prompt: All compilation targets are up to date.

This annoying processes stopped but now TiWorker.exe is taking his time and eating the CPU and Memory and it looks like it will take 3-4 hours. Yikes! I really hate Windows programmers for this hidden background CPU and Memory eaters.

Cannot resolve %windir%

I faced once strange problem that somehow system environment variable %windir% could not be resolved.

This means that all shortcuts that are pointing to C:\Windows\System32 will not work.

You will not be able to set it directly by clicking with right mouse button on Computer icon and you will get this message

windir

To solve this just open this directly from Start > Run… and set this environment variable

c:\Windows\System32\systempropertiesadvanced.exe

In my case %windir% was pointing to undefined %SystemRoot% so I just added it and make it point to C:\Windows

This didn’t helped. After the research I found out that length of my %PATH% variable was more then 2048 chars.

It must be bellow 2048 chars and when I deleted non-important paths it started working OK!

Conclusion:

Keep your system variables less then 204 chars in length!

Microsoft .NET to run on Linux and Mac OS X

Microsoft .NET will finally run fully on Linux and Mac OS X platform and this is a great news.

Visual Studio is maybe the best tool for developers and Microsoft decided to open sourcing the full server-side .NET Core stack, from ASP.NET 5 down to Core Runtime and Framework.

Microsoft is also giving for free Visual Studio Community 2013 full featured edition of Visual Studio, available today.

You can watch more about this here.

Open source links
ASP.NET – https://github.com/aspnet/home
.NET Compiler – http://roslyn.codeplex.com/
.NET Core – https://github.com/dotnet/corefx
.NET – https://github.com/Microsoft/dotnet

How to stop annoying mouse selection on click in Visual Studio?

You were working normally and suddenly on every click your mouse perform annoying multirow selection.

In order to stop this just press this combination of keys:

Alt Gr+Ctrl (right)+Shift (right)

How to delete duplicated records in MS SQL Server?

First, create one demo table

Let’s create one demo table first. It will be called Person and will hold some fictive person data.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE dbo.Person
(
  Id int IDENTITY(1,1) NOT NULL,
  Firstname nvarchar(50) NULL,
  Lastname nvarchar(50) NULL,
  Email varchar(100) NULL,
  Created datetime NULL,
  CONSTRAINT PK_Person PRIMARY KEY CLUSTERED
  (
    Id ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
GO
SET ANSI_PADDING OFF
GO

Now, fill the data

Insert some fictive person data and do some duplicated records that have the same values for fields  firstname, lastname and email.

INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('John', 'Belvien', 'john.belvien@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Peter', 'Lumberjack', 'peter.lumberjack@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Peter', 'Lumberjack', 'peter.lumberjack@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Peter', 'Lumberjack', 'peter.lumberjack@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Suzy', 'Leghorn', 'suzy.leghorn@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Suzy', 'Leghorn', 'suzy.leghorn@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())

Now the magic

Use this subquery trick to extract duplicate records and then delete them. Notice the use of PARTITTION and WITH Transact-SQL keywords. All records that have RowNumber > 1 are deleted.

WITH CTE AS
(
  SELECT  Id,
          Firstname,
          Lastname,
          Email,
          ROW_NUMBER() OVER (PARTITION BY Firstname, Lastname, Email
          ORDER BY	Created) AS RowNumber
  FROM Person
)
DELETE FROM Person WHERE Id IN (SELECT Id FROM CTE WHERE RowNumber > 1)

Using SQL Server APPLY command in queries

APPLY clause in Transact-SQL is interesting because it can be user in the following scenario:
For example, you might create a query that returns a list of payments (in this case last 2 payment amounts and dates) for each subscriber inside your SaaS product.

SELECT c.Id AS CustomerId,
       c.Firstname + ' ' + c.Lastname AS Name,
       i.PaidDate,
       i.Amount
FROM   Customer c
OUTER APPLY
(
  SELECT TOP 2 i.PaidDate, i.Amount
  FROM Invoice i
  WHERE i.CustomerId = c.Id
  ORDER BY i.PaidDate DESC
) AS i
WHERE YEAR(c.Registered) = YEAR(GETDATE())

You get something like this

958 Usain Jordan 2014-06-01 299.00
958 Usain Jordan 2014-07-18 458.00
110 Seagal KungFu 2014-01-12 15.16
110 Seagal KungFu 2014-03-06 17.45

Stop automatic rebooting of Windows 7 after Windows Update

If you want your PC with Windows 7 operating system to work as server then you definitely want to stop automatic rebooting after Windows Update install new patches.

To do this you need to do the following:

  1. Open the Registry Edit (WindowsKey + R, “regedit.exe“)
    (Remember to be careful! DON’T change anything unless you know exactly what you’re doing!)
  2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU
    Chances are that you’ll be missing these keys.
    If keys WindowsUpdate\AU exist, go ahead to step 3
    OTHERWISE, continue through these indented steps…

    1. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows
    2. (Make sure the above key is highlighted and) go to the edit menu and choose New > Key
    3. Name the key WindowsUpdate
    4. Now, repeat the above steps, except highlight WindowsUpdate and name the next keyAU
  3. With WindowsUpdate\AU highlighted, create a new 32-bit DWORD calledNoAutoRebootWithLoggedOnUsers
  4. Right click on your newly created value and give it a value data of 1 (hexadecimal)
  5. Double check all the steps to make sure you did everything EXACTLY how I explained it
  6. Close Registry Editor

Using SQL Server PIVOT feature to transform rows into columns

PIVOT queries in SQL Server are useful when we need to transform data from row-level to column-level.

Example: Imagine having the table named Invoice (payments per month) – for simplicity let’s say that we have PaidDate and Amount.

Id	PaidDate                    Amount
1	2014-01-05 12:34:22.000	    318.22
2	2014-01-06 11:44:00.000	    128.33
3	2014-04-07 16:44:00.000	     55.50
4	2014-02-08 15:12:00.000	     88.55
5	2014-02-16 18:39:00.000     115.45
6	2014-03-01 16:27:00.000	      9.99
7	2014-03-08 13:13:00.000	     16.99
8	2014-04-27 16:44:00.000	    568.69
9	2014-05-10 19:40:00.000	     43.65

What we wanted now is to get total money collected from the beginning of the year 2014, and in order to do this we can use this PIVOT query:

SELECT *
FROM (
  SELECT  YEAR(PaidDate) AS [Year],
          LEFT(DATENAME(month, PaidDate), 3) AS [Month],
          Amount FROM Invoice
) as PaymentsByYear
PIVOT
(
  SUM(Amount)
  FOR [Month] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
) AS PaymentsByYear
ORDER BY [Year] DESC

And we finally get

Year    JAN     FEB     MAR     APR     MAY     JUN     JUL     AUG     SEP     OCT     NOV     DEC
2014	446.55  204.00  26.98   624.19  43.65   NULL    NULL    NULL    NULL    NULL    NULL    NULL

MS SQL Server 2014 “Hybrid” to attack MongoDB

On April 1st 2014 MS SQL Server 2014 is internally released for small group of big customers such as bwin and others. The biggest improvement is in-memory OLTP engine to deliver breakthrough performance to their mission critical applications. Company bwin scale its applications to 250K requests a second, a 16x increase from before, and provide an overall faster and smoother customer playing experience.

The new In-Memory OLTP engine (formerly code named Hekaton), provides significantly improved OLTP performance by moving selected tables and stored procedures into memory. Hekaton enables you to use both disk based tables and Memory-Optimized Tables together in the same queries and stored procedures.

The In-memory OTLP engine works with standard x64 hardware. The new In-Memory OLTP engine uses a completely new optimistic locking design that’s optimized for in-memory data operations. In addition, stored procedures are compiled into native Win64 code. The end result is far faster application performance.

Microsoft recommends that you provide an amount of memory that’s two times the on-disk size of the memory-optimized tables and indexes.

Why similarity to MongoDB? Because we were using it also and if you have enough RAM memory it keeps all indexes in RAM and is very quick plus it uses the advantages of SSD disks. Where SQL Server shines comparing to Mongo is when you need to group large amount of data and analyze them. Until this version of MS SQL 2014 (we didn’t test it) MongoDB was superior when it comes to large number of insert request into database. For analyzing the large amount of data MS SQL was better. Now, with new SQL Server 2014 it comes closer to MongoDB main advantage (process data in memory and use SSD advantages – if specified).

in-memory-oltp

(Source: sqlmag.com)

The following data types aren’t supported by memory-optimized tables:

  • datetimeoffset
  • geography
  • hierarchyid
  • image
  • ntext
  • sql_variant
  • text
  • varchar(max)
  • User data types (UDTs)

In addition, there are a number of database features that aren’t supported. Here are some of the most important database and table limitations:

  • Database mirroring isn’t supported.
  • The AUTO_CLOSE database option isn’t supported.
  • Database snapshots aren’t supported.
  • DBCC CHECKDB and DBCC CHECKTABLE don’t work.
  • Computed columns aren’t supported.
  • Triggers aren’t supported.
  • FOREIGN KEY, CHECK, and UNIQUE constraints aren’t supported.
  • IDENTITY columns aren’t supported.
  • FILESTREAM storage isn’t supported.
  • ROWGUIDCOL isn’t supported.
  • Clustered indexes aren’t supported.
  • Memory-optimized tables support a maximum of eight indexes.
  • COLUMNSTORE indexes aren’t supported.
  • ALTER TABLE isn’t supported. In-Memory OLTP tables must be dropped and re-created.
  • Data compression isn’t supported.
  • Multiple Active Result Sets (MARS) aren’t supported.
  • Change Data Capture (CDC) isn’t supported.

Other SQL Server 2014 improvements

  • Using advantages of SSD disks technology (new buffer pool enhancements increase performance by extending SQL Server’s in-memory buffer pool to SSDs for faster paging)
  • AlwaysOn Availability Groups now support up to eight secondary replicas
  • Business Intelligence Enhancements (Power View can work against multidimensional cube data, new data visualization tool named Power Query, new visual data mapping feature named Power Maps)
  • SQL Server 2014 also supports encrypted backups
  • SQL Server 2014 will have the ability to scale up to 640 logical processors and 4 TB of memory in a physical environment. Plus, it has the ability to use up to 64 virtual processors and 1 TB of memory when running in a VM

More reading about this:

How to escape parenthesis/brackets ‘{‘, ‘}’ in string.Format?

Usually you have something like this:

string.Format("Output: {0}", "A");

This will result as:

Output: A

What you should do if you want to make an output like this?

Output: {A}

You need to escape parenthesis/brackets like this:

  • You use {{ to output {
  • You use }} to output }

So, you final C# command should look like this:

string.Format("Output: {{{0}}}", "A");

Follow

Get every new post delivered to your Inbox

Join other followers