Prev: SQL Server
Next: How to track transation activities in transaction log in sql serve
From: Malkesh on 22 Jul 2010 17:46
Since i'm not primary DBA and i'm introduced just because these issue, i've
no idea about the application coding. Also i found that more than 500 indexes
on the main database is highly fragmented ranging from 30% to 99%. I'm
planning to run the maintenance job for this.
Will it contibute to deadlocking?
--
Thanks & Regards
Malkesh


"Erland Sommarskog" wrote:

> 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
>
> .
>
From: Erland Sommarskog on 23 Jul 2010 09:06
Malkesh (Malkesh(a)discussions.microsoft.com) writes:
> Since i'm not primary DBA and i'm introduced just because these issue,
> i've no idea about the application coding. Also i found that more than
> 500 indexes on the main database is highly fragmented ranging from 30%
> to 99%. I'm planning to run the maintenance job for this.
> Will it contibute to deadlocking?

Defragmenting heavily fragmented indexes can certainly help to alleviate
the deadlock situation. If queries can complete faster, this also reduces
the window in which deadlocks can occur.

--
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

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