Performance Baseline Report
Aug 23, 2007
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
View 3 Replies
ADVERTISEMENT
Apr 7, 2000
Hi ALL,
I have a 5G database with 1G log file running on SQL server6.5. I would like to establish a short list of performance Monitor objects (NT & SQL server) so that is adequate to capture application performance.
I would like to get some advice:
1.What kind of objects should I monitor on my list?
2.How to automate the process (via a resource kit utility?) to save a new log file off daily and manage an archive process?
3.How to set alerts/notification (but no SQL mail is available now)?
Any help would be appreciated and thanks in advance.
Stella Liu
View 1 Replies
View Related
Jan 29, 2003
Hello, I'm running sql2k sp3 on a win2k server. I run the baseline security Analyzer and get yellow (X's) for unchecked buffer in windows help facility, ms02-55.
Flaw in SMB signing could enable group policy to be mofdified, MS02-070
These warnings are telling me that my file versions are greater than expected. the file i have is version 5.2.3718.0 and the one it wants is 5.2.3669.0 and its the same for the other file as well. do i need to fix this?
View 1 Replies
View Related
Jan 9, 2007
Sarfaraz writes "I have captured SQL Profiler data. I was reviewing top running CPU intensive SQL statements. The Duration (in seconds) 1.39, 1.09, 0.16 and CPU (in seconds) 0.97, 0.95, 0.16 respectively for some SQL statements. How do I know what is the normal baseline for duration and CPU in order to determine the CPU intensive SQL statements.
Secondly same question for long running procedure duration 0.14, 0.11. What is the normal baseline here. Is this normal or too long.
Thanks,
Sarfaraz"
View 5 Replies
View Related
Dec 13, 2007
I am training to be a DBA in a company running about 30 machines with MS SQL Server (2000 and 2005). Last week I went to a class where the instructor recommended establishing a performance baseline using windows performance monitor. He also advised to run perfmon remotely so as to not effect the performance.
What I am wondering is since I have so many different machines to baseline, can I run perfmon on one box, using a seperate counter log for each server? I would like to get a nice week-long baseline for each machine, but I also don't want to get bad data by running too many logs at the same time.
My plan is to do a small set of counters for processor, memory, disk, and the SQL server instance(about 10 counters total).
If anyone has experience in this area, I would appreciate any advice that you might have.
Thanks,
Matt
View 1 Replies
View Related
Jul 20, 2005
What is the best method of creating schema creation scripts that can bestored into a version control system. The process of using em togenerate a script is not an appealing option. I am still learning theMS Sql sys tables and have not found a useful list of all the codes &types to join the tables etc.mike--Posted via http://dbforums.com
View 2 Replies
View Related
Sep 27, 2007
Hi
I downloaded MBSA and ran it against my SQL 2005 Server. It tells me that I have a severe risk because
'The following databases have public access.Remove the public access if it is not required - tempdb , model , msdb , ReportServer , ReportServerTempDB'
I have checked these databases and each have the Guest User but it is disabled. If I check the database properties the public role has no permissions against the listed databases.
Is this a bug with MBSA? If not how do I remove Public Access?
View 11 Replies
View Related
Apr 9, 2015
I've been put in a situation where I have a number of SQL DB's running on 2005/2008 which I have responsibility for. I've been given limited information so am looking at a starting point to determine where I go from here.
I have of course ensured there is a backup strategy in place to secure the data.
View 1 Replies
View Related
Jul 1, 2015
We are adding 4-5 indexes to one database and dropping 2 unused indexes. I don't have proper testing environment. How to monitor these indexes changes? Do we need to run any baseline but we don't get load all the time same load all the days
View 8 Replies
View Related
Jul 7, 2015
Any method on creating baselines for your stored procedures/objects.
View 4 Replies
View Related
Mar 13, 2014
I'm trying to run the SQL Server 2012 Best Practices Analyzer. After learning that I first had to install the Microsoft Baseline Configuration Analyzer, I did that.
When I tried to run the Microsoft Baseline Configuration Analyzer/Best Practices Analyzer remotely, though, I got an extremely verbose error message and want to confirm if I really need to do all of the steps involved on each target server that I want to analyze.
Here is what I tried after launching the Microsoft Baseline Configuration Analyzer application (I'm using 'MYSERV' as the target server name):
1. Clicked on "Connect to another computer"
2. Clicked "Another computer:" entered: MYSERV
3. Checked the "Connect as another user:" box
4. Clicked "Set User..." added by Windows credentials.
5. Checked the Use CredSSP box
6. Clicked OK.
After a second or two, the error below came back. Is that what I have to do on each remote computer to run the analyzers?
Microsoft Baseline Configuration Analyzer
Connecting to the remote server failed with the following error message : The WinRM client cannot process this request. CredSSP authentication is currently disabled in the client configuration. Change the client configuration and try the request again. CredSSP authentication must also be enabled in the server configuration. Also, Group Policy must be edited to allow credential delegation to the target computer. Use gpedit.msc and look at the following policy: Computer Configuration -> Administrative Templates -> System -> Credentials Delegation -> Allow Delegating Fresh Credentials. Verify that it is enabled and configured with an SPN appropriate for the target computer. For example, for a target computer name "myserver.domain.com", the SPN can be one of the following; WSMAN/myserver.domain.com or WSMAN/*.domain.com For more information
[URL]
View 1 Replies
View Related
Jan 28, 2008
Hi all,
I have a report which uses two different datasets. The first one is used to get the required data and the second one is to get the customer name to be used in a parameter.
I had a major performance issue with the report as the first dataset is very complex. And after I did a lot of SQL tuning to the first dataset, it is now working fine with an acceptable performance.
The problem now is when I tried to add a condition in this dataset to be used by the parameter like
WHERE CUSTOMER_NAME IN (@CustName )
The performance of the report is getting very bad again and the report can take more than 15 min. to get the result.
The second dataset is a simple query that is used to get the customer name from the customer table so I don€™t think it is the problem.
Any ideas? Do you think it has anything to do with the SSRS parameters.
Thanks all.
View 1 Replies
View Related
Jan 31, 2008
As far as I know there are 2 ways to render the report in a Web page:
1. Using ReportViewer control and call its Render method
2. Using URL access
Which of the method is faster? As we are in the process of increasing the Performance of our Reporting Web Application.
I appreciate any suggestions in this regard.
View 4 Replies
View Related
Feb 11, 2008
Hello! We are having a strange problem with Reporting Services 2005. Our RS server is located in a DMZ with the supporting RS database housed in our internal network. We have a batch of reports that perform well when rendering to browsers in our internal network. However, when accessing these same reports externally, the reports take a very long time to render. The difference between internal vs. external is huge; we are looking at maybe a minute tops to render the report from an internal browser vs. 10-15 minutes from an external client browser. The website and reports are setup for anonymous access, so it shouldnt be an authentication problem.
The RS execution log doesnt indicate much difference between the two. The data retrieval does take a little longer (only a second or two), according to the log, for an external request. But still, the times recorded in the Execution Log dont match at all what we are experiencing for external requests.
The various logs, both for reporting services and IIS arent picking up any anomalies either and Im at my wits end as to what else I can use to try and troubleshoot this problem. Has anyone seen this behavior before, or have any ideas what might shed some light on this?
View 2 Replies
View Related
Jan 7, 2008
I'm using sql2000. VB2003.
My report uses the following parameters
WHERE (@Unit='' or a.unit = @Unit) and (@WO='' or a.wo = @WO)
It takes about 23 seconds to run if I type in 71173 as Unit leaving WO blank.
If I edit the SQL to be Where a.unit=71173 it execustes in 1 second.
Where am I going wrong?
I read some of the threads but I couldn't get a clear idea if this is normal or not.
Card Gunner
View 3 Replies
View Related
Oct 26, 2007
Hi all,
In my project, I have a website and through that, I run my reports. But the reports take a lot of time to render. When I checked the profiler, it showed that the SP for the report is run around 4-5 times. Due to this, the report rendering takes a lot of time.
When, I ran the SP with the same set of Parameters in Query Analyser, it ran in around 18 seconds. But when I ran the report from web interface, it took around 3 minutes to completely show the data. And the SP has been run 5 times.
I am having serious problems with Report's performance because of this. Many a times, report just times out. I have set the timeout as 10 minutes. And because the Sp is run 5 times, the report times out, if there is huge amount of data.
Any help would be appreciated.
Thanks in advance.
Swati
View 5 Replies
View Related
Apr 18, 2007
i am in the process of creating some new reports on a new Reporting Services site
reports developed in VisualStudio2005 pointing an an AnalysisServices Cube on a (development) SQL2005 database
when i Preview the reports in Visual Studio 2005 on my development box, the reports take about 12-15 seconds to run (about 4-5 seconds before the "Report is being generated" logo appears, then another 8-10 seconds before the report appears (report is a series (1-8) of charts))
when i run the same report (with the same report criteria) via the Report Manager (pointing at the same AnalysisServices Cube), it now takes about 45 seconds to run (about 15 -16 seconds before the "Report is being generated" logo appears, then another 30 seconds before the report appears)
(n.b. this is not due to initial Report Manager start-up delays, the timings are for the second and third run of the report)
can anyone out there point me in the direction of where to look ??
View 1 Replies
View Related
May 3, 2007
Hi, all experts here,
Would any of you give me any ideas for how could we optimize the report on data mining models? (as we know, for the data mining report, we have to select the mining model and the case table)
Hope it is clear for your advices and help.
Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 6 Replies
View Related
Jul 20, 2005
Is there anybody out there with a MS SQL 2K Security Baseline orSecurity Checklist. Where can I get one????Thanks in advanceDavid
View 1 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jun 22, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies
View Related
Jun 16, 2006
SQL server 2005 express reporting problem.
error message:
This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service
I got this error message when I try to connect to database hosted in another PC running SQL server 2000.
Is it true that SQlL server Express can only use Local Database Engine to host the database?
View 5 Replies
View Related
Sep 9, 2015
I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my .rdl file to be use as my sub report.
View 5 Replies
View Related
Jun 30, 2006
After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?
The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.
Any suggestions would be appreciated. Thanks
View 3 Replies
View Related
May 3, 2007
How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.
View 7 Replies
View Related
Feb 6, 2008
Hi,
When i select datasource in Report Builder, i am able to see all the available DataSources.
Eg: I have selected one datasource from the list and which has 3 tables(table1, table2, table3) associated to that datasource.
when i drag and drop table1 fields to report, i am not able to see the other 2 tables(table2 & table3)
Is there any property or relationship do i need to maintain?
Thanks,
SR.
View 5 Replies
View Related
Sep 6, 2007
I've created a linked server with a pretty basic Excel spreadsheet, and used this command to create a linked server to it:
sp_addlinkedserver ''XL_SPS_1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls', null, 'Excel 8.0'
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel
as
select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
Thanks,
--Stan
View 3 Replies
View Related
Oct 9, 2015
I have done the following and a domain user would not access report created a login to the SQL server to the user (this SQL Server is where data source DB is)went to site setting in Report Manager and made this use a system userright clicked on report folder and made this user in the browser roleeven checked that in the report in question, the user is already in the browser role Still the user would not access the report! "User .......... does not have required permission" is the error message I am getting.Â
View 6 Replies
View Related
Apr 30, 2007
Hi,
I have created a report with the report server project template.
the report is created from stoked procedure having defaut input parameters.
With visual studio, i publish my report on reportserver. whenever i access to my report on this url :
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.
I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.
I tried to put parameter directly in the url in this way
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'
but without success.
my error message is that one
An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)
My stocked procedure is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region
The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region
What is wrong in that ?
Thanks for your help.
Arioule
View 1 Replies
View Related
Jul 5, 2012
I have written a report visual studio. Â The report has 10 multi-value parameters that pull data from their individual data sets within the reports. Â When running the report from within Visual Studio it renders fine. Â There are no errors reported, only a warning related to a pathname I have use to retrieve image data and display on the report.Deployment of the report is error free.When I view the report I briefly get the "Loading" splash window, but then nothing. Â None of the headers or static text is displayed. Â The results window is blank.
The report defaults all the parameter values. Â In trying to debug the issue I have found by reducing the number of parameter values the report will render. Â Once I have all the values added, the report does nothing.Below is the query being used for the report. Â The where clause in the query shows the parameters being used and the syntax.We are running on Windows Server 2008 R2 and SQL Server 2008 R2.
Ralph
SELECT RTRIM(a.ITEMNMBR) AS Style, RTRIM(a.ITEMDESC) AS Description, a.ITMSHNAM AS UPC, c.LOCNCODE AS Store, d.LISTPRCE AS ListPrice, a.CURRCOST AS Cost,
a.USCATVLS_1 AS [Main Category], a.USCATVLS_2 AS Market, a.USCATVLS_3 AS [Alternate Retail], a.USCATVLS_4 AS Country, b.ITEMXTRAS_1_Type AS Type,
b.ITEMXTRAS_2_Finish AS Finish, b.ITEMXTRAS_3_SubCategory AS SubCategory, b.ITEMXTRAS_4_Department AS Department,
[code]...
View 7 Replies
View Related
Aug 20, 2007
Here's tricky one.
I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
Essentially, I want to create a for each loop.
Any ideas?
View 4 Replies
View Related
Nov 23, 2015
We are about to implement SSRS reports (SQL Server 2012), and have found one issue we don't like. The report looks great when we open it in pdf format. However, when we open it in Excel format, we want it to be the raw data, not look like the pdf report. I tried the simpl headers, and that removed the page header/footer. However, the report format was still in play.In other words, we would like the report when opened in Excel, to look just like it does when we open it as a CSV.I have tried to modify the config file a couple of times. Here is my latest attempt:
Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">CSV (comma delimited)</Name>
    </OverrideNames>
   </Extension>
   <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">Excel (csv)</Name>
    </OverrideNames>
   </Extension>
Essentially I have set Visible = "false" for the Excel type, and tried to duplicate the CSV, changing the name to make it look to the user like it is "Excel". This fails. I checked the event viewer, and I see two entries.. The RSReportServer.config file has been modified. (Information)The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.I literally took the file, opened it in Notepad, pasted in my changes, from another notepad window, and tried to run the report. I did not see EITHER of the two names I expected, using the OverrideNames tag. As I said earlier, I was able to affect the outcome, by setting SimpleHeaders to true (or however it is identified). That did work in that the page header and footer was removed.
View 6 Replies
View Related
