Determine which SQL statements consuming most of the CPU & RAM

Problem:-  One of the most important question comes in our mind what is the cause of slow  SQL SERVER.

We always struggle with following questions

Which is highly CPU consuming query ?

Which is highly RAM consuming query ?

Who is blocking the transaction ?

and many more other performance dragging questions.

Believe me if you know who is culprit of making your system slow, you will win half battle.

Solution:-  SQL Server provided an easy way to clear your all doubts related to above questions. This easy way is “STANDARD REPORTS”.

You can access Standard Reports option by right clicking the SQL SERVER instance. These Standard reports contains not only performance related reports but other useful reports also.

If you see below image you will find there are many other reports option available.

Top_Cpu_consuming_Query_Indiandotnet

Now, suppose I am interested to know which query consuming high CPU , to achieve this I clicked on   Performance Query – TOP Queries by Average CPU time or Performance query – Top queries  by total CPU time.

When I clicked on any of this option ,I got a report.

This report contained a bar graph & query detail in tabular format which contains query & CPU consuming time as shown below.

Performance_Graph

tabular_Query

in similar way we can get answer of our other performance related query also.