December 23, 2008 at 12:13 pm
We migrated our environment from SQL 2000 to SQL 2005 and since then we have been having performance problems daily.
Let me explain the environment
Enviroment:
OS : Windwos 2003 R2 64 bit Enterprise edition
CPU: 16
RAM : 64 GB
SQL Server : Microsoft SQL Server Enterprise Edition (64-bit)
Service Pack : SP2
Version : 9.00.3253.00
Its a two node active\active cluster environment and the data resides on SAN.
Problem:
1. The front end applications slows down while talking to DB,the DB shows blocking/locking issue. The SQL service has to be restarted to fix the problem.
2. The system runs for one whole day and then we face the same problem
3. A SQL restart fix the problems for the day untill the next day.
Things i have done:
1. After migration Update the statistics, rebuild the indexes on all DB's
2. Update the MDAC for front end application server to latest MDAC version
3. Ran PSSDIAG utility to collect blocking data and analyzed it with SQLNexus.
4. Perfmon does not display any CPU,RAM or I/O bottlenecks.
5. We have opened support ticket with MS but they are still investigating.
My Questions:
1. What else can i check if there are problems with SQL Server?
2. Any tools\articles that might help.
Regards
December 23, 2008 at 12:24 pm
What are the min/max memory settings for each instance running on each node?
What is the Maximum Degree of Parallelism setting on each instance?
What was the previous system configuration?
And last, but not least - identify the queries that are locking/blocking and see what can be done to optimize them. This will probably be where you will find the biggest gains (and problems).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2008 at 12:46 pm
William - Thanks for your reply.
Current memory settings are
SQLInstance1 : (instance with problems)
Min Memory - 30 GB
Max Memory - 40 GB
Max Degree of //sm - 1
SQLInstance2:
Min memory- 10 GB
Max Memory - 20 GB
Note: Only one sql instance runs on a node.
The queries that are causing blocking are simple update on table with not much room to optimize.
However what iam seeing in blocker script is that connections are waiting on "WriteLog" waittype when they issue "Commit tran" sql command.
This might point to possible contention on transaction log drive but the perfmon counters on this drive are normal.
The most peculiar thing is blocking only happens at morning (between 8:30 to 9:30 am) which is not busy time for us. Our disk activity is at peak from noon to 5:00 pm.
What should i be looking for?
Regards
December 23, 2008 at 1:09 pm
How large is the database?
How often do you backup the logs?
Are there scheduled maintenance jobs running during the slow periods on either the SQL Server or the SAN?
How 'large' are the update transactions? Single rows, hundreds, thousands?
December 23, 2008 at 2:07 pm
What is your disk configuration? How are your user and TempDB data and log files allocated to these disks?
What are your PerfMon Physical disk statistics during this problem? In particular, what are the following values for each of the involved disks:
Physical disk:Avg Sec/read
Physical disk:Avg Sec/write
Physical disk:% idle time
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 2:54 pm
I will reply to David's and Young's queries.
1. The db is 100 GB+
2. The log backup are taken every 5 minutes
3. No maintenance jobs run during day time
4. the updates are single row updates
5. The tempdb is on separate drive of its own
The Physical disk:Avg Sec/write counter is high on drive where tlog files reside.
The MS engineer is emphasizing that we have to fix the drive issue.
But my manager argues that why the disk problem happens during the morning time and not for rest of the day.
what else i can try?
December 23, 2008 at 3:32 pm
fssherwani (12/23/2008)
I will reply to David's and Young's queries...
Well you didn't really answer most of my questions...
The Physical disk:Avg Sec/write counter is high on drive where tlog files reside.
The MS engineer is emphasizing that we have to fix the drive issue.
But my manager argues that why the disk problem happens during the morning time and not for rest of the day.
We might be able to help you answer this question if you can answer my questions.
To review:
1) what is the disk configuration? In particular the RAID & SAN/NAS configuration.
2) What are the following PerfMon Physical disk statistics during this problem? :
Physical disk:Avg Sec/read
Physical disk:Avg Sec/write
Physical disk:% idle time
A statistic is a number and we really need numbers to be much help here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 3:40 pm
Sorry I forgot two of the statistics:
Physical disk: Disk bytes/sec
Physical disk: Disk transfers/sec
If you could also get these numbers from times when the performance is OK, that would tell us a lot.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 24, 2008 at 6:17 am
Couple of questions:
Are the locking and blocking issues associated with a single table or multiple tables?
Aside from the locks and blocks are you seeing deadlock issues?
When the row update occurs what kind of lock is being held on the table being updated?
Is it updates blocking other updates, or some other combination?
What kind of, and how many indexes do you have on the table(s)? & how many columns in the table(s)?
December 24, 2008 at 6:08 pm
Hi Nicholas - The locking/blocking is associated with couple of tables only. These table serve as unique id generator and are highly transactional
I have recorded no deadlocks during the slowdown.
The locks are update key lock and blocking happen during updates on the table.
The table structure is as follows
SEQUENCEGENERATOR:
sequnce_Name varchar --- primary key ,clustered index
sequence_start_value int
sequence_value int
DML Statement is done via stored proc, the @sequence_name and @increment are passed as parameter and updated @sequence_value is returned
--- start
Update SEQUENCEGENERATOR set
@sequence_value=sequence_value=sequence_value+@increment where sequnce_Name=@sequnce_Name
select @sequence_value
--- end
I think the DML statement is very simple, the application starts to slow down when there is blocking on this table.
ANOTHER OBSERVATION:
When analyzing the blocker script i see a lot of occurances of PAGELATCH*_Waits
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply