Increasing performance on SQL application
Hello all
I have an application server and and a database.
The application runs a report by reading the data from the SQL DB server.
The report to be completely generated is taking around 20 mins.
We doubled the hardware resources on both servers. But the process is still taking the same exact amount to finish.
The network utilization between the two servers is not fully utilized during the process.
1- that means the bottleneck for this is not HW over utilization.
2- what else could it be? Any ideas where to look?
I have an application server and and a database.
The application runs a report by reading the data from the SQL DB server.
The report to be completely generated is taking around 20 mins.
We doubled the hardware resources on both servers. But the process is still taking the same exact amount to finish.
The network utilization between the two servers is not fully utilized during the process.
1- that means the bottleneck for this is not HW over utilization.
2- what else could it be? Any ideas where to look?
>The report to be completely generated is taking around 20 mins.
Good God man. 20 minutes for a single report, or is the majority of this time aggregating data into some kind of a data warehouse/datamart that serves up the report?
Just for kicks and giggles generate an execution plan of the T-SQL that populates this report, and paste it into this question.
Good God man. 20 minutes for a single report, or is the majority of this time aggregating data into some kind of a data warehouse/datamart that serves up the report?
Just for kicks and giggles generate an execution plan of the T-SQL that populates this report, and paste it into this question.
ASKER
I'll try to that today. You can see the report is generating around 8 users/second (we have 15,000 users) was wondering what can we do/increase/adjust to make it for example 30 users/second
Will check what you asked above and try to figure it out, and paste it here.
Will check what you asked above and try to figure it out, and paste it here.
Have you checked the rest of the network for a bottleneck? If this report and database are on two different servers and they have both had their hardware upgraded, did it include the NIC cards? Maybe you have a bad network cable somewhere? Are they both connected to the same switch? and what kind of switch is it?
I would first off, restart the switches, and replace the network cables.
Also, look into your security software on both servers. Is your antivirus causing the bottleneck? Can you turn it off and do a test to see if it cures the problem?
I would first off, restart the switches, and replace the network cables.
Also, look into your security software on both servers. Is your antivirus causing the bottleneck? Can you turn it off and do a test to see if it cures the problem?
ASKER
Two VM's on the same hyperV
Is there a way to capture the queries and run them straight on the DB to see if the bottleneck the application server or the DB server.
Is there a way to capture the queries and run them straight on the DB to see if the bottleneck the application server or the DB server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, check if the report maybe running some SQL stored procedure that relies on tempdb and check to see if tempdb on that SQL is optimized.
SSRS Report Caching could be used to improve the report performance but is usually the SQL code that needs optimizations or simply adding missing index(es)