using the Dynamic management views

SELECT QS.CREATION_TIME,QS.TOTAL_ELAPSED_TIME,QS.EXECUTION_COUNT,QT.TEXT,DB_NAME(QT.DBID) AS DATABASENAME,OBJECT_NAME(QT.OBJECTID) AS OBJECTNAME,QP.QUERY_PLAN 

FROM SYS.DM_EXEC_QUERY_STATS QS 

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT 

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP

ORDER BY QS.TOTAL_ELAPSED_TIME DESC 

in the above query SYS.DM_EXEC_QUERY_STATS  is system table
SYS.DM_EXEC_SQL_TEXT(),SYS.DM_EXEC_QUERY_PLAN() are table valued functions 



same way we can find the slowest procedures in the database

SELECT QS.TOTAL_ELAPSED_TIME,QS.EXECUTION_COUNT,QT.TEXT,DB_NAME(QT.DBID) AS DATABASENAME,OBJECT_NAME(QT.OBJECTID) AS OBJECTNAME,QP.QUERY_PLAN 

FROM SYS.DM_EXEC_PROCEDURE_STATS QS 

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT 

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP

ORDER BY QS.TOTAL_ELAPSED_TIME DESC 
 
Finding queries which require most I/O
SELECT QS.TOTAL_LOGICAL_READS,QS.TOTAL_LOGICAL_WRITES,QS.EXECUTION_COUNT,QT.TEXT,DB_NAME(QT.DBID) AS DATABASENAME,OBJECT_NAME(QT.OBJECTID) AS OBJECTNAME,QP.QUERY_PLAN 

FROM SYS.DM_EXEC_QUERY_STATS QS 

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT 

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP

ORDER BY QS.TOTAL_LOGICAL_READS+QS.TOTAL_LOGICAL_WRITES DESC