Size of Index on a table.

We can use the following query to find the size of the index.

EXEC sp_spaceused[tableName]

Sparse Columns

Sparse Columns , in terms of special data structure, were introduced in SQL Server 2008. These are meant to deal with the recurring scenario where you have columns that you esentially just need ” Sometimes”. That is , they are going to be null a high percentage of time. There are scenarios where, if you bump into a few of these kinds of columns, you tend to bump into a ton of them. using spares columns, you can increase the total number of allowed columns  in a single table to 30,000.

Internally, the data from columns marked as being sparsely populated is embedded within a single column, allowing a way to break the former limitation of 1,024 columns without a major architectural changes.

IMAGE, Text, ntext, geogrraphy , gemomentry, timestamp and all user defined data types are prohibited from being marked as a sparse column.

Compare and Identify the Schema Changes

use master
go

DECLARE @Server1 VARCHAR(100) = ‘[Server1].’; –include a dot at the end
DECLARE @DB1 VARCHAR(100) = ‘[Database1]’;

DECLARE @Server2 VARCHAR(100) = ‘[Server2].’; –include a dot at the end
DECLARE @DB2 VARCHAR(100) = ‘[Database2]’;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL =
‘
SELECT Schema1.DBName,
Schema1.SchemaName,
Schema1.TableName,
Schema1.ColumnName,
Schema1.name DataType,
Schema1.Length,
Schema1.Precision,
Schema1.Scale,
Schema1.Is_Identity,
Schema1.Is_Nullable,
Schema2.DBName,
Schema2.SchemaName,
Schema2.TableName,
Schema2.ColumnName,
Schema2.name DataType,
Schema2.Length,
Schema2.Precision,
Schema2.Scale,
Schema2.Is_Identity,
Schema2.Is_Nullable
FROM
(SELECT ”’ + @DB1 + ”’ DbName,
SCHEMA_NAME(schema_id) SchemaName,
t.Name TableName,
c.Name ColumnName,
st.Name,
c.Max_Length Length,
c.Precision,
c.Scale,
c.Is_Identity,
c.Is_Nullable
FROM ‘ + @Server1 + @DB1 + ‘.sys.tables t
INNER JOIN ‘ + @Server1 + @DB1 + ‘.sys.columns c ON t.Object_ID = c.Object_ID
INNER JOIN systypes st ON St.xType = c.System_Type_id
) Schema1
FULL OUTER JOIN
(SELECT ”’ + @DB2 + ”’ DbName,
SCHEMA_NAME(schema_id) SchemaName,
t.name TableName,
c.name ColumnName,
st.Name,
c.max_length Length,
c.Precision,
c.Scale,
c.Is_Identity,
c.Is_Nullable
FROM ‘ + @Server2 + @DB2 + ‘.sys.tables t
INNER JOIN ‘ + @Server2 + @DB2 + ‘.sys.columns c ON t.Object_ID = c.Object_ID
INNER JOIN systypes st ON St.xType = c.System_Type_id
) Schema2
ON Schema1.TableName = Schema2.TableName
AND Schema1.ColumnName = Schema2.ColumnName
ORDER BY CASE WHEN Schema1.TableName IS NULL THEN 2 ELSE 1 END, Schema1.TableName,
CASE WHEN Schema1.ColumnName IS NULL THEN 2 ELSE 1 END, Schema1.ColumnName
‘

EXEC sp_executesql @SQL

Which Databases in SQL Server is consuming more memory?

–List the Number of pages in the buffer pool by database and page type
SELECT DB_NAME(database_id),
page_type,
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! = 32767
GROUP BY database_id, page_type
ORDER BY number_pages DESC
GO
–List the number of pages in the buffer pool by database
SELECT DB_NAME(database_id),
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! =32767
GROUP BY database_id
ORDER BY database_id
GO

Identify Lock Tables in SQLServer

SELECT
OBJECT_NAME(p.OBJECT_ID) AS TableName,
resource_type, resource_description
FROM
sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id

List all the subscriptions scheduled in the Report Server

At times, we might need to pull the list of all the Subscriptions configured in the Report Server. In such cases the following script would be helpfull.

SELECT USR.UserName AS SubscriptionOwner
,SUB.ModifiedDate
,SUB.[Description]
,SUB.EventType
,SUB.DeliveryExtension
,SUB.LastStatus
,SUB.LastRunTime
,SCH.NextRunTime
,SCH.Name AS ScheduleName
,CAT.[Path] AS ReportPath
,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
ORDER BY USR.UserName
,CAT.[Path];

SQL Server Specific code – to Identify Locked table in SQL Server.

Select
OBJECT_Name(p.object_id) as TableName,
resource_Type,
resource_Description
From
Sys.dm_tran_locks l
Join Sys.partitions p on l.resource_associated_entity_id = p.hobt_id

List All columns of certain type in a specified database.

Select s.Name as ‘Schmea’,
ts.name as tableName,
schema_Name(t.schema_id) as DataTypesSchema,
t.Name as DataTypeName,
t.is_user_defined ,
t.is_assembly_type,
c.is_nullable,
c.max_length,
c.Precision,
c.scale
from Sys.columns AS c
Inner Join Sys.types as t on c.user_type_id=t.user_type_id
Inner join sys.tables ts on ts.object_id=c.object_id
Inner join sys.schemas s on s.schema_id =ts.schema_id
Order by s.name ,ts.Name,c.column_id

Temporary Tables in TSQL

when you need to temporarily store data in tables , suppose we might need the data to be visible only to the current Section, we will generally go for Temp tables.

SQL Server supports three kind of temporary tables . Local Temporary tables, global temporary tables, and table variables. All the temporary tables are created in the tempDb database.

1) Local temporary table. like as #T1. This type of temp table is visible only to the session that created it, in the creating level and all inner levels in the  call stack. A local Temporary table is destroyed automatically by SQL Server when the creating level in the call stack goes out of scope. we might wonder how SQL Server prevents name conflicts when two sessions create local temporary tables with the same name. SQL Server internally adds a suffix to the table name that makes it unique in tempdb.

2) Global temporary Tables . like as ##T1

when we create a global temporary table, it is visible to all other sessions. Global temporary tables are destroyed automatically by SQLServer when the creating session disconnects and there are no active references to the table.

3) Table Variables.

Table variables are similar to local temporary  tables in some ways and different n others. You declare table variables much like you declare other variables, by using DECLARE statement.

As with local temporary tables, table variables have a physical presence as a table in the tempdb database, contrary to the common misconception that they exist only in memory.Like local temporary tables, table variables are visible only to the current session, but they have a more limited scope: only to the current batch. Table variables are  visible neither to inner batches n the call stack nor to subsequent batches in the session.

DECLARE @MYDat TABLE

(

ID INT NOTNUll Primary key,

Firstname varchar(10)

)

Monitioring Performance in SSIS using Performance counters.

SSIS provides a set of Performance counters.

few of them are

• Buffers in USE
• Flat buffers in Use
• Private buffers in use.
• Buffers spooled.
• Rows Read
• Rows written

Out of the above mentioned Counters, “Buffers in use”,”Flat buffers in use”  and “Private buffers in use” are useful to discover Leaks. During Package execution time, we will see these counters fluctuating.But Once the package finishes Execution , their values should return to the same value as what they were before the execution.Otherwise ,buffers are leaked.

“Buffers Spooled” has initial value of 0. when it goes above 0 , it indicates that the engine has started memory swapping.

“Rows Read”,”Rows Written”  show how many rows the entire data Flow has processed. They give you an overall idea about the execution Progress.