SQL server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL server 2005

Author

Topic

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-04 : 17:44:26

Hi,

we have new application "CAMS" and the backend is sql server 2005. It was running fine for 2 months and it started drgrading. It is very slow.
I am doing transaction log,differential backup and defrag indexes every day.

Do we need to do rebuild indexes also?

What other things we need to be aware of?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 17:51:36

Rebuilding indexes = defragmenting indexes

How often are you running transaction log backups? We run ours every 15 minutes. This is unrelated to performance, but I ask the question since you brought up backups in your post.

Have you run SQL Profiler to determine the most expensive queries? Are you properly indexed? How about your database design? Have you looked at the execution plans?

Have you run Performance Monitor to determine if there are hardware bottlenecks?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-04 : 18:15:37

and also check out standard reports- Top queries by CPU time

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-04 : 18:18:55

We bought this software. I do defragmentation every night.

I do transaction log backup every hour and during this I truncate the log. It was running fine and suddenly it started acting up. We are on vmware and dual core processor.
Our server is not clustered and we have 80 users at times.

the software vendor says that they have tested load balancing and no one had problem like this.

We ran Performance Monitor and our cpu usage was more than 90%. then we restarted sql server and rebooted the machine. It was fine for 1 hour and it started going down again.

I don't know what else to do.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 18:27:50

quote:
Originally posted by tanu

I do transaction log backup every hour and during this I truncate the log.

There is absolutely no point to backing up the transaction log if you are going to truncate it as you are breaking the transaction log chain. Stop truncating it immediately!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-04 : 18:36:24

this is my script.

USE [msdb]
GO
/****** Object: Job [TransactionLogBackup] Script Date: 08/04/2008 17:35:04 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/04/2008 17:35:05 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TransactionLogBackup',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'
',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sadf',
@notify_email_operator_name=N'sad', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Transaction Log Backup] Script Date: 08/04/2008 17:35:10 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Transaction Log Backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP LOG [database] TO DISK = N''F:\data\dbBak\database\TransactionLog Backup\databaseLog.bak'' WITH NOFORMAT, NOINIT, NAME = N''database-Transaction Log Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Morning Transaction Log Backup',
@enabled=1,
@freq_type=8,
@freq_interval=126,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080623,
@active_end_date=99991231,
@active_start_time=70000,
@active_end_time=115959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

contrari4n
Starting Member

27 Posts

Posted - 2008-08-04 : 19:05:34

If you were OK but now it is slow, consider the following:

1. Different plans are being generated due to out of date statistics. Run sp_updatestats, and check that auto update statistics is enabled.
2. You state it is running on vmware. Have other virtual servers been added to the same server recently. If so, these could be slowing you down.
3. CPU usage in perfmon is not accurate in vmware. Is this on ESX? If so there are some very comprehensive reports you could look at (see ESX documentation)
4. What else has changed? More users? Disks shared with other apps? Configuration changes?
5. Do you have a physical box to move it to? Vmware is great for low throughput applications and small databases, but could be struggling with yours.

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-05 : 15:20:16

All of a sudden it started working again. We don't know what happened. In the morning around 9:00 am we increased the memory on sql box. But nothing changed. But around 12:00 noon everything went back to normal.

Everyone wants to know what happened and I don't have any answer. Someone suggest me what was that. I checked all logs, didn't find anything.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources