Solved

SQL server performance when running reports...

Posted on 2016-09-27
33
63 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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Can you post execution plan for your query.?

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

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
then SQL Query of your report needs to be improved. Please post it. I shall do it.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
You can place a profiler and get the query. Stats may be one of the reason your query is taking time.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 250 total points
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Great ! waiting for the query plan
0
 

Author Comment

by:MJB2011
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Without the query or query plan we cant do much. You can update the stats and check. Normally updating really helps.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
GO to SSMS ,--> Tools  --> SQL Profile  --> Choose Database Engine...
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 45

Accepted Solution

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

Author Comment

by:MJB2011
Comment Utility
OK I have some data.... what am I looking for here?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
okay
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
Not seeing anything out of the ordinary?

cap2
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 45

Expert Comment

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

Author Comment

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

Expert Comment

by:Vitor Montalvão
Comment Utility
That's fair. How can we help you resolving this issue?
0
 
LVL 45

Expert Comment

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

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Is your problem fixed ?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now