PAD_INDEX : activating or not ?

Hi all,

On SQL Server 7 SP4.

3 tables with about 100 000 rows each one, frequently updated.
1 clustered index on primary key, and 2 non clustered index on columns used by JOIN and by queries.

Fillfactor for index : 90.
PAD_INDEX : not activated.

I have some deadlocks problems, probably due to index pages and key locking (as precised in error log). Is it possible that the cause of that is splitting index pages while UPDATE ?

May I activate PAD_INDEX to solve part of this problem ?

Thanks for any helpIt's a performance issue thing, I don't think that's your problem

http://www.sql-server-performance.com/oltp_performance.asp|||Well, the nature of deadlocks has NOTHING to do with performance, thus cannot be fixed by recommendations given in the link. The only applicable advice which is not even in the section implied is to keep your transactions short. This will minimize the possibility of deadlocks, but will not eliminate them. As you all know, it deals with object access order, and should be addressed accordingly, by examining all possible transactions involving the same set of objects in reverse order.|||Thanks all, I'm going to have a look at it.|||pad index is really not neccesary. i have chased this guy down many roads
i even had extended conversations with kimberly tripp and kalen delaney on this and everyone i have talked to has told me that pad index is just not gonna provide any real advantage.

P4 xeon Hyperthreading

We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon 2.8
ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
processors.
I've heard that it performs better with HT turned off. Anyone here that?In general, no. However, SQL Server might parallize a query too much so setting maxdop to number pf
physical processors (using sp_configure) can be a good idea.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kevin Jackson" <softwiz@.covad.net> wrote in message news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon 2.8
> ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> processors.
> I've heard that it performs better with HT turned off. Anyone here that?
>|||If setting MaxDop to the actual no. of physical processor, wouldnt it not be
best to disable hyperthreading ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uHaWj9EhDHA.1952@.TK2MSFTNGP12.phx.gbl...
> The only issue I have seen with Hyperthreading is oversaturation of CPU
> resources by too much parallelism. Set your MAX Degree of Parallelism
> (MADXOP) down to the actual physical processor count and HT works just
fine.
> --
> Geoff N. Hiten
> SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Kevin Jackson" <softwiz@.covad.net> wrote in message
> news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> >
> > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon
> 2.8
> > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > processors.
> >
> > I've heard that it performs better with HT turned off. Anyone here
that?
> >
> >
>|||> If setting MaxDop to the actual no. of physical processor, wouldnt it
not be
> best to disable hyperthreading ?
MAXDOP applies to parallel queries only. The virtual processors can
benefit non-parallel queries as long as you don't disable
hyperthreading.
For example, on a dual Xeon with HT disabled, a CPU-bound parallel query
will degrade response time for other users. With HT enabled and MAXDOP
2, response time for the other users will be a bit better..
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"FR" <floydrev@.hotmail.com> wrote in message
news:u5y9r9LhDHA.3616@.TK2MSFTNGP11.phx.gbl...
> If setting MaxDop to the actual no. of physical processor, wouldnt it
not be
> best to disable hyperthreading ?
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uHaWj9EhDHA.1952@.TK2MSFTNGP12.phx.gbl...
> > The only issue I have seen with Hyperthreading is oversaturation of
CPU
> > resources by too much parallelism. Set your MAX Degree of
Parallelism
> > (MADXOP) down to the actual physical processor count and HT works
just
> fine.
> >
> > --
> > Geoff N. Hiten
> > SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> > >
> > > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad
P4 xeon
> > 2.8
> > > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees
8
> > > processors.
> > >
> > > I've heard that it performs better with HT turned off. Anyone
here
> that?
> > >
> > >
> >
> >
>|||> If you're not running advanced server and enterprise edition, you won't
> really be able to use more than 4 logical or physical processors.
Apparently SQL2K with sp3 should be HT aware and be able to use more logical processors then 4 on
SE.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:uLat%23WFhDHA.656@.TK2MSFTNGP12.phx.gbl...
> If you're not running advanced server and enterprise edition, you won't
> really be able to use more than 4 logical or physical processors.
> But to answer your question, it depends - we saw little difference, except
> that HT is off now everywhere because of OS/Hardware stability problems.
> Just test it for yourself.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kevin Jackson" <softwiz@.covad.net> wrote in message
> news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> >
> > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon
> 2.8
> > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > processors.
> >
> > I've heard that it performs better with HT turned off. Anyone here that?
> >
> >
>|||I don't think the OS will "mount" them ergo they're not available to SQL.
Not really sure tho.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ubaoQYYhDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > If you're not running advanced server and enterprise edition, you won't
> > really be able to use more than 4 logical or physical processors.
> Apparently SQL2K with sp3 should be HT aware and be able to use more
logical processors then 4 on
> SE.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uLat%23WFhDHA.656@.TK2MSFTNGP12.phx.gbl...
> > If you're not running advanced server and enterprise edition, you won't
> > really be able to use more than 4 logical or physical processors.
> >
> > But to answer your question, it depends - we saw little difference,
except
> > that HT is off now everywhere because of OS/Hardware stability problems.
> >
> > Just test it for yourself.
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> > >
> > > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4
xeon
> > 2.8
> > > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > > processors.
> > >
> > > I've heard that it performs better with HT turned off. Anyone here
that?
> > >
> > >
> >
> >
>|||From what I've heard the same goes fro the OS as for SQL Server. With some service pack, the OS
becomes HT aware and can use more logical processors then the edition allow.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:eHd$5O2hDHA.1048@.TK2MSFTNGP11.phx.gbl...
> I don't think the OS will "mount" them ergo they're not available to SQL.
> Not really sure tho.
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ubaoQYYhDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > > If you're not running advanced server and enterprise edition, you won't
> > > really be able to use more than 4 logical or physical processors.
> >
> > Apparently SQL2K with sp3 should be HT aware and be able to use more
> logical processors then 4 on
> > SE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:uLat%23WFhDHA.656@.TK2MSFTNGP12.phx.gbl...
> > > If you're not running advanced server and enterprise edition, you won't
> > > really be able to use more than 4 logical or physical processors.
> > >
> > > But to answer your question, it depends - we saw little difference,
> except
> > > that HT is off now everywhere because of OS/Hardware stability problems.
> > >
> > > Just test it for yourself.
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > > news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> > > >
> > > > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4
> xeon
> > > 2.8
> > > > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > > > processors.
> > > >
> > > > I've heard that it performs better with HT turned off. Anyone here
> that?
> > > >
> > > >
> > >
> > >
> >
> >
>