IT

Information Technology

May 2, 2022

SQL Server migrating DB from 2008 to 2017

The scenario

We have several databases running on SQL Server 2008 which needs to be decommissioned. The databases are a mixture of production, training and development. We have two destination servers which are SQL Server 2017, one for production and one for development. Each database is quite small around 10-20 GB.
I am using Ola Hallengren’s maintenance scripts on the old SQL Server, the databases are running in full recovery mode and I backup the databases once a day and the transaction logs every five minutes as suggested by Brent Ozar (he has great training).

Testing the test databases

I notified the test users when I was going to move the database. Processing one database at a time I took the databases offline and check the web interface has failed, then restore the database to the dev SQL Server 2017 restoring it to the last transaction backup.
The databases were left at the compatibility level for SQL Server 2008 (100). The other issue is the recovery time as this setting was not available in version 2008 so I checked it and set it to 60 seconds which is the default for 2017.

/* Checking recovery time on the databases... */
SELECT name, target_recovery_time_in_seconds FROM sys.databases;

/* Set the recovery time on the databases... */
ALTER DATABASE <your db name> SET target_recovery_time = 60 seconds;

The connection account was in Microsoft Active Directory (AD) so I was able to add the account and check the access. I configure the web host to point to the new SQL Server and it started working.
The advantage of having accounts in AD is that you do not have to worry about the unique system account id so there is no messing around sorting out the account.

We completed a round of testing the web application which worked fine, the vendor said it should work fine in compatibility level 2017 (140) so we set it and re-ran the tests and it was fine.

Testing the training databases

Training databases are not development databases so we need to run them in a production database. The advantage is, that if it all goes horribly wrong, we can roll back to the previous version of the database (migration is a one-way street).
We will still need to check the compatibility level, I left it at 2008, and I set the recovery time to 60 seconds as the migrated value was 0 seconds.

Going live

We migrated the databases we tested in the development environment as the vendor was very clued up and easy to work with. The database application running in training is business-critical and they seem to be unsure of the technology and testing is not under my control so I am still waiting.

Checking the database

I used Brent’s First Aid Kit I check the health of the databases. All the tables were heaps, there was an index issue but the tables were small and the application runs fine so although I would like to tweak the database to run faster, but the bottleneck seems to be the ColdFusion Server code.

May 1, 2022

SQL Server account migrations

SQL Server accounts

There are two types of ‘user’ accounts, an Active Directory (AD) and an internal account. Each account has a unique ID but the issue is if you need to create another internal user account although the username will be the same the ID will be different.

Fixing internal accounts to work with migrated databases

If a database is migrated from a different server and a new internal account needs to be created to match the old user credentials the ID will be different I use the following command to fix the user account:

sp_change_users_login ‘AUTO_FIX’, ‘<broken username>’;

If all your accounts are on AD this won’t be an issue as the ID will be consistent.

dbatools.io

I remember seeing a presentation on an online seminar from Jess Pomfret using ‘dbatools‘ to migrate database and user accounts. I found a presentation on youtube called ‘LifeHacks – easy database migrations with dbatools‘.

The downside is that you need to install the modules but they are free and feature-rich. If you do not use dbatools check out their website and the commands.

The command I used Copy-DbaLogin, the account I used to run the command was an admin on both servers to I simply ran the command defining the source, destination and the account name and dbatools did the rest:

## Migrate login

$migrateLoginSplat = @{

Source = “<source server URL>“

Destination = ‘<destination server URL>‘

Verbose = $true

}

Copy-DbaLogin @migrateLoginSplat

The command copied the account over, set the id to the correct value and copied over the password so within seconds all of the required accounts had been transferred with little effort.

I would like to thank Jess Pomfret and I highly suggest you look at dbatools.io.

February 9, 2022

IBM’s Maximo SQL Server Table (Heap) Fragmentation

Maximo tables by default become fragmented as they are heaps

IBM’s Maximo User Performance

Where I work, we are running two sites with Maximo 7.6 and the users do not have an issue with the performance, most of the ‘power users’ love the system so when I criticise it I feel a bit out of step.

Maximo is an old system and the implementation is old and this is a serious bottleneck on the system. The front end interface is easy to change but in internals is much harder.

All Maximo tables are heaps – All tables within Maximo are heaps, if data is modified and deleted they can become fragmented and have holes. (Heaps are tables without a clustered index).

Basics

Tables – A table (not a heap) has a clustered index which should be the primary key. The table is the index, the data is stored in the order defined by the clustered index. When rows are added or deleted, the server manages changes; when the data in a row increases, for example, inserting into a null column, if the block holding the data isn’t large enough the server creates a page split and the data is shared across two blocks. Inserting, deleting and updating does not create fragmentation in the table.

Heaps – Heaps are that, the data is stored as inserted into the table in no particular order and each row is assigned a system row identifier rid. When data is inserted it’s added to the end of the table when data is deleted, the blocks where the data used still remain part of the table until the table is rebuilt but these unused blocks as still accessed during a table scan. When a row is altered and there isn’t enough space in the blocks to hold the data is moved to a new location and a forward pointer is created in the table to point to this new area. Only be rebuilding the table will clean up the forward pointers but this also forces a rebuild of any table indexes.

The problem with rebuilding heaps – The issue with rebuilding a heap is that with ‘Standard SQL Server’ it takes the heap offline, if you have the ‘Enterprise’ version the heap can be kept online during the rebuild. With our production database, the biggest heap on our takes around two hours to rebuild.

Why use heaps? – Heaps are faster for inserts and for ‘Key/RID lookups’. The RID enables faster row location than an index id but only if the heap is not fragmented.

Key lookup has to find the identify the page(s) which hold the index, from that identify the page to collect the row id then open the page with that id.

Key lookup

RID lookup as the RID holds the ‘page number’, ‘slot number’ and the ‘row’ it can skip the first to step a key lookups neeto take so reading data with lookups are marginally faster.

RID lookup

Maximo uses heaps as tables – this means the tables can have unused block and forward lookups which slows the system down. This might have been a performance enhancement ten years ago but heaps are not an advantage now. This isn’t an exclusive issue with Maximo and SQL Server, before we migrated the data from our Oracle instance we had issues with ‘block chaining’ which is similar to SQL Server ‘forward lookups’ and the performance of the system would become a serious issue.

Checking the health of heaps – I used two SQL scripts which give slightly different information about heaps. The first query is slower than the first.

/* forward fetch count on Dev took 17 mins to run*/
SELECT OBJECT_NAME(OBJECT_ID) AS table_name,
 forwarded_record_count, /* Number of records in a heap that have forward pointers to another data location. */
 avg_fragmentation_in_percent, /* Extent fragmentation for heaps in the IN_ROW_DATA allocation unit. */
 page_count /* Total number of data pages. */
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED')
WHERE forwarded_record_count <> 0
ORDER BY 3 DESC;

The output from the query is below and it gives a good summary of the status your heaps:

table_name	forwarded_record_count	avg_fragmentation_in_percent	page_count
workorder	118	9.805747249	4570252
wostatus	638	13.17411125	887828
multiassetlocci	36	11.05582396	829461
workview	11112	18.15360944	623885
locationspec	119	20.1733689	467883
assetspec	229	22.56323893	465526
locations	17047	7.540335858	24292
route_stop	1	0.2066115702	11599
lochierarchy	3	6.755373593	7792
locoper	12	12.73291925	7719
assignment	30	4.47761194	6419
jobplan	1	3.064516129	4949
jobtask	1	0.1821493625	4380
person	5	81.81818182	81
maxsysindexes	97	28.57142857	50
reportsched	5	66.66666667	17
maxuser	3	33.33333333	15

The query below runs relatively fast and like all lazy coders I print out the query to rebuild the heap:

/* forward fetch usage and rebuild command */
SELECT os.forwarded_fetch_count /* Count of rows that were fetched through a forwarding record. */
 ,OBJECT_NAME(os.OBJECT_ID, os.database_id) AS table_name
 ,command = N'ALTER TABLE ' + QUOTENAME(DB_NAME(os.database_id)) + N'.'
 + QUOTENAME(OBJECT_SCHEMA_NAME(os.OBJECT_ID, os.database_id)) + N'.'
 + QUOTENAME(OBJECT_NAME(os.OBJECT_ID, os.database_id)) + N' REBUILD WITH (ONLINE=OFF, MAXDOP=0);'
 ,heap_size_mb = CAST(ps.reserved_page_count * 8.0 / 1024. AS BIGINT) /* reserved_page_count - Total number of pages reserved. in_row_reserved_page_count + lob_reserved_page_count + row_overflow_reserved_page_count. */
 ,nonclustered_indexes =
 (
 SELECT COUNT(DISTINCT i.index_id)
 FROM sys.indexes i
 WHERE os.OBJECT_ID = i.OBJECT_ID
 AND i.index_id <> 0
 AND i.is_disabled = 0
 AND i.is_hypothetical = 0
 )
 --SELECT COUNT(*)
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) os
 INNER JOIN sys.dm_db_partition_stats ps
 ON ps.OBJECT_ID = os.OBJECT_ID
 AND ps.index_id = os.index_id
 AND ps.partition_number = os.partition_number
WHERE os.index_id = 0
 AND os.forwarded_fetch_count > 0
ORDER BY os.forwarded_fetch_count DESC;

The output is from the script above is below:

forwarded_fetch_count	table_name	command	heap_size_mb	nonclustered_indexes
1280651	locations	ALTER TABLE [max76UAT].[dbo].[locations] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	189	11
3506	workview	ALTER TABLE [max76UAT].[dbo].[workview] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	4874	2
2978	workorder	ALTER TABLE [max76UAT].[dbo].[workorder] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	35710	39
1455	maxsysindexes	ALTER TABLE [max76UAT].[dbo].[maxsysindexes] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	0	3
208	person	ALTER TABLE [max76UAT].[dbo].[person] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	0	10
201	maxuser	ALTER TABLE [max76UAT].[dbo].[maxuser] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	0	5
172	locoper	ALTER TABLE [max76UAT].[dbo].[locoper] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	60	3
97	reportsched	ALTER TABLE [max76UAT].[dbo].[reportsched] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	0	3
64	jobplan	ALTER TABLE [max76UAT].[dbo].[jobplan] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	38	9
55	assignment	ALTER TABLE [max76UAT].[dbo].[assignment] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	50	7
20	assetspec	ALTER TABLE [max76UAT].[dbo].[assetspec] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	3637	4
7	locationspec	ALTER TABLE [max76UAT].[dbo].[locationspec] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	3655	5
5	route_stop	ALTER TABLE [max76UAT].[dbo].[route_stop] REBUILD WITH (ONLINE=OFF, MAXDOP=0);	90	7

The smaller heaps can be quickly rebuilt in seconds but the larger tables are too big to be rebuilt without causing an issue.
From the results from the query rebuilding the ‘locations’ table which is relatively small and has the highest forward record count and forward record fetch count will help performance.

Heap maintenance
I use Ola Hallengren’s maintenance scripts but if you want the index maintenance to rebuild the heaps you need to alter the script. Brent Ozar did a post about altering the script to rebuild heaps ‘How To Fix Forwarded Records‘. The only problem as been if you run this you could knock out the system for hours fixing the issues with your heaps so think carefully about the issue. I enabled the heap rebuild but then I disabled it as it caused too many issues. I normally plan heap rebuilds to ensure we do not have any issues and the users are ~~happy~~ less grumpy.

Summary
Heaps were only created for ‘Extract Transform and Load’ (ELT) process and not for use in production systems. I have raised this issue with IBM but nothing is going to change in the short term. If you want good performance you need to ensure the heaps are not fragmented, do not have many forward lookups which are being used. The only blunt tool to fix these issues is a table rebuild which also rebuilds all the heaps indexes and unless the server is the enterprise edition takes the heap off-line.

January 5, 2022

Maximo – Configuring RoboCopy to duplicate DocLinks to a second server

Intro

IBM’s Maximo can be used to record maintenance on objects. We use it to track maintenance of items to ensure our sites are safe and fall within any nation-state regulation and our sites keep running.

Like most process systems, it has a relational database and a text search engine, it uses the SQL Server for both, and a file store which is on local storage on the application server.

Configuration

We have three servers:

SQL Server – for the relational database and the text search engine.
Application server – to run the main interface for the web front-end.
Reporting server – to offload the running of the long reports.

I manage the operating systems and the management of the SQL Server, so I have a limited perspective on the system, and I see problems, but the users love the application, and it gives them a tool to complete their business tasks.

Heaps, implicit transactions & Indexes

All of the tables are heaps, they don’t have a clustered index, and the queries from the server use implicit transactions which always go single-threaded.
IBM allows you to manage your indexes so using Brent’s DEATH method (without the heap bit) I’ve trimmed the DB to not have indexes it doesn’t use. The problem is that the heaps are very wide so indexing will always be an issue.

Doclinks

Doclinks is the managed area for the file store. This area allows items like PDF items to be stored and retrieved for the users to check, the problem is that Maximo only allows local storage for the Doclinks area it can’t be on a shared storage device which is OK for the main application server but for the reporting server have access to the files they need to be replicated from the application servers local storage to the reporting servers local storage.

RoboCopy

Microsoft’s command-line RoboCopy (Robust File Copy) directory/file replication software has been around since 1996. There are probably better tools out there but as it can be installed with the standard Windows Server Operating System, it works, people know how to use it, and is from a trusted vendor we tend to use it.

Problem domain

We have a primary file store area we need to regularly synchronize with the secondary area. There tends not to be a rapid change in file store so the secondary file store can be slightly behind the primary.
Ideally, when a file is changed in the file store it should be event-driven but we are not using this method as we are going for a simpler, polling, implementation.

Task Scheduler

Using the built-in task scheduler, I configure a batch script to run the RoboCopy tool. With feedback from the users, they felt that they wouldn’t want the reporting file store to be out of date by more than thirty minutes.
The RoboCopy application is relatively lightweight and quick to run especially if it is only checking to see if it needs to copy or delete a file on the secondary file store.

Problems

Initially, I set up the task with a simple one-liner which seemed to work but occasionally I received feedback about files not copying over. The implementation wasn’t good enough, when an application runs it should exit with an exit status to indicate how successful it was. I also want to create a log so if there was an issue, I would have something to look at instead of looking dumb.

I found a great site with information about creating an error-handling batch file at ss64.com. This enables the management of the exit status from RoboCopy and I also added a log file to be created over the last log file each time. I know this is a crude solution but so far it seems to have worked or I would have to implement a rolling log process.

Solution

I am not sure if the changes I made fixed the issue but running RoboCopy more frequently seemed to resolve the issue and the user base is happy with the solution.

rem -- Run robocopy
robocopy L:doclinks <<your destination server>>L$doclinks /MIR /Z /LOG:C:robocopy_scriptrobocopy_last_log.txt

rem -- report on exit status
if %ERRORLEVEL% EQU 16 echo ***FATAL ERROR*** & goto end
if %ERRORLEVEL% EQU 15 echo OKCOPY + FAIL + MISMATCHES + XTRA & goto end
if %ERRORLEVEL% EQU 14 echo FAIL + MISMATCHES + XTRA & goto end
if %ERRORLEVEL% EQU 13 echo OKCOPY + FAIL + MISMATCHES & goto end
if %ERRORLEVEL% EQU 12 echo FAIL + MISMATCHES& goto end
if %ERRORLEVEL% EQU 11 echo OKCOPY + FAIL + XTRA & goto end
if %ERRORLEVEL% EQU 10 echo FAIL + XTRA & goto end
if %ERRORLEVEL% EQU 9 echo OKCOPY + FAIL & goto end
if %ERRORLEVEL% EQU 8 echo FAIL & goto end
if %ERRORLEVEL% EQU 7 echo OKCOPY + MISMATCHES + XTRA & goto end
if %ERRORLEVEL% EQU 6 echo MISMATCHES + XTRA & goto end
if %ERRORLEVEL% EQU 5 echo OKCOPY + MISMATCHES & goto end
if %ERRORLEVEL% EQU 4 echo MISMATCHES & goto end
if %ERRORLEVEL% EQU 3 echo OKCOPY + XTRA & goto end
if %ERRORLEVEL% EQU 2 echo XTRA & goto end
if %ERRORLEVEL% EQU 1 echo OKCOPY & goto end
if %ERRORLEVEL% EQU 0 echo No Change & goto end
:end

if %ERRORLEVEL% EQU 1 exit 0
exit %ERRORLEVEL%


REM https://social.technet.microsoft.com/wiki/contents/articles/1073.robocopy-and-a-few-examples.asp
REM The following command will mirror the directories using Robocopy:

Rem Robocopy F:inetpubwwwroot <server>\d$inetpubwwwroot /MIR /FFT /Z /XA:H /W:5

REM Robocopy SourceServerShare DestinationServerShare /MIR /FFT /Z /XA:H /W:5
REM Explanation of the switches used:

REM /MIR specifies that Robocopy should mirror the source directory and the destination directory. Note that this will delete files at the destination if they were deleted at the source.
REM /FFT uses fat file timing instead of NTFS. This means the granularity is a bit less precise. For across-network share operations this seems to be much more reliable - just do not rely on the file timings to be completely precise to the second.
REM /Z ensures Robocopy can resume the transfer of a large file in mid-file instead of restarting.
REM /XA:H makes Robocopy ignore hidden files, usually these will be system files that we're not interested in.
REM /W:5 reduces the wait time between failures to 5 seconds instead of the 30 second default.

Below are the screenshots of the ‘Task’ I created in the ‘Task Scheduler’:

Task Scheduler - General

Task Scheduler - Tridggers

Task Scheduler - Actions

Task Scheduler - Conditions

Task Scheduler - Settings

Task Scheduler History

Task Scheduler – Task

If you save a task from the Task Scheduler it produces the XML similar to the file below:

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
 <RegistrationInfo>
 <Date>2021-03-10T16:02:15.4687623</Date>
 <!-- add a valid domain and admin account for your server -->
 <Author>**your domain****an admin account**</Author>
 <Description>The doclinks area is used for the files store for Maximo so this needs to be replicated to the report server.</Description>
 </RegistrationInfo>
 <Triggers>
 <CalendarTrigger>
 <StartBoundary>2021-03-10T00:00:00</StartBoundary>
 <Enabled>true</Enabled>
 <ScheduleByDay>
 <DaysInterval>1</DaysInterval>
 </ScheduleByDay>
 </CalendarTrigger>
 </Triggers>
 <Principals>
 <Principal id="Author">
 <!-- add a valid domain and admin account for your server -->
 <Author>**your domain****an admin account**</Author>
 <LogonType>Password</LogonType>
 <RunLevel>HighestAvailable</RunLevel>
 </Principal>
 </Principals>
 <Settings>
 <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
 <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
 <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
 <AllowHardTerminate>true</AllowHardTerminate>
 <StartWhenAvailable>false</StartWhenAvailable>
 <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
 <IdleSettings>
 <StopOnIdleEnd>true</StopOnIdleEnd>
 <RestartOnIdle>false</RestartOnIdle>
 </IdleSettings>
 <AllowStartOnDemand>true</AllowStartOnDemand>
 <Enabled>true</Enabled>
 <Hidden>false</Hidden>
 <RunOnlyIfIdle>false</RunOnlyIfIdle>
 <WakeToRun>false</WakeToRun>
 <ExecutionTimeLimit>P1D</ExecutionTimeLimit>
 <Priority>7</Priority>
 </Settings>
 <Actions Context="Author">
 <Exec>
 <!-- The location of the RoboCopy batch script -->
 <Command>C:robocopy_scriptrobocopy_doclinks_to_report_server.bat</Command>
 </Exec>
 </Actions>
</Task>

Log file

If you are generating a log file from Robocopy is should looks something like the one below but I have removed some of the data…

-------------------------------------------------------------------------------
 ROBOCOPY :: Robust File Copy for Windows 
-------------------------------------------------------------------------------

 Started : 03 January 2022 19:00:00
 Source : L:doclinks
 Dest : <<server name removed >>L$doclinks

 Files : *.*
 
 Options : *.* /S /E /DCOPY:DA /COPY:DAT /PURGE /MIR /Z /R:1000000 /W:30 

------------------------------------------------------------------------------

 12425 L:doclinks
 41944 L:doclinksAttached
 163297 L:doclinksattachments
 0 L:doclinksCommLogDocs
 0 L:doclinksCONTTEMP
 2 L:doclinksdefault
 0 L:doclinksdiagrams
 0 L:doclinksENGDOCS
 0 L:doclinksimages
 17 L:doclinksIntegrity
 ...

------------------------------------------------------------------------------

 Total Copied Skipped Mismatch FAILED Extras
 Dirs : 251 0 0 0 0 0
 Files : 219394 0 219394 0 0 0
 Bytes : 239.598 g 0 239.598 g 0 0 0
 Times : 0:00:00 0:00:00 0:00:00 0:00:00
 Ended : 03 January 2022 19:00:00

Text content

Github

I have saved the examples to my GitHub area RoboCopy.

GitHub is a great service to allow people to version, share and collaborate so if you can start to use it. There are also some amazing project you can access and look at the source code.

December 31, 2021

SQL Server – Using The Calendar Table

Calendar & Audit tables

When I first heard about calendar tables I was intrigued. I built a calendar table to see if I could understand the hype about them but I could not see how I would ever use one until now.

Calendar table

I used Sean Smith’sDate Calendar script to build the calendar table.

The table looked interesting with useful information, but I couldn’t see how to apply it to the queries that I normally build until I had an issue with a supplier who said we had gone over our limit of user accounts.

Calendar table

I need to create a script to analyse the audit table over a time period evaluating the number of active users at each point in time. Initially I did not think about the calendar table and manually created a few iterations of the SQL with different dates, but I was finding it too restrictive until eventually I remembered about the ‘Calendar table’.

Step one – collecting on the need data to a smaller table

The audit table was large (over 50 million rows) and covered all objects in the system which had been altered. Initially I started to write queries on the audit table, but the queries could table over 40 minutes, so I had to reduce the time of the iterations from alter, run and check results.

As I only needed the data over the last year and only on user accounts which had been active. I created a smaller table (around 1600 rows) with the select data from the audit table as would allow me to run queries faster and develop the SQL faster (this would have been the ideal situation to create a heap).

The audit contained a line for every time a user accessed the system with a status of ‘login’ and a time. Working on the assumption if an account is active they are logging in before a point in time and after, both these conditions need to hold true for the account to be active. If the account hasn’t logged in at the defined point in time or hasn’t logged in after the point in time, then the account is not active.

SELECT count(*)
FROM DBATools.dbo.user_audit
where login_min_date <= calendar_date
AND login_max_date>= calendar_date
AND calendar_date = ‘2021-06-01’;

Step Two – developing the queries and using the calendar table

The queries on the smaller table were considerably faster which did enable quicker iterations of the code. I was able to quickly iterate over the my SQL code, I did not even create an index as my scratch table was so small.

Integrating the calendar table was simple as I could only see one option to implement it. For each point in time, I would have to run the count so it had to be a correlated subquery.

Using this gave me great flexibility on which dates I could select, I would start off with a single date, then increase to daily or monthly. I set the calendar year to be 2021 and I initially used one date then I used five different dates. The output I finally settled on was the first day of each week of the year.

SELECT calendar_date, (
 SELECT count(*) 
 /* like the user table to the group table */
 FROM DBATools.dbo.user_audit
 WHERE
 /* logged in before and after the date */
 login_min_date <= calendar_date AND login_max_date>= calendar_date 
 ) active_users
from
 dbatools.dbo.date_calendar DC
 WHERE DC.calendar_year=2021
 AND dc.day_of_week = 1;

Query output

Statistics

The the query running in about 22 ms I didn’t see the point in tuning the indexes for this one off process.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.

(52 rows affected)
Table ‘Worktable’. Scan count 52, logical reads 3683, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘user_audit’. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 34, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘date_calendar’. Scan count 2, logical reads 7, physical reads 3, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 21 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Conclusion

You need to use the right tool for the job so a calendar table seems essential if you need to analyse audit or log tables as it gives easy control over the granularity of the results.

I think the calendar table is a great tool to see trending of your data which might not be obvious when you first look at it.

December 11, 2021

SQL Server – Counting the rows in a table

SQL Server
Counting the rows in a table

If you need to find how many rows there are in a table the simplest method that works across most databases is to use the “select count(*) from <table name>”.

The way Microsoft’s SQL Server manages the request is to find the narrowest index or the table and use that to scan it for the number of rows. For MySql it used to be selecting a known narrow indexed column, like the id, would result in a quicker result but I don’t know if recent versions are better…

With MS SQL Server if we turn on statistics for IO (to show blocks read) and time (this is less helpful) to give an indication of the load on resources when we run the query.

I’m using SQL Server Management Studio (SSMS) 18.10 and SQL Server 2019. The statistics information will be shown in the messages tab of the output from the query:

SET STATISTICS IO, TIME ON;

SELECT COUNT(*)
FROM Posts;

Clicking on the messages tab in the SSMS after the select count has been run we can see that we had “logical reads 96921”. This means we had 96,921 blocks read to calculate the number of rows in the Posts table.

1 ms + 225 ms = 226 ms compile and execute time.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

(1 row affected)
Table ‘Posts’. Scan count 33, logical reads 96921, physical reads 0, page server reads 0, read-ahead reads 0, …

SQL Server Execution Times:
CPU time = 6499 ms, elapsed time = 225 ms.

If we look at the blocks read and the execution plan it shows it scanned of the index to calculate the row count:

SQL Server – Counting the rows in a table

A more efficient way to collect the row count of a table is from its statistics which are maintained by the system. The query is more complicated and it’s harder to remember but if you need quick results it’s very fast.

The SQL code below is from Pinal Dave’s post: Fastest Way to Retrieve Rowcount for a Table.

I have altered it so that it only returns the row count to match the first query.

SELECT SCHEMA_NAME(t.schema_id) SchemaName,
 t.[name] TableName,
 SUM(p.row_count) TotalRows
FROM sys.tables t
 INNER JOIN sys.dm_db_partition_stats p
 ON t.object_id = p.object_id
 AND t.type_desc = 'USER_TABLE'
 AND p.index_id IN ( 0, 1 )
WHERE t.[name] = 'Posts'
GROUP BY t.schema_id,
 t.[name]
ORDER BY TotalRows DESC;

Looking at the number of blocks read and the time to run:

SQL Server parse and compile time:
CPU time = 43 ms, elapsed time = 43 ms.

(1 row affected)
Table ‘syssingleobjrefs’. Scan count 3, logical reads 6, physical reads 0,,…
Table ‘sysidxstats’. Scan count 3, logical reads 6, physical reads 0,…
Table ‘sysrowsets’. Scan count 1, logical reads 5, physical reads 0,…
Table ‘syspalnames’. Scan count 0, logical reads 2, physical reads 0,…
Table ‘sysschobjs’. Scan count 1, logical reads 4, physical reads 0,…

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 28 ms.

So, 6 + 6 + 5 + 2 + 4 = 23 blocks in total are read. This is considerably smaller than 96,921 blocks with the first query.

43 ms + 28 ms = 61 ms – so this too is also far quicker to run than the first query (226 ms).

Obviously, the query plan is considerably more complex than the ‘select count’ but it if far more resource-efficient:

It’s not that often we need to know the number of rows in a table which is time-critical as normally the result are returned quite quickly and it’s easy to remember the ‘select count’ SQL query.

If resources and/or time is important then you can use the query to collect the data from the statistics but you’ll need to retrieve the query as I know I won’t remember it from memory.

December 4, 2021

Unix Solaris Linux – Directory failing to backup – ‘ls’ error ‘not enough memory’

Unix Solaris Linux – Directory failing to backup
‘ls’ error ‘not enough memory’

Directory Failing to Backup

Currently we use Netbackup to backup our systems a we started to a failure message on one of our Oracle directories on an mature Solaris server.
I remote shelled into the server, changed to the offending directory (/u01/oracle/admin/DIM/adump) and ran ls but it came back with a ‘not enough memory’ error. I should have run the ls command with the -u flag (unsorted) to see how many files existed in the directory but I knew it was a lot.

The adump (audit) directory

I have Burleson Consulting to be a good source of Oracle SQL Server information and on his webpage it give information about the location and the configuration of the adump directory.
Our system has been dumping audit files into this directory at a rate of a few hundred a day.SQL> show parameter audit.

NAME TYPE VALUE
———————– ———– ————–
audit_file_dest string /u01/app/oracle/admin/DIM/audit
audit_sys_operations boolean TRUE
audit_trail string DB

Find – a dangerous command with -exec rm -f {} \;

I knew we had a massive backlog of files which need to be deleted I used the find command to see how many files over ten years existed assuming a year is 365 days. Unix stores three dates for each file, creation, modified and accessed ‘date and time’.
I used the modified date to identify if we had files older than ten years with the shell in the audit directory (I used the full path as I’m paranoid). I also used a filter to only select files ending in ‘aud’ as I was going to use this command to delete files:

find /u01/oracle/admin/DIM/adump/ -name “*.aud” -mtime +3650 -ls

There was quite a few audit files over ten years old so I ran the rm command with the help of find:

find /u01/oracle/admin/DIM/adump/ -name “*.aud” -mtime +3650 -exec -f {} \; -ls

This would find files in the directory ending in ‘aud’ which were older than ten year, delete them and then list the file which was being deleted. Running the command initially without the rm and then adding it and running with a few files at a time as this could create a serious issue if it was let loose deleting files.
I ran it against a year at a time which took several hours per year. There was also the risk of freeing up storage on the disk subsystem which hadn’t been written to in over ten years.
I removed five years of audit files as this would allow the backup to run if we need to and as the system will be upgraded soon I won’t have to worry about the number of files.

About 10 million audit files

I would appear we had about 10 million audit files and we down to about half.

Unix - ls | wc -l - this lists the files and then does a word count.

December 3, 2021

IIS & ColdFusion 2018 configuring mime types & Java Config Part 2 of 2

IIS & ColdFusion 2018 configuring mime types & Java Config Part 2 of 2

Still under construction

Mime Types

After successfully migrating from an ancient version of ColdFusion to a ‘new’ Windows Server 2019 with ColdFusion 2018 the users started having issues.
The problem turned out to be we were missing a ‘msg’ mime type on the production server.

Next day problems

Users started experiencing issues with downloading files from the ColdFusion application. The developer identified we had a missing mime type ‘msg’.
We had to make one change to the IIS server, the ColdFusion application needed the IIS server to manage ‘msg’ mime types or it failed.
The developer asked if I could check the new version of IIS against the old version for any differences in mime types.

Comparing the Mime Type list between the two versions of IIS

Microsoft IIS mime type configuration file is normally located in a file:
C:\System\Windows\System32\inetsrv\Config\applicationHost.config

Notepad++ and it’s ‘Compare’ plugin

There are various applications which will help you compare files, I used notepad++ using the ‘Compare’ plugin as this application is relatively lightweight and easy to install. On the top menu of Notpad++ click on the ‘Plugins’ menu, then select ‘Plugin Admin…’. From there you can click on the ‘Available’ tab, search for the ‘Compare’ plugin then click on the install button. After the installation you list of plugins should look like the image below:

Compare two files

Open both files in Notepad++, with one file ‘active’ on the display go to the ‘Plugins’ menu at the top, use the Compare plugin to set the file to ‘Set as First to Compare’ or press Ctrl+Alt+1. Then make the second file active and using the Compare plugin set it to ‘Compare’ or press Ctrl+Alt+C to compare the files.
Scroll down to the mime types section to compare the types. I found we also had a missing ‘log’ mime so I added to the list but not via this file which would have probably been OK but it wouldn’t be the best way to go.

Comparing two files using Notepad++ and the Compare plugin

I compared the c:\windows\system32\inetsrv\Config\applicationHost.config files and these were the only two differences:

Encapsulation

You should never, never alter the data directly in a persistence store as you don’t know if it is being cached by the application and could get overwritten unless you known it’s OK. This is the first rule of encapsulation and you need to use the Application Programmers Interface (API) to ensure the data is valid and stored correctly.
I used the IIS manager to add the additional mime type so I knew this wouldn’t cause an issue.

Adding in the mime type enabled the users to use the system without any issues. The old server also had a ‘log’ mime type which the ‘new’ server didn’t so I added that as well.
I compared the c:\windows\system32\inetsrv\…:

500 Server Error

We hit another problem, the ColdFusion 2018 server started throwing 500 Server Errors. Restarting the service resolve the issue for a short time. I disabled then re-enabled the anti-virus but that didn’t seem to affect the situation.
The default size of the Java application was 4096 MB so we doubled it to 8192 MB. This stabilized the situation but I wasn’t happy until we could identify why.
Going through the error log it recorded the following error about the time of the 500 server error:

java.lang.OutOfMemoryError: Metaspace

I found a great write up about the issue from Charlie Arehart called Solving metaspace errors, once and for all

I followed Charlie’s instructions and removed the limiting parameter ‘MaxMetaspaceSize’ from the jvm.config file and restarted the server and so far it’s going well. I feel reasonably confident we have solved the issue (thanks Charlie).

The system seems to be running so far…

Next Step

The database connections are to our production SQL Server. We have limited applications, about five, on the server and we are running both production and dev servers on the ColdFusion server and the databases are all on the production SQL Server.
The installer allows both production and dev (without a license) on a server. The dev server allows two external connections and one local connection to the server. I’m going to leave the prod and dev apps on the production ColdFusion server but I will move the dev databases to my dev SQL Server where it should have always been (it was like this when I arrived).
The tables for the application are ‘heaps’ as the developer thinks they are faster so I need to educate him…

ColdFusion 2018 log files

A good place to look for hints if you have a problem or you think the server is running without any issues is to read the log files.
Notepad++ can be configured to ‘tail’ log files so it can be used to tail the log files to give real-time information about what is happening on the server. The server can ‘cache’ data in buffers so the log files can be behind the situation until the buffers have been flushed to the log files so you need to be patient.

IIS & ColdFusion 2018 configuring mime types & Java Config Part 2 of 2

November 24, 2021

SQL Server on VMware 6.7 – configuring controllers & disks

What is the best controller disk configuration to run SQL Server on VMware 6.7?

Preamble

I had to build some SQL Server VMs on VMWare 6.7 hosts. I searched for good articles on VM configurations and I struggled to find enough and they all seemed to contradict each other. The company I work for are not too concerned with purchasing dedicated hardware for our SQL Servers as high performance is not an issue to the business so our SQL Servers run on the hardware which was available at the time. As a business, we are moving towards replacing running systems on bare metal to running on VMWare hosts which enables us to easily manage the hardware and build in resilience. I personally love the snapshot feature so I can alter the system knowing I can quickly restore the system to the snapshot.

Summary

I used the defaults to test my ESXi host and I only tested virtual controller and disk configurations. The answer is question is depends and you need to test but for me using the ParaVirtual controller(s) gave around 50% faster performance of the LSI Logic SAS (LSI) controller. Using a dedicated VMware ParaVirtual controller per drive gave a limited performance increase.

How a SQL Server uses storage

The way a SQL Server using its data files, tempdb and log files are all different. Data files – Theses need to read quickly as the server is waiting for them to be transferred to its internal memory. Data is written back as soon as the server can but at a lower priority as it relies on the log files to check for consistency if the server fails and has to recover. Log files – when the server makes a change to its data it writes to the log file first. Once the change has been written to the log file it is committed and the data is updated to the data file at a convenient time to the Server. TempDB files – the load on these files varies depending on how the system uses the tempdb files. One of the biggest impacts on the TempDB area is if the server is configured for optimistic locking as the version store is within the TempDB area.

My Configuration

Hardware

I have access to an old Dell PowerEdge R820 with Intel(R) Xeon(R) CPU E5-4650 0 @ 2.70GHz. It has one PERC H710P Adapter and the only drives I could find old spinning disks to I created a raid 5 with 900 GB 10 K drives (WD9002BKTG) and a raid 0 with two 300 GB 15 K drives (MK3001GRRB).

Test software (Crystal Disk Mark)

I used open source Crystal DiskMark 8.0.4 x64 to test the performance of the storage using the administrator account with its default settings of 1 GiB. I used DiskMark to test 9 times when I tested each configuration. Only the SEQ1M Q8T1 (Sequential: Block Size=1MiB, Queue=8, Thread=1) and RND4K Q1T1 (Random: Block Size=4KiB, Queue=1, Thread=1) are close to how a SQL Server would use storage to I will be ignoring the other two sets of results.

Virtual Machine

As we use Microsoft Software Assurance to allow us to legally move the VM around our VMWare farm the minimum licencing is 4 cores so set the VM to have 4 cores on one socket with 32 GB of RAM. I created the VM with four storage controllers, the maximum, with one LSI and three ParaVirtual.

Disk Configuration

I added three drives to the LSI controller running on the raid 5 sub system. on one of the ParaVirtual controllers I added three drives from the raid 5 sub system and three drives from the raid 0 sub system. I attached a disk to the two remaining ParaVirtual controllers with a disk from the raid 5 sub system.

Running the tests

The test VM was to the on VM running on the ESXi host.

Single controller to one or more disks

Controller	Number of disks	SEQ1M Q8T1 read (average)	SEQ1M Q8T1 write (average)	RND4K Q32T1 read (average)	RND4K Q32T1 write (average)
LSI	1	1,088.61 MB/s	578.44 MB/s	25.13 MB/s	14.35 MB/
ParaVirtual raid 5	1	1,596.52 MB/s	819.17 MB/s	75.98 MB/s	16.92 MB/
ParaVirtual raid 0	1	513 MB/s	428 MB/s	17.01 MB/s	24.26 MB/
LSI	2	1,122.23 MB/s	853.71MB/s	33.47MB/s	19.97 MB/
ParaVirtual raid 5	2	1,212.72 MB/s	827.30 MB/s	23.59 MB/s	9.06 MB/
ParaVirtual raid 0	2	1067.91 MB/s	525.54 MB/s	55.89 MB/s	41.33MB/
LSI	3	1,145.89 MB/s	1,165.14 MB/s	38.42 MB/s	21.11 MB/
ParaVirtual raid 5	3	1,356.21 MB/s	1,288.38 MB/s	28.82 MB/s	22.20 MB/
ParaVirtual raid 0	3	415.58 MB/s	487.79 MB/s	21.60 MB/s	21.09 MB/

One Controller to one disk

Controller(s)	Number of disks	SEQ1M Q8T1 read (average)	SEQ1M Q8T1 write (average)	RND4K Q32T1 read (average)	RND4K Q32T1 write (average)
LSI	1	1,088.61 MB/s	578.44 MB/s	25.13 MB/s	14.35 MB/
ParaVirtual raid 5	1	1,596.52 MB/s	819.17 MB/s	75.98 MB/s	16.92 MB/
ParaVirtual raid 0	1	513 MB/s	428 MB/s	17.01 MB/s	24.26 MB/
LSI	2	652.45 MB/s	507.10 MB/s	14.23 MB/s	15.81 MB/
ParaVirtual raid 5	2	1,698.28 MB/s	1,151.22 MB/s	41.61 MB/s	20.87 MB/
ParaVirtual raid 0	2	1,607.74 MB/s	631.81 MB/s	55.27 MB/s	30.39 MB/
LSI	3	718.48 MB/s	629.52 MB/s	15.19 MB/s	11.60MB/
ParaVirtual raid 5	3	1,893.33 MB/s	909.48 MB/s	44.46 MB/s	21.62 MB/
ParaVirtual raid 0	3	859.07 MB/s	765.63 MB/s	16.93 MB/s	28.01 MB/

Comparing the data

Using the data from using one ParaVirtual controller with one disk as the baseline to compare the the others then using three PavaVirtual controllers or two each with a disk with give roughly 5% increase in performance than a single PavaVirtual controller with one disk.

Concerns

The results from Crystal Disk Mark seemed to vary could be due to a number of factors, for example there is caching on the disk, controller and operating system which could contribute to variation in the results. This setup could have been typical five years ago but now most systems use Solid State Drives (SSDs) for storage which are very fast in both writing and reading compared to spinning disks.

Conclusion

The broad conclusion is using Crystal Mark Disk the VMware ParaVirtual controllers for your SQL Server storage will delivery better performance over the LSI Logic SAS upto around 50% faster with my configuration. The highest performance was with one to one relationship between the controller and the disk.
The best configuration depends on how complicated you wish to make it. It seems like the data and log files can exist on the same disk as they use the storage differently so with a disk connected a ParaVirtual controller for the data and log files and another ParaVirtual controller for a disk with the TempDB files would give good performance and a simple layout.
If you wanted a more complex solution then probably the VM would need to have four ParaVirtual controllers with the data, log and TempDB files spread over the disks connected to each controller. For example a user database could have four data files on four disks each attached to different controllers and using techniques like partitioning to equally share the data across the disks.
I was surprised at how badly the raid 0 striped 15 K spinning disk performed compared to the 10 K spinning raid 5, I assume due to the high performance of the raid controller the number of disks in the raid was the most important factor.
Running these tests gave an indication of possible configuration but I think have a process to load the SQL Server to test the configuration will be a better solution which I will be investigating.

November 24, 2021

Installing and patching ColdFusion 2018 on Windows Server 2019 Part 1 of 2

Installing and patching ColdFusion 2018
on a Windows Server 2019 Part 1 of 2

Disclaimer: I’m just an end-user of the ColdFusion product and it is about passing on my experience with upgrading to ColdFusion 2018. The applications we use it will run fast and on the whole maintenance-free. We don’t have access to the source code but I have completed an undergraduate course in ColdFusion and I found it OK.

I recently had to update our ColdFusion system. We had bought an upgraded version for our server but due to other pressing issues, we delayed in installing the system. We have upgraded the system but I would like to point out the issues I found.

Preparing the Windows Server 2019 (installing the roles) for ColdFusion 2018

With the help of our support consultant, we installed the Windows Server 2019 roles to reduce problems with the install of ColdFusion.

Installing and patching ColdFusion 2018 on Windows Server 2019 Part 1 of 2

Preparing the Windows Server 2019 (installing the features) for ColdFusion 2018

The Features also had to be installed…we use SNMP for our monitoring software. There are two images below to cover the list…

ColdFusion 2018 Installation

The installation process appeared to worked fine (I just went with the defaults and we used our IIS server on the Windows Server 2019) but there is no automatic patching to the current level during the install. You can tick a box to check for updates when you access the admin pages post install.
Our developer found that the earlier version of ColdFusion used a Veritas search engine but the 2018 version uses Solr so he had to make some changes to his code.

ColdFusion 2018 Patching/updating

This is the bummer part… patching. The install was upto update 2 so when you go to the admin console and look to see which patches to install you get the display below:

The issue is that I couldn’t get to the download and install button. I tried chrome, edge and IE. The only solution I could find was to manually install the updates to update 6.

Download the updates from the Adobe “ColdFusion (2018 release) updates” site (they will be tar files).

Rename the tar files to jar and then run the command below in an administrator shell (right-click on the cmd or PowerShell icon and run as admin) which to update.
I found unless I ran it as an admin it didn’t install cleanly.
hotfix-003 is update 3,hotfix-004 is update 4 etc..
Using a PowerShell I changed to the directory with the renamed hotfix files and I ran the following commands:

PS C:\ColdFusion2018\cfusion\hf-updates> C:\ColdFusion2018\jre\bin\java -jar hotfix-003-314033.jar

PS C:\ColdFusion2018\cfusion\hf-updates> C:\ColdFusion2018\jre\bin\java -jar .\hotfix-004-314546.jar

PS C:\ColdFusion2018\cfusion\hf-updates> C:\ColdFusion2018\jre\bin\java -jar .\hotfix-005-315699.jar

PS C:\ColdFusion2018\cfusion\hf-updates> C:\ColdFusion2018\jre\bin\java -jar .\hotfix-006-316308.jar

PS C:\ColdFusion2018\cfusion\hf-updates> C:\ColdFusion2018\jre\bin\java -jar .\hotfix-007-316715.jar

Once you get to about patch level 6 there is enough space on the web interface to allow you to download and install them.

Problems at update 7

After update 7 ColdFusion didn’t seem to work anymore. I had to use the Adobe ColdFusion Web Server Configuration application to either add or update the IIS server before it would work. The solution was to either add (if the IIS server wasn’t there) or upgrade the IIS server; after this the integration with IIS and ColdFusion was restored.

Running in Production

All seemed to be good until we switched the DNS servers to point to the new server. With the support of the application developer I made two changes to get the server to work, altering the bindings and adding a virtual directory. I think the binding probably was the issue it we had to get the system running.

Site Bindings
I checked each site binding to ensure it had the IP address of the ColdFusion server.

Virtual Directory
I added a virtual director ‘/jakarta’ to each site.

Up and running
It would seem after these changes the system was running without any issues until the next day when we had issues with mime types (see part 2)

IT

The scenario

Testing the test databases

Testing the training databases

Going live

Checking the database

SQL Server accounts

Fixing internal accounts to work with migrated databases

dbatools.io

Maximo tables by default become fragmented as they are heaps

IBM’s Maximo User Performance

Basics

Key lookup

RID lookup

Intro

Configuration

Heaps, implicit transactions & Indexes

Doclinks

RoboCopy

Problem domain

Task Scheduler

Problems

Solution

Task Scheduler - General

Task Scheduler - Tridggers

Task Scheduler - Actions

Task Scheduler - Conditions

Task Scheduler - Settings

Task Scheduler History

Task Scheduler – Task

Log file

Github

Calendar & Audit tables

Calendar table

Calendar table

Step one – collecting on the need data to a smaller table

Step Two – developing the queries and using the calendar table

Query output

Statistics

Conclusion

SQL ServerCounting the rows in a table

Unix Solaris Linux – Directory failing to backup‘ls’ error ‘not enough memory’

Directory Failing to Backup

The adump (audit) directory

Find – a dangerous command with -exec rm -f {} \;

About 10 million audit files

IIS & ColdFusion 2018 configuring mime types & Java Config Part 2 of 2

Still under constructionMime Types

Next day problems

Comparing the Mime Type list between the two versions of IIS

Notepad++ and it’s ‘Compare’ plugin

Compare two files

Encapsulation

500 Server Error

ColdFusion 2018 log files

What is the best controller disk configuration to run SQL Server on VMware 6.7?

Preamble

Summary

How a SQL Server uses storage

My Configuration

Hardware

Test software (Crystal Disk Mark)

Virtual Machine

Disk Configuration

Running the tests

Single controller to one or more disks

One Controller to one disk

Comparing the data

Concerns

Conclusion

Preparing the Windows Server 2019 (installing the roles) for ColdFusion 2018

Preparing the Windows Server 2019 (installing the features) for ColdFusion 2018

ColdFusion 2018 Installation

ColdFusion 2018 Patching/updating

Problems at update 7

Running in Production

SQL Server
Counting the rows in a table

Unix Solaris Linux – Directory failing to backup
‘ls’ error ‘not enough memory’

Still under construction

Mime Types