Is there a workaround when missing the system view dm_db_index_physical_stats

Question

Post reply

Is there a workaround when missing the system view dm_db_index_physical_stats

daniel.cloutier

SSC Enthusiast

Points: 132
More actions
December 1, 2008 at 6:58 am

#202161

We need some help.
We are new at using SQL server 2005. We did a few searches on how to optimized our indexes and came across the script that looks at the variable avg_fragmentation_in_percent from the system view sys.dm_db_index_physical_stats. Unfortunately for us, we do not have that view in our "Standard" edition of SQL Server. Is there a nice work around to obtain the same result without that system view?
For reference here is the script:
-- Ensure a USE statement has been executed first.
USE imarkupq
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Viewing 14 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

All of the DMVs are present in all editions of SQL 2005.

What error are you getting?

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

sys.dm_db_index_physical_stats is not specific for any SQL 2005 edition.

But it's a system function, so you won't see it under views.

[font="Verdana"]Markus Bohse[/font]

daniel.cloutier SSC Enthusiast Points: 132 More actions · Answer 3

If I could send a print screen I would but it is not in any of our servers right noew!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

You should be able to find in the following location in the object browers in SSMS:

Databases\System Databases\master\Programmability\Functions\System Functions\Table-valued Functions

daniel.cloutier SSC Enthusiast Points: 132 More actions · Answer 5

daniel.cloutier

SSC Enthusiast

Points: 132

December 1, 2008 at 8:18 am

#905662

Thanks I will read the articles.

daniel.cloutier SSC Enthusiast Points: 132 More actions · Answer 6

Gail,

here is the error we have

Msg 102, Level 15, State 1, Line 22

Incorrect syntax near '('.

Line 22 is

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Can you run the following code snippet in your database?

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

select * from #work_to_do

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

If I could send a print screen I would but it is not in any of our servers right noew

It's included in all editions of SQL 2005, from enterprise right down to express.

Is the database in compatibility mode 90? I believe that is required for that particular DMV

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Good catch Gail. I didn't think of asking what compatibility mode the database was using.

daniel.cloutier SSC Enthusiast Points: 132 More actions · Answer 10

Thank you Gail and SSCarpal Tunnel,

You were right, the function is in our system. It is not where we we're looking for.

I found the problem in our script.

-- Ensure a USE statement has been executed first.

--USE imarkupq

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID('imarkupq'), NULL, NULL , NULL, 'LIMITED')[/b]

I set the USE db in comments and added the database name in the DB_ID() function. And now it works very well.

Thank you very much for your fast and brilliant answers. For a newbie it is nice to see devoted ressources out there!!

Thanks again

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

Lynn Pettis (12/1/2008)
Good catch Gail. I didn't think of asking what compatibility mode the database was using.

Some of the DMVs require compat mode 90, some don't. I can never remember which is which and BoL doesn't help.

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

daniel.cloutier (12/1/2008)
Thank you Gail and SSCarpal Tunnel,

p.s. that's the person's forum level (affected by no of posts), not the person's user name.

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

daniel.cloutier SSC Enthusiast Points: 132 More actions · Answer 13

daniel.cloutier

SSC Enthusiast

Points: 132

December 1, 2008 at 9:11 am

#905695

Oops

Thanks Gail and Lynn for everything!!