SQL Server useful but less known features

  • Posted on: 2 November 2016
  • By: Michał Turecki

  1. Checking who is connected to the database

    sp_who

    SELECT
    NULL as [Connections by Database],
    [host_name] AS [Client Machine],
    db.name AS [Database],
    [program_name] AS [Client Program],
    COUNT(*) AS [Open Connections]
    FROM sys.dm_exec_sessions s (nolock)
    LEFT JOIN sys.dm_exec_requests r (nolock) ON r.session_id = s.session_id
    LEFT JOIN sys.databases db (nolock) ON db.database_id = r.database_id
    WHERE s.session_id >= 50 -- Ignore SQL Server processes
    GROUP BY [host_name], db.name, [program_name]
    ORDER BY [Client Machine], [Database], [Client Program]

  2. Adjusting connection pooling settings

    By default pool size is 100 per database with a command timeout (effectively a connection timeout) of 15 seconds. To increase add following to the connection string:

    Connection Timeout=30;Pooling='true';Max Pool Size=200

  3. Checking the size of column's contents

    To check how much data the column contains:

    SELECT *, DATALENGTH([column]) AS ColumnSizeInBytes FROM table

  4. Using SQL Server profiler

    Profiler is a powerful way to debug application performance problems, to check query execution time. If you're developing any application using SQL Server as a database, running it at least once for every application release can help with pinpointing possible performance issues. Remember the rule - there should be a constant number of queries regardless of the amount of records fetched - that way application will scale better. Sometimes it's better to replace a single complex query with more simpler queries because for each result row, columns shared by joins are repeated and delivered to client for processing many times.

Tags: 
programming
SQL
databases
sql-server

Comments

Craigwoomo (not verified)

Sun, 03/02/2019 - 22:58

Hello! tadalafil buy cialis medication purchase propecia online no prescription excellent internet site

Craigwoomo (not verified)

Sun, 03/02/2019 - 22:54

Hello! tadalafil buy cialis medication purchase propecia online no prescription excellent internet site

Craigwoomo (not verified)

Sun, 03/02/2019 - 22:51

Hello! tadalafil buy cialis medication purchase propecia online no prescription excellent internet site

Craigwoomo (not verified)

Sun, 03/02/2019 - 22:50

Hello! tadalafil buy cialis medication purchase propecia online no prescription excellent internet site

Craigwoomo (not verified)

Sun, 03/02/2019 - 19:33

Hello there! buy generic cialis buy cialis no prescription buy finasteride pills very good web site

Craigwoomo (not verified)

Sun, 03/02/2019 - 19:30

Hello there! buy generic cialis buy cialis no prescription buy finasteride pills very good web site

Craigwoomo (not verified)

Sun, 03/02/2019 - 19:23

Hello there! buy generic cialis buy cialis no prescription buy finasteride pills very good web site

Craigwoomo (not verified)

Sun, 03/02/2019 - 08:19

Hi there! purchase tadalafil tadalafil buy generic propecia great site

Craigwoomo (not verified)

Sun, 03/02/2019 - 08:14

Hi there! purchase tadalafil tadalafil buy generic propecia great site

Craigwoomo (not verified)

Sun, 03/02/2019 - 05:29

Howdy! purchase cialis order tadalafil propecia good site

Craigwoomo (not verified)

Sun, 03/02/2019 - 05:06

Howdy! purchase cialis order tadalafil propecia good site

Craigwoomo (not verified)

Thu, 31/01/2019 - 23:44

Hi! buy cialis cheap order cialis buy propecia medication great website

Craigwoomo (not verified)

Thu, 31/01/2019 - 20:50

Hello there! buy generic cialis buy cialis no rx buy propecia cheap excellent web site

Craigwoomo (not verified)

Thu, 31/01/2019 - 20:39

Hello there! buy generic cialis buy cialis no rx buy propecia cheap excellent web site

Craigwoomo (not verified)

Thu, 31/01/2019 - 20:35

Hello there! buy generic cialis buy cialis no rx buy propecia cheap excellent web site

BianscaNow (not verified)

Wed, 30/01/2019 - 01:45

best online casino
free casino games online
casino movie online
free online casino
free online casino slots

BianscaNow (not verified)

Wed, 30/01/2019 - 01:32

best online casino
free casino games online
casino movie online
free online casino
free online casino slots

BianscaNow (not verified)

Wed, 30/01/2019 - 01:19

best online casino
free casino games online
casino movie online
free online casino
free online casino slots

Dorеismam (not verified)

Tue, 29/01/2019 - 23:50

casino online
online casino
free casino games online
online casino real money no deposit
free casino games online

CarcolZed (not verified)

Tue, 29/01/2019 - 18:56

casino online gambling
pogo casino slots
casino games slots
casino games free online
top rated free online casino games

CarcolZed (not verified)

Tue, 29/01/2019 - 18:54

casino online gambling
pogo casino slots
casino games slots
casino games free online
top rated free online casino games

CarcolZed (not verified)

Tue, 29/01/2019 - 18:49

casino online gambling
pogo casino slots
casino games slots
casino games free online
top rated free online casino games

Linzanut (not verified)

Tue, 29/01/2019 - 16:58

pogo casino slots
casino online
new usa online casinos 2017
new usa online casinos 2017
casino online application

CarcolZed (not verified)

Tue, 29/01/2019 - 12:40

play casino games online
top rated free online casino games
pogo casino slots
casino online application
vegas casino games

CarcolZed (not verified)

Tue, 29/01/2019 - 12:34

play casino games online
top rated free online casino games
pogo casino slots
casino online application
vegas casino games

Linzanut (not verified)

Tue, 29/01/2019 - 10:41

casino online free
casino online subtitrat
pogo casino slots
casino online usa
online casino real money

Linzanut (not verified)

Tue, 29/01/2019 - 10:29

casino online free
casino online subtitrat
pogo casino slots
casino online usa
online casino real money

Linzanut (not verified)

Tue, 29/01/2019 - 10:22

casino online free
casino online subtitrat
pogo casino slots
casino online usa
online casino real money

Floshietriag (not verified)

Tue, 29/01/2019 - 05:56

casino online for fun
casino games real money
casino games free online
casino games real money
casino games free

Emidlycep (not verified)

Tue, 29/01/2019 - 02:43

top rated free online casino games
casino games free online
casino online application
casino online usa
new usa online casinos

Floshietriag (not verified)

Mon, 28/01/2019 - 18:01

casino games free
online casinos 2016
casino online for fun
casino games roulette
casino online free

Floshietriag (not verified)

Mon, 28/01/2019 - 17:55

casino games free
online casinos 2016
casino online for fun
casino games roulette
casino online free

Floshietriag (not verified)

Mon, 28/01/2019 - 17:49

casino games free
online casinos 2016
casino online for fun
casino games roulette
casino online free

Floshietriag (not verified)

Mon, 28/01/2019 - 17:41

casino games free
online casinos 2016
casino online for fun
casino games roulette
casino online free

Dominicduake (not verified)

Mon, 28/01/2019 - 12:50

Hi there! order tadalafil buy valtrex 500 mg rx cost excellent web site

Dominicduake (not verified)

Mon, 28/01/2019 - 10:53

Hi there! buy cialis pills buy valtrex pills online prescription without a doctor's prescription beneficial web site

Dominicduake (not verified)

Mon, 28/01/2019 - 10:50

Hi there! buy cialis pills buy valtrex pills online prescription without a doctor's prescription beneficial web site

Dorеismam (not verified)

Mon, 28/01/2019 - 10:49

best online casino
free online casino
free online casino
online casino real money
online casino real money

Dominicduake (not verified)

Mon, 28/01/2019 - 10:43

Hi there! buy cialis pills buy valtrex pills online prescription without a doctor's prescription beneficial web site

Dominicduake (not verified)

Mon, 28/01/2019 - 07:37

Hello there! buy cialis with no prescription purchase valtrex online no prescription drug pricing very good site

Dominicduake (not verified)

Mon, 28/01/2019 - 07:35

Hello there! buy cialis with no prescription purchase valtrex online no prescription drug pricing very good site

Dominicduake (not verified)

Mon, 28/01/2019 - 07:33

Hello there! buy cialis with no prescription purchase valtrex online no prescription drug pricing very good site

Dominicduake (not verified)

Mon, 28/01/2019 - 07:30

Hello there! buy cialis with no prescription purchase valtrex online no prescription drug pricing very good site

Dorеismam (not verified)

Sun, 27/01/2019 - 23:03

watch casino online free
free casino games online
real online casino
online casino usa
watch casino online

Dorеismam (not verified)

Sun, 27/01/2019 - 15:41

free online casino games
resorts casino online
online casino
watch casino online free
online casino

Dorеismam (not verified)

Sun, 27/01/2019 - 15:22

free online casino games
resorts casino online
online casino
watch casino online free
online casino

Dorеismam (not verified)

Sun, 27/01/2019 - 15:14

free online casino games
resorts casino online
online casino
watch casino online free
online casino

BianscaNow (not verified)

Sat, 26/01/2019 - 16:44

free casino games online
online casino no deposit bonus
online casino
online casino real money
casino movie online

BianscaNow (not verified)

Sat, 26/01/2019 - 16:41

free casino games online
online casino no deposit bonus
online casino
online casino real money
casino movie online

BianscaNow (not verified)

Sat, 26/01/2019 - 16:37

free casino games online
online casino no deposit bonus
online casino
online casino real money
casino movie online

Pages