Reporting on Server Performance

Question

Reporting on Server Performance

kocheese

SSCommitted

Points: 1517
More actions
September 16, 2015 at 6:27 am

#300494

All:
I have been asked to provide a report on: "Sit back and think, what would some supposed VP want to see around the server (maybe charts showing peaks and valleys of use, highest users, longest running jobs, failed jobs, etc…)"
Is it possible to use SSRS to report on peaks / valleys of use, jobs, CPU, RAM?
I was always under the impression that SSRS reported on data, not server active server performances of the previous day that isn't captured in anyway, but wanted to find out from more knowledgeable people.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

andrew.mills 44498 (9/16/2015)
I was always under the impression that SSRS reported on data, not server active server performances of the previous day that isn't captured in anyway, but wanted to find out from more knowledgeable people.

There are DMVs available which you can query that will give you some performance statistics (without having to actually log data to then report on).

andrew.mills 44498 (9/16/2015)
Is it possible to use SSRS to report on peaks / valleys of use, jobs, CPU, RAM?

Will management know? Some pretty charts (random, or construed, data) would have the same comforting effect. I can recommend an excellent book "How to lie with computer graphics" - for example, if the Slice in the Pie Chart, i.e. the slice important to YOU, is not big enough rotate the pie chart so your slice is at the front and lean the chart over so that your slice has a really generous helping of Pie Crust 🙂

kocheese SSCommitted Points: 1517 More actions · Answer 2

I found this on DMVs

https://msdn.microsoft.com/en-us/library/hh230820.aspx

But it looks like these are only available for SQL Server 2012 and later. We are still using SQL Server 2008 R2.

Do I understand this correctly then that this wouldn't be available for our team?

JeepHound SSCrazy Points: 2963 More actions · Answer 3

The SQL Server DMV's are available in 2008r2 so you will be fine to use those. The issue is capturing the data and storing some history. We use a 3rd party tool called Idera but Red Gate, Dell, and many others have different options. SQL server has a built in capability with canned reports called a Data Management Warehouse that is easy to set up and will probably get you what you need. Browse to the section in SSMS under management - data collection - you can right click that and set up the DMW. And yes, reporting services can be used as a reporting tool to write your own customized reports that may not be included in the out of the box reports.

kocheese SSCommitted Points: 1517 More actions · Answer 4

Thank you for the help, but a follow-up question....

1). In SSMS (SQL Server Management Studio), I do not have an option for Management -- Data Collection. I can't find anything in SSMS for Data Collection.

2). There is also no option for "Data Management Warehouse" I see in my icons for SQL Server 2008 R2. It sounds like this might be an install needed?

I will research some 2008R2 DMVs and see how to set them up in the meantime. Thank you for the help

kocheese SSCommitted Points: 1517 More actions · Answer 5

I think I found the information you mention:

http://searchsqlserver.techtarget.com/tip/Manage-your-SQL-Server-databases-with-SQL-DMVs

http://www.codeproject.com/Articles/16861/Dynamic-Management-Views-DMV-A-SQL-Server-Fea

I think some of these links provide the information you were talking about. I should be able to find something in these to give what is requested.

Thanks again for pointing me to the DMV, as I never knew these existed....