SQL Server Performance Forum – Threads Archive

New to Reporting – help me to learn

Hi, I am new to SQL server Reporting.
Could you please give me suggestion that how to begin to learn.
Could you please give me reference materials.[|)] Mathivanan K

What kind of reporting are you trying to do? Are you using the SQL Server Analysis Services to do OLAP type reporting, or are you talking about using Crystal Reports (or like product) to simply pull data out of your database? My response will be along the lines of the Crystal Report type reporting where you select data out of your OLTP database, and assumes you are in a situation where your volume of data is not overwhelmingly large. Suggestion #1: Realize that if you are running very complicated select statements against the same database that others are using for their work you will impact the performance and the phone will start ringing. Thus my suggestion is to have a second server in place which holds nearly the same data as the production server, but the data on it may or may not be "live." Meaning if you do nightly backups of your production system, you can implement an automated job on the second server to simply restore that data. Then your reporting server has a day old copy of the data. If you are more advanced you can use replication of your production system to have nearly live data on your reporting server. With a Reporting Server in place you can then run monster querueies without impacting the performance of your production system. In my environment (only 50 GIG’s of database) we use the backup/restore approach and 95% of our reports can be run from the day old data. However you will still find that some users insist on "live" data, and for those we have to take the hit on the production performance. Suggestion #2: When running queruies to pull data from your reporting server understand that in selecting the data for reports it is extremely likely that you will need to construct new indexes in order to speed the queries up since they will likely be pulling based on fields that aren’t already indexed. You can identify the likely indexes in a couple of ways: 1. Simply turn on the SQL Profiler and capture the report queries and then utilize the Indexing Wizard. 2. Use a third party tool like Speed Coefficient which will capture a trace just like Profiler but then it will automatically display and rank the worst of the bunch so you know which ones are killers and which can be put off. It will rank them by CPU usage or Duration, either way. You then pull the worst of the bunch and run them in SQL Query Analyzer and have it show you the Execution Plan. You then start adding indexes to speed things up. Keep in mind once you create an index you need to do 1 of 3 things: 1. (not suggested approach) Create the index in the production database so that it will be backed up/restored each night to your reporting server (not suggested since having indexes that are for reporting only will only slow down your production server). 2. If you use the backup/restore approach you can alter the Job you run to do the restore and apply the Alter Table commands to create the indexes you want. Thus you restore the database as it was, then you create the indexes on the fly afterwards. If you create the index using the Manage Indexes option for your table(s), you can have it generate the SQL you will need to paste into your job. 3. If you are using the replication approach then you can just create the indexes you want to speed things up directly in your reporting server and they will remain in tact. Suggestion #3: Once you have the reports/queruies in place you can optimize them further by creating Views on your report server or stored procedures so that they can be pre compiled and pre optimized and change your reports to report from/using them instead of just sending the lengthy queries each and every time. I hope this helps,
Dalton

Hi Dalton, Thanks for all suggestion. i am talking about MSSQL Server Reporting Services Thanks & Regards
Mathivanan K
Great work may have to pass through these stages – ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

hi Mathivanan , I got some good F&Q related to Reporting Services
Check this out:
http://msdn.microsoft.com/SQL/2005/2005Webcasts/ReportingServicesQandA.aspx
You can check with MS site for more info about this. I got some info related to the Virtual Lab for Free in MS site.
Pls Check the site:
http://msdn.microsoft.com/vstudio/tryit/hosted/sql/default.aspx
-Johnson

[FONT=å®‹ä½“]Hi,[/FONT][FONT=å®‹ä½“][/FONT]
[FONT=å®‹ä½“]Try to use the tool at [/FONT][FONT=å®‹ä½“]http://www.raqsoft.com/[/FONT][FONT=å®‹ä½“].[/FONT][FONT=å®‹ä½“][/FONT]
[FONT=å®‹ä½“]You can get it for free,I think it can help you solve your problem.[/FONT]

]]>