August 21, 2011 at 9:30 pm
Hi,
I'm running the below query on 300 GB database ti find out index fragmentation but it's taking more than one hour. Is this normal? or can I improve the query?
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (10, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>10 AND INDEX_ID>0 AND page_count>1000
Thanks
August 22, 2011 at 1:01 am
Hi,
Execution was normal, bcoz u said the size of the database is near about 300 GB.
August 22, 2011 at 4:03 am
No, an hour seems very excessive for that query.
"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
August 22, 2011 at 4:21 am
Especially in limited mode...
Is the query blocked by anything?
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
August 22, 2011 at 10:38 am
when i execute that query on my server, it says
User doesn't have permission to run htt query
Why does it tell so when i already logged in as sa??
Regards
Sushant Kumar
MCTS,MCP
August 22, 2011 at 10:47 am
New question, new thread. Thanks
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
August 22, 2011 at 10:53 am
SKYBVI (8/22/2011)
when i execute that query on my server, it saysUser doesn't have permission to run htt query
Why does it tell so when i already logged in as sa??
Some parameter is wrong, try using @dbid = 1000 instead of a valid one and that's the exact error you get.
P.S. Next time you hijack I won't answer unless you start a new thread.
August 22, 2011 at 10:58 am
Grant Fritchey (8/22/2011)
No, an hour seems very excessive for that query.
I just did a speed test on my prod server.
Everyone's out to lunch and we have a very decent san.
It took 96 secs to run that query. Now compare 20 GB to 300 GB and that would take ±24 minutes to run on my system.
So while 60 minutes seems long, it doesn't seem very excessive to me. Especially if the server's under load.
August 23, 2011 at 2:06 pm
Hi,
It may happen that many jobs were also running during the execution of your query and might create blockage and perform light weighted locks on table moreover you must check the I/O graph of the disk from perfmon and the size of tempdb and the size of disk onto which is located ..make sure your db should not be in use of while running the query..
Chandan Gupta
IBM India Pvt Ltd
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply