Find Most Blocked Queries and Sp's

SELECT
OBJECT_NAME(objectid)
,BlockTime = total_elapsed_time - total_worker_time
,execution_count
,total_logical_reads
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY total_elapsed_time - total_worker_time DESC

using Mutex in threads instead of thread lock

Use the below sample in a console application and run it as 2 exe, to understand mutex

Mutex m = new Mutex(false, "MyMutex");

// Try to gain control of the named mutex. If the mutex is
// controlled by another thread, wait for it to be released.
Console.WriteLine("Waiting for the Mutex.");
m.WaitOne();

// Keep control of the mutex until the user presses
// ENTER.
Console.WriteLine("This application owns the mutex. " +
"Press ENTER to release the mutex and exit.");
Console.ReadLine();

m.ReleaseMutex();

Create Async web method

The below code shows the sample of Async web method.
Note: the web method should not return any vlaue
/// Write the given text in the log file path
[WebMethod]
[SoapDocumentMethod(OneWay = true)]
public void WriteLogFile(String inputString, String stringFileName)
{
// write your code
}

Change DataTable ColumnType after loading data

Giving Dataset as input

private DataSet ChangeDataTableColumnType(DataSet ds,string tablename,string columnName,Type dataType)
{
DataTable dt = ChangeDataTableColumnType(ds.Tables[tablename], columnName, dataType);
ds.Tables.Remove(tablename);
ds.Tables.Add(dt);
return ds;
}

Giving DataTable as input

private DataTable ChangeDataTableColumnType(DataTable dt,string columnName,Type dataType)
{
DataTable newDt = dt.Clone();
newDt.Columns[columnName].DataType = dataType;
newDt.Merge(dt, true, MissingSchemaAction.Ignore);
return newDt;
}

SQL : Search text in store procedure

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%search text%'
AND ROUTINE_TYPE='PROCEDURE'

DB structure compare query

select distinct s1.type,s1.type_desc,count(s1.type)
FROM sys.objects s1 with(nolock)
left join sys.objects s2 with(nolock) on s2.object_id = s1.parent_object_id
where s1.name not like '%__SQLXMLB%' and s1.type not in ('IT','SQ')
group by s1.type,s1.type_desc

-------------------------------------------------------------------------------------

SELECT distinct s1.name,s1.type,s1.type_desc,s2.name as parentname
FROM sys.objects s1 with(nolock)
left join sys.objects s2 with(nolock) on s2.object_id = s1.parent_object_id
where s1.name not like '%__SQLXMLB%' and s1.type not in ('IT','SQ')
order by s1.type,s1.name

SQl : Grouping Sets

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping in the same query. Grouping Sets produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

Example:

SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders FROM sales_order GROUP BY GROUPING SETS ((Year, Quarter), (Year))

ORDER BY Year, Quarter