April 20, 2010 at 4:58 am
Hi,
We have a report which run 15 store procedures to fetch the data. This report takes 1 hours to process during non buisness hours and about 4 hours during buisness hours. The majority of data is pulled form two large tables by using a join. Each containing about 30 millows rows each.
To improve the performace, we are looking at the following options:
Create a view on these tables
Optimise the stored procedures
So I need some guidance in my action plan:
Create the view and check how much time the sp would take.
Any other alter ways to improve performance.
April 20, 2010 at 6:27 am
A view, unless you're talking about a materialized view, will not help performance in the situation you describe. You need to focus on the queries and what they're doing and how they're doing it.
If you want to know what's been happening with the queries to date, I'd suggest taking a look into the cache at sys.dm_exec_query_stats. That will tell you, while the queries are in cache, how many times they've been called, what the min, max & average run times are, etc. You can combine this with other DMV's to get the query text, the execution plan used and other information.
Another option is to set up a server side trace and capture the execution of the queries in real time. This has the added advantage of showing the order in which queries are called and the parameters used.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 20, 2010 at 7:51 am
You need to identify the procedure(s) which are running slow and the reason for that. Just creating view will not help.
There could be many other ways to address the issue. Materialized view may help.
April 20, 2010 at 4:22 pm
Grant has made an excellent suggestion.
You should find the queries that are slow, evaluate their execution plans and then tune the queries and indexes from there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply