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

Jamescic (not verified)

Sat, 09/02/2019 - 03:02

Hello there! cialis cheap good site.

Jamescic (not verified)

Sat, 09/02/2019 - 02:58

Hello there! cialis cheap good site.

Jamescic (not verified)

Sat, 09/02/2019 - 02:54

Hello there! cialis cheap good site.

Jamescic (not verified)

Fri, 08/02/2019 - 22:49

Hi! cialis cheap great web site.

Jamescic (not verified)

Fri, 08/02/2019 - 22:45

Hi! cialis cheap great web site.

Jamescic (not verified)

Fri, 08/02/2019 - 22:33

Hi! cialis cheap great web site.

Jamescic (not verified)

Fri, 08/02/2019 - 18:10

Howdy! buy cialis medication beneficial web page.

Jamescic (not verified)

Fri, 08/02/2019 - 13:47

Hello there! buy cialis online beneficial web site.

Jamescic (not verified)

Fri, 08/02/2019 - 13:36

Hello there! buy cialis online beneficial web site.

Jamescic (not verified)

Fri, 08/02/2019 - 13:31

Hello there! buy cialis online beneficial web site.

Jamescic (not verified)

Fri, 08/02/2019 - 09:13

Hello there! buy generic cialis great site.

Jamescic (not verified)

Fri, 08/02/2019 - 08:58

Hello there! buy generic cialis great site.

turgylet (not verified)

Fri, 08/02/2019 - 07:34

www.ahaonline.org dhatpa.net ef-taxservice.com irsbusters.org amishshowcase.com trashtruck.com transcanada-limited.us hurtback.com deeharrisrd.com cowboytop.org.uk orgill.com.cn flight1pc.com nippy.ask-oxford.net www.hallacon.com vicinti.com intellisum.com razortechnology.com blacklistdatabase.com sextpanthers.com www.peerlessaviation.com adfornepal.com nouril.com www.nagsheadrealty.com noovietix.com www.24livenewspaper.com www.citysysco.com awkum.com vickertainment.com www.ansproston.com ihatelaurelcars.org hhr.pro-basketball-reference.com piano-fox.net www.jeffersoncountyfl.gov www.boysandgirlsclubs.org vivajeep.com www.sapbymukesh.com irishpermanentintl.net

Jamescic (not verified)

Fri, 08/02/2019 - 04:34

Hello there! buy cialis medication good site.

Jamescic (not verified)

Fri, 08/02/2019 - 04:32

Hello there! buy cialis medication good site.

Jamescic (not verified)

Fri, 08/02/2019 - 04:27

Hello there! buy cialis medication good site.

Jamescic (not verified)

Fri, 08/02/2019 - 00:27

Hello! order tadalafil excellent web site.

Jamescic (not verified)

Fri, 08/02/2019 - 00:24

Hello! order tadalafil excellent web site.

Jamescic (not verified)

Fri, 08/02/2019 - 00:19

Hello! order tadalafil excellent web site.

Jamescic (not verified)

Fri, 08/02/2019 - 00:11

Hello! order tadalafil excellent web site.

Jamescic (not verified)

Thu, 07/02/2019 - 19:55

Hi there! cialis very good site.

Jamescic (not verified)

Thu, 07/02/2019 - 19:49

Hi there! cialis very good site.

Jamescic (not verified)

Thu, 07/02/2019 - 19:45

Hi there! cialis very good site.

Jamescic (not verified)

Thu, 07/02/2019 - 19:42

Hi there! cialis very good site.

Jamescic (not verified)

Thu, 07/02/2019 - 15:50

Hello! [url=http://onlineuspharmacies.party/#pharcharmy-online-no-precipitation]online pharmacy technician programs[/url] great website.

Jamescic (not verified)

Thu, 07/02/2019 - 15:48

Hello! [url=http://onlineuspharmacies.party/#pharcharmy-online-no-precipitation]online pharmacy technician programs[/url] great website.

Jamescic (not verified)

Thu, 07/02/2019 - 15:45

Hello! [url=http://onlineuspharmacies.party/#pharcharmy-online-no-precipitation]online pharmacy technician programs[/url] great website.

Jamescic (not verified)

Thu, 07/02/2019 - 15:42

Hello! [url=http://onlineuspharmacies.party/#pharcharmy-online-no-precipitation]online pharmacy technician programs[/url] great website.

Jamescic (not verified)

Thu, 07/02/2019 - 11:59

Howdy! testosterone online pharmacy great web page.

Jamescic (not verified)

Thu, 07/02/2019 - 11:53

Howdy! testosterone online pharmacy great web page.

Jamescic (not verified)

Thu, 07/02/2019 - 11:46

Howdy! testosterone online pharmacy great web page.

Jamescic (not verified)

Thu, 07/02/2019 - 07:57

Hi! online pharmacy technician school very good web page.

Jamescic (not verified)

Thu, 07/02/2019 - 07:39

Hi! online pharmacy technician school very good web page.

Jamescic (not verified)

Thu, 07/02/2019 - 07:29

Hi! online pharmacy technician school very good web page.

Jamescic (not verified)

Thu, 07/02/2019 - 03:37

Howdy! ritalin online pharmacy excellent internet site.

Jamescic (not verified)

Thu, 07/02/2019 - 03:30

Howdy! ritalin online pharmacy excellent internet site.

Jamescic (not verified)

Thu, 07/02/2019 - 03:22

Howdy! ritalin online pharmacy excellent internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 23:37

Hello! pharmacy technician course online excellent internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 23:31

Hello! pharmacy technician course online excellent internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 23:25

Hello! pharmacy technician course online excellent internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 19:09

Hello there! online pharmacy oxycontin excellent web site.

Jamescic (not verified)

Wed, 06/02/2019 - 18:59

Hello there! online pharmacy oxycontin excellent web site.

Jamescic (not verified)

Wed, 06/02/2019 - 18:50

Hello there! online pharmacy oxycontin excellent web site.

Jamescic (not verified)

Wed, 06/02/2019 - 15:18

Hello! legal online pharmacy great site.

Jamescic (not verified)

Wed, 06/02/2019 - 11:39

Hi! universal online pharmacy beneficial site.

Jamescic (not verified)

Wed, 06/02/2019 - 08:19

Hi! canada pharmacy no prescription beneficial internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 08:15

Hi! canada pharmacy no prescription beneficial internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 08:12

Hi! canada pharmacy no prescription beneficial internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 08:08

Hi! canada pharmacy no prescription beneficial internet site.

Jamescic (not verified)

Wed, 06/02/2019 - 05:09

Hi! online pharmacy hydrocodone great website.

Pages