WHERE filter is killing my query against a SQL Server 2005 database

Question

WHERE filter is killing my query against a SQL Server 2005 database

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

ss-457805 SSCertifiable Points: 5872 More actions · Answer 1

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

blog: http://sarveshsingh.com Twitter: @sarveshsing

Brian Fischer Old Hand Points: 367 More actions · Answer 2

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.

ss-457805 SSCertifiable Points: 5872 More actions · Answer 3

This will help:

http://msdn.microsoft.com/en-us/library/ms173804.aspx

blog: http://sarveshsingh.com Twitter: @sarveshsing

Brian Fischer Old Hand Points: 367 More actions · Answer 4

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brian Fischer Old Hand Points: 367 More actions · Answer 7

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).

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brian Fischer Old Hand Points: 367 More actions · Answer 10

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brian Fischer Old Hand Points: 367 More actions · Answer 12

Actually the vendor has me run the DBCC UPDATEUSAGE as part of the upgrade process. So that's been done already.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brian Fischer Old Hand Points: 367 More actions · Answer 14

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!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

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