Performance testing - tips

I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. In
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly in
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:
> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the query
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAIDs
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not
>> performing
>> like it should. What we expericence is ok performance running single long
>> running queries. But running 2-3 long running queries at the same time
>> really makes the whole server very slow. Imagine this:
>> 1) Start 1 long running query accessing 2 large tables. CPU is used
>> 25-30%
>> 2) At the same time, run a select on a small table not involved in the
>> query
>> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
>> not display until the first query is done. There is no lock-situation
>> involved.
>> I have done the same thing on SQL Server 2000 with slower hardware for
>> several years. Sadly I don't have access to the old machine anymore, so
>> it's
>> hard to tell the real difference.
>> CPU is not an issue - SQL server is using only 25-50 percent. So my first
>> conclusion is that the disk-system is not performing well. We separate
>> RAIDs
>> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
>> (raid1). The raids are spread on 2 controllers with 2 channels each.
>> But where to start? Does anyone have some stored procedures+testdata
>> which I
>> can use as a start to compare with?
>> Per Schjetne
>>

Performance testing - tips

I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. I
n
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly i
n
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:

> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performin
g
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the que
ry
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it
's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAI
Ds
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which
I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...[vbcol=seagreen]
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary

> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right? :-))
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||See also
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Linchi
"cpchan" wrote:

> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||noted and thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45ECE387-9152-4E2A-8B07-31572C29152B@.microsoft.com...
> See also
>
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-
should-i-create-for-a-user-database.aspx[vbcol=seagreen]
> Linchi
> "cpchan" wrote:
thread[vbcol=seagreen]
file,[vbcol=seagreen]
files[vbcol=seagreen]
|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right?
:-))[vbcol=seagreen]
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files
>
|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-dis
cussed.aspx.[vbcol=seagreen]
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
>
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files
>

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right' :-))
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||See also
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Linchi
"cpchan" wrote:
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||noted and thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45ECE387-9152-4E2A-8B07-31572C29152B@.microsoft.com...
> See also
>
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-
should-i-create-for-a-user-database.aspx
> Linchi
> "cpchan" wrote:
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
> >|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-dis
cussed.aspx.
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right'
:-))
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>sql

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archiv...d
.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary

> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right' :-))
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right'
:-))
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files[vbcol=seagreen]
>|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archiv...ban-legends-dis
cussed.aspx.
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
>
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files[vbcol=seagreen]
>

performance question - SQL2k sp3

We moved a large database & application from a single server with RAID
drives to a clustered server with a SAN and are not seeing any performance
gains. Statistics were updated, procs recompiled, etc. after the move. The
CPU load is lower (as seen in perfmon), and the SAN disks are also not
working nearly as hard as the RAID was, but the application shows no speed
improvement. Any thoughts on where to look next?
thx.
k
Hi
Generally, moving to a SAN will not improve performance, but might decrease
it due to the higher IO latency.
Disk layout is important, make sure your transaction logs are on a seperate
volume, with RAID-1 or RAID 10. If possible, don't put your data on RAID-5,
but RAID-10.
Run profiler to find your slowest queries and see if you can not optimize
them with indexing.
Regards
Mike
"wasnotwas" wrote:

> We moved a large database & application from a single server with RAID
> drives to a clustered server with a SAN and are not seeing any performance
> gains. Statistics were updated, procs recompiled, etc. after the move. The
> CPU load is lower (as seen in perfmon), and the SAN disks are also not
> working nearly as hard as the RAID was, but the application shows no speed
> improvement. Any thoughts on where to look next?
> thx.
> k
>
>

Performance Question

I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
processor. Would my performance be better served by adding the 2nd CPU or
upgrading the current CPU to a 3.2GHz?
Any thoughts are welcome,
ScottGenerally I would go with a multi proc environment as you could
simultaneously serve mulitple requests faster although individual request ma
y
be served faster by increasing the clock speed of the single processor.
Licensing would also be a factor in my decision.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Scott Cadreau" wrote:

> I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
> processor. Would my performance be better served by adding the 2nd CPU or
> upgrading the current CPU to a 3.2GHz?
> Any thoughts are welcome,
> Scott
>
>

Performance Problems in SQL Server 2000

Hello All,
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select
*
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows?
The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA,
did you try both text and grid
mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTN
GP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ selec
t *
> from myTable ] query on the same machine where DB is installed. Also, I ha
ve
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>|||Dear Irfan,
Please check your statistics using "DBCC SHOW_STATISTICS" if your table stat
ictics are not update properly than update using "update STATISTICS " or "sp
_updatestats".Hope it will help u .
Faheem
NETSOL

Performance Problems in SQL Server 2000

Hello All,
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select *
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.
Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows? The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA, did you try both text and grid
mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ select *
> from myTable ] query on the same machine where DB is installed. Also, I have
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>
|||Dear Irfan,
Please check your statistics using "DBCC SHOW_STATISTICS" if your table statictics are not update properly than update using "update STATISTICS " or "sp_updatestats".Hope it will help u .
Faheem
NETSOL

Performance Problems in SQL Server 2000

Hello All,
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select *
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows? The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA, did you try both text and grid
mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ select *
> from myTable ] query on the same machine where DB is installed. Also, I have
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>|||Dear Irfan
Please check your statistics using "DBCC SHOW_STATISTICS" if your table statictics are not update properly than update using "update STATISTICS " or "sp_updatestats".Hope it will help u
Faheem
NETSOL

Performance of Hardware

Hey all,
I have 2 servers that I am interested in SQL Server 2000. One is a
single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. This
server is running SBS2k3 running as a domain controller and Exchange. The
other is a dual proc AMD Athlon MP with 1500's in them. This server has
768MB and is running Windows 2003 Enterprise with RRAS acting as the gateway
to the internet, DNS, and DHCP. My question is which is the lesser evil? I
want to try out Microsoft CRM 1.2 and trying to balance out the workload.
Any help would be much appriciated.
MichaelMichael,
Some questions to point you down the right track:
What system has the better disc subsystem?
Check perfmon for current CPU loadings etc?
Mike John
"Michael Sainz" <mike@.twofatfrogs(remove).com> wrote in message =news:efCVtdA1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hey all,
> I have 2 servers that I am interested in SQL Server 2000. One is a
> single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. =This
> server is running SBS2k3 running as a domain controller and Exchange. =The
> other is a dual proc AMD Athlon MP with 1500's in them. This server =has
> 768MB and is running Windows 2003 Enterprise with RRAS acting as the =gateway
> to the internet, DNS, and DHCP. My question is which is the lesser =evil? I
> want to try out Microsoft CRM 1.2 and trying to balance out the =workload.
> Any help would be much appriciated.
> > Michael
> >|||John,
The Intel system (DC, Exchange2k3) has a single 80GB SATA Drive. The AMD
has dual 80GB in a mirrored array (hardware). In terms of performance and
load...there are only 3 clients ever connected so I dont think this is an
issue at all, but I just want to make sure and that is why i'm posting.
Thanks!
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:ug7sctJ1DHA.3468@.TK2MSFTNGP11.phx.gbl...
Michael,
Some questions to point you down the right track:
What system has the better disc subsystem?
Check perfmon for current CPU loadings etc?
Mike John
"Michael Sainz" <mike@.twofatfrogs(remove).com> wrote in message
news:efCVtdA1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hey all,
> I have 2 servers that I am interested in SQL Server 2000. One is a
> single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. This
> server is running SBS2k3 running as a domain controller and Exchange. The
> other is a dual proc AMD Athlon MP with 1500's in them. This server has
> 768MB and is running Windows 2003 Enterprise with RRAS acting as the
gateway
> to the internet, DNS, and DHCP. My question is which is the lesser evil? I
> want to try out Microsoft CRM 1.2 and trying to balance out the workload.
> Any help would be much appriciated.
> Michael
>