August 11, 2008 at 1:04 pm
RAM utilization could definitely be an issue. If any of the reports are big enough, and you don't have the server configured to use all that RAM, they'll end being processed in tempdb. If, even worse, tempdb isn't configured correctly for maximum performance, you'll end up with a major performance bottleneck on that. Check both of those things.
But definitely, the first suspect in slow queries is the queries themselves, or the tables and indexes they are running on. I've seen lots of code that ran just fine on smaller amounts of data, then hit some threshold of number of rows to process, and just tanked. Every time, it's been cursors or some other form of RBAR.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2008 at 4:32 am
GilaMonster (8/10/2008)
Mike Alfa (8/10/2008)
I would love to see your comments on structural difference between SQL 2000 and 2005. As this database was designed on SQL2000 and we upgraded to SQL2005 just 2 years back.Could that affect the performance?
--Mike
Highly unlikely. In general there's a performance improvements when upgrading from 2000 to 2005, and 2 years is a long time for a database.
Have the reports always been slow or has that started recently?
Honestly, if it were my system, I'd be looking at profiler right now to identify the slow running code.
Hi Gila,
These reports used to be faster than they are currently.
Going with your profiler suggestion, let me what key areas or counters I should monitor or can you give me some references or articles so that I can pin point the bottleneck or problem of this slow problems.
thanks
August 13, 2008 at 6:29 am
Mike Alfa (8/13/2008)
Going with your profiler suggestion, let me what key areas or counters I should monitor or can you give me some references or articles so that I can pin point the bottleneck or problem of this slow problems.thanks
As I mentioned in an earlier post
Take SQL Profiler and run it for an hour or so during your busiest time. Trace the RPC:Completed events and the T-SQL:Batch Completed. Make sure you include the textdata, the reads, cpu and duration. Save the trace to disk and once its completes load the trace into a database for analysis. You're looking for queries with a high duration, a high CPU time or high reads. You'll probably find that queries have more than one of those high.
Take the offending procedures/queries and look for bad query constructs (cursors, while loops, etc) or signs of inadequate indexing (table scans). If you need help with that step, post here. There are a number of people here who enjoy doing query optimisation.
The fact that their running lower over time indicates that probably the data volumes are increasing and either you're using code constructs that don't scale well (cursors, while loops, triangular joins,...) or your indexes are less useful with a higher data volume, or both.
It's usually an iterative process, identify about the top 3 worst offending queries, fix them, evaluate again, find the next 3, repeat. It's a bad idea to try and fix everything at once. You just get overwhelmed.
If you need more help in any stage, please post here. There are several people who frequent this site that do this kind of work regularly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply