ktaranov/sqlserver-kit

Useful links, scripts, tools and best practice for Microsoft SQL Server Database

sqlserver-kit's Issues

Add to Utilities

• https://github.com/dbcli/mssql-cli
• https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility
• https://flowheater.net/en/

Order in table and in description part: sqlcmd after bcp util, mssql-cli after sqlcmd. FlowHeater last.

Explicitly add Aaron Bertrand's dbo.sp_ForEachDb

What is the current behavior?
Some code references this stored procedure but it doesn't exist in the repository

Examples: https://github.com/ktaranov/sqlserver-kit/search?q=dbo.sp_ForEachDb&unscoped_q=dbo.sp_ForEachDb

Create stored procedure change Identity Column Type

http://www.sqlservercentral.com/articles/Data+Types/163116/

Create Audit Security script for SQL Server >= 2008

Start here:

• http://www.sqlservergeeks.com/sql-server-security-audit-a-time-consuming-task-for-dba-simplified/
• https://www.mssqltips.com/sqlservertip/1881/sql-server-security-audit-report/
• https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_SrvPermissions.sql
• https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_DBPermissions.sql

Add to SQL Server People

• https://sqlnotesfromtheunderground.wordpress.com/about-me/

• https://twitter.com/brian78

• http://sheldonhull.com/tag/sql-server/

• http://sqlbits.com/content/speakers.aspx

• http://www.sqldoubleg.com/ https://twitter.com/SQLDoubleG Raul Gonzalez

• http://sqlblog.com/user/Profile.aspx?UserID=13570 Hugo Kornelis

• https://joeydantoni.com

• https://sqlserverscotsman.wordpress.com/

• https://sqltechblog.com/

• https://joeydantoni.com/category/sql-2/

• https://dbaphilosophy.wordpress.com/

• http://sql-sasquatch.blogspot.ru/ @sqL_handLe

• https://www.sheldonhull.com

• http://www.sqlshack.com/author/jefferson-elias/

• https://blobeater.blog/

• https://sqljudo.wordpress.com/monthly-dba-challenge/

• https://chrisshaw.wordpress.com/ BLogRoll left widget

• Drew Furgiuele http://port1433.com/about-me/

• https://chrisadkin.io Chris Adkin

• https://twitter.com/BeginTry https://itsalljustelectrons.blogspot.ru/ Dave Mason

• https://twitter.com/sqlpto Pedro Lopes

• http://thomaslarock.com/rankings/

New home for "Learning Extended Events in 60 Days"

It seems that the course "Learning Extended Events in 60 Days" has a new landing page (see here). The new link is sqlxevents.com.

That bothers me is the sentence: "... The old methods (bitly and subdomain) will still be valid to point to the old landing page – for a time. ... ". Maybe it is good to add also the new link to the repository.

SSMSBoost / SQL Search

I'm missing some of my favorite tools (all free):

• SQL Search from RedGate (http://www.red-gate.com/products/sql-development/sql-search/), a SSMS plugin that let you search for string patterns in the meta data of a database (tables, views, triggers, functions, stored procedures...)

• SSMSBoost (http://www.ssmsboost.com/): a plugin with many functions as Open transaction warner, grid column aggregation, code completion, several script-result-as functions, a very useful find-colum-in-grid function

• Ola Hallengreens (http://ola.hallengren.com/) well known maintenance solution (backup / index reorg)

Improve procedure sp_BenchmarkTSQL

Improve procedure https://rebrand.ly/sp_BenchmarkTSQL:

• Add new column to Bencmark table - FinishBencmarkTime, rename column StartTimeStamp on StartBencmarkTime
• Rename variable @startTime on @startBenchmarkTime
• rename all variables to understandable names
  • @r
  • @rts
  • @dateTimeFun on dateTimeFunction
• Add in Benchmark finished at print Duration of benchmark = @startBenchmarkTime - FinishBenchmarkTime

Add to Utilities ETL tools and other Tools

Add to Utilities all ETL tools (supported SQL Server) from this list (see menu from left): https://www.etltools.net/integration-services.html
For template use EDIS - https://github.com/ktaranov/sqlserver-kit/tree/master/Utilities#edis

Red Gate new tools:

• https://www.red-gate.com/products/dba/sql-provision/
• https://www.red-gate.com/products/dba/data-masker/

Apex new tool:

• https://www.apexsql.com/sql_tools_model.aspx

SQL Server 2012 Diagnostic queries - problem with query - latency per database file

Hi,
on SQL Server 2012 the query that collects the latency per database file, the governor metrics are not working in 2012.
I suggest just removing the following columns:

io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)]

Regards,
Hugo Alhandra

Imrove In-Memory OLTP diagnostic script

https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/In-memory_OLTP_diagnostic_script.sql

1. Add @debug for printing dynamic sql statements
2. Replace EXEC (@sql) on EXEC sp_executesql @sql;
3. Replace DROP IF EXISTS ... on IF OBJECT_ID...
4. Check and solve another SQL Server 2014 issues
5. Test script on case sensitive SQL Server 2014 and SQL Server 2017
6. Transform to stored procedure with parameters:
   • @instanceLevelOnly default value 1, only instance info
   • @dbName default value ALL, list of affected databases
   • @debug, default value 0, just print dynamic sql if @debug = 1
7. Test script on SQL Server 2014 and SQL Server 2017

Add to powershell scripts and SP

• Powershell http://tracyboggiano.com/archive/2017/04/query-multipleservers/
• SP https://sqlundercover.com/2017/08/18/undercover-toolbox-sp_changedatabaseownership-producing-statements-to-alter-authorisation/
• Powershell https://www.petri.com/adding-script-to-your-powershell-html-reports
• Powershell https://www.sqlshack.com/multi-server-script-find-orphaned-data-files-using-powershell/
• Powershell http://www.sqlservercentral.com/articles/PowerShell/160582/

Add trace flag 876

From Slava Oks' tweet this week on SQL Server 2019:

https://twitter.com/slava_oks/status/1044257034361757696

With every new #SQLServer announcement it feels like Christmas time of the year, presents for everyone, @NikoNeugebauer with #SQLServer2019 CTP 2.0, could you please try trace flag -T876 instead of -T834 for some of key CS scenarios and check performance?

https://twitter.com/slava_oks/status/1044413511466512384

No -T876 is different beast... it turns 8k page allocations for CS segments into 2MB instead. -T834 does exactly that as well as it pre-commits the entire sql memory with large pages which many workloads don’t really need.

Add to Solution

• http://tracyboggiano.com/archive/2017/10/auto-reseed-identity-values/
• https://www.codeproject.com/Articles/1231882/SQL-Server-Brute-Force-Attack-Detection-Part https://www.codeproject.com/Articles/1232491/SQL-Server-Brute-Force-Attack-Detection-Part

Benchmarking stored procedure

Start: https://blog.jooq.org/2017/03/29/how-to-benchmark-alternative-sql-queries-to-find-the-fastest-query/

Name: sp_BenchmarkTSQL

Parameters:

• @tsqlStatement NVARCHAR(MAX) mandatory
• @numberOfExecution INT default 10
• @isSaveResults BIT default 0 (save benchmark result in table for analyze)
• Add @tsqlStatement validation like in function https://github.com/ktaranov/sqlserver-kit/blob/master/User_Defined_Function/udf_CheckDynamicSQL.sql
• @resetBuferCacheForStatement BIT default 0
• Add desriptiion like in function https://github.com/ktaranov/sqlserver-kit/blob/master/User_Defined_Function/udf_CheckDynamicSQL.sql

Trace flags to add

I did a very poor job of documenting them, but there are some new trace flags here: https://orderbyselectnull.com/2018/01/09/45-new-trace-flags/

During research for that post I also found the following that were posted by someone else:

9479 https://dba.stackexchange.com/questions/141220/sql-server-2014-any-explanation-for-inconsistent-self-join-cardinality-estimate
8691 https://www.sqlservercentral.com/Forums/Topic1390297-3122-5.aspx
9415 http://www.queryprocessor.com/adaptive-join-internals/
9399 http://www.queryprocessor.com/adaptive-join-internals/
9398 http://www.queryprocessor.com/adaptive-join-internals/
2399 https://dba.stackexchange.com/questions/149811/why-does-the-estimated-cost-of-the-same-1000-seeks-on-a-unique-index-differ-in
9410 https://support.microsoft.com/en-us/help/3167159/fix-query-runs-slowly-when-sql-server-uses-hash-aggregate-in-the-query

Improve sp_RecreateIndexes

https://www.brentozar.com/archive/2017/08/drop-indexes-fast/

• Add create option dynamic sql

Add to uilities

Update info and add new utilities:

• Update all Devart Products (see email)
• ApexSQL Trigger Viewer
• ApexSQL Snapshot Utility
• ApexSQL Propagate
• ApexSQL Plan
• Update release date for others Apex products https://www.apexsql.com/Download.aspx?download=Search
• Stellar Phoenix SQL Database
• DataNumen SQL Recovery
• https://www.xsql.com/products/
• SQL Recoverytoolbox
• http://www.sqlmdfviewer.org/products.html
• http://www.sqlbi.com/tools/dax-studio/
• https://www.sqlshack.com/review-sql-cop-unit-tests-collection/

SSMSBoost free version is now crippled after the trial period

Regarding the listing in https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Addins.md

Compute transitive closure of sys.sql_dependencies, find missing objects

https://mssqlwiki.com/2012/05/04/copy-database-wizard-or-replication-setup-might-fail-due-to-broken-dependency/

This script is pure gold. I've repeatedly reached out to Karthik asking permission to host it on GitHub, but he has never responded. It does contain bugs, some of which I've fixed in the comments and others which were left with my former employer when I left.

Some things I fixed:

• The original script assumed stored procedures could be refreshed in any order. This is only true for trivial stored procedures that don’t reference objects that can’t be refreshed in any order. My fix handles that scenario.
• My fix explicitly handles stored procedures and/or triggers.
• Fix the scenario where a schema-bound view is transitively referencing a broken view.

Some fixes not contained in my patch, off the top of my head:

• Common Table Expressions variables erroneously flag as false positives in this script. At my former employer, we would maintain a lookup table of Common Table Expression variables and generally exclude object names under 3 characters as bogus.
• Session-scoped temp tables and global temp tables erroneously flag as false positives in this script.

While Karthik uses it to find broken dependencies in the context of copy database wizard or replication setup, I used it after running database migrations to automatically rollback all migrations if the migration broke references/didn't clean up after itself. Some people naively call sp_refreshview or sp_refreshsqlmodule over all sys.objects, but this will fail if you have circular references, which is what Karthik's original script mostly resolves.

Add trace flag 8759

Trace flag 8759 will detect and write part of the query to the error log when it has been autoparameterized.

TF 8726 and 8727

I came across these in an answer from Paul White on TopAnswer.xyz and just realized they are not in your list (which I reference frequently, so thanks for compiling it!).

I've included the info, and repros for both flags. Both repros use the StackOverflow2010 database, and require these indexes to be present:

CREATE INDEX IX_UserID ON dbo.Comments (UserID, PostID);
CREATE INDEX IX_PostID ON dbo.Comments (PostID, UserID);

Trace Flag: 8726
Undocumented trace flag
Function: Disables the heuristic that discourages the optimizer from producing an index union plan
Link: Why are (seemingly) suitable indexes not used on a LEFT JOIN with OR

Repro

Execution Plan: https://www.brentozar.com/pastetheplan/?id=B1mSzXGv8

-- No index union (seek on Users, multiple scans on Comments)
SELECT *
FROM Users u
	LEFT JOIN Comments c
		ON u.Id = c.UserId 
		OR u.Id = c.PostId
WHERE u.DisplayName = 'alex';

-- Gets the index union plan
SELECT *
FROM Users u
	LEFT JOIN Comments c
		ON u.Id = c.UserId 
		OR u.Id = c.PostId
WHERE u.DisplayName = 'alex'
OPTION (QUERYTRACEON 8726);

Trace Flag: 8727
Undocumented trace flag
Function: Can be used to prevent unwanted index union plans
Link: Why are (seemingly) suitable indexes not used on a LEFT JOIN with OR

Repro

Execution Plan: https://www.brentozar.com/pastetheplan/?id=HkwhlQGwL

-- Gets an index union plan normally
SELECT *
FROM dbo.Users u
OUTER APPLY 
(
	SELECT *
	FROM dbo.Comments c
	WHERE 
		u.Id = c.UserId 
		OR u.Id = c.PostId
) c
WHERE u.DisplayName = 'alex';

-- No index union (seek on Users, multiple scans on Comments)
SELECT *
FROM dbo.Users u
OUTER APPLY 
(
	SELECT *
	FROM dbo.Comments c
	WHERE 
		u.Id = c.UserId 
		OR u.Id = c.PostId
) c
WHERE u.DisplayName = 'alex'
OPTION (QUERYTRACEON 8727);

Trace Flag 9288

I don't see it in your list. While it's not documented much anywhere, it does have known effects around local and global aggregates. Here's a demo:

CREATE TABLE tf_9288
(
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    SomeNumber INT
);

INSERT dbo.tf_9288 WITH ( TABLOCK ) ( SomeNumber )
SELECT TOP 1000000 x.n
FROM   (   SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m
           CROSS JOIN sys.messages AS m2 ) AS x;

SELECT SUM(x.c) AS sum_c
FROM   (   SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber < 500000
           UNION ALL
           SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber > 500000 ) AS x;

SELECT SUM(x.c) AS sum_c
FROM   (   SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber < 500000
           UNION ALL
           SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber > 500000 ) AS x
OPTION ( QUERYTRACEON 9288 );

Add Security Audit Report

http://stackoverflow.com/a/7059579/1387418

Script source found at : http://stackoverflow.com/a/7059579/1387418

Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role



Columns Returned:
UserName         : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
UserType         : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName
PrincipalType    : Possible values are 'SQL User', 'Windows User', 'Database Role', 'Windows Group'
DatabaseUserName : Name of the associated user as defined in the database user account.  The database user may not be the
                   same as the server user.
Role             : The role name.  This will be null if the associated permissions to the object are defined at directly
                   on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType   : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                   DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.
PermissionState  : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.
ObjectType       : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                   SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.          
ObjectName       : Name of the object that the user/role is assigned permissions on.  
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.
ColumnName       : Name of the column of the object that the user/role is assigned permissions on. This value
                   is only populated if the object is a table, view or a table value function.

sp_BlitzInMemoryOLTP modify to MIT license?

• Ask to author (Ned Otter) about add MIT License to this stored procedure

sp_BlitzInMemoryOLTP

https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_BlitzInMemoryOLTP.sql

• Enhance @debug functionality like a 'List indexes on memory-optimized tables in this database'
• Fix issue for @dbName = 'ALL' (now only works for one database)

sql server object permissions with username and group details

Looking to find for one object full permissions including the user name and its windows domain group detials. any suggestions? Tried with fn_my_permissions. Its giving only kind of access.

Extend functionality for usp_BulkUpload procedure

https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/usp_BulkUpload.sql

• Add all bulk parameters (https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql):
  • BATCHSIZE
  • CHECK_CONSTRAINTS
  • ERRORFILE_DATASOURCE
  • FIRE_TRIGGERS
  • FORMATFILE_DATASOURCE
  • KILOBYTES_PER_BATCH
  • MAXERRORS
  • ROWS_PER_BATCH

Brent Ozar Link for books is not working

Link http://www.brentozar.com/first-aid/free-database-books-pdfs-download/ is leading to "Page not found"

Test Generate Insert statement stored procedure

https://www.codeproject.com/Tips/1218208/Generate-Insert-Scripts-of-Existing-Rows-with-if-E

Add Trace Flag 9198 to doc

Referenced here

Undocumented trace flag 9198 can be used to disable lazy index performance spools specifically. You may still get a lazy table or row count spool instead (with or without sort optimization), depending on costing.

Find more tools and utilities

Check this tools lists:

• https://www.sqlshack.com/free-ms-sql-server-tools/
• https://www.sqlshack.com/sql-azure-tools/
• https://www.sqlshack.com/sql-formatting-tools
• http://www.ilovefreesoftware.com/23/featured/online-database-diagram-tool-create-design-database-schema-free.html
• https://sqlgrease.com/

Author incorrect

I (Brett Shearer) did not write the article attributed to me.

usp_BulkUpload: The module 'usp_BulkUpload' depends on the missing object

Do you want to request a feature or report a bug? BUG

What is the current behavior? Problem during installation of the Stored Procedure

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/ run the usp_BulkUpload.sql on a new server installation

What is the expected behavior? the stored procedure should install without problems

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

SQL Server 2016
Windows10

Link for TF 2398

Smart seek costing is discussed here: https://dba.stackexchange.com/a/149933/32281

Add to scripts

• https://sqlundercover.com/2017/09/13/undercover-toolbox-checking-your-backup-paths-exist-and-automatically-creating-them-if-they-dont/
• https://www.mssqltips.com/sqlservertip/4876/finding-sql-server-object-dependencies-for-synonyms/

Test and add to scripts

• http://www.sqlservercentral.com/blogs/sqlrnnr/2017/10/27/drop-that-schema/
• http://nedotter.com/archive/2017/10/in-memory-oltp-diagnostic-script/

Create separate study databases list with instructions

• Replace adventure-works links: https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/adventure-works
• Create property tables

Add sqlsig SQLCLR function for auto-parameterizing TextData from SQL Profiler Trace

This code is from Itzik Ben-Gan's book on query tuning, and is largely unknown to those who have never read the book.

Add Vulnerability Assessment in SSMS gif to SSMS tips

https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-vulnerability-assessment

Create separate images and gif from it using http://gifmaker.me.

Database: WorldWideImporters.

Create search string in any tables columns procedure

Examples to start:

• https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/
• http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Parameters:

• @databaseMask default all
• @shemaMask default all
• @tableMask default all
• @dataTypeMask default 'char','nchar','ntext','nvarchar','text','varchar'
• @searchString

Add gifs to SSMS tips

SSMS tips: https://rebrand.ly/gh-ssms-tips

• Tip 13 SQLCMD mode
• Tip 30 Live Query Statistics
• Tip 36 Import Flat File to SQL Wizard

Glenn Berry Diagnostic Scripts Copyright

According this comments ffc09ba#commitcomment-27792716

Waiting for Glen answer.

Add to stored procedures and scripts

Add to scripts (test before on SQL Server 2014 case sensitive):

• http://www.sqlservercentral.com/articles/Extended+Property/137761/
• https://sqlstudies.com/2013/07/29/notes-on-debugging-database-mail-problems/
• http://billfellows.blogspot.ru/2017/10/temporal-table-maker.html

dbo.sp_DBPermissions.sql third result set ambiguous return order

I have been at my wit's end trying to understand an issue in production where I'm getting a SELECT DENIED error on an UPDATE statement, and it works fine in BETA environment.. so I'm using Ken Fisher's wonderful sp_DBPermissions to try to compare one by one each permission to see if somehow something got deployed incorrectly.

While doing this, I noticed that it's ordered by grantee_name and object_name but NOT permission_name, leading to situations where VIEW DEFINITION and EXECUTE permissions on two different servers might be output in different order.

For my use case, I was copy-pasting the output into EXCEL and running a simple =I3=S3 column sanity check operation.

SSMS Locking up on explorer operations

Do you want to request a feature or report a bug?
Question regarding potential SSMSBoost issue relating to a specific query.

What is the current behavior?
With SSMS Boost installed with SSMS version 17.4, we keep seeing SSMS locking up and a "sp_whoisactive" run shows us that its locked and sitting on this specific query (will be at thee bottom).

The query is essentially the SQL found here below the proc:
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Table_Parameters.sql

I'm trying to figure out if this query is an SSMS thing, or part of SSMSBoost.

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/

What is the expected behavior?

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2016, Windows 10. Not sure about previous versions.

SQL Server Transaction Log Management link dead

https://assets.red-gate.com/offerings/transaction-log-management.pdf returns an access denied message.

Add naming convention for Linked Servers

Improve sp_snapshot

https://sqlundercover.com/2017/09/20/sp_snapshot/

• add semicolons and others standards (create and alter, name convention and formating options)
• rename @ListOnly on @debug parameter

Test and enhance util_CopyTable

https://gist.github.com/ktaranov/efd5c017edcfe11dc6bad0658d894a7e

• Change name on sp_CopyTable
• Add @debug parameter (remove unnecessary comments)
• Add powershell like description section
• Ask Mantovanelli to convert in MIT license
• Test on SQL Server 2014, 2017 with different tables
• Change DROP PROCEDURE algo on OBJECT_ID