Avatar of sg05121983
sg05121983
 asked on

sql cursor - need help

The attached sql script performs index rebuild for single DB.

We need to perform index rebuild against all DB's present on sql instance in single script.

I have tried to create cursor but it was not returning results as expected.

Please help me to create cursor for the attached script (need to perform index rebuild against all DB's present on server using attached script).

Thanks in advance. .
Index-Maintainance-Script-.txt
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
sg05121983

8/22/2022 - Mon
sg05121983

ASKER
Increasing point to 500 from 250.
krtyknmsql

sg05121983

ASKER
Thanks Krtyknmsql,

we can't use script given in above URL. Bacause script doesn't contain update stats & page count condition and we dont want to set any filefactor.

Can you please help me to write the cursor for attached script

@Experts : please share the logic to create cursor for the attached script.
Index-Maintainance-Script-.txt
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Saurabh Bhadauria

Can you please check what below query is returning.....


SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
      AND index_id > 0
      AND page_count > 50
ORDER BY avg_fragmentation_in_percent DESC;

your code will work only if above query will return something...
sg05121983

ASKER
Hi Saurv,

executed on some DB's not all DB's and the above query is returing output.

sample output:

objectid             indexid      partitionnum      frag                  page_count
356912343      1      1                    98.1818181818182      55
421576540      1      1                 98.1818181818182      55
1010102639      1      1                97.4789915966387      119
1224391431      1      1             88.659793814433      97
1157579162      1      1             66.3      1000
1074102867      4      1              22.4787363304982      823

FYI...
I have tested above code before posting my question on EE.

Now need cursor logic ti execute attached script against all DB's in sql instance.

I am DBA and i dont have devlopment knowldge of query.

Could you please help me on this.
Index-Maintainance-Script-.txt
Saurabh Bhadauria

Hi sg05121983 ,
sorry  I misunderstood your problem... I am working on your actual issue...will provide you solution soon...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Saurabh Bhadauria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
sg05121983

ASKER
Hi Saurv,

I got below errors.

Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 410
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 455
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 515
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 530
Incorrect syntax near '-'.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Msg 102, Level 15, State 1, Line 545
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 755
Incorrect syntax near '-'.
sg05121983

ASKER
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 410
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 455
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 515
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 530
Incorrect syntax near '-'.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Msg 102, Level 15, State 1, Line 545
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 755
Incorrect syntax near '-'.
Saurabh Bhadauria

Code is running fine at my end.....

I am attaching code file with some more log inside....
try now and if you got any error post it...

Also check what you are getting in #work_to_do table..
indexes.sql
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
sg05121983

ASKER
I got same error:

 NOTE : I am running given query on sql 2008.

please find the attached query output

Getting Details of all indexes
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 410
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 455
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 515
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 530
Incorrect syntax near '-'.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Msg 102, Level 15, State 1, Line 545
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 755
Incorrect syntax near '-'.
query-op.TIF
Saurabh Bhadauria

actually query is getting failed at first level.....


SELECT @SQL = @SQL + 'Select ' + quotename(NAME, '''') + ' as [DB_Name],
ps.object_id AS objectid, object_Name(PS.Object_ID,' + convert(VARCHAR(10), database_id) + ') as object_name ,
ps.index_id AS indexid,  ind.name as IndexName,sch.name as SchemaName,ps.partition_number AS partitionnum, ps.avg_fragmentation_in_percent AS frag, ps.page_count
 from ' + quotename(NAME) + '.sys.dm_db_index_physical_stats(' + convert(VARCHAR(10), database_id) + ', NULL, NULL, NULL, NULL)  ps
inner join ' + NAME + '.sys.tables as tbl
    on ps.object_id = tbl.object_id
  inner join ' + NAME + '.sys.schemas as sch
    on tbl.schema_id = sch.schema_id  
  inner join ' + NAME + '.sys.indexes as ind
    on ps.index_id = ind.index_id and
       ps.object_id = ind.object_id
WHERE ps.avg_fragmentation_in_percent > 10.0
      AND ps.index_id > 0
      AND ps.page_count > 50
ORDER BY [DB_Name] asc , ps.avg_fragmentation_in_percent DESC;
' + CHAR(13)
FROM sys.databases
WHERE state_desc = 'ONLINE'
      AND NAME NOT IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB')

Please run below code .. it will return multiple queries  as  output.

 execute them one by one check why they are failing at your end....

declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL +
'Select ' + quotename(name,'''') + ' as [DB_Name], 
ps.object_id AS objectid, object_Name(PS.Object_ID,' + convert(varchar(10),database_id) + ') as object_name , 
ps.index_id AS indexid,  ind.name as IndexName,sch.name as SchemaName,ps.partition_number AS partitionnum, ps.avg_fragmentation_in_percent AS frag, ps.page_count
 from ' + quotename(name) + '.sys.dm_db_index_physical_stats(' + 
convert(varchar(10),database_id) + ', NULL, NULL, NULL, NULL)  ps 
inner join ' + name + '.sys.tables as tbl
    on ps.object_id = tbl.object_id
  inner join ' + name + '.sys.schemas as sch
    on tbl.schema_id = sch.schema_id  
  inner join ' + name + '.sys.indexes as ind
    on ps.index_id = ind.index_id and
       ps.object_id = ind.object_id
WHERE ps.avg_fragmentation_in_percent > 10.0
	AND ps.index_id > 0
	AND ps.page_count > 1
ORDER BY [DB_Name] asc , ps.avg_fragmentation_in_percent DESC;

' 
+ CHAR(13) +'---------------------------------------------'  + CHAR(13) 

 from sys.databases where state_desc = 'ONLINE'
 and name not in ('master','tempdb','model','msdb', 'ReportServer','ReportServerTempDB')
 
print @SQL

Open in new window

sg05121983

ASKER
The query is running sucessfully on sql 2008 (version - 10.50.2811.0)
got the zero rows--please find the attached output.

But it was giving error on sql 2008 (version - 10.0.5775).

It is bug?
op.TIF
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sg05121983

ASKER
I have executed output 1-by-1 and got 6 select statments with DB name as output (Server conatins 75 Db's).

All 5 select statments executed sucessfully. I got the eror for 6th select statment
6th select statment:

Select XYZ_TT1234567' as [DB_Name],
ps.object_id AS objectid, object_Name(PS.Object_ID,62) as object_name ,
ps.index_id AS indexid,  ind.name as IndexName,sch.name as SchemaName,ps.partition_number AS partitionnum, ps.avg_fragmentation_in_percent AS frag, ps.page_count
 from [XYZ_1234567].sys.dm_db_index_physical_stats(62, NULL, NULL, NULL, NULL)  ps
inner join XYZ_1234567.sys.tables as tbl
    on ps.object_id = tbl.object_id
  inner join XYZ_T

The last inner join condition does not contain full DB name

Error message for above select statment:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near XYZ_T'.
RehanYousaf

Try the follwoing code

-- Ensure a USE <databasename> statement has been executed first.
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 @page_count BIGINT;
DECLARE @command NVARCHAR(4000); 
DECLARE @updateStatsThreshold INT;

DECLARE @StartTime DATETIME;
DECLARE @EndTime DATETIME;

-----------------------------------------------------------
DECLARE @Work_To_Do TABLE (
	 [DATABASE]	VARCHAR(50)
	,ObjectID NUMERIC
	,IndeID INT
	,PartitionNum INT
	,Frag FLOAT
	,Page_Count INT
)

DECLARE @Databases TABLE (
	 RowNumber INT
	,Name VARCHAR(50)
	,[dbID] INT
)

DECLARE @x INT
DECLARE @y INT
DECLARE @dbID INT

-----------------------------------------------------------
INSERT INTO
	@Databases
SELECT 
	 ROW_NUMBER() OVER (ORDER BY dbID) AS 'RowNumber' 
	,Name
	,dbID	
FROM
	master..sysdatabases

--SELECT * FROM  #Databases

-----------------------------------------------------------
SET @x = 1
SELECT @y = COUNT(*) FROM @Databases

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
WHILE @x < @y
BEGIN
	SELECT @dbID = dbID FROM @Databases WHERE RowNumber = @x
	
	INSERT INTO
		@Work_To_Do
	SELECT
		(SELECT Name FROM @Databases WHERE RowNumber = @x) AS 'Database',
		OBJECT_ID AS ObjectID,
		index_id AS IndexID,
		partition_number AS PartitionNum,
		avg_fragmentation_in_percent AS Frag,
		Page_Count
	FROM 
		sys.dm_db_index_physical_stats (@dbID, NULL, NULL , NULL, 'LIMITED')
	WHERE 
		avg_fragmentation_in_percent > 10.0 
		AND index_id > 0
		--AND page_count > 50
	ORDER BY 
		avg_fragmentation_in_percent DESC;
	PRINT @x
	SET @x = @x + 1
END

-----------------------------------------------------------
--stats threshold
SET @updateStatsThreshold = 7

-- 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;

--write header
PRINT N'"Date", "Page_Count", "Frag", "Command"';

SET @StartTime = (SELECT GETDATE()) 

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag, @page_count;
        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 < 90.0
            -- SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE WITH (MaxDop=8)'; 
			--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        --IF @frag >= 90.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MaxDop=8)';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));
        PRINT N'"' + CAST(GETDATE() AS VARCHAR(20)) + '", "' + CAST(@page_count AS VARCHAR(30)) + '", "' + CAST(@frag AS VARCHAR(20)) + '", "' + @command + '"';
		EXEC (@command);

--stats
		IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, GETDATE())
		BEGIN
			SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + OBJECT_NAME(@objectid) + ' ' + @indexname +' WITH RESAMPLE'
	        PRINT N'"' + CAST(GETDATE() AS VARCHAR(20)) + '", "' + CAST(@page_count AS VARCHAR(30)) + '", "' + CAST(@frag AS VARCHAR(20)) + '", "' + @command + '"';
			EXEC (@command)
		END
--End loop
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

SET @EndTime = (SELECT GETDATE())

SELECT LTRIM(STR(DATEDIFF(mi,@starttime,@endtime) % 60 )) + '.' + LTRIM(STR(DATEDIFF(ss,@starttime,@endtime) % 60 ))  AS 'CompletionTime'

SELECT * FROM @Work_To_Do

Open in new window

RehanYousaf

All you need to do is modify your work_to_do table so that it contains indexes from all databases

-----------------------------------------------------------
DECLARE @Work_To_Do TABLE (
	 [DATABASE]	VARCHAR(50)
	,ObjectID NUMERIC
	,IndeID INT
	,PartitionNum INT
	,Frag FLOAT
	,Page_Count INT
)

DECLARE @Databases TABLE (
	 RowNumber INT
	,Name VARCHAR(50)
	,[dbID] INT
)

DECLARE @x INT
DECLARE @y INT
DECLARE @dbID INT

-----------------------------------------------------------
INSERT INTO
	@Databases
SELECT 
	 ROW_NUMBER() OVER (ORDER BY dbID) AS 'RowNumber' 
	,Name
	,dbID	
FROM
	master..sysdatabases

--SELECT * FROM  #Databases

-----------------------------------------------------------
SET @x = 1
SELECT @y = COUNT(*) FROM @Databases

WHILE @x < @y
BEGIN
	SELECT @dbID = dbID FROM @Databases WHERE RowNumber = @x
	
	INSERT INTO
		@Work_To_Do
	SELECT
		(SELECT Name FROM @Databases WHERE RowNumber = @x) AS 'Database',
		OBJECT_ID AS ObjectID,
		index_id AS IndexID,
		partition_number AS PartitionNum,
		avg_fragmentation_in_percent AS Frag,
		Page_Count
	FROM 
		sys.dm_db_index_physical_stats (@dbID, NULL, NULL , NULL, 'LIMITED')
	WHERE 
		avg_fragmentation_in_percent > 10.0 
		AND index_id > 0
		AND page_count > 50
	ORDER BY 
		avg_fragmentation_in_percent DESC;
	PRINT @x
	SET @x = @x + 1
END

-----------------------------------------------------------	
SELECT 
	*
FROM
	@Work_To_Do	

	
	

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Saurabh Bhadauria

I guess query is failing due to db name .. can you please post what you are getting in below query


select * from sys.databases


I want to check the databases name...
Saurabh Bhadauria

It might be a bug.....

by the way  on which version you are going to run maintenance code.... ?
sg05121983

ASKER
Sure Saurv, i will share DB names.

@RohanYousf: I have executed attached script and got the below error.

Please review script once agin.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
"Date", "Page_Count", "Frag", "Command"
Msg 16924, Level 16, State 1, Line 97
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
indexscript.sql
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sg05121983

ASKER
Saurv/Rohan,

Could you please review the attached script.

I got below error

"Date", "Page_Count", "Frag", "Command"
Msg 16924, Level 16, State 1, Line 97
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
indexscript.sql
Saurabh Bhadauria

can you try attached script... It should work now......


I guess the issue was your db name contains single quote  (') ....

Well try now...
indexes1.sql
SOLUTION
RehanYousaf

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
sg05121983

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sg05121983

ASKER
--
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy