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

BrianDib (not verified)

Tue, 13/08/2019 - 06:11

pet pharmacy online generic viagra online canadian pharmacy online pharmacies canada

BrianDib (not verified)

Tue, 13/08/2019 - 06:09

pet pharmacy online generic viagra online canadian pharmacy online pharmacies canada

BrianDib (not verified)

Tue, 13/08/2019 - 06:00

pet pharmacy online generic viagra online canadian pharmacy online pharmacies canada

Brianrib (not verified)

Tue, 13/08/2019 - 03:01

best online pharmacy xanax online pharmacy technician course best canadian pharmacy online

Brianrib (not verified)

Tue, 13/08/2019 - 02:53

best online pharmacy xanax online pharmacy technician course best canadian pharmacy online

Brianrib (not verified)

Tue, 13/08/2019 - 02:49

best online pharmacy xanax online pharmacy technician course best canadian pharmacy online

Brianrib (not verified)

Tue, 13/08/2019 - 02:40

best online pharmacy xanax online pharmacy technician course best canadian pharmacy online

Brettirrix (not verified)

Mon, 12/08/2019 - 22:22

medrol

Brettirrix (not verified)

Mon, 12/08/2019 - 21:58

medrol

BrianDib (not verified)

Mon, 12/08/2019 - 04:55

online pharmacy technician training legal online pharmacy walmart online pharmacy

Brianrib (not verified)

Mon, 12/08/2019 - 04:47

pharmacy online usa legal online pharmacy provigil online pharmacy

BrianDib (not verified)

Mon, 12/08/2019 - 04:46

online pharmacy technician training legal online pharmacy walmart online pharmacy

Brianrib (not verified)

Mon, 12/08/2019 - 04:43

pharmacy online usa legal online pharmacy provigil online pharmacy

BrianDib (not verified)

Mon, 12/08/2019 - 04:42

online pharmacy technician training legal online pharmacy walmart online pharmacy

Brianrib (not verified)

Mon, 12/08/2019 - 04:40

pharmacy online usa legal online pharmacy provigil online pharmacy

Brianrib (not verified)

Mon, 12/08/2019 - 04:36

pharmacy online usa legal online pharmacy provigil online pharmacy

JamaalRuify (not verified)

Sun, 11/08/2019 - 15:55

buy propecia online buy finasteride online buy propecia no rx

JamaalRuify (not verified)

Sun, 11/08/2019 - 15:49

buy propecia online buy finasteride online buy propecia no rx

JamaalRuify (not verified)

Sun, 11/08/2019 - 15:45

buy propecia online buy finasteride online buy propecia no rx

JamaalRuify (not verified)

Sun, 11/08/2019 - 15:42

buy propecia online buy finasteride online buy propecia no rx

Michaelbesse (not verified)

Sun, 11/08/2019 - 07:29

http://imitrex-sumatriptan.com/ - buy imitrex cheap buy imitrex pills buy generic imitrex

Michaelbesse (not verified)

Sun, 11/08/2019 - 07:27

http://imitrex-sumatriptan.com/ - buy imitrex cheap buy imitrex pills buy generic imitrex

Michaelbesse (not verified)

Sun, 11/08/2019 - 07:25

http://imitrex-sumatriptan.com/ - buy imitrex cheap buy imitrex pills buy generic imitrex

Michaelbesse (not verified)

Sun, 11/08/2019 - 07:22

http://imitrex-sumatriptan.com/ - buy imitrex cheap buy imitrex pills buy generic imitrex

Stewartskist (not verified)

Sun, 11/08/2019 - 02:46

lexapro lasix albendazole prednisolone buy furosemide Prednisone albuterol nebulizer

Brettirrix (not verified)

Sat, 10/08/2019 - 19:14

buy metformin amoxicillin 875 trazodone purchase celexa

JamaalRuify (not verified)

Sat, 10/08/2019 - 18:17

where buy propecia order propecia buy propecia no rx

JamaalRuify (not verified)

Sat, 10/08/2019 - 18:12

where buy propecia order propecia buy propecia no rx

JamaalRuify (not verified)

Sat, 10/08/2019 - 18:09

where buy propecia order propecia buy propecia no rx

JamaalRuify (not verified)

Sat, 10/08/2019 - 18:02

where buy propecia order propecia buy propecia no rx

Michaelbesse (not verified)

Sat, 10/08/2019 - 06:53

http://imitrex-sumatriptan.com/ - order imitrex buy imitrex pills buy imitrex online without prescription

Michaelbesse (not verified)

Sat, 10/08/2019 - 06:48

http://imitrex-sumatriptan.com/ - order imitrex buy imitrex pills buy imitrex online without prescription

Michaelbesse (not verified)

Sat, 10/08/2019 - 06:41

http://imitrex-sumatriptan.com/ - order imitrex buy imitrex pills buy imitrex online without prescription

Michaelbesse (not verified)

Sat, 10/08/2019 - 06:35

http://imitrex-sumatriptan.com/ - order imitrex buy imitrex pills buy imitrex online without prescription

JamaalRuify (not verified)

Fri, 09/08/2019 - 20:54

buy propecia medication purchase propecia online no prescription buy propecia

JamaalRuify (not verified)

Fri, 09/08/2019 - 20:51

buy propecia medication purchase propecia online no prescription buy propecia

JamaalRuify (not verified)

Fri, 09/08/2019 - 20:45

buy propecia medication purchase propecia online no prescription buy propecia

Michaelbesse (not verified)

Fri, 09/08/2019 - 06:16

http://imitrex-sumatriptan.com/ - buy imitrex online buy imitrex cheap buy imitrex with no prescription

Michaelbesse (not verified)

Fri, 09/08/2019 - 06:11

http://imitrex-sumatriptan.com/ - buy imitrex online buy imitrex cheap buy imitrex with no prescription

Michaelbesse (not verified)

Fri, 09/08/2019 - 06:06

http://imitrex-sumatriptan.com/ - buy imitrex online buy imitrex cheap buy imitrex with no prescription

Michaelbesse (not verified)

Fri, 09/08/2019 - 06:03

http://imitrex-sumatriptan.com/ - buy imitrex online buy imitrex cheap buy imitrex with no prescription

JamaalRuify (not verified)

Thu, 08/08/2019 - 23:39

purchase propecia online no prescription buy propecia online without prescription order finasteride

JamaalRuify (not verified)

Thu, 08/08/2019 - 23:37

purchase propecia online no prescription buy propecia online without prescription order finasteride

JamaalRuify (not verified)

Thu, 08/08/2019 - 23:34

purchase propecia online no prescription buy propecia online without prescription order finasteride

JamaalRuify (not verified)

Thu, 08/08/2019 - 23:32

purchase propecia online no prescription buy propecia online without prescription order finasteride

Stewartskist (not verified)

Thu, 08/08/2019 - 20:37

trazodone MEDICATION FUROSEMIDE buy furosemide 40 mg albenza generic doxycycline buy arimidex colchicine where to buy tetracycline online wellbutrin buy levaquin

Brettirrix (not verified)

Thu, 08/08/2019 - 10:44

albuterol sulfate clonidine 0.2mg sildalis without a prescription purchase lasix online sildenafil citrate online cialis advair

Michaelbesse (not verified)

Thu, 08/08/2019 - 05:52

http://imitrex-sumatriptan.com/ - where buy imitrex purchase imitrex purchase imitrex

Michaelbesse (not verified)

Thu, 08/08/2019 - 05:49

http://imitrex-sumatriptan.com/ - where buy imitrex purchase imitrex purchase imitrex

Michaelbesse (not verified)

Thu, 08/08/2019 - 05:44

http://imitrex-sumatriptan.com/ - where buy imitrex purchase imitrex purchase imitrex

Pages