Calculate sql row size

Question: How can I calculate the sql row size?

 Answer: Calculating the  row-level row size is complicated and challenging.  One of the key problems is SQL Server defines space at the extent level .

 An extent is made up of 8 x 8 KB physically  contiguous pages .  The  complication for calculating a row size is : Even if one row exists – for example 4 bytes , an extent has to be allocated.

Read this post for information on the difference between uniform and mixed extents - Disk IO performance , disk block size tuning and SQL Servers

 There is complicated formula on the MSDN Online - http://msdn.microsoft.com/en-us/library/ms189124.aspx to predict the space required to store data in a heap

There are some reliable methods of estimating  row sizes . Using DBCC SHOWCONTIG  , sys.dm_db_index_physical_stats  and SysColumns.

Each method offers a slightly different perspective  on row size.

 

 

---METHOD 1  : DBCC SHOWCONTIG

dbcc showcontig ('TableName') with tableresults

--check max min and average record size



--METHOD 2 : sys.dm_db_index_physical_stats
--dbcc showcontig is to be deprecated 
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'DatabaseName'), OBJECT_ID(N'TableName'), NULL, NULL , 'DETAILED')


--METHOD 3 : SysColumns

use db_name
go
create table ##tmpRowSize (TableName varchar(100),RowSizeDefinition int)
exec sp_msforeachtable 'INSERT INTO ##tmpRowSize Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '
select * from ##tmpRowSize order by RowSizeDefinition  desc
drop table ##tmpRowSize

 Read More

SQL Server - How to find the largest sql index and table size

SQL Server - Calculate table size with existing data

Storage Checklist FAQ

Amount of sql server processors with sys.dm_os_schedulers

I needed to establish the amount of processors available to a SQL Server. A SQL Server scheduler is mapped to a single logical processor, so using this query you can establish the amount of processors. Any scheduler_id < 255 is a scheduler.

It gets lightly more complicated if hyperthreading is enabled. . Find out from the server administrator , use a product  such as the coreinfo or this Powershell script  - Number of physical processors with Powershell – number of cores to establish if hyperthreading is turned on.

If hyperthreading is enabled – this equates to double the schedulers. 

SELECT * 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255;

 Read More

SOS_SCHEDULER_YIELD and how to reduce it - SQL Server DBA

Affinity Mask on SQL Server 2008 R2 - SQL Server DBA

How to report a SQL Server performance problem - SQL Server DBA

Monitor SQL Server TempDB usage

Large queries spilling into tempdb such as SQL Server – SORT IN TEMPDB and Sort Warnings  and large temporary tables can use Tempdb space very quickly and lead to outages. Use the DMVs : sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage

How to Troubleshoot

First Step - Comprehensive Tempdb Sizing exercise. There are many different methods  , such as : a) create a workload b) running large maintenance jobs e.g index rebuild

Second Step - monitoring Tempdb usage. As well as a  SQL Server monitoring alerts solution based on events , such as disk full, there are some useful DMVs  to drill into TempDB usage based on tasks and sessions.

sys.dm_db_file_space_usage -  Lists space usage information for each file in the Tempdb database. Reports on unallocated extent page count.

sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated

sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity

 

go
select * from  sys.dm_db_file_space_usage 
go 
select * from  sys.dm_db_session_space_usage
go
select * from sys.dm_db_task_space_usage

Read More

SQL Server – List current tempdb statements

SQL Server - TempDB performance and strategy checklist

SQL Server – tempdb and solid state drives

SQL Server - Shrink tempdb without sql server restart

 

SQL Server - Find last sql statement based on SPID

Find the last query based on a SPID can be useful . For example, a  client may be experiencing SQL performance issues  and the DBA needs to isolate the last run query.  There are three methods below  to return the query.

Before we explore the methods – you’ll need to obtain the SPID. The SPID is the session User ID.

Try:

--this returns your current session ID

SELECT @@SPID

--this returns list of all sessions-check the spid column
 sp_who

 

 Method 1 - DBCC INPUTBUFFER

-----------------------------

DBCC INPUTBUFFER  returns the last sql statement  issued by a client. The command requires the SPID

 

DBCC INPUTBUFFER (SPID)

 

Method 2 - SYS.SYSPROCESSES and SYS.DM_EXEC_SQL_TEXT

-----------------------------------

Obtain the sql_handle from sys.sysprocesses and use the sql_handle to obtain the sql text from the DMV sys.dm_exec_sql_text.

 

DECLARE @sqlhandle VARBINARY(64)
SELECT @sqlhandle = sql_handle from sys.sysprocesses where spid = 987
SELECT text FROM sys.dm_exec_sql_text(@sqlhandle)

 

Method 3 – SYS.FN_GET_SQL

---------------------------------

Use sys.fn_get_sql to return SQL statement text based on a SQL handle. Microsoft intend deleting this function in an unspecified future version of SQL Server          

 

DECLARE @sqlhandle varbinary(64);
SELECT @sqlhandle = sql_handle 
FROM sys.dm_exec_requests 
WHERE session_id = 987 and request_id = 0;
SELECT * FROM sys.fn_get_sql(@sqlhandle);

 Read More

dm_exec_sql_text and Display text of SQL - SQL Server DBA

SQL Server - SQL open transactions and how to find

SQL Server – Current queries executing on SQL Server

SQL Server – LATCH_EX AND how to reduce it

SQL Server Books online defines the SQL wait type LATCH_EX as “Occurs when waiting for a EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A breakdown of LATCH_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.”

 Note:

1)The definition mentions “does not include buffer latches or transaction mark latches”.  This suggests this wait type is not related to IO or data.

How to reduce it

1) Investigate the DMV sys.dm_os_latch_stats  for latch contention source. Most sources are esoteric and there is very little in-depth information available on most latch classes. A DBA with deep knowledge of SQL Server Internals can interpret the results .

2) Investigate for administration procedures which may cause an underlying contention. Latches are internal SQL Server and there is very little impact by the DBA. Latches are short lived and it’s difficult to find the correlation between processes and latch contention

One method of investigating is to monitor the sys.dm_exec_requests  DMV while running a DBA procedure you think may cause latch contention. Monitor the wait_type column

 Read More

Timeout occurred while waiting for latch - SQL Server DBA

SQL Server – Pageiolatch_sh and how to reduce it - SQL Server DBA

Identify Page Latch contention - SQL Server DBA