Loading...
 

MS SQL Server

Home

SQL Server versions


Columnstore

Columnstore indexes - Overview

Columnstore indexes - what's new

Columnstore indexes - Design guidance

Columnstore Index Design Guidelines

Columnstore indexes - Query performance

What are Columnstore Indexes?



Indexes

SQL Server Agent Jobs

  • Get my own JOB_ID
    • Copy to clipboard
      DECLARE @name NVARCHAR(128)
	select @name =  name from msdb.dbo.sysjobs where job_id = $(ESCAPE_SQUOTE(JOBID))
	PRINT @name
    • If you "parse" the code, ignore the error: "Incorrect syntax near 'ESCAPE_SQUOTE'."

  • Set Output file name for all jobs
    • Copy to clipboard
      _	UPDATE a
	SET a.output_file_name = b.name + '.txt'
	FROM msdb.dbo.sysjobsteps a inner join
		msdb.dbo.sysjobs b
			ON a.job_id = b.job_id

  • Update to include step output in history
    • Copy to clipboard
      _	SELECT  j.name,
		flags | 32 AS NewFlag,
		js.*
	-- UPDATE js SET flags = flags | 32
	FROM    msdb.dbo.sysjobsteps js
		INNER JOIN msdb.dbo.sysjobs j 
			ON j.job_id = js.job_id
	WHERE   flags & 32 = 0
		AND subsystem IN ('CmdExec', 'SSIS')

	SELECT  j.name,
		flags | 4 AS NewFlag,
		js.*
	-- UPDATE js SET flags = flags | 4
	FROM    msdb.dbo.sysjobsteps js
		INNER JOIN msdb.dbo.sysjobs j 
			ON j.job_id = js.job_id
	WHERE   flags & 4 = 0
		AND subsystem = 'TSQL'
    • http://www.nuronconsulting.com/TemplateScripts_msdbSet_Include_step_in_output_history_sql.aspx

  • msdb.dbo.sysjobsteps.flags
    • OR together as bits in a field
    • subsystem IN ('CmdExec', 'SSIS', 'PowerShell')
      • none = 0
      • Append output to existing file = 2
      • log to table = 8
      • append to table = 16 — mutually exclusive with log to table
      • Include step output in history = 32
    • subsystem = 'TSQL'
      • none = 0
      • Append output to existing file = 2
      • include step output in history = 4
      • log to table = 8
      • append to table = 16 — mutually exclusive with log to table

VLFs

Finding equivalent elements in SQL (regrouping)

Logging SSPI failed logins on computers on a doman

SQL Server TLS 1.0

Ports to open

  • TCP 135 - SQL Transact Debugger/RPC
  • TCP 1433 - SQL Server Default Instance
  • TCP 1434 - SQL Server Admin Connection
  • TCP 2382 - SQL Server Browser
  • TCP 2383 - SQL Analysis Services
  • TCP 4022 - SQL Service Broker
  • UDP 1434 - SQL Server Browser Multicast Response
  • Program Name - C:\Program Files\Microsoft SQL Server\MSSQLxxxx\MSSQL\Binn\sqlservr.exe

List of permissions for Service account

SMTP - DBMail


Errors

  • File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty
    • Grow file by 1MB
    • Should be able to shrink after that

Instant File Initialization

  • Only for data files, not on log files
  • Testing if turned on, using trace flags

Lock pages in memory

  • Need to add the proper rights using the User / Groups settings, and need to set a trace flag
    • ***UPDATE ACTUAL DIRECTIONS***

Adding users to non-domain Windows (home) versions

SYS.DATABASES collations issue between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AI

Sample Databases

Workload generators

SQL Server 2000 SMTP mail

Disk IO Tests

Disk Layout

Profile Traces

SSIS

SSRS

SQL Server Data Tools (SSDT) in 2014

Data and Plan Cache

Currently Running Processes

Performance Tuning and Monitoring

SSMS Custom Reports

SSMS Settings

Add-Ons

  • SSMS Tools Pack
    • add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2, 2012
    • Execution Plan Analyzer
    • SQL Snippets
    • Window Connection Coloring
    • Window Content History, Query Execution History, Current Window History and Tab Sessions
    • Format SQL
    • Search Table, View or Database Data
    • Run one script on multiple databases
    • Copy execution plan bitmaps to clipboard or file
    • Search Results in Grid Mode
    • Generate Insert statements from resultsets, tables or database
    • Regions and Debug sections
    • Running custom scripts from Object Explorer
    • CRUD stored procedure generation
    • New query template
    • General options
    • http://www.ssmstoolspack.com/

  • SQL#
    • SQLCLR package with new functions not in SQL
    • Free and paid version
    • Most of the free ones are easy enough to code in SQL, so haven't used it
    • http://www.sqlsharp.com/

  • SQL Query Stress
    • Free tool
    • Assist with performance stress testing of T-SQL queries and routines
    • Automatically collects metrics to help determine how queries perform under load, and what kind of resource strain they put on server
    • http://www.datamanipulation.net/sqlquerystress/

SQL Tools and Plugins

  • SQLRanger
    • Might be neat, but requires .Net 4.5 and installs as a ClickOnce Application, so hard to get it to run under different users
    • Setting Windows Credentials does not change user context when connecting to other domains
    • Run from the client computer, doesn't need to install on server
    • http://www.sqlranger.com/
  • Top 10 Free SQL Server Tools
  • Qure Profiler
    • Realtime profiling solution for analyzing and comparing SQL Server trace files
    • Ability to compare database workloads before-and-after changes
    • Free part of their full suite
    • http://www.dbsophic.com/qure-analyzer.html

SSMSBoost

DataGrip

SSMS Tools Pack

dbForge SQL Complete

Business Intelligence


CSV Splitter Functions


Schema Binding