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 problem with RS XML data source

Hi,
I am experiencing unacceptable performance when reading XML files using a
custom XML data processing extensions with RS on SS 2000.
The XML data processing extensions I have used is the one on the MSDN site
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp)
This works fine with low volumes of data but as the number of records
increase in the XML file the time taken to open the file increases
exponentially. The timings I have recorded are:
records seconds
1,000 = 1
2,500 = 3
5,000 = 6
10,000 = 22
15,000 = 75
The largest XML file I have contains 40,000 records and takes about 1200
secs (20 mins) to open which is a show stopper.
Has anyone else run into this problem and if so how did you resolve it?
An example of XML File
<REPORT>
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/files123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></base>
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/files123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></base>
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/files123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></base>
.
</REPORT>
My thanks,
JezHello Jez,
Based on the code for the dataset extension, it uses ReadXml mehtod to read
data from xml file.
internal void CreateDataSet(string schemaFile)
{
// Open an XML doc to hold the data.
XmlDocument xmlDoc = new XmlDocument();
// Create the DataSet.
DataSet ds = new DataSet("Customers");
// Create the schema for the DataSet.
ds.ReadXmlSchema(schemaFile);
// Parse the command text string for the files.
string[] parameters = this.ParseCmdText();
// Get the XML data and
// merge it into the DataSet.
try
{
for(int i=0;i<parameters.GetLength(0);i++)
{
DataSet tempDs = new DataSet();
tempDs.ReadXml(parameters[i]);
ds.Merge(tempDs);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
// Set the DataSet variable used in
// the rest of the DataReader members
// to the one just produced.
_ds = ds;
// Set the current row to -1
// to prepare for reading.
_currentRow = -1;
}
You may want to use this function in your own test if the big file take
much time. Also, because reporting services rending all pages before
returning HTML stream to the client, it may take a lot of of time. You may
want to use some constraint to query less records or less columns to make
the processing/rendering time less.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "jez" <jnel@.nospam.nospam>
| Subject: Performance problem with RS XML data source
| Date: Thu, 7 Apr 2005 18:30:47 +0100
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <etpwKe5OFHA.3748@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: host217-42-145-128.range217-42.btcentralplus.com
217.42.145.128
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:40305
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
|
|
|
| I am experiencing unacceptable performance when reading XML files using a
| custom XML data processing extensions with RS on SS 2000.
|
|
|
| The XML data processing extensions I have used is the one on the MSDN
site
|
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
ml/RSDSetEx3.asp)
|
|
|
| This works fine with low volumes of data but as the number of records
| increase in the XML file the time taken to open the file increases
| exponentially. The timings I have recorded are:
|
|
|
| records seconds
|
| 1,000 = 1
|
| 2,500 = 3
|
| 5,000 = 6
|
| 10,000 = 22
|
| 15,000 = 75
|
|
|
|
|
| The largest XML file I have contains 40,000 records and takes about 1200
| secs (20 mins) to open which is a show stopper.
|
|
|
| Has anyone else run into this problem and if so how did you resolve it?
|
|
|
| An example of XML File
|
|
|
| <REPORT>
|
|
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
e>
|
|
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
e>
|
|
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
e>
|
| .
|
| </REPORT>
|
|
|
| My thanks,
|
| Jez
|
|
||||Peter,
I had a look at the code sample you provided and it appears to be the same
as the code on the MSDN site. Therefore using this code for the large file
(40,000 rows) will give identical performance. Is this the example you
meant to send?
Is it the ReadXml method that is causing the problem? If I changed to file
format to CSV would I see better performance?
Thanks,
Jez
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:yVaWd4$OFHA.4360@.TK2MSFTNGXA01.phx.gbl...
> Hello Jez,
> Based on the code for the dataset extension, it uses ReadXml mehtod to
> read
> data from xml file.
> internal void CreateDataSet(string schemaFile)
> {
> // Open an XML doc to hold the data.
> XmlDocument xmlDoc = new XmlDocument();
> // Create the DataSet.
> DataSet ds = new DataSet("Customers");
> // Create the schema for the DataSet.
> ds.ReadXmlSchema(schemaFile);
> // Parse the command text string for the files.
> string[] parameters = this.ParseCmdText();
> // Get the XML data and
> // merge it into the DataSet.
> try
> {
> for(int i=0;i<parameters.GetLength(0);i++)
> {
> DataSet tempDs = new DataSet();
> tempDs.ReadXml(parameters[i]);
> ds.Merge(tempDs);
> }
> }
> catch (Exception e)
> {
> throw new Exception(e.Message);
> }
> // Set the DataSet variable used in
> // the rest of the DataReader members
> // to the one just produced.
> _ds = ds;
> // Set the current row to -1
> // to prepare for reading.
> _currentRow = -1;
> }
> You may want to use this function in your own test if the big file take
> much time. Also, because reporting services rending all pages before
> returning HTML stream to the client, it may take a lot of of time. You may
> want to use some constraint to query less records or less columns to make
> the processing/rendering time less.
>
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | From: "jez" <jnel@.nospam.nospam>
> | Subject: Performance problem with RS XML data source
> | Date: Thu, 7 Apr 2005 18:30:47 +0100
> | Lines: 68
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <etpwKe5OFHA.3748@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: host217-42-145-128.range217-42.btcentralplus.com
> 217.42.145.128
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:40305
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi,
> |
> |
> |
> | I am experiencing unacceptable performance when reading XML files using
> a
> | custom XML data processing extensions with RS on SS 2000.
> |
> |
> |
> | The XML data processing extensions I have used is the one on the MSDN
> site
> |
> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
> ml/RSDSetEx3.asp)
> |
> |
> |
> | This works fine with low volumes of data but as the number of records
> | increase in the XML file the time taken to open the file increases
> | exponentially. The timings I have recorded are:
> |
> |
> |
> | records seconds
> |
> | 1,000 = 1
> |
> | 2,500 = 3
> |
> | 5,000 = 6
> |
> | 10,000 = 22
> |
> | 15,000 = 75
> |
> |
> |
> |
> |
> | The largest XML file I have contains 40,000 records and takes about 1200
> | secs (20 mins) to open which is a show stopper.
> |
> |
> |
> | Has anyone else run into this problem and if so how did you resolve it?
> |
> |
> |
> | An example of XML File
> |
> |
> |
> | <REPORT>
> |
> |
> <base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
> iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
> j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
> e>
> |
> |
> <base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
> iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
> j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
> e>
> |
> |
> <base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
> iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
> j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
> e>
> |
> | .
> |
> | </REPORT>
> |
> |
> |
> | My thanks,
> |
> | Jez
> |
> |
> |
>|||Hello Jez,
I included the code from the MSDN site is just to describe that it will
take much time to read/process big xml file into dataset. If csv type
loading is faster, the whole performance might be better. However, if you
import the data into SQL server directly, I believe it will be benefit for
performance.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "jez" <jnel@.nospam.nospam>
| References: <etpwKe5OFHA.3748@.TK2MSFTNGP10.phx.gbl>
<yVaWd4$OFHA.4360@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: Performance problem with RS XML data source
| Date: Mon, 11 Apr 2005 10:16:59 +0100
| Lines: 190
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <#day4cnPFHA.708@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 213.86.115.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:40485
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Peter,
|
| I had a look at the code sample you provided and it appears to be the
same
| as the code on the MSDN site. Therefore using this code for the large
file
| (40,000 rows) will give identical performance. Is this the example you
| meant to send?
|
| Is it the ReadXml method that is causing the problem? If I changed to
file
| format to CSV would I see better performance?
|
| Thanks,
| Jez
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:yVaWd4$OFHA.4360@.TK2MSFTNGXA01.phx.gbl...
| > Hello Jez,
| >
| > Based on the code for the dataset extension, it uses ReadXml mehtod to
| > read
| > data from xml file.
| >
| > internal void CreateDataSet(string schemaFile)
| > {
| >
| > // Open an XML doc to hold the data.
| > XmlDocument xmlDoc = new XmlDocument();
| > // Create the DataSet.
| > DataSet ds = new DataSet("Customers");
| > // Create the schema for the DataSet.
| > ds.ReadXmlSchema(schemaFile);
| > // Parse the command text string for the files.
| > string[] parameters = this.ParseCmdText();
| > // Get the XML data and
| > // merge it into the DataSet.
| > try
| > {
| > for(int i=0;i<parameters.GetLength(0);i++)
| > {
| > DataSet tempDs = new DataSet();
| > tempDs.ReadXml(parameters[i]);
| > ds.Merge(tempDs);
| > }
| >
| > }
| > catch (Exception e)
| > {
| > throw new Exception(e.Message);
| > }
| >
| > // Set the DataSet variable used in
| > // the rest of the DataReader members
| > // to the one just produced.
| > _ds = ds;
| > // Set the current row to -1
| > // to prepare for reading.
| > _currentRow = -1;
| >
| > }
| >
| > You may want to use this function in your own test if the big file take
| > much time. Also, because reporting services rending all pages before
| > returning HTML stream to the client, it may take a lot of of time. You
may
| > want to use some constraint to query less records or less columns to
make
| > the processing/rendering time less.
| >
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| >
| >
| > --
| > | From: "jez" <jnel@.nospam.nospam>
| > | Subject: Performance problem with RS XML data source
| > | Date: Thu, 7 Apr 2005 18:30:47 +0100
| > | Lines: 68
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <etpwKe5OFHA.3748@.TK2MSFTNGP10.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: host217-42-145-128.range217-42.btcentralplus.com
| > 217.42.145.128
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:40305
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Hi,
| > |
| > |
| > |
| > | I am experiencing unacceptable performance when reading XML files
using
| > a
| > | custom XML data processing extensions with RS on SS 2000.
| > |
| > |
| > |
| > | The XML data processing extensions I have used is the one on the MSDN
| > site
| > |
| >
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
| > ml/RSDSetEx3.asp)
| > |
| > |
| > |
| > | This works fine with low volumes of data but as the number of records
| > | increase in the XML file the time taken to open the file increases
| > | exponentially. The timings I have recorded are:
| > |
| > |
| > |
| > | records seconds
| > |
| > | 1,000 = 1
| > |
| > | 2,500 = 3
| > |
| > | 5,000 = 6
| > |
| > | 10,000 = 22
| > |
| > | 15,000 = 75
| > |
| > |
| > |
| > |
| > |
| > | The largest XML file I have contains 40,000 records and takes about
1200
| > | secs (20 mins) to open which is a show stopper.
| > |
| > |
| > |
| > | Has anyone else run into this problem and if so how did you resolve
it?
| > |
| > |
| > |
| > | An example of XML File
| > |
| > |
| > |
| > | <REPORT>
| > |
| > |
| >
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
| >
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
| >
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
| > e>
| > |
| > |
| >
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
| >
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
| >
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
| > e>
| > |
| > |
| >
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
| >
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
| >
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
| > e>
| > |
| > | .
| > |
| > | </REPORT>
| > |
| > |
| > |
| > | My thanks,
| > |
| > | Jez
| > |
| > |
| > |
| >
|
|
|

Performance on SQL 2000

I have a SQL server with one file group that's about 600GB and two files
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks and
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:
> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks and
> see that they often break up the database into a number of files with 200GB
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney among
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>> I have a SQL server with one file group that's about 600GB and two files
>> residing on a clariion array. Index jobs, etc. appear to run faster if
>> I
>> create more files. I looked at some of microsoft's tpc server benchmarks
>> and
>> see that they often break up the database into a number of files with
>> 200GB
>> as the max file size I've seen.
>> Does anyone out there know of any articles, web sites or whatever that
>> specifically address this? Are users out there creating large numbers
>> of
>> files and I'm in the dark? Articles by reputable sources (K. Delaney
>> among
>> just a few) are what I'm looking for.
>> --
>> burt_king@.yahoo.com

Performance Monitor log files

For the SQL Server running here we have setup a log file that monitors all the activities while the server is running, such as processor percentage being used over the day, CPU percentage taken by SQL Server etc. The problem I'm facing is that the log files (which are in PERFMON format) are recorded over a period of a business day for 30 days. Now I want to get the measurements for all the 30 days combined into one file where i can analyze all the measurement over 30 days.

Thanks in advance.HI

I dont know of any way to combine log files. The best you can do is establish numeric values then manually put these into a spreadsheet if you want a graph.

In future, just run the perfmon monitoring for 30 days......

Hope this helps,

SG