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

Brettirrix (not verified)

Wed, 04/12/2019 - 22:53

albuterol buy generic lasix viagra online

Brettirrix (not verified)

Wed, 04/12/2019 - 22:42

albuterol buy generic lasix viagra online

Stewartskist (not verified)

Wed, 04/12/2019 - 20:58

cheap ventolin

Brettirrix (not verified)

Mon, 02/12/2019 - 23:39

viagra generic

Stewartskist (not verified)

Mon, 02/12/2019 - 23:11

www.tadalafil0l1.com

Brettirrix (not verified)

Thu, 28/11/2019 - 22:21

generic retin a
www.keflexcephalexin.com

Brettirrix (not verified)

Thu, 28/11/2019 - 22:11

http://www.sildalis365.us.com
zithromax
cheap anafranil
www.buynolvadex.us.com
generic silagra

Brettirrix (not verified)

Sat, 23/11/2019 - 00:42

http://sildenafila1c.com
allopurinol in renal failure

Brettirrix (not verified)

Thu, 21/11/2019 - 01:48

http://www.cialis0l1.com
allopurinol 300mh tab nor
http://www.cialisa1c.com
allopurinol pronunciation
www.propecia0l1.com
canadian pharmacy tadalafil viagara cialis

Brettirrix (not verified)

Thu, 21/11/2019 - 01:44

http://www.cialis0l1.com
allopurinol 300mh tab nor
http://www.cialisa1c.com
allopurinol pronunciation
www.propecia0l1.com
canadian pharmacy tadalafil viagara cialis

Brettirrix (not verified)

Thu, 21/11/2019 - 01:40

http://www.cialis0l1.com
allopurinol 300mh tab nor
http://www.cialisa1c.com
allopurinol pronunciation
www.propecia0l1.com
canadian pharmacy tadalafil viagara cialis

Stewartskist (not verified)

Tue, 19/11/2019 - 22:17

buy valtrex

Brettirrix (not verified)

Tue, 19/11/2019 - 00:43

lisinopril
generic tadalafil reviews
www.cialisa1c.com
30 mg prozac
http://www.zithromax0l1.com
if 100 mg viagra is not enough can i take two

Brettirrix (not verified)

Tue, 19/11/2019 - 00:34

lisinopril
generic tadalafil reviews
www.cialisa1c.com
30 mg prozac
http://www.zithromax0l1.com
if 100 mg viagra is not enough can i take two

Brettirrix (not verified)

Sun, 17/11/2019 - 01:37

generic xenical
when will cialis go generic in us
generic tadalafil
generic sildenafil
http://www.prednisone0l1.com
online ed prescriptions

Brettirrix (not verified)

Thu, 14/11/2019 - 05:06

http://cialis0l1.com
cialis and a fibrillation
http://zithromax0l1.com
tadalafil generic canada over the counter
http://www.xenical0l1.com
allopurinol uses how long before you see a result

Stewartskist (not verified)

Thu, 14/11/2019 - 04:52

www.metformin0l1.com

kwwsycoutr (not verified)

Tue, 12/11/2019 - 07:45

cialis for bph generic levitra online buy generic cialis online

kwwsycoutr (not verified)

Tue, 12/11/2019 - 07:42

cialis for bph generic levitra online buy generic cialis online

kwwsycoutr (not verified)

Tue, 12/11/2019 - 07:36

cialis for bph generic levitra online buy generic cialis online

fdpkmursbs (not verified)

Tue, 12/11/2019 - 06:08

cialis cost canadian online pharmacy cialis professional

fdpkmursbs (not verified)

Tue, 12/11/2019 - 06:05

cialis cost canadian online pharmacy cialis professional

fdpkmursbs (not verified)

Tue, 12/11/2019 - 06:01

cialis cost canadian online pharmacy cialis professional

rnshwjnwyg (not verified)

Tue, 12/11/2019 - 04:35

cialis from canada lipitor 20mg generic for cialis

rnshwjnwyg (not verified)

Tue, 12/11/2019 - 04:33

cialis from canada lipitor 20mg generic for cialis

rnshwjnwyg (not verified)

Tue, 12/11/2019 - 04:30

cialis from canada lipitor 20mg generic for cialis

wmxbhaonbt (not verified)

Tue, 12/11/2019 - 03:11

generic cialis 20mg erectile medicines levitra cialis 20mg

wmxbhaonbt (not verified)

Tue, 12/11/2019 - 03:03

generic cialis 20mg erectile medicines levitra cialis 20mg

wmxbhaonbt (not verified)

Tue, 12/11/2019 - 02:56

generic cialis 20mg erectile medicines levitra cialis 20mg

hrtiahcgvc (not verified)

Tue, 12/11/2019 - 01:38

cheap cialis online canadian pharmacy meds acheter cialis

hrtiahcgvc (not verified)

Tue, 12/11/2019 - 01:31

cheap cialis online canadian pharmacy meds acheter cialis

hrtiahcgvc (not verified)

Tue, 12/11/2019 - 01:24

cheap cialis online canadian pharmacy meds acheter cialis

orrlcykqmf (not verified)

Mon, 11/11/2019 - 23:55

canadian cialis cialis generic tadalafil cialis 5mg

orrlcykqmf (not verified)

Mon, 11/11/2019 - 23:50

canadian cialis cialis generic tadalafil cialis 5mg

orrlcykqmf (not verified)

Mon, 11/11/2019 - 23:48

canadian cialis cialis generic tadalafil cialis 5mg

orrlcykqmf (not verified)

Mon, 11/11/2019 - 23:46

canadian cialis cialis generic tadalafil cialis 5mg

dqsylortry (not verified)

Mon, 11/11/2019 - 22:17

cheap cialis online cialis generic tadalafil cialis for bph

dqsylortry (not verified)

Mon, 11/11/2019 - 22:15

cheap cialis online cialis generic tadalafil cialis for bph

dqsylortry (not verified)

Mon, 11/11/2019 - 22:13

cheap cialis online cialis generic tadalafil cialis for bph

dqsylortry (not verified)

Mon, 11/11/2019 - 22:10

cheap cialis online cialis generic tadalafil cialis for bph

LesKern (not verified)

Wed, 30/10/2019 - 16:19

Metformin 850 Cialis Y Levitra Genericos viagra Cafergot Available In Canada Amoxil Needs Refrigeration Cephalexin For Fish

LesKern (not verified)

Wed, 30/10/2019 - 16:09

Metformin 850 Cialis Y Levitra Genericos viagra Cafergot Available In Canada Amoxil Needs Refrigeration Cephalexin For Fish

Ellarem (not verified)

Tue, 22/10/2019 - 08:15

Generique Viagra Soft Vega H viagra Xenical Amazon Cheapest Canada Drugs Online Pharmacy

Ellarem (not verified)

Tue, 22/10/2019 - 08:08

Generique Viagra Soft Vega H viagra Xenical Amazon Cheapest Canada Drugs Online Pharmacy

oroavehcjf (not verified)

Sat, 05/10/2019 - 02:45

order cialis online erectile medicines levitra cialis without a prescription

oroavehcjf (not verified)

Sat, 05/10/2019 - 02:42

order cialis online erectile medicines levitra cialis without a prescription

oroavehcjf (not verified)

Sat, 05/10/2019 - 02:40

order cialis online erectile medicines levitra cialis without a prescription

oroavehcjf (not verified)

Sat, 05/10/2019 - 02:31

order cialis online erectile medicines levitra cialis without a prescription

gzaqpqzgtd (not verified)

Tue, 01/10/2019 - 19:05

cialis usa side effects of nexium cialis vs viagra

gzaqpqzgtd (not verified)

Tue, 01/10/2019 - 19:02

cialis usa side effects of nexium cialis vs viagra

Pages