Linked Server Connectivity issue(KILLED/ROLLBACK status with PREEMPTIVE_OLEDBOPS wait stats)

Few Days ago, we had a particular issue with Linked Server. Before I start with the issue, let me give a small outline of the enviornment.

We have two servers A and B where a data porting is happening from A to B through a Linked server.Support person has manually invoked the job from the query analyser(SSMS). It was not a usual invoking instead as he faced some issues at connection, 1st run had failed at 45the minute and 2nd attempt failedat 6th minute. However the third attempt got succeeded in 2 hours time, for the attempt the person had kicked out and the query window got closed for first two attempts.

After the thrid successful attempt there were backgound activities running for the first two attempts with KILLED/ROLLBACK status. As it could be an issue with high transaction and a rollback we left thesituation to rollback to complete. However, after few days (4days), we again noticed that there were no changes in KILLED/rollback status and other processes has been blocked by these activity where it wasan usual behaviour and started analysing.

Our Analysis end up with few things as follows:
1. There were 2 SPIDs with status KILLED/ROLLBACK with PREEMPTIVE_OLEDBOPS wait stats in Server A.
2. These KILLED/ROLLBACK were blocking other processes in server A
3. Analysing the log growth on both servers A and B, it was steady at both servers.
4. The porting was based on PUSH method of Linked server(pushing the data from A to B which runs at Server A).
5. There were no blocking at Server B.
As the waitstats "PREEMPTIVE_OLEDBOPS", it was a strong indication of some issues outside SQL server, we then decided to restart the server. Upon restarting the machine,the server was back to normal and there were no blocking!!!

What would have happened???
From the above outcomes of the analysis, we were almost sure that the rollback has been completed successfully, however the rollback process were blocked on sending the acknoweldgement to server A from server B through OLEDB provider. In OLEDB Linked server, the transactions are happening at OLEDB throughITransactionJoin and ITransactionLocal interfaces. As there were some break in communication, the rollback at OLEDB did not happen completelty even the rollback of transactions had completed.

Fragmentation on Heap

Do you think a Heap would have a Page fragmentation? As long as a heap is not logically ordered, do you think a heap would undergo a page fragmentation???

Fragmentation happens when logical ordering is different from the physical ordering. A heap will not have any orders on its data.However Heaps are with "Forwarding Pointers"
Which would be really a mess in terms for the heaps' performance. When we modify a row and the row modified does not fit in the same page, a forwarding pointer will create moving the modified row to a new page and leaving a pointer to the old location which is called forwarding record. Forwarding record which points to the new location of the record is called forwarded record. This is for a better performance because all the non-clustered index on the heap do not have to be altered to the new location as long as there are forwarding records in the old location.

If we have more number of forwarding pointers, the read operation would go back and forth to get the rows in a heap. This would create a performance issues in the case of heaps.

How do we get rid of this?
Method A:
We can create a clustered index and drop the index would remove the forwarding pointers by creating a clustered index. However the removal of cluster index will retain the structure too. However there are some limitations/performance issues with this method:
1. Clustering a large heap is costly opeartion as it has to do SORTing.
2. Dropping the index has to update to reflect the Free space on each page.
Method B:
In SQL server 2008, it introduces a concept to rebuild the table. Rebuilding the table actually does a compression of pages in a heap(need to specify the compression option).
Alter table rebuild may cause rebuild of non-clustered indexes created on the heap.Bacause of the possibility to change the RIDs. Even though the non-clustered indexes are getting rebuilt, if we want to enforce the compression, we need to explicity compress the indexes using alter index with compression option.


--How to find out the forwarding pointers?
SELECT object_name(object_id) as Object, page_count,
avg_page_space_used_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID('DB_Name'), object_id ('Table_Name'), null, null, 'DETAILED')
Where IsNull(forwarded_record_count,0)<>0 and page_count>0;


Note: DBCC CLEANTABLE Will never clean up the forward pointers on a heap even though it reclaims the spce.

Procedure Header Comments

It is always good to have a clear precise header for stored procedure for tracking and understanding the functionality implemented. The following is the one I use in my procedures.Please comment if you can add more info to the same.
/**********************************
Author :
Created Date :
Stored Procedure Name :
Purpose :
Inputs :
Outputs :
Revision History
-----------------
Version No: Modified By: Modified Date: Comments:
***************************************************
V10 SQL Zealot 1. Comments for the change
***************************************************/

SSC - Questions And Answers

1. Delete and Left Join
http://www.sqlservercentral.com/Forums/Topic1112822-1292-1.aspx

Does rebuild/re-organize index update the statistics?

This is a very good question. I guess it can be a good question in interviews too.

Coming to the answer, am answering the question as two parts:

There are two types of statistics:Index stats(stats created on index) and column stats(stats created on individaul column)

1.Does rebuild index update the statistics?
Ans: Rebuild index will update statistics for the index, but not for the statistics of the columns.

2.Does re-organize index update the statistics?
Ans: Re-organize does not deal with statistics. Hence no update statistics.

Please refer the importance of stats here.

How to restrict number of connections to a database

Today, one of my friend at workstation asked me "How to restrict the number of users connected to a DB in a SQL server"?

Initially, my answer was as easy as to configure the user connection option to the required number using sp_configure.

sp_configure 'user connections',100
RECONFIGURE

There is a break!!!It can only be done at server level. What about at DB level???

From SQL Server 2005 sp2, there a feature called LOGON Trigger. How about using the feature???Yes, it is very well possible
using Logon trigger by rolling back the connections once it reached a maximum required connections.It is not at DB level, but through Login.

create trigger AuditLogin
on database_Name
with execute as self
for logon
as
begin
 IF ORIGINAL_LOGIN()= '< Login Name > ' AND
  (SELECT COUNT(*) FROM sys.dm_exec_sessions
   WHERE is_user_process = 1 AND
   original_login_name = '< Login Name > ) > 10
 ROLLBACK;
end

Common Best Practises

1. Never ever use Select * in your query.
- If you use *, the engine has to get all the column information from the table. If your table is huge and the column contain huge data, it may lead to IO bottleneck.
- There is a good prone to break your code if some one adds a column to the table when you use select * in insert statement.
- Try to get only the required information from the table.Take this as a first thumb rule.
2. Never use sp_ prefix to user procedures
- When a proc has been prefixed with sp_, the optimizer looks at the procedure in master db first and then flow goes to the current database. This unnecessary check can be eliminated by avoiding the sp_ prefix to all user procedures.
Note: Procedures can be prefixed with sp_ when it creates in master database that can be accessed through all other database.
3. Try to avoid functions with a join condition keys.
- When we use functions with join keys, the index created on the keys will not have any effect on the join.

4. Avoid Select count(*) to check record existence

-Record existence check has always to be replaced with exists one unless the check does not do anything to do with the data. Either (*) or (1) can be used in the statement inside of exists as it does not have any difference. As long as the query optimizer finda a value, optimizer quits from the execution satisfying the condition. Try with a larger table to get understand the difference between the logical reads.

Example:

/*------------------------
Declare @Count int
Select @Count = COUNT(1) From Test_Zealot_Count
If @Count>0
Select 'Proceed'
IF Exists(Select 1 From Test_Zealot_Count)
Select 'Proceed'
------------------------*/

Table 'Test_Zealot_Count'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

Table 'Test_Zealot_Count'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

Table Information

The below script provides a complete possible information on a table.
/*
First Resultset(Table Information)
Table Name
ObjectId
Table Type
Is replicated table or not
Row count of the table

Second Resultset(Column information)

Third Resultset(Index Information)
Name, Id, Seeks, scans, updates, lock information, type of index, constraint information, fill factor and partition number

Forth Resultset(Detail index information)
Fifth Resultset(Detail Statistics information)
Sixth Resultset(Detail relation key information)
*/

Declare @TableName Varchar(100)
Set @TableName = 'TableName'

Select @TableName,A.Object_Id,Case When A.index_id=0 Then 'Heap' Else 'Clustered Table' End ,
  Case When B.objId is null Then 'Not Replicated' Else 'Replicated' End Is_Replicated,C.row_count
  From sys.partitions A
  Left Join sys.dm_db_partition_stats C On A.object_id = C.object_id and c.index_id in(0,1)
  Left Join sysarticles B On A.object_id = B.Objid
  Where A.index_id <2 and A.object_id = Object_Id(@TableName)
  
Select column_id,name,TYPE_NAME(user_type_id),max_length,precision,scale,Case When Is_Nullable =0 Then 'No' Else 'Yes' End 
 , Is_Identity From sys.columns where object_id = OBJECT_ID(@TableName)

 
 Create Table #Temp_Index_Details
 (
  [Object_Id]  BigInt,
  [Object_Name] Varchar(100),
  Index_ID  Int,
  Index_Name  Varchar(100),
  User_Seeks  BigInt,
  User_Scans  BigInt,
  User_Lookups BigInt,
  Index_Rows  Bigint,
  User_Updates BigInt,
  Index_Lock_Attempt_Count BigInt,
  Index_Lock_Promotion_Count BigInt,
  page_lock_wait_count  BigInt,
  page_lock_wait_in_ms  BigInt,
  [FillFactor]  Int,
  Constraint_Type VarChar(100),
  Partition_Number BigInt
 ) 
 
 Insert Into #Temp_Index_Details ([Object_Id]  ,
  [Object_Name] ,
  Index_ID  ,
  Index_Name  ,
  User_Seeks  ,
  User_Scans  ,
  User_Lookups ,
  Index_Rows  ,
  User_Updates ,
  [FillFactor] ,
  Constraint_Type )
 SELECT u.object_id,OBJECT_NAME(u.object_id) , i.indid
 , i.name , u.user_seeks , u.user_scans
 , u.user_lookups , i.rowcnt , u.user_updates,i.OrigFillFactor
 , k.[type] AS [constraint type]
 FROM sys.dm_db_index_usage_stats u
 INNER JOIN sys.sysindexes i ON u.object_id = i.id AND u.index_id = i.indid
 LEFT OUTER JOIN sys.key_constraints k
 ON i.id = k.parent_object_id AND i.indid = k.unique_index_id
 WHERE u.database_id = db_id() and OBJECT_NAME(u.object_id)=@TableName
 ORDER BY OBJECT_NAME(u.object_id), i.name, u.user_updates DESC ;
 
 

 Update B Set B.Partition_Number = A.partition_number, 
    B.Index_Lock_Attempt_Count = index_lock_promotion_attempt_count,
    B.Index_Lock_Promotion_Count = A.index_lock_promotion_count ,
    B.page_lock_wait_count = A.page_lock_wait_count, 
    B.page_lock_wait_in_ms = A.page_lock_wait_in_ms
 FROM sys.dm_db_index_operational_stats
    (db_id('Cognizant20'), object_id(@TableName), NULL, NULL) A
    Inner Join #Temp_Index_Details B On A.object_id = B.Object_Id and A.index_id = B.Index_ID


 Select [Object_Name], Index_ID, Index_Name, User_Seeks, User_Scans, User_Lookups, Index_Rows, User_Updates,
   Index_Lock_Attempt_Count, Index_Lock_Promotion_Count, page_lock_wait_count, page_lock_wait_in_ms,
   [FillFactor], Constraint_Type, Partition_Number From #Temp_Index_Details

 Select A.name IndexName,A.index_id,c.name ColumnName,
   Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End
   ,A.type_desc,is_identity,is_included_column,is_replicated
   ,Is_Unique,Is_Primary_Key,IS_Unique_Constraint,Fill_factor,is_Padded,is_replicated,is_nullable,is_computed
   
 From sys.indexes A 
 Inner Join sys.index_columns B On A.object_id = B.object_id And A.index_id = B.index_id
 Inner Join sys.columns C On c.object_id = B.object_id  And C.column_id  = B.column_id
 Where A.Object_ID = OBJECT_ID(@TableName)
 order by A.Index_id,Is_included_Column,Key_ordinal asc
 
 --Stats information
 Select A.object_id,A.name,A.stats_id,C.column_id,C.name,A.auto_created,user_created,has_filter,STATS_DATE(A.object_id,A.stats_id) 
 From sys.stats A
 Inner Join sys.stats_columns B On A.object_id = B.object_id and A.stats_id = B.stats_id
 Inner Join sys.columns C On B.column_id = C.column_id and A.object_id = C.object_id
 where A.object_id = OBJECT_ID(@Tablename)


 
 --Primary Key and Foreign Key information
 SELECT tc.TABLE_NAME AS PrimaryKeyTable,
 tc.CONSTRAINT_NAME AS PrimaryKey,
 COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
 COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' and OBJECT_ID(tc.TABLE_NAME) = OBJECT_ID(@Tablename)
 ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
  
  
  Drop Table #Temp_Index_Details

Index size on a table

It is often a requirement to understand the size of indexes on a table.
Here is a script shared by one of my friend at my workspace... looks very useful!!!

DECLARE @OBJECT_NAME  VARCHAR(255) = 'trn_defect_tracking_history';
DECLARE @temp         TABLE
        (
            indexID BIGINT,
            objectId BIGINT,
            index_name NVARCHAR(MAX),
            used_page_count BIGINT,
            pages BIGINT
        )
--Insert into temp table
INSERT INTO @temp
SELECT P.index_id,
       P.OBJECT_ID,
       I.name,
       SUM(used_page_count),
       SUM(
           CASE 
                WHEN (p.index_id < 2) THEN (
                         in_row_data_page_count + lob_used_page_count + 
                         row_overflow_used_page_count
                     )
                ELSE lob_used_page_count + row_overflow_used_page_count
           END
       )
FROM   sys.dm_db_partition_stats P
       INNER JOIN sys.indexes I
            ON  I.index_id = P.index_id
            AND I.OBJECT_ID = P.OBJECT_ID
WHERE  p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
GROUP BY
       P.index_id,
       I.Name,
       P.OBJECT_ID;
SELECT index_name INDEX_NAME,
       LTRIM(
           STR(
               (
                   CASE 
                        WHEN used_page_count > pages THEN (used_page_count - pages)
                        ELSE 0
                   END
               ) * 8,
               15,
               0
           ) + ' KB'
       ) INDEX_SIZE
FROM   @temp T
GO

Dead lock Information from XML to Table Format

Here with a script to convert the XML dead lock file to a table format as we DBA love the table fundamentally...

declare @deadlock xml
set @deadlock = 'dead lock xml content'

select 
 [PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
 [DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
 [KeylockObject] = Keylock.Process.value('@objectname', 'varchar(200)'),
 [Index] = Keylock.Process.value('@indexname', 'varchar(200)'),
 [IndexLockMode] = Keylock.Process.value('@mode', 'varchar(5)'),
 [RIDLock] = RIDLock.Process.value('@objectname', 'varchar(200)'),
 [RIDLockMode] = RIDLock.Process.value('@mode', 'varchar(5)'),
 [Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
 [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
 [LockMode] = Deadlock.Process.value('@lockMode', 'varchar(3)'),
 [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
 [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
 [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
 [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
 [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
 [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
 from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
      LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/keylock') as Keylock(Process)
        ON Keylock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') = 
           Deadlock.Process.value('@id', 'varchar(50)')
      LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/ridlock') as RIDLock(Process)
        ON RIDLock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') = 
           Deadlock.Process.value('@id', 'varchar(50)')

Identity Saturation

Identity saturation is one of a major check we need to have as a DBA for a big projects.The below script would detail the curretn identity and the percentage used for all table objects of a database.

SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName, 
 c.name AS ColumnName,
 TYPE_NAME(c.system_type_id) AS 'DataType',
 IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
 CASE c.system_type_id
  WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 9223372036854775807
  WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 2147483647
  WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 32767
  WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 255
 END AS 'PercentageUsed' 
FROM sys.columns AS c 
 INNER JOIN
 sys.tables AS t 
 ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC

Hide/Show Tables from Object Explorer

Just thought of sharing an information about how to hide/show tables from your object explorer. The below query has to cut and paste in your query and “CNTRL+SHIFT+M” to input your parameters. One more thing… CNTRL+SHIFT+M work like a programming language giving us an opportunity to input the parameter in query analyser.

--To Hide the object

EXEC sp_addextendedproperty 

@name = N'microsoft_database_tools_support', 

@value = '<Hide , 1 Or 0, ?>', 

@level0type ='schema', 

@level0name ='<Schema Name, sysname, dbo>',

@level1type = 'table', 

@level1name = N'<Table Name, sysname, ?>'

--To Show the object

EXEC sp_dropextendedproperty 

@name = N'microsoft_database_tools_support', 

@level0type ='schema', 

@level0name ='<Schema Name, sysname, dbo>',

@level1type = 'table', 

@level1name = N'<Table Name, sysname, ?>'

Statistics - An Important Factor

Update statistics are important for SQL server database as the query plan generation heavily depends on the histogram of the data been passed for the very first execution. The DBA should have given with sysadmin fixed server role or ownership of the database.

When do I need a statistics update?

- If we see some drastic difference between Estimated number of records in Estimated and Actual plans, it shows a statistics update is much adviced.

(Caught very good stuff from Kim L Tripp's blog thought of worth mentioning...) Statistics are traditionally updated when roughly 20% (+ a minimum of 500 rows) of the data has changed. In SQL Server 200x (2000, 2005 and 2008) statistics are NOT immediately updated when the threshold is reached, instead they are invalidated. It's not until someone needs the statistic that SQL Server updates it. This reduces thrashing that occurred in SQL Server 7.0 when stats were updated immediately instead of just being invalidated. Another interesting point is what is meant by "20% of the data has changed?"... How is that defined? Is it based on updates to columns or inserts of rows? Of course the answer is... it depends - here, it depends on the version of SQL Server that you're using: SQL Server 2000 defines 20% as 20% of the ROWS have changed. You can see this in sysindexes.rcmodctr. SQL Server 2005/8 defines 20% as 20% of the COLUMN data has changed. You cannot see this unless you are accessing SQL Server through the DAC as it's in a base system table (2005: sysrowsetcolumns.rcmodified and for 2008: sysrscols.rcmodified).

There are two ways we can acheive the updates on statistics: a. Update Statistics b. sp_updatestats

Whats the difference between the above two?

Update statistics

- Runs statistics updates for the table given for all indexes and columns.

sp_updatestats

- Runs update statistics for all user defined and internal tables.

- Uses rowmodcntr to update the statistics using the 20% data changed.

It avoids unnecessary updates of statistics for unchanged rows. column statistics are never being updated by sp_updatestats unless it reaches its threshold.Column statistics have a major role in the creation of a query plan. Sometimes optimizer looks at the column statistics histogram when generates a query plan.So if we see any performance degradation in our production, it is always look at the statistics of a table and update if you feel any of cloumn statistics are a way out of up-to-date albeit the index statistics are up to date.

Select *,STATS_DATE(object_id,stats_id) 
From sys.stats where object_id = OBJECT_ID('')

Notes:

Update statistics uses tempdb heavily.As tempdb is a shared resorce and is often a bottleneck for large system, it would be great if we could run the update statistics at traffic time.When we do update statistics, internally it does the following generation which is causing lots of sort warnings:

SELECT StatMan([SC0], [SB0000]) FROM 
(SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] 
FROM (SELECT [cEndDateTime] AS [SC0] FROM [dbo].[Tablename] WITH (READUNCOMMITTED,SAMPLE 5.949706e+001 PERCENT) ) 
AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

--to find all the Tables and Index with number of days statistics being old

SELECT OBJECT_NAME(A.id) AS Object_Name, A.name AS index_name, STATS_DATE(A.id, indid) AS StatsUpdated , DATEDIFF(d,STATS_DATE(A.id, indid),getdate()) DaysOld FROM sysindexes A INNER JOIN sysobjects B ON A.id = B.id and Xtype='U' WHERE A.name IS NOT NULL ORDER BY DATEDIFF(d,STATS_DATE(A.id, indid),getdate()) DESC

DBCC CHECK* would cause blocking

DBCC CHECK* would never cause a blocking as early days.
SQL Server 2005 ownwards, DBCC CHECK* works on snapshot.DBCC CHECK* will not cause any blocking on concurrency as user process is never going to affect by DBCC and vice versa.However, as long as we do DBCC operation on Snapshot, SQL server has to preserve some space for snapshot in the database server.If we do not have enough space then DBCC command would fail. Alternative is to run DBCC against a table, so that SQL server only needs to preserve space for table. However again we will not be able to perform all check apart from the table level information. There is an option to use the TABLOCK keyword which is kind of old way of doing DBCC which is not based on snapshot instead on table lock. This would cause issues with concurrency.

Importance of Column order on a Table

Here Let us see what is the significance of of columns on a table.
Create a Test Table with 10 columns having first two column as default(for easy purpose)and the rest with null to test the table size.

Create Table Table_Size
(
 Col1 int Default(1),
 Col2 Varchar(50) Default 'Test',
 Col3 Varchar(50) NULL,
 Col4 Varchar(50) NULL,
 Col5 Varchar(50) NULL,
 Col6 Varchar(50) NULL,
 Col7 Varchar(50) NULL,
 Col8 Varchar(50) NULL,
 Col9 Varchar(50) NULL,
 Col10 Varchar(50) NULL,
 Col11 Varchar(50) NULL,
 Col12 Varchar(50) NULL,
 Col13 Varchar(50) NULL,
 Col14 Varchar(50) NULL,
 Col15 Varchar(50) NULL,
 Col16 Varchar(50) NULL,
 Col17 Varchar(50) NULL,
 Col18 Varchar(50) NULL,
 Col19 Varchar(50) NULL
)

Find out the object id of the table to use the same for finding out the record size.

Select * From sys.tables Where name='Table_Size'

Use sys.dm_db_index_physical_statsdynamic management view to get the record size information as min, max and avg sizes.

Select min_record_size_in_bytes,max_record_size_in_bytes,avg_record_size_in_bytes From sys.dm_db_index_physical_stats
(5,1925655066,NULL,NULL,'DETAILED')

Test Case1:
1. Insert default value
2. Get the record size information
3. Insert values to the very next column
4. Get the record size information

Insert into Table_Size default values
--Min-21,Max-21,Avg-21

Insert into Table_Size(Col1,Col2)Select 2,'Test'
--Min-21,Max-21,Avg-21

Truncate the table for the next test case.

Truncate Table Table_Size
--Min-0,Max-0,Avg-0

Test Case2:
1. Insert default value
2. Get the record size information
3. Insert values to the third next column
4. Get the record size information

Insert into Table_Size default values
--Min-21,Max-21,Avg-21

Insert into Table_Size(Col1,Col3)Select 2,'Test'
--Min-21,Max-27,Avg-24

Truncate the table for the next test case.

Truncate Table Table_Size
--Min-0,Max-0,Avg-0

Test Case3:
1. Insert default value
2. Get the record size information
3. Insert values to the last next column
4. Get the record size information

Insert into Table_Size default values
--Min-21,Max-21,Avg-21

Insert into Table_Size(Col1,Col19)Select 2,'Test'
--Min-21,Max-59,Avg-33.666

Conclusion:

From the above test, it is clear that for every column it adds a 2 byte.
21 bytes - for the record
38 bytes - for 19 columns ( 19 columns * 2 bytes)
59 bytes - for the entire table.

It is always keep non-nullable values first and then nullable values. The reason is that SQL server uses 2 bytes offset to store the information in variable block for variable width columns. Even we do not have values in the first18 columns, the offset uses for those 18 columns if we have value in 19th column.

Memory Pressure and its resolutions

1. How much space been occupied by single adhoc plans?
The below query would give you information about how much space been used for single adhoc. This is a wastage of memeory.
We can think of some useful setting at server level to have Optimize for adhoc settings.
Optimize for adhoc will never save the cache plan until it is been used for the second time. First time, it saves only the plan stub in the memeory.
Second time execution, it uses the same plan stub and save the cached plan in the memeory.To me this is a very good option that we can think of.

/**************************Check Optimize for Adhoc settings**********************************/
SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go

--Total Size of the procedure cache
SELECT SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
FROM sys.dm_exec_cached_plans;

--Break up of procedure cache utilization
SELECT objtype AS 'Type',
COUNT(*) AS '# Plans',
SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)',
AVG(usecounts) AS 'Avg uses'
FROM sys.dm_exec_cached_plans
GROUP BY objtype;

--Total size used for single use adhocs
SELECT SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1;
/**************************Check Optimize for Adhoc settings**********************************/

Setting Optimize for Adhoc, follow the below steps:
Look at sp_configure for "optimize for ad hoc workloads"
Exec sp_configure

Set the value as 1
sp_configure 'optimize for ad hoc workloads',1

Memory Utilization

Memory utilization is an important area considering for performance tuning of an environment.

When we executes a query, there would 3 consumers for memory:
1. Compile
Compilation is a process of generating a compiled plan for each execution. It requires a significant amount of memory to
find out the optimal plan for the query.
2. Cache
Once we got the optimal plan, the plan has to be cached in athe server memory.
3. Memory grant
When we have sorting and hashing, there would be a consumption of memory while executing the query.
Grant can be again devided into "Required Memory" and "Additional Memory".
The required memory to execute a query can be called as "Required". This is vital one as lack of this memory, the query never executes.
However additional memory is for sorting and hashing.Depends on the data to be sorted, the size of the memory may increase depends on the cardinatlity of the data.

Apart from the above, there would be some consumption of memory for Degree of Parallelism(CXPACKET).
When a query executes in parallel, it uses memory to split the process and for each worker thread separate memory to do the sort operation.

Resource semaphore distributes the memory to different processes depends on the availability.

SQL server 2005 ownwards there is no upper limit for the caching.
Both data and procedure cache allocate as per the request clearing the old/obsolete entries(its a huge area; shortened).
SQL Server has divided its memory for two major categories:

1. Memory for Data caching
- All data has been cached in this particular area.
2. Memory for Procedure cache
- Procedure cache contains all cached plans for procedures, funtions, triggers, adhoc etc...


Major elements in Procedure Cache objects are as below:
1. CACHESTORE_OBJCP
2. CACHESTORE_SQLCP

The below script will show you how your memory been divided(took only top 6 most consuming area)

SELECT  TOP 6
 LEFT([name], 20) as [name],
 LEFT([type], 20) as [type],
 [single_pages_kb] + [multi_pages_kb] AS cache_kb,
 [entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

CACHESTORE_OBJCP
These are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP
These are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR
These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

There are few things that we need to look for memory pressure.

Finding out blocks/locks in your db(Mail Notification)

Blocking and locking is a main area for performance tuning and to ensure a good health of production system. Here is a script to notify DBAs about the locking occurs in the production system.It uses service brokers to notify the lock information.

As a first step, we need to set a threshold value for blocked processes. This can be set using sp_configure as below:

--Set a throshold for blocked process to 10 Sec
sp_configure 'blocked process threshold (s)',10

Once we set a threshold value for the blocking, the next step is to create a queue and service for the event to put an entry in the queue.

--Create a Queue for the event notification
CREATE QUEUE DBA_Prod_Lock_Queue
--Create a Service on queue for the event notification
CREATE SERVICE DBA_Prod_Lock_Service
ON QUEUE DBA_Prod_Lock_Queue ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )

We need to set up a event notification to notify the event of blocking for the threshold.

--Create an event notification for the blocked process report
CREATE EVENT NOTIFICATION DBA_Prod_Notify_Locks
ON SERVER
WITH fan_in
FOR blocked_process_report
TO SERVICE 'DBA_Prod_Lock_Service', 'current database';

Verify the messages are coming to the queue whenever a lock exceeds a threshold and the event notification.

SELECT cast( message_body as xml ), *
FROM DBA_Prod_Lock_Queue
SELECT * FROM sys.server_event_notifications

The below table is a physical table in our local db to hold the event notification further analysis.

Create Table DBA_ProductionMonitor_EventLockInformation
(
SeqID BigInt Identity(1,1),
MessageBody XML,
DatabaseID Int,
Process XML,
Is_Notified Bit Default(0)
)

The below procedure recieve the messages from the queue and push into a local table to proceed with our functionality.

Create Proc usp_DBA_ProductionMonitor_ServiceProc
AS
Begin
DECLARE @msgs TABLE ( message_body xml not null,
message_sequence_number int not null );
RECEIVE message_body, message_sequence_number
FROM DBA_Prod_Lock_Queue
INTO @msgs;
Insert Into DBA_ProductionMonitor_EventLockInformation(MessageBody,DatabaseID,Process)
SELECT message_body,
DatabaseId = cast( message_body as xml ).value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ),
Process = cast( message_body as xml ).query( '/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process' )
FROM @msgs
ORDER BY message_sequence_number
End

The below procedure is used to extract the relevant information from the local table where the messages been pushed from queue and send a mail for lock notification.

Alter Procedure usp_DBA_ProductionMonitor_LockInformation
As
Begin
declare @body varchar(max) =''
Create Table #Temp_SeqIds (SeqId Int)
Insert into #Temp_SeqIds Select SeqID From DBA_ProductionMonitor_EventLockInformation Where Is_Notified=0
IF Exists(Select 1 From #Temp_SeqIds)
Begin
set @body = cast( (
select td =
Cast(BlockedSPID as Varchar(50))
+ ''
+ Cast(BlockedStatus as Varchar(MAX))
+ ''
+ CAST( BlockedCommand as Varchar(MAX))
+ ''
+ Cast(BlockedHostName as Varchar(50))
+ ''
+ Cast(BlockedLoginName as Varchar(MAX))
+ ''
+ CAST( BlockingSPID as Varchar(MAX))
+ ''
+ Cast(BlockingStatus as Varchar(MAX))
+ ''
+ CAST( BlockingCommand as Varchar(MAX))
+ ''
+ Cast(BlockingHostName as Varchar(50))
+ ''
+ Cast(BlockingLoginName as Varchar(MAX))
+ ''
+ CAST( DataBaseID as Varchar(MAX))
+ ''
+ CAST( ServerName as Varchar(MAX))
+ ''
+ CAST( StartTime as Varchar(MAX))
+ ''
+ CAST( EndTime as Varchar(MAX))
from (
SELECT
--Blocked Information
BlockedSPID=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)[1]', 'varchar(100)' ) ,
BlockedStatus=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@status)[1]', 'varchar(100)' ) ,
BlockedCommand=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process)[1]', 'varchar(max)' ) ,
BlockedHostName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)[1]', 'varchar(100)' ) ,
BlockedLoginName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)[1]', 'varchar(100)' ) ,
--Blocking Information
BlockingSPID = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)[1]', 'varchar(100)' ) ,
BlockingStatus = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@status)[1]', 'varchar(100)' ) ,
BlockingCommand = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process)[1]', 'varchar(max)' ) ,
BlockingHostName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)[1]', 'varchar(100)' ) ,
BlockingLoginName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)[1]', 'varchar(100)' ) ,
--General Information
DataBaseID = MessageBody.value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ) ,
ServerName = MessageBody.value( '(/EVENT_INSTANCE/ServerName)[1]', 'varchar(100)') ,
StartTime = MessageBody.value( '(/EVENT_INSTANCE/StartTime)[1]', 'datetime' ) ,
EndTime = MessageBody.value( '(/EVENT_INSTANCE/EndTime)[1]', 'datetime' )
FROM DBA_ProductionMonitor_EventLockInformation A
Inner Join #Temp_SeqIds B On A.SeqID = B.SeqId
) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body = ' '
+ ' '
+ ' '
+ ' '
+ ' '
+ ''
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
Blocked SPID
Blocked Status
Blocked Command
Blocked HostName
Blocked LoginName
Blocking SPID
Blocking Status
Blocking Command
Blocking HostName
Blocking LoginName
Database ID
Server Name
Start Time
End Time
'
print @body
EXEC msdb.dbo.sp_send_dbmail @PROFILE_NAME = '',
@recipients='',
@subject = 'DBA Notification - Production Lock Occured.',
@body = @body,
@body_format = 'HTML' ;
Update A Set Is_Notified = 1 From DBA_ProductionMonitor_EventLockInformation A
Inner Join #Temp_SeqIds B On A.SeqID = B.SeqId
End

The below script to enable and disable the queue to maintain the functionality.

--Disable Queue
ALTER QUEUE [dbo].[DBA_Prod_Lock_Queue] WITH STATUS = OFF , RETENTION = OFF , ACTIVATION ( STATUS = OFF , MAX_QUEUE_READERS = 0 , EXECUTE AS OWNER )
--Enable Queue
ALTER QUEUE [dbo].[DBA_Prod_Lock_Queue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF , MAX_QUEUE_READERS = 0 , EXECUTE AS OWNER )

Hope the script would be useful for all of us!!!

Calculating Working days

Here is a script for finding out the calculating the work days (excluding the saturdays and sundays). The script can be altered also to accomodate local holidays in a separate table too. Only thing is to substract the count of the table for that period.

Declare @StartDate Date,  @EndDate Date
Set @StartDate = Getdate()-20
Set @EndDate = Getdate()

SELECT  (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

SQL Server Non-Clustered Index structure details

The non-clustered index key structure would be as follows:

Case 1: If a table has a unique clustered index index and non-unique non-clusterd index index

then its root level will have non-clustered key + clustered key

And at leaf level, non-clustered key + clustered key

Case 2: If a table has a unique clustered index index and unique non-clusterd index index

then my root level will have non-clustered key

And at leaf level, non-clustered key + clustered key

Case 3: If a table has a non-unique clustered index index and non-unique non-clusterd index index

then its root level will have non-clustered key + clustered key + Uniquifier

And at leaf level, non-clustered key + clustered key + Uniquifier.

Case 4: If a table has non-unique clustered index index and unique non-clusterd index index

then its root level will have non-clustered key

And at leaf level, non-clustered key + clustered key +Uniquifier.

The below snapshot explains the same as above: