tag:blogger.com,1999:blog-6097409806591916698.post3014638956053354216..comments2024-03-19T05:19:12.823-07:00Comments on Solutions with Practical SQL DBA : SQL Server: Measuring The Index FragmentationNelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-6097409806591916698.post-59476583105263209402018-04-26T03:24:10.095-07:002018-04-26T03:24:10.095-07:00Thanks again for the blog post.Really looking forw...Thanks again for the blog post.Really looking forward to read more. Will read on…<br /><a href="//jual-tangki-panel.blogspot.com/" rel="nofollow">Tangki Fiberglass</a><br /><a href="//jual-tangki-panel.blogspot.com/p/jual-septic-tank.html" rel="nofollow">Jual Septic Tank</a><br />gunardihttps://www.blogger.com/profile/08665060660799487487noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-69129579555406482172017-03-09T08:57:33.722-08:002017-03-09T08:57:33.722-08:00Measuring the fragmentation index is a very comple...Measuring the fragmentation index is a very complex and time-consuming process that takes a long time.<br />http://www.check-my-essay.comhttp://check-my-essay.com/noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-34834155022138916842015-02-24T02:18:44.327-08:002015-02-24T02:18:44.327-08:00How do you recommend capturing fragmentation level...How do you recommend capturing fragmentation levels of tables\indexes large in size i.e. > 50 GB going up to 800 +GB Anonymoushttps://www.blogger.com/profile/15288733820736050554noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-65617425886397452902013-08-30T22:59:27.447-07:002013-08-30T22:59:27.447-07:00hello ,
I want run time change when pass dbname
...hello ,<br /><br />I want run time change when pass dbname <br />when pass @dbid then dynamical change records ,<br />---<br />SELECT object_name(IPS.object_id) AS [TableName], <br /> SI.name AS [IndexName], <br /> IPS.Index_type_desc, <br /> IPS.avg_fragmentation_in_percent, <br /> IPS.avg_page_space_used_in_percent, <br /> IPS.page_count<br />FROM sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL , 'DETAILED') IPS<br /> JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id<br /> JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id<br />WHERE IPS.avg_fragmentation_in_percent > 30<br /><br />--<br /><br />when first type manually write <br /><br />use <br /><br />SELECT object_name(IPS.object_id) AS [TableName], <br /> SI.name AS [IndexName], <br /> IPS.Index_type_desc, <br /> IPS.avg_fragmentation_in_percent, <br /> IPS.avg_page_space_used_in_percent, <br /> IPS.record_count, <br /> IPS.page_count,<br /> ips.index_level<br />FROM sys.dm_db_index_physical_stats(db_id(N'dbname'), NULL, NULL, NULL , 'DETAILED') IPS<br /> JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id<br /> JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id<br />WHERE IPS.avg_fragmentation_in_percent > 30<br /><br /><br />this is work<br /><br /><br />Regards ,<br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-57968806615680015172013-08-30T08:05:17.011-07:002013-08-30T08:05:17.011-07:00Because it changes context only within sp_sqlexec,...Because it changes context only within sp_sqlexec, but when it gets to print the current database changes back.<br />You have to put the whole query in @sql1 in order to do what you want.Alexander Suprunnoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-10817099704996498592013-08-30T05:06:53.125-07:002013-08-30T05:06:53.125-07:00Hello ,
yes,
Second thing why not change runtime...Hello ,<br /><br />yes,<br /><br />Second thing why not change runtime use <@dbname><br />e.g.<br />set @sql1 = 'USE ' + @Dbname + ';' <br /><br /> EXEC sp_sqlexec @sql1<br /> print @sql1<br /><br />print perfect but database not change , How to change ? <br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-54788502477810151622013-08-26T05:58:37.667-07:002013-08-26T05:58:37.667-07:00is it a small table?
is it a small table?<br />Nelsonhttps://www.blogger.com/profile/02156244559143388581noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-90464661455295325542013-08-26T03:25:38.105-07:002013-08-26T03:25:38.105-07:00Hello Sir ,
I have one table and frgamentation tw...Hello Sir ,<br /><br />I have one table and frgamentation two entry for same index\table<br />I am allready drop and re-ceate but problem not solve and also change fillfactor .<br /><br />IndexName PercentFragment avg_page_space_used_in_percent NumPages<br />DPCCalcOther46 0.542635658914729 89.8397949098097 1290<br />DPCCalcOther46 87.5 65.7184334074623 8<br /><br />Please give me advise<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-77280024038882756092013-08-23T09:08:40.275-07:002013-08-23T09:08:40.275-07:00Hmmm... this carried over okay to my SQL 2012 SP1 ...Hmmm... this carried over okay to my SQL 2012 SP1 CU2 environment... Maybe an RTM issue??<br /><br />SELECT<br /> object_id AS objectid,<br /> index_id AS indexid,<br /> partition_number AS partitionnum,<br /> avg_fragmentation_in_percent AS frag<br />FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')<br />WHERE avg_fragmentation_in_percent > 15.0 <br /><br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-29578526399750525912012-11-07T13:27:46.061-08:002012-11-07T13:27:46.061-08:00Thanks for the scripts. I found that these run fin...Thanks for the scripts. I found that these run fine on SQL Server 2008R2 but a slight mod was needed using SSMS on 2012 for both queries<br /><br />change: sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'DETAILED') <br />to: sys.dm_db_index_physical_stats(@DB_ID, NULL, NULL , NULL, 'DETAILED') <br /><br />and of course add<br /> DECLARE @DB_ID INTTony Chttps://www.blogger.com/profile/17006754089850914184noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-419281461040490622012-09-03T06:10:51.027-07:002012-09-03T06:10:51.027-07:00Thank you for reading the article. In the first se...Thank you for reading the article. In the first section ,measuring the internal fragmentation , I have put the statement correctly but in the other part the 'else' part was missing which caused the confusion. <br />SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 END FROM @Fillfactor . Now I have corrected it as <br />SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 else run_value END FROM @Fillfactor.<br />This will help us to display the actual fill factor of the index. Yes fill factor 0 and 100 are same .I prefer to display it as 100 to avoid any confusion. Thank you for pointing it out.Nelsonhttps://www.blogger.com/profile/02156244559143388581noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-12359271796955969112012-09-03T05:48:51.334-07:002012-09-03T05:48:51.334-07:00Hello Nelson,
Nice blog! I am cursing myself now ...Hello Nelson,<br /><br />Nice blog! I am cursing myself now for not noticing this one the moment it was published!<br /><br />But, in my understanding, having a fill factor of 0 is the same as having a fill factor of 100 as both will not let any free space in the page and use up all the space to insert the records. I am not seeing a point why we should assign a 0 or a 100 the variable @DefaultFillFactor. Shouldn't it be something like <br /><br />SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE 80 END FROM @Fillfactor <br /><br />Considering 80 is an allowable FillFactor.<br />Please let me know what you think about this.<br /><br />Thanks in advance,<br />AnanthramAnonymoushttps://www.blogger.com/profile/17314618009438987692noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-39510633007893277542012-05-20T13:36:26.715-07:002012-05-20T13:36:26.715-07:00http://msdn.microsoft.com/en-us/library/ms188917(v...http://msdn.microsoft.com/en-us/library/ms188917(v=sql.100).aspxAlexander Suprunnoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-3656298808121410872012-04-30T05:25:58.559-07:002012-04-30T05:25:58.559-07:00John, thanks for these articles. We've been u...John, thanks for these articles. We've been using Ola Hallengren's IndexOptimize scripts for years. It only looks at indexes where index_level=0 and uses only the LIMITED switch when querying the DMV. What are we missing by only using the LIMITED switch and not looking at index_level>0 It seems that the fragmented indexes where index_level>0 have fairly small page counts. Our DB is 1.7 TB with 60 million primary records -- some tables have nearly 200 million records. Many hundreds of indexes.Randyhttps://www.blogger.com/profile/16504844681754360503noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-32816862819680670552012-04-28T09:42:00.846-07:002012-04-28T09:42:00.846-07:00In my environment run value is 0 ...Thank you for ...In my environment run value is 0 ...Thank you for pointing it out. I am going correct itNelsonhttps://www.blogger.com/profile/02156244559143388581noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-50611739906500279532012-04-28T06:21:26.747-07:002012-04-28T06:21:26.747-07:00Hi
you may want to change this
SELECT @DefaultFill...Hi<br />you may want to change this<br />SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 END FROM @Fillfactor <br /><br />to this<br />SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE run_value END FROM @Fillfactor <br />regards<br />PerryPerry Whittlehttp://blog.perrywhittle.co.uknoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-3287198440067361802012-04-20T12:24:09.844-07:002012-04-20T12:24:09.844-07:00The second of two very good posts. Very clear expl...The second of two very good posts. Very clear explanations in both. Thank you.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-56067809278363897092012-04-20T08:16:08.124-07:002012-04-20T08:16:08.124-07:00Very nice and well explained post ..Very nice and well explained post ..Anonymousnoreply@blogger.com