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

NormanAbota (not verified)

Wed, 27/02/2019 - 02:10

Hello! buy valacyclovir pills buy propecia usa legitimate canadian mail order pharmacies beneficial web page

NormanAbota (not verified)

Tue, 26/02/2019 - 20:40

Howdy! buy valacyclovir buy finasteride no prescription overseas pharmacies that deliver to usa good website

NormanAbota (not verified)

Tue, 26/02/2019 - 20:24

Howdy! buy valacyclovir buy finasteride no prescription overseas pharmacies that deliver to usa good website

NormanAbota (not verified)

Tue, 26/02/2019 - 15:02

Hello! purchase valacyclovir finasteride approved canadian pharmacies online good website

NormanAbota (not verified)

Tue, 26/02/2019 - 03:25

Hello! buy valtrex buy finasteride pills canadian pharmacies top best beneficial site

NormanAbota (not verified)

Tue, 26/02/2019 - 03:15

Hello! buy valtrex buy finasteride pills canadian pharmacies top best beneficial site

NormanAbota (not verified)

Tue, 26/02/2019 - 03:09

Hello! buy valtrex buy finasteride pills canadian pharmacies top best beneficial site

NormanAbota (not verified)

Mon, 25/02/2019 - 22:13

Hello there! buy valtrex pills online buy propecia with no prescription pharmacy without dr prescriptions excellent site

NormanAbota (not verified)

Mon, 25/02/2019 - 21:55

Hello there! buy valtrex pills online buy propecia with no prescription pharmacy without dr prescriptions excellent site

NormanAbota (not verified)

Mon, 25/02/2019 - 17:34

Hello there! generic viagra online pharmacy canadian online pharmacy no prior prescription required pharmacy great internet site

NormanAbota (not verified)

Mon, 25/02/2019 - 17:22

Hello there! generic viagra online pharmacy canadian online pharmacy no prior prescription required pharmacy great internet site

NormanAbota (not verified)

Mon, 25/02/2019 - 17:16

Hello there! generic viagra online pharmacy canadian online pharmacy no prior prescription required pharmacy great internet site

NormanAbota (not verified)

Mon, 25/02/2019 - 12:32

Hi! pharmacies online mexico pharmacy online top rated canadian pharmacies online very good website

NormanAbota (not verified)

Mon, 25/02/2019 - 07:25

Hi! online pharmacy online pharmacy technician schools discount rx great internet site

NormanAbota (not verified)

Mon, 25/02/2019 - 07:22

Hi! online pharmacy online pharmacy technician schools discount rx great internet site

NormanAbota (not verified)

Mon, 25/02/2019 - 07:15

Hi! online pharmacy online pharmacy technician schools discount rx great internet site

NormanAbota (not verified)

Mon, 25/02/2019 - 02:44

Hello there! cvs pharmacy application online reputable canadian online pharmacy international pharmacies that ship to the usa beneficial internet site

NormanAbota (not verified)

Mon, 25/02/2019 - 02:40

Hello there! cvs pharmacy application online reputable canadian online pharmacy international pharmacies that ship to the usa beneficial internet site

NormanAbota (not verified)

Sun, 24/02/2019 - 21:50

Hello! no prescription online pharmacy canadian pharmacy medicine mexico very good site

NormanAbota (not verified)

Sun, 24/02/2019 - 21:42

Hello! no prescription online pharmacy canadian pharmacy medicine mexico very good site

NormanAbota (not verified)

Sun, 24/02/2019 - 21:36

Hello! no prescription online pharmacy canadian pharmacy medicine mexico very good site

NormanAbota (not verified)

Sun, 24/02/2019 - 21:34

Hello! no prescription online pharmacy canadian pharmacy medicine mexico very good site

NormanAbota (not verified)

Sun, 24/02/2019 - 16:46

Hello! cgv online pharmacy canadian pharmacy viagra online online canadian pharmacies very good site

NormanAbota (not verified)

Sun, 24/02/2019 - 16:41

Hello! cgv online pharmacy canadian pharmacy viagra online online canadian pharmacies very good site

NormanAbota (not verified)

Sun, 24/02/2019 - 12:28

Howdy! online pharmacies in india no prescription online pharmacy canadian drugstore reviews good internet site

NormanAbota (not verified)

Sun, 24/02/2019 - 12:26

Howdy! online pharmacies in india no prescription online pharmacy canadian drugstore reviews good internet site

NormanAbota (not verified)

Sun, 24/02/2019 - 12:18

Howdy! online pharmacies in india no prescription online pharmacy canadian drugstore reviews good internet site

NormanAbota (not verified)

Sun, 24/02/2019 - 07:50

Hello! health pharmacy online cvs pharmacy online refill no prescription pharmacy beneficial web site

NormanAbota (not verified)

Sun, 24/02/2019 - 07:40

Hello! health pharmacy online cvs pharmacy online refill no prescription pharmacy beneficial web site

NormanAbota (not verified)

Sun, 24/02/2019 - 07:31

Hello! health pharmacy online cvs pharmacy online refill no prescription pharmacy beneficial web site

NormanAbota (not verified)

Sun, 24/02/2019 - 02:52

Howdy! testosterone online pharmacy cheap pharmacy online pharmacy online excellent internet site

NormanAbota (not verified)

Sun, 24/02/2019 - 02:50

Howdy! testosterone online pharmacy cheap pharmacy online pharmacy online excellent internet site

NormanAbota (not verified)

Sun, 24/02/2019 - 02:47

Howdy! testosterone online pharmacy cheap pharmacy online pharmacy online excellent internet site

NormanAbota (not verified)

Sun, 24/02/2019 - 02:43

Howdy! testosterone online pharmacy cheap pharmacy online pharmacy online excellent internet site

NormanAbota (not verified)

Sat, 23/02/2019 - 22:18

Hello there! online pharmacy no prescription walgreen online pharmacy pharmacy without dr prescriptions great web page

NormanAbota (not verified)

Sat, 23/02/2019 - 22:09

Hello there! online pharmacy no prescription walgreen online pharmacy pharmacy without dr prescriptions great web page

NormanAbota (not verified)

Sat, 23/02/2019 - 21:57

Hello there! online pharmacy no prescription walgreen online pharmacy pharmacy without dr prescriptions great web page

DanielRal (not verified)

Sat, 23/02/2019 - 15:51

Hello! buy cialis uk good web site.

DanielRal (not verified)

Sat, 23/02/2019 - 15:45

Hello! buy cialis uk good web site.

DanielRal (not verified)

Sat, 23/02/2019 - 15:39

Hello! buy cialis uk good web site.

DanielRal (not verified)

Sat, 23/02/2019 - 15:33

Hello! buy cialis uk good web site.

DanielRal (not verified)

Sat, 23/02/2019 - 11:25

Hi there! buy tadalafil pills excellent internet site.

DanielRal (not verified)

Sat, 23/02/2019 - 06:26

Howdy! buy tadalafil online great web site.

DanielRal (not verified)

Sat, 23/02/2019 - 02:02

Howdy! buy tadalafil no prescription good internet site.

DanielRal (not verified)

Fri, 22/02/2019 - 16:30

Hello there! buy tadalafil pills beneficial web page.

DanielRal (not verified)

Fri, 22/02/2019 - 11:10

Hello there! buy cialis medication great site.

DanielRal (not verified)

Fri, 22/02/2019 - 06:30

Hello! purchase cialis good internet site.

DanielRal (not verified)

Fri, 22/02/2019 - 06:11

Hello! purchase cialis good internet site.

DanielRal (not verified)

Fri, 22/02/2019 - 01:52

Howdy! cialis cheap excellent internet site.

DanielRal (not verified)

Fri, 22/02/2019 - 01:46

Howdy! cialis cheap excellent internet site.

Pages