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

Haroldguics (not verified)

Wed, 04/09/2019 - 11:32

pharmacy technician classes online prescription drugs online pharmacy technician class online

Haroldguics (not verified)

Wed, 04/09/2019 - 11:29

pharmacy technician classes online prescription drugs online pharmacy technician class online

LarryJourn (not verified)

Mon, 02/09/2019 - 08:49

pharmacy degrees online pharmacy tech schooling online online pharmacy viagra

LarryJourn (not verified)

Mon, 02/09/2019 - 08:43

pharmacy degrees online pharmacy tech schooling online online pharmacy viagra

Josephmaymn (not verified)

Mon, 02/09/2019 - 08:42

mexican online pharmacy cvs pharmacy online online pharmacy viagra

Josephmaymn (not verified)

Mon, 02/09/2019 - 08:41

mexican online pharmacy cvs pharmacy online online pharmacy viagra

LarryJourn (not verified)

Mon, 02/09/2019 - 08:37

pharmacy degrees online pharmacy tech schooling online online pharmacy viagra

Josephmaymn (not verified)

Mon, 02/09/2019 - 08:35

mexican online pharmacy cvs pharmacy online online pharmacy viagra

Louiscut (not verified)

Sun, 01/09/2019 - 23:12

buy cialis online buy cialis no rx buy tadalafil online/#buy cialis

Louiscut (not verified)

Sun, 01/09/2019 - 23:10

buy cialis online buy cialis no rx buy tadalafil online/#buy cialis

Louiscut (not verified)

Sun, 01/09/2019 - 23:08

buy cialis online buy cialis no rx buy tadalafil online/#buy cialis

Louiscut (not verified)

Sun, 01/09/2019 - 23:04

buy cialis online buy cialis no rx buy tadalafil online/#buy cialis

Curtisarime (not verified)

Sat, 31/08/2019 - 20:40

buy sildenafil buy viagra cheap buy viagra medication

Curtisarime (not verified)

Sat, 31/08/2019 - 20:33

buy sildenafil buy viagra cheap buy viagra medication

Curtisarime (not verified)

Sat, 31/08/2019 - 20:30

buy sildenafil buy viagra cheap buy viagra medication

Louiscut (not verified)

Sat, 31/08/2019 - 18:28

buy cialis online without prescription buy cialis usa buy cialis no rx/#purchase cialis online no prescription

Louiscut (not verified)

Sat, 31/08/2019 - 18:20

buy cialis online without prescription buy cialis usa buy cialis no rx/#purchase cialis online no prescription

Louiscut (not verified)

Sat, 31/08/2019 - 18:16

buy cialis online without prescription buy cialis usa buy cialis no rx/#purchase cialis online no prescription

Louiscut (not verified)

Sat, 31/08/2019 - 18:14

buy cialis online without prescription buy cialis usa buy cialis no rx/#purchase cialis online no prescription

Brettirrix (not verified)

Fri, 30/08/2019 - 07:28

buy vibramycin

StevFreell (not verified)

Thu, 29/08/2019 - 15:37

Buy Cipro 500mg Allergia Propecia viagra online prescription Propecia Disfunzione Erettile

StevFreell (not verified)

Thu, 29/08/2019 - 15:34

Buy Cipro 500mg Allergia Propecia viagra online prescription Propecia Disfunzione Erettile

StevFreell (not verified)

Thu, 29/08/2019 - 15:28

Buy Cipro 500mg Allergia Propecia viagra online prescription Propecia Disfunzione Erettile

Jamiejella (not verified)

Wed, 28/08/2019 - 11:10

buy finasteride where buy propecia order propecia/#where buy propecia

Jamiejella (not verified)

Wed, 28/08/2019 - 11:06

buy finasteride where buy propecia order propecia/#where buy propecia

Jamiejella (not verified)

Wed, 28/08/2019 - 11:03

buy finasteride where buy propecia order propecia/#where buy propecia

Jamiejella (not verified)

Wed, 28/08/2019 - 10:59

buy finasteride where buy propecia order propecia/#where buy propecia

zetMarion (not verified)

Sun, 25/08/2019 - 15:51

Поршев Андрей Викторович мошенник
город Казань

Машина Камри госномер У033ВС116 вин XW7BF4FKX0S0007025
сервис Восстания 8А
дом Казань, ул. Лукина д.41, кв.126
мобильный 8(951)069-79-47

замена sti

этот человек-обманщик
обещает поставку любых деталей по приемлемой цене и исчезает
отключает телефон когда вы звоните
с ним лучше не заключать никаких договоренностей
он берет деньги и исчезает.

zetMarion (not verified)

Sun, 25/08/2019 - 15:44

Поршев Андрей Викторович мошенник
город Казань

Машина Камри госномер У033ВС116 вин XW7BF4FKX0S0007025
сервис Восстания 8А
дом Казань, ул. Лукина д.41, кв.126
мобильный 8(951)069-79-47

замена sti

этот человек-обманщик
обещает поставку любых деталей по приемлемой цене и исчезает
отключает телефон когда вы звоните
с ним лучше не заключать никаких договоренностей
он берет деньги и исчезает.

zetMarion (not verified)

Sun, 25/08/2019 - 15:40

Поршев Андрей Викторович мошенник
город Казань

Машина Камри госномер У033ВС116 вин XW7BF4FKX0S0007025
сервис Восстания 8А
дом Казань, ул. Лукина д.41, кв.126
мобильный 8(951)069-79-47

замена sti

этот человек-обманщик
обещает поставку любых деталей по приемлемой цене и исчезает
отключает телефон когда вы звоните
с ним лучше не заключать никаких договоренностей
он берет деньги и исчезает.

zetMarion (not verified)

Sun, 25/08/2019 - 15:37

Поршев Андрей Викторович мошенник
город Казань

Машина Камри госномер У033ВС116 вин XW7BF4FKX0S0007025
сервис Восстания 8А
дом Казань, ул. Лукина д.41, кв.126
мобильный 8(951)069-79-47

замена sti

этот человек-обманщик
обещает поставку любых деталей по приемлемой цене и исчезает
отключает телефон когда вы звоните
с ним лучше не заключать никаких договоренностей
он берет деньги и исчезает.

cwCiz (not verified)

Sun, 25/08/2019 - 14:34

cialis without prescription sildenafil citrate discount cialis

cwCiz (not verified)

Sun, 25/08/2019 - 14:30

cialis without prescription sildenafil citrate discount cialis

mhCiz (not verified)

Sun, 25/08/2019 - 13:16

cialis pills when is lyrica going generic generic cialis

mhCiz (not verified)

Sun, 25/08/2019 - 13:14

cialis pills when is lyrica going generic generic cialis

mhCiz (not verified)

Sun, 25/08/2019 - 13:10

cialis pills when is lyrica going generic generic cialis

mhCiz (not verified)

Sun, 25/08/2019 - 13:06

cialis pills when is lyrica going generic generic cialis

ffCiz (not verified)

Sun, 25/08/2019 - 12:22

cialis for sale finasteride cialis dosage

ffCiz (not verified)

Sun, 25/08/2019 - 12:19

cialis for sale finasteride cialis dosage

ffCiz (not verified)

Sun, 25/08/2019 - 12:14

cialis for sale finasteride cialis dosage

ffCiz (not verified)

Sun, 25/08/2019 - 12:11

cialis for sale finasteride cialis dosage

akCiz (not verified)

Sun, 25/08/2019 - 11:31

generic cialis tadalafil sildenafil cost acheter cialis

akCiz (not verified)

Sun, 25/08/2019 - 11:17

generic cialis tadalafil sildenafil cost acheter cialis

snCiz (not verified)

Sun, 25/08/2019 - 10:29

cialis price generic ed drugs buy cialis now

snCiz (not verified)

Sun, 25/08/2019 - 10:26

cialis price generic ed drugs buy cialis now

efCiz (not verified)

Sun, 25/08/2019 - 09:34

cialis coupon lipitor chemical formula cialis 5 mg

efCiz (not verified)

Sun, 25/08/2019 - 09:31

cialis coupon lipitor chemical formula cialis 5 mg

efCiz (not verified)

Sun, 25/08/2019 - 09:29

cialis coupon lipitor chemical formula cialis 5 mg

exCiz (not verified)

Sun, 25/08/2019 - 09:00

canadian cialis ed cure buy cialis

exCiz (not verified)

Sun, 25/08/2019 - 08:46

canadian cialis ed cure buy cialis

Pages