| Groups | Blog | Home |
sql server reporting services : General questions on reporting services.
All
Our Management Information department are trying to replace our current reporting solution in Crystal to a solution in reporting services. However they are coming to me with various questions which I don`t know the answer to. 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When they report from a 20G database they can load the tempDB upto 10G. To me this sounds like they need to optomise their code and improve the way they manipulate the data. But does RS place a high load on the tempDB ? 2. RS create SQL jobs to control the reports. These are always poorly named with alpha numeric strings. Can these be renamed safely to something more sensible ? 3. Does RS have its own scheduler or are we limited to using what SQL provides ? 4. Would you envisage having the need to multiple temp tables for each report ? Surely sp`s and the like should be able to remove the need to create temp tables in many/most cases. Any help would be great as at the moment RS does not look like the way forward.
[quoted text, click to view]
On May 16, 3:19 am, Simon <S...@discussions.microsoft.com> wrote: > All > > Our Management Information department are trying to replace our current > reporting solution in Crystal to a solution in reporting services. However > they are coming to me with various questions which I don`t know the answer to. > > 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When they > report from a 20G database they can load the tempDB upto 10G. To me this > sounds like they need to optomise their code and improve the way they > manipulate the data. But does RS place a high load on the tempDB ? > > 2. RS create SQL jobs to control the reports. These are always poorly named > with alpha numeric strings. Can these be renamed safely to something more > sensible ? > > 3. Does RS have its own scheduler or are we limited to using what SQL > provides ? > > 4. Would you envisage having the need to multiple temp tables for each > report ? Surely sp`s and the like should be able to remove the need to create > temp tables in many/most cases. > > Any help would be great as at the moment RS does not look like the way > forward. > > Si I can't speak to all the questions; but, I will speak to what I know. 1. Generally speaking, SSRS should not load down the tempDB very heavily (in comparison w/CR). From the questions listed, it seems that a lot of temporary tables are being used somewhere, which would explain the tempDB usage (regardless of which type used). 2. Since I have not tried this myself, I would assume you could rename them; but, then trying to re-manipulate them after the fact through the Report Mgr would most likely error out. I would suggest trying it out once and testing it. 4. The use of temp tables can be eliminated in may cases via cascading select queries and clever stored procedure design. I will say this though, when first creating a report, do not base the initial query on temp tables as it will error out. Instead base it on a known dataset/ query and then switch to temp tables in stored procs if necessary. Hope this helps. Regards, Enrique Martinez Sr. Software Consultant
[quoted text, click to view]
On May 16, 4:19 am, Simon <S...@discussions.microsoft.com> wrote: > All > > Our Management Information department are trying to replace our current > reporting solution in Crystal to a solution in reporting services. However > they are coming to me with various questions which I don`t know the answer to. > > 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When they > report from a 20G database they can load the tempDB upto 10G. To me this > sounds like they need to optomise their code and improve the way they > manipulate the data. But does RS place a high load on the tempDB ? > > 2. RS create SQL jobs to control the reports. These are always poorly named > with alpha numeric strings. Can these be renamed safely to something more > sensible ? > > 3. Does RS have its own scheduler or are we limited to using what SQL > provides ? > > 4. Would you envisage having the need to multiple temp tables for each > report ? Surely sp`s and the like should be able to remove the need to create > temp tables in many/most cases. > > Any help would be great as at the moment RS does not look like the way > forward. > > Si Hi Simon, I've been using RS for 2 years, and previously used Crystal for 5 years. Answers: 1) RS does not, as far as I know, place a heavy load on TempDB. RS does have several "working" tables that it is constantly updating (the "Catalog") to manage jobs, reports, schedules etc. But it sounds to me like the TempDB growth is related to the underlying query that RS is executing. The underlying query and SQL Server can be traced for further analysis of the heavy growth. 2) RS does expose SQL Server Agent jobs with non-sensical names, but really those jobs should be created/updated/executed/deleted by RS behind the scenes, not by a person. Do not rename, as the non- sensical name uniquely identifies that job to RS and is stored in one of the RS "Catalog" tables. This brings us to answer #3..... 3) Reports can be scheduled using Report Manager (or by code). Using Report Manager, select a report and navigate to the 'Subscriptions' tab. You can setup regular and "data-driven" subscriptions. 4) RS executes data queries - those queries can be straight embedded sql or stored proc's or even MDX queries. So temp tables are more a design choice in the sql code. What is a limiting factor of RS/SQL Server is that RS cannot recognize multiple result sets from a SQL stored proc. HTH- Matt
Thanks for your comments. I had a look at some of the repotrs that have been
written. One uses 10 sp`s and 10 temp tables which I can not believe is either a)efficient or b) necessary. I will need to speak to your report writers and suggest reviewing the way they write reports. Si [quoted text, click to view] "EMartinez" wrote:
> On May 16, 3:19 am, Simon <S...@discussions.microsoft.com> wrote: > > All > > > > Our Management Information department are trying to replace our current > > reporting solution in Crystal to a solution in reporting services. However > > they are coming to me with various questions which I don`t know the answer to. > > > > 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When they > > report from a 20G database they can load the tempDB upto 10G. To me this > > sounds like they need to optomise their code and improve the way they > > manipulate the data. But does RS place a high load on the tempDB ? > > > > 2. RS create SQL jobs to control the reports. These are always poorly named > > with alpha numeric strings. Can these be renamed safely to something more > > sensible ? > > > > 3. Does RS have its own scheduler or are we limited to using what SQL > > provides ? > > > > 4. Would you envisage having the need to multiple temp tables for each > > report ? Surely sp`s and the like should be able to remove the need to create > > temp tables in many/most cases. > > > > Any help would be great as at the moment RS does not look like the way > > forward. > > > > Si > > > I can't speak to all the questions; but, I will speak to what I know. > 1. Generally speaking, SSRS should not load down the tempDB very > heavily (in comparison w/CR). From the questions listed, it seems that > a lot of temporary tables are being used somewhere, which would > explain the tempDB usage (regardless of which type used). > 2. Since I have not tried this myself, I would assume you could rename > them; but, then trying to re-manipulate them after the fact through > the Report Mgr would most likely error out. I would suggest trying it > out once and testing it. > 4. The use of temp tables can be eliminated in may cases via cascading > select queries and clever stored procedure design. I will say this > though, when first creating a report, do not base the initial query on > temp tables as it will error out. Instead base it on a known dataset/ > query and then switch to temp tables in stored procs if necessary. > Hope this helps. > > Regards, > > Enrique Martinez > Sr. Software Consultant >
Thanks for your replies as well.
I think a course of how to optomise SQL code and the correct use of temp tables may be the way forward for the report writers. Si [quoted text, click to view] "MattA" wrote:
> On May 16, 4:19 am, Simon <S...@discussions.microsoft.com> wrote: > > All > > > > Our Management Information department are trying to replace our current > > reporting solution in Crystal to a solution in reporting services. However > > they are coming to me with various questions which I don`t know the answer to. > > > > 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When they > > report from a 20G database they can load the tempDB upto 10G. To me this > > sounds like they need to optomise their code and improve the way they > > manipulate the data. But does RS place a high load on the tempDB ? > > > > 2. RS create SQL jobs to control the reports. These are always poorly named > > with alpha numeric strings. Can these be renamed safely to something more > > sensible ? > > > > 3. Does RS have its own scheduler or are we limited to using what SQL > > provides ? > > > > 4. Would you envisage having the need to multiple temp tables for each > > report ? Surely sp`s and the like should be able to remove the need to create > > temp tables in many/most cases. > > > > Any help would be great as at the moment RS does not look like the way > > forward. > > > > Si > > Hi Simon, > > I've been using RS for 2 years, and previously used Crystal for 5 > years. > > Answers: > 1) RS does not, as far as I know, place a heavy load on TempDB. RS > does have several "working" tables that it is constantly updating (the > "Catalog") to manage jobs, reports, schedules etc. But it sounds to > me like the TempDB growth is related to the underlying query that RS > is executing. The underlying query and SQL Server can be traced for > further analysis of the heavy growth. > 2) RS does expose SQL Server Agent jobs with non-sensical names, but > really those jobs should be created/updated/executed/deleted by RS > behind the scenes, not by a person. Do not rename, as the non- > sensical name uniquely identifies that job to RS and is stored in one > of the RS "Catalog" tables. This brings us to answer #3..... > 3) Reports can be scheduled using Report Manager (or by code). Using > Report Manager, select a report and navigate to the 'Subscriptions' > tab. You can setup regular and "data-driven" subscriptions. > 4) RS executes data queries - those queries can be straight embedded > sql or stored proc's or even MDX queries. So temp tables are more a > design choice in the sql code. What is a limiting factor of RS/SQL > Server is that RS cannot recognize multiple result sets from a SQL > stored proc. > > HTH- > > Matt >
Thanks for the reply.
My concern is that the only thing that has changed on the SQL box in the last week is new reports via reporting services. It does worry me that one report has 10 temp tables and having analysed them whilst the report was running they do grow to an insane size compared to the db that they are reporting on. Si [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> See in line: > > "Simon" <Simon@discussions.microsoft.com> wrote in message > news:C5533AB9-751B-48F3-8444-65E697485652@microsoft.com... > > All > > > > Our Management Information department are trying to replace our current > > reporting solution in Crystal to a solution in reporting services. However > > they are coming to me with various questions which I don`t know the answer > > to. > > > > 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When > > they > > report from a 20G database they can load the tempDB upto 10G. To me this > > sounds like they need to optomise their code and improve the way they > > manipulate the data. But does RS place a high load on the tempDB ? > I go against a 25 Gig database. My tempdb has grown to 800 meg but right now > 90% of that is free. My guess is this has nothing to do with RS. Somewhere > you have a stored procedure or some other code that is responsible for this. > > > > > 2. RS create SQL jobs to control the reports. These are always poorly > > named > > with alpha numeric strings. Can these be renamed safely to something more > > sensible ? > > > I guess I just don't worry about these. The jobs are being managed by RS and > I just let it do it. > > > 3. Does RS have its own scheduler or are we limited to using what SQL > > provides ? > You use RS to schedule reports but it in turn uses SQL Server. > > > > > 4. Would you envisage having the need to multiple temp tables for each > > report ? Surely sp`s and the like should be able to remove the need to > > create > > temp tables in many/most cases. > Not sure why you think as a rule most reports would need a temp table. Even > in my stored procedures I only use temp tables when necessary. Their > presence is not required at all. Temp tables are not bad or evil and should > be used when it makes sense. And, here I have to diverge from Enrique. You > can use temp tables whenever you want. They will absolutely not cause any > problems. There are few things you should do to make temp table usage in SP > work with RS: > 1. Click on the refresh fields button (to the right of the ...) > 2. Do not use set nocount on > 3. Do not explicitly drop the temp tables > 4. Have your last statement be a select > 5. If none of these work then add Set FMTONLY Off This has to do with how > RS finds our metadata information about the stored procedure. > > If the above rules are followed you will have no problem with temp tables. > > > > > Any help would be great as at the moment RS does not look like the way > > forward. > > > > Si > > I would say that you conclusion here is based on faulty information. > Especially the tempdb issue. Whoever is coding in a way that causes this > will cause the problem regardless of the tool used. One point, I hope you > are evaluating RS 2005, not RS 2000. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > >
See in line:
[quoted text, click to view] "Simon" <Simon@discussions.microsoft.com> wrote in message I go against a 25 Gig database. My tempdb has grown to 800 meg but right now news:C5533AB9-751B-48F3-8444-65E697485652@microsoft.com... > All > > Our Management Information department are trying to replace our current > reporting solution in Crystal to a solution in reporting services. However > they are coming to me with various questions which I don`t know the answer > to. > > 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When > they > report from a 20G database they can load the tempDB upto 10G. To me this > sounds like they need to optomise their code and improve the way they > manipulate the data. But does RS place a high load on the tempDB ? 90% of that is free. My guess is this has nothing to do with RS. Somewhere you have a stored procedure or some other code that is responsible for this. [quoted text, click to view] > I guess I just don't worry about these. The jobs are being managed by RS and > 2. RS create SQL jobs to control the reports. These are always poorly > named > with alpha numeric strings. Can these be renamed safely to something more > sensible ? > I just let it do it. [quoted text, click to view] > 3. Does RS have its own scheduler or are we limited to using what SQL You use RS to schedule reports but it in turn uses SQL Server.> provides ? [quoted text, click to view] > Not sure why you think as a rule most reports would need a temp table. Even > 4. Would you envisage having the need to multiple temp tables for each > report ? Surely sp`s and the like should be able to remove the need to > create > temp tables in many/most cases. in my stored procedures I only use temp tables when necessary. Their presence is not required at all. Temp tables are not bad or evil and should be used when it makes sense. And, here I have to diverge from Enrique. You can use temp tables whenever you want. They will absolutely not cause any problems. There are few things you should do to make temp table usage in SP work with RS: 1. Click on the refresh fields button (to the right of the ...) 2. Do not use set nocount on 3. Do not explicitly drop the temp tables 4. Have your last statement be a select 5. If none of these work then add Set FMTONLY Off This has to do with how RS finds our metadata information about the stored procedure. If the above rules are followed you will have no problem with temp tables. [quoted text, click to view] > > Any help would be great as at the moment RS does not look like the way > forward. > > Si I would say that you conclusion here is based on faulty information. Especially the tempdb issue. Whoever is coding in a way that causes this will cause the problem regardless of the tool used. One point, I hope you are evaluating RS 2005, not RS 2000. -- Bruce Loehle-Conger MVP SQL Server Reporting Services
Well, again, that is how someone programmed the stored procedure (I hope
this is in a stored procedure). It is not RS specific. If you were running this from query analyzer you would get the same issue. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Simon" <Simon@discussions.microsoft.com> wrote in message news:A4B08014-17F7-4529-8B9C-10196C095A98@microsoft.com... > Thanks for the reply. > > My concern is that the only thing that has changed on the SQL box in the > last week is new reports via reporting services. It does worry me that one > report has 10 temp tables and having analysed them whilst the report was > running they do grow to an insane size compared to the db that they are > reporting on. > > Si > > "Bruce L-C [MVP]" wrote: > >> See in line: >> >> "Simon" <Simon@discussions.microsoft.com> wrote in message >> news:C5533AB9-751B-48F3-8444-65E697485652@microsoft.com... >> > All >> > >> > Our Management Information department are trying to replace our current >> > reporting solution in Crystal to a solution in reporting services. >> > However >> > they are coming to me with various questions which I don`t know the >> > answer >> > to. >> > >> > 1. Is it usual for RS to place a heavy load on the tempDB in SQL. When >> > they >> > report from a 20G database they can load the tempDB upto 10G. To me >> > this >> > sounds like they need to optomise their code and improve the way they >> > manipulate the data. But does RS place a high load on the tempDB ? >> I go against a 25 Gig database. My tempdb has grown to 800 meg but right >> now >> 90% of that is free. My guess is this has nothing to do with RS. >> Somewhere >> you have a stored procedure or some other code that is responsible for >> this. >> >> > >> > 2. RS create SQL jobs to control the reports. These are always poorly >> > named >> > with alpha numeric strings. Can these be renamed safely to something >> > more >> > sensible ? >> > >> I guess I just don't worry about these. The jobs are being managed by RS >> and >> I just let it do it. >> >> > 3. Does RS have its own scheduler or are we limited to using what SQL >> > provides ? >> You use RS to schedule reports but it in turn uses SQL Server. >> >> > >> > 4. Would you envisage having the need to multiple temp tables for each >> > report ? Surely sp`s and the like should be able to remove the need to >> > create >> > temp tables in many/most cases. >> Not sure why you think as a rule most reports would need a temp table. >> Even >> in my stored procedures I only use temp tables when necessary. Their >> presence is not required at all. Temp tables are not bad or evil and >> should >> be used when it makes sense. And, here I have to diverge from Enrique. >> You >> can use temp tables whenever you want. They will absolutely not cause any >> problems. There are few things you should do to make temp table usage in >> SP >> work with RS: >> 1. Click on the refresh fields button (to the right of the ...) >> 2. Do not use set nocount on >> 3. Do not explicitly drop the temp tables >> 4. Have your last statement be a select >> 5. If none of these work then add Set FMTONLY Off This has to do with >> how >> RS finds our metadata information about the stored procedure. >> >> If the above rules are followed you will have no problem with temp >> tables. >> >> > >> > Any help would be great as at the moment RS does not look like the way >> > forward. >> > >> > Si >> >> I would say that you conclusion here is based on faulty information. >> Especially the tempdb issue. Whoever is coding in a way that causes this >> will cause the problem regardless of the tool used. One point, I hope you >> are evaluating RS 2005, not RS 2000. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> >>
One design point that might help you. A very nice UI feature of RS is drill
down. Seems like a great idea and it is a nice looking feature. However, you retrieve lots and lots of data that the user does not necessarily see. That could be the reason behind this. See if they are using drill down, suggest drill through instead. Also, are they using filter versus query parameters. When you use a filter in RS it applies the filter after retrieving the data rather than have SQL Server apply the where clause and return only the data you need. Except in rare cases they should stay away from filters. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Simon" <Simon@discussions.microsoft.com> wrote in message news:A203FB05-24B7-46FF-94D5-CC5A92A49A8E@microsoft.com... > Thanks for your replies as well. > > I think a course of how to optomise SQL code and the correct use of temp > tables may be the way forward for the report writers. > > Si >
On May 16, 10:52 am, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com>
[quoted text, click to view] wrote: > One design point that might help you. A very nice UI feature of RS is drill > down. Seems like a great idea and it is a nice looking feature. However, you > retrieve lots and lots of data that the user does not necessarily see. That > could be the reason behind this. See if they are using drill down, suggest > drill through instead. > > Also, are they using filter versus query parameters. When you use a filter > in RS it applies the filter after retrieving the data rather than have SQL > Server apply the where clause and return only the data you need. Except in > rare cases they should stay away from filters. > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Simon" <S...@discussions.microsoft.com> wrote in message > > news:A203FB05-24B7-46FF-94D5-CC5A92A49A8E@microsoft.com... > > > Thanks for your replies as well. > > > I think a course of how to optomise SQL code and the correct use of temp > > tables may be the way forward for the report writers. > > > Si My reference to not using a temp table at report inception is based on the fact that using a query including a temp table when first creating a report will error out (if using the report wizard to set it up). This does happen in this case w/o exception. That said, temp tables can be used in the report once an initial query is set up for the report. Regards, Enrique Martinez Sr. Software Consultant
Check out also:
DataTime Reporting Solution DataTime Universal (downloadable) http://cam70.sta.uniroma1.it/DataTimeUniversal/ DataTime Documentation http://cam70.sta.uniroma1.it/DataTimeDocumentation/ Group: http://groups.google.it/group/DataTime
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |