SQL server performance when running reports...

Can you post execution plan for your query.?

Or you can also update statistics on your table.

We'll need more information. By the graphics I can only see that it had one minute of peaks for batch processing and at 9:07 there was an higher demand of disk writing. Should this be an import process?
On the other this may mean nothing special.
Also, processor time has a 40% average but there's nothing about the memory usage.
And I can see that you took counters from a named instance and that might mean you have at least another SQL Server instance installed.

Btw, what are the SQL Server versions and editions that are installed in the machine?

SQL 2012 SP1

My understanding is that it drags data from an oracle database.

There is only one instance of SQL but there are other databases (doing very little)

The peak you see is the report that ran and took 34 seconds.

then SQL Query of your report needs to be improved. Please post it. I shall do it.

The peak you see is the report that ran and took 34 seconds.

Depending on the quantity of data that is being handled and taking in consideration that the data is coming from an Oracle database, 34 seconds for a report may not be that bad.
Anyway, if you want, share with us the query but if the query is retrieving data from Oracle you might need to tune the Oracle database and not the SQL Server one.

OK I will try and retrieve the query, but this is a GUI based reporting tool so not sure how to get that. The main issue we have is that it may take 34 seconds today, but the same report and another time of day, or a different report will take much much longer. So im just to discover where the bottle neck is.

You can place a profiler and get the query. Stats may be one of the reason your query is taking time.

OK I will try and retrieve the query, but this is a GUI based reporting tool so not sure how to get that.

Can't you get the person or the team that designed the report?

The main issue we have is that it may take 34 seconds today, but the same report and another time of day, or a different report will take much much longer.

It can depend on the filter used (some can use indexes so it will run faster) and the time the report is being run (during period of more activities it should run slower).

Looking at the spreadsheet exported by the query i can see there is about 34000 lines of data and 20 columns.

Will try and dig out a query plan

Great ! waiting for the query plan

The client we use to run reports is a 3rd party who dont appear to want to help us. The query will be different depending which report template and variables they apply, ie. date range, company name etc. This particular report I have chosen to monitor is for transactions.

What else can I do to dig a bit deeper with this?

Without the query or query plan we cant do much. You can update the stats and check. Normally updating really helps.

This seems to be a political issue between your company and the vendor. What kind of support you have from them? Maybe you can have them looking for the performance issue.

Another option is to have SQL Profiler running to capture the queries so you can see the query variations.

I wouldn't say its political, this is the usual battle you tend to have with performance related issues. They will say its my SQL server under performing and I will say its a badly written query.

SQL profiler is an option. Please can you help me do this?

GO to SSMS ,--> Tools  --> SQL Profile  --> Choose Database Engine...

Click on the link from my last comment. I think is easy to follow those instructions. If you have particular issues with that just return with the issue and I'll try to help you.

Should I run the profiler just whilst the report runs or shall i run for longer periods?

Do I save to file? and should I use the standard template?

Yes, run the Profiler few seconds before you run the report so you can be sure that you'll capture mostly queries from the report.
You can save the file for later analysis. Personally I will avoid standard templates since I never found them helpful at all. Start with a blank template and add the events (SQL statements), columns  (at least SPID, Text data, Start time and duration should be included and you might find that extra columns may also be helpful) and filters (Database ID and User Name) as you think you need.

Hi Im on the events selection tab but im not sure which of the ones you are suggesting to choose?
SPID, Text data, Start time and duration - I cannot see any of these? Can you please elaborate?

There click on Column Filters Section ( You will get that at the right hand bottom ). There you will find these filters..

See Attached.

Enjoy ! Pawan.
SQl-Profiler.jpg

OK I have some data.... what am I looking for here?

okay

OK I have some data.... what am I looking for here?

Im currently looking into performance issues

Performance issues should be long running queries, right? So, verify the queries that have bigger duration and start to analyze them.
NOTE: Duration comes in ms, so 1000ms=1s.

Not seeing anything out of the ordinary?

Try running below query and see if you are getting any queries. [Brent ozar's Query]

SELECT  st.text,
        qp.query_plan        
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 200
      OR qs.max_elapsed_time > 200

Select allOpen in new window

Not seeing anything out of the ordinary?

That can be a good sign. You can also apply a filter for duration > 30000 so it will capture only queries running more that 30 seconds. This will help you analyze only the necessary data.

@author, a feedback will be appreciated.

Your assistance was greatly appreciated but im still unsure of whether the data is showing obviously bottlenecks or not.

That's fair. How can we help you resolving this issue?

MJB2011, do you still need help on this question or not?
If negative, then please close this question.
Cheers

@Author - Is your problem fixed ?