sql cursor - need help

Increasing point to 500 from 250.

The below link will help you to re-build the index

http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

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

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

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

Hi sg05121983 ,
sorry  I misunderstood your problem... I am working on your actual issue...will provide you solution soon...

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 '-'.

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 '-'.

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

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

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

Select all Open in new window

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

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'.

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

Select all Open in new window

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	

	
	

Select all Open in new window

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

It might be a bug.....

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

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

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

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

--