August 6, 2009 at 6:43 am
So we have an SQL server and two SSRS report servers. Our clients go to a site we host on IIS and make various selections and then that links to SSRS to run reports.
Currently we don’t have any performance data being captured over time. When are our SSRS report servers the most busy, when does the database have the highest amount of disk I/O, does the CPU on any of the servers ever get higher than 80% for more than 10 seconds?
We have no idea.
So I’m just looking for some advice, best practices, solid game plans to start with.
Ideally in the end I would be able to say:
1.Know what our usage patterns are – “Mondays at 8 AM our SSRS CPU is thrashing as everyone seems to be pulling weekly reports” or “Wednesday at 11 AM lots of people seem to be running reports X, Y, Z”
2.For every 1,000 users (based off our average usage patterns) we need X resources. Right now we have 2,000 users and are using 80% of our available hardware’s capacity. If we increase to 6,000 users we will need to look at increase our capacity in the following areas… (faster disks/more disks and file groups, or more SSRS servers who need more RAM or faster CPUs to crunch numbers, maybe we need more database servers and need to mirror data so more people can access it read only to run reports because the current server can’t keep up, etc…)
3.Know what our bottlenecks are: “Well it looks like report Z really hits these two tables hard, and notice it uses a table scan. OK, well now we can look at tuning that, add an index, re-write the SELECT and WHERE criteria, etc..” or “When this report runs SSRS has a massive amount of CPU activity on the server. Maybe we can look at the report itself and try to reduce the number of calculations or on the fly grouping work it has to do and offload those by making SQL Server do more of that by writing a more targeted SQL statement”
So basically:
What are our users doing on our site, what reports are they running and when?
What do we have to do to keep up with our growth as we add more users, how much hardware do we have to purchase to support x+n users?
What can we do to optimize/performance tune our system. We know these reports seem slow, and we see that they are slow because they are waiting on query results or because SSRS’ CPU is spiked, etc… Now that we know that we can start addressing it.
Suggestions, advice, good articles to read, etc…?
Since we are starting from nothing I want to do what I can to make sure that whatever direction we head down is a good one.
August 6, 2009 at 6:59 am
Hi Maxer
Did you give idera's SQL diagnostic manager?
I'd say it will give you a good start while you may explore other options towards resolving your problems. It is a very good tool and 14 days trial is available from their site or you may even look into buying it.
It does create reporsitory database obviously to store historic information for analysis purposes and that creates an extra overhead on your sql server but it is worth it.
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 6, 2009 at 7:47 am
Thank you, I will look into Idera and grab their trial to take it out for a spin.
I'm very open to other suggestions as well as just general guidance.
Thanks!
August 6, 2009 at 8:13 am
You are most welcome.
Also, I have attached some articles on Index Tuning which i though might be useful.
Query execution plan analysis
http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx
finding the cause of poor performance
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/ (server seems to be down but it is surely working)
How to get index usage information
http://www.mssqltips.com/tip.asp?tip=1239
good luck!
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 15, 2009 at 2:06 pm
The SQL Idera is per 5 minutes not every sql statement. Is there a way to make this every statement.
Quest for SQL is the boom. expensive though
August 19, 2009 at 3:21 am
I am sure you can change this parameter in sql idera too.
by default it collects query information every 5000 milliseconds but it is chanchable using configuration wizard.
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 20, 2009 at 7:50 am
A good start may be the execution log and its related sample reports. William Pearson has an excellent article (albeit for SQL 2000) on creating the Execution Log Reporting structure.
Also this article also provides some details
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply