Microsoft SQL Server is the DBMS of the Year

Microsoft SQL Server had its ups and downs throughout the years in our ranking,
but 2016 was exceptionally good,regaining ground towards Oracle, the leading system
in the ranking. The release of SQL Server 2016 and the announcement to port
SQL Server to Linux has certainly helped to curb interest in that product.

 

SQL Server is particularly strong in the ranking categories job offers and LinkedIn profiles,
but it scores very well also in all the other ranking components such as Search Engine hits,
Google Trends and StackOverflow discussions.

  1.     Method of calculating the scores of the DB-Engines Ranking
  2.     Number of mentions of the system on websites, measured as number of results
    in search engines queries.
  3.     General interest in the system. For this measurement, we use the frequency
    of searches in Google Trends.
  4.     Frequency of technical discussions about the system.
  5.     Number of job offers, in which the system is mentioned.
  6.     Number of profiles in professional networks, in which the system is mentioned.
  7.     Relevance in social networks. We count the number of Twitter tweets, in which the
    system is mentioned.

SQL Server connection details

Get All connection details for instance.

Use this query

;with cte as (select conn.session_id,sess.[host_name],sess.[program_name],sess.client_interface_name,
sess.login_name,sess.nt_domain,sess.nt_user_name,conn.auth_scheme,conn.client_net_address,
conn.num_reads,conn.num_writes,conn.client_tcp_port,sproc.cmd,databaseid=sproc.[dbid]
from sys.dm_exec_connections conn inner join
sys.dm_exec_sessions  sess on conn.session_id=sess.session_id
inner join sys.sysprocesses  sproc on sess.session_id=sproc.spid
)
select DatabaseName=db1.name,cte.*   from cte inner join sys.sysdatabases db1 on cte.databaseid=db1.dbid
and session_id>=48 and cte.databaseid>4
order by DatabaseName

 

 

SQL Server – Calculate Row Size in a Table

Following Scripts will calculate rowsize for every table.

;with cte as (
SELECT TableName=a.name,SizeInBytes=sum(a.max_length)
FROM (
SELECT SysTab.NAME,SysCol.MAX_LENGTH
FROM SYS.COLUMNS SysCol inner join sys.tables SysTab on SysCol.object_id = SysTab.object_id
UNION ALL
SELECT SysTab.NAME,SysCol.MAX_LENGTH
FROM SYS.INDEXES SysInd INNER JOIN SYS.TABLES SysTab ON SysInd.OBJECT_ID = SysTab.OBJECT_ID
inner join sys.columns SysCol on SysCol.object_id = SysTab.object_id
inner join sys.index_columns IC on SysCol.object_id = IC.object_id
and SysCol.column_id = IC.column_id ) a
group by a.name

)
select TableName, SizeInBytes, case when SizeInBytes>8060 then 1 else 0 end Spill  from CTE.

There is an additional column by name Spill in case the row size is exceeding 8060 bytes which is max a page can hold, anything beyond that will need that record to be accommodated in another page.

Refer to http://sqlnuts.com/2015/09/05/sql-server-understanding-data-density/ to know more about page.

Thanks.

SQL Server – Setup Database E-mail using – T-SQL

By Navratan Verma

Let us quickly learn how to setup database email by using your gmail account.  (Applicable to SQL 2005 onwards)
Download Script Here. SetupDB_Email

Enable the Database email feature.

SetupEmail1

Setup Profile and add account.

SetupEmail2

In case of gmail make sure you enable the option Allow less secure apps : ON
under Sign in and Security -> Connected apps and Sites.

Emailsetup5

Send the Test Email.

SetupEmail3

Finally do a cleanup, remove Logs using Date parameter relevant to your time and delete account and profile.

SetupEmail4

Thanks.

SQL Server – Understanding data density

By Navratan Verma

Density – The degree of compactness of a substance or the quantity of people or things in a given area or space.
Let us try to understand in this data point of view.  Download the script Here BufferUsage

Data in SQL Server world is stored in pages of 8KB each.

DataPage1

Now let us take a step back and understand how data is stored, read and displayed to the user.
As we know that data is stored in table which are in turn stored as pages of 8 KB each, assume that we have a
table Addressbook which has ID,FirstName,LastName,PresentAddress,PermanentAddress,DOB.

Data will be stored in in the table based on the data types chosen and loaded into buffer pool whenever a
select statement is issued against the table then display to the user. The data (pages) will remain loaded into
the buffer pool thus occupying certain space based on the no of records in that table or result.

Coming back to density if the space in the page is not utilized correctly in other words if more space is left free
in the page the condition is called lower density.

Causes of low density in pages are

  1. Tables with fixed sized rows.
  2. Page splits caused by random updates or inserts.
  3. Data deletions and pages free not re-used due to insert patterns.

Demonstration below (using fixed and variable sized rows) – I am creating a two versions of table Addressbook with different data types and inserting few thousand records.

DataPage2

Let us examine the table after inserting records using sys.dm_db_index_physical_stats

DataPage3

Although both tables have same no of rows you can observe the difference in data pages and avg_page_space_used_inpercent.

Let us select data from these two tables and see the Buffer count.

DataPage4

These are the issues caused by low data density

  • High buffer pool memory needed to store additional pages.
  • Additional I/O to read extra data pages in memory.
  • Additional disk space used to store same amount of data

Possible Solutions

  • Proper table design
  • Proper fill factor to avoid Page splits
  • Proper index re-build or re-organize strategy
  • Evaluating usage of data compression

Thanks

 

 

SQL Server – Buffer Pool Usage

By Navratan Verma

When we give a simple select from a table (eg: select * from Person.person) what happens behind the scenes is SQL Server tries to see if the required data is present in the Memory (Buffer Pool) if not the same data (page) is retrieved from the disk and copied into the SQL buffer pool and the same is presented to the user as result and cached for later use.

All the pages cached can be retrieved using the DMV sys.dm_os_buffer_descriptors applicable for SQL 2005 onwards. Download Script Here Bufferpool

Here are few examples. ( I had run few random select statements on the databases in my Test system)
This display Database wise cache size in Buffer pool.

Buffer1

Display Objects names and buffer size for a particular database.

Buffer2

These results helps us understand how much memory each database is using in the buffer pool.  The second result shows the amount of memory is being used by individual indexes in the current database.  This information can be helpful in case you want to implement data compression available in SQL 2008 onwards only in Enterprise Edition.

 

SQL Server – Advanced scanning

By Navratan Verma

SQL Server Advanced scanning is also known as “Merry-go-round” scan.
This feature is available only in Enterprise Edition of SQL Server introduced in SQL 2005.

Scenario – Multiple scan on the same table.

  • First scan starts, second scan joins in the middle, later scans the rest

The advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

At that point, the first execution plan has the complete results of a scan, but the second execution plan must still retrieve the data pages that were read before it joined the in-progress scan. The scan for the second execution plan then wraps back to the first data page of the table and scans forward to where it joined the first scan. Any number of scans can be combined like this. The Database Engine will keep looping through the data pages until it has completed all the scans. This mechanism is also called “merry-go-round scanning” and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.

For example, assume that you have a table with 500,000 pages. User A executes a Transact-SQL statement that requires a scan of the table. When that scan has processed 100,000 pages, User B executes another Transact-SQL statement that scans the same table. The Database Engine schedules one set of read requests for pages after 100,001, and passes the rows from each page back to both scans. When the scan reaches the 200,000th page, User C executes another Transact-SQL statement that scans the same table. Starting with page 200,001, the Database Engine passes the rows from each page it reads back to all three scans. After it reads the 500,000th row, the scan for User A is complete, and the scans for User B and User C wrap back and start to read the pages starting with page 1. When the Database Engine gets to page 100,000, the scan for User B is completed. The scan for User C then keeps going alone until it reads page 200,000. At this point, all the scans have been completed.

Without advanced scanning, each user would have to compete for buffer space and cause disk arm contention. The same pages would then be read once for each user, instead of read one time and shared by multiple users, slowing down performance and taxing resources.

SQL Server – Index Structure

By Navratan Verma

Let us see how is index structure stored in SQL Server, this demo uses SQL Server 2014.
Demo Script IndexStructure

Create the Database and Table.

IndexDatabase

Create clustered index, use DBCC Traceon(3604) to display output to window.
Then use undocumented command (SEMETADATA) to list pages for the table

IdxDemo2

Output from the above command, identify the root page ID from the field HoBtRoot

IdxDemo3

Display Root Page Info from DBCC Page

IdxDemo4

Pick one of the page to display its contents ex: 288 here, using DBCC Page.

IdxDemo5

You can also see actual page data stored in the leaf level page, in the above command.