Svein Terje Gaup wrote:
> Hi, I have a SQL Server 2000 with 14 different databases. I have been
> monitoring it for some time, and it seems there is a high number of
> page faults occurring. The number is constantly above 3000. The server
> has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> The Pages/sec counter is stabilised at a very low level (mostly it is
> at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> dedicated SQL Server.
> So what could be causing the high amount of Page Faults?
> Sincerely
> Svein Terje Gaup
>
Do you have something copying files off of this machine, perhaps a tape
backup or something copy files across the network? I see this sort of
thing when copying files from server to server. The page faults occur
as the files are read from disk.Hi, I have a SQL Server 2000 with 14 different databases. I have been
monitoring it for some time, and it seems there is a high number of
page faults occurring. The number is constantly above 3000. The server
has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
The Pages/sec counter is stabilised at a very low level (mostly it is
at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
dedicated SQL Server.
So what could be causing the high amount of Page Faults?
Sincerely
Svein Terje Gaup|||Svein Terje Gaup wrote:
> Hi, I have a SQL Server 2000 with 14 different databases. I have been
> monitoring it for some time, and it seems there is a high number of
> page faults occurring. The number is constantly above 3000. The server
> has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> The Pages/sec counter is stabilised at a very low level (mostly it is
> at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> dedicated SQL Server.
> So what could be causing the high amount of Page Faults?
> Sincerely
> Svein Terje Gaup
>
Do you have something copying files off of this machine, perhaps a tape
backup or something copy files across the network? I see this sort of
thing when copying files from server to server. The page faults occur
as the files are read from disk.|||Thank you for your answer. I will try to look into it, and see if any
jobs are running that copies files from the server.
The Page Faults are constantly over 3000 during the whole working day.
Maybe there's some fileshare that is accessed constantly by users (it's
a customer's server, and they said it was dedicated as a sql server,
but who knows...) ? Anyway, I will get back with any results.
Sincerely
Svein Terje Gaup
Tracy McKibben wrote:
> Svein Terje Gaup wrote:
> Do you have something copying files off of this machine, perhaps a tape
> backup or something copy files across the network? I see this sort of
> thing when copying files from server to server. The page faults occur
> as the files are read from disk.|||Thank you for your answer. I will try to look into it, and see if any
jobs are running that copies files from the server.
The Page Faults are constantly over 3000 during the whole working day.
Maybe there's some fileshare that is accessed constantly by users (it's
a customer's server, and they said it was dedicated as a sql server,
but who knows...) ? Anyway, I will get back with any results.
Sincerely
Svein Terje Gaup
Tracy McKibben wrote:
> Svein Terje Gaup wrote:
> Do you have something copying files off of this machine, perhaps a tape
> backup or something copy files across the network? I see this sort of
> thing when copying files from server to server. The page faults occur
> as the files are read from disk.|||I'd try to find out what process(es) is primarily responsible for these page
faults. Go to the Processes tab on Task Manager, and rank the output by page
Faults or PF delta. It's common to have page faults in hundreds without any
performance issue. But if it's constantly around 3000, I'd conisder that hig
h
(just from the numbers I've seen on some of the busy servers in my
environments).
Linchi
"Svein Terje Gaup" wrote:
> Thank you for your answer. I will try to look into it, and see if any
> jobs are running that copies files from the server.
> The Page Faults are constantly over 3000 during the whole working day.
> Maybe there's some fileshare that is accessed constantly by users (it's
> a customer's server, and they said it was dedicated as a sql server,
> but who knows...) ? Anyway, I will get back with any results.
> Sincerely
> Svein Terje Gaup
>
> Tracy McKibben wrote:
>|||I'd try to find out what process(es) is primarily responsible for these page
faults. Go to the Processes tab on Task Manager, and rank the output by page
Faults or PF delta. It's common to have page faults in hundreds without any
performance issue. But if it's constantly around 3000, I'd conisder that hig
h
(just from the numbers I've seen on some of the busy servers in my
environments).
Linchi
"Svein Terje Gaup" wrote:
> Thank you for your answer. I will try to look into it, and see if any
> jobs are running that copies files from the server.
> The Page Faults are constantly over 3000 during the whole working day.
> Maybe there's some fileshare that is accessed constantly by users (it's
> a customer's server, and they said it was dedicated as a sql server,
> but who knows...) ? Anyway, I will get back with any results.
> Sincerely
> Svein Terje Gaup
>
> Tracy McKibben wrote:
>|||Thank you. Thats a good idea. I'll try it next time I'm on-site.
Sincerely
Svein Terje Gaup
Linchi Shea wrote:[vbcol=seagreen]
> I'd try to find out what process(es) is primarily responsible for these pa
ge
> faults. Go to the Processes tab on Task Manager, and rank the output by pa
ge
> Faults or PF delta. It's common to have page faults in hundreds without an
y
> performance issue. But if it's constantly around 3000, I'd conisder that h
igh
> (just from the numbers I've seen on some of the busy servers in my
> environments).
> Linchi
> "Svein Terje Gaup" wrote:
>
Showing posts with label monitoring. Show all posts
Showing posts with label monitoring. Show all posts
Wednesday, March 21, 2012
High number of Page Faults
Hi, I have a SQL Server 2000 with 14 different databases. I have been
monitoring it for some time, and it seems there is a high number of
page faults occurring. The number is constantly above 3000. The server
has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
The Pages/sec counter is stabilised at a very low level (mostly it is
at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
dedicated SQL Server.
So what could be causing the high amount of Page Faults?
Sincerely
Svein Terje GaupSvein Terje Gaup wrote:
> Hi, I have a SQL Server 2000 with 14 different databases. I have been
> monitoring it for some time, and it seems there is a high number of
> page faults occurring. The number is constantly above 3000. The server
> has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> The Pages/sec counter is stabilised at a very low level (mostly it is
> at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> dedicated SQL Server.
> So what could be causing the high amount of Page Faults?
> Sincerely
> Svein Terje Gaup
>
Do you have something copying files off of this machine, perhaps a tape
backup or something copy files across the network? I see this sort of
thing when copying files from server to server. The page faults occur
as the files are read from disk.|||Thank you for your answer. I will try to look into it, and see if any
jobs are running that copies files from the server.
The Page Faults are constantly over 3000 during the whole working day.
Maybe there's some fileshare that is accessed constantly by users (it's
a customer's server, and they said it was dedicated as a sql server,
but who knows...) ? Anyway, I will get back with any results.
Sincerely
Svein Terje Gaup
Tracy McKibben wrote:
> Svein Terje Gaup wrote:
> > Hi, I have a SQL Server 2000 with 14 different databases. I have been
> > monitoring it for some time, and it seems there is a high number of
> > page faults occurring. The number is constantly above 3000. The server
> > has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> > The Pages/sec counter is stabilised at a very low level (mostly it is
> > at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> > dedicated SQL Server.
> >
> > So what could be causing the high amount of Page Faults?
> >
> > Sincerely
> > Svein Terje Gaup
> >
> Do you have something copying files off of this machine, perhaps a tape
> backup or something copy files across the network? I see this sort of
> thing when copying files from server to server. The page faults occur
> as the files are read from disk.
monitoring it for some time, and it seems there is a high number of
page faults occurring. The number is constantly above 3000. The server
has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
The Pages/sec counter is stabilised at a very low level (mostly it is
at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
dedicated SQL Server.
So what could be causing the high amount of Page Faults?
Sincerely
Svein Terje GaupSvein Terje Gaup wrote:
> Hi, I have a SQL Server 2000 with 14 different databases. I have been
> monitoring it for some time, and it seems there is a high number of
> page faults occurring. The number is constantly above 3000. The server
> has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> The Pages/sec counter is stabilised at a very low level (mostly it is
> at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> dedicated SQL Server.
> So what could be causing the high amount of Page Faults?
> Sincerely
> Svein Terje Gaup
>
Do you have something copying files off of this machine, perhaps a tape
backup or something copy files across the network? I see this sort of
thing when copying files from server to server. The page faults occur
as the files are read from disk.|||Thank you for your answer. I will try to look into it, and see if any
jobs are running that copies files from the server.
The Page Faults are constantly over 3000 during the whole working day.
Maybe there's some fileshare that is accessed constantly by users (it's
a customer's server, and they said it was dedicated as a sql server,
but who knows...) ? Anyway, I will get back with any results.
Sincerely
Svein Terje Gaup
Tracy McKibben wrote:
> Svein Terje Gaup wrote:
> > Hi, I have a SQL Server 2000 with 14 different databases. I have been
> > monitoring it for some time, and it seems there is a high number of
> > page faults occurring. The number is constantly above 3000. The server
> > has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> > The Pages/sec counter is stabilised at a very low level (mostly it is
> > at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> > dedicated SQL Server.
> >
> > So what could be causing the high amount of Page Faults?
> >
> > Sincerely
> > Svein Terje Gaup
> >
> Do you have something copying files off of this machine, perhaps a tape
> backup or something copy files across the network? I see this sort of
> thing when copying files from server to server. The page faults occur
> as the files are read from disk.
High file i/o
SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql
High file i/o
SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)