There are many ways of collecting statistics of SQL Server such as CPU usage, IO made, reads and writes. One facility given by SQL Server is, an Extended Stored Procedure, sp_monitor. This procedure can be used for seeing statistics related to resource use as SQL Server keeps these information using system statistical functions. This procedure shows values since SQL Server was restarted and values since last run sp_monitor was run.
For more details: https://msdn.microsoft.com/en-us/library/ms188912.aspx
Here is the result of the procedure;
This has been mainly given to DBAs to have a quick look. Because of that, collecting them regularly for future analysis is bit difficult. Format is not much user-friendly and returns multiple resultsets, hence calling it using TSQL and saving it in a table is not much easy. However, it can be called and save the result using simple c#.net code. Let's make a table for collecting them first.
-- create a database for holding data
CREATE DATABASE ServerMain;
GO
-- create a table for holding data
USE ServerMain;
GO
CREATE TABLE dbo.ServerStatistics
(
DateRun datetime primary key
, CPU_Busy int not null
, IO_Busy int not null
, Packet_Received int not null
, Packet_Sent int not null
, Packet_Errors int not null
, Total_Reads int not null
, Total_Writes int not null
, Total_Errors int not null
, Connections int
);
GO
-- Procedure for inserting data
CREATE PROCEDURE dbo.AddServerStatistics @DateRun datetime, @CPU_Busy int, @IO_Busy int
, @Packet_Received int, @Packet_Sent int, @Packet_Errors int, @Total_Reads int
, @Total_Writes int, @Total_Errors int, @Connections int
AS
BEGIN
INSERT INTO dbo.ServerStatistics
(DateRun, CPU_Busy, IO_Busy, Packet_Received, Packet_Sent
, Packet_Errors, Total_Reads, Total_Writes, Total_Errors, Connections)
VALUES
(@DateRun, @CPU_Busy, @IO_Busy, @Packet_Received, @Packet_Sent
, @Packet_Errors, @Total_Reads, @Total_Writes, @Total_Errors, @Connections)
END
GO
and here is the c#.net code for calling this procedure and saving the result.
DateTime DateRun = DateTime.Now;
int CPU_Busy = 0;
int IO_Busy = 0;
int Packet_Received = 0;
int Packet_Sent = 0;
int Packet_Errors = 0;
int Total_Reads = 0;
int Total_Writes = 0;
int Total_Errors = 0;
int Connections = 0;
string commandText = "sp_monitor";
using (SqlConnection connection = new SqlConnection(@"Data Source=(local)\SQL2014;Database=ServerMain;Integrated Security=true;"))
{
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DateRun = Convert.ToDateTime(reader[1]);
}
reader.NextResult();
while (reader.Read())
{
CPU_Busy = Convert.ToInt32(reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).Substring(0, reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).IndexOf(")")));
IO_Busy = Convert.ToInt32(reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).Substring(0, reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).IndexOf(")")));
}
reader.NextResult();
while (reader.Read())
{
Packet_Received = Convert.ToInt32(reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).Substring(0, reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).IndexOf(")")));
Packet_Sent = Convert.ToInt32(reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).Substring(0, reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).IndexOf(")")));
Packet_Errors = Convert.ToInt32(reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).Substring(0, reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).IndexOf(")")));
}
reader.NextResult();
while (reader.Read())
{
Total_Reads = Convert.ToInt32(reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).Substring(0, reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).IndexOf(")")));
Total_Writes = Convert.ToInt32(reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).Substring(0, reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).IndexOf(")")));
Total_Errors = Convert.ToInt32(reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).Substring(0, reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).IndexOf(")")));
Connections = Convert.ToInt32(reader[3].ToString().Substring(reader[3].ToString().IndexOf("(") + 1).Substring(0, reader[3].ToString().Substring(reader[3].ToString().IndexOf("(") + 1).IndexOf(")")));
}
connection.Close();
}
commandText = "AddServerStatistics";
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameterDateRun = new SqlParameter("DateRun", SqlDbType.DateTime);
parameterDateRun.Value = DateRun;
command.Parameters.Add(parameterDateRun);
SqlParameter parameterCPU_Busy = new SqlParameter("CPU_Busy", SqlDbType.Int);
parameterCPU_Busy.Value = CPU_Busy;
command.Parameters.Add(parameterCPU_Busy);
SqlParameter parameterIO_Busy = new SqlParameter("IO_Busy", SqlDbType.Int);
parameterIO_Busy.Value = IO_Busy;
command.Parameters.Add(parameterIO_Busy);
SqlParameter parameterPacket_Received = new SqlParameter("Packet_Received", SqlDbType.Int);
parameterPacket_Received.Value = Packet_Received;
command.Parameters.Add(parameterPacket_Received);
SqlParameter parameterPacket_Sent = new SqlParameter("Packet_Sent", SqlDbType.Int);
parameterPacket_Sent.Value = Packet_Sent;
command.Parameters.Add(parameterPacket_Sent);
SqlParameter parameterPacket_Errors = new SqlParameter("Packet_Errors", SqlDbType.Int);
parameterPacket_Errors.Value = Packet_Errors;
command.Parameters.Add(parameterPacket_Errors);
SqlParameter parameterTotal_Reads = new SqlParameter("Total_Reads", SqlDbType.Int);
parameterTotal_Reads.Value = Total_Reads;
command.Parameters.Add(parameterTotal_Reads);
SqlParameter parameterTotal_Writes = new SqlParameter("Total_Writes", SqlDbType.Int);
parameterTotal_Writes.Value = Total_Writes;
command.Parameters.Add(parameterTotal_Writes);
SqlParameter parameterTotal_Errors = new SqlParameter("Total_Errors", SqlDbType.Int);
parameterTotal_Errors.Value = Total_Errors;
command.Parameters.Add(parameterTotal_Errors);
SqlParameter parameterConnections = new SqlParameter("Connections", SqlDbType.Int);
parameterConnections.Value = Connections;
command.Parameters.Add(parameterConnections);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
If you continuously run this code or schedule for every one hour, you have enough of data to see how busy is your SQL Server and whether it needs more resources or not. Here is a simple chart created using SSRS, based on the saved data.
No comments:
Post a Comment