Solved

SQL server performance when running reports...

Posted on 2016-09-27
33
73 Views
Last Modified: 2016-10-28
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
0
Comment
Question by:MJB2011
  • 12
  • 11
  • 10
33 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41817641
Can you post execution plan for your query.?

Or you can also update statistics on your table.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41817657
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
 

Author Comment

by:MJB2011
ID: 41817671
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41817677
then SQL Query of your report needs to be improved. Please post it. I shall do it.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41817679
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
 

Author Comment

by:MJB2011
ID: 41817685
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41817700
You can place a profiler and get the query. Stats may be one of the reason your query is taking time.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41817704
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
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41817748
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
 

Author Comment

by:MJB2011
ID: 41818084
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41818127
Great ! waiting for the query plan
0
 

Author Comment

by:MJB2011
ID: 41819457
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41819468
Without the query or query plan we cant do much. You can update the stats and check. Normally updating really helps.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41819472
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
 

Author Comment

by:MJB2011
ID: 41819892
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41819904
GO to SSMS ,--> Tools  --> SQL Profile  --> Choose Database Engine...
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41819945
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
 

Author Comment

by:MJB2011
ID: 41820069
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41820081
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
 

Author Comment

by:MJB2011
ID: 41821463
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41821467
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
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41821468
Check if this article can help you.
0
 

Author Comment

by:MJB2011
ID: 41821481
OK I have some data.... what am I looking for here?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41821484
okay
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41821485
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
 

Author Comment

by:MJB2011
ID: 41821769
Not seeing anything out of the ordinary?

cap2
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41821809
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41821813
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41842111
@author, a feedback will be appreciated.
0
 

Author Comment

by:MJB2011
ID: 41842126
Your assistance was greatly appreciated but im still unsure of whether the data is showing obviously bottlenecks or not.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41842130
That's fair. How can we help you resolving this issue?
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41863440
MJB2011, do you still need help on this question or not?
If negative, then please close this question.
Cheers
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41863447
@Author - Is your problem fixed ?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question