Stored procedure execution on SQL Server startup

I have studied a very interesting topic in SQL Server and wish to blog the same in my site.
Scenario:
On each SQL Server database startup, I need to execute a procedure in my database. It's a very basicscenario in all places.

Solution:

For this, SQL Server is providing an option of using a system stored procedure sp_procoption

create procedure Murli_Insert_Procedure

as

begin

insert into tblMurli(id,val) values (5,'F')

end

EXEC sp_procoption @ProcName = 'Murli_Insert_Procedure',@OptionName = 'startup',@OptionValue = 'true'

Now, your stored procedure is set as a initial startup which will execute on DB start.

Temporary table Vs Temporary variable in SQL Server

We have seen lot of difference between temporary variable and temporary table. Here is a nice difference in Transaction perspective.

Temporary table is transaction dependent and it abides to the database transaction whereas temporary variable is not transaction bound.


Sample Query:

---------------------Temporary table -------------------------------

drop table #temp

create table #temp (id int, val varchar(100))

begin tran ins

insert into #temp values (1,'Venkat')

rollback tran ins

select * from #temp

We are not getting any records indicating the temporary table will bound to the transaction strategies.

------------------Temporary variable --------------------------

Declare @tempval table(id int, val varchar(100))

begin tran ins

insert into @tempval values (1,'Venkat')

rollback tran ins

select * from @tempval

Even we have provided rollback transaction. Records are available in the table variable.

list column names for a table

To list the column names for a table:

In SQL Server, we can retrieve these details by two methods.

1. This can be taken from the sys.objects and sys.columns table (Used in SQL Server 2000 and higher versions).
2. Another option is to use INFORMATION_SCHEMA.COLUMNS (This is introduced in SQL Server 2005 and higher version)

Below is the query to achieve the same,

drop table venkat_table
go
create table venkat_table (id int,val varchar(100),val1 varchar(100))

First Option:

SELECT *FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='venkat_table'

Second Option:

Joining sys.objects and sys.columns table based on the object Id.

SELECT C.name,O.* FROM sys.objects O INNER JOIN sys.columns CON O.object_id=C.object_id WHERE O.name='venkat_table'

View Log file information in SQL Server

Scenario:

Usually, we will face log space issue in our SQL Server. Most of the time, we dont know the reason for our logfile space. I would suggest to get a third party tool to check it. By reading through some of the forums. I found there is some undocumented DBCC command which is used to dig the log file and get some information to us.

This command won't give a very great information at least we can get some high level information. Let's see the command,

DBCC Log(Databasename, Option number)

Default option number is 0

For instance,

dbcc log ('master',0) - 0 indicates a very minimal information.

The above command will provide the below details,

Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

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

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.

To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

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

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.

To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Performance monitoring command in SQL Server

DBCC SQLPERF(umsstats) - This command will provide all the OS related information for your threads in the SQL Server instance. It provides the below values,

Node Id
Avg Sched
LoadSched
SwitchesSched Pass
IO Comp Passes
Scheduler ID (The below data will be populated for each scheduler ID)
online - Whether it's online or not
num tasks - Number of tasks
num runnable - Runnable tasks
num workers - Parallel workers involved in the scheduler
active workers - Active parallel workers involved in the scheduler
work queued - Works queued up in this schedule
cntxt switches - How many context switching happening in this schedule
cntxt switches(idle) - Idle threads after context switches.
preemptive switches - Prirority switches happened in this schedule.