Consultancy - Freelance - Contract - Permanent
Knowledge Base
Here are a number of links that as a DBA I have found useful over the years. Reproduced here with thanks to all of the originators.
“i/o is frozen on database” and “i/o was resumed on database”17 questions every sql dba should ask before supporting a new third-party database
3 ways to run dbcc checkdb faster
a closer look at cxpacket wait type in sql server
a method to find trace flags
a self-tuning fill factor technique for sql server
a toolbox every dba should have (dbatools link in code section)
accelerated database recovery in sql server 2019
accelerating database recovery with sql server 2019
adaptive query processing in sql server 2017
add a linear trendline to a graph in sql server reporting services
add a sql server database to an existing always on availability group
add sql server agent management to sql operations studio
adding charts and interactive sort buttons to ssrs reports
adding user comments to reports in sql server reporting services
altering datatypes with (almost) no downtime
always on - endpoint ownership
an alternative to data masking
an introduction to gaps and islands analysis
an overview of string_split()
analyze sql server results in excel with conditional formatting
analyzing formula 1 results in r
application down after sql service logon account change
at time zone: the easy way to deal with time zones and daylight savings time in sql 2016
automate refresh of a sql server database that is part of an availability group
automate refresh of a sql server database that is part of an availability group
automatically failover (enable or disable) jobs based on hadr role
availability group readable secondaries – just say no
availability groups with docker containers
availability groups: when your synchronous secondary isn’t synchronous
avoid order by in sql server views
avoid using nolock on sql server update and delete statement
avoid using nolock on sql server update and delete statements
back up sql server 43%-67% faster by writing to multiple files
back up transaction logs every minute. yes, really.
backdoor to elevate sql server security privileges
backwards compatibility in sql server data tools for integration services
backwards compatibility in sql server data tools for integration services
be careful with key order in sql server missing index recommendations
beginner guide to in-memory optimized tables in sql server
benefits of schemabinding in sql server
benefits of using sql server temporal tables
better sql server agent job failure monitoring
beware! of unintended changes when altering columns
blitz results: page verification not optimal
build and automate a sql server status report on a schedule / server restart
building sql server indexes in ascending vs descending order
calculate database backup compression ratio
calculate moving averages using t-sql in sql server
can select * make a query go…faster?!?
can you use nvarchar as a universal parameter? almost
capture and store sql server database integrity history using dbcc checkdb
capture executions of stored procedures in sql server
change not for replication value for sql server identity columns
changing schema owner removes all direct permissions of objects owned by the schema
changing sql server collation after installation
changing sql server collation after installation
changing the collation of the instance, databases, and columns in all user databases
check current patch levels for all sql servers in environment
checking availability groups with dbachecks
cleanly uninstalling stubborn sql server components
cleanly uninstalling stubborn sql server components
cleanup sql server query store data after database restore
clone a sql server database using dbcc clonedatabase
computed columns: reversing data for easier searching
concurrency week: can selects win deadlocks?
conditional formatting with ssrs
conditionally returning rows based on query results
configure a dedicated network adapter for availability groups data replication traffic
configure log shipping for sql server on linux
configure read only routing
configure replication between an on-premises sql server and aws rds
connecting to integration services access is denied in sql server 2016 or 2017
connection timeouts in multi-subnet availability group
convert ms access to sql server with always encrypted database
cool query hints
correct sql server tempdb spills in query plans caused by outdated statistics
covering indexes: not just for select but also for update statements
create a process dump of sql server using sqldumper.exe tool
creating a multi-option parameter report for ssrs
creating a multi-server query ssrs report using central management servers
creating a sql server availability group dashboard for all servers
creating a sql server build and patch release table
creating compound interest calculations in sql
creating sql agent jobs to run powershell
cross database queries in azure sql database
cross tab report in ssrs
crosstab queries using pivot in sql server
database snapshot use case: service migration
datatypes and the default mask with dynamic data masking
date and time conversions using sql server
dba team mashup
dbcc shrinkfile with emptyfile to migrate from filegroups
delete duplicate rows with no primary key on a sql server table
deploy database objects to multiple sql server instances using powershell
deprecated and discontinued features in sql server
determine database version from a bak file
difference between sql server unique indexes and unique constraints
different approaches to correct sql server parameter sniffing
different ways to execute a sql server ssis package
different ways to flush or clear sql server cache
disk encryption and the impact to dba's
displaying hierarchical data using a cte
do not set trustworthy on for the ssisdb catalog
dodging deadlocks with indexes
does nolock really apply no locks?
dynamic quorum for windows server 2012 r2 cluster to support sql server alwayson
enable data compression on a table or index
enable encryption for microsoft sql server connections
ensure that ssrs is working while using availability groups
error 9002: the transaction log for database [dbname] is full due to 'replication'
execute a command in the context of each database in sql server
execute an ssrs report from integration services package
execute ssis package from powershell
export sql server tables to excel
file maintenance – cleaning up old files
fill in missing dates for sql server query output
filling in missing values using the t-sql window frame (data smears)
filtered indexes and forced parameterization
filtered indexes and is_parameterization_forced
filtered indexes in sql server
find all stopped sql server services for all instances
find all stopped sql server services for all instances
find and remove duplicate rows from a sql server table
find max value from multiple columns in a sql server table
find tables, columns or stored procedures used in a sql server reporting services report
finding encrypted data in a sql server database
finding single use plans in the plan cache
fix sql server log shipping after a new database file has been added
fixing error 19405 when configuring sql server availability groups
fixing nested functions
fixing system database corruption with setup.exe
format sql server dates with format function
gap and islands analysis using algebra
generate html formatted email of sql server database consistency check errors
generate qr code® barcodes in an ssrs report with the qrcoder library
get sql server row count and space used with the sp_rows
getting database size from backup file metadata
getting started with power bi
getting sysadmin access to sql server when locked out
going serverless with azure sql database
grant sp_who2, & sp_who3 execution without granting sysadmin access
granting view definition permission to a user or role in sql server
how check constraints
how do i know if my query is good enough for production?
how do i know if my query is good enough for production?
how does accelerated database recovery work?
how is a t-sql trigger that only fires on real changes made?
how scalar user-defined functions slow down queries
how to add reporting services to an existing sql server clustered instance
how to attach a sql server database without a transaction log and with open transactions
how to build your first power bi dashboard
how to change the file extension for a data file
how to check performance on a new sql server
how to create a table with a partitioned clustered columnstore index
how to delete just some rows from a really big table using indexed views
how to delete top (x) with an order by
how to deploy and execute an ssis package from the ssisdb catalog
How to determine SQL Server database transaction log usage
how to enable ssl encryption for an instance of sql server
how to encrypt and restore your sql server database backups
how to execute a powershell script from an ssis package
how to execute a powershell script via ssis
how to execute ssis packages from sql server agent
how to find data differences between two tables in sql server with ssis
how to find out whose queries are using the most cpu
how to fix orphaned users easily
how to fix sql server uninstallation error could not open key
how to fix the error “string or binary data would be truncated”
how to insert rows and get their identity values with the output clause
how to join to the same table multiple times for a sql server query
how to linux for sql server dbas
how to maintain state in a sql server agent job
how to make sql server wildcard searches faster
how to migrate a sql server database to a lower version
how to move a windows server cluster from one domain to another
how to perform an online page level restore in sql server
how to read log file in sql server using tsql
how to recover a suspect msdb database in sql server
how to recover data from a sql server temporal table
how to remove times from dates in sql server
how to restore a sql server database to another server after disabling tde
how to restore model database in sql server
how to run your cte just once and re-use the output
how to set & get the next id without serializable isolation
how to think like the engine: when a seek isn’t
how to track progress of create index command?
how to troubleshoot someone else’s temp table contents
how to turn off telemetry for sql 2016
how to use crystaldiskmark 7 to test your sql server’s storage
i most certainly do have a join predicate
i thought my database was collation agnostic?
identify sql server databases that are no longer in use
identify system object differences between sql server versions
if you have foreign keys, don’t update fields that aren’t changing
implementation of sliding window partitioning in sql server to purge data
implementing date calculations in sql using time periods
implementing luhn's algorithm in t-sql to validate credit card numbers
implementing stretch database
install ssrs reportserver databases on azure sql managed instance
installing extensions in sql operations studio
installing sql server integration services
installing sql server vnext ctp1 on red hat linux 7.2
introducing sql server ssrs in windows containers
introduction to sql server temporal tables
introduction to sql server temporal tables
isolation levels in sql server
liberating the dba from sql authentication with ad groups
login script generation for migration or dr
make sql server agent jobs hadr aware
manage historical data in temporal tables with retention policy
maxdop isn’t really maxdop. it’s more like dop
measure delayed durability impact in sql server 2016 and later
measuring availability group synchronization lag
memory optimizer advisor
memory-optimized tempdb metadata in sql server 2019
methods to collect sql server stored procedure execution history
migrate a sql server fci while using the same virtual network name & ip address
migrate an oracle database to sql server using sql server migration assistant for oracle
migrate ssrs 2017 to power bi report server (i.e. don't0
migrating sql server reporting services
minimum permissions for sql server truncate table
misaligned log ios which required falling back to synchronous io
misconceptions in sql server: regarding table variables
monitor performance counters using powershell in sql server agent
monitor sql server alwayson availability groups
monitoring performance by using the query store
move sql reporting services to another server
mssql-cli interactive command line tool for sql server
mssql-scripter tool and examples to generate scripts for sql server objects
navigating dbcc checkdb for vldb
network issue or threadpool waits?
never create columns with ansi_padding set to off
new built-in function concat_ws() in sql server 2017
new resumable online index create sql server 2019
nonclustered index structure
null complexities
obsoleting unused ssrs reports
one million tables
optimize large sql server insert, update and delete processes by using batches
options to improve sql server bulk load performance
parallel execution of tasks in ssis
paul white explains temp table caching 3 ways
percentile_cont : the "median" function in sql server
performance comparison between sql server 2014 and 2016 (batch mode)
performance tuning using extended events (stairway)
please stop using this upsert anti-pattern
populate a sql server column with a sequential number not using an identity
posting sql server notifications to slack
posting sql server notifications to slack
powershell get cpu and cores for sql server licensing
preemptive waits always show running state
print.. the disruptor of batch deletes in sql
query store, plan forcing, and drop/create
queue table issues with availability groups in sql server
quick sql server health check report using ps/sql scripts
quickstart: extended events in sql server
read_committed_snapshot db option and read commited transaction isolation level
reading data from an excel file starting from the nth row with ssis
recover access to a sql server instance
recover deleted sql server data and tables with the help of transaction log and lsns
recover sql server resource database (mssqlsystemresource)
recovering databases from a master backup
recursive cte calculations
removing ad hoc plans from query store
rename logical database file name for a sql server database
renaming a schema in sql server (via create/move)
replacing model sql server database with a user database
restore a database with cdc enabled
restore sql server master database options
restoring the master database even without a backup
resumable online index create in sql server 2019
retrieve the column causing an error in sql server integration services
return sql server connections information using sys.dm_exec_connections
returning sql server stored procedure parameter values to a calling stored procedure
reverse engineer sql server databases with visual studio
run all sql scripts in a folder (with logging)
scalar functions can cause multi-table blocking
scale sql server bulk loading on a budget
scheduling a powershell script using sql server agent
script for sql server agent job issues across all instances
script to create dynamic pivot queries in sql server
scripting all objects from ssms in to a vcs
scripting environments in ssis (solution)
scripting objects with powershell
searching and finding a string value in all columns in a sql server table
securing data in sql server
service packs & cu's look at logical name for master (not database id)..
set implicit_transactions on is one hell of a bad idea
setting up transactional replication
setup and implement sql server 2016 always on distributed availability groups
setup environment variables in sql server integration services
setup environment variables in sql server integration services
setup sql server database mail to use a gmail, hotmail, or outlook account
shutdown sql server
side by side sql server upgrade with log shipping
significant sql server 2019 licensing changes for ha/dr
simple image import and export using t-sql for sql server
simple way to find errors in sql server error log
solve old problems with sql server’s new string_agg and string_split functions
solving the target principal name is incorrect. cannot generate sspi context
sp_whoisactive monitoring sql server estate centrally
specify replication synchronization schedules
sql 2017 adaptive query processing – batch mode memory grant feedback
sql agent job dependencies with table driven solution
sql convert date to yyyymmdd
sql server 2016 always encrypted
sql server 2016 availability group automatic seeding with ssms 17.x
sql server 2016 string_split function
sql server 2017 and python basics
sql server 2017 and python basics
sql server 2017 cu3 adds cxconsumer waits
sql server 2017 cu3 adds tempdb spill diagnostics in dmvs and extended events
sql server 2017 identity_cache feature
sql server 2019 installation enhancements for maxdop and max memory
sql server agent service won't start due to access denied
sql server automatic tuning in the real-world
sql server backup interview questions
sql server clustered index fragmentation on guid columns
sql server cross apply and outer apply
sql server cross apply and outer apply
sql server cross apply and outer apply
sql server cte vs temp table vs table variable performance test
sql server dba phone interview questions
sql server distributed availability groups with on-premises and azure instances
sql server dynamic pivot query
sql server execute as
sql server file and filegroup space usage
sql server identity system function comparison
sql server indexing for performance, parameter sniffing etc.
sql server integration services ssis 2016 tutorial
sql server integration services ssis 2016 tutorial
sql server machine learning services – python basics
sql server management studio tips
sql server on tls 1.2: checklist to disabling tls 1.1 and 1.0
sql server performance troubleshooting system health checklist using powershell
sql server reporting services 2017 setup
sql server reporting services 2017 tutorials
sql server reporting services empty report from a data driven subscription
sql server reporting services indicators
sql server reporting services lookup, lookupset and multilookup functions
sql server reporting services multi-value parameter wildcard usage
sql server reporting services ssrs auto refresh report
sql server rounding tips and tricks
sql server single user mode connection with sql server management studio
sql server standard edition does not use all assigned cpus on vm due to vmware config
sql server static data masking example
sql server sys.dm_exec_query_plan query plan is null
sql server temporal tables tips
sql server update lock and updlock table hints
sql server utc date and time functions with examples
sql server whole instance migration steps
ssis toolbox is not visible in sql server data tools
ssis variable and expression example
ssisdb catalog defaults best practices
ssms 17.4 vulnerability assessment
ssrs lookup, lookupset and multilookup functions
ssrs report manager site permissions error after installation
ssrs trending report example
stairway to columnstore indexes
stairway to dynamic data masking
stairway to sql powershell
stairway to sql server agent
stairway to sql server extended events
stairway to sql server on linux
stairway to sql server: principals and securables
start sql server without tempdb
step-by-step installation of sql server 2019 on a windows server 2019 failover cluster
steps to plan an upgrade of sql server
stop sql server from starting..
storing a variable with a select, and why it didn't work
storing passwords in a secure way in a sql server database
storing passwords in a secure way in a sql server database
string split function in sql server
table variable myths and partial truths
tempdb filling up on secondary replicas
temporal history table retention in sql server 2017
testing copy_only backups with sql server
the case of max() requiring an index scan, while top(1)/order by desc does not
the complete list of sql server trace flags
the connection to the primary replica is not active
the count function in t-sql
the many levels of concurrency
the many uses of coalesce in sql server
the many uses of coalesce in sql server
the output clause for insert and delete statements
the output clause for the merge statement
the sql server lag function
the two ways to fix non-sargable queries
there is a new count in town (approx_count_distinct)
three reasons to (temporarily) change max worker threads
transactional replication for sql server tables without primary keys
trim() in t-sql
trim() in t-sql
troubleshooting parameter sniffing issues the right way: part 1
troubleshooting resource_semaphore_query_compile helper queries
troubleshooting sql server repeatedly starting in single user mode
troubleshooting the sql server dedicated administrator connection
try_convert
t-sql decommenter part 3
t-sql enhancements in sql server 2017
two code patterns that don’t virtualize well
understanding azure storage options
understanding log buffer flushes
understanding sql server linked servers
understanding sql server linked servers
understanding sql server ownership chaining
understanding the sql server nolock hint
understanding the sql server nolock hint
unicode compression implementation in sql server 2019 (15.x)
unindexed foreign keys can make performance worse
union vs. union all in sql server
unused indexes – are they really unused or have they just not been used y-e-t?
update only year, month or day in a sql server date
updating an expired sql server tde certificate
updating statistics with ola hallengren’s script
updating the tcp port on a sql server cluster node
using binary searches to find result in huge tables without an index
using compress and decompress in sql server to save disk space
using dbatools to fix dependencies when a dba leaves the company
using merge in sql server to insert, update and delete at the same time
using powershell to simulate load
using recommendations for maxdop and max memory during installation
using switch on a single partition
using t-sql to find events that overlap (or don't) in sql server
using t-sql to find the greatest common divisor for any number of integers
using undocumented dbcc writepage to instigate sql server database corruption
verify your sql server database backups
visual studio 2019 configuration for the production dba
vmware and sql server best practices
ways to compare and find differences for sql server tables and data
ways to sort multi-server query results using central management server
weighted vs simple moving average with sql server t-sql code
what are soft deletes, and how are they implemented?
what happens if i disable the clustered index?
what happens when you cancel or kill a resumable index creation?
what is a guid in sql server
what is an event-less extended event session?
what is polybase?
what queries does microsoft’s telemetry service run on your sql server?
what to do when dbcc checkdb reports corruption
what’s better, ctes or temp tables?
what's new in sql server 2019 ctp
when do i need to use desc in indexes?
when should i use explicit transactions for single statements?
when to use the select…into statement
when you’re troubleshooting blocking, look at query #2, too
where getdate() between startdate and coalesce(canceldate, enddate) is harder to tune
where getdate() between startdate and enddate is hard to tune
which one is better.. in or between?
which sql server enterprise edition should i download?
which version of sql server should you use?
who should own the database?
who's using the dac?
why are you dropping and recreating your indexes?
why database monitoring tools are so hard to interpret
why do cached plans go bad?
why full text’s contains queries are so slow
why multiple plans for one query are bad
why multiple plans for one query are bad
why ordering isn’t guaranteed without an order by
why you shouldn't use select * in production systems (ever!)
write more compact sql server code using new features
zip and unzip files using 7-zip in sql server integration services ssis