Skip to content

Instantly share code, notes, and snippets.

Avatar

Gianluca Sartori spaghettidba

View GitHub Profile
View sqlversion.sql
DECLARE @ProductVersion varchar(15);
DECLARE @Edition varchar(15);
DECLARE @EngineEdition smallint;
DECLARE @HostPlatform varchar(15);
SELECT @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS varchar(15)),
@Edition = CAST(SERVERPROPERTY('Edition') AS varchar(15)),
@EngineEdition = CAST(SERVERPROPERTY('EngineEdition') AS smallint)
DECLARE @Version int
View question.md

I see a few issues with your code that could be slowing it down. Let's try to go through them together:

  1. SELECT * - Unless you really need all the columns, SELECT * is not a good practice: it prevents the optimizer from using indexes because looking up all the columns usually exceeds the cost of scanning the whole table
  2. nolock - It is not a "go faster" switch: it instructs the engine to ignore locks posed by other sessions and not lock rows while reading them. This could end up reading rows more than once or skipping them altogether, besides reading rows not yet committed by other sessions. Make sure you understand what are the effects on your query.
  3. p.col5 in ( -- list of 20 strings ) or p.col5 in ( -- list of 3000 strings ) can be collapsed in a single p.col5 in ( -- list of 3020 strings )
  4. You should probably save your strings in a temporary table or table parameter, because passing too many literals on a query can exhaust the resources of the query optimizer (it usually times out tryi
@spaghettidba
spaghettidba / dowload-micro.ps1
Created Feb 14, 2020
Download and extract Micro
View dowload-micro.ps1
wget "https://github.com/$([regex]::match((wget https://github.com/zyedidia/micro/releases/latest).Content,'\/zyedidia\/micro\/releases\/download\/.+win64\.zip').Groups[0].Value)" -OutFile "$($env:temp)\micro.zip"; Expand-Archive -Path "$($env:temp)\micro.zip" -DestinationPath (gl)
@spaghettidba
spaghettidba / capture-diagnostic.ps1
Created Nov 24, 2019
Capture diagnostic queries
View capture-diagnostic.ps1
[CmdletBinding()]
param(
[Parameter(Mandatory=$true, Position=1)]
[string[]]$SourceServer,
[Parameter(Mandatory=$true, Position=2)]
[string]$DestinationServer,
[Parameter(Mandatory=$false, Position=3)]
[string]$DestinationDatabase = "diagnostic",
[Parameter(Mandatory=$false, Position=4)]
[string]$DestinationSchema = "dbo"
View testPFCLNT_2.ps1
#
# RUN THIS CODE IN Powershell x86 !!!!!
#
$dllPath = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.ConnectionInfoExtended.dll"
$typeName = "Microsoft.SqlServer.Management.Trace.Tracefile"
try {
$InputFile = "c:\temp\test.trc"
$baseAssembly = [Reflection.Assembly]::LoadFrom($dllPath)
View testPFCLNT.ps1
#
# RUN THIS CODE IN Powershell x86 !!!!!
#
$dllPath = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\PFCLNT.DLL"
$typeName = "Microsoft.SqlServer.Management.Trace.CTraceObjectsFileController"
try {
#
# this is the code found in Microsoft.SqlServer.Management.Trace.TraceFile.InitializeAsReader (thanks DotPeek...)
@spaghettidba
spaghettidba / Import-ServerNames.ps1
Created Apr 3, 2019
Import-ServerNames.ps1 #blog
View Import-ServerNames.ps1
$servers = @()
$regservers = Invoke-sqlcmd -ServerInstance GSVSQL33\SQL21 -Query "SELECT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal"
$regservers |
ForEach-Object {
$servnameparts = $_.server_name.Split(@("\"),[System.StringSplitOptions]::None)
$serverName = $servnameparts[0]
if(Test-Connection -ComputerName $serverName -Quiet -Count 1){
try {
Invoke-Sqlcmd -ServerInstance $_.server_name -Query "SELECT SERVERPROPERTY('computernamephysicalnetbios') AS servername" |
ForEach-Object {
View Disconnect-RemoteSessions.ps1
[CmdletBinding()]
Param(
[Parameter(Mandatory=$False,Position=1)]
[string[]]$servers
)
if(-not $servers){
$servers = Get-Content servers.txt
}
@spaghettidba
spaghettidba / create-diagnostic-notebook.ps1
Last active Mar 20, 2019
create-diagnostic-notebook.ps1 #blog
View create-diagnostic-notebook.ps1
#
# Purpose: take the diagnostic queries from Glenn Berry
# and generate a Jupyter Notebook to run in Azure Data Studio
#
# Example usage:
# create-diagnostic-notebook.ps1 -diagnosticScriptPath "C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777\bin\diagnosticquery\SQLServerDiagnosticQueries_2019_201901.sql" -notebookOutputPath "diagnostic-notebook.ipynb"
#
[CmdletBinding()]
Param(
[parameter(Mandatory)]
View setvar.sql
:setvar somevar "somevalue"
SELECT '$(somevar)'
GO
SELECT '$(somevar)'
GO
You can’t perform that action at this time.