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

JerryCoots (not verified)

Thu, 21/03/2019 - 15:47

Hello! online pharmacies excellent website.

JerryCoots (not verified)

Thu, 21/03/2019 - 15:42

Hello! online pharmacies excellent website.

JerryCoots (not verified)

Thu, 21/03/2019 - 15:28

Hello! online pharmacies excellent website.

JerryCoots (not verified)

Thu, 21/03/2019 - 15:22

Hello! online pharmacies excellent website.

JerryCoots (not verified)

Thu, 21/03/2019 - 11:06

Howdy! online pharmacies good internet site.

JerryCoots (not verified)

Thu, 21/03/2019 - 10:58

Howdy! online pharmacies good internet site.

JerryCoots (not verified)

Thu, 21/03/2019 - 10:48

Howdy! online pharmacies good internet site.

MarvinNaw (not verified)

Tue, 19/03/2019 - 15:36

Hi! best canadian pharmacies shipping to usa very good internet site http://onlinepharmacygx.com

MarvinNaw (not verified)

Tue, 19/03/2019 - 15:18

Hi! best canadian pharmacies shipping to usa very good internet site http://onlinepharmacygx.com

YoungBem (not verified)

Tue, 19/03/2019 - 07:37

Howdy! viagra buy online + canadian pharmacy beneficial web page.

MarvinNaw (not verified)

Mon, 18/03/2019 - 23:36

Hi! internet pharmacy canada excellent web page http://onlinepharmacygx.com

YoungBem (not verified)

Mon, 18/03/2019 - 04:37

Hello! drugstore in canada great internet site.

YoungBem (not verified)

Mon, 18/03/2019 - 04:21

Hello! drugstore in canada great internet site.

YoungBem (not verified)

Mon, 18/03/2019 - 04:08

Hello! drugstore in canada great internet site.

RobertMah (not verified)

Tue, 12/03/2019 - 00:16

Hi! uk generic propecia 5 mg very good web page.

RobertMah (not verified)

Tue, 12/03/2019 - 00:04

Hi! uk generic propecia 5 mg very good web page.

RobertMah (not verified)

Mon, 11/03/2019 - 16:22

Hi! propecia 1 mg 5 mg great web page.

RobertMah (not verified)

Mon, 11/03/2019 - 16:18

Hi! propecia 1 mg 5 mg great web page.

RobertMah (not verified)

Mon, 11/03/2019 - 16:15

Hi! propecia 1 mg 5 mg great web page.

RobertMah (not verified)

Mon, 11/03/2019 - 16:09

Hi! propecia 1 mg 5 mg great web page.

RobertMah (not verified)

Mon, 11/03/2019 - 09:11

Hello there! generic drugs propecia from india good web site.

RobertMah (not verified)

Mon, 11/03/2019 - 08:52

Hello there! generic drugs propecia from india good web site.

RobertMah (not verified)

Mon, 11/03/2019 - 08:48

Hello there! generic drugs propecia from india good web site.

RobertMah (not verified)

Mon, 11/03/2019 - 08:45

Hello there! generic drugs propecia from india good web site.

RobertMah (not verified)

Mon, 11/03/2019 - 02:02

Hello there! propecia singapore great web page.

RobertMah (not verified)

Mon, 11/03/2019 - 01:44

Hello there! propecia singapore great web page.

RobertMah (not verified)

Sun, 10/03/2019 - 17:56

Hello there! comprare propecia senza ricetta great internet site.

RobertMah (not verified)

Sun, 10/03/2019 - 17:44

Hello there! comprare propecia senza ricetta great internet site.

RobertMah (not verified)

Sun, 10/03/2019 - 17:37

Hello there! comprare propecia senza ricetta great internet site.

RobertMah (not verified)

Sun, 10/03/2019 - 09:41

Hi! propecia online pharmacy mastercard beneficial site.

RobertMah (not verified)

Sun, 10/03/2019 - 09:37

Hi! propecia online pharmacy mastercard beneficial site.

RobertMah (not verified)

Sun, 10/03/2019 - 09:32

Hi! propecia online pharmacy mastercard beneficial site.

RobertMah (not verified)

Sun, 10/03/2019 - 09:20

Hi! propecia online pharmacy mastercard beneficial site.

RobertMah (not verified)

Sun, 10/03/2019 - 01:39

Hi! propecia price very good website.

Robertdom (not verified)

Sat, 09/03/2019 - 15:48

Howdy! prednisone online pharmacy good internet site.

Robertdom (not verified)

Sat, 09/03/2019 - 07:12

Hi there! nabp online pharmacies very good internet site.

Robertdom (not verified)

Fri, 08/03/2019 - 22:51

Hello there! best online pharmacys beneficial site.

Robertdom (not verified)

Fri, 08/03/2019 - 05:18

Hello there! desoxyn online pharmacy excellent site.

Robertdom (not verified)

Fri, 08/03/2019 - 04:49

Hello there! desoxyn online pharmacy excellent site.

Robertdom (not verified)

Thu, 07/03/2019 - 20:35

Hello! american online pharmacy very good website.

Robertdom (not verified)

Thu, 07/03/2019 - 11:46

Hi there! cymbalta online pharmacy beneficial internet site.

Robertdom (not verified)

Thu, 07/03/2019 - 11:40

Hi there! cymbalta online pharmacy beneficial internet site.

Robertdom (not verified)

Thu, 07/03/2019 - 11:34

Hi there! cymbalta online pharmacy beneficial internet site.

Robertdom (not verified)

Thu, 07/03/2019 - 11:26

Hi there! cymbalta online pharmacy beneficial internet site.

AllenSmafe (not verified)

Wed, 06/03/2019 - 08:55

Hello! online pharmacy hydrocodone great site.

AllenSmafe (not verified)

Wed, 06/03/2019 - 08:42

Hello! online pharmacy hydrocodone great site.

AllenSmafe (not verified)

Wed, 06/03/2019 - 00:24

Hi there! kroger online pharmacy good internet site.

AllenSmafe (not verified)

Wed, 06/03/2019 - 00:21

Hi there! kroger online pharmacy good internet site.

AllenSmafe (not verified)

Wed, 06/03/2019 - 00:16

Hi there! kroger online pharmacy good internet site.

AllenSmafe (not verified)

Wed, 06/03/2019 - 00:14

Hi there! kroger online pharmacy good internet site.

Pages