December 31, 2009 at 9:05 am
You can use this query to check the statistics date when it was last updated.
select object_name (i.id)as objectname,i.name as indexname,i.origfillfactor,i.rowcnt,i.rowmodctr ,STATS_DATE(i.id, i.indid) as ix_Statistics_Date,o.instrig,o.updtrig,o.deltrig,o.seltrig
from sysindexes i INNER JOIN dbo.sysobjects o ON i.id = o.id
where rowcnt >1000 and i.name not like'sys%'and object_name(i.id)not like'sys%'
order by rowcnt desc
December 31, 2009 at 9:06 am
Just asking because there are 906 tables in this database. 🙂 I'm going to try to speed things up by querying all tables and running a script to update them.
December 31, 2009 at 9:12 am
December 31, 2009 at 9:13 am
Thanks ss-457805. Running that script, I see 543 objects popup the oldest date is 12/14/2009 that is the date that the database was upgraded to 2005. So it seems to me that the statics were updated recently, however seeing as how a table could have multiple statistics and there are 906 tables but only 543 statistics, my assumption is that there are a lot of tables that don't even have statistics. So by updating them all like Gila said, I would be doing myself a huge favor. Is that an accurate assumption?
December 31, 2009 at 9:13 am
Create a maintenance plan, drop the 'update statistics' task. Make sure the settings are for all stats and full scan. Save it and run it or schedule it for a quiet time. Minimal work on your part, if it's run when the system's not busy, it won't impact anyone.
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
December 31, 2009 at 9:31 am
ss-457805's script shows indexes, and only ones where the table has more than 1000 rows. In fact, it will filter out column statistics (stats not associated with indexes) because they always have a row count of 0. As such, that's giving you a very incorrect idea of what's happening.
The correct script would be this. (untested cause I'm still installing my computer. Column names might be wrong, if so, replace with select * to get correct ones)
SELECT object_name(object_id), name, stats_date(object_id, stats_id) as LastUpdatedDate
FROM sys.stats
WHERE ObjectProperty(object_id,'IsUserTable') = 1
Updating stats will do nothing for missing ones. It only updates existing. Check if auto_create statistics is enabled (database option). If it is, SQL will create any stats that it needs
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
December 31, 2009 at 9:44 am
I ran your script Gila and got 6960 objects. I also updated statistics on all tables related to that crystal report and reran the query. I don't think that solved my issue. So far I'm at 10min with the query and it's still going. Seeing as how it was 5sec or so on the old server with 2000, 10min is a failure let alone what it will end up being (my guess is still 2hr).
December 31, 2009 at 10:03 am
Try the DBCC UpdateUsage that Gail also suggested. Also, try updating stats for all tables rather than just the tables involved.
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
January 1, 2010 at 1:41 am
CirquedeSQLeil (12/31/2009)
Try the DBCC UpdateUsage that Gail also suggested.
Won't help performance. That just fixes incorrect metadata.
Somehow I suspect this query is going to need a rewrite to get it fast. The optimiser changed a lot between 2000 and 2005. Mostly for the better, but there are some cases (I suspect this being one) where it doesn't do as well.
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
January 2, 2010 at 8:06 am
Just an update....
I ran UPDATE STATISTICS <Table Name> WITH FULLSCAN on every single table in the database and it did indeed improve performance. It went from a 2 hour query down to a 50 min query. I have also attached the new execution plan. While that is a significant improvement, until I can get it below a minute (or even 5 sec like on the old server), our accounting department won't be happy with it.
I still have yet to try some of Gila's other suggestions like:
Change page verify to Checksum
Run a checkDB with data purity
Run DBCC UpdateUsage
It sounds like the last one won't help performance though. I did have two ideas that I'll have to try regarding Gila's suggestion to re-write the query. One was to create a view to the prj_task table with the filter applied there. And then I would join to that view in the main query. The other idea is to join to a subquery, the subquery being the prj_task table with the filter applied. I'm not all too familiar with how to can setup a query inside of Crystal, but I'm thinking the first option would be the only one that would work with it.
For now, I have me a cut over to get crackin' on. I have to make this thing go live now. Next week when the dust has settled and the small fires have been put out, I'll try the remaining suggestions and my query ideas.
Thanks again for the help and Happy New Year!
January 2, 2010 at 9:02 am
Brian Fischer (1/2/2010)
I still have yet to try some of Gila's other suggestions like:Change page verify to Checksum
Run a checkDB with data purity
Run DBCC UpdateUsage
None of those will do anything for performance. They're involved with database integrity and are simply things that should be done to a DB upgreaded from SQL 2000
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
January 2, 2010 at 10:21 am
Actually the vendor has me run the DBCC UPDATEUSAGE as part of the upgrade process. So that's been done already.
January 2, 2010 at 10:37 am
Great. CheckDB will take a while, so don't run it when the server is in use.
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
January 5, 2010 at 3:25 pm
OK, so I completed my migration over the weekend and in doing so had to recopy the database from the 2000 server to the 2005 server. I ran the update statistics on all tables. I tested every crystal report that we have and there are two others that run slow to. The original one that I posted about runs at 1hr 30min. The other two run at 50min. and 30min. Out of about 110 crystal reports, this isn't too bad.
Is there anything else short of reworking the query that I can do to improve the performance? Sounds like a query rewrite is the only thing left?
Thanks!
January 5, 2010 at 3:33 pm
I would go for the query rewrites at this point.
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 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply