/* Looking for top running query stats.
Keep in mind that the stats in "sys.dm_exec_query_stats" are reset when SQL Server starts and when
you drop or recreate their components (Proc, Tables).
This Query come from the URL below.
 http://www.sql-server-performance.com/2008/Query-Execution-Statistics/
*/
SELECT TOP 10 COALESCE(DB_NAME(st.dbid),DB_NAME(CAST(pa.value as int))+'*','Resource') AS DBNAME, 
       SUBSTRING(text,-- starting value for substring 
        CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 
               ELSE statement_start_offset/2 + 1 END,
         -- ending value for substring
         CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1 OR statement_end_offset IS NULL 
               THEN LEN(text) 
               ELSE statement_end_offset/2 END - CASE WHEN statement_start_offset = 0 
                          OR statement_start_offset IS NULL THEN 1 
                               ELSE statement_start_offset/2  END + 1
         )  AS TSQL, 
SUBSTRING(CONVERT(CHAR(23),DATEADD(ms,(total_worker_time/execution_count)/1000,0),121),12,23)  AVG_CPU_MS,
total_logical_reads/execution_count AS AVG_LOGICAL_READS,
SUBSTRING(CONVERT(CHAR(23),DATEADD(ms,(total_elapsed_time/execution_count)/1000,0),121),12,23) AS AVG_ELAPSED_TIME

 FROM sys.dm_exec_query_stats  
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
     WHERE attribute = 'dbid'  
   ORDER BY AVG_CPU_MS DESC ;
 --ORDER BY AVG_LOGICAL_READS DESC ;
 --ORDER BY AVG_ELAPSED_TIME DESC ;

/* SQL Stats Lookup for a database.
http://blog.sqlauthority.com/2011/06/29/sql-server-find-details-for-statistics-of-whole-database-dmv-t-sql-script/
*/
USE AdventureWorks
 GO
 SELECT DISTINCT
 OBJECT_NAME(s.[object_id]) AS TableName,
 c.name AS ColumnName,
 s.name AS StatName,
 s.auto_created,
 s.user_created,
 s.no_recompute,
 s.[object_id],
 s.stats_id,
 sc.stats_column_id,
 sc.column_id,
 STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
 FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
 JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
 JOIN sys.partitions par ON par.[object_id] = s.[object_id]
 JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
 WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
 AND (s.auto_created = 1 OR s.user_created = 1);
/* Microsoft Technet
Troubleshooting Performance Problems in SQL Server 2005 
http://technet.microsoft.com/en-us/library/cc966540.aspx
*/

/* CPU Bottlenecks
System Monitor is generally the best means to determine if the server is CPU bound. You should look to see if the Processor:% Processor Time counter is high; values in excess of 80% processor time per CPU are generally deemed to be a bottleneck. You can also monitor the SQL Server schedulers using the sys.dm_os_schedulers view to see if the number of runnable tasks is typically nonzero. A nonzero value indicates that tasks have to wait for their time slice to run; high values for this counter are a symptom of a CPU bottleneck. You can use the following query to list all the schedulers and look at the number of runnable tasks.
*/
 select  
    scheduler_id, 
    current_tasks_count, 
    runnable_tasks_count 
from  
    sys.dm_os_schedulers 
where  
    scheduler_id < 255
/* 
The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU. The query aggregates the CPU consumed by all statements with the same plan__handle (meaning that they are part of the same batch or procedure). If a given plan_handle has more than one statement, you may have to drill in further to find the specific query that is the largest contributor to the overall CPU usage. 
*/
select top 50  
    sum(qs.total_worker_time) as total_cpu_time,  
    sum(qs.execution_count) as total_execution_count, 
    count(*) as  number_of_statements,  
    qs.plan_handle  
from  
    sys.dm_exec_query_stats qs 
group by qs.plan_handle 
order by sum(qs.total_worker_time) desc
/* 
The remainder of this section discusses some common CPU-intensive operations that can occur with SQL Server, as well as efficient methods to detect and resolve these problems.
*/

/* http://technet.microsoft.com/en-us/library/cc966540.aspx */
/* http://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/ /*
-- http://www.databasejournal.com/features/mssql/article.php/3752866/Check-your-SQL-Server-using-Windows-PowerShell-150-Part-1.htm   --