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

GregoryFuh (not verified)

Mon, 24/12/2018 - 06:15

Hi there! norco online pharmacy very good site http://onlinegenepharmacy.com

GregoryFuh (not verified)

Mon, 24/12/2018 - 02:00

Howdy! pharmacy tech certification online very good internet site http://onlinegenepharmacy.com

GregoryFuh (not verified)

Mon, 24/12/2018 - 01:55

Howdy! pharmacy tech certification online very good internet site http://onlinegenepharmacy.com

GregoryFuh (not verified)

Mon, 24/12/2018 - 01:54

Howdy! pharmacy tech certification online very good internet site http://onlinegenepharmacy.com

GregoryFuh (not verified)

Mon, 24/12/2018 - 01:50

Howdy! pharmacy tech certification online very good internet site http://onlinegenepharmacy.com

Shawnnut (not verified)

Sun, 23/12/2018 - 17:56

Hello there! online pharmacy technician certification beneficial internet site http://onlinepharmacycanadaus.com

Shawnnut (not verified)

Sun, 23/12/2018 - 15:47

Hi there! online pharmacy technician great site http://onlinepharmacycanadaus.com

Shawnnut (not verified)

Sun, 23/12/2018 - 15:41

Hi there! online pharmacy technician great site http://onlinepharmacycanadaus.com

Shawnnut (not verified)

Sun, 23/12/2018 - 11:45

Hi there! pharmacies online great site http://onlinepharmacycanadaus.com

Shawnnut (not verified)

Sun, 23/12/2018 - 11:41

Hi there! pharmacies online great site http://onlinepharmacycanadaus.com

Shawnnut (not verified)

Sun, 23/12/2018 - 11:40

Hi there! pharmacies online great site http://onlinepharmacycanadaus.com

Shawnnut (not verified)

Sun, 23/12/2018 - 11:38

Hi there! pharmacies online great site http://onlinepharmacycanadaus.com

Shawncag (not verified)

Sat, 22/12/2018 - 22:43

Hi! non prescription pharmacy beneficial internet site http://onlinepharmacycanadaus.com

Shawncag (not verified)

Sat, 22/12/2018 - 22:39

Hi! non prescription pharmacy beneficial internet site http://onlinepharmacycanadaus.com

Shawncag (not verified)

Sat, 22/12/2018 - 20:45

Hello there! pharmacy technician schools online beneficial website http://onlinepharmacycanadaus.com

Shawncag (not verified)

Sat, 22/12/2018 - 20:41

Hello there! pharmacy technician schools online beneficial website http://onlinepharmacycanadaus.com

DonaldDootA (not verified)

Sat, 22/12/2018 - 13:46

Hello! online mexican pharmacy great web page http://onlinepharmacycanadaus.com

Ellarem (not verified)

Fri, 13/09/2019 - 10:08

Abuso De Kamagra Baclofene Nantes Buy Nosipren Prednisone 20mg Priligy Revenue

DonaldDootA (not verified)

Sat, 22/12/2018 - 13:41

Hello! online mexican pharmacy great web page http://onlinepharmacycanadaus.com

NathanHit (not verified)

Wed, 19/12/2018 - 06:03

Hello there! compare prescription drug prices very good site http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Wed, 19/12/2018 - 04:41

Hi there! cialis coupon for pharmacy very good website http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Wed, 19/12/2018 - 04:37

Hi there! cialis coupon for pharmacy very good website http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Wed, 19/12/2018 - 02:50

Howdy! canada pharmacies free shipping very good web site http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Wed, 19/12/2018 - 02:42

Howdy! canada pharmacies free shipping very good web site http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Wed, 19/12/2018 - 02:38

Howdy! canada pharmacies free shipping very good web site http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Tue, 18/12/2018 - 23:38

Hi! canadian pharmacies generic viagra great web site http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Tue, 18/12/2018 - 23:28

Hi! canadian pharmacies generic viagra great web site http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Tue, 18/12/2018 - 23:26

Hi! canadian pharmacies generic viagra great web site http://canadiantrustpharmacyhd.com

NathanHit (not verified)

Tue, 18/12/2018 - 23:24

Hi! canadian pharmacies generic viagra great web site http://canadiantrustpharmacyhd.com

Brettirrix (not verified)

Mon, 17/12/2018 - 06:15

tadacip 20 lexapro buspar lisinopril buy propecia online tadalafil 20mg lasix Torsemide buy real valtrex online prednisone tablets 5 mg

Brettirrix (not verified)

Mon, 17/12/2018 - 06:09

tadacip 20 lexapro buspar lisinopril buy propecia online tadalafil 20mg lasix Torsemide buy real valtrex online prednisone tablets 5 mg

AntioneGloro (not verified)

Mon, 17/12/2018 - 02:47

Hello there! buy tadalafil buy finasteride association cialis et viagra great web site

AntioneGloro (not verified)

Mon, 17/12/2018 - 00:51

Howdy! purchase tadalafil purchase finasteride cialis mail very good website

AntioneGloro (not verified)

Mon, 17/12/2018 - 00:48

Howdy! purchase tadalafil purchase finasteride cialis mail very good website

AntioneGloro (not verified)

Sun, 16/12/2018 - 22:05

Hello there! cialis buy propecia online without prescription viagra and cialis online excellent site

AntioneGloro (not verified)

Sun, 16/12/2018 - 22:00

Hello there! cialis buy propecia online without prescription viagra and cialis online excellent site

AntioneGloro (not verified)

Sun, 16/12/2018 - 21:59

Hello there! cialis buy propecia online without prescription viagra and cialis online excellent site

AntioneGloro (not verified)

Sun, 16/12/2018 - 21:57

Hello there! cialis buy propecia online without prescription viagra and cialis online excellent site

Wesleyemunc (not verified)

Sun, 16/12/2018 - 04:15

Hello there! order cialis finasteride cialis tablets india tablets 20 mg excellent web page

Wesleyemunc (not verified)

Sun, 16/12/2018 - 02:28

Hello! [url=http://cialissmx.com/]buy cialis usa[/url] [url=http://propeciausfinasteridhq.com/]buy finasteride[/url] [url=http://cialissmx.com/]cialis profesional india[/url] excellent web page

Wesleyemunc (not verified)

Sun, 16/12/2018 - 02:23

Hello! [url=http://cialissmx.com/]buy cialis usa[/url] [url=http://propeciausfinasteridhq.com/]buy finasteride[/url] [url=http://cialissmx.com/]cialis profesional india[/url] excellent web page

AshleyVaf (not verified)

Sat, 15/12/2018 - 15:05

Hello! where buy cialis order finasteride double dose of cialis good site

AshleyVaf (not verified)

Sat, 15/12/2018 - 14:59

Hello! where buy cialis order finasteride double dose of cialis good site

AshleyVaf (not verified)

Sat, 15/12/2018 - 13:16

Hi! purchase tadalafil buy propecia usa how can i buy cialis in a store in ct beneficial site

AshleyVaf (not verified)

Sat, 15/12/2018 - 13:12

Hi! purchase tadalafil buy propecia usa how can i buy cialis in a store in ct beneficial site

AshleyVaf (not verified)

Sat, 15/12/2018 - 05:30

Hi! purchase cialis online no prescription where buy propecia cialis 5mg daily dose price good web site

AshleyVaf (not verified)

Sat, 15/12/2018 - 04:08

Hi there! cialis finasteride cialis 100mg kaufen excellent web site

AshleyVaf (not verified)

Sat, 15/12/2018 - 04:07

Hi there! cialis finasteride cialis 100mg kaufen excellent web site

AshleyVaf (not verified)

Sat, 15/12/2018 - 02:11

Hello! buy cialis online without prescription buy propecia online cialis mg to take great web site

AshleyVaf (not verified)

Fri, 14/12/2018 - 23:08

Hello! buy tadalafil no prescription buy propecia medication cialis and levitra viagra price online beneficial internet site

Pages