DBA Questions

These questions are relevant your first day on the job as Database Administrator. Also the questions are usefull to look at on a regular basis like a couple of times every year.

Q1: Where are the backups?

This is all types of backup files; full, differential and log backup files. And on all databases.

Also the backup files on the Service Master Key (SMK) and the Database Master Keys (DMK) on the system database [msdb] and the user databases with encrypted columns. This might require some investigations on encrypted columns.

This is most likely the most important question the first day.

Q2: How are the backups created?

Get concrete quickly and ask about tools and schedules. Get to know all the tools all the way to the last storage area. If you want to be a little more confrontative you could ask for the definitions of the processes to handle SQL Server recovery. I would wait a couple of weeke at least and prepare the formulation.

If the backups are created by SQL Server Maintenance Plans you should consider that as an anti-pattern and be cautious.

Q3: How are the backups used?

At first you should focus on database restore. Ask for a recovery plan and restore guides. The restore guides are the most important.

Be aware of the tools used for restore. Are they general available or are you to install them by your self? In what context are the tools to be used? Is a special user or other access required? Is a priviledged access device required?

Then you can open up and ask for a report from the last recovery test. Also you can ask about your new collegues personal experiences with recovery. Both in general and in the actual organisation.

Please remember that it is not you but the SQL Server service accounts that require access to the backup files.

Q4: How do I get administrative access to the SQL Server installations?

Do I have to apply for AD groups? Or apply for an administrative account? Or a combination? Is the access permanent or only by request? This is also you first peek into the security structure build around the SQL Server installations. The approach to SQL Server security is usually unique to organizations and people. A lot of people say that there is a strick role-based (RBAC) security model, but usually the reality is way different and with several variations.

Q5: What are the three most important systems using SQL Server?

Limit the question to three system. This you can look into the first day. Do the rest later.

Ask about a inventory on these systems. What databases, servers, contact persons etcetera.

Q6: What is your phone number?

Ask this question to your new collegues, Incident Management, line manager and the IT manager. Enter the numbers in your personal caontact list. Decide by yourself who will have your private phone number. Personally I always have two phones, one job and one private. Also I am rather picky with who have my private number. Especially if I am not paid to take calls outside office hours.

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.

Use schema name

... on used tables. Put procedure in custom schema without data.

Use tables

... not views, other procedures and triggers. And only functions with careful big-O considerations

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

... for dynamic SQL. Not EXECUTE. In general you should avoid dynamic SQL.

Use specific columns

... only take data that are required.

Use order of columns

... as given in the tables. One table a time.

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.

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.

Check values

... before usage. Especially parameter values!

Use effective types

... and avoid custom types.

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.

Use EXISTS

... not COUNT.

Use effective error handling

... by testing before exception handling with TRY - CATCH. Consider exception handling as last-line-of-defence after tests and checks.

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.

Set security explicit

... on procedure through custom database roles on Active Directory groups.

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

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.