Analysis Service is slowing down the server

Hi,

I am facing the following problem on SQL2K.

When I start the service the server slows down and users start complaining that generation of reports based on system (i.e. databases) that are hosted on the same server have REALLY slowed down... and thus I have to stop the service...

Can some please guide me regarding how to solve this problem and how I can be able to use the Analysis service.

Many thanks in advance for you kind help.Howdy

It would make sense to :

(1) Ensure the tables Analysis Services are querying have good indexes on them - use profiler & Index Tuner wizrd to check

(2) RUn the queries after hours to reduce competion on resources

(3) Check perfmon and see if the box is under powered for what you are trying to do

(4) Maybe find another server and copy the database to it & run analysis services on that - that way no-one will ever complain and it may be quicker for oyu.

Cheers,

SG.

am I backing up free space

sql2k sp3
All ideas are welcomed. In my db;
dbfile1 used 5796.94mb, free 295.56mb
dbfile2 used 4212.52mb, free 75.62mb
log used 75.14mb, free 4401.1mb
My backup is 13.2 gigs. By my calulations it should be 10.
How can it be?
TIA, Chrisi'm pretty sure you are backing up the free space. try restoring one
of the dbs and you can see then how the files are restored. do you
need such a large transaction log?
think you would be better shrinking it and letting it grow again.
advice from - http://www.sql-server-performance.com/misc_tips.asp
According to Microsoft, the way to shrink a log file is to use DBCC
SHRINKFILE. This process is painfully slow and inefficient. Even after
running the procedure numerous times, you may not get much free space.
Here's another way to shrink your logs that is guaranteed to work
every time. First, back up the database and then detach (sp_detach_db)
it (you will have to bring the database down to do this). Next,
delete the log file and then re-attach (sp_attach_db) the database,
not providing the old log file location from within the sp_attach_db
command. This will create a new log file with the old log file name in
the old location with default size i.e. 512 KB.
To make sure there is no problem during the operation, the old log
file can be renamed and kept until the database is reattached
successfully. This provides a backup plan if for some reason SQL
server fails to attach the database without the old log file.
This trick won't work if the database has more than one log file, but
if you need to, you can alter the database so that it only has a
single log file, while will allow you to perform the above steps.
After carrying out the detach and attach database activity, the
database can be again altered to add more log files. [7.0, 2000] Added
2-24-2003 Contributed by Gaurav Bindlish
hope this helps,
rh
"chris" <anonymous@.discussions.microsoft.com> wrote in message news:<21fb01c3e081$13570960$a401280
a@.phx.gbl>...

quote:

---

> sql2k sp3
> All ideas are welcomed. In my db;
> dbfile1 used 5796.94mb, free 295.56mb
> dbfile2 used 4212.52mb, free 75.62mb
> log used 75.14mb, free 4401.1mb
> My backup is 13.2 gigs. By my calulations it should be 10.
> How can it be?
> TIA, Chris

---

am I backing up free space

sql2k sp3
All ideas are welcomed. In my db;
dbfile1 used 5796.94mb, free 295.56mb
dbfile2 used 4212.52mb, free 75.62mb
log used 75.14mb, free 4401.1mb
My backup is 13.2 gigs. By my calulations it should be 10.
How can it be?
TIA, Chrisi'm pretty sure you are backing up the free space. try restoring one
of the dbs and you can see then how the files are restored. do you
need such a large transaction log?
think you would be better shrinking it and letting it grow again.
advice from - http://www.sql-server-performance.com/misc_tips.asp
According to Microsoft, the way to shrink a log file is to use DBCC
SHRINKFILE. This process is painfully slow and inefficient. Even after
running the procedure numerous times, you may not get much free space.
Here's another way to shrink your logs that is guaranteed to work
every time. First, back up the database and then detach (sp_detach_db)
it (you will have to bring the database down to do this). Next,
delete the log file and then re-attach (sp_attach_db) the database,
not providing the old log file location from within the sp_attach_db
command. This will create a new log file with the old log file name in
the old location with default size i.e. 512 KB.
To make sure there is no problem during the operation, the old log
file can be renamed and kept until the database is reattached
successfully. This provides a backup plan if for some reason SQL
server fails to attach the database without the old log file.
This trick won't work if the database has more than one log file, but
if you need to, you can alter the database so that it only has a
single log file, while will allow you to perform the above steps.
After carrying out the detach and attach database activity, the
database can be again altered to add more log files. [7.0, 2000] Added
2-24-2003 Contributed by Gaurav Bindlish
hope this helps,
rh
"chris" <anonymous@.discussions.microsoft.com> wrote in message news:<21fb01c3e081$13570960$a401280a@.phx.gbl>...
> sql2k sp3
> All ideas are welcomed. In my db;
> dbfile1 used 5796.94mb, free 295.56mb
> dbfile2 used 4212.52mb, free 75.62mb
> log used 75.14mb, free 4401.1mb
> My backup is 13.2 gigs. By my calulations it should be 10.
> How can it be?
> TIA, Chris

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass withou
t
having to insert/update each row individually. You could use DTS, BULK INSER
T
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:

> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are the
re
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.

> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.

> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT[vbcol=seagreen]
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
>
Stored[vbcol=seagreen]
there[vbcol=seagreen]|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:

> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
> staging
> without
> INSERT
> Stored
> there
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
>
from[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
Inserts/[vbcol=seagreen]
Are[vbcol=seagreen]|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:

> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> from
> to
> the
> Inserts/
> Are
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.

>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.

>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
the[vbcol=seagreen]
> staging
> without
> INSERT
Inserts/[vbcol=seagreen]
> Stored
> there
>

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass without
having to insert/update each row individually. You could use DTS, BULK INSERT
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:
> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.
> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.
> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
> > SQL2K SP4
> >
> > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > Updates/ Deletes and imports them into a SQL DB every day. I have a
Stored
> > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
there
> > any alternatives to this from within SQL Server?
> >
> > TIA, ChrisR
> >
> >
> >|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
> > I appreciare your ideas, but Im confused.
> >
> > > You could write your own application to parse the file and run each
> > command
> >
> > I really need to do this from within SQL Server.
> >
> > > If you just provided a data file and loaded it into a staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> >
> > How would this be any better than what I have? I still need to get it
from
> > the file into a table. The way Im reading this, I need to go from file
to
> > table, then from table to table?
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > Hi Chris
> > >
> > > You could write your own application to parse the file and run each
> > command,
> > > a different alternative would be to load the data into a table and use
the
> > > EXEC command to run them. As your statements are in a file, there is a
> > > security risk. If you just provided a data file and loaded it into a
> > staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> > >
> > > John
> > >
> > > "ChrisR" wrote:
> > >
> > > > SQL2K SP4
> > > >
> > > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > Stored
> > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
Are
> > there
> > > > any alternatives to this from within SQL Server?
> > > >
> > > > TIA, ChrisR
> > > >
> > > >
> > > >
> >
> >
> >|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:
> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> > Hi Chris
> >
> > How do you create the file that makes up these SQL Statements?
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > I appreciare your ideas, but Im confused.
> > >
> > > > You could write your own application to parse the file and run each
> > > command
> > >
> > > I really need to do this from within SQL Server.
> > >
> > > > If you just provided a data file and loaded it into a staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > >
> > > How would this be any better than what I have? I still need to get it
> from
> > > the file into a table. The way Im reading this, I need to go from file
> to
> > > table, then from table to table?
> > >
> > >
> > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > > Hi Chris
> > > >
> > > > You could write your own application to parse the file and run each
> > > command,
> > > > a different alternative would be to load the data into a table and use
> the
> > > > EXEC command to run them. As your statements are in a file, there is a
> > > > security risk. If you just provided a data file and loaded it into a
> > > staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > > >
> > > > John
> > > >
> > > > "ChrisR" wrote:
> > > >
> > > > > SQL2K SP4
> > > > >
> > > > > Howdy all. I have a daily process that gets a .txt file full of
> Inserts/
> > > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > > Stored
> > > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
> Are
> > > there
> > > > > any alternatives to this from within SQL Server?
> > > > >
> > > > > TIA, ChrisR
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use
the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>