September 22, 2009 at 8:03 pm
NEED SOME HELP with this one URGENTLY
Ran SP_UPDATESTATS on PROD which is a 2000 SQL Server SP4 thinking that it would help out with performance but now DTS jobs that used to take 20 minutes to complete now take 5 or more hours to complete. Ran this command: EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" and did nothing to help the situation. Also did UPDATE STATISTICS tablename WITH FULLSCAN on tables that are used in the query in the DTS packages, and this has done nothing to improve the performance. The Queries themselves within the DTS are perhaps not the greatest as far as performance tuning goes, but 20 minutes was at least an acceptable time jobs to complete. Any suggestions would be greatly appreciated. THanks
September 28, 2009 at 7:27 am
1. Drop and re-create indexes
2. Run Peformance monitor and analyze results.
3. Run DTA against those queries.
September 28, 2009 at 7:56 am
If its not a blocking issue, it will be a query plan problem
Please see this article
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 28, 2009 at 8:00 am
Pull the queries out as well, run them in QA, analyze the execution plan and stats. See if an index might have been dropped and you're now getting a scan instead of a seek in places. Look for scans, see if they make sense in terms of the indexes on those tables.
Also, has data size increased for some reason? Or some indexes dropped?
September 28, 2009 at 9:36 pm
Performance was already pretty bad So I just Ended up doing a DBCC FreeProcCache statement and DBCC DROPCLEANBUFFERS of course some customers were complaining about their queries taking even longer after I flushed the plans. Did a DBCC UPDATEUSAGE to get better statistics on rows counts... turned on auto update statistics since update statistics with full scan and and sp_updatestats seemed to be generating bad execution plans. Rebuilt all the indexes. The system is pretty much louzy.. no foreign key constraints..very little primary keys.. very little indexes for tables that have joins and there are about 600 dts jobs.. also lots of locking issues since queries are taking longer. Honestly.. don't think I am being paid enough to be cleaning up somebody elses mess. Database was not even created by a DBA.. -- crazy Think the system is starting to stabilize now as it has to parse new execution plans. Won't be doing sp_updatestats anymore that is for sure
September 28, 2009 at 9:44 pm
Also, there are many jobs that will truncate tables or even some DTS jobs will say "DELETE" rather than truncate. Then once this is done new inserts are being done on the empty tables almost on an everyday basis. The data on a lot of tables is always fluctuating because of truncating and inserting new records. Also there is a lot of fragmentation issues, but not on the indexes anymore, mostly at the table level.
September 28, 2009 at 9:49 pm
Wow, sounds like you have a busy job ahead of you.
I'd look to do a few things. First, see if the clustered indexes make sense. They might all be created on primary keys, which isn't necessarily the best choice.
I'd also check the fill factors. If you have lots of inserts, try to avoid hot spots in the clustered indexes. If you need to set them lower, that might help with fragmentation. Rebuild as necessary to avoid page splits and keep fragmentation at a manageable level.
Build FKs where you can, but be sure that columns used in WHERE and join clauses are indexed. Once you've done that, it's low hanging fruit, you probably need to start digging through troublesome queries and working on them. I might deal with complaints for a week and try to just analyze things, see which ones are run the most often and cause the most trouble. Then set up a priority order for tackling them once you have a bigger picture.
September 28, 2009 at 9:58 pm
Have never used DTA. Have any good suggestions for setting this up and are the results easy to read? Sorry..nore of an Oracle DBA .. SQL Server is more of a side hobby... any suggestions that would walk me through on setting this up would be great. Bending backwards as it is to learn sql 2000 cuz had some training in 2005 and 2008.. but 2000 is pretty ancient.
September 29, 2009 at 7:19 am
It's a long shot, but simple enough to try: clear your procedure cache (assuming that your system can take the hit for recompilations).
I once dealt with a similar issue. The existing plans were created over old stats, the old plans weren't all best according to new stats, so nothing was happy together.
It's the a-bomb approach by removing all cached plans to recreate a few, but it worked in my case & is easy enough to try.
September 29, 2009 at 7:31 am
If I remember, you need a trace file of activity on the system and DTA will analyze it. It wasn't as good in 2000 as it is in 2005/2008. Tends to recommend indexes on all columns used.
September 29, 2009 at 8:54 am
do NOT simply implement what DTA might advise. It has SIGNIFICANT limitations and will create MANY indexes with large percentages of the columns in a table INCLUDED just to cover single queries.
May I recommend that you suggest to your company to get a professional in for a day or three to give the system a performance review?? Huge ROI to be gained there if this is an important production system.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2009 at 10:07 pm
After clearing the cache plans things seem to be going back to normal this week. Used Steve Jones's suggestions.. prioritized longest running jobs that the developers said needed to be worked on. Added indexes to joins on a lot of convoluted DTS queries that take minutes if not hours to run. Rebuilt the indexes on tables DBCC DBREINDEX ('?', ' ', 80)". The job that used to run in 20 minutes then started taking two hours to run.. guess page splits were occurring. Their big job ran today I made the indexes DBCC DBREINDEX ('?', ' ', 50) since there are heavy updates.. job completed in 19 minutes.. this is good. Supposed to be going to 2008 and converting all the DTS jobs into SSIS by next year using Pragmatic Works.. hope 2008 takes care of the performance issues. Going to be running on 64 bit boxes.. only using 32 bit .. dealing with the 4g limitation. Thanks for your help and suggestions
September 29, 2009 at 10:09 pm
Errrrrr... made the fillfactor low on the heavy insert tables. Seems to be a lot of those in this database. Heavy page splits as well as bad execution plans.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply