I have a database with an unfortunate name like '4u2defag'. Calling usp_AdaptiveIndexDefrag using @dbScope of N'4u2defag' creates a command like
ALTER INDEX [cci_s_poltran] ON 4u2defag.[dbo].[STG_POLICYTRANSACTION] REORGANIZE;

This causes an error:

Error 102 has occurred. Message: Incorrect syntax near '4'.

Calling usp_AdaptiveIndexDefrag using @dbScope of N'[4u2defag]' fails.

Hello,

Report is empty. Does it take time to show any data. Please help.

Attaching screenshot of same.

Hi all,

as I have already mentioned in #34
some people will get a subsequent error when they use the conversion of the Performance Counter Output to varchar (20) inside the stored procedure spGetPerfCountersFromPowershell.

If you have a system which uses a comma instead of a point to mark your decimal place, then you will (of course) save your perf Counter values inside the table dba_local.dbo.CounterValue also with commas instead of points.
This leads to "Error converting data type varchar to float".
I have picked up the query the dashboard report uses:

exec sp_executesql N'DECLARE @CPUCOUNT int
SELECT @CPUCOUNT=cpu_count from sys.dm_os_sys_info
SELECT
Date,
ROUND([processor(_total)% processor time],0) as ''Processor Utilization'',
ROUND(([processor(_total)% privileged time][processor(_total)% processor time])/100,0) as ''Priviledged Utilization'',
ROUND(([processor(_total)% processor time][process(sqlservr)% processor time])/100/@CPUCOUNT,0) as ''SQL Utilization'',
ROUND([sql statistics\batch requests/sec],0) as ''Batch Request per second'',
ROUND([sql statistics\sql compilations/sec],1) as ''SQL Compilations per second'',
ROUND([sql statistics\sql re-compilations/sec],1) as ''SQL Recompilations per second'',
ROUND([General Statistics\User Connections],0) as ''User Connections'',
ROUND([logicaldisk(_total)\avg. disk sec/read]*1000.0,1) AS ''Disk Read Latency'',
ROUND([logicaldisk(_total)\avg. disk sec/write]*1000.0,1) AS ''Disk Write Latency'',
ROUND([memory\available mbytes],0) AS ''Available Memory In MB'',
ROUND([transactions\free space in tempdb (kb)]/1024.0/1024.0,0) AS ''Free Space in Tempdb''
FROM
(
select
CounterName,
CONVERT(varchar(20),DateSampled,101) as ''Date'',
ROUND(CounterValue,7) as ''CounterValue''
from PerformanceCounter
where CounterName IN (''processor(_total)% processor time'',''processor(_total)% privileged time'',''process(sqlservr)% processor time'',''sql statistics\batch requests/sec'',''sql statistics\sql compilations/sec'',''sql statistics\sql re-compilations/sec'',''General Statistics\User Connections'',''logicaldisk(_total)\avg. disk sec/read'', ''logicaldisk(_total)\avg. disk sec/write'', ''memory\available mbytes'',''transactions\free space in tempdb (kb)'')
and DateSampled between @StartDate and @EndDate

) AS p
PIVOT
(
AVG(CounterValue)
FOR
CounterName IN ([processor(_total)% processor time],[processor(_total)% privileged time],[process(sqlservr)% processor time],[sql statistics\batch requests/sec],[sql statistics\sql compilations/sec],[sql statistics\sql re-compilations/sec],[General Statistics\User Connections],[logicaldisk(_total)\avg. disk sec/read], [logicaldisk(_total)\avg. disk sec/write], [memory\available mbytes],[transactions\free space in tempdb (kb)])
) AS pivottable
Order by [Date]',N'@StartDate datetime,@EndDate datetime',@StartDate='2016-11-10 00:00:00',@EndDate='2017-05-10 10:00:00'

I made an adjustment to the EndDate to get data at all because I have set up a "fresh Monitoring".
When running the query I get the error., which is caused by the ROUND() in the sub-select.

So, a point to be discussed: do you want to fix every Report / query to work around the conversion error or does it make sense to load the data in a suitable format?

Regards
Dirk

The system table sys.sysprocesses table is marked as depreciated in newer editions of SQL Server:
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql

The stored procedure spLoadSessionStatus still references this table in the code below:
SELECT Getdate() as "Date Captured", DB_NAME(database_id) as "Database Name",s.status,s.lastwaittype , SUM(s.waittime) as [Wait in ms],COUNT(s.spid) as [Session Count],SUM(s.open_tran) as [Open Transactions]
from sys.sysprocesses s left join sys.dm_exec_requests r
on s.spid = r.session_id
where
r.session_id is NULL
GROUP BY DB_NAME(database_id),s.status,s.lastwaittype
How can this be address in SQL Server 2016?

There are problems with dacpac, bacpac, and visual studio that I need to report or have addressed and I cant find a web presence for those modules, I did try reporting them via Developer Community but the issues have been in status "Triaged" for months. Is there a different site or portal I should be using?

The following error occured when running BPCheck v2.2 on SQL Server 2008 and 2008R2:

-Starting Feature usage
Msg 50000, Level 16, State 1, Line 1724
Feature usage subsection - Error raised in TRY block. Invalid object name 'sys.security_policies'.

Of course Row Level Security Feature should be checked for SQL Server Major Version >= 13 (and not > 9 as in the script ).

On systems with a lot of security errors sploadsecurityringbuffer is throwing an overflow error on the integer [id] columns. Changing it to bigint does the job.

The Check-Sqlncli function uses "-eq 50" to filter for 2008 R2 Native Client. This will cause false positive "TLS 1.2 supported" messages for 2008 R2 patch levels that do not support TLS 1.2 like the one below:

DisplayName: Microsoft SQL Server 2008 R2 Native Client
DisplayVersion: 10.51.2796.0

This error was also mentioned at the bottom of the page on your "guide" post.

I have to say that installing tigertoolbox is not straight forward.

when running against a VLDB (50TB+) we get the following error:

  |-Starting Buffer Pool Consumers from Buffer Descriptors
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.

Hey,

I followed all the installation instructions to install RServices on an instance of SQL Server 2016 Standard, but continue to get a fairly uninformative error when ever I try the test case (the hello 1 thing) saying it can't communicate with MS Launchpad service. When I look at the error log it says it can't close the connection. I've tried looking up info to fix things every where I can think of but still have no luck. Was hoping maybe some of the folks here might be able to give me an idea of what to try or how to get someone on the phone/screen share to help me fix the problem?

https://github.com/Microsoft/tigertoolbox/blob/140111f3c95339a8d510dbb59b7c24a9dcc470c7/change-tracking/ChangeTrackingCleanup.sql#L15

It returned the error message saying that change tracking hasn't been enabled on that table. But when I try to enable CT on that table, it prompts me that CT has already been enabled on that table. Mine is Sql server 2016 SP1.

People shouldn't run this script on servers - querying that class makes Windows Installer run reconfigure on every installed application, which can lead to downtime.

I think leaving it in the script like that will catch some people off guard.

The AdaptiveIndexDefrag does not handle if an index is setup to use data_compression =page (or row) this routine will remove the data_compression and rebuild without data_compression.

The script is missing an END in one of the BEGIN...END blocks.

Corrected in pull request #9

When parsing or executing CREATEOBJECTS.sql an error is thrown:
Msg 119, Level 15, State 1, Line 529
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Therefore spGetPerfCountersFromPowershell does not get created.

It seems that there are a few single quotes missing in the dynamlic SQL creating the stored proc

Hey Amit,
👍 Thanks for the ps script.
Just a syntactic sugar that you can check the versions by:

if ([version]$cli.DisplayVersion -lt [version]"11.0.6538")

Also line 35 and 39 should be:

$cli.DisplayVersion

Microsoft SQL Server Enterprise Edition (64-bit)
Microsoft Windows NT 6.0 (6002)
9.00.4060.00 (I know !!!!!)

AdaptiveIndexDefrag: Version: v1.6.6.2 - 2/24/2018

The following error occours executing:
EXEC dbo.usp_AdaptiveIndexDefrag @onlineRebuild = 1, @Timelimit = 240, @statsSample = 'FULLSCAN'

Message
Executed as user: NT AUTHORITY\SYSTEM. Scope has changed. Forcing rescan... [SQLSTATE 01000] (Message 50000) Error 207 has occurred. Message: Invalid column name 'data_compression_desc'. (Line Number: 2) [SQLSTATE 01000] (Message 50000). The step succeeded.

Workaround:
Comment (Line 1301)
--, [compression_type] = [data_compression_desc]

does this report cause any blocking or slowdown the performance if we run during business hours or at wrong time?

This script is completely broken at the moment.

At C:\Users\mgilbert\Desktop\temp\CheckClientUpdates.ps1:75 char:80
+ ... oft.win32.registrykey]::OpenRemoteBaseKey(‘LocalMachine’, $env:CO ...
+                                                                 ~
Missing argument in parameter list.

Note the funky unicode quotes in place of the ASCII quotes that should be present.

I am getting errors on a few database servers.
Error 515 has occurred. Message: Cannot insert the value NULL into column 'objectID', table 'Tools.dbo.tbl_AdaptiveIndexDefrag_Stats_log'; column does not allow nulls. INSERT fails. (Line Number: 2446)

i am running
EXECUTE [dbo].[usp_AdaptiveIndexDefrag] @ignoreDropObj = 1
I also get the error just running
EXECUTE [dbo].[usp_AdaptiveIndexDefrag]

Unfortunately, it appears Win32Reg_AddRemovePrograms is only available if SCCM is installed.

Per https://blogs.technet.microsoft.com/heyscriptingguy/2011/11/13/use-powershell-to-quickly-find-installed-software/ ... querying the registry may be a solution.

Not urgent at all, just filing because I'd heard Pedro talking about the script, figured I'd give it a shot.

Steps to repro:

CREATE DATABASE [¯\_(ツ)_/¯];
CREATE DATABASE [ಠ_ಠ];
GO

Then run Check_BP_Servers, and it fails with errors like:

|-Starting Database Information
  |-Building DB list
  |-Applying specific database scope list, if any
Msg 50000, Level 16, State 1, Line 1349
Database Information subsection - Error raised in TRY block. Database '?_?' does not exist. Make sure that the name is entered correctly.
Msg 50000, Level 16, State 1, Line 1349
Database Information subsection - Error raised in TRY block. Database '¯\_(?)_/¯' does not exist. Make sure that the name is entered correctly.
  |-Starting Storage analysis for In-Memory OLTP Engine
Msg 50000, Level 16, State 1, Line 1559
Storage analysis for In-Memory OLTP Engine subsection - Error raised in TRY block. Database '?_?' does not exist. Make sure that the name is entered correctly.
Msg 50000, Level 16, State 1, Line 1559
Storage analysis for In-Memory OLTP Engine subsection - Error raised in TRY block. Database '¯\_(?)_/¯' does not exist. Make sure that the name is entered correctly.

Root cause looks like the @dbname parameters being varchar, because this won't work:

DECLARE @test VARCHAR(50);
SET @test = '¯\_(ツ)_/¯';
SELECT @test

Result: ¯_(?)_/¯

Environment: 14.0.1000.169, Windows, case-sensitive default collation.

I encountered with 2 minor issues,

Collation issue:
Current: WHERE feature_name IN ('ColumnStoreIndex','InMemoryOLTP')
New: WHERE Feature_Name IN ('ColumnStoreIndex','InMemoryOLTP')
Current: SET distinctCnt = @distinctCnt, OptimBucketCnt = @OptimBucketCnt, isdone = 1
New: SET DistinctCnt = @distinctCnt, OptimBucketCnt = @OptimBucketCnt, isdone = 1
Current: WHERE total_bucket_count > distinctCnt
New: WHERE total_bucket_count > DistinctCnt
Current: distinctCnt AS [distinct_keys],
New: DistinctCnt AS [distinct_keys],
Current: WHERE total_bucket_count > distinctCnt
New: WHERE total_bucket_count > DistinctCnt
Current: WHERE total_bucket_count < distinctCnt
New: WHERE total_bucket_count < DistinctCnt
I am using Modern_Spanish_CS_AS

error convertion:
Current: WHEN @sqlmajorver >= 11 AND @winver <= 6.0 AND @accntrsservice <> 'NT AUTHORITY\NETWORKSERVICE' THEN '[INFORMATION: SQL Server Reporting Services is not running with the default account]'
New: WHEN @sqlmajorver >= 11 AND CONVERT(DECIMAL(3,1), @winver) <= 6.0 AND @accntrsservice <> 'NT AUTHORITY\NETWORKSERVICE' THEN '[INFORMATION: SQL Server Reporting Services is not running with the default account]'

Current: WHEN @clustered = 0 AND @sqlmajorver >= 11 AND @winver <= 6.0 AND @accntolapservice <> 'NT AUTHORITY\NETWORKSERVICE' THEN '[INFORMATION: SQL Server Analysis Services is not running with the default account]'
New: WHEN @clustered = 0 AND @sqlmajorver >= 11 AND CONVERT(DECIMAL(3,1), @winver) <= 6.0 AND @accntolapservice <> 'NT AUTHORITY\NETWORKSERVICE' THEN '[INFORMATION: SQL Server Analysis Services is not running with the default account]'
I am using SQL Server 2016 - 13.0.4001.0

BTW thanks for sharing this great tool
Check_BP_Servers.zip

hi,
when i launch database overview report i received this error:

missing value for parameter 'version_string'

thank's

Microsoft SQL Server Standard (64-bit)
Microsoft Windows NT 6.1 (7601)
SQL: 11.0.3128.0

AdaptiveIndexDefrag: Version: v1.6.6.2 - 2/24/2018

When executing the command:
EXEC dbo.usp_AdaptiveIndexDefrag @onlinerebuild = 1, @Timelimit = 240, @statssample = 'FULLSCAN'

Message:
Error 515 has occurred. Message: Cannot insert the value NULL into column 'objectID', table 'msdb.dbo.tbl_AdaptiveIndexDefrag_Stats_log'; column does not allow nulls. INSERT fails. (Line Number: 2475)

hi,

this vesion of the usp_AdaptiveIndexDefrag isn't updating statistics. Not even the ones on the tables wich have fragmented indexes.

with the following parameters i get 551 indexes to bedefragmented but 0 statistics to be updated.

the table tbl_AdaptiveIndexDefrag_Working is always empty.

==================================
EXEC dbo.usp_AdaptiveIndexDefrag
@Exec_Print = 0
, @printCmds = 1
, @updateStats = 1
, @updateStatsWhere = 0
, @debugmode = 1
, @outputresults = 1
, @forceRescan = 1
, @maxDopRestriction = 2
, @minPageCount = 8
, @maxPageCount = NULL
, @minFragmentation = 1
, @rebuildThreshold = 1
, @rebuildThreshold_cs = 1
, @defragDelay = '00:00:05'
, @defragOrderColumn = 'range_scan_count'
, @dealMaxPartition = NULL
, @disableNCIX = 1
, @offlinelocktimeout = 180
, @scanMode = 'DETAILED'
, @statsSample = 'FULLSCAN';

Validating options...
Starting up...

Executing AdaptiveIndexDefrag v1.6.3.1 on Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64)
Mar 2 2016 21:29:16
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )
.

OUTPUT OF THE DEBUG

The selected parameters are:
Defragment indexes with fragmentation greater or equal to 1;
Rebuild indexes with fragmentation greater than 1;
Rebuild columnstore indexes with fragmentation greater than 1;
Non-clustered indexes will be disabled prior to rebuild;
Defragment ALL indexes;
Commands WILL NOT be executed automatically;
Defragment indexes in DESC order of the RANGE_SCAN_COUNT value;
Time limit was specified and is 480 minutes;
ALL databases will be defragmented;
ALL tables will be defragmented;
We WILL be rescanning indexes;
The scan will be performed in DETAILED mode;
LOBs will be compacted;
Limit defrags to indexes with more than 8 pages;
Indexes will be defragmented OFFLINE;
Indexes will be sorted in the DATABASE;
Indexes will have its ORIGINAL Fill Factor;
All partitions will be considered;
Statistics WILL be updated on all stats belonging to parent table;
Statistics will be updated with FULLSCAN.
Statistics will be updated with Incremental property (if any) not changed from current setting.
Defragmentation will use system defaults for processors;
Lock timeout is set to 180 for offline rebuilds;
From SQL Server 2014, lock timeout is set to 5 for online rebuilds;
From SQL Server 2014, lock timeout action is set to SELF for online rebuilds;
DO print the sql commands;
DO output fragmentation levels;
Wait 00:00:05 (hh:mm:ss) between index operations;
Execute in DEBUG mode.

Listing databases...
Retrieving list of databases to loop, excluding Always On secondary replicas...
Cross checking with exceptions for today...
Preparing for new database scan...
Scanning database(s)...
Looping through list of databases and checking for fragmentation...
Working on msdb...
Building list of objects in database...
Filtering indexes according to ixtypeOption parameter...
Getting rowstore indexes...
Looking up additional index information...
Looking up additional statistic information...
.
.
.
.
.
.
.
Working on DBA...
Building list of objects in database...
Filtering indexes according to ixtypeOption parameter...
Getting rowstore indexes...
Looking up additional index information...
Looking up additional statistic information...
Listing and removing disabled indexes (except those disabled by the defrag cycle itself) or hypothetical indexes from loop...
Updating Exception mask for any index that has a restriction on the days it CANNOT be defragmented...
Looping through batch list... There are 551 indexes to defragment in 9 database(s)!
Looping through batch list... There are 0 index related statistics to update in 0 database(s), plus 0 other statistics to update in 0 database(s)!
Printing SQL statements...
Selecting an index to defragment...

When running BP Check script V2.1.5 on a SQL Server 2012 SP3 instance the following error occurred and the script never end:

|-Starting User DBs with non-default options
Msg 207, Level 16, State 1, Line 6
Invalid column name 'is_auto_create_stats_incremental_on'.

When looking at sys.dm_exec_request the script has status running, blocking_session_id 0 ,last_wait_type SOS_SCHEDULER_YIELD

C:\Windows\system32>C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe "& get-counter -counter '\LogicalDisk()\Avg. Disk Queue Length','\LogicalDisk()\Avg. Disk sec/Read','\LogicalDisk()\Avg. Disk sec/Transfer','\LogicalDisk()\Avg. Disk sec/Write','\LogicalDisk()\Current Disk Queue Length','\Memory\Available MBytes','\Paging File()*','\Process(sqlservr)% Privileged Time','\Process(sqlservr)% Processor Time','\Processor(_Total)% Privileged Time','\Processor(_Total)% Processor Time','\SQLServer:Buffer Manager\Buffer cache hit ratio','\SQLServer:Buffer Manager\Checkpoint Pages/Sec','\SQLServer:Buffer Manager\Free Pages','\SQLServer:Buffer Manager\Lazy Writes/Sec','\SQLServer:Buffer Manager\Page life expectancy','\SQLServer:General Statistics\User Connections','\SQLServer:Memory Manager\Target Server Memory (KB)','\SQLServer:Memory Manager\Total Server Memory (KB)','\SQLServer:SQL Statistics\Batch Requests/sec','\SQLServer:SQL Statistics\SQL Compilations/sec','\SQLServer:SQL Statistics\SQL Re-Compilations/sec','\SQLServer:Transactions\Free Space in tempdb (KB)' | Select-Object -ExpandProperty Readings"
Get-Counter : The specified counter could not be found.
At line:1 char:1

• & get-counter -counter '\LogicalDisk(*)\Avg. Disk Queue Length','\Log ...

•   + CategoryInfo          : InvalidResult: (:) [Get-Counter], Exception
    + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand
  
  
  

Traced in down to the counter:

C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe "& get-counter -counter '\SQLServer:Buffer Manager\Free Pages' | Select-Object -ExpandProperty Readings"

There was a change to spGetPerfCountersFromPowerShell on the 18 April 2017 to handle named instances however this change is affecting the Perf Dashboard from being able to collect the performance counters as the performance counter list in the from sub query does not handle named instances and therefore does not match on any performance counters resulting in no data available. Doing an update on the PerformanceCounter table removing the injected instance name returns data on the perf dashboard

Hi,

I tried to implement both SQL Baseline Reports and Performance Dashboard.. If the second one seems to work great, the first one is not working and I don't know why.. All the report show me "no data available"... I followed the guideline provided with the source but it doesn't work... Can you please help me ?

Regards
cmz

When running 'Load SystemHealthSession' agent job the following error message is received.
Could not find stored procedure 'dbo.spLoadSystemHealthSession'. [SQLSTATE 42000] (Error 2812). The step failed.

dbo.spLoadSystemHealthSession does not exist in the dba_local database and doesn't appear to be present in the createobjects.sql file.

Hi all,

after running succesful for 18 days the SQL Agent Job "Load SystemHealthSession" fails with the error
"The conversion of the nvarchar value '3632140316' overflowed an int column.
Running the proc dbo.spLoadSystemHealthSession manually provided more Details. The stored procedure spLoadSchedulerMonitor fails.

Msg 248, Level 16, State 1, Procedure spLoadSchedulerMonitor, Line 6 [Batch Start Line 0]
The conversion of the nvarchar value '3632140316' overflowed an int column.

When trying to open a report under XEventReporting. The other "SQL Performance Baselinining Reports" worked great! I am very eager to see the XEvents reports :)

This is due to CONVERT(money,ct2.[output]) in [dbo].[spGetPerfCountersFromPowerShell].
Replacing this by CONVERT(varchar(20),ct2.[output]) seems to do the job.

I am seeing an issue with part of the code.

In particular, the section
“/* Handling all the other statistics not covered before*/ “

It appears that the variables @objectName and @objectid are not getting set in the loop.

The tbl_AdaptiveIndexDefrag_Stats_log, then doesn’t contain the correct ObjectName and ObjectID.

I believe the two SELECT statements need changed are

			IF @Exec_Print = 1
			BEGIN
				SELECT TOP 1 @statsID = idss.statsID, @dbID = idss.dbID, @statsobjectID = idss.objectID, @dbName = idss.dbName, @statsobjectName = idss.objectName, @statsschemaName = idss.schemaName, @partitionNumber = idss.partitionNumber
				FROM dbo.tbl_AdaptiveIndexDefrag_Stats_Working idss
				WHERE idss.updateDate IS NULL --AND NOT EXISTS (SELECT TOP 1 objectID FROM dbo.tbl_AdaptiveIndexDefrag_Working ids WHERE ids.[dbID] = idss.[dbID] AND ids.objectID = idss.objectID AND idss.statsName = ids.indexName AND idss.updateDate IS NULL AND ids.exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0) 
			END
			ELSE IF @Exec_Print = 0
			BEGIN
				SELECT TOP 1 @statsID = idss.statsID, @dbID = idss.dbID, @statsobjectID = idss.objectID, @dbName = idss.dbName, @statsobjectName = idss.objectName, @statsschemaName = idss.schemaName, @partitionNumber = idss.partitionNumber
				FROM dbo.tbl_AdaptiveIndexDefrag_Stats_Working idss
				WHERE idss.updateDate IS NULL AND idss.printStatus = 0 --AND NOT EXISTS (SELECT TOP 1 objectID FROM dbo.tbl_AdaptiveIndexDefrag_Working ids WHERE ids.[dbID] = idss.[dbID] AND ids.objectID = idss.objectID AND idss.statsName = ids.indexName AND idss.updateDate IS NULL AND ids.exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0) 
			END

TO

			IF @Exec_Print = 1
			BEGIN
				SELECT TOP 1 @objectID=idss.objectID, @objectName=idss.objectName, @statsID = idss.statsID, @dbID = idss.dbID, @statsobjectID = idss.objectID, @dbName = idss.dbName, @statsobjectName = idss.objectName, @statsschemaName = idss.schemaName, @partitionNumber = idss.partitionNumber
				FROM dbo.tbl_AdaptiveIndexDefrag_Stats_Working idss
				WHERE idss.updateDate IS NULL --AND NOT EXISTS (SELECT TOP 1 objectID FROM dbo.tbl_AdaptiveIndexDefrag_Working ids WHERE ids.[dbID] = idss.[dbID] AND ids.objectID = idss.objectID AND idss.statsName = ids.indexName AND idss.updateDate IS NULL AND ids.exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0) 
			END
			ELSE IF @Exec_Print = 0
			BEGIN
				SELECT TOP 1 @objectID=idss.objectID, @objectName=idss.objectName, @statsID = idss.statsID, @dbID = idss.dbID, @statsobjectID = idss.objectID, @dbName = idss.dbName, @statsobjectName = idss.objectName, @statsschemaName = idss.schemaName, @partitionNumber = idss.partitionNumber
				FROM dbo.tbl_AdaptiveIndexDefrag_Stats_Working idss
				WHERE idss.updateDate IS NULL AND idss.printStatus = 0 --AND NOT EXISTS (SELECT TOP 1 objectID FROM dbo.tbl_AdaptiveIndexDefrag_Working ids WHERE ids.[dbID] = idss.[dbID] AND ids.objectID = idss.objectID AND idss.statsName = ids.indexName AND idss.updateDate IS NULL AND ids.exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0) 
			END

Documentation is wrong. All of the options examples have the same issue: The option should be 1, not 0. For example:

@printCmds defaults to 0 (do not print all commands to screen) or optionally 0 (print all commands to screen).

Should be:
@printCmds defaults to 0 (do not print all commands to screen) or optionally 1 (print all commands to screen).

I see two datasources for the XEReports. For the Performance reports we just provide the SQL Server which has the dba_local database as the datasource. Please confirm what the two datasources should be for the XEventReporting.

BTW...this is a treasure trove! Thanks for sharing this. Many thanks for Parikshit and Tiger team and Pedro for pointing this to me

I was able to create the dba_local database, objects and the sql jobs and all is fine. Did not know how the powershell scripts are being used. Please clarify.

It returns:
Error 4104 has occurred. Message: The multi-part identifier "sp.partition_number" could not be bound. (Line Number: 2)

To prevent this, I suggest to change this parts:

SUBSTRING(qt2.text,
(CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),
(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))),

to this:

SUBSTRING(qt2.text,
1+(CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),
1+(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))),

as described here:
https://msdn.microsoft.com/en-us/library/ms181929.aspx (Example B)

It appears that there is no check to see if a table is dropped after the buckets are created but before the checks are performed. This results in an endless loop in usp_CheckIntegrity.

Example output:

Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:877AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog.
 [SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:880AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:880AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:883AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:887AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:890AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:890AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:893AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:897AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:900AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name "dbo.tmpPositionsIds". Check the system catalog. 
[SQLSTATE 42000] (Error 50000)  Jul  5 2018  1:00:00:900AM - Started table checks on ieMR - table dbo.tmpPositionsIds [SQLSTATE 01000] (Message 50000)  Error raised in TRY block. Cannot find a table or object with the name...  The step failed.

Exce proc like this:
EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'SomeDataBase', @tblName = 'dbo.SomeTable', @onlineRebuild = 1, @dealLOB=1 , @printCmds = 1, @debugmode = 1
but in messages i see that proceeds all tables, not just one from params:

Hi,

There is this conversion error if the user that runs the job is configured with Portuguese language:

Msg 8114, Level 16, State 1, Procedure xp_enumerrorlogs, Line 1 [Batch Start Line 2]
Error converting nvarchar date tipe to datetime

I had to add the following code at the beginning of step 1:

set language 'us_english'

Hi,

I downloaded and installed SQL Performance Baseline which created a new database, objects and jobs to gather and collect performance data to run the SSRS reports.

However, one of the jobs (Load SystemHealthSession) is missing the sproc spLoadSystemHealthSession to execute. I don't see it anywhere in the install package.

Am I missing something?

Thanks.

When setting @statsThreshold to a small value eg 0.002, there is a calculation performed to determine whether to update statistics (around line 2045 & 2345 of procedure [dbo].[usp_AdaptiveIndexDefrag]).
The calc uses @rowmodctr / @record_count > 0.002 to determine if update is required but the result when less than 0 is always 0 as both variables are INT datatypes.
The fix is to DECLARE @rowmodctr NUMERIC(10,3) instead of BIGINT.

/* Repro issue example table of approx 4million rows, 13000 updates */
DECLARE @statsThreshold DECIMAL(10,3)
, @statsminRows BIGINT
, @rowmodctr    BIGINT  /* Change @rowmodctr from BIGINT to NUMERIC(10,3) to get a non-zero result when dividing */
, @record_count BIGINT

select @statsThreshold = 0.002
, @statsminRows = 10000
, @rowmodctr = 13321
, @record_count = 4546132

select @statsThreshold,@rowmodctr,@record_count,@statsMinRows,@rowmodctr/@record_count

IF --(@statsThreshold IS NOT NULL AND @statsThreshold BETWEEN 0.001 AND 100.0 AND @statsMinRows IS NULL AND (@rowmodctr*100)/@record_count >= @statsThreshold)
--OR 
(@statsThreshold IS NOT NULL AND @statsThreshold BETWEEN 0.001 AND 100.0 AND @statsMinRows IS NOT NULL AND @record_count >= @statsMinRows AND (@rowmodctr*100)/@record_count >= @statsThreshold)
/*OR (@statsThreshold IS NULL AND (
						(@record_count BETWEEN 6 AND 500 AND @rowmodctr >= 500) OR -- like the default
						(@record_count BETWEEN 501 AND 10000 AND (@rowmodctr >= (@record_count*20)/100 + 500 OR @rowmodctr >= SQRT(@record_count*1000))) OR -- 500 + 20 percent or simulate TF 2371
						(@record_count BETWEEN 10001 AND 100000 AND (@rowmodctr >= (@record_count*15)/100 + 500 OR @rowmodctr >= SQRT(@record_count*1000))) OR -- 500 + 15 percent or simulate TF 2371
						(@record_count BETWEEN 100001 AND 1000000 AND (@rowmodctr >= (@record_count*10)/100 + 500 OR @rowmodctr >= SQRT(@record_count*1000))) OR -- 500 + 10 percent or simulate TF 2371
						(@record_count >= 1000001 AND (@rowmodctr >= (@record_count*5)/100 + 500 OR @rowmodctr >= SQRT(@record_count*1000))) -- 500 + 5 percent or simulate TF 2371
					))*/
BEGIN	
select 'RUNNING'
END
      

This is explained by the following query returning NULL values except for the Data and Time columns:
exec sp_executesql N'SELECT
Date,[Time],
[buffer manager\page life expectancy] AS ''Page Life Expectancy'',
[buffer manager\buffer cache hit ratio] AS ''Buffer Cache Hit Ratio'',
ROUND([memory manager\target server memory (kb)]/1024.0/1024.0,0) AS ''Target Server Memory'',
ROUND([memory manager\total server memory (kb)]/1024.0/1024.0,0) AS ''Total Server Memory'',
ROUND([memory\available mbytes]/1024.0,0) AS ''Available Memory'',
[buffer manager\checkpoint pages/sec] AS ''Checkpoint Pages per sec'',
[buffer manager\lazy writes/sec] AS ''Lazy Writes per sec'',
[Buffer Manager\Free pages] AS ''Free pages'',
ROUND([transactions\free space in tempdb (kb)]/1024.0/1024.0,0) AS ''Tempdb Free Space in GB''
FROM
(
SELECT CounterName,
CONVERT(varchar(20),DateSampled,101) as ''Date'',
LEFT(CONVERT(varchar(20),DateSampled,8),5) as ''Time'',
DATEPART(mi, DateSampled) AS ''Minute'',
ROUND(CounterValue,0) AS ''CounterValue''
FROM PerformanceCounter
WHERE (CounterName like ''%buffer manager\page life expectancy%'' or CounterName like ''%buffer manager\buffer cache hit ratio%'' or CounterName like''%memory manager\target server memory (kb)%'' or CounterName like ''%memory manager\total server memory (kb)%'' or CounterName like ''%buffer manager\checkpoint pages/sec%'' or CounterName like ''%buffer manager\lazy writes/sec%'' or CounterName like ''%Buffer Manager\Free pages%'' or CounterName like ''%transactions\free space in tempdb (kb)%'' or CounterName like ''%memory\available mbytes%'' or CounterName like ''%buffer manager\page life expectancy%'')
and DateSampled between @StartDate and @EndDate
) as p
PIVOT
(
AVG(CounterValue)
FOR
CounterName IN ([buffer manager\page life expectancy],
[buffer manager\buffer cache hit ratio],
[memory manager\target server memory (kb)],
[memory manager\total server memory (kb)],
[memory\available mbytes],
[buffer manager\checkpoint pages/sec],
[buffer manager\lazy writes/sec],
[Buffer Manager\Free pages],
[transactions\free space in tempdb (kb)])
) AS pivottable
ORDER by [Time]',N'@StartDate nvarchar(18),@EndDate nvarchar(18)',@StartDate=N'4/17/2017 00:01:00',@EndDate=N'4/18/2017 23:59:59'

Hi,
Tkz for this script. There are 2 errors in the last commit:

• line 842: orphelan CREATE TABLE Info_
• line 4566: FROM msdb.dbo.backupset WITH (NOLOCK) b is wrong, alias must be put after the table name -> FROM msdb.dbo.backupset b WITH (NOLOCK)

Corrected in pull request #7

Hi,

Can you help me whit the subject ?

Have one SQL 2005 instance that runs dbo.usp_AdaptiveIndexDefrag every night with no parameters, but only takes 15 seconds and always less than one minute.
The resident databases are not in shape to justifie this behavior.
If we implement the SP with the option "SET @deploymode = 0", the next night it works every 8 hours by default.
The nigth after we have the same behavior.

Can you advise one specific version to run with SQL 2005? Any thoughts?

Thank you very much.

Regards

Alex.