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

High Availability across different sites

Posting to groups again..
Can someone give me a list of outside vendors that help support High
Availability of SQL across geographically dispersed locations. I know
Veritas, NSI, EMC have some solutions that eliminate the shared disk storage
limitation of clustering.. Thanks.Using SQL 2000
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
>I think Hitachi does as well.
--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> > Can someone give me a list of outside vendors that help support High
> > Availability of SQL across geographically dispersed locations. I know
> > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> storage
> > limitation of clustering.. Thanks.Using SQL 2000
> >
> >
>|||if you go with hardware (EMC or Hitachi), you are usually going with
"synchronous" which will raise the ongoing cost of the solution in bandwidth
and limit the maximum distance apart that the nodes can be.
if you go with NSI, you can go any distance since its asynchronous and as a
software-only soluton, you wont have to buy to expensive Symetrix boxes and
SRDF.
jason
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> > Can someone give me a list of outside vendors that help support High
> > Availability of SQL across geographically dispersed locations. I know
> > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> storage
> > limitation of clustering.. Thanks.Using SQL 2000
> >
> >
>|||...though you would risk lose Microsoft support for your cluster by doing
so.
Regards,
John
"Jason Buffington" <jasonbuffington@.hotmail.com> wrote in message
news:%23$I4sjGdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> if you go with hardware (EMC or Hitachi), you are usually going with
> "synchronous" which will raise the ongoing cost of the solution in
bandwidth
> and limit the maximum distance apart that the nodes can be.
> if you go with NSI, you can go any distance since its asynchronous and as
a
> software-only soluton, you wont have to buy to expensive Symetrix boxes
and
> SRDF.
> jason
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > Posting to groups again..
> >
> > Can someone give me a list of outside vendors that help support High
> > Availability of SQL across geographically dispersed locations. I know
> > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> storage
> > limitation of clustering.. Thanks.Using SQL 2000
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> > > Can someone give me a list of outside vendors that help support High
> > > Availability of SQL across geographically dispersed locations. I know
> > > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> > storage
> > > limitation of clustering.. Thanks.Using SQL 2000
> > >
> > >
> >
> >
>

Hiding parent column groups in matrix control

I use matrix control in my report. Thre are 4 column groups in that control : Year, Half year, Quarter, and Month. I can expand the groups from Year to Months using drilldown. But I would like to see the only group at a time: Half year or Months or Quarter or Year. I tried to solve this task in following way:

- Created parameter named DateMode containing four labels :

Year,half year, quarter, month, and ennumerated them 1,2,3,4 . They appeared in combo box in report.

-Set visibility expression for each group like: Parameters!DateMode.Value<>1 ( 2,3,4 ).

- Uncheck "Visibility can be toggled by another report item"

So in my opinion matrix control should show only one group selected in combo box.

But it is working only for Year. If I select another date mode, there is not column titles shown and data always are shown for Year.

If anybody already solved similar task, please share.

Thanks in advance.

Dima,

the visibility is triggered via true or false - therefor you will have to build up an expression like:
=IIF(Parameters!DateMode.Value = 1,true,false)
for the year group and
=IIF(Parameters!DateMode.Value = 4,true,false) for the month group

you could also have a look here http://www.msbicentral.com/ for some more suggestions.

hth....

cheers,
Markus

Hiding document map.

First off let me say that I am not sure that I want to hide the document map.
I have a situation where I have a report that displays data in groups of
'contractors'. I have a document map that lists these 'contractors'. When a
'contractor' is selected from the document map it will move to the page that
the 'contractor' can be found on but it does not set focus to that
'contractor' group.
Is it possible to set focus to that group?
If it is not possible to set focus, then how can I hide the document map? I
am using the Report Manager to display this report.
Thanks in advance.You can "hide" the document map in Report Manager by clicking on the "x"
button in the document map window.
If you don't want the content of the document map being generated at all,
then you should remove the expression from the grouping's "Label" property.
By default reportitems and groupings don't have a Label value specified and
therefore no document map is generated.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Anger" <MichaelAnger@.discussions.microsoft.com> wrote in message
news:DCDB64DB-5504-4ED2-9E2A-A38D1203A58D@.microsoft.com...
> First off let me say that I am not sure that I want to hide the document
map.
> I have a situation where I have a report that displays data in groups of
> 'contractors'. I have a document map that lists these 'contractors'. When
a
> 'contractor' is selected from the document map it will move to the page
that
> the 'contractor' can be found on but it does not set focus to that
> 'contractor' group.
> Is it possible to set focus to that group?
> If it is not possible to set focus, then how can I hide the document map?
I
> am using the Report Manager to display this report.
> Thanks in advance.

Hiding a group also hides all nested groups

I have several nested sub-total groups. Depending on a report parameter, I may want to suppress one of the sub-totals. This one group is not the lowest group in the hierarchy and, when I hide it, all the groups "below" it become hidden too. I only want the one group to "disappear", not any others. Is there a way to accomplish this other than creating two versions of the report?

Instead of giving visibility condition to the group (group row), try using the visibility condition on all textboxes (or any other elements) in that group individually. This way, you are hiding only those individual items instead of the group as a whole.

Shyam