October 8, 2010 at 4:14 am
When running the following query:
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('dbname'), OBJECT_ID('dbo.tblname'), NULL, NULL, NULL);
I get an output like the one below. There is only 1 index on that table. Why does the query return so many records? Is there a way to resolve it?
21575115104558
53575229000
53575229200
66099276000
85575343027.45398773006145149
8557534320.228050171037628877
117575457000
117575457200
1495755710502
149575571201
181575685000
181575685200
213575799000
213575799200
2455759130502
245575913201
277576027000
277576027200
309576141072.7477910103726166545
309576141218.331842473451287198
3095761411117.942475881345222182
309576141126.1875324975446317309
341576255000
341576255200
373576369000
373576369200
405576483000
405576483200
437576597000
450100644000
453576654000
453576654200
466100701000
485576768071.428571428571438
485576768206
517576882000
533576939000
533576939200
54610098610.04777830864787392093
546100986201404
546100986301234
546100986401169
546100986501101
546100986601101
5461009861101101
562101043101
562101043102
56557705308.5992907801418436036
578101100101
58157711003.65699873896595126840
594101157100
597577167000
610101214100
61357722404.21986830804466139678
626101271101
629577281000
642101328101
64557733804.90542025799734138255
658101385101
658101385102
661577395000
674101442100
677577452087.511
690101499100
693577509000
706101556000
709577566000
722101613100
7255776230502
738101670101
738101670101
741577680000
757577737022.8346456692913998
773577794001
789577851083.33333333333337
821577965000
837578022108
837578022101527
869578136087.59
885578193000
885578193200
917578307033.33333333333335228
91757830720877
949578421000
949578421200
981578535001
981578535201
10101026390754
1013578649000
1013578649200
10421027530504
1045578763001
1045578763201
105810281000.3872216844143278251
10741028670502
1090102924100
1109578991063.27683615819211353
110957899120157
1141579105000
1141579105200
1173579219000
1173579219200
1205579333103995
1237579447000
1237579447200
1269579561072.727272727272726
1269579561206
1301579675102092
1317579732102092
1381579960092.857142857142948
1397580017000
1429580131000
1429580131200
1445580188090.909090909090921
1461580245000
1461580245200
1493580359000
1493580359200
1525580473000
1525580473200
1557580587000
1557580587200
1589580701000
1589580701200
1621580815000
1621580815200
1653580929000
1653580929200
1685581043000
1685581043200
1717581157000
1717581157200
1749581271000
1749581271200
1781581385000
1781581385200
1813581499000
1813581499200
1845581613000
1845581613200
1877581727000
1877581727200
1909581841000
1909581841200
1941581955001
1957582012000
1973582069000
1989582126000
2005582183000
2005582183200
2037582297000
2037582297200
2069582411000
2069582411200
2073058421034.05405405405411341
207305842120157
2101582525000
2101582525200
2137058649000
2137058649200
October 8, 2010 at 4:21 am
I suspect you ran that in a database other than dbname, so the Object_id returned null, hence you got stats for all tables.
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
October 8, 2010 at 4:23 am
Even when I run it in the proper place, I get output like this, indicating a null value:
0NULL33.4131736526946
2UQ__ixname1__5441852A78.7769784172662
October 8, 2010 at 4:25 am
As you can see, it is showing an avg frag percent of 78 which is crazy. I've tried to re-index that db with the script below, but those numbers just aren't going down. Could the stats be wrong? Or is the script below not proper?
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
-- SQL 2000 command
--DBCC DBREINDEX(@Table,' ',@fillfactor)
-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
October 8, 2010 at 4:26 am
Index 0 is the heap. Heaps don't have names. Hence the column for index name is null (and that's not the same query you ran earlier)
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
October 8, 2010 at 4:27 am
The stats are not wrong.
Likely the index is too small to benefit from reindexing. How many pages does it have?
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
October 8, 2010 at 4:35 am
here are the stats with page count:
0NULL33.41317365269466530
2UQ__idxname1__5441852A78.77697841726621668
October 8, 2010 at 4:46 am
Should work. You've checked that the script is actually running to completion? You're not shrinking the DB at all?
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
October 8, 2010 at 4:56 am
it says Command Completed Successfully, but when I recheck the stats, they are what they are...
October 8, 2010 at 5:06 am
here are some more stats after running that script:
0NULL 4.39639970609846 2177304
2UQ__idx1__1273C1CDA1012 89.0017242754288 1291557
3IDX_idx2_2K100204 6.45540164578241 136227
12IDX_idx3 98.0792358739323 488087
13IDX_idx4 90.933517724473 266101
October 8, 2010 at 6:49 am
I suggest you add some print statements to that code and make sure that it really is doing all the tables as it should be.
Print the table name, print the statement right before executing. It should take quite some time to run (if your db is even reasonably large)
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
October 8, 2010 at 6:51 am
Im just testing it one table at a time right now to check the same sort of thing.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply