Performance Problem when using Caching

Question

Performance Problem when using Caching

Viewing 10 posts - 16 through 24 (of 24 total)

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

Robert Brown-303469 SSC Journeyman Points: 96 More actions · Answer 1

I've checked all of these and they are identical.

We've managed to get this going by creating a new database and using sql compare and sql data compare to copy the structure and the data over from the one that's not working.

The newly copied database works fine!

I'd love to get to the bottom of this but I just can't see what else to check.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

The fact that copying everything to a new database resulted in a working query strongly points out that something on the old database was different.

"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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Did you check if the SP is doing a recompile everytime it is running? Did you by any chance try to force it to recompile after updating the statistics? Maybe it will pick the right query plan once that is done.

-Roy

Robert Brown-303469 SSC Journeyman Points: 96 More actions · Answer 4

Grant Fritchey (12/21/2007)
The fact that copying everything to a new database resulted in a working query strongly points out that something on the old database was different.

I agree entirely but it's finding what is different that is the problem. SQL Compare, management studio and all the system views I've looked at don't show anything.

Robert Brown-303469 SSC Journeyman Points: 96 More actions · Answer 5

We've forced the sp to recompile, deleted it's plan etc. Even just running the update statement in a management studio window didn't work.

Len Olivo Valued Member Points: 55 More actions · Answer 6

You may want to try dbcc update usage with count_rows. Sounds like the catalog views may not be correct.

A snipit from BOL:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 7

Heh... I'm still trying to figure out why anyone would try to cache a 54 million row table...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert Brown-303469 SSC Journeyman Points: 96 More actions · Answer 8

All these databases are SQL 2005 databases, none have been upgraded from 2000. I'll give this a go but it seems like it shouldn't make any difference.

Robert Brown-303469 SSC Journeyman Points: 96 More actions · Answer 9

Jeff Moden (12/26/2007)
Heh... I'm still trying to figure out why anyone would try to cache a 54 million row table...

😛 We're caching partial sets of the data based on access keys. It's an accounting system so the access keys tend to be driven mainly by the period that the system is operating in.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 10

Sure and understood... but, with proper indexing, good set based code, and the like, access will be extremely fast without having to go through a cache layer that needs to be maintained.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)