How to detect if SQL Server IN operator is identical to multiple OR operators

Create a simple table:

CREATE TABLE MYTABLE

(

     ID INT

)

Write an invalid query intentionally, give invalid column name in where clause:

SELECT * FROM MYTABLE WHERE ID_X IN (1, 2, 3)

Sql Server generates the error message as:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'ID_X'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'ID_X'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'ID_X'.

As you see, for one invalid column, the engine generates 3 same error messages.
Now, lets rewrite the invalid query as below, which has the same meaning:

SELECT * FROM MYTABLE WHERE

ID_X = 1 OR

ID_X = 2 OR

ID_X = 3

Sql Server generates the error message as:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'ID_X'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'ID_X'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'ID_X'.

As two error messages generated for two versions of query are identical, we can conclude that IN clause is converted to multiple OR clauses in engine side before execution.

Searching For a Specific String in Entire Database

Sometimes we need to search for a piece of string in entire SQL Server database.
Scenario:
Example string: "Currency". You want to look up all database and find the columns that include values like "Currency" or exactly the value "Currency".
It is an enormous effort to write a LIKE query for each table and each column.
The SQL script below helps you determine all columns at once.

Parameter Explanations:

@SEARCH_CRITERIA: The string to be looked up. Use '%' for pattern matching.

@COLUMN_LENGTH_GREATER_THAN: The column length who will be excluded from search operation. If your search criteria already contains 10 characters, it does not make sense query the columns whose max length is less than 10 characters. This parameter is optional.

@TABLE_ROW_COUNT_LESS_THAN: The number of records for the tables who will be excluded from search operation. Possibly, you would not prefer to look large tables when you are looking for a parameter or code definition table. This parameter is optional.

------ Set Parameter Values ------

DECLARE @SEARCH_CRITERIA VARCHAR(100) = '%Currency%'

DECLARE @COLUMN_LENGTH_GREATER_THAN INT = 15

DECLARE @TABLE_ROW_COUNT_LESS_THAN INT = 10000

------ End Of Set Parameter Values ------

DECLARE @TABLE_SCHEMA VARCHAR(100)

DECLARE @TABLE_NAME VARCHAR(100)

DECLARE @COLUMN_NAME VARCHAR(100)

DECLARE @OBJECT_ID INT

DECLARE CUR CURSOR FOR

SELECT

     SCHEMA_NAME(obj.schema_id) as TABLE_SCHEMA_NAME,

     obj.name AS TABLE_NAME,

     col.name AS COLUMN_NAME,

     obj.object_id AS TABLE_OBJECT_ID

FROM

     sys.objects obj (nolock)

     join sys.columns col (nolock)

     on obj.object_id = col.object_id

where

     obj.type = 'U' AND -- Tablo

     col.system_type_id in (167, 175, 231, 239) AND -- varchar, char, nvarchar, nchar

     (ISNULL(@COLUMN_LENGTH_GREATER_THAN, 0) = 0 OR col.max_length = -1 OR col.max_length >= @COLUMN_LENGTH_GREATER_THAN)

ORDER BY 1, 2, 3

    

OPEN CUR

FETCH NEXT FROM CUR INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @OBJECT_ID

DECLARE @RESULT TABLE

(

     TABLE_SCHEMA VARCHAR(100),

     TABLE_NAME VARCHAR(100),

     COLUMN_NAME VARCHAR(100),

     OBJECTID INT

)

DECLARE @ROW_COUNT BIGINT = 0

DECLARE @QUERY NVARCHAR(1000)

DECLARE @IS_QUERYING BIT = 1

WHILE @@FETCH_STATUS = 0

BEGIN

     SET @IS_QUERYING = 1

     IF ISNULL(@TABLE_ROW_COUNT_LESS_THAN, 0) > 0

     BEGIN

         -- Check Table Row Count Criteria

         SET @ROW_COUNT = 0

         SELECT

              @ROW_COUNT = SUM(row_count)

         FROM 

              sys.dm_db_partition_stats STAT (NOLOCK)

         WHERE

              STAT.object_id = @OBJECT_ID AND

              STAT.index_id < 2

         IF @ROW_COUNT > @TABLE_ROW_COUNT_LESS_THAN

              SET @IS_QUERYING = 0

     END

     IF @IS_QUERYING = 1

     BEGIN

         DECLARE @retvalOUT bit = 0

         SET @QUERY = N'SET @retvalOUT = 0 IF EXISTS (SELECT TOP(1) * FROM ' + '[' + @TABLE_SCHEMA + ']' + '.' + '[' + @TABLE_NAME  + '] WITH(NOLOCK)' + ' WHERE [' + @COLUMN_NAME + '] LIKE '''+ @SEARCH_CRITERIA + ''') SET @retvalOUT = 1'

        

         EXEC sp_executesql @QUERY, N'@retvalOUT bit OUTPUT', @retvalOUT=@retvalOUT OUTPUT;

         IF @retvalOUT = 1

         BEGIN

              INSERT INTO @RESULT SELECT @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @OBJECT_ID

         END

     END

     FETCH NEXT FROM CUR INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @OBJECT_ID

END

CLOSE CUR

DEALLOCATE CUR

SELECT * FROM @RESULT

Asynchronous Message Execution

Asynchronous message execution is a common requirement of enterprise applications. Suppose that you have a web application which receives financial transaction requests, processes them (create accounting entries) and generates response.

Throughout the life cycle of a single transaction, many operations may need to be executed without blocking current execution of request.For example, some kind of logging operations, notifying other integrated applications, etc.

This kind of non-blocking execution of operations can be fulfilled by way of message pools. In our type of message pool, there is one message pool in which the client code inserts its messages synchronously, but not executing messages.The message pool is listened by dedicated listener threads and responsibility of message execution belongs to them.

In this model, adding a message to the pool and taking a message from the pool may occur at the same time due to multithread model. For this reason, all the operations on pool must be thread safe.

For implementing this kind of message pool model, we can use ConcurrentQueue collection class of .NET Framework which is thread safe. Any kind of asynchronous execution request is enqueued by client code. Listener threads dequeue message one at a time, and invoke the "Execute" function of the message, which is contracted by an interface.

Here is the C# code:

Contract of the any message in the pool:

 interface IThreadPoolMessage
    {
        void Execute();
    }

--
Implementation of MessagePool class

Code:

class MessagePool : IDisposable
    {
        int listenerThreadCount;
        // The queue into which the messages are inserted.
        ConcurrentQueue<IThreadPoolMessage> msgQueue;
        // The flag for notifying the threads in order to stop.
        bool continueProcessingMessages = true;
        List<Thread> workerThreadList;
        public MessagePool(int listenerThreadCount)
        {
            this.listenerThreadCount = listenerThreadCount;
            Initialize();
        }

        void Initialize()
        {
            msgQueue = new ConcurrentQueue<IThreadPoolMessage>();
            workerThreadList = new List<Thread>();
            continueProcessingMessages = true;
            for (int i = 0; i < listenerThreadCount; i++)
            {
                Thread th = new Thread(new ThreadStart(ListenPool));
                th.Name = string.Format("NewThreadPool Thread_{0}", i + 1);
                th.IsBackground = true;
                workerThreadList.Add(th);
                th.Start();
            }
        }

       
        void ListenPool()
        {
            IThreadPoolMessage msg;
            while (continueProcessingMessages)
            {
                if (msgQueue.TryDequeue(out msg))
                {
                    try
                    {
                        msg.Execute();
                    }
                    catch (Exception ex)
                    {
                        // Logla.
                    }
                }
            }
        }


        public void AddMessage(IThreadPoolMessage msg)
        {
            this.msgQueue.Enqueue(msg);
        }

        public void StopAllThreads()
        {
            this.continueProcessingMessages = false;
            while (!workerThreadList.TrueForAll(p => p.ThreadState == System.Threading.ThreadState.Stopped))
            {
                ;
            }
        }

        public bool HasAwatingMessage { get { return this.msgQueue.Any(); } }

        public void Dispose()
        {
            StopAllThreads();
        }
    }

Here is a sample message, which is implemented for determining primality of an integer.

Code:

class CalculateIsPrimeMessage : IThreadPoolMessage
    {
        int val;
        public CalculateIsPrimeMessage(int val)
        {
            this.val = val;
        }
        public void Execute()
        {
            bool isPrime = IsPrime(val);
            WriteToDb(val, isPrime);
        }

        void WriteToDb(int val, bool isPrime)
        {
            using (SqlConnection conn = new SqlConnection("Server=.;Database=DENEME;Trusted_Connection=YES"))
            {
                string cmdStr = string.Format("INSERT INTO dbo.PRIMES SELECT {0}, {1}", val, isPrime ? 1 : 0);
                using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        bool IsPrime(int val)
        {
            if (val % 2 == 0)
            {
                return val == 2;
            }
            else if (val % 3 == 0)
            {
                return val == 3;
            }
            else if (val % 5 == 0)
            {
                return val == 5;
            }
            for (int i = 7; i * i <= val; i += 2)
            {
                if (val % i == 0)
                {
                    return false;
                }
            }
            return true;
        }
    }

--
Here is the sample client code which used message pooling system:

Code:

 static void Main(string[] args)
        {
            MessagePool msgPool = new MessagePool(10);
            Stopwatch sw = Stopwatch.StartNew();
            for (int i = 2; i < 10000; i++)
            {
                IThreadPoolMessage msg = new CalculateIsPrimeMessage(i);
                msgPool.AddMessage(msg);
            }

            while (msgPool.HasAwatingMessage)
            {
                ;
            }
            msgPool.Dispose();
            sw.Stop();
            Console.WriteLine("Elapsed Milliseconds: {0}", (int)sw.Elapsed.TotalMilliseconds);
        }

--

Reducing Memory Consumption of .NET Dictionary Class

.NET programmers generally use parameterless constructor of .NET Dictionary class. Actually, the class has various constructors. Here I am focused on the one, which takes the initial capacity as parameter.

Using public Dictionary(int capacity)constructor, reduces memory consumption of instance dramatically.

To measure the difference clearly, we create here 2 dictionaries of type Dictionary<long, string>;

n  One with unknown capacity,

n  One with known capacity

We add 1 million items to each of them. Here are the results:

Code:

using System;
using System.Collections.Generic;

namespace caDictionaryMemory
{
    class Program
    {
        const int length = 1000000;
        static void Main(string[] args)
        {
            Dictionary<long, string> dictWithCapacity = new Dictionary<long, string>(length);
            long lengthWithCapacity = AddItemsAndFindMemoryUsage(dictWithCapacity, length);

            Dictionary<long, string> dictWithoutCapacity = new Dictionary<long, string>();
            long lengthWithoutCapacity = AddItemsAndFindMemoryUsage(dictWithoutCapacity, length);
            Console.WriteLine("Memory Usage When Capacity Known: {0:0,0} KB", lengthWithCapacity);
            Console.WriteLine("Memory Usage When Capacity Unknown: {0:0,0} KB", lengthWithoutCapacity);
            Console.WriteLine();
            // Dummy usage of dictionaries to prevent Garbage Collector to collect them.
            Console.WriteLine("Item Count When Capacity Known: {0:0,0}", dictWithCapacity.Count);
            Console.WriteLine("Item Count When Capacity Unknown: {0:0,0}", dictWithoutCapacity.Count);
        }

        static long AddItemsAndFindMemoryUsage(Dictionary<long, string> dict, int count)
        {
            long beforeBytes = GC.GetTotalMemory(true);
            for (int i = 0; i < count; i++)
            {
                dict.Add(i, i.ToString());
            }
            long afterBytes = GC.GetTotalMemory(true);
            return (afterBytes - beforeBytes) / 1024;
        }
    }
}

The dictionary with unknown capacity uses 221% memory compared to known one.

Programmatic Analysis of SQL Server Execution Plan

Developers generally display the execution plan on SQL Server Management Studio visually. (Most known method: Select name of the procedure and press CTRL + L). SQL Server Database Engine serves execution plans in XML format. The images you see for plan in Management Studio is the interpretation of this XML string.

This XML formatted execution plan can be examined programmatically in order to check for certain operations.

Scenario: For a given stored procedure, I want to determine if the proc performs table scan or index scan operations on large tables.

In SQL Server connection, queries which are executed after "SET SHOWPLAN_XML ON" are not executed, but execution plan is created only.

Here is the class that retrieves Execution Plan XML of a stored proc.

Code:

static class ExecutionPlanRetriever
    {
        public static string Retrieve(string spName, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = GetCommandWithParams(spName, connectionString))
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SetShowPlanOn(conn);
                        SetCommandParamsByDefault(cmd);
                        cmd.CommandTimeout = 0;
                        string planStr = cmd.ExecuteScalar().ToString();
                        return planStr;
                    }
                }
                catch (Exception e)
                {
                }
            }
            return null;
        }

        static SqlCommand GetCommandWithParams(string spName, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(cmd);
                return cmd;
            }
        }

        static void SetShowPlanOn(SqlConnection conn)
        {
            string cmdText = "SET SHOWPLAN_XML ON";
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        static void SetCommandParamsByDefault(SqlCommand cmd)
        {
            foreach (SqlParameter prm in cmd.Parameters)
            {
                object val = GetDefaultSqlTypeValue(prm.SqlDbType);
                if (val is DataTable)
                {
                    prm.TypeName = ((DataTable)val).TableName;
                }
                prm.Value = val;
            }
        }

        static object GetDefaultSqlTypeValue(SqlDbType dbType)
        {
            object val = null;
            switch (dbType)
            {
                case System.Data.SqlDbType.BigInt: val = 0;
                    break;
                case System.Data.SqlDbType.Binary: val = val = new byte[1] { 0 };
                    break;
                case System.Data.SqlDbType.Bit: val = 0;
                    break;
                case System.Data.SqlDbType.Char: val = 'A';
                    break;
                case System.Data.SqlDbType.Date: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTime: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTime2: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTimeOffset: val = null;
                    break;
                case System.Data.SqlDbType.Decimal: val = 0;
                    break;
                case System.Data.SqlDbType.Float: val = 0;
                    break;
                case System.Data.SqlDbType.Image: val = null;
                    break;
                case System.Data.SqlDbType.Int: val = 0;
                    break;
                case System.Data.SqlDbType.Money: val = 0;
                    break;
                case System.Data.SqlDbType.NChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.NText: val = string.Empty;
                    break;
                case System.Data.SqlDbType.NVarChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Real: val = 0;
                    break;
                case System.Data.SqlDbType.SmallDateTime: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.SmallInt: val = 0;
                    break;
                case System.Data.SqlDbType.SmallMoney: val = 0;
                    break;
                case System.Data.SqlDbType.Text: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Time: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.Timestamp: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.TinyInt: val = 0;
                    break;
                case System.Data.SqlDbType.UniqueIdentifier: val = string.Empty;
                    break;
                case System.Data.SqlDbType.VarBinary: val = new byte[1] { 0 };
                    break;
                case System.Data.SqlDbType.VarChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Variant: val = null;
                    break;
                case System.Data.SqlDbType.Xml: val = null;
                    break;
                default:
                    break;
            }
            return val;
        }
    }

Now, once the XML string is ready, I can search for certain operations by using XPath.
Assuming we have the list of large tables, we can determine risky scan operations as:

Code:

    class DbObjectName
    {
        public string Schema { get; set; }
        public string Name { get; set; }
    }

    static class ExecutionPlanChecker
    {
        static DbObjectName[] TableIndexScanRiskTables = new DbObjectName[] {
            new DbObjectName { Schema = "SAMPLE_SCHEMA", Name = "SAMPLE_TABLE" }, 
        };

        // Resource for scan types:
        //http://technet.microsoft.com/en-us/library/ms186954(v=sql.105).aspx
        static readonly string[] riskyOperationNames = new string[] { "Table Scan", "Index Scan", "Clustered Index Scan" };

        public static bool PlanHasRiskyScanOperation(string planXml)
        {
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(planXml);
            doc = StripNamespace(doc);
            foreach (var opName in riskyOperationNames)
            {
                if (HasRiskyScanOperation(doc, opName))
                {
                    return true;
                }
            }
            return false;
        }

        static XmlDocument StripNamespace(XmlDocument doc)
        {
            if (doc.DocumentElement.NamespaceURI.Length > 0)
            {
                doc.DocumentElement.SetAttribute("xmlns", string.Empty);
                XmlDocument newDoc = new XmlDocument();
                newDoc.LoadXml(doc.OuterXml);
                return newDoc;
            }
            else
            {
                return doc;
            }
        }

        static bool HasRiskyScanOperation(XmlDocument doc, string scanOperationName)
        {
            string pattern = string.Format("//RelOp[@LogicalOp='{0}']", scanOperationName);
            XmlNodeList nodeList = doc.SelectNodes(pattern);
            foreach (XmlNode node in nodeList)
            {
                if (NodeTargetsRiskyTable(node))
                {
                    return true;
                }
            }
            return false;
        }
        
        static bool NodeTargetsRiskyTable(XmlNode node)
        {
            foreach (var item in TableIndexScanRiskTables)
            {
                string pattern = string.Format(@"OutputList/ColumnReference[@Schema='[{0}]' and @Table='[{1}]']", item.Schema, item.Name);
                if (node.SelectNodes(pattern).Count > 0)
                {
                    return true;
                }
            }
            return false;
        }
    }

Here, the client code calls "PlanHasRiskyScanOperation" function by passing the execution plan.

An Algorithm for Restricting CPU Usage of CPU Bound Programs

For a CPU bound program which requires multi thread execution, we sometimes need certain number of processor cores to be used by our program and other cores to be left unassigned so that, other processes may utilize them.

Let's make the idea more clear:

I develop an exe application which performs long running calculations, such as running heuristic algorithms. During the calculations, I want to reserve some CPU resource for other processes so that my program will not "eat up" all the CPU resource.

Processor assignment can be achieved by setting processor affinity to threads:

C# Code:

        [DllImport("kernel32")]
        static extern int GetCurrentThreadId();

        static int unusedProcessorCount = 1;

        static void SetProcessorAffinity(int threadOrder)
        {
            int curThreadId = GetCurrentThreadId();
            int processorOrder = threadOrder % (Environment.ProcessorCount - unusedProcessorCount);
            int processorAddress = 1 << processorOrder;
            foreach (ProcessThread th in Process.GetCurrentProcess().Threads)
            {
                if (th.Id == curThreadId)
                {
                    th.ProcessorAffinity = (IntPtr)processorAddress;
                }
            }
        }

We can specify the number of cores that we want to be unassigned by setting the variable "unusedProcessorCount".

In the code above, the function that fetches current thread id needs to be imported from kernel32.dll. 

As soon as the function that thread executes is called, this function needs to call SetProcessorAffinity by passing its thread order so that the thread is assigned to the processor which comes up next.

Retrieving All System Details by Windows PowerShell

Have you ever heard about Windows PowerShell? PowerShell is a great scripting tool for administrative tasks on computer.

So why is it "power"? Because it extends standard shell scripting with the power of .NET Framework. While accessing standard "command let"s of PowerShell, you can use any .NET Framework class. In addition to this, standard cmdlet library of PowerShell is quite enough for basic tasks that a system administrator may need.

Common Scenario: I want to display, all essential details of any computer on network. I want something quite more "advanced" than typing systeminfo on Windows command prompt.

The details I need are:             

n  Computer Name

n  Operating System,

n  Model

n  Manufacturer

n  Last Bootup Time

n  IP Address

n  Physical Memory Details: Capacity of each RAM on slots

n  Processor Details: For each  physical processor; number of cores, number of logical processors

n  Logical Disk Details: For each disk, free and total size

n  Process Details: Process name, Id, Memory Usage, Percent Load

We can achieve all, for any computer on network, by specifying computer name only.

Here is the script:
(You can run this script on Windows PowerShell ISE)

$computername = Read-Host 'Please Enter Computer Name'

$Name = Get-WmiObject win32_OperatingSystem -computer $computername | Select csName
$OperatingSystem = Get-WmiObject win32_OperatingSystem -computer $computername | Select Caption
$Model = get-wmiObject Win32_ComputerSystem  -computer $computername | select Model
$Manufacturer = get-wmiObject Win32_ComputerSystem  -computer $computername | select Manufacturer
$LastBootTime = Get-WmiObject Win32_OperatingSystem -computer $computername | select csname, @{LABEL='LastBootUpTime'; EXPRESSION={$_.ConverttoDateTime($_.LastBootupTime)}}
$PhysicalMemoryList = Get-WmiObject Win32_PhysicalMemory -computer $computername | select DeviceLocator, @{Name='Capacity_MB'; Expression={$_.Capacity / 1024 / 1024}}
$FreeMemoryMB = get-WmiObject Win32_PerfRawData_PerfOS_Memory -computer $computername | select AvailableMBytes 
$DiskList = Get-WmiObject Win32_LogicalDisk -computer $computername | select Caption, Description, @{Name="Total_Size_GB"; Expression={$_.Size/1024/1024/1024}}, @{Name="Free_Size_GB"; Expression={$_.FreeSpace/1024/1024/1024}}
$ProcessorList = Get-WmiObject Win32_Processor -computer $computername  | select Name, NumberOfCores, LoadPercentage, NumberOfLogicalProcessors
$allIpList = [System.Net.Dns]::GetHostEntry($computername) | select AddressList
$IP = ''
foreach ($item in $allIpList.AddressList)
{
 if ($item -notlike '*:*')
 {
  $IP = $item
 }
}

$ResObject = @{
ComputerName = $Name.csName
OperatingSystem = $OperatingSystem.Caption
Model = $Model.Model
Manufacturer = $Manufacturer.Manufacturer
LastBootUpTime = $LastBootTime.LastBootUpTime
FreeMemoryMB = $FreeMemoryMB.AvailableMBytes
IPAddress = $IP.IpAddressToString
#PhysicalMemoryList = $PhysicalMemoryList | Format-Table
#DiskList = $DiskList | Format-Table
#ProcessorList = $ProcessorList | Format-Table
}

$ResObject | Format-List

Write-Host 'Physical Memory Details: '
$PhysicalMemoryList | Format-Table

Write-Host 'Disk Details: '
$DiskList | Format-Table

Write-Host 'Processor Details: '
$ProcessorList | Format-Table

$answer = Read-Host "Do you want to get process details? Yes[Y]; No[N]"
if ($answer.ToUpper() -ne 'Y')
{
 Read-Host 'Script Ended'
 Exit
}

$procListDef = Get-WmiObject win32_process -computer $computername | select Name, ProcessId, @{Name='Owner'; Expression={$_.getowner() | select Domain, User}  }
$procListResource = get-WmiObject Win32_PerfFormattedData_PerfProc_Process -computer $computername | select Name, IDProcess, PercentProcessorTime, Timestamp_Sys100NS, WorkingSet

foreach ($proc in $procListDef )
{
 $ownerFullName = $proc.Owner.Domain + '\' + $proc.Owner.User;
 $res = $procListResource | Where-Object {$_.IDProcess -eq $proc.ProcessId}
 $percent = $res.PercentProcessorTime / [Environment]::ProcessorCount;
 $ws = $res.WorkingSet / 1024 / 1024
 
 $proc | Add-Member -type NoteProperty -name OwnerName -value $ownerFullName 
 $proc | Add-Member -type NoteProperty -name MemoryInMb -value $ws
 $proc | Add-Member -type NoteProperty -name PercentProcessorTime -value $percent
}

$procListDef | select Name, ProcessId, OwnerName, MemoryInMb, PercentProcessorTime | sort PercentProcessorTime -Descending | Format-Table
Read-Host 'Script Ended'