SQL Server

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.

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.

SQL Server

SQL Server migrating DB from 2008 to 2017

The scenario

Testing the test databases

Testing the training databases

Going live

Checking the database

SQL Server account migrations

SQL Server accounts

Fixing internal accounts to work with migrated databases

dbatools.io

IBM’s Maximo SQL Server Table (Heap) Fragmentation

Maximo tables by default become fragmented as they are heaps

IBM’s Maximo User Performance

Basics

Key lookup

RID lookup

Maximo – Configuring RoboCopy to duplicate DocLinks to a second server

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

SQL Server – Using The Calendar Table

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 Server – Counting the rows in a table

SQL ServerCounting the rows in a table

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

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

SQL Server
Counting the rows in a table