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

JamaalRuify (not verified)

Thu, 08/08/2019 - 02:42

where buy propecia buy finasteride online order propecia

JamaalRuify (not verified)

Thu, 08/08/2019 - 02:39

where buy propecia buy finasteride online order propecia

Stewartskist (not verified)

Tue, 06/08/2019 - 11:06

tretinoin gel Nolvadex Atarax For Sale tetracycline pills buy prozac lexapro lisinopril 15 mg read more here lasix for sale AUGMENTIN

Stewartskist (not verified)

Tue, 06/08/2019 - 11:04

tretinoin gel Nolvadex Atarax For Sale tetracycline pills buy prozac lexapro lisinopril 15 mg read more here lasix for sale AUGMENTIN

Stewartskist (not verified)

Tue, 06/08/2019 - 11:01

tretinoin gel Nolvadex Atarax For Sale tetracycline pills buy prozac lexapro lisinopril 15 mg read more here lasix for sale AUGMENTIN

Stewartskist (not verified)

Tue, 06/08/2019 - 11:00

tretinoin gel Nolvadex Atarax For Sale tetracycline pills buy prozac lexapro lisinopril 15 mg read more here lasix for sale AUGMENTIN

Brettirrix (not verified)

Tue, 06/08/2019 - 03:32

where can you buy viagra mobic generic viagra no prescription cipralex

Brettirrix (not verified)

Tue, 06/08/2019 - 03:26

where can you buy viagra mobic generic viagra no prescription cipralex

Brettirrix (not verified)

Mon, 05/08/2019 - 22:08

Lexapro

Brettirrix (not verified)

Mon, 05/08/2019 - 22:06

Lexapro

Brettirrix (not verified)

Mon, 05/08/2019 - 22:04

Lexapro

Brettirrix (not verified)

Mon, 05/08/2019 - 22:03

Lexapro

WilliamRoura (not verified)

Mon, 05/08/2019 - 08:34

Howdy! http://onlinepharmaciescanadarx.com/#finasteride-online-pharmacy - canadian drugs beneficial website.

WilliamRoura (not verified)

Mon, 05/08/2019 - 08:32

Howdy! http://onlinepharmaciescanadarx.com/#finasteride-online-pharmacy - canadian drugs beneficial website.

WilliamRoura (not verified)

Mon, 05/08/2019 - 08:30

Howdy! http://onlinepharmaciescanadarx.com/#finasteride-online-pharmacy - canadian drugs beneficial website.

WilliamRoura (not verified)

Mon, 05/08/2019 - 08:27

Howdy! http://onlinepharmaciescanadarx.com/#finasteride-online-pharmacy - canadian drugs beneficial website.

WilliamRoura (not verified)

Sun, 04/08/2019 - 08:19

Howdy! http://onlinepharmaciescanadarx.com/#online-pharmacy-mexico - good online pharmacy reviews good web site.

WilliamRoura (not verified)

Sun, 04/08/2019 - 08:16

Howdy! http://onlinepharmaciescanadarx.com/#online-pharmacy-mexico - good online pharmacy reviews good web site.

WilliamRoura (not verified)

Sun, 04/08/2019 - 08:08

Howdy! http://onlinepharmaciescanadarx.com/#online-pharmacy-mexico - good online pharmacy reviews good web site.

WilliamRoura (not verified)

Sun, 04/08/2019 - 08:06

Howdy! http://onlinepharmaciescanadarx.com/#online-pharmacy-mexico - good online pharmacy reviews good web site.

WilliamRoura (not verified)

Sat, 03/08/2019 - 06:29

Hi! http://onlinepharmaciescanadarx.com/#online-pharmacy-no-prescription - online discount pharmacy good website.

WilliamRoura (not verified)

Sat, 03/08/2019 - 06:23

Hi! http://onlinepharmaciescanadarx.com/#online-pharmacy-no-prescription - online discount pharmacy good website.

WilliamRoura (not verified)

Sat, 03/08/2019 - 06:19

Hi! http://onlinepharmaciescanadarx.com/#online-pharmacy-no-prescription - online discount pharmacy good website.

WilliamRoura (not verified)

Sat, 03/08/2019 - 06:17

Hi! http://onlinepharmaciescanadarx.com/#online-pharmacy-no-prescription - online discount pharmacy good website.

Terrytor (not verified)

Sat, 03/08/2019 - 02:11

legitimate canadian online pharmacies pharmacies online canadian pharmacies online

Terrytor (not verified)

Sat, 03/08/2019 - 02:04

legitimate canadian online pharmacies pharmacies online canadian pharmacies online

Terrytor (not verified)

Sat, 03/08/2019 - 02:00

legitimate canadian online pharmacies pharmacies online canadian pharmacies online

Terrytor (not verified)

Sat, 03/08/2019 - 01:52

legitimate canadian online pharmacies pharmacies online canadian pharmacies online

WilliamRoura (not verified)

Fri, 02/08/2019 - 05:31

Hello! [url=http://onlinepharmaciescanadarx.com/#mexican-pharmacy-online]reliable online pharmacy[/url] good web page.

WilliamRoura (not verified)

Fri, 02/08/2019 - 05:30

Hello! [url=http://onlinepharmaciescanadarx.com/#mexican-pharmacy-online]reliable online pharmacy[/url] good web page.

WilliamRoura (not verified)

Fri, 02/08/2019 - 05:27

Hello! http://onlinepharmaciescanadarx.com/#mexican-pharmacy-online - reliable online pharmacy good web page.

WilliamRoura (not verified)

Fri, 02/08/2019 - 05:25

Hello! http://onlinepharmaciescanadarx.com/#mexican-pharmacy-online - reliable online pharmacy good web page.

PhiliptourB (not verified)

Fri, 02/08/2019 - 04:48

Hi there! walgreens online pharmacy very good internet site.

PhiliptourB (not verified)

Fri, 02/08/2019 - 04:46

Hi there! walgreens online pharmacy very good internet site.

PhiliptourB (not verified)

Fri, 02/08/2019 - 04:44

Hi there! walgreens online pharmacy very good internet site.

PhiliptourB (not verified)

Fri, 02/08/2019 - 04:42

Hi there! walgreens online pharmacy very good internet site.

Brettirrix (not verified)

Thu, 01/08/2019 - 09:38

vardenafil finasteride 1 mg buy colchicine albendazole generic for allopurinol diclofenac

DavidLag (not verified)

Thu, 01/08/2019 - 06:01

canada pharmacy online review best online pharmacy cialis online pharmacy

DavidLag (not verified)

Thu, 01/08/2019 - 05:48

canada pharmacy online review best online pharmacy cialis online pharmacy

DavidLag (not verified)

Thu, 01/08/2019 - 05:43

canada pharmacy online review best online pharmacy cialis online pharmacy

Brettirrix (not verified)

Thu, 01/08/2019 - 03:24

doxycycline

Brettirrix (not verified)

Thu, 01/08/2019 - 03:22

doxycycline

Brettirrix (not verified)

Thu, 01/08/2019 - 03:16

doxycycline

RodneySes (not verified)

Wed, 31/07/2019 - 22:09

Hi there! http://canadian-drugsale.com/ - online pharmacy technician canada online pharmacy tramadol pharmacy online

RodneySes (not verified)

Wed, 31/07/2019 - 22:08

Hi there! http://canadian-drugsale.com/ - online pharmacy technician canada online pharmacy tramadol pharmacy online

RodneySes (not verified)

Wed, 31/07/2019 - 22:06

Hi there! http://canadian-drugsale.com/ - online pharmacy technician canada online pharmacy tramadol pharmacy online

RodneySes (not verified)

Wed, 31/07/2019 - 22:03

Hi there! http://canadian-drugsale.com/ - online pharmacy technician canada online pharmacy tramadol pharmacy online

RodneySes (not verified)

Tue, 30/07/2019 - 15:59

Hi! http://cialisopharmacy.com/ - buy cialis online cialis cheap order tadalafil

RodneySes (not verified)

Tue, 30/07/2019 - 15:50

Hi! http://cialisopharmacy.com/ - buy cialis online cialis cheap order tadalafil

RodneySes (not verified)

Tue, 30/07/2019 - 15:47

Hi! http://cialisopharmacy.com/ - buy cialis online cialis cheap order tadalafil

Pages