Balancing data between files

We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.

First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.

Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql

bad excel performance / capacity question

a web application that i have recently deployed uses reporting services and exports data to excel reports with drill down options

i have reports that are being generated up to 5 MB and are not usable in excel (i.e. scrolling freezes, slow opening times, large file size)

the reason for this is the detail displayed for each record

each record has a drop down of approximatley 4-6 rows of detail

a report with 3000 lines will have those 4-6 rows of detail for each record

shouldn't excel be able to handle this?

the reports also take a long time to generate and i have had timeout issues, i was able to set the webservice that generates the reports to have no timeout to resolve this problem, my bigger concern is why excel performs so slowly and why the file size is so large

previously we ran these reports out of access, and instead of having the reporting services format, we displayed detail information across as columns and there was no performance issue with the same amount of data

the problems seems to be with out excel handles the (+) signs

Hi,

I also have this problem and it is a big problem according to my customers - I've contacted Microsoft via an incident.

The MS's answer was : the SSRS Excel renderer is the problem and it shall be solved in the next SQL Server version (completely rewritten). Not earlier, and there is no possibility to fix it now.

The only workaround I've found:

1. the main point is to open the exported excel file for the 1.time (you have simply to wait until it opens) and then copy-paste as values the whole sheet. save the file after this, the next time you open it you will see performance improvement.

2. maybe you can split the report into smaller parts - this means less data but more reports

3. you can modify the renderer by setting OMIT_FORMULAS to true in the renderer's config file (there will be no formulas exported, all is exported as values)

The only what really helps is the point 1.

Not only the (+) sign is the problem. I have a report exporting 7000 of rows in 20 columns and there is a problem too.

Hope this helps, if someone has more ideas please let me know, I would also appreciate it.

Jan

|||I would guess that you have a significant number of merged cells in your generated report, due to alignment issues in the RDL. Excel has known issues when loading a spreadsheet with many merge cells. The Excel Export in RS uses merged cells to maintain the layout defined in RDL, and if you have many rows, this can result in large numbers of merged cells.

Check out Chris's blog entry on merging cells and how to reduce them:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx
|||

interesting...thanks for the answer

but there are no merged columns in my problematic excel file...and how is it possible that after copy-paste as values of the whole sheet becomes the opening and working with the excel file non-problematic...?

bad excel performance / capacity question

a web application that i have recently deployed uses reporting services and exports data to excel reports with drill down options

i have reports that are being generated up to 5 MB and are not usable in excel (i.e. scrolling freezes, slow opening times, large file size)

the reason for this is the detail displayed for each record

each record has a drop down of approximatley 4-6 rows of detail

a report with 3000 lines will have those 4-6 rows of detail for each record

shouldn't excel be able to handle this?

the reports also take a long time to generate and i have had timeout issues, i was able to set the webservice that generates the reports to have no timeout to resolve this problem, my bigger concern is why excel performs so slowly and why the file size is so large

previously we ran these reports out of access, and instead of having the reporting services format, we displayed detail information across as columns and there was no performance issue with the same amount of data

the problems seems to be with out excel handles the (+) signs

Hi,

I also have this problem and it is a big problem according to my customers - I've contacted Microsoft via an incident.

The MS's answer was : the SSRS Excel renderer is the problem and it shall be solved in the next SQL Server version (completely rewritten). Not earlier, and there is no possibility to fix it now.

The only workaround I've found:

1. the main point is to open the exported excel file for the 1.time (you have simply to wait until it opens) and then copy-paste as values the whole sheet. save the file after this, the next time you open it you will see performance improvement.

2. maybe you can split the report into smaller parts - this means less data but more reports

3. you can modify the renderer by setting OMIT_FORMULAS to true in the renderer's config file (there will be no formulas exported, all is exported as values)

The only what really helps is the point 1.

Not only the (+) sign is the problem. I have a report exporting 7000 of rows in 20 columns and there is a problem too.

Hope this helps, if someone has more ideas please let me know, I would also appreciate it.

Jan

|||I would guess that you have a significant number of merged cells in your generated report, due to alignment issues in the RDL. Excel has known issues when loading a spreadsheet with many merge cells. The Excel Export in RS uses merged cells to maintain the layout defined in RDL, and if you have many rows, this can result in large numbers of merged cells.

Check out Chris's blog entry on merging cells and how to reduce them:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx|||

interesting...thanks for the answer

but there are no merged columns in my problematic excel file...and how is it possible that after copy-paste as values of the whole sheet becomes the opening and working with the excel file non-problematic...?

Backups

Our current SAN capacity has just hit the wall and I need some advice. The
SAN stores our current production SQL db and the backups on another volume.
The volume that the backups for TL's and the database is on is completely
full. Can backups be sent to any server with capacity on the network or
would it be too much overhead? All the the servers are on a fibre switch
between all of the servers so I am not sure it would it would have that much
impact. Moving the backups would give us breathing room for a while until
the SAN is upgraded.Yeah you can - whether it's tolerable, doable or not is
going to depend on the size of the backups and the activity
on the network resources.
-Sue
On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
<Rockn@.newsgroups.nospam> wrote:

>Our current SAN capacity has just hit the wall and I need some advice. The
>SAN stores our current production SQL db and the backups on another volume.
>The volume that the backups for TL's and the database is on is completely
>full. Can backups be sent to any server with capacity on the network or
>would it be too much overhead? All the the servers are on a fibre switch
>between all of the servers so I am not sure it would it would have that muc
h
>impact. Moving the backups would give us breathing room for a while until
>the SAN is upgraded.
>|||I would probably do it at it's scheduled time which is currently 12 am when
no one is on the network.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lgpnd29qne8ajposrnuvtuspbu4gp05t0e@.
4ax.com...
> Yeah you can - whether it's tolerable, doable or not is
> going to depend on the size of the backups and the activity
> on the network resources.
> -Sue
> On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
> <Rockn@.newsgroups.nospam> wrote:
>
The[vbcol=seagreen]
volume.[vbcol=seagreen]
much[vbcol=seagreen]
>

Backups

Our current SAN capacity has just hit the wall and I need some advice. The
SAN stores our current production SQL db and the backups on another volume.
The volume that the backups for TL's and the database is on is completely
full. Can backups be sent to any server with capacity on the network or
would it be too much overhead? All the the servers are on a fibre switch
between all of the servers so I am not sure it would it would have that much
impact. Moving the backups would give us breathing room for a while until
the SAN is upgraded.Yeah you can - whether it's tolerable, doable or not is
going to depend on the size of the backups and the activity
on the network resources.
-Sue
On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
<Rockn@.newsgroups.nospam> wrote:
>Our current SAN capacity has just hit the wall and I need some advice. The
>SAN stores our current production SQL db and the backups on another volume.
>The volume that the backups for TL's and the database is on is completely
>full. Can backups be sent to any server with capacity on the network or
>would it be too much overhead? All the the servers are on a fibre switch
>between all of the servers so I am not sure it would it would have that much
>impact. Moving the backups would give us breathing room for a while until
>the SAN is upgraded.
>|||I would probably do it at it's scheduled time which is currently 12 am when
no one is on the network.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lgpnd29qne8ajposrnuvtuspbu4gp05t0e@.4ax.com...
> Yeah you can - whether it's tolerable, doable or not is
> going to depend on the size of the backups and the activity
> on the network resources.
> -Sue
> On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
> <Rockn@.newsgroups.nospam> wrote:
> >Our current SAN capacity has just hit the wall and I need some advice.
The
> >SAN stores our current production SQL db and the backups on another
volume.
> >The volume that the backups for TL's and the database is on is completely
> >full. Can backups be sent to any server with capacity on the network or
> >would it be too much overhead? All the the servers are on a fibre switch
> >between all of the servers so I am not sure it would it would have that
much
> >impact. Moving the backups would give us breathing room for a while until
> >the SAN is upgraded.
> >
>