Performance :Sql server Performance on Windows 2003

Sql server has far better performance in Windows 2003 than in windows 2000.

1.If you are running in an Sql server dedicated server you can remove all the other services that are not needed for the Sql server.

Some of the services may be SmartCard,SmartCardHelper,QOS ,Clipbook,Telnet.

It all depends on what application the Sql Server are using

2.By Applying QOS across the servers in the organisation we can increase the Network Bandwidth for the Sql Server users thereby utilising the bandwidth efficiently across all other servers.

3.Most companies use the Windows environment so its better to remove all the other protocols except TCP/IP

4. Windows 2003 server has included some good command line performance related tools to better monitor performance

They are

a. Logman
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_logman.mspx?mfr=true

b.relog
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx?mfr=true

c.tracerpt
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_tracerpt.mspx?mfr=true

d.typeperf
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx?mfr=true

Simple TSQL Scripting -2 : Generating Fibonacci Series

Fibonacci series

The Fibonacci Series is a sequence of numbers first created by Leonardo Fibonacci (fi-bo-na-chee) in 1202. It is a deceptively simple series, but its ramifications and applications are nearly limitless. It has fascinated and perplexed mathematicians for over 700 years, and nearly everyone who has worked with it has added a new piece to the Fibonacci puzzle, a new tidbit of information about the series and how it works. Fibonacci mathematics is a constantly expanding branch of number theory, with more and more people being drawn into the complex subtleties of Fibonacci's legacy

1 1 2 3 5 8 13 21


create procedure vic_fibonacci(@num int)as

declare @a int ,@b int,@c int
set @a = 1
set @b = 1
while @num >0
begin
set @c = @a + @b
select @c
set @a = @b
set @b = @c
set @num = @num - 1
end

exec vic_fibonacci 7

Sql Server ShortCuts -1:Executing a stored procedure whenever sql server starts

I found this interesting option under the Sql server 2000 Enterprise manager.

If a DBA wants to execute any system procedure when the Sql Server starts
go to

Enterprise Manager-->Databases-->Master-->StoredProcedures

Right Click any system stored procedure and there is an option below which indicates

Execute whenever Sql Server Starts

and check it

To check whether any procedures are running when sql server starts execute the following

USE master
GO
SELECT
name
FROM
sysobjects
WHERE
OBJECTPROPERTY(id, 'ExecIsStartup') = 1

or

USE master
GO
SELECT
ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsStartUp') = 1

Simple TSQL Scripting -1: Generating Prime Numbers

Create the procedure as follows:

create procedure primegen(@k integer)
as
declare @a int
declare @b int
declare @p int
declare @i int
declare @n int
set @a =2
set @n =1
set @p = @k
set @i =2
print @a
while @n < @p - 1-- Checking for the number of terms
begin --1
set @i = 2
set @a = @a + 1 --incrementing the prime number
while(@i < @a) -- checking whether the dividend is less than the divisor
begin --2
if(@a % @i) <> 0 --- whether the modulo is not zero,if not zero increment i for the next dividend
begin --3 --and check the same modulo..Repeat it till the dividend one less than the divisor
set @i = @i +1
if @i = @a --check whether all the dividends have been divided whose modulo is not zero and print the
begin -- 4 number which is prime
print @a
set @n = @n + 1 -- increment the number of prime terms
break
end --4
end--3
else
begin--5 --if its modulo is zero increment the dividend and come out of the if modulo checking loop
set @i = @i + 1
break
end--5
end--2
end--1

--Execute the First 100 prime numbers

Exec primegen 100

Using System Tables 1 : Oh!!! which processes are taking most CPU time

Master...sysprocesses

This command gives you an inside depth of which process is running the CPU process very high

SELECT * FROM master..sysprocesses WHERE status = 'runnable' ORDER BY cpu desc

When you get the spid you can get the input command from the following

DBCC INPUTBUFFER(spid)

which gives you what statement the end user has been running.

KILL spid would close or kill the process automatically

The other notable columns in this table are

• nt_username
• loginname
• hostname

About me

Hi,My Full name is Vivekanand Serou.Friends and family call me Vic.I am currently working as a Database Administrator in Uswitch(DBA). I will be posting the various stuff i learnt in IT as well as in Relational Databases.
If you have any comments you can mail me to svicky9@yahoo.com