Find which stored procedures taking more time to execute using SQL Server DMV Query

It is very important to optimize the SQL Server stored procedure query to improve the performance. Optimizing the query is not a one time task as it needs to be checked frequently based on the developers activity on database related changes. Adding one single column to an existing select query would leads to performance degradation. There are many ways to monitor and identify the query performance. One of which is DMV (Dynamic Management Views).

DMV is nothing but queries returning information about server state that is current at the time the query was run. The information which are returned are actually returned from the cache. Every time a query executes, its information will be stored in the cache.

Below is the DMV query to identify the stored procedure which uses most resources which taking more time to execute. It is also returning the Query Plan in the last column which is very useful to identify the query section and optimize it.

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]  
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS,
      queryplan.query_plan
FROM sys.dm_exec_procedure_stats querystatus
CROSS APPLY sys.dm_exec_query_plan(querystatus.plan_handle) queryplan
where DB_NAME(database_id) = 'DBNAME'
ORDER BY AVG_LOGICAL_READS DESC

You have to mention your database name in the where condition to return stored procedure information from that. As I said earlier, the last column shows the Query plan link. Clicking it would open the query plan of the corresponding stored procedure similar to the below diagram.

Sample Program to Convert String to Hex and Hex to String in CSharp (C#)

Here is the sample program to convert String to Hex and Hex to String in CSharp (C#).


String to Hex:

public static string ConvertStringToHex(String input, System.Text.Encoding encoding)
{
    Byte[] stringBytes = encoding.GetBytes(input);
    StringBuilder sbBytes = new StringBuilder(stringBytes.Length * 2);

    foreach (byte b in stringBytes)
    {
        sbBytes.AppendFormat("{0:X2}", b);
    }

return sbBytes.ToString();
}

Hex to String:

public static string ConvertHexToString(String hexInput, System.Text.Encoding encoding)
{
    int numberChars = hexInput.Length;
    byte[] bytes = new byte[numberChars / 2];

    for (int i = 0; i < numberChars; i += 2)
    {
        bytes[i / 2] = Convert.ToByte(hexInput.Substring(i, 2), 16);
    }

    return encoding.GetString(bytes);
}

Sample Program to Encrypt and Decrypt string using RijndaelManaged in CSharp (C#)

Here is a class to Encrypt and Decrypt a given string using RijndaelManaged. You have to change the private variable value based on your strength requirement of the encryption.

Encryption:

public static string Encrypt(string plainText)
{
    byte[] initVectorBytes = Encoding.ASCII.GetBytes(initVector);
    byte[] saltValueBytes = Encoding.ASCII.GetBytes(saltValue);
    byte[] plainTextBytes = Encoding.UTF8.GetBytes(plainText);
    PasswordDeriveBytes password = new PasswordDeriveBytes(passPhrase, saltValueBytes, hashAlgorithm, passwordIterations);
    byte[] keyBytes = password.GetBytes(keySize / 8);
    RijndaelManaged symmetricKey = new RijndaelManaged();
    symmetricKey.Mode = CipherMode.CBC;
    ICryptoTransform encryptor = symmetricKey.CreateEncryptor(keyBytes, initVectorBytes);
    MemoryStream memoryStream = new MemoryStream();
    CryptoStream cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write);
    cryptoStream.Write(plainTextBytes, 0, plainTextBytes.Length);
    cryptoStream.FlushFinalBlock();
    byte[] cipherTextBytes = memoryStream.ToArray();
    memoryStream.Close();
    cryptoStream.Close();
    string cipherText = Convert.ToBase64String(cipherTextBytes);
    return cipherText;
 }

Decryption:

public static string Decrypt(string cipherText)
{
    byte[] initVectorBytes = Encoding.ASCII.GetBytes(initVector);
    byte[] saltValueBytes = Encoding.ASCII.GetBytes(saltValue);
    byte[] cipherTextBytes = Convert.FromBase64String(cipherText);
    PasswordDeriveBytes password = new PasswordDeriveBytes(passPhrase, saltValueBytes, hashAlgorithm, passwordIterations);
    byte[] keyBytes = password.GetBytes(keySize / 8);
    RijndaelManaged symmetricKey = new RijndaelManaged();
    symmetricKey.Mode = CipherMode.CBC;
    ICryptoTransform decryptor = symmetricKey.CreateDecryptor(keyBytes, initVectorBytes);
    MemoryStream memoryStream = new MemoryStream(cipherTextBytes);
    CryptoStream cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read);
    byte[] plainTextBytes = new byte[cipherTextBytes.Length];
    int decryptedByteCount = cryptoStream.Read(plainTextBytes, 0, plainTextBytes.Length);
    memoryStream.Close();
    cryptoStream.Close();
    string plainText = Encoding.UTF8.GetString(plainTextBytes, 0, decryptedByteCount);
    return plainText;
 }

These private variables also needs to be initialized within the same class.

    private const string passPhrase = "Pas5pr@se";
    private const string saltValue = "s@1tValue";
    private const string hashAlgorithm = "MD5";
    private const int passwordIterations = 2;
    private const string initVector = "@1B2c3D4e5F6g7H8";
    private const int keySize = 256;