Find most expensive cached queries

Below queries returns aggregate performance statistics for cached query plans.  The result contains one row per query within the cached plan. If the query plan is removed from the cache than that query is excluded from the output. sys.dm_exec_query_stats is a very useful DMV to get cached query details for performance & server load analysis.

Execution count of each query

SELECT QT.DBID, DB_NAME(QT.DBID) AS [Database Name], QT.ObjectID, QT.Text AS [Query Text], QS.Execution_Count AS [Execution Count], QS.Total_Elapsed_Time AS [Total Elapsed Time], QS.Max_Elapsed_Time AS [Max Elapsed Time], QS.Min_Elapsed_Time AS [Min Elapsed Time], QS.Last_Elapsed_Time AS [Last Elapsed Time] FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT ORDER BY QS.Execution_Count DESC

Top 5 I/O intensive Queries

SELECT TOP 5 Total_Logical_Reads [Total Logical Read], Total_Logical_Writes AS [Total Logical Write], Total_Logical_Reads + Total_Logical_Writes AS [IO Total], Execution_Count AS [Execution Count], QT.Text AS [Query Text], DB_NAME(QT.DBID) AS [Databaes Name], QT.ObjectID AS [Object IO] FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT WHERE Total_Logical_Reads + Total_Logical_Writes > 0 ORDER BY [IO Total] DESC

Top 5 CPU consuming Queries 

SELECT TOP 5 QT.TEXT AS [Statement Text], QS.TOTAL_WORKER_TIME AS [CPU Time], QP.QUERY_PLAN AS [Query Plan] FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP ORDER BY TOTAL_WORKER_TIME DESC

Top 5 Average CPU consuming Queries 

SELECT TOP 5 TOTAL_WORKER_TIME AS [Total Worker Time], EXECUTION_COUNT AS [Execution Count], TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU Time], QT.TEXT AS [Query Text] FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT ORDER BY QS.TOTAL_WORKER_TIME DESC

Installation Error Codes

• 2 – The system cannot find the file specified.
• 5 – Access denied.
• 52 – You were not connected because a duplicate name exists on the network. Make sure there is not a duplicate name in DNS and that 2 machines don’t have the same IP in DNS.
• 53 – Unable to locate – http://support.microsoft.com/kb/920852 – cannot connect to admin$ – Computer Browser not started – add File/print sharing to Exceptions in Firewall – turn file and print on.
• 58 – The specified server cannot perform The requested operation
• 64 – The specified network name is no longer available. Source: Windows
• 67 – network name cannot be found. 
• 86 – network password is not correct? Machine Name <> resolved name.
• 112 – Not enough disk space
• 1003 – Cannot complete this function.
• 1053 – The service did not respond to the start or control request in a timely fashion.
• 1068 – The dependency service or group failed to start
• 1130 – Not enough server storage is available to process this command. Source: Windows
• 1203 – The network path was either typed incorrectly, does not exist, or the network provider is not currently available. Please try retyping the path or contact your network administrator.
• 1208 – An extended error has occurred. Source: Windows
• 1396 – Logon Failure: The target account name is incorrect. (NBTSTAT -a reverse lookup, duplicate IP address)
• 1450 – Insufficient system resources exist to complete the requested service. Source: Windows
• 2147749889 – Generic WMI failure (Broken WMI)
• 2147749890 – not found – Source: Windows Management (WMI) – try repair WMI
• 2147749904 – Invalid class – Source: Windows Management (WMI)
• 2147749908 – Initialization failure – Source: Windows Management (WMI)
• 2147942405 – Access is Denied (Firewall rule? / MacAfee-HIPS?)
• 2147944122 – The RPC server is unavailable. (Dcom is miss-configured for security . http://support.microsoft.com/kb/899965 )
• 2148007941 – Server Execution Failed

Details of current running query

Use below query to check the details of current running Query on SQL Server.

SELECT R.Session_ID, R.Request_ID AS [Session Request ID], CASE WHEN S.Login_Name = S.Original_Login_Name THEN S.Login_Name ELSE S.Login_Name + ' (' + S.Original_Login_Name + ')' END AS [Login Name], S.Program_Name AS [Program Name], DB_Name(R.Database_ID) AS [Database Name], R.Status, S.Host_Name AS [Host Name], C.Client_Net_Address AS [Client Net Address], R.Command, ST.Text AS [Query Text], QP.Query_Plan AS [XML Query Plan], R.Last_Wait_Type AS [Last Wait Type], R.Wait_Type AS [Current Wait Type], R.Blocking_Session_ID AS [Blocking Session ID], R.Row_Count AS [Row Count], 
R.Granted_Query_Memory AS [Granted Query Memory], R.Open_Transaction_Count AS [Open Transaction Count], R.User_ID AS [User ID], R.Percent_Complete AS [% Complete], R.Start_Time AS [Start Time], R.CPU_Time AS [CPU Time], R.Estimated_Completion_Time AS [Estimated Completion Time], CASE R.Transaction_Isolation_Level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE CAST(R.Transaction_Isolation_Level AS VARCHAR(32)) END AS [Transaction Isolation Level]
FROM SYS.DM_EXEC_REQUESTS AS R LEFT OUTER JOIN SYS.DM_EXEC_SessionS AS S ON S.Session_ID = R.Session_ID LEFT OUTER JOIN SYS.DM_EXEC_ConnectionS AS C ON C.Connection_ID = R.Connection_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_Handle) AS ST CROSS APPLY SYS.DM_EXEC_Query_Plan(R.Plan_Handle) AS QP 
WHERE R.STATUS NOT IN ('Background','Sleeping')

Identity column information

Use below query to get information for all identity columns in  a database:

SELECT QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME, C.NAME AS COLUMNNAME, CASE C.SYSTEM_TYPE_ID WHEN 56 THEN 'INT' WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE, IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT, IDENT_SEED(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYSEED, CASE C.SYSTEM_TYPE_ID WHEN 56 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 2147483647 WHEN 52 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 32767 WHEN 48 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 255 END AS 'PERCENTAGEUSED'
FROM SYS.COLUMNS AS C INNER JOIN SYS.TABLES AS T ON T.[OBJECT_ID] = C.[OBJECT_ID] AND C.IS_IDENTITY = 1 ORDER BY PERCENTAGEUSED DESC