3

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ and the sql queries are as follows:

Sql query to check index fragmentation.

IF EXISTS ( SELECT * 
    FROM [tempdb].[dbo].[sysobjects] 
    WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
    DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details] 

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
    [DatabaseName]                  [nvarchar] (1000) NULL,
    [ObjectName]                    [nvarchar] (1000) NULL,
    [Pagecount]                         INT,
    [indexName]                     [nvarchar] (1000) NULL,
    [avg_fragmentation_percent]     float NULL,
) ON [PRIMARY]
DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)
DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')and  state not in('6')
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sqlQuery = '
    USE [' + @dbname + '];

    IF EXISTS
    (
        SELECT compatibility_level 
        FROM sys.databases 
        WHERE 
            name  = N'''+ @dbname +'''
            AND compatibility_level >= 90
    )
    BEGIN
        INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details] 
        (
            DatabaseName
            , ObjectName
            , IndexName
            , avg_fragmentation_percent
            , PageCount
        ) 
            SELECT db_name() as DatabaseName,
dbtables.[name], 
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
    END;'
    EXEC sp_executesql @sqlQuery
FETCH NEXT FROM dbcursor
INTO @dbname
END
CLOSE dbcursor
Deallocate dbcursor
-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details] 
ORDER BY databasename, avg_fragmentation_percent desc

Sql query to check database size (for baseline)

with Temp
as (select database_id, type, size * 8.0 / 1024 / 1024 size from sys.master_files)
select (select sum(CAST(size AS DECIMAL (38, 2))) from Temp where  Temp.database_id = db.database_id) DataFileSizeGB, Name
from sys.databases db 
order by datafilesizeGB desc

After these 2 queries save their results in an .xlsx file, I run the following stored procedure using the basic script as found on http://ola.hallengren.com/ .

execute dbo.indexoptimize
@Databases = 'USER_DATABASES, -%WSSContent%, -%SP2013%, -%WSS_CONTENT%',
@fragmentationmedium = 'INDEX_REORGANIZE',
@fragmentationLevel2 = '30',
@fragmentationHigh = 'INDEX_REORGANIZE',
@FragmentationLevel1 = '5',
@fragmentationLow = 'NULL'

execute dbo.indexoptimize
@Databases = 'USER_DATABASES, -%WSSContent%, -%SP2013%, -%WSS_CONTENT%',
@updatestatistics = 'all',
@FragmentationLevel1 = '5',
@FragmentationLevel2 = '30'
  • When I run the stored procedure in SSMS, followed by the query to check fragmentation I can see changes in the indexes
  • When I run the stored procedure using my third party tool, I don't see any changes in the indexes
  • The stored procedure takes the same amount of time using the third party tool, as it does running directly in SSMS
  • When I check sql profiler, I can see the stored procedure being executed when being run from VisualCron

How do I go about further troubleshooting the stored procedure being run from VisualCron?

3

By default IndexOptimize is skipping indexes with less than 1000 pages. You can read more about this in the Frequently Asked Questions. Could this explain it?

  • Hi, thank you for your response. Unfortunately I found that the VisualCron client was not correctly transferring the parameters of the stored procedure, so I decided to do without the added step of using VisualCron. So it was unrelated to your awesome script. – Reaces May 12 '14 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.