Dear All,
My Database Server is using RAID 5.
I put the mdf and ldf file of my database at the same drive.
Does it affect the SQL Server Performance?
Thanks
Robert LieThe best thing would be not to use Raid 5 due to performance.
For best overall performance, locate the database files (.mdf) and log files
(.ldf) on separate arrays in your server to isolate potentially conflicting
reads and writes. [6.5, 7.0, 2000] Updated 12-20-2004
*****
To store your database files (.mdf), the best performance is gained by
storing them using RAID 10 arrays. If this is too expensive, then RAID 5 is
the next best bet. Each RAID array (5 or 10) should have as many physical
disks in the array as the controller will support. This allows reads and
writes to be performed simultaneously on each physical drive in the array,
significantly boosting disk I/O. [6.5, 7.0, 2000] Updated 12-20-2004
From: http://www.sql-server-performance.com/sql_server_setup.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie|||Hi Robert,
yes but it only becomes a problem in very large databases, the ldf (log
file) should be on a drive of its own in a perfect world and using RAID5 for
it is overkill and will slow it's performance as the log file is written to
sequentially and most other things including the mdf file are written to
randomly. Again this is only a concirn is big installations and if you have
a spare physical drive then the ldf file could be put on that.
--
Regards
Alex White MCDBA MCSE
http://www.intralan.co.uk
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie|||Hi,
It is always recommended to put Data files and Log files in multiple drives,
it will be even better if you put the files in drives belongs to different
disk controllers.
This will help you to reduce the disk I/O and eventually the better
performance.
But incase if your volume pd transaction is less then you may not have any
I/O issues, inthis case you can keep both the files in same drives which
belongs to same disk controller. And monitor the Disk Queue length , all
hardware resorce usage using Perfmon.
If you find every thing is normal then you are good. See the website
http://www.sql-server-performance.com/ for more performanace monitoring and
tuning options.
Thanks
Hari
SQL server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Maximun Worker Threads
Hello All,
I'm having performance problem with my database server:
My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
2003 Std Edition server, SQL 2000 Std Edition SP4.
I notice the slowness when there are more that 255 connections were made to
the SQL. Would it help if I increase the work threads?
Please Advise. Thanks.SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
memory MAX. So if you really have 8 dual core processors and 255 concurrent
connections I suggest you look at upgrading the edition and or at least the
version. Max worker threads is not your problem but what exactly is can not
be determined from this little bit of information.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made
> to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Thanks for the reply.
Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
dual-core would be 8 processors?
My question is Can I increase the number of maximun worker threads? I see
whenever it reach over maximun threads (255) I start to see the latency.
Thanks.
"Andrew J. Kelly" wrote:
> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
> memory MAX. So if you really have 8 dual core processors and 255 concurrent
> connections I suggest you look at upgrading the edition and or at least the
> version. Max worker threads is not your problem but what exactly is can not
> be determined from this little bit of information.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> > Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made
> > to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.
>|||From BOL:
The default setting for max worker threads (255) is best for most systems.
However, depending on your system configuration, setting max worker threads
to a smaller value sometimes improves performance.
When the actual number of user connections is less than the amount set in
max worker threads, one thread handles each connection. However, if the
actual number of connections exceeds the amount set in max worker threads,
SQL Server pools the worker threads so that the next available worker thread
can handle the request.
Yes, you can increase max worker threads. Most recommendations you will
find are to not mess with it. I would go up in small increments, and stay
way away from the upper limit of 32767. Also note that this setting take a
restart of sql server prior to the change taking effect.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||> Would it help if I increase the work threads?
Collect the SQL wait stats, and check if there are a lot of waits on
ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
for the worker. In general, you shouldn't see any significant waits on the
worker at all.
Linchi
"David" wrote:
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Can you increase this, yes of course you can. But you need to determine that
adding more threads will actually help. If you have a lot of waits on the
threads it can be a sign you need more. But I am willing to bet that your
system as configured can not adequately handle that many threads. If the
system (both hardware and database) was properly configured and tuned you
would most likely never need more than 255 worker threads. Try bumping it
and see if it helps but I would start to tune the system or think about
moving to SQL2005 so you can use more memory effectively.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||Linchi-
SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
SQL2000?
Thanks.
"Linchi Shea" wrote:
> > Would it help if I increase the work threads?
> Collect the SQL wait stats, and check if there are a lot of waits on
> ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> for the worker. In general, you shouldn't see any significant waits on the
> worker at all.
> Linchi
> "David" wrote:
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.|||Oops! I didn't realize you were using SQL2000. In that case, just collect the
SQL wait stats via dbcc sqlperf(waitstats).
Linchi
"David" wrote:
> Linchi-
> SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
> SQL2000?
> Thanks.
> "Linchi Shea" wrote:
> > > Would it help if I increase the work threads?
> >
> > Collect the SQL wait stats, and check if there are a lot of waits on
> > ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> > for the worker. In general, you shouldn't see any significant waits on the
> > worker at all.
> >
> > Linchi
> >
> > "David" wrote:
> >
> > > Hello All,
> > >
> > > I'm having performance problem with my database server:
> > > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> > >
> > > I notice the slowness when there are more that 255 connections were made to
> > > the SQL. Would it help if I increase the work threads?
> > >
> > > Please Advise. Thanks.
I'm having performance problem with my database server:
My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
2003 Std Edition server, SQL 2000 Std Edition SP4.
I notice the slowness when there are more that 255 connections were made to
the SQL. Would it help if I increase the work threads?
Please Advise. Thanks.SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
memory MAX. So if you really have 8 dual core processors and 255 concurrent
connections I suggest you look at upgrading the edition and or at least the
version. Max worker threads is not your problem but what exactly is can not
be determined from this little bit of information.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made
> to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Thanks for the reply.
Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
dual-core would be 8 processors?
My question is Can I increase the number of maximun worker threads? I see
whenever it reach over maximun threads (255) I start to see the latency.
Thanks.
"Andrew J. Kelly" wrote:
> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
> memory MAX. So if you really have 8 dual core processors and 255 concurrent
> connections I suggest you look at upgrading the edition and or at least the
> version. Max worker threads is not your problem but what exactly is can not
> be determined from this little bit of information.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> > Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made
> > to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.
>|||From BOL:
The default setting for max worker threads (255) is best for most systems.
However, depending on your system configuration, setting max worker threads
to a smaller value sometimes improves performance.
When the actual number of user connections is less than the amount set in
max worker threads, one thread handles each connection. However, if the
actual number of connections exceeds the amount set in max worker threads,
SQL Server pools the worker threads so that the next available worker thread
can handle the request.
Yes, you can increase max worker threads. Most recommendations you will
find are to not mess with it. I would go up in small increments, and stay
way away from the upper limit of 32767. Also note that this setting take a
restart of sql server prior to the change taking effect.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||> Would it help if I increase the work threads?
Collect the SQL wait stats, and check if there are a lot of waits on
ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
for the worker. In general, you shouldn't see any significant waits on the
worker at all.
Linchi
"David" wrote:
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Can you increase this, yes of course you can. But you need to determine that
adding more threads will actually help. If you have a lot of waits on the
threads it can be a sign you need more. But I am willing to bet that your
system as configured can not adequately handle that many threads. If the
system (both hardware and database) was properly configured and tuned you
would most likely never need more than 255 worker threads. Try bumping it
and see if it helps but I would start to tune the system or think about
moving to SQL2005 so you can use more memory effectively.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||Linchi-
SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
SQL2000?
Thanks.
"Linchi Shea" wrote:
> > Would it help if I increase the work threads?
> Collect the SQL wait stats, and check if there are a lot of waits on
> ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> for the worker. In general, you shouldn't see any significant waits on the
> worker at all.
> Linchi
> "David" wrote:
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.|||Oops! I didn't realize you were using SQL2000. In that case, just collect the
SQL wait stats via dbcc sqlperf(waitstats).
Linchi
"David" wrote:
> Linchi-
> SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
> SQL2000?
> Thanks.
> "Linchi Shea" wrote:
> > > Would it help if I increase the work threads?
> >
> > Collect the SQL wait stats, and check if there are a lot of waits on
> > ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> > for the worker. In general, you shouldn't see any significant waits on the
> > worker at all.
> >
> > Linchi
> >
> > "David" wrote:
> >
> > > Hello All,
> > >
> > > I'm having performance problem with my database server:
> > > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> > >
> > > I notice the slowness when there are more that 255 connections were made to
> > > the SQL. Would it help if I increase the work threads?
> > >
> > > Please Advise. Thanks.
Subscribe to:
Posts (Atom)