Performance Base Line Monitoring Tool for SQL Server Instances
Supported SQL Server Version:- SQL 2005/2008/2008R2
Software Version;-1.0
Developed by:- Nirav Joshi
Mail :-Nirav Joshi
Features of this Tool.
1. In-depth analysis of SQL Server instance from all the performance parameters perspectives.
2. It does not required any database to stored historical performance data.
3. It can be run on SQL Server 2005/2008/2008R2 versions are supported.
4. Output of Performance Baseline report tool is in HTML format which can be very easy to read and interpret.
Pre-Requisites of this tool:-
1. It requires XP_cmdshell command to be enable to generate reports on the file system.
2.It create two stored procedure named as following
[InstanceAnalysis_PerformanceBaseLine] (Collects Performance Related data for specific instance)
[SP_InstanceBaselinePerfReport](It generate HTML report based on the data collected by above SP)
3. It will create following job [DBA_PerfBaseline_Report_Job] under the SQL Server agent to run this performance baseline report on demand as well on mention schedule.
4. We may need to give proper Write/Full permission to the folder where the HTML report will be placed. Please ensure that SQL Server login account and SQL Server agent account has proper full permission for folder where the HTML files are created.
Index of Read Me
1. How to Install the tool.
2. Where to look for the report.
3. General Errors and troubleshooting steps while deploying this report.
4. How to read reports.
How to Install Performance Base Line Monitoring tool in your SQL Server Instance.
1. Please open the file name “Performance Baseline TSQL.txt” in your server SQL Server management studio.
2. You have to run this TSQL in your SQL Server instance.
3. Once Command is successfully complete you will get message in the Query Message windows as following
4. This above command will create the two stored procedure under master database context and one agent job without schedule under the SQL Server Agent.
5. Once done now we have to invoke (start) the SQL Server agent job as following.
6. On successful completion of this job you will be able to see one HTML file generated in the location mention in the job history as following. Please note here we have not scheduled agent job for daily report generation you can specify time and day for report to be generated by scheduling this job under the SQL Agent.
Where to look for the Report:-
1. This report will be created on your servers file system and it will be on same location where the MSSQL Instance Data and Log directory resides.
2. In many servers you might not get the same location as mention above the best way to look for it in the job history which will give you clue where your report is located.
General Errors and Troubleshooting errors while you are deploying Performance Baseline Report:-
1. Most common error you may receive is to enable xp_cmdshell inside SQL Server. I have already place logic where it checks if xp_cmdshell is already enable then it will not touch it. But in the case if it is disable then it will enable it.
2. Sometime it may happen that you might able to update this configuration parameter using script then you have to use the following code to allow updates “allow updates” set to 1 using the following code.
3. Sometime error might be related to permission issue where we are not able to create folder under the specified location possible reason for these types of error message is insufficient permission given to the parent folder. In such cases you have to ensure that SQL Server /SQL Server Agent log on Service account has FULL rights on the parent folder where the “PerformanceBaseLine” folder is going to create as following
How to Read Reports.
Reports is in the HTML format you can open report in any browser available in your system.
· When the report is generated it will have the Server name followed by the time and date that report was created.
· It will be easy for you to look for the report for the older date also it so any issue if it happened in the past.
· Report will have following section to understand in depth analysis of SQL Server instance performance.
· Following are the parameter on which we will be measuring SQL Server Instance Performance Base Line and there will be comment section available for every section report which explain each parameter in some extent. Please refer to BooksOnline(BOL) for more information about the following parameter.
· Please note here when SQL Server reboot it will reset all the SQL Server related performance counters and DMV through which we are generating this report it is always good time to check history of performance if SQL Server is up and running for quite long time please always look in to the Uptime section where it will give you an idea about when the SQL Server was last restart.
1. SQL Server Up Time
2. SQL Server Name and Version Detail
3. SQL Server Name and Installation Detail
4. SQL Server Server properties
5. SQL Server Server CPU Information
6. Server Processor Information
7. SQL Server SP_CONFIGURE Information For Instance
8. SQL Server Databases Datafiles location size and status
9. SQL Server Databases Configuration Properties
10. SQL Server Databases Datafiles Writes/Reads
11. SQL Server Databases Wise CPU Utilization
12. SQL Server Databases Cache Size Information in Buffer Pool
13. SQL Server Instance Wait Type Information
14. SQL Server Signal Wait in Percentage
15. SQL Server Login and session count detail
16. SQL Average Tasks count
17. SQL and OS CPU Utilization from SQL Ring Buffer
18. SQL Memory Utilization History
19. SQL Memory Grant Pending History
20. SQL Memory Clerks Memory Utilization
21. SQL Ad Hoc Query Plan cache Utilization by Top 10
22. SQL Server 2005 TokenAndPermUserStore cache information
23. Monitor the number of entries that are removed in the cache store during the clock hand movement
24. SQL Server enable trace information
25. SQL Server Top 10 SP ordered by Total Worker time
26. SQL Server Scheduler stats and NUMA Stats
27. SQL Server Top 20 SP Executed by Physical Read(IO Pressure)
28. SQL Server Top 10 SP Executed by Logical Read(Memory Pressure)
29. SQL Server Missing Indexes by Index Advantage
30. SQL Server Detected Blocking on Instance
31. SQL Server Database Growth in Last Six Month
32. SQL Server Instance Memory Configuration
33. SQL Server Instance Buffer Pool Usage
34. SQL Server Total Memory Consumption
35. Memory Needed by SQL Server Instance
36. Dynamic Memory Usage for SQL Server Connections
37. Dynamic Memory Usage for SQL Server Locks
38. Dynamic Memory Usage for SQL Server Cache
39. Dynamic Memory Usage for SQL Server Query Optimization
40. Dynamic Memory Usage for Hash sort Index Creation
41. Dynamic Memory Usage by SQL Cursors
42. Bufferpool Pages(Includes Free,Datapage,Stolen)
43. Bufferpool Pages Total Number of DataPages
44. Bufferpool Pages Total Number of FreePages
45. Bufferpool Pages Total Number of Reserved Pages
46. Bufferpool Pages Total Number of Stolen Pages
47. Bufferpool Pages Total Number of Plan Cache Pages
48. SQL Server Binary Module Information
49. SQL Server Version Store Information
50. SQL Server Version Store Information
51. SQL Server Tempdb Usaage by Session
52. SQL Server Top Sessions
