Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

SQL Server Performance Base Line Script

By Nirav Joshi,

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

Total article views: 6705 | Views in the last 30 days: 137
 
Related Articles
ARTICLE

A Quick Tour of the Performance Dashboard Reports

SQL Server Performance Dashboard comprises a set of custom reports that give you nitty gritty detail...

FORUM

memory

memory

FORUM

SQL server performance

SQL server performance - Page File memory usage

ARTICLE

Analyzing Memory Requirements for SQL Server

The second in a series from Adindam Banerjee on server performance and capacity planning. This time ...

FORUM

Memory_Parallelism Performance Problem

Memory consumption appears to be greater than Max Server Memory setting and sudden rise in Paralleli...

Tags
baselines    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones