Making groups visible using Parameters

Hi everyone,

I am new to reporting services and really need some help..
Im trying to simply my requirement using this scenario: Lets say

I want to display Sales for the current year (i.e from the start of the year january to the month we are currently at). The next requirement is to group these sales, by Area , Region , Zone...

Top leve l = Zone , then Region then Area.

I need the user to be able to control this using a parameter (i.e User can select "View Area Level" or "View Region Level" or "View Zone level")

How can this best be achieved ?

My idea was using row groupings and controling visibility using the parameter.. The problem is.. If i want to view ONLY the area level and not see anything above, can I turn visibility off of the Zone and Region and still see the inner group (area)

Area Level (default view)

Jan Feb
Area 1 100 100
Area 2 200 200

Region Level Selected

Reagion 1
Area 1 100 100
Region 2
Area 2 200 200

Zone Level Select

Zone 1
Region 1
Area 1 100 100
Region 2
Area 2 200 200
Any help would REALLY be appreciated ..

Kind Regards,
Neil
http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx|||

Hello Neil,

I can think of a couple ways to do this, one way is having 2 boolean parameters. From the way you show in your example, it looks like the Area level will always show.

First, you'll need to create the 2 parameters as boolean type and set your defaults. For example: ShowRegion - False, ShowZone - False.

Click on the row handle for your Zone level and go to the Hidden setting in the Properties window.

Change the Hidden setting to an expression and enter this into it:

=Iif(Parameters!ShowZone.Value = True, False, True)

Click on the row handle for your Region level and change the Hidden setting to this expression:

=Iif(Parameters!ShowRegion.Value = True OR Parameters!ShowZone.Value = True, False, True)

Hope this helps.

Jarret

|||Hey Jarret

Thanks so much for the reply, I have found away to do this, well In theory the idea works..
With regards to your idea.. I was thinking that If the parent group is made invisible so are the children groups (invisible).. well.. I dont know if that is the case.. If you know the answer please let me know..

Thanks for taking the time to help out.. really appreciate it

Kind Regards,
Neil
|||

Hello,

If you hide a higher group (parent), the lower groups (children) will continue to show until explicitly hidden. They are independent of each other.

Jarret

|||Great !!

Thanks!!

Making groups available

I'd like to get the sum of a row in a reporting services table. Unlike
summing columns which are easy, I'm told I need to create a group.
I create the group with the fields I want to sum. Then I right click on the
table cell I want to hold the sum value, and select expression. My table
column names are there, but not my group (which I need to sum my row).
When I create the group, I give it a name and add the fields I want to sum.
What else do I have to do to make the group available when I edit expressions?
Of course, if there's some aggregate functions that let's me sum a row
without a group, all the better.
Thanks
--
RandyHi Randy,
Welcome to the MSDN newsgroup.
Regarding on the "sum of a row in a reporting services table", do you mean
perform sum on all the columns in a single data row or still sum a certain
column , but restricted in a certain group?
If you're wantting to perform the sum on all the columns in a single row,
I'm afraid, the group or other filter can not help on this. You may need to
manually write the sum code in the expression of the textbox or certain
table cell. Also, since you'll perform sum on the columns, we need to make
sure all the columns are of number type.
Regards,
Steven Cheng
Microsoft Online Community 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.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Many thanks for the reply.
Yes, I would like to sum all the columns in the row, and they are all type
int.
I have found the code window, and written a VB function to sum all the
columns. When I say Code.MySum(...) in the Edit Expression dialog box, MySum
is underlined in red and "not defined".
I've tried to just sum the columns in the edit expression dialog box. The
sum function provided only takes one parameter. I'd need an example or BOM
reference to see how to do this. At one point, someone suggested the Subtotal
function, but I couldn't find that in the available aggregate functions list.
If you could point me toward an article on writing functions for reporting
services under the Code tab or in the Edit Expression dialog box, that would
help a lot.
Thanks again,
--
Randy
"Steven Cheng[MSFT]" wrote:
> Hi Randy,
> Welcome to the MSDN newsgroup.
> Regarding on the "sum of a row in a reporting services table", do you mean
> perform sum on all the columns in a single data row or still sum a certain
> column , but restricted in a certain group?
> If you're wantting to perform the sum on all the columns in a single row,
> I'm afraid, the group or other filter can not help on this. You may need to
> manually write the sum code in the expression of the textbox or certain
> table cell. Also, since you'll perform sum on the columns, we need to make
> sure all the columns are of number type.
> Regards,
> Steven Cheng
> Microsoft Online Community 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.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||Thanks for your followup Randy,
IMO, if you just want to do sum on all the columns in a single row, you can
just put the sum code in the expression of the textbox( use + operator).
For example:
=Fields("col1").Value + Fields("col2").Value + Fields("col3").Value
For custom code in SSRS, you can have a look at the following web reference:
#Using Custom Code References in Expressions [Reporting Services]
http://msdn2.microsoft.com/en-US/library/ms155798(SQL.90).aspx
#Chapter 5: Using Custom .Net Code with Reports
http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community 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.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Make the series stand by themselves in stacked column graph

I'm making some reports that are made for getting printed. I currently have three groups of reports, each group having four charts (four A4 papers/group). To cut down on the sheer number of papers on the boards I've been asked to merge these charts so that we'll have all three groups in one chart. This works fine for three of the charts (two line charts and one column chart), where the results gets neatly organized in group order. Where I ran into problems was the last chart..

The last chart is a stacked column chart. With one group, it works quite well; the x-axis is by year/week, and for each week there's a bar where the lower part is "done" and the upper part is "failed".
Now when doing this graph with multiple groups (groups as series), it doesn't put the different groups beside each other as I would've wished, instead it piles them on top of each other as if it was a 100% stacked column.

So the question is; is there some way to make a stacked column graph with multiple series that are one stacked bar per group instead of having all of them stacked on top of each other? Am I just missing some small detail here?What you are observing is the nature of stacked charts--the values for each category are stacked on top of each other. Unfortunately, what you are looking for, having multiple stacked columns for the same category, is not possible.|||Can you do a column graph and have some bars as a single bar and other bars be stacked?

Make a Group Section Visible when there is no data for this group

I have a report that groups on departments. The requestor would like all
sections to show whether they have information in them or not - also all
sections need to show if there are is no information, totals, etc.
Thank you,
NormaOn Nov 20, 1:48 pm, NormaD <Nor...@.discussions.microsoft.com> wrote:
> I have a report that groups on departments. The requestor would like all
> sections to show whether they have information in them or not - also all
> sections need to show if there are is no information, totals, etc.
> Thank you,
> Norma
Two ways of doing it from what I see.
1) Do a left join on the Department information with your fact data,
so that a department name is included even if there is no data for it.
Data:
SELECT D.NAME AS DEPARTMENT_NAME, F.AMOUNT
FROM DEPARTMENTS D LEFT JOIN FACT_TABLE F
ON D.DEPARTMENT_KEY = F.DEPARTMENT_KEY
Layout: Drop a Table object, add a Group that groups on the
department name. Put your department header information on that group
row, and all of the facts in the detail row.
The table will still render, but your row counts will be different
because you will be adding a row for each department that has no fact
data.
2) Implement a Subreport that passes the Department info
Data: I would have an outer Report that does a SELECT on all of your
departments by name/id.
Layout: Drop a list box on the report that groups by the Department
name/id. Then in the list, put a subreport, and pass the Department
as a Parameter to the subreport. Put your department header
information here.
Data: In the Subreport, use the @.Department in your WHERE clause to
filter to a single department.
Layout: Drop your fact headers & totals here
Now your row counts in the Subreport will properly show zero rows when
there are no facts for a department.
-- Scott

Maintenance, indexes, fragmentation & file groups

I am trying to get my head around many of the issues related to
maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the
database has 15,783,725 rows with a size of 9.8 GB and an index size of
2.8 GB according to the taskpad view. It has one clustered index and
13 indexes. The clustered index has a prefix of PK_*** Four of the
indexes have a prefix of IX_*** and the remaining nine indexes have a
prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan
looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we
are renting SAN space, so we need to be as efficient as possible with
disk space. We plan to shrink the db before the tlog backup so that we
can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can
reduce fragmentation and thus improve performance. Given the size of
our indexes, this seems to make sense to me. Should I move all
indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this
database, but they are both in the same file group. Should I leave
that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main
points for performing regular maintenance. I have already collected,
created and modified a set of stored procs to do most of these tasks,
so it will just be a matter of scripting a few jobs.
Thank you,
KevinSee in-Line:
--
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140623640.392540.197050@.g43g2000cwa.googlegroups.com...
>I am trying to get my head around many of the issues related to
> maintaining some of our larger databases.
> Our largest database is roughly 10 GB. Our largest table in the
> database has 15,783,725 rows with a size of 9.8 GB and an index size of
> 2.8 GB according to the taskpad view. It has one clustered index and
> 13 indexes. The clustered index has a prefix of PK_*** Four of the
> indexes have a prefix of IX_*** and the remaining nine indexes have a
> prefix of _WA_Sys_***.
_WA... means that these are statistics and not actual indexes. You can
ignore them for all practical purposes.
> I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
> First question, my current rough idea for our new maintenance plan
> looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backupdb
> 3) shrinkfile db
> 4) backup tlog
> 5) shrinkfile tlog
> 6) file system defrag
> 7) reindex (not an indexdefrag)
No need for shrink as I will explain below. You should not have to defrag
the file system if you don't continuously grow and shrink.
> I keep reading that shrinking the database files is wasted I/O but, we
> are renting SAN space, so we need to be as efficient as possible with
> disk space. We plan to shrink the db before the tlog backup so that we
> can reclaim the tlog space created during shrinking the db.
If the file grows again after it is shrunk that is a PURE indication that it
needs the space at one time or another. This is especially true when you
reindex. You must have free space in the files in order for the db to
operate properly. Shrinking just prolongs the inevitable and will hurt
overall performance. Find the size in which it no longer grows and leave it
there because that is what it requires. There is no getting around this so
accept it and move on.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> I have read that putting your indexes into separate filegroups can
> reduce fragmentation and thus improve performance. Given the size of
> our indexes, this seems to make sense to me. Should I move all
> indexes, clustered and nonclustered to the separate filegroup?
It's probably not worth the trouble. Make sure you have a proper fill
factor to reduce fragmentation and page splits.
> Also, someone previous to me had created a second db file for this
> database, but they are both in the same file group. Should I leave
> that as is, or do I need to do something about the second file?
Having more than one file is often a good thing just make sure they are both
the same size when you create them.
> Is there anything I am missing? I think I have caught all the main
> points for performing regular maintenance. I have already collected,
> created and modified a set of stored procs to do most of these tasks,
> so it will just be a matter of scripting a few jobs.
There is usually no need to rebuild indexes every night. Look in
BooksOnLine under DBCC SHOWCONTIG for a script to reindex only the indexes
that actually are fragmented beyond a certain amount.
> Thank you,
> Kevin
>|||OK, I have been sold on the no shrinking. We were originally concerned
because both our db and tlog have been as much as 50% larger than what
is currently in our db and tlog. For example, our two files are broken
down like this:
Size In Use Free
--
2.7GB 1.5GB 1.2GB
10.4GB 5.5GB 4.9GB
If I am understanding reindexing properly and we have a 2.9 GB index on
one table, then it will use much of that free space during reindexing.
Our updated maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backup db
3) backup tlog
4) file system defrag
5) reindex (not an indexdefrag, using a stored proc >20% or so)
One note about our database usage. Our database has relatively few
writes and new rows being added on a daily basis. This database is
primarily used for many SELECT's and our primary table is usually being
joined to other tables in these SELECT's.
I don't know if it is possible to physically order out data so that
common rows are contiguous within the files. Our data is generally
time dependant and tied to different organizations. So a SELECT
statement will generally read all rows that are within the last four
months and belong to organization x.
So I think the goal of fragmentation in our case is to keep the index
contiguous to make reads of the index faster and more efficient. I
think I am understanding this correctly.
As noted above, our two files are quite a bit different in size. Do I
need to do something to correct this?
I have to read up more on fill factors. I don't understand them well
enough yet to understand their impact on fragmentation. Any tips while
I research this more thouroughly?
Thank you,
Kevin|||If you use DBCC DBREINDEX and just specify the table name it will rebuild
ALL indexes on that table in one big transaction. You will need at least 1.2
times the total size of all the indexes including the clustered index. I
assume you have a clustered index and if not you should. The differences in
the size of the file is due to the fact the second file was not made the
same as the first. SQL Server splits the data evenly across multiple files
in the same file group if they are of the same size and have the same amount
of free space. In reality it is the amount of free space in each file that
determines how much data is placed in each file. If you have twice as much
free space in one file then the other it will put twice as much data in the
one that has more free space and so on. The goal is to create all the files
the same size to begin with and then data gets evenly distributed across
them from the beginning. In your case if you make them both say 7GB and run
DBCC DBREINDEX a few times they should even out. Then you can see how much
free space is left and shrink slightly if needed. Back to the clustered
index. In general every table should have one. They are the only things
that help to control fragmentation in the table itself. But choosing the
right column(s) for the clustered index is not always so straight forward.
They are great for times when you always search in ranges or on values that
have a lot of repeating rows. In your case the datetime column or the
organization may be good choices. These should help.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140637582.856303.277700@.g14g2000cwa.googlegroups.com...
> OK, I have been sold on the no shrinking. We were originally concerned
> because both our db and tlog have been as much as 50% larger than what
> is currently in our db and tlog. For example, our two files are broken
> down like this:
> Size In Use Free
> --
> 2.7GB 1.5GB 1.2GB
> 10.4GB 5.5GB 4.9GB
> If I am understanding reindexing properly and we have a 2.9 GB index on
> one table, then it will use much of that free space during reindexing.
> Our updated maintenance plan looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backup db
> 3) backup tlog
> 4) file system defrag
> 5) reindex (not an indexdefrag, using a stored proc >20% or so)
> One note about our database usage. Our database has relatively few
> writes and new rows being added on a daily basis. This database is
> primarily used for many SELECT's and our primary table is usually being
> joined to other tables in these SELECT's.
> I don't know if it is possible to physically order out data so that
> common rows are contiguous within the files. Our data is generally
> time dependant and tied to different organizations. So a SELECT
> statement will generally read all rows that are within the last four
> months and belong to organization x.
> So I think the goal of fragmentation in our case is to keep the index
> contiguous to make reads of the index faster and more efficient. I
> think I am understanding this correctly.
> As noted above, our two files are quite a bit different in size. Do I
> need to do something to correct this?
> I have to read up more on fill factors. I don't understand them well
> enough yet to understand their impact on fragmentation. Any tips while
> I research this more thouroughly?
> Thank you,
> Kevin
>|||kghammond a écrit :
[...]
> 6) file system defrag
Never do that !
In fact impossible on data and log files, while the database is hot.
But the OS defrag does not did a optimized defrag, just a logical one.
If you created your db with large fixed files, the SQL Server OS try to
find the tracks on the differents plates of the disk wich must reduce
the moving of the head on the disk surface. So the cluster wont be
logically continuous.
Il you do a OS defrag you will broke this optimization.
A +
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I don't agree with that at all. First off you can in fact do on-line defrag
at the OS level with tools like DiskKeeper. While I never recommend that
when it can be avoided it is in fact possible. As far as where sql server
looks for the data on disk that too is not true. SQL Server has absolutely
no control over the actual fetching of the data in terms of moving the heads
etc. It simply makes a call to the OS and requests a certain piece of data
but it is the responsibility of the I/O driver and the storage foundation to
do the work as it sees fit. SQL Server knows of coarse that a particular
page lives in a particular file in a particular slot. But that is as far as
it goes.
--
Andrew J. Kelly SQL MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:uf%23mgMFOGHA.1360@.TK2MSFTNGP10.phx.gbl...
> kghammond a écrit :
> [...]
>> 6) file system defrag
> Never do that !
> In fact impossible on data and log files, while the database is hot.
> But the OS defrag does not did a optimized defrag, just a logical one.
> If you created your db with large fixed files, the SQL Server OS try to
> find the tracks on the differents plates of the disk wich must reduce the
> moving of the head on the disk surface. So the cluster wont be logically
> continuous.
> Il you do a OS defrag you will broke this optimization.
> A +
> A +
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

Maintenance, indexes, fragmentation & file groups

I am trying to get my head around many of the issues related to
maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the
database has 15,783,725 rows with a size of 9.8 GB and an index size of
2.8 GB according to the taskpad view. It has one clustered index and
13 indexes. The clustered index has a prefix of PK_*** Four of the
indexes have a prefix of IX_*** and the remaining nine indexes have a
prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan
looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we
are renting SAN space, so we need to be as efficient as possible with
disk space. We plan to shrink the db before the tlog backup so that we
can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can
reduce fragmentation and thus improve performance. Given the size of
our indexes, this seems to make sense to me. Should I move all
indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this
database, but they are both in the same file group. Should I leave
that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main
points for performing regular maintenance. I have already collected,
created and modified a set of stored procs to do most of these tasks,
so it will just be a matter of scripting a few jobs.
Thank you,
KevinSee in-Line:
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140623640.392540.197050@.g43g2000cwa.googlegroups.com...
>I am trying to get my head around many of the issues related to
> maintaining some of our larger databases.
> Our largest database is roughly 10 GB. Our largest table in the
> database has 15,783,725 rows with a size of 9.8 GB and an index size of
> 2.8 GB according to the taskpad view. It has one clustered index and
> 13 indexes. The clustered index has a prefix of PK_*** Four of the
> indexes have a prefix of IX_*** and the remaining nine indexes have a
> prefix of _WA_Sys_***.
_WA... means that these are statistics and not actual indexes. You can
ignore them for all practical purposes.

> I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
> First question, my current rough idea for our new maintenance plan
> looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backupdb
> 3) shrinkfile db
> 4) backup tlog
> 5) shrinkfile tlog
> 6) file system defrag
> 7) reindex (not an indexdefrag)
No need for shrink as I will explain below. You should not have to defrag
the file system if you don't continuously grow and shrink.

> I keep reading that shrinking the database files is wasted I/O but, we
> are renting SAN space, so we need to be as efficient as possible with
> disk space. We plan to shrink the db before the tlog backup so that we
> can reclaim the tlog space created during shrinking the db.
If the file grows again after it is shrunk that is a PURE indication that it
needs the space at one time or another. This is especially true when you
reindex. You must have free space in the files in order for the db to
operate properly. Shrinking just prolongs the inevitable and will hurt
overall performance. Find the size in which it no longer grows and leave it
there because that is what it requires. There is no getting around this so
accept it and move on.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> I have read that putting your indexes into separate filegroups can
> reduce fragmentation and thus improve performance. Given the size of
> our indexes, this seems to make sense to me. Should I move all
> indexes, clustered and nonclustered to the separate filegroup?
It's probably not worth the trouble. Make sure you have a proper fill
factor to reduce fragmentation and page splits.

> Also, someone previous to me had created a second db file for this
> database, but they are both in the same file group. Should I leave
> that as is, or do I need to do something about the second file?
Having more than one file is often a good thing just make sure they are both
the same size when you create them.

> Is there anything I am missing? I think I have caught all the main
> points for performing regular maintenance. I have already collected,
> created and modified a set of stored procs to do most of these tasks,
> so it will just be a matter of scripting a few jobs.
There is usually no need to rebuild indexes every night. Look in
BooksOnLine under DBCC SHOWCONTIG for a script to reindex only the indexes
that actually are fragmented beyond a certain amount.

> Thank you,
> Kevin
>|||OK, I have been sold on the no shrinking. We were originally concerned
because both our db and tlog have been as much as 50% larger than what
is currently in our db and tlog. For example, our two files are broken
down like this:
Size In Use Free
--
2.7GB 1.5GB 1.2GB
10.4GB 5.5GB 4.9GB
If I am understanding reindexing properly and we have a 2.9 GB index on
one table, then it will use much of that free space during reindexing.
Our updated maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backup db
3) backup tlog
4) file system defrag
5) reindex (not an indexdefrag, using a stored proc >20% or so)
One note about our database usage. Our database has relatively few
writes and new rows being added on a daily basis. This database is
primarily used for many SELECT's and our primary table is usually being
joined to other tables in these SELECT's.
I don't know if it is possible to physically order out data so that
common rows are contiguous within the files. Our data is generally
time dependant and tied to different organizations. So a SELECT
statement will generally read all rows that are within the last four
months and belong to organization x.
So I think the goal of fragmentation in our case is to keep the index
contiguous to make reads of the index faster and more efficient. I
think I am understanding this correctly.
As noted above, our two files are quite a bit different in size. Do I
need to do something to correct this?
I have to read up more on fill factors. I don't understand them well
enough yet to understand their impact on fragmentation. Any tips while
I research this more thouroughly?
Thank you,
Kevin|||If you use DBCC DBREINDEX and just specify the table name it will rebuild
ALL indexes on that table in one big transaction. You will need at least 1.2
times the total size of all the indexes including the clustered index. I
assume you have a clustered index and if not you should. The differences in
the size of the file is due to the fact the second file was not made the
same as the first. SQL Server splits the data evenly across multiple files
in the same file group if they are of the same size and have the same amount
of free space. In reality it is the amount of free space in each file that
determines how much data is placed in each file. If you have twice as much
free space in one file then the other it will put twice as much data in the
one that has more free space and so on. The goal is to create all the files
the same size to begin with and then data gets evenly distributed across
them from the beginning. In your case if you make them both say 7GB and run
DBCC DBREINDEX a few times they should even out. Then you can see how much
free space is left and shrink slightly if needed. Back to the clustered
index. In general every table should have one. They are the only things
that help to control fragmentation in the table itself. But choosing the
right column(s) for the clustered index is not always so straight forward.
They are great for times when you always search in ranges or on values that
have a lot of repeating rows. In your case the datetime column or the
organization may be good choices. These should help.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140637582.856303.277700@.g14g2000cwa.googlegroups.com...
> OK, I have been sold on the no shrinking. We were originally concerned
> because both our db and tlog have been as much as 50% larger than what
> is currently in our db and tlog. For example, our two files are broken
> down like this:
> Size In Use Free
> --
> 2.7GB 1.5GB 1.2GB
> 10.4GB 5.5GB 4.9GB
> If I am understanding reindexing properly and we have a 2.9 GB index on
> one table, then it will use much of that free space during reindexing.
> Our updated maintenance plan looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backup db
> 3) backup tlog
> 4) file system defrag
> 5) reindex (not an indexdefrag, using a stored proc >20% or so)
> One note about our database usage. Our database has relatively few
> writes and new rows being added on a daily basis. This database is
> primarily used for many SELECT's and our primary table is usually being
> joined to other tables in these SELECT's.
> I don't know if it is possible to physically order out data so that
> common rows are contiguous within the files. Our data is generally
> time dependant and tied to different organizations. So a SELECT
> statement will generally read all rows that are within the last four
> months and belong to organization x.
> So I think the goal of fragmentation in our case is to keep the index
> contiguous to make reads of the index faster and more efficient. I
> think I am understanding this correctly.
> As noted above, our two files are quite a bit different in size. Do I
> need to do something to correct this?
> I have to read up more on fill factors. I don't understand them well
> enough yet to understand their impact on fragmentation. Any tips while
> I research this more thouroughly?
> Thank you,
> Kevin
>|||kghammond a crit :
[...]
> 6) file system defrag
Never do that !
In fact impossible on data and log files, while the database is hot.
But the OS defrag does not did a optimized defrag, just a logical one.
If you created your db with large fixed files, the SQL Server OS try to
find the tracks on the differents plates of the disk wich must reduce
the moving of the head on the disk surface. So the cluster wont be
logically continuous.
Il you do a OS defrag you will broke this optimization.
A +
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I don't agree with that at all. First off you can in fact do on-line defrag
at the OS level with tools like DiskKeeper. While I never recommend that
when it can be avoided it is in fact possible. As far as where sql server
looks for the data on disk that too is not true. SQL Server has absolutely
no control over the actual fetching of the data in terms of moving the heads
etc. It simply makes a call to the OS and requests a certain piece of data
but it is the responsibility of the I/O driver and the storage foundation to
do the work as it sees fit. SQL Server knows of coarse that a particular
page lives in a particular file in a particular slot. But that is as far as
it goes.
Andrew J. Kelly SQL MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:uf%23mgMFOGHA.1360@.TK2MSFTNGP10.phx.gbl...
> kghammond a crit :
> [...]
> Never do that !
> In fact impossible on data and log files, while the database is hot.
> But the OS defrag does not did a optimized defrag, just a logical one.
> If you created your db with large fixed files, the SQL Server OS try to
> find the tracks on the differents plates of the disk wich must reduce the
> moving of the head on the disk surface. So the cluster wont be logically
> continuous.
> Il you do a OS defrag you will broke this optimization.
> A +
> A +
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

Maintenance, indexes, fragmentation & file groups

I am trying to get my head around many of the issues related to
maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the
database has 15,783,725 rows with a size of 9.8 GB and an index size of
2.8 GB according to the taskpad view. It has one clustered index and
13 indexes. The clustered index has a prefix of PK_*** Four of the
indexes have a prefix of IX_*** and the remaining nine indexes have a
prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan
looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we
are renting SAN space, so we need to be as efficient as possible with
disk space. We plan to shrink the db before the tlog backup so that we
can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can
reduce fragmentation and thus improve performance. Given the size of
our indexes, this seems to make sense to me. Should I move all
indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this
database, but they are both in the same file group. Should I leave
that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main
points for performing regular maintenance. I have already collected,
created and modified a set of stored procs to do most of these tasks,
so it will just be a matter of scripting a few jobs.
Thank you,
Kevin
See in-Line:
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140623640.392540.197050@.g43g2000cwa.googlegr oups.com...
>I am trying to get my head around many of the issues related to
> maintaining some of our larger databases.
> Our largest database is roughly 10 GB. Our largest table in the
> database has 15,783,725 rows with a size of 9.8 GB and an index size of
> 2.8 GB according to the taskpad view. It has one clustered index and
> 13 indexes. The clustered index has a prefix of PK_*** Four of the
> indexes have a prefix of IX_*** and the remaining nine indexes have a
> prefix of _WA_Sys_***.
_WA... means that these are statistics and not actual indexes. You can
ignore them for all practical purposes.

> I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
> First question, my current rough idea for our new maintenance plan
> looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backupdb
> 3) shrinkfile db
> 4) backup tlog
> 5) shrinkfile tlog
> 6) file system defrag
> 7) reindex (not an indexdefrag)
No need for shrink as I will explain below. You should not have to defrag
the file system if you don't continuously grow and shrink.

> I keep reading that shrinking the database files is wasted I/O but, we
> are renting SAN space, so we need to be as efficient as possible with
> disk space. We plan to shrink the db before the tlog backup so that we
> can reclaim the tlog space created during shrinking the db.
If the file grows again after it is shrunk that is a PURE indication that it
needs the space at one time or another. This is especially true when you
reindex. You must have free space in the files in order for the db to
operate properly. Shrinking just prolongs the inevitable and will hurt
overall performance. Find the size in which it no longer grows and leave it
there because that is what it requires. There is no getting around this so
accept it and move on.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> I have read that putting your indexes into separate filegroups can
> reduce fragmentation and thus improve performance. Given the size of
> our indexes, this seems to make sense to me. Should I move all
> indexes, clustered and nonclustered to the separate filegroup?
It's probably not worth the trouble. Make sure you have a proper fill
factor to reduce fragmentation and page splits.

> Also, someone previous to me had created a second db file for this
> database, but they are both in the same file group. Should I leave
> that as is, or do I need to do something about the second file?
Having more than one file is often a good thing just make sure they are both
the same size when you create them.

> Is there anything I am missing? I think I have caught all the main
> points for performing regular maintenance. I have already collected,
> created and modified a set of stored procs to do most of these tasks,
> so it will just be a matter of scripting a few jobs.
There is usually no need to rebuild indexes every night. Look in
BooksOnLine under DBCC SHOWCONTIG for a script to reindex only the indexes
that actually are fragmented beyond a certain amount.

> Thank you,
> Kevin
>
|||OK, I have been sold on the no shrinking. We were originally concerned
because both our db and tlog have been as much as 50% larger than what
is currently in our db and tlog. For example, our two files are broken
down like this:
Size In Use Free
2.7GB 1.5GB 1.2GB
10.4GB 5.5GB 4.9GB
If I am understanding reindexing properly and we have a 2.9 GB index on
one table, then it will use much of that free space during reindexing.
Our updated maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backup db
3) backup tlog
4) file system defrag
5) reindex (not an indexdefrag, using a stored proc >20% or so)
One note about our database usage. Our database has relatively few
writes and new rows being added on a daily basis. This database is
primarily used for many SELECT's and our primary table is usually being
joined to other tables in these SELECT's.
I don't know if it is possible to physically order out data so that
common rows are contiguous within the files. Our data is generally
time dependant and tied to different organizations. So a SELECT
statement will generally read all rows that are within the last four
months and belong to organization x.
So I think the goal of fragmentation in our case is to keep the index
contiguous to make reads of the index faster and more efficient. I
think I am understanding this correctly.
As noted above, our two files are quite a bit different in size. Do I
need to do something to correct this?
I have to read up more on fill factors. I don't understand them well
enough yet to understand their impact on fragmentation. Any tips while
I research this more thouroughly?
Thank you,
Kevin
|||If you use DBCC DBREINDEX and just specify the table name it will rebuild
ALL indexes on that table in one big transaction. You will need at least 1.2
times the total size of all the indexes including the clustered index. I
assume you have a clustered index and if not you should. The differences in
the size of the file is due to the fact the second file was not made the
same as the first. SQL Server splits the data evenly across multiple files
in the same file group if they are of the same size and have the same amount
of free space. In reality it is the amount of free space in each file that
determines how much data is placed in each file. If you have twice as much
free space in one file then the other it will put twice as much data in the
one that has more free space and so on. The goal is to create all the files
the same size to begin with and then data gets evenly distributed across
them from the beginning. In your case if you make them both say 7GB and run
DBCC DBREINDEX a few times they should even out. Then you can see how much
free space is left and shrink slightly if needed. Back to the clustered
index. In general every table should have one. They are the only things
that help to control fragmentation in the table itself. But choosing the
right column(s) for the clustered index is not always so straight forward.
They are great for times when you always search in ranges or on values that
have a lot of repeating rows. In your case the datetime column or the
organization may be good choices. These should help.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.co...showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/colu...illfactors.asp
Fill Factors
http://www.sql-server-performance.co...ed_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140637582.856303.277700@.g14g2000cwa.googlegr oups.com...
> OK, I have been sold on the no shrinking. We were originally concerned
> because both our db and tlog have been as much as 50% larger than what
> is currently in our db and tlog. For example, our two files are broken
> down like this:
> Size In Use Free
> --
> 2.7GB 1.5GB 1.2GB
> 10.4GB 5.5GB 4.9GB
> If I am understanding reindexing properly and we have a 2.9 GB index on
> one table, then it will use much of that free space during reindexing.
> Our updated maintenance plan looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backup db
> 3) backup tlog
> 4) file system defrag
> 5) reindex (not an indexdefrag, using a stored proc >20% or so)
> One note about our database usage. Our database has relatively few
> writes and new rows being added on a daily basis. This database is
> primarily used for many SELECT's and our primary table is usually being
> joined to other tables in these SELECT's.
> I don't know if it is possible to physically order out data so that
> common rows are contiguous within the files. Our data is generally
> time dependant and tied to different organizations. So a SELECT
> statement will generally read all rows that are within the last four
> months and belong to organization x.
> So I think the goal of fragmentation in our case is to keep the index
> contiguous to make reads of the index faster and more efficient. I
> think I am understanding this correctly.
> As noted above, our two files are quite a bit different in size. Do I
> need to do something to correct this?
> I have to read up more on fill factors. I don't understand them well
> enough yet to understand their impact on fragmentation. Any tips while
> I research this more thouroughly?
> Thank you,
> Kevin
>
|||kghammond a crit :
[...]
> 6) file system defrag
Never do that !
In fact impossible on data and log files, while the database is hot.
But the OS defrag does not did a optimized defrag, just a logical one.
If you created your db with large fixed files, the SQL Server OS try to
find the tracks on the differents plates of the disk wich must reduce
the moving of the head on the disk surface. So the cluster wont be
logically continuous.
Il you do a OS defrag you will broke this optimization.
A +
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||I don't agree with that at all. First off you can in fact do on-line defrag
at the OS level with tools like DiskKeeper. While I never recommend that
when it can be avoided it is in fact possible. As far as where sql server
looks for the data on disk that too is not true. SQL Server has absolutely
no control over the actual fetching of the data in terms of moving the heads
etc. It simply makes a call to the OS and requests a certain piece of data
but it is the responsibility of the I/O driver and the storage foundation to
do the work as it sees fit. SQL Server knows of coarse that a particular
page lives in a particular file in a particular slot. But that is as far as
it goes.
Andrew J. Kelly SQL MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:uf%23mgMFOGHA.1360@.TK2MSFTNGP10.phx.gbl...
> kghammond a crit :
> [...]
> Never do that !
> In fact impossible on data and log files, while the database is hot.
> But the OS defrag does not did a optimized defrag, just a logical one.
> If you created your db with large fixed files, the SQL Server OS try to
> find the tracks on the differents plates of the disk wich must reduce the
> moving of the head on the disk surface. So the cluster wont be logically
> continuous.
> Il you do a OS defrag you will broke this optimization.
> A +
> A +
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************