Jan3
Reporting on the performance of your SharePoint SQL Server
Categories:
Or any other SQL server for that matter...
Another task I managed to complete within a day was to get SSRS installed on WSSDemo.com's SQL server (still running 2005, upgrading to 2008 will wait for another day).
There are a number of reports you can get from the SQL Server 2005 Performance Dashboard which is a free download from http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
After installing this package, additional reports are added to SQL Management Studio. You can also import these into Reporting Services. After importing them you have to reset the Data Source for each report before running the performance_dashboard_main report from which you can launch all the other more details reports.
|
Historical IO Report |
|
|
|
|
|
Report Time: 1/2/2009 8:24:28 PM |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The following table shows the number of IOs performed for each database since it was brought online (normally server startup) and how this compares to the total IO across all online databases. |
|
|
|
|
|
Database Name |
% Reads |
Reads |
Read Wait Time (ms) |
Avg Read Wait (ms) |
% Writes |
Writes |
Write Wait Time (ms) |
Avg Write Wait (ms) |
% Total IO |
|
AdventureWorks |
0.03% |
26 |
270 |
10.4 |
0.00% |
6 |
12 |
2.0 |
0.00% |
|
AdventureWorksDW |
0.03% |
22 |
125 |
5.7 |
0.00% |
6 |
12 |
2.0 |
0.00% |
|
Ian |
0.05% |
35 |
273 |
7.8 |
0.00% |
6 |
12 |
2.0 |
0.00% |
|
master |
0.12% |
87 |
672 |
7.7 |
0.00% |
105 |
256 |
2.4 |
0.01% |
|
model |
0.12% |
88 |
735 |
8.4 |
0.00% |
18 |
51 |
2.8 |
0.00% |
|
MOSS_New_Content |
0.07% |
52 |
315 |
6.1 |
0.00% |
6 |
11 |
1.8 |
0.00% |
|
MOSS_Templates |
7.25% |
5394 |
53978 |
10.0 |
0.24% |
6200 |
4494 |
0.7 |
0.44% |
|
MOSS_Training |
2.21% |
1647 |
8525 |
5.2 |
0.11% |
2912 |
1127 |
0.4 |
0.17% |
|
mossdemo_Content |
0.49% |
365 |
1508 |
4.1 |
0.08% |
2042 |
657 |
0.3 |
0.09% |
|
msdb |
0.25% |
186 |
1718 |
9.2 |
7.93% |
204445 |
130085 |
0.6 |
7.71% |
|
MySite_Content |
0.57% |
427 |
2753 |
6.4 |
0.16% |
4048 |
1131 |
0.3 |
0.17% |
|
MySiteAccountManagerRole |
0.10% |
76 |
285 |
3.8 |
0.00% |
6 |
3 |
0.5 |
0.00% |
|
MySiteFinancialAnalystRole |
0.09% |
69 |
248 |
3.6 |
0.00% |
6 |
3 |
0.5 |
0.00% |
|
MySiteHRManagerRole |
0.08% |
56 |
210 |
3.8 |
0.00% |
6 |
10 |
1.7 |
0.00% |
|
ReportServer |
0.04% |
32 |
13 |
0.4 |
0.10% |
2696 |
2584 |
1.0 |
0.10% |
|
ReportServerTempDB |
0.06% |
44 |
25 |
0.6 |
0.03% |
745 |
989 |
1.3 |
0.03% |
|
SharePoint_AdminContent_a0e0098f-f4e2-43fb-9608-0f462fc357cf |
1.10% |
819 |
4351 |
5.3 |
0.00% |
20 |
8 |
0.4 |
0.03% |
|
SharePoint_Config |
0.30% |
225 |
2644 |
11.8 |
53.48% |
1379153 |
709271 |
0.5 |
51.98% |
| |
File Name |
% Reads |
Reads |
Read Wait Time (ms) |
Avg Read Wait (ms) |
% Writes |
Writes |
Write Wait Time (ms) |
Avg Write Wait (ms) |
% Total IO |
| |
SharePoint_Config |
69.33% |
156 |
2048 |
13.13 |
0.20% |
2784 |
1651 |
0.593031609 |
0.21% |
| |
SharePoint_Config_log |
30.67% |
69 |
596 |
8.64 |
99.80% |
1376369 |
707620 |
0.51412085 |
99.79% |
|
SharePointTrainingKit |
0.33% |
248 |
1170 |
4.7 |
0.01% |
339 |
231 |
0.7 |
0.02% |
|
SSO |
0.05% |
35 |
139 |
4.0 |
0.00% |
25 |
11 |
0.4 |
0.00% |
|
SSP1_Content |
0.37% |
278 |
1587 |
5.7 |
0.07% |
1910 |
496 |
0.3 |
0.08% |
|
SSP1_DB |
3.70% |
2754 |
72989 |
26.5 |
16.04% |
413664 |
517477 |
1.3 |
15.69% |
|
SSP1_Search_DB |
7.16% |
5332 |
92981 |
17.4 |
15.86% |
408991 |
6293620 |
15.4 |
15.61% |
|
tempdb |
56.72% |
42216 |
56133 |
1.3 |
3.14% |
80918 |
333505 |
4.1 |
4.64% |
|
WSS_Search_MOSSAPP |
1.06% |
792 |
10732 |
13.6 |
2.42% |
62502 |
46053 |
0.7 |
2.39% |
|
WSSDemo_Content |
17.63% |
13120 |
155213 |
11.8 |
0.32% |
8271 |
36427 |
4.4 |
0.81% |
| |
|
Grand Total |
100% |
74425 |
469592 |
6.3 |
100% |
2579046 |
8078536 |
3.1 |
100% |