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

Douglasmilky (not verified)

Mon, 15/07/2019 - 18:17

Hello there! canadian online pharmacies great internet site.

Douglasmilky (not verified)

Mon, 15/07/2019 - 18:09

Hello there! canadian online pharmacies great internet site.

Douglasmilky (not verified)

Mon, 15/07/2019 - 18:05

Hello there! canadian online pharmacies great internet site.

Douglasmilky (not verified)

Mon, 15/07/2019 - 17:59

Hello there! canadian online pharmacies great internet site.

DanielEdicy (not verified)

Mon, 15/07/2019 - 17:32

Hello there! buy cialis cheap very good internet site.

DanielEdicy (not verified)

Mon, 15/07/2019 - 17:30

Hello there! buy cialis cheap very good internet site.

DanielEdicy (not verified)

Mon, 15/07/2019 - 17:27

Hello there! buy cialis cheap very good internet site.

DanielEdicy (not verified)

Mon, 15/07/2019 - 17:22

Hello there! buy cialis cheap very good internet site.

Douglasmilky (not verified)

Sun, 14/07/2019 - 21:10

Hi! accutane online pharmacy great website.

Douglasmilky (not verified)

Sun, 14/07/2019 - 21:07

Hi! accutane online pharmacy great website.

Douglasmilky (not verified)

Sun, 14/07/2019 - 21:02

Hi! accutane online pharmacy great website.

Douglasmilky (not verified)

Sun, 14/07/2019 - 21:00

Hi! accutane online pharmacy great website.

DanielEdicy (not verified)

Sun, 14/07/2019 - 20:31

Hi! purchase cialis very good web site.

DanielEdicy (not verified)

Sun, 14/07/2019 - 20:28

Hi! purchase cialis very good web site.

DanielEdicy (not verified)

Sun, 14/07/2019 - 20:25

Hi! purchase cialis very good web site.

DanielEdicy (not verified)

Sun, 14/07/2019 - 20:23

Hi! purchase cialis very good web site.

RebImparf (not verified)

Sun, 14/07/2019 - 11:09

Free Shipping Zentel Best Website Tablets Amex Accepted Buy Levitra Overnight Shipping viagra Sicher Viagra Kaufen Order Now On Line Doxycycline

RebImparf (not verified)

Sun, 14/07/2019 - 10:49

Free Shipping Zentel Best Website Tablets Amex Accepted Buy Levitra Overnight Shipping viagra Sicher Viagra Kaufen Order Now On Line Doxycycline

Douglasmilky (not verified)

Sun, 14/07/2019 - 00:17

Hi! online pharmacy mexico excellent internet site.

Douglasmilky (not verified)

Sun, 14/07/2019 - 00:10

Hi! online pharmacy mexico excellent internet site.

Douglasmilky (not verified)

Sun, 14/07/2019 - 00:03

Hi! online pharmacy mexico excellent internet site.

Douglasmilky (not verified)

Sun, 14/07/2019 - 00:00

Hi! online pharmacy mexico excellent internet site.

DanielEdicy (not verified)

Sat, 13/07/2019 - 23:57

Howdy! buy cialis medication very good site.

DanielEdicy (not verified)

Sat, 13/07/2019 - 23:49

Howdy! buy cialis medication very good site.

DanielEdicy (not verified)

Sat, 13/07/2019 - 23:45

Howdy! buy cialis medication very good site.

DanielEdicy (not verified)

Sat, 13/07/2019 - 23:39

Howdy! buy cialis medication very good site.

Brettirrix (not verified)

Sat, 13/07/2019 - 12:28

bupropion bupropion 150 mg propecia 5mg for sale cost of viagra amoxicillin drug

Brettirrix (not verified)

Sat, 13/07/2019 - 10:49

Tadalafil Online

Douglasmilky (not verified)

Sat, 13/07/2019 - 03:31

Hello! pharmacy technician online program very good internet site.

Douglasmilky (not verified)

Sat, 13/07/2019 - 03:27

Hello! pharmacy technician online program very good internet site.

DanielEdicy (not verified)

Sat, 13/07/2019 - 03:17

Hello there! buy cialis cheap very good internet site.

Douglasmilky (not verified)

Sat, 13/07/2019 - 03:14

Hello! pharmacy technician online program very good internet site.

DanielEdicy (not verified)

Sat, 13/07/2019 - 03:11

Hello there! buy cialis cheap very good internet site.

DanielEdicy (not verified)

Sat, 13/07/2019 - 03:04

Hello there! buy cialis cheap very good internet site.

Viagragow (not verified)

Fri, 12/07/2019 - 18:10

4s2t viagra without a doctor prescription pink viagra
w6p1 navigate to this web-site r74v
viagra and melanoma view website x1g9

Stewartskist (not verified)

Fri, 12/07/2019 - 18:04

Viagra synthroid synthroid trazodone hcl acyclovir PREDNISONE 40MG Buy Provera advair diskus

Stewartskist (not verified)

Fri, 12/07/2019 - 17:34

Viagra synthroid synthroid trazodone hcl acyclovir PREDNISONE 40MG Buy Provera advair diskus

Ernestotah (not verified)

Fri, 12/07/2019 - 14:57

Howdy! buy viagra great web page.

Ernestotah (not verified)

Fri, 12/07/2019 - 14:54

Howdy! buy viagra great web page.

Michaelexpor (not verified)

Fri, 12/07/2019 - 12:57

Howdy! hcg online pharmacy excellent website.

Michaelexpor (not verified)

Fri, 12/07/2019 - 12:54

Howdy! hcg online pharmacy excellent website.

Michaelexpor (not verified)

Fri, 12/07/2019 - 12:51

Howdy! hcg online pharmacy excellent website.

Michaelexpor (not verified)

Fri, 12/07/2019 - 12:47

Howdy! hcg online pharmacy excellent website.

RebImparf (not verified)

Fri, 12/07/2019 - 00:34

Best Price Cialis 20mg Doxycycline 100mg Tablets In Canada Levitra Erfahrungsaustausch [url=http://buyoxys.com]mail order levitra[/url] Erfahrungen 777 Tablets Propecia En Productos De Farmacia

RebImparf (not verified)

Fri, 12/07/2019 - 00:21

Best Price Cialis 20mg Doxycycline 100mg Tablets In Canada Levitra Erfahrungsaustausch [url=http://buyoxys.com]mail order levitra[/url] Erfahrungen 777 Tablets Propecia En Productos De Farmacia

Michaelexpor (not verified)

Thu, 11/07/2019 - 17:35

Hi there! legit online pharmacies beneficial web site.

Michaelexpor (not verified)

Thu, 11/07/2019 - 17:27

Hi there! legit online pharmacies beneficial web site.

Michaelexpor (not verified)

Thu, 11/07/2019 - 17:25

Hi there! legit online pharmacies beneficial web site.

Michaelexpor (not verified)

Thu, 11/07/2019 - 17:22

Hi there! legit online pharmacies beneficial web site.

RebImparf (not verified)

Thu, 11/07/2019 - 11:03

Par 701 Clomid For Sale Dhea Propecia cialis cheapest online prices Propecia A Diez Dias Cialis Mas Viagra

Pages