Getting number of query executions for a definite period of time

Question

I have an application that uses a single database on MS SQL Server. I need to learn some statistics about application requests to database to analyse it and improve performance.

Basically I need kind of a summary of information that is displayed in Recent Expensive Queries in Activity Monitor of SQL Server Management Studio. The most important thing to know is total number of executions of the query for definite period of time (working time of a heavy function). Please, take into consideration that a query in Activity Monitor looks like that:

SELECT [A],[B],[C] FROM [Table] WHERE [ID]=@1

so, there are often same query but with different parameter(s).

Please, let me know the options for gathering such data or just share your ideas of "how to". I will learn and try it and write about the results.
Thanks!

Answer 1

I really enjoy the articles that this individual provides

http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

Also, I would make sure that your application has its own SQl Login because it will make things much easier when analyzing data.

Answer 2

You can get all query that now is executing.

USE master;

SELECT  st.text,
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time
FROM    sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

And you can get all 50 last query that are execued

SELECT TOP 50
    deqs.last_execution_time AS [Time],
    dest.text AS [Query]
FROM    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

And I you suggest that with SQL Server Profiler, Profile your database and save your result in one table in a database and then select all command and group by that to analyzes.

Select * 
From
(
    Select 
        Convert(VarChar(Max) , PT.TextData ) As ExecutedCommand ,
        COUNT( RowNumber ) As CommandCount
    From ProfilerTable As PT (nolock)
    Where       
        ( PT.TextData Is Not Null )
        And
        ( PT.EndTime Is Not Null )
    Group By Convert( VarChar( Max ) , PT.TextData )
) As SubQuery
Order By SubQuery.CommandCount Desc

Answer 3

Plug the query into SSMS and turn on Query/Include Actual Execution Plan. This will take some getting used to to read, but, in general, look for table scans. A table scan usually means that an index should be present on a join, group by or where clause column.