Please help me to resolve this query

Question

Please help me to resolve this query

anshu84onnet

SSC Eights!

Points: 836
More actions
March 31, 2010 at 6:53 am

#140140

Dear All
i was executing this query on my database server
select avg_fragmentation_in_percent,name from sys.dm_db_index_physical_stats(db_id('ebpl_prod'),object_id('transactions'),null,null,'sampled') a,sys.indexes b
where a.index_id =b.index_id and a.object_id= b.object_id
and i was getting avg_fragmentation_in_percent and name of that index and i found that in one index fragmentation percent is around 25 % so i reorganize that index
it but when i am again executing this same query again i am getting no result from this query please reply immediately
select avg_fragmentation_in_percent,name from sys.dm_db_index_physical_stats(db_id('ebpl_prod'),object_id('transactions'),null,null,'sampled') a,sys.indexes b
where a.index_id =b.index_id and a.object_id= b.object_id

Viewing 11 posts - 1 through 10 (of 10 total)

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

anshu84onnet SSC Eights! Points: 836 More actions · Answer 1

Dear All

Waiting for your valuable suggestion one more question in my mind i have ...does the reorganizing one of table will decrease the fragmentation for other index also..please reply:-)

Richard M. SSCertifiable Points: 7401 More actions · Answer 2

What command did you use to Reorganize the index?

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

anshu84onnet SSC Eights! Points: 836 More actions · Answer 3

Sir i use this query to reorganize

alter index IX_TXNDATE_SRVID on transactions

reorganize;

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

In that case it should only have reorganized that specific index. I thought you might have used the ALL parameter on a table which would have done all indexes on that one table.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

anshu84onnet SSC Eights! Points: 836 More actions · Answer 5

But i was using this query only (alter index IX_TXNDATE_SRVID on transactions

reorganize;) ...

please some give me the valid answer please...

:unsure:

Richard M. SSCertifiable Points: 7401 More actions · Answer 6

How many results where you getting before?

Did you by any chance change the Database selected in SSMS? If you are not in the same database where the tables reside in, you will receive no results unless you add the database to the sys.indexes table reference, ie:

select avg_fragmentation_in_percent,name from sys.dm_db_index_physical_stats(db_id('ebpl_prod'),object_id('transactions'),null,null,'sampled') a,ebpl_prod.sys.indexes b

where a.index_id =b.index_id and a.object_id= b.object_id

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

anshu84onnet SSC Eights! Points: 836 More actions · Answer 7

thanks for getting me that point master database was selected as default at that point so not getting data now got the data thanks a lot....i need one more help dear i have very big confusion in locking concept that how and which lock have more priority in which transaction isolation level please tell me some link so that i can go through that..thanks again dear:satisfied:

Richard M. SSCertifiable Points: 7401 More actions · Answer 8

I'd suggest creating a new post with that inquiry, so it gets proper attention. Embedding it here will probably not produce answers you need, plus it makes it more difficult when searching for solutions. By the way, have you tried searching for that? I am sure it has been covered many times.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 9

anshu84onnet (3/31/2010)
thanks for getting me that point master database was selected as default at that point so not getting data now got the data thanks a lot....i need one more help dear i have very big confusion in locking concept that how and which lock have more priority in which transaction isolation level please tell me some link so that i can go through that..thanks again dear:satisfied:

See Lock Modes and Lock Compatibility

And please, for the love of all that is good, please stop calling us 'dear'. It makes me cringe.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 10

Richard M. (3/31/2010)
I'd suggest creating a new post with that inquiry, so it gets proper attention. Embedding it here will probably not produce answers you need, plus it makes it more difficult when searching for solutions. By the way, have you tried searching for that? I am sure it has been covered many times.

Oops! 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi