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