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

DanielRal (not verified)

Fri, 22/02/2019 - 01:46

Howdy! cialis cheap excellent internet site.

DanielRal (not verified)

Fri, 22/02/2019 - 01:37

Howdy! cialis cheap excellent internet site.

DanielRal (not verified)

Thu, 21/02/2019 - 21:26

Hi there! order tadalafil great site.

DanielRal (not verified)

Thu, 21/02/2019 - 21:19

Hi there! order tadalafil great site.

DanielRal (not verified)

Thu, 21/02/2019 - 16:45

Hello there! purchase cialis excellent site.

DanielRal (not verified)

Thu, 21/02/2019 - 16:42

Hello there! purchase cialis excellent site.

DanielRal (not verified)

Thu, 21/02/2019 - 16:37

Hello there! purchase cialis excellent site.

DanielRal (not verified)

Thu, 21/02/2019 - 16:34

Hello there! purchase cialis excellent site.

DanielRal (not verified)

Thu, 21/02/2019 - 12:23

Hi there! buy tadalafil online good website.

DanielRal (not verified)

Thu, 21/02/2019 - 12:17

Hi there! buy tadalafil online good website.

DanielRal (not verified)

Thu, 21/02/2019 - 12:07

Hi there! buy tadalafil online good website.

DanielRal (not verified)

Thu, 21/02/2019 - 12:03

Hi there! buy tadalafil online good website.

DanielRal (not verified)

Thu, 21/02/2019 - 07:37

Hello there! buy cialis no rx good web page.

DanielRal (not verified)

Thu, 21/02/2019 - 07:29

Hello there! buy cialis no rx good web page.

DanielRal (not verified)

Thu, 21/02/2019 - 07:22

Hello there! buy cialis no rx good web page.

DanielRal (not verified)

Thu, 21/02/2019 - 07:18

Hello there! buy cialis no rx good web page.

DanielRal (not verified)

Thu, 21/02/2019 - 02:57

Hi! buy cialis online without prescription very good internet site.

DanielRal (not verified)

Thu, 21/02/2019 - 02:48

Hi! buy cialis online without prescription very good internet site.

DanielRal (not verified)

Thu, 21/02/2019 - 02:38

Hi! buy cialis online without prescription very good internet site.

DanielRal (not verified)

Wed, 20/02/2019 - 22:01

Hello! buy cialis usa great site.

ElmerReund (not verified)

Wed, 20/02/2019 - 09:56

Hi! buy generic cialis beneficial site http://cialisxonline.com

ElmerReund (not verified)

Wed, 20/02/2019 - 09:52

Hi! buy generic cialis beneficial site http://cialisxonline.com

BrianScors (not verified)

Wed, 13/02/2019 - 21:17

naturalne wybielanie zębów

http://pt.evsbyken.com/remedio-para-micose/micose-de-unhas-tratamento/

JamesIsogy (not verified)

Sun, 10/02/2019 - 18:39

Hello! pharmacies online very good site http://canadiantrustpharmacyio.com

JamesIsogy (not verified)

Sun, 10/02/2019 - 18:34

Hello! pharmacies online very good site http://canadiantrustpharmacyio.com

JamesIsogy (not verified)

Sun, 10/02/2019 - 18:32

Hello! pharmacies online very good site http://canadiantrustpharmacyio.com

JamesIsogy (not verified)

Sun, 10/02/2019 - 18:22

Hello! pharmacies online very good site http://canadiantrustpharmacyio.com

Jamescic (not verified)

Sun, 10/02/2019 - 14:46

Howdy! buy cialis pills beneficial website.

Jamescic (not verified)

Sun, 10/02/2019 - 14:39

Howdy! buy cialis pills beneficial website.

Jamescic (not verified)

Sun, 10/02/2019 - 10:23

Howdy! purchase cialis beneficial site.

Jamescic (not verified)

Sun, 10/02/2019 - 10:15

Howdy! purchase cialis beneficial site.

Jamescic (not verified)

Sun, 10/02/2019 - 10:10

Howdy! purchase cialis beneficial site.

Jamescic (not verified)

Sun, 10/02/2019 - 06:06

Hi there! buy cialis online without prescription beneficial website.

Jamescic (not verified)

Sun, 10/02/2019 - 05:58

Hi there! buy cialis online without prescription beneficial website.

Jamescic (not verified)

Sun, 10/02/2019 - 01:46

Hi! buy cialis usa excellent site.

Jamescic (not verified)

Sun, 10/02/2019 - 01:33

Hi! buy cialis usa excellent site.

Jamescic (not verified)

Sun, 10/02/2019 - 01:24

Hi! buy cialis usa excellent site.

Jamescic (not verified)

Sat, 09/02/2019 - 21:28

Hello! buy generic cialis great internet site.

Jamescic (not verified)

Sat, 09/02/2019 - 20:59

Hello! buy generic cialis great internet site.

Jamescic (not verified)

Sat, 09/02/2019 - 16:40

Hello! cialis excellent internet site.

Jamescic (not verified)

Sat, 09/02/2019 - 16:28

Hello! cialis excellent internet site.

Jamescic (not verified)

Sat, 09/02/2019 - 12:04

Hi! purchase cialis very good site.

Jamescic (not verified)

Sat, 09/02/2019 - 11:52

Hi! purchase cialis very good site.

Jamescic (not verified)

Sat, 09/02/2019 - 11:43

Hi! purchase cialis very good site.

Jamescic (not verified)

Sat, 09/02/2019 - 11:38

Hi! purchase cialis very good site.

Jamescic (not verified)

Sat, 09/02/2019 - 07:38

Hi there! cialis cheap good website.

Jamescic (not verified)

Sat, 09/02/2019 - 07:29

Hi there! cialis cheap good website.

Jamescic (not verified)

Sat, 09/02/2019 - 07:23

Hi there! cialis cheap good website.

Jamescic (not verified)

Sat, 09/02/2019 - 07:18

Hi there! cialis cheap good website.

Jamescic (not verified)

Sat, 09/02/2019 - 03:09

Hello there! cialis cheap good site.

Pages