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

WarrenHob (not verified)

Tue, 26/03/2019 - 22:36

Howdy! best best online pharmacy forum great web site http://trustonlinepharmacies.com

WarrenHob (not verified)

Tue, 26/03/2019 - 22:31

Howdy! best best online pharmacy forum great web site http://trustonlinepharmacies.com

WarrenHob (not verified)

Tue, 26/03/2019 - 22:28

Howdy! best best online pharmacy forum great web site http://trustonlinepharmacies.com

Stevenhoart (not verified)

Tue, 26/03/2019 - 16:39

Hello! direct payday loan lenders online no teletrack good internet site.

Stevenhoart (not verified)

Tue, 26/03/2019 - 16:28

Hello! direct payday loan lenders online no teletrack good internet site.

Stevenhoart (not verified)

Tue, 26/03/2019 - 16:19

Hello! direct payday loan lenders online no teletrack good internet site.

ElmerReund (not verified)

Tue, 26/03/2019 - 10:10

Hello! [url=http://paydayloansps.website/]direct payday loans lenders only[/url] very good internet site http://paydayloansps.website

ElmerReund (not verified)

Tue, 26/03/2019 - 09:59

Hello! [url=http://paydayloansps.website/]direct payday loans lenders only[/url] very good internet site http://paydayloansps.website

Stevenhoart (not verified)

Tue, 26/03/2019 - 04:20

Hi there! payment direct payday loan phone number very good internet site.

Stevenhoart (not verified)

Tue, 26/03/2019 - 04:11

Hi there! payment direct payday loan phone number very good internet site.

Stevenhoart (not verified)

Mon, 25/03/2019 - 22:57

Hi there! no teletrack direct payday loans beneficial web site.

Stevenhoart (not verified)

Mon, 25/03/2019 - 22:53

Hi there! no teletrack direct payday loans beneficial web site.

Stevenhoart (not verified)

Mon, 25/03/2019 - 22:46

Hi there! no teletrack direct payday loans beneficial web site.

Jeremymub (not verified)

Sun, 24/03/2019 - 13:39

Hi! cialis daily very good site.

Jeremymub (not verified)

Sun, 24/03/2019 - 13:30

Hi! cialis daily very good site.

Jeremymub (not verified)

Sun, 24/03/2019 - 13:26

Hi! cialis daily very good site.

Jeremymub (not verified)

Sun, 24/03/2019 - 13:23

Hi! cialis daily very good site.

Jeremymub (not verified)

Sun, 24/03/2019 - 08:40

Hello! cialis free shipping great website.

Jeremymub (not verified)

Sun, 24/03/2019 - 08:34

Hello! cialis free shipping great website.

Jeremymub (not verified)

Sun, 24/03/2019 - 08:30

Hello! cialis free shipping great website.

Stadcinop (not verified)

Sun, 24/03/2019 - 07:54

free online casino
best online casino
free online casino slots
online casino real money
free online casino slots

turgylet (not verified)

Sun, 24/03/2019 - 05:16

http://kamagrainus.com - kamagra uk
kamagra online pharmacy

turgylet (not verified)

Sun, 24/03/2019 - 04:56

http://kamagrainus.com - kamagra uk
kamagra online pharmacy

turgylet (not verified)

Sun, 24/03/2019 - 04:52

http://kamagrainus.com - kamagra uk
kamagra online pharmacy

Jeremymub (not verified)

Sun, 24/03/2019 - 03:51

Hello there! free samples of cialis by mail excellent site.

Jeremymub (not verified)

Sun, 24/03/2019 - 03:34

Hello there! free samples of cialis by mail excellent site.

Jeremymub (not verified)

Sat, 23/03/2019 - 22:28

Hello! free samples of cialis good internet site.

Jeremymub (not verified)

Sat, 23/03/2019 - 22:18

Hello! free samples of cialis good internet site.

Jeremymub (not verified)

Sat, 23/03/2019 - 22:03

Hello! free samples of cialis good internet site.

PatrickBiore (not verified)

Sat, 23/03/2019 - 15:29

Hello there! cheap viagra at canadian pharmacy excellent internet site.

PatrickBiore (not verified)

Sat, 23/03/2019 - 10:09

Hello! generic pharmacy india great website.

PatrickBiore (not verified)

Sat, 23/03/2019 - 09:56

Hello! generic pharmacy india great website.

PatrickBiore (not verified)

Sat, 23/03/2019 - 09:52

Hello! generic pharmacy india great website.

PatrickBiore (not verified)

Sat, 23/03/2019 - 04:55

Hello! cheapest drug prices great site.

PatrickBiore (not verified)

Sat, 23/03/2019 - 04:47

Hello! cheapest drug prices great site.

PatrickBiore (not verified)

Sat, 23/03/2019 - 00:07

Hi there! cialas canadian pharmacies very good web page.

PatrickBiore (not verified)

Fri, 22/03/2019 - 23:59

Hi there! cialas canadian pharmacies very good web page.

PatrickBiore (not verified)

Fri, 22/03/2019 - 23:50

Hi there! cialas canadian pharmacies very good web page.

PatrickBiore (not verified)

Fri, 22/03/2019 - 13:04

Hello there! cialis for recreational use good site.

PatrickBiore (not verified)

Fri, 22/03/2019 - 12:48

Hello there! cialis for recreational use good site.

PatrickBiore (not verified)

Fri, 22/03/2019 - 06:58

Hello! [url=http://cialismpl.com/#cialis-online]cialis commercial actor[/url] beneficial internet site.

PatrickBiore (not verified)

Fri, 22/03/2019 - 00:02

Hello! manufactures of viagra cialis levitra online great website.

PatrickBiore (not verified)

Thu, 21/03/2019 - 23:54

Hello! manufactures of viagra cialis levitra online great website.

PatrickBiore (not verified)

Thu, 21/03/2019 - 23:49

Hello! manufactures of viagra cialis levitra online great website.

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.

Pages