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

Dorеismam (not verified)

Sun, 27/01/2019 - 15:41

free online casino games
resorts casino online
online casino
watch casino online free
online casino

Dorеismam (not verified)

Sun, 27/01/2019 - 15:22

free online casino games
resorts casino online
online casino
watch casino online free
online casino

Dorеismam (not verified)

Sun, 27/01/2019 - 15:14

free online casino games
resorts casino online
online casino
watch casino online free
online casino

BianscaNow (not verified)

Sat, 26/01/2019 - 16:44

free casino games online
online casino no deposit bonus
online casino
online casino real money
casino movie online

BianscaNow (not verified)

Sat, 26/01/2019 - 16:41

free casino games online
online casino no deposit bonus
online casino
online casino real money
casino movie online

BianscaNow (not verified)

Sat, 26/01/2019 - 16:37

free casino games online
online casino no deposit bonus
online casino
online casino real money
casino movie online

BianscaNow (not verified)

Sat, 26/01/2019 - 16:33

free casino games online
online casino no deposit bonus
online casino
online casino real money
casino movie online

Albertvox (not verified)

Thu, 24/01/2019 - 20:06

where buy cialis
buy cialis no rx
purchase cialis online no prescription
purchase cialis online no prescription
tadalafil

Albertvox (not verified)

Thu, 24/01/2019 - 16:53

order tadalafil
buy cialis no prescription
buy cialis medication
buy cialis no rx
order tadalafil

Albertvox (not verified)

Thu, 24/01/2019 - 16:51

order tadalafil
buy cialis no prescription
buy cialis medication
buy cialis no rx
order tadalafil

Albertvox (not verified)

Thu, 24/01/2019 - 16:49

order tadalafil
buy cialis no prescription
buy cialis medication
buy cialis no rx
order tadalafil

Albertvox (not verified)

Thu, 24/01/2019 - 16:48

order tadalafil
buy cialis no prescription
buy cialis medication
buy cialis no rx
order tadalafil

AngolaSek (not verified)

Wed, 23/01/2019 - 19:45

online casino real money no deposit
watch casino online
online casino no deposit bonus
online casino real money
casino online

AngolaSek (not verified)

Wed, 23/01/2019 - 19:40

online casino real money no deposit
watch casino online
online casino no deposit bonus
online casino real money
casino online

AngolaSek (not verified)

Wed, 23/01/2019 - 19:37

online casino real money no deposit
watch casino online
online casino no deposit bonus
online casino real money
casino online

Joafnsquah (not verified)

Tue, 22/01/2019 - 12:48

free online casino games
free online casino
online casino real money
online casino
online casino reviews

Joafnsquah (not verified)

Tue, 22/01/2019 - 12:46

free online casino games
free online casino
online casino real money
online casino
online casino reviews

Joafnsquah (not verified)

Tue, 22/01/2019 - 12:39

free online casino games
free online casino
online casino real money
online casino
online casino reviews

KrastalRes (not verified)

Sun, 20/01/2019 - 21:02

free online casino games
online casino real money
online casino games
online casino
free online casino slots

KrastalRes (not verified)

Sun, 20/01/2019 - 20:59

free online casino games
online casino real money
online casino games
online casino
free online casino slots

KrastalRes (not verified)

Sun, 20/01/2019 - 20:53

free online casino games
online casino real money
online casino games
online casino
free online casino slots

RobertPug (not verified)

Sat, 19/01/2019 - 16:01

Hello there! clomid online pharmacy buy valtrex 100 mg planet drugs direct beneficial web site

RobertPug (not verified)

Sat, 19/01/2019 - 12:48

Hello there! walgreens online pharmacy buy valtrex usa international pharmacies that ship to the usa very good web page

Brettirrix (not verified)

Sat, 19/01/2019 - 09:52

prednisolone

Showtus (not verified)

Fri, 18/01/2019 - 14:23

In 2019 there are lot of movies are going to be released. Obviously, there are Avengers part 2, new Star Wars Episode nine and many others. These are films that every person wants to see, but sometimes you can miss it.It is where applications come in handy you can watch a movies for free. Btw, looking for the best app to watch free movies on your Android phone? Then your should check Showbox APK. This is the most famous app today that has a big library of tv shows and films. This app is also available for Windows users. But your need to download it first to enjoy free films Showbox download on PC

Showtus (not verified)

Fri, 18/01/2019 - 14:21

In 2019 there are lot of movies are going to be released. Obviously, there are Avengers part 2, new Star Wars Episode nine and many others. These are films that every person wants to see, but sometimes you can miss it.It is where applications come in handy you can watch a movies for free. Btw, looking for the best app to watch free movies on your Android phone? Then your should check Showbox APK. This is the most famous app today that has a big library of tv shows and films. This app is also available for Windows users. But your need to download it first to enjoy free films Showbox download on PC

Showtus (not verified)

Fri, 18/01/2019 - 14:20

In 2019 there are lot of movies are going to be released. Obviously, there are Avengers part 2, new Star Wars Episode nine and many others. These are films that every person wants to see, but sometimes you can miss it.It is where applications come in handy you can watch a movies for free. Btw, looking for the best app to watch free movies on your Android phone? Then your should check Showbox APK. This is the most famous app today that has a big library of tv shows and films. This app is also available for Windows users. But your need to download it first to enjoy free films Showbox download on PC

Showtus (not verified)

Fri, 18/01/2019 - 14:17

In 2019 there are lot of movies are going to be released. Obviously, there are Avengers part 2, new Star Wars Episode nine and many others. These are films that every person wants to see, but sometimes you can miss it.It is where applications come in handy you can watch a movies for free. Btw, looking for the best app to watch free movies on your Android phone? Then your should check Showbox APK. This is the most famous app today that has a big library of tv shows and films. This app is also available for Windows users. But your need to download it first to enjoy free films Showbox download on PC

NorvaPiego (not verified)

Fri, 18/01/2019 - 14:12

free casino games online
casino online
online casino real money no deposit
free online casino
casino games online

NorvaPiego (not verified)

Fri, 18/01/2019 - 14:11

free casino games online
casino online
online casino real money no deposit
free online casino
casino games online

NorvaPiego (not verified)

Fri, 18/01/2019 - 14:08

free casino games online
casino online
online casino real money no deposit
free online casino
casino games online

RobertPug (not verified)

Fri, 18/01/2019 - 04:02

Howdy! online pet pharmacy buy generic valtrex approved canadian online pharmacies good web site

RobertPug (not verified)

Fri, 18/01/2019 - 03:56

Howdy! online pet pharmacy buy generic valtrex approved canadian online pharmacies good web site

RobertPug (not verified)

Fri, 18/01/2019 - 00:45

Hi! india online pharmacy buy valtrex pills medications canada excellent site

KevinTag (not verified)

Thu, 17/01/2019 - 00:46

Hi there! buy propecia no rx online pharmacy review forum osu cialis pagamento paypal excellent web site

KevinTag (not verified)

Thu, 17/01/2019 - 00:41

Hi there! buy propecia no rx online pharmacy review forum osu cialis pagamento paypal excellent web site

KevinTag (not verified)

Thu, 17/01/2019 - 00:38

Hi there! buy propecia no rx online pharmacy review forum osu cialis pagamento paypal excellent web site

KevinTag (not verified)

Thu, 17/01/2019 - 00:36

Hi there! buy propecia no rx online pharmacy review forum osu cialis pagamento paypal excellent web site

KevinTag (not verified)

Wed, 16/01/2019 - 03:28

Hello! finasteride accutane online pharmacy cialis ou viagra beneficial web page

KevinTag (not verified)

Wed, 16/01/2019 - 00:28

Howdy! buy finasteride international online pharmacy daily cialis without a prescription beneficial web page

KevinTag (not verified)

Wed, 16/01/2019 - 00:19

Howdy! buy finasteride international online pharmacy daily cialis without a prescription beneficial web page

KevinTag (not verified)

Wed, 16/01/2019 - 00:11

Howdy! buy finasteride international online pharmacy daily cialis without a prescription beneficial web page

ThomasLox (not verified)

Tue, 15/01/2019 - 20:46

modafinil online pharmacy
generic viagra online pharmacy
online pharmacies canada review
best online pharmacy review
target pharmacy online

ThomasLox (not verified)

Tue, 15/01/2019 - 20:42

modafinil online pharmacy
generic viagra online pharmacy
online pharmacies canada review
best online pharmacy review
target pharmacy online

ThomasLox (not verified)

Tue, 15/01/2019 - 17:27

tramadol pharmacy online
viagra online canada pharmacy
best online pharmacy viagra
online discount pharmacy
online pharmacy no prescription

ThomasLox (not verified)

Tue, 15/01/2019 - 17:22

tramadol pharmacy online
viagra online canada pharmacy
best online pharmacy viagra
online discount pharmacy
online pharmacy no prescription

ThomasLox (not verified)

Tue, 15/01/2019 - 17:17

tramadol pharmacy online
viagra online canada pharmacy
best online pharmacy viagra
online discount pharmacy
online pharmacy no prescription

ThomasLox (not verified)

Tue, 15/01/2019 - 17:07

tramadol pharmacy online
viagra online canada pharmacy
best online pharmacy viagra
online discount pharmacy
online pharmacy no prescription

ntaletpaaj (not verified)

Tue, 15/01/2019 - 12:19

generico do viagra da neo quimica comprar generico de viagra en andorra http://writeessayhtfd.com

ntaletpaaj (not verified)

Tue, 15/01/2019 - 12:15

generico do viagra da neo quimica comprar generico de viagra en andorra http://writeessayhtfd.com

Pages