SQL server performance when running reports...

Hi All,

Im currently looking into performance issues around a client that reports against a SQL database. Im not a DBA and my knowledge of SQL is basic. I have been running some perfmon counters against the SQL server whilst running reports. I have installed the reporting client directly on the SQL server.

I have statistics but not entirely sure what they are telling me. I have no benchmark, but i can see definite peaks when the report is running.

Any assistance appreciated.
Capture.JPG
MJB2011Asked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Check if this article can help you.
0
 
Pawan KumarDatabase ExpertCommented:
Can you post execution plan for your query.?

Or you can also update statistics on your table.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
MJB2011Author Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
then SQL Query of your report needs to be improved. Please post it. I shall do it.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
MJB2011Author Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
You can place a profiler and get the query. Stats may be one of the reason your query is taking time.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
In most of the cases when the stats goes for a toss we see this kind of performance.

or

The query is poorly written which I think will not be the case.

or

we may need to introduce an index to improve the performance

or

we may need to tweak the query to improve the performance.

Once you have query or queryplan we can dig this more further.
0
 
MJB2011Author Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
Great ! waiting for the query plan
0
 
MJB2011Author Commented:
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?
0
 
Pawan KumarDatabase ExpertCommented:
Without the query or query plan we cant do much. You can update the stats and check. Normally updating really helps.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
MJB2011Author Commented:
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?
0
 
Pawan KumarDatabase ExpertCommented:
GO to SSMS ,--> Tools  --> SQL Profile  --> Choose Database Engine...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
MJB2011Author Commented:
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?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
MJB2011Author Commented:
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?
0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
MJB2011Author Commented:
OK I have some data.... what am I looking for here?
0
 
Pawan KumarDatabase ExpertCommented:
okay
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
MJB2011Author Commented:
Not seeing anything out of the ordinary?

cap2
0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
@author, a feedback will be appreciated.
0
 
MJB2011Author Commented:
Your assistance was greatly appreciated but im still unsure of whether the data is showing obviously bottlenecks or not.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's fair. How can we help you resolving this issue?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
MJB2011, do you still need help on this question or not?
If negative, then please close this question.
Cheers
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Is your problem fixed ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.