ktaranov / sqlserver-kit Goto Github PK
View Code? Open in Web Editor NEWUseful links, scripts, tools and best practice for Microsoft SQL Server Database
Home Page: http://sqlserver-kit.org
License: MIT License
Useful links, scripts, tools and best practice for Microsoft SQL Server Database
Home Page: http://sqlserver-kit.org
License: MIT License
Link http://www.brentozar.com/first-aid/free-database-books-pdfs-download/ is leading to "Page not found"
Order in table and in description part: sqlcmd after bcp util, mssql-cli after sqlcmd. FlowHeater last.
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/In-memory_OLTP_diagnostic_script.sql
@debug
for printing dynamic sql statementsEXEC (@sql)
on EXEC sp_executesql @sql;
DROP IF EXISTS ...
on IF OBJECT_ID...
@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
https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/usp_BulkUpload.sql
This code is from Itzik Ben-Gan's book on query tuning, and is largely unknown to those who have never read the book.
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.
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:
Some fixes not contained in my patch, off the top of my head:
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.
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.
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.
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.
Add to scripts (test before on SQL Server 2014 case sensitive):
https://assets.red-gate.com/offerings/transaction-log-management.pdf returns an access denied message.
Start here:
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.
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 8759 will detect and write part of the query to the error log when it has been autoparameterized.
Update info and add new utilities:
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
According this comments ffc09ba#commitcomment-27792716
Waiting for Glen answer.
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.
Smart seek costing is discussed here: https://dba.stackexchange.com/a/149933/32281
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.
https://sqlundercover.com/2017/09/20/sp_snapshot/
@ListOnly
on @debug
parameterAdd 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:
Apex new tool:
https://gist.github.com/ktaranov/efd5c017edcfe11dc6bad0658d894a7e
sp_CopyTable
@debug
parameter (remove unnecessary comments)DROP PROCEDURE
algo on OBJECT_ID
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
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.
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 );
https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_BlitzInMemoryOLTP.sql
@debug
functionality like a 'List indexes on memory-optimized tables in this database'@dbName = 'ALL'
(now only works for one database)Examples to start:
Parameters:
@databaseMask
default all
@shemaMask
default all
@tableMask
default all
@dataTypeMask
default 'char','nchar','ntext','nvarchar','text','varchar'
@searchString
http://www.sqldoubleg.com/ https://twitter.com/SQLDoubleG Raul Gonzalez
http://sqlblog.com/user/Profile.aspx?UserID=13570 Hugo Kornelis
http://sql-sasquatch.blogspot.ru/ @sqL_handLe
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
SSMS tips: https://rebrand.ly/gh-ssms-tips
https://www.brentozar.com/archive/2017/08/drop-indexes-fast/
I (Brett Shearer) did not write the article attributed to me.
Improve procedure https://rebrand.ly/sp_BenchmarkTSQL:
FinishBencmarkTime
, rename column StartTimeStamp
on StartBencmarkTime
@startTime
on @startBenchmarkTime
@r
@rts
@dateTimeFun
on dateTimeFunction
Benchmark finished at
print Duration of benchmark = @startBenchmarkTime - FinishBenchmarkTime
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
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)@tsqlStatement
validation like in function https://github.com/ktaranov/sqlserver-kit/blob/master/User_Defined_Function/udf_CheckDynamicSQL.sql@resetBuferCacheForStatement
BIT default 0I'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)
Regarding the listing in https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Addins.md
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.