Prev: SQL Server
Next: How to track transation activities in transaction log in sql serve
From: Malkesh on 21 Jul 2010 18:12
Hi All,

Since last 2-3 weeks, our system has been experiencing issue of deadlocks
and slow performance.

The configuration is SQL Server 2005 SP3 64 bit Standard edition with 8
processor and 64 GB RAM (57 GB for SQL Server). Though it is not required but
AWE is enabled and lock pages in memory priviledge granted on service
account. We are using Quest SpotLight to monitor performance. The database
file configurations are: on E drive we have SQL installation and all the data
files and on F drive we have log files and tempdb. I'm not sure but it is on
RAID 1+0

Issues are:
1- High log flush wait time frequently on the server
2- High number of deadlocks

Does log file placement alongwith tempdb leads to high log flush wait time
issue? Also could it be possible cause for deadlocks? My doubt is on high log
flush time for slow performance. If i isolate tempdb from other log files,
does it solve problem of high log flush time. Also let me know the possible
solution.

Thanks in advance.

Thanks & Regards
Malkesh
From: Eric Isaacs on 21 Jul 2010 21:33
Database:Log Flush Waits/sec: The number of commits that are waiting
on log flush. Although transactions do not wait for the log to be
flushed in tempdb, a high number in this performance counter indicates
and I/O bottleneck in the disk(s) associated with the log.

Look at finding ways to speed up your log file drive.

Deadlocks may be related, or may be a separate issue.

-Eric Isaacs

From: Erland Sommarskog on 22 Jul 2010 05:30
Malkesh (Malkesh(a)discussions.microsoft.com) writes:
> Since last 2-3 weeks, our system has been experiencing issue of deadlocks
> and slow performance.
>
> The configuration is SQL Server 2005 SP3 64 bit Standard edition with 8
> processor and 64 GB RAM (57 GB for SQL Server). Though it is not
> required but AWE is enabled and lock pages in memory priviledge granted
> on service account. We are using Quest SpotLight to monitor performance.
> The database file configurations are: on E drive we have SQL
> installation and all the data files and on F drive we have log files and
> tempdb. I'm not sure but it is on RAID 1+0
>
> Issues are:
> 1- High log flush wait time frequently on the server
> 2- High number of deadlocks
>
> Does log file placement alongwith tempdb leads to high log flush wait
> time issue? Also could it be possible cause for deadlocks? My doubt is
> on high log flush time for slow performance. If i isolate tempdb from
> other log files, does it solve problem of high log flush time. Also let
> me know the possible solution.

Moving the log files may remove that symptom, but I don't think the
overall performance will improve very much.

Most likely what has happened is that a query plan for one or more queries
has changed from good to bad. There are a number of reasons why that could
happen. It could be because the plan of the cache, and statististics are
not up to date. Or it could be because statistics changed, and the optimizer
now does a misestimation.

So you need to find the slow queries. There are a number of ways to find
them: run a trace are dm_exec_query_stats two examples. But since you are
getting deadlocks, it may be a start to analyse the deadlock traces. It
is not unlikely that any of the bad plans are involved in the deadlock.
You will get deadlock tracaes in the SQL Server error log by adding
-T1222 as a start-up parameter to SQL Server. You can also enable it
without a server restart by issuing DBCC TRACEON (1222, -1), but Books
Online suggest that we should use it only when the server is idle.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Malkesh on 22 Jul 2010 12:21
Thank you all for the feedback.

Deadlock were already there in the system. We have eConnect application
which was initially run in multi thread. We have changed it to single thread
and deadlocak numbers have come down but still it is high there.

Before this issues, we were exeperiencing avg. disk queue length and page
life expectancy issues. So we have increased RAM and now those two issues
gone, but comes the high log flush time issue and system is slow down. Now
page life expectany is around 10 minutes and Buffer cache hit ratio is almost
99%. Also procedure cache hit ration is just above 90% however Soptlight
sometime throws message "56% of all the SQL compilation is recompilation" and
"Size of Virtual page file down to 8%'. Are these also bottlenecks?

We have following user defined settings:
1. SQL Server 2005 64 bit Standard with 8 processor
2. 64 GB RAM
3. AWE Enabled
4. Min 0 and Max 57 GB RAM allocation
5. Max Worker Thread 1024 9 (We almost have around 400-500 sessions running
all the time)
6 Max DOP 1 (since multi threading was creating deadlocks we have changed
this from 0 to 1)
7. Great planes database is on the same server/

Also could you please tell me any of the above settings especially Max DOP
or Max Worker thread affect the issue?
--
Thanks & Regards
Malkesh


"Erland Sommarskog" wrote:

> Malkesh (Malkesh(a)discussions.microsoft.com) writes:
> > Since last 2-3 weeks, our system has been experiencing issue of deadlocks
> > and slow performance.
> >
> > The configuration is SQL Server 2005 SP3 64 bit Standard edition with 8
> > processor and 64 GB RAM (57 GB for SQL Server). Though it is not
> > required but AWE is enabled and lock pages in memory priviledge granted
> > on service account. We are using Quest SpotLight to monitor performance.
> > The database file configurations are: on E drive we have SQL
> > installation and all the data files and on F drive we have log files and
> > tempdb. I'm not sure but it is on RAID 1+0
> >
> > Issues are:
> > 1- High log flush wait time frequently on the server
> > 2- High number of deadlocks
> >
> > Does log file placement alongwith tempdb leads to high log flush wait
> > time issue? Also could it be possible cause for deadlocks? My doubt is
> > on high log flush time for slow performance. If i isolate tempdb from
> > other log files, does it solve problem of high log flush time. Also let
> > me know the possible solution.
>
> Moving the log files may remove that symptom, but I don't think the
> overall performance will improve very much.
>
> Most likely what has happened is that a query plan for one or more queries
> has changed from good to bad. There are a number of reasons why that could
> happen. It could be because the plan of the cache, and statististics are
> not up to date. Or it could be because statistics changed, and the optimizer
> now does a misestimation.
>
> So you need to find the slow queries. There are a number of ways to find
> them: run a trace are dm_exec_query_stats two examples. But since you are
> getting deadlocks, it may be a start to analyse the deadlock traces. It
> is not unlikely that any of the bad plans are involved in the deadlock.
> You will get deadlock tracaes in the SQL Server error log by adding
> -T1222 as a start-up parameter to SQL Server. You can also enable it
> without a server restart by issuing DBCC TRACEON (1222, -1), but Books
> Online suggest that we should use it only when the server is idle.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Erland Sommarskog on 22 Jul 2010 16:45
Malkesh (Malkesh(a)discussions.microsoft.com) writes:
> Deadlock were already there in the system. We have eConnect application
> which was initially run in multi thread. We have changed it to single
> thread and deadlocak numbers have come down but still it is high there.
>
> Before this issues, we were exeperiencing avg. disk queue length and
> page life expectancy issues. So we have increased RAM and now those two
> issues gone, but comes the high log flush time issue and system is slow
> down. Now page life expectany is around 10 minutes and Buffer cache hit
> ratio is almost 99%. Also procedure cache hit ration is just above 90%
> however Soptlight sometime throws message "56% of all the SQL
> compilation is recompilation" and "Size of Virtual page file down to
> 8%'. Are these also bottlenecks?

Not necessarily.

> We have following user defined settings:
> 1. SQL Server 2005 64 bit Standard with 8 processor
> 2. 64 GB RAM
> 3. AWE Enabled
> 4. Min 0 and Max 57 GB RAM allocation
> 5. Max Worker Thread 1024 9 (We almost have around 400-500 sessions
> running all the time)
> 6 Max DOP 1 (since multi threading was creating deadlocks we have changed
> this from 0 to 1)
> 7. Great planes database is on the same server/
>
> Also could you please tell me any of the above settings especially Max DOP
> or Max Worker thread affect the issue?

It's common to set MaxDOP to 1 for OLTP systems. I don't want to venture
about what is a good setting for Max Worker Threads.

I will have to admit that server configuration is not my field of
expertise. I rather look on a server from what queries are doing,
to see if queries can be improved or indexes be added. I can understand
that if you are running third-party applications only, your options are
a bit limited in that regard. Nevertheless, poorly tuned queries is
usually the root of all evil.

So I think you still need to dig under the surface to determine where
the slowness is coming from.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: SQL Server
Next: How to track transation activities in transaction log in sql serve