SQL Server Workstation

Some notes from a SQL Server 2022 Developer Edition installation on a Windows 11 Pro workstation. The infrastructure is VMware Workstation Pro.

General Preperations

If you clone an exiting VM then you should rename the new SQL Server workstation to something different. And do it before anything else.

As I usually has a sandbox for different versions of SQL Server the name could something like "MSSQL 2022 Workstation". Consider if you should differ between sandbox, workstation, devbox or anything else.

Make sure the Windows installation is up to date.

PowerShell

Install PowerShell and then Windows Terminal from Microsoft Store.

Open Windows Terminal and pin it to the task bar. Then close Windows Terminal.

PS > Install-Module -Name SqlServer
PS > Install-Module -Name dbatools

Storage

Move CD-ROM to Z–drive.

Drive	Name	Size	NTFS	Path	Comments
C	System	64 GB	default		Windows and tools (SSMS)
P	Program	64 GB	default		SQL Server
D	Data	64 GB	64 KB		User database (mdf & ndf)
L	Translog	32 GB	64 KB		User db & tempdb (ldf)
T	tempdb	32 GB	64 KB		tempdb (mdf & ndf)
X	Backup	128 GB	64 KB		Full, diff & log backup

1. Create one disk at a time: One-Piece Flow (Lean)- Must be done manually in VMware Workstation. Can be scripted in vCenter with PowerCLI.
2. In VMware Workstation NVMe can only be added offline.
3. Split virtual disk into multiple files.
4. Do not allocate all space now.
5. „MSSQL Workstation_<Drive>“
6. All partitions are GPT.
7. Only use Simple Volume.
8. Quick format.

SQL Server Installation

1. Connect VM CD-drive to SQL Server ISO-file
2. Run SETUP.EXE
3. Select „SQL Server Developer Edition“
4. Select "Database Engine Services" and "Full-Text…"
5. Place "Instance Root Directory" on another physical drive than Windows, e.g. "P:\Program_Files\Microsoft_SQL_Server\". This is to spread preassure on (local) storage units.
6. Name the (Database Engine) instance, e.g. „SSDB00“. This will allow side-by-side installation of different SQL Server versions. On the other hand I usually does not recommend instanse name with version information. The instance name in shared installations should be generic – like a dumb database key.
7. Change Service Startup Type to "Manual". When running several instances this will help you control local resources on your workstation. With virtual workstations I usually create one VM to each instance.
8. Set „Grant Perform Volume Maintenance Task priviledge to SQL Server Database Engine Service“. This is to speed up database file (auto-)growths which might be when expanding the physical data model. This is really relevant on a workstation where you experiment. On the other hand I would never use Auto Shrink (AUTO_SHRINK) even in a sandbox.
9. Collation tab: Use collation "Latin_General_100 AS KS WS" maybe with UTF-8.
10. Add Current User as SQL Server administrator. Adding (Local) Administrators is not enough.
11. Set Directories. Define seperate paths for
• Data
• TransactionLog
• Backup
• tempdb
  where [tempdb] transaction log is with the other user database transaction logs.
12. Set tempdb data and log
13. Set MAXDOP to 4
14. Set memory to Recommended
15. Run installation
16. Close SQL Server Installation
17. Disconnect CD-drive
18. Restart VM

SQL Server Update

1. Copy latest CU to VM. I use „C:\temp\“
2. Start PowerShell as Administrator. That is Microsoft PowerShell (v7) not Windows PowerShell (v5)
3. Change directory to „C:\temp\“
4. Run the SQL Server CU exe-file
5. Complete SQL Server CU installation
6. Delete SQL Server CU exe-file from „C:\temp\“
7. Restart VM

SSMS Installation

1. Copy latest SSMS installation (SSMS-Setup-ENU.exe) to „C:\temp\“
2. Start PowerShell as Administrator
3. Change directory to „C:\temp\“
4. Run SSMS Setup exe-file
5. Complete SSMS installation
6. Delete SSMS Setup exe-file from „C:\temp\“
7. Start SSMS
8. Cancel suggested connection
9. Pin SSMS to taskbar
10. View Registered Servers (Ctrl + Alt + G)
11. Select properties on SSDB00
• Server name: (local)\SSDB00
• Trust server certificate
• Registered server name: SSDB00
• Connection Properties tab…
• Connect to database: tempdb
• Connection time-out: 5 seconds
• Use custom color: green
• Save
12. View Object Explorer Details (F7)

ODBC

If SQL Server ODBC 17 is already installed, it will most likely generate a error like "The feature you are trying to use is on a network resource that is unavailable.".
The solution is to uninstall SQL Server ODBC 17. This can be done by Control Panel > Remove Program.

SSDB Configuration

Query: ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT;

Query:

EXECUTE master.sys.sp_configure
	@configname = N'remote access',
	@configvalue = N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO

Query:

EXECUTE master.dbo.xp_create_subdir N'X:\MSSQL\Backup';

Query:

EXECUTE master.sys.sp_configure
	@configname = N'backup compression default',
	@configvalue = N'1';
GO
EXECUTE master.sys.sp_configure
	@configname = N'backup checksum default',
	@configvalue = N'1';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.dbo.xp_instance_regwrite
	@rootkey = N'HKEY_LOCAL_MACHINE',
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
	@value_name = N'BackupDirectory',
	@type = N'REG_SZ',
	@value = N'X:\MSSQL\Backup';
GO

Query:

EXECUTE master.sys.sp_configure
	@configname = N'show advanced options',
	@configvalue = N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.sys.sp_configure
	@configname = N'cost threshold for parallelism',
	@configvalue = N'50';
EXECUTE master.sys.sp_configure
	@configname = N'backup compression default',
	@configvalue = N'1';
EXECUTE master.sys.sp_configure
	@configname = N'backup checksum default',
	@configvalue = N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.sys.sp_configure
	@configname = N'show advanced options',
	@configvalue = N'0';
RECONFIGURE WITH OVERRIDE;
GO

AdventureWorks Installation

See SQLAdmin post on Restore AdventureWorks on SQL Server 2019.

;

Plan Explorer Installation

1. Download Plan Explorer from www.solarwinds.com/free-tools/plan-explorer. This requires registration with e-mail and phone#.
2. Start Plan Explorer
3. Pin to Start

History

2024-10-23 – Details added.
2024-09-22 – Element on administrators added. And a few typhographic corrections.
2024-09-09 – Post created.

 

File as parameter to PowerShell function

 Quite often I find it usefull to have a file as parameter to a PowerShell function. And most times I want to make sure the file exists when the function is called. In a PowerShell advanced function this can be done quick and short by a validation. More precisely by a ValidationScript attribute to the parameter.

A example on a function with a file as parameter with validation on existance could be something like

function Write-LogFile {
    <#
    .DESCRIPTION
    #>
    [CmdletBinding()]
    [OutputType([void])]
    Param(
        [Parameter(Mandatory=$true, ValueFromPipeLine=$true, HelpMessage='...')]
        [ValidateScript({$_.Exists})]
        [System.IO.FileInfo]$LogFile,

Running a call to the function with a file object that does not exist

[System.IO.FileInfo]$theLogFile = "$PSScriptRoot\log.txt"
Write-LogFile -LogFile $theLogFile

… will generate an error like

PS C:\Scripts> .\FileParameter.ps1
Write-LogFile: C:\Scripts\FileParameter.ps1:26
Line |
  26 |  Write-LogFile -LogFile $theLogFile …
     |                         ~~~~~~~~~~~
     | Cannot validate argument on parameter 'LogFile'. The "$_.Exists" validation script for the argument with value
     | "C:\Scripts\log.txt" did not return a result of True. Determine why
     | the validation script failed, and then try the command again.

There are many other ways to validate if a file exists, but I like to use a FileInfo object as it give the functionality without much custom code.

Stored Procedure Guidelines (T-SQL)

Here are some personal guidelines on writing and using a stored procedure. They are mentioned in a somewhat random order. The list and each recommendation will most likely change over time with new experinces, feedback and other new knowledge.

SET NOCOUNT ON

… to avoid unnecessary activity and communication on row count.

SET XACT_ABORT ON

… to roll back most transactions on error. See details from Sommarskog (link) and Swart (link).

SET QUOTED_IDENTIFIER ON

… to make the code a little more robust and strict. Also some protection against injection. Still I would prefer to enclose object and other identifiers in square brackets. This I think makes the code more strict and easy to maintain with several developers and teams involved.

Use schema name

... on used tables. Put procedure in custom schema without data. Do not use the schemas dbo or sys as they are not in your control.

Use tables

... not functional objects such as views, other procedures and triggers. And only use functions in stored procedure with careful big-O considerations. This could only be worse with the use of nested views, procedures, functions, triggers and other functional objects.

Use sets

... not cursors or sub-selects as SQL Server is set-based. Cursors are fine when on DB2 or Oracle. Sub-selects are often seen on SAS.

Use functional name

... not sp_ or other Hungarian naming. It doesn´t really matter what type the object is.

Use sp_executesql

hellip; for dynamic SQL. Not EXECUTE. In general you should avoid dynamic SQL. This includes prepared statements.

Use specific columns

... only take data that are required. Network traffic is then reduced to the required amount.

Use order of columns

... as given in the tables. One table a time. Re-arranging the columns could add significant resource consumption on each call.

Use keys

... and indexes with care. Use index on foreign keys. Check for covering index. Index usage might change with change of data in used tables.

Use table variables

... not temporary tables. This will keeps activity in buffer and away from storage,

Be direct

... and avoid temporary cached or staged data. Do not use dirty reads!

Be short

... by short transactions. Avoid costly isolation levels such as Repeatble Read. Also you can save some network traffic on often used stored procedures by short names on the procedure and the parameters.

Use effective parameters

... and avoid parameter sniffing. Focus on parameters where the values change a lot. Convert these parameters to local variables when suitable.

Check values

... before usage. Especially parameter values! Do the value check inside the procedure before usage.

Use effective types

... and avoid custom types. Custom types will add resource consumption on resources. And in some situation increase blockings on metadata.

Use effective operators

... avoid costly operators such as LIKE. Be carefull with COALESCE, string operations and aggregated values. T-SQL is for finding and sorting data. Use other languages for complex calculations and text processing.

Use explicit type cast

... not indirect or implicit. And you are still somewhat in control.

Use EXISTS

... not COUNT. As this is more effective with T-SQL.

Use effective error handling

... by testing before exception handling with TRY - CATCH. Consider exception handling as last-line-of-defence after tests and checks. Even when using exception handling this should be kept inseide the procedure. Throwing a exception outside the procedure could be rather costly on the resource consumption.

Use MS_Description

... extended property. This helps development with Visual Studio, see SQLAdmin on Descriptions.

Document

... procerdure, each parameter and output/return value in code, detailed and with examples. Also when you are the one and only developer and user.

Set security explicit

... on procedure through custom database roles on Active Directory groups. Using default database roles is outside your control.

Use ALTER PROCEDURE

... not DROP - CREATE PROCEDURE. Security and other external configurations will be lost on each change.

Monitor

... usage and performance with DMVs, XEvents etc. See Monitoring Performance of Natively Compiled Stored Procedures.

Call procedure effective

... with CommandType.StoredProcedure and SqlParameter objects on the SqlCommandobject. Use careful crafted connection string with secure authentication (SSPI) like Kerberos.

Reference

Kimberly Tripp, SQLSkills: Building High Performance Stored Procedures.

 

Top 10 stored procedure performance tuning tips in SQL server

History

2026-01-02 : Section on XACT_ABORT added. 2024-07-03 : Post published.

Buffer Pool scan

In a SQL Errorlog we saw a lot of events like this.

Buffer Pool scan took 10 seconds: database ID 6, command 'CHECKPOINT', operation 'FlushCache', scanned buffers 341183509, total iterated buffers 345933594, wait time 75 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information.

The refered article "Operations that trigger a buffer pool scan may run slowly on large-memory computers" states that

Prior to SQL Server 2022, there was no way to eliminate this problem.

But then another event came around where we looked at Indirect Checkpoint configuration. It turned out that the database originally was so old that the "Target Recovery Time" was 0 (zero).

When we changed this configuration to 60 then the Buffer Pool scan messages no longer showed up in the SQL Errorlog.

Another benefit was that spikes and average on Disk Write Latency were reduced significantly.

Kendra Little wrote some time ago a useful article (link) with good references and details when Target Recovery Interval changed default value.

Disassembling a hard disk

Hard disks with spinning platters are going away very fast. During easter cleanup in my workshop I found a old 750 GB hard disk, and decided not only to take it to the local recycling station but to disassemple it to see how it it constructed. During the disassembly I took some photos with my phone - they are not near professional quality but they are still usefull with the comments.

The drive is a Seagate Barracuda ES.2 (data sheet) with a SATA 3Gb/s interface. It runs with 7200 RPM which made it rater usefull when it was released. I stopped using the drive when I really swithched to SSD in my workstation. There is nothing wrong with the drive - it just went obsolete to me.

To remove the cover there are at first six T9 screws. But hidden behind the sticker there are three more screws, also T9.

With the cover removed there is direct access to the inner components. Also this is the point of no return to use the drive again as the disk is now filled with dirt from the normal atmosphere.

On the backside of the hard disk the circut board is placed. It is fastned with T6 screws but none hidden. The connection from the board to the inner drive is through some contacts. The picture below shows two sets of contacts with one set of three contacts for the electrical engine spinning the platters and another set of four by five contacts for the head.

A closer look at the board shows chips from Seagate and Samsung. The external connections are direct from the board to the physical interfaces.

Under the board and a metallic sticker there is another hidden T9 screw. This holds the head.

The connection for the head is on a small board. But is quite easy pulled out and disconnected from the head.

The head unit movements are controlled with a coil on the back of the arms and two powerfull static magnets. The top magnet is only held in place by the magnetic force and can be removed with a small screwdriver.

The arm with heads and coil is one unit together with a small board. This head unit is removed without any tools.

A closer look at the head unit from another angle show a small board and the arms with the physical heads to transport electro-magnetic signals to and from the platters.

Going from buttom to top on the picture below it looks like the buttom arm only hold one head to communicate with the lower side of the lowest platter.

The next two arms holds two heads each to communicate with the upper side of the platter below the arm and the lower side of another platter placed above the arm.

The fourth arm hold only one head to communicate with the upper side of the top platter.

There is a fifth arm but this hold no heads and is not connected on the board.

I guess that the head unit was designed and partly constructed for up to four platters where this hard-drive only has three platters as shown later.

The bottom magnet that was under the head unit is also quite easy removed without any tools. I guess that the small plastic spring to the right on the picture below is some sort of anti-vibration or shock-absorbtion.

The spindle holding the platters on the axle is removed with six T9 screws.

The first platter is removed without tools. Between the platters there is a open ring of metal fastned to the chassis. I guess this is to stabilize the platters when they are spinning. Also there is a inne ring to give space for the heads.

After all three platters are removed the chassis looks quite empty. I have tried to lay out the components to show the stack from buttom to top. Between the platters there is a inner ring and a open outer ring.

The outher part of the electric motor spinning the platters is removed with brute force as indicated by the tools on the picture below. The outer part is the static magnetics where the inner part is the coils. How the outher part is fastned I can't see. There are no obvious traces of glue.

The three-pin connector for the motor on the back of the chassis is glued on but easy removed with a small screwdriver.

The inner part of the motor with the coils is removed with brute force and another set of tools.

All in all I count about 55 major components in the hard drive. In that there is about 31 screws.

Looking at modern storage there has certainly been some major changes. Compared to a NVMe storage unit with only one major components in a single board and no mechanics the technological span is amazing. But also compared to an elder SAS SSD drive typically with one board, around three screws and two metal half-shells the technological evolution is significant.

In general and looking back the flash storage was a paradigm shift where all our notes on spindles, RPMs and configuration tricks like short-strike suddenly were to no use. With NVMe many rules of thumb were obsolete. As PCIe (PCI-SIG) evolve we will see new fantastic features also on NVMe and CXL. But the next paradigm shift does not come from evole the current mainstream technology but when a completely new technology matures and push to the side the mainstream tech.