Fetch the most used/retrieved rows from a table

Question

Post reply

Fetch the most used/retrieved rows from a table

ramu.valleti

SSCrazy

Points: 2229
More actions
December 8, 2009 at 11:44 pm

#214517

Hello There,

I've a scenario where i want to retrieve the records in a table which are retrieved/useed mostly...

Let me be more clear..

I've few facts and dimension tables, users write or execute some queries and retrieve the data based on their requirement.

What i've to do is i've to write some queries and retrieve data say from one dimension table to display which records/rows are being used mostly.

Guys, please let me know whether this is possible in SQL Server 2008 or not, even if there is any workaround to achieve this, please let me know.

Regards,

Ramu

Ramu
No Dream Is Too Big....!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

I don't know of anywhere in SQL Server (Database Engine) where there are statistics for what rows are accessed most often.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

I can't think of a single way of doing this either. Most of the DMV's that I can think of that might relate to this only store aggregate information, so you can't get down to the row level information that you'd need for something like this.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ramu.valleti SSCrazy Points: 2229 More actions · Answer 3

Thanks a lot Jack Corbett and Grant Fritchey for your quick response...:)

I know even if there is way, it might be expensive interms of performance or additional load load on the database...but still If you come across anything related to this please share with us...

Thanks a lot once again...!

Ramu

Ramu
No Dream Is Too Big....!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I'm with Grant and Jack on this one... I don't know of a way to do this without changing the queries.

IF you wanted to change the queries, change them from SELECTs to UPDATEs. Update each row to be returned by adding one to a new counter row and use OUTPUT to provide the required return as if a SELECT had been done.

Of course, that won't help if people have access to the table directly.

--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)
Intro to Tally Tables and Functions

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

There's no record within SQL of how often a row is selected. If you need to do this, then you're going to have to make some changes to your app so that, when a user retrieves X rows, the app writes back to the DB that these rows have been used.

Is going to be murder on performance unless done very, very, very carefully

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