SQL Performance and Optimization

The sql server performance and optimization can be improved in many ways, but it again depends on the number of factors and individual scenariso, here are some the of steps taken by myself.

1. Check the size of the tables, you can use the query from my post which identifies the largest table.

2. Check the max. cpu utilization, you can use the query from my post on this. Then you can work on the sql query.

3. Check the indexes on the table, see if the tables have clustered index and non clustered index, it depends on how the table is queried and what fields are queried.

4.Location of the mdf and ldf files, it is advisable for optimimum performance to locate the temp database file on d:/ drive, mdf on the e:/ and the ldf on the f:/ and leave the operating system on the c:/.

5. Check the views on the database this may used in excess, too many views causes concern on the performance of the database and on the sql server.

Identify the No of days between job status

The above table represents the status of a jobnumber from order entery to completion. In this case for performance mprovement if we need to identify the number of days it has taken between each status

drop table #temp
delete from tbltulsaopkpi
create table #temp( id int identity , lotno varchar(30), jobDate datetime, status varchar(5) )
insert into #temp
select jobnumber, date,jobstatus
from tbltransjobstatus where jobnumber = 'A8000160-1'
--select * from #temp
declare @ctr int, @jobstatus varchar (3), @lotno varchar(20)
SELECT @ctr = count(id) FROM #temp
declare @olddate datetime, @newDate datetime
declare @i as int, @noDays as int
set @i= 1
set @noDays = 0
WHILE @i <= @ctr -- - 1 >= 0
BEGIN
if @i = 1 --or @i =@ctr
BEGIN
select @oldDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
-- select @oldDate, @lotNo, @jobStatus, 0
insert into tbltulsaopkpi values
(@lotno,@jobstatus,0)
END
else
BEGIN
select @newDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
set @noDays = datediff(day,@oldDate, @newDate)
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@oldDate) = 'sunday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@newDate) = 'saturday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when @newDate not in (select date from tblholiday where finyr = dbo.ufgetfinyr(@newDate)) then 1 else 0 end))
insert into tbltulsaopkpi values
(@lotno,@jobstatus,@noDays)
-- select @newDate, @lotNo, @jobStatus, @noDays
set @oldDate = @newDate
END
set @i = @i + 1
-- create table tbltulsaopkpi (
-- lotno varchar(20),
-- jobstatus varchar(5),
-- NoofDays int
-- )
END
-- select * from tbltulsaopkpi

--writter by marquerite

SQL Restore from Backups

Please note to have this working the BACKUP has to be done first, then from that point the transaction log has to be done. If there a backup done in between this process the RESTORE may not work properly.


Sql script to RESTORE DATABASE, please note this can also be done through wizards in SSMS.

RESTORE DATABASE [test]
FROM DISK = 'E:\transfer\vijay\test_backup_201001111451.bak'
WITH FILE = 1, NORECOVERY,
MOVE N'test' TO N'E:\MSSQL\test.mdf',
MOVE N'test_Log' TO N'F:\MSSQL\test_log.ldf',
NOUNLOAD, STATS = 10

Sql script to RESTORE TRANSACTION LOG files, this is applicable when you do the full back up and transaction log back ups. This option enables to get to the point of data loss.

RESTORE LOG [test]
FROM DISK = 'E:\Transfer\vijay\test.trn'
WITH FILE = 1,
RECOVERY

SQL Backup

Sql backup

You can do a complete full backup on daily basis in the night and subsequently do a transaction log backup for every hour or every two hours

The next option is to do a set the options to simple and do a simple backup daily then do a transaction back up every two hours.



-*********************************************************************************
--BACKUP DATABASE
--*********************************************************************************
BACKUP DATABASE [Pacop] TO
DISK = N'Z:\DATABASE\Pacop_backup.bak' WITH NOFORMAT,
INIT, NAME = N'Pacop_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
--*********************************************************************************
--BACKUP LOG OVERWRITE THE TRN LOG FILE
--*********************************************************************************
BACKUP LOG [Pacop] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Pacop.trn'
WITH NOFORMAT, INIT, NAME = N'Pacop-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
DBCC SHRINKFILE (N'Pacop_Log' , 786)
--*********************************************************************************

--*********************************************************************************
--BACKUP LOG APPEND THE TRN LOG FILE
--*********************************************************************************
BACKUP LOG [Pacop] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Pacop.trn'
WITH NOFORMAT, NOINIT, NAME = N'Pacop-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

--*********************************************************************************
--*********************************************************************************
DBCC SHRINKFILE (N'Pacop_Log' , 786)
--*********************************************************************************