which are query using high usage.

Find out sql server Usage.

SELECT TOP 100

qs.execution_count,

DatabaseName = DB_NAME(qp.dbid),

ObjectName = OBJECT_NAME(qp.objectid,qp.dbid),

StatementDefinition =

SUBSTRING (

st.text,

(

qs.statement_start_offset / 2

) + 1,

(

(

CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset

) / 2

) + 1

),

query_plan,

st.text, total_elapsed_time

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp

WHERE

st.encrypted = 0

ORDER BY qs.execution_count DESC

Month day count

-- select dbo.F_MonthCount('2012-02-03')

-- =============================================

ALTER FUNCTION [dbo].[F_MonthCount]

(

@date datetime

)

RETURNS int

AS

BEGIN

      DECLARE @start datetime,@count int

      select @start=cast(year(@date) as varchar(4))+'-'+cast(Month(@date) as varchar(2))+'-'+'01'

    SELECT @count=datediff(day,@start,DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)))+1

      RETURN @count

 

END