Click here to Skip to main content
12,297,983 members (53,940 online)
Click here to Skip to main content
Add your own
alternative version

Stats

44.4K views
4.6K downloads
105 bookmarked
Posted

Advanced SQL Server Monitor with Performance Graph, Analysis and Version Control

, 10 Jan 2012 LGPL3
Rate this:
Please Sign up or sign in to vote.
monitor sql server processes and jobs, analyse performance, object version control, view executing sql query, kill process / job, object explorer, database shrink/log truncate/backup/detach/attach etc

Introduction

This is the second article of SQL Monitor, the first one is here: sqlmon.aspx

For background infomation, please refer to the above mentioned article.

SQL Monitor can monitor sql server processes and jobs, analyse performance, object version control, view executing sql query, kill process / job, object explorer, database shrink/log truncate/backup/detach/attach etc.

Ok, for a better understanding, first let's look at the following picture:

Well, we can see it shows the real time IO/CPU/Network info of the SQL Server. In fact, it can do a lot more. For infomation about object explorer, activities and version control, please the above mentioned first article.

Background

In the previous article, we discussed about how I implemented the object explorer, activities and version control, in this article, I will talk about the newly introduced performance graph and analysis features.

We have been wondering like "What is going on with my SQL Server?", "How well is it running?", "Where is the bottleneck", "Can I make it faster?" etc. Well, it is time to get the answer, ok, to some extent, somehow, so far Wink | ;)

Performance Graph

Ok, maybe this is not the most exciting functionality you wish for the coming Christmas Big Grin | :-D .

With performance graph, you can keep tracking what is going on with your SQL Server, it shows the following real time infomation:

  • Total IO busy time since last server start
  • Total CPU busy time since last server start
  • Total IO Reads (in byte) since last server start
  • Recent n seconds IO Reads (in byte)
  • Total IO Writes (in byte) since last server start
  • Recent n seconds IO Writes (in byte)
  • Total Packet Reads (in byte) since last server start
  • Recent n seconds Packet Reads (in byte)
  • Total Packet Writes (in byte) since last server start
  • Recent n seconds Packet Writes (in byte)
  • Total connections since last server start
  • Recent n seconds connections

n second is the monitor refresh interval, which you can set in the options.

Where does the infomation come from?

Well, because I am a very boring guy, so I really want to do something to kill time, from time to time, I will dig through different kinds of resources, like the whole SQL Server databases, including all functions, stored procedures, views, and one day I found a system stored procedure called sp_monitor. mmmmmm...... That means everything, right? Thank you very much, SQL Server team Wink | ;) . When you dig deeper, you can find out that it uses the following system variables:

@@cpu_busy
@@io_busy
@@idle
@@pack_received
@@pack_sent
@@connections
@@packet_errors
@@total_read
@@total_write
@@total_errors
I modified the stored procedure to accomplish my need:
declare @now         datetime
declare @cpu_busy     int
declare @io_busy    int
declare @idle        int
declare @pack_received    int
declare @pack_sent    int
declare @pack_errors    int
declare @connections    int
declare @total_read    int
declare @total_write    int
declare @total_errors    int

declare @oldcpu_busy     int    /* used to see if DataServer has been rebooted */
declare @interval    int
declare @mspertick    int    /* milliseconds per tick */


/*
**  Set @mspertick.  This is just used to make the numbers easier to handle
**  and avoid overflow.
*/
select @mspertick = convert(int, @@timeticks / 1000.0)

/*
**  Get current monitor values.
*/
select
    @now = getdate(),
    @cpu_busy = @@cpu_busy,
    @io_busy = @@io_busy,
    @idle = @@idle,
    @pack_received = @@pack_received,
    @pack_sent = @@pack_sent,
    @connections = @@connections,
    @pack_errors = @@packet_errors,
    @total_read = @@total_read,
    @total_write = @@total_write,
    @total_errors = @@total_errors

/*
**  Check to see if DataServer has been rebooted.  If it has then the
**  value of @@cpu_busy will be less than the value of spt_monitor.cpu_busy.
**  If it has update spt_monitor.
*/
select @oldcpu_busy = cpu_busy
    from master.dbo.spt_monitor
if @oldcpu_busy > @cpu_busy
begin
    update master.dbo.spt_monitor
        set
            lastrun = @now,
            cpu_busy = @cpu_busy,
            io_busy = @io_busy,
            idle = @idle,
            pack_received = @pack_received,
            pack_sent = @pack_sent,
            connections = @connections,
            pack_errors = @pack_errors,
            total_read = @total_read,
            total_write = @total_write,
            total_errors = @total_errors
end

/*
**  Now print out old and new monitor values.
*/
set nocount on
select @interval = datediff(ss, lastrun, @now)
    from master.dbo.spt_monitor
/* To prevent a divide by zero error when run for the first
** time after boot up
*/
if @interval = 0
    select @interval = 1
select last_run = lastrun, current_run = @now, seconds = @interval,
    cpu_busy_total = convert(int, ((@cpu_busy * @mspertick) / 1000)),
    cpu_busy_current = convert(int, (((@cpu_busy - cpu_busy)
        * @mspertick) / 1000)),
    cpu_busy_percentage = convert(int, ((((@cpu_busy - cpu_busy)
        * @mspertick) / 1000) * 100) / @interval),
    io_busy_total = convert(int, ((@io_busy * @mspertick) / 1000)),
    io_busy_current = convert(int, (((@io_busy - io_busy)
        * @mspertick) / 1000)),
    io_busy_percentage = convert(int, ((((@io_busy - io_busy)
        * @mspertick) / 1000) * 100) / @interval),
    idle_total = convert(int, ((convert(bigint,@idle) * @mspertick) / 1000)),
    idle_current = convert(int, (((@idle - idle)
        * @mspertick) / 1000)),
    idle_percentage = convert(int, ((((@idle - idle)
        * @mspertick) / 1000) * 100) / @interval),
    packets_received_total = @pack_received,
    packets_received_current = @pack_received - pack_received,
    packets_sent_total = @pack_sent,
    packets_sent_current = @pack_sent - pack_sent,
    packet_errors_total = @pack_errors,
    packet_errors_current = @pack_errors - pack_errors,
    total_read = @total_read,
    current_read = @total_read - total_read,
    total_write = @total_write,
    current_write =    @total_write - total_write,
    total_errors = @total_errors,
    current_errors = @total_errors - total_errors,
    connections_total = @connections,
    connections_current = @connections - connections
from master.dbo.spt_monitor

/*
**  Now update spt_monitor
*/
update master.dbo.spt_monitor
    set
        lastrun = @now,
        cpu_busy = @cpu_busy,
        io_busy = @io_busy,
        idle = @idle,
        pack_received = @pack_received,
        pack_sent = @pack_sent,
        connections = @connections,
        pack_errors = @pack_errors,
        total_read = @total_read,
        total_write = @total_write,
        total_errors = @total_errors

You can see that it uses a table named spt_monitor to hold last snapshot of the variables, when next time comes, use current variables to subtract the previous ones.

You can find the detail of the stored procedure here: http://technet.microsoft.com/en-gb/library/ms188912.aspx

How to show the information?

I uses the .NET 4.0 in-built Chart under System.Windows.Forms.DataVisualization. You can find samples here: http://archive.msdn.microsoft.com/mschart/Release/ProjectReleases.aspx?ReleaseId=4418

Analysis

Currently SQL Monitor can analyse disk/database/table/index space relationship and give possible suggestions.

How you did it?

First it finds out all databases, for each database, get size of all files (data, log), then use master.sys.xp_fixeddrives to find out free space for each disk. The it will get the total size of all database files and group them by disk that the file resides. Based on the ratio use defined in the options, SQL Monitor will decide whether a certain database need to shrink or truncate.

//database & disk free space
        var databases = GetDatabasesInfo();
        var files = new List<tuple<bool, />>();
        databases.AsEnumerable().ForEach(d =>
        {
            var database = GetDatabaseInfo(d["name"].ToString());
            database.AsEnumerable().ForEach(f =>
            {
                files.Add(new Tuple<bool, />(Convert.ToInt32(f["type"]) == 1, f["physical_name"].ToString(), Convert.ToInt64(Convert.ToDecimal(f["Size"]) / Size1K)));
            }
            );
        });
        var spaces = new Dictionary<string, />>();
        //MB free
        var driveSpaces = Query("EXEC master.sys.xp_fixeddrives");
        driveSpaces.AsEnumerable().ForEach(s =>
        {
            //could not use name but rather index, because the column name will change according to locale
            spaces.Add(s[0].ToString(), new KeyValue<long, />(Convert.ToInt64(s[1]), 0));
        });
        files.ForEach(f =>
        {
            //maybe some access issues
            try
            {
                var drive = f.Item2.Substring(0, 1);
                if (spaces.ContainsKey(drive))
                {
                    spaces[drive].Value += f.Item3;
                }
            }
            catch (Exception)
            {
                //mmmm.....what can we do, mate?
            }
        });
        spaces.ForEach(s =>
        {
            if (s.Value.Key < s.Value.Value / 100 * Settings.Instance.DatabaseDiskFreeSpaceRatio)
            {
                analysisResult.Add(new AnalysisResult { ResultType = AnalysisResultTypes.DiskFreeSpace, ObjectName = s.Key, ReferenceValue = s.Value.Key, CurrentValue = s.Value.Value, Factor = Settings.Instance.DatabaseDiskFreeSpaceRatio + SizePercentage });
            }
        });

        //database data file & log file space
        databases.AsEnumerable().ForEach(d =>
        {
            var name = d["name"].ToString();
            if (!systemDatabases.Contains(name))
            {
                var database = GetDatabaseInfo(name);
                var databaseSpace = new Dictionary<databasefiletypes, /> { { DatabaseFileTypes.Data, 0 }, { DatabaseFileTypes.Log, 0 } };
                database.AsEnumerable().ForEach(f =>
                {
                    var key = (DatabaseFileTypes)Convert.ToInt32(f["type"]);
                    databaseSpace[key] += Convert.ToInt64(Convert.ToDecimal(f["Size"]) / Size1K);
                }
                );
                bool? shrink = null;
                if (databaseSpace[DatabaseFileTypes.Log] > databaseSpace[DatabaseFileTypes.Data] / 100 * Settings.Instance.DatabaseDataLogSpaceRatio)
                    shrink = false;
                else
                {
                    var logSpaces = SQLHelper.Query("DBCC SQLPERF(LOGSPACE)", GetServerInfo(name));
                    var logSpace = logSpaces.Select(string.Format("[Database Name] = '{0}'", name));
                    if (logSpace.Length > 0)
                    {
                        var logSpacedUsed = Convert.ToDouble(logSpace[0]["Log Space Used (%)"]);
                        if (logSpacedUsed < Settings.Instance.DatabaseDataLogSpaceRatio)
                            shrink = true;
                    }
                }
                if (shrink != null)
                    analysisResult.Add(new AnalysisResult { ResultType = AnalysisResultTypes.DatabaseLogSpace, ObjectName = name, ReferenceValue = databaseSpace[DatabaseFileTypes.Log], CurrentValue = databaseSpace[DatabaseFileTypes.Data], Factor = Settings.Instance.DatabaseDataLogSpaceRatio + SizePercentage, Key = (bool)shrink ? 1 : 0 });
            }
        });

For table space, system stored procedure called sp_spaceused to get the space usage of a table:

var tables = GetObjects(KeyTables);
tables.AsEnumerable().ForEach(t =>
    {
        var name = t[KeyName].ToString();
        var space = Query(string.Format("EXEC sp_spaceused '{0}'", name), CurrentServerInfo);
        if (space.Rows.Count > 0)
        {
            var row = space.Rows[0];
            var dataSize = ToKB(row["data"]) / Size1K;
            var indexSize = ToKB(row["index_size"]) / Size1K;
            if (indexSize > dataSize / 100 * Settings.Instance.TableDataIndexSpaceRatio)
                analysisResult.Add(new AnalysisResult { ResultType = AnalysisResultTypes.TableIndexSpace, ObjectName = name, ReferenceValue = dataSize, CurrentValue = indexSize, Factor = Settings.Instance.DatabaseDataLogSpaceRatio + SizePercentage, Key = (int)TableIndexSpaceRules.DataIndexSpaceRatio });
        }
    });

Points of Interest

Well, I really learned a lot during the digging (sounds like a construnction workerBig Grin | :-D ) into SQL Server, the deeper I dig, the more I realize SQL Server is very complicated, and powerful: it can easily handle over 10 billion records in one table. However, in my daily database development, I found quite a few problems with SQL Server (even in SQL Server 2008 R2), especially when it try to yield resources to other worker, it just hangs there not doing anything. Thus whhy I develop SQL Monitor, trying to figure out what actually going on inside SQL Server.

Please, stay tuned, in the next version, will be more exciting, ambitous, hardcore and comprehensive features coming.

Finally, Happy New Year Smile | :)

History

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)

Share

About the Author

Huisheng Chen
Product Manager www.xnlab.com
Australia Australia
I was born in the south of China, started to write GWBASIC code since 1993 when I was 13 years old, with professional .net(c#) and vb, founder of www.xnlab.com

Now I am living in Sydney, Australia.

You may also be interested in...

Comments and Discussions

 
Questiondo you have any steps how to set this tool up? Pin
Member 1154878323-Mar-15 8:36
memberMember 1154878323-Mar-15 8:36 
QuestionExcellent Program Pin
Member 98779222-Mar-13 16:51
memberMember 98779222-Mar-13 16:51 
AnswerRe: Excellent Program Pin
Huisheng Chen3-Mar-13 22:41
memberHuisheng Chen3-Mar-13 22:41 
Generalexcellent Pin
mparvez8-Dec-12 4:34
membermparvez8-Dec-12 4:34 
GeneralMy vote of 5 Pin
Rajiv Ranjan(S/W Eng)11-Oct-12 2:14
memberRajiv Ranjan(S/W Eng)11-Oct-12 2:14 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:52
mvpKanasz Robert24-Sep-12 5:52 
GeneralMy vote of 5 Pin
Patrick Harris12-Jul-12 6:27
memberPatrick Harris12-Jul-12 6:27 
GeneralMy vote of 5 Pin
eric_yu28-Mar-12 4:00
membereric_yu28-Mar-12 4:00 
QuestionCan it be used with MySQL Pin
HamidYaseen22-Dec-11 10:59
memberHamidYaseen22-Dec-11 10:59 
AnswerRe: Can it be used with MySQL Pin
Unruled Boy22-Dec-11 15:07
memberUnruled Boy22-Dec-11 15:07 
AnswerRe: Can it be used with MySQL Pin
Patrick Harris13-Jul-12 16:00
memberPatrick Harris13-Jul-12 16:00 
GeneralRe: Can it be used with MySQL Pin
Huisheng Chen16-Jul-12 0:04
memberHuisheng Chen16-Jul-12 0:04 
GeneralRe: Can it be used with MySQL Pin
Patrick Harris30-Jul-12 7:38
memberPatrick Harris30-Jul-12 7:38 
GeneralMy vote of 5 Pin
HamidYaseen22-Dec-11 10:56
memberHamidYaseen22-Dec-11 10:56 
QuestionLogin Pin
Member 233816720-Dec-11 0:15
memberMember 233816720-Dec-11 0:15 
AnswerRe: Login Pin
Unruled Boy20-Dec-11 9:55
memberUnruled Boy20-Dec-11 9:55 
AnswerRe: Login Pin
Unruled Boy21-Dec-11 23:13
memberUnruled Boy21-Dec-11 23:13 
QuestionNice Pin
Liuxf5-Dec-11 14:33
memberLiuxf5-Dec-11 14:33 
AnswerRe: Nice Pin
Unruled Boy5-Dec-11 18:06
memberUnruled Boy5-Dec-11 18:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160525.2 | Last Updated 11 Jan 2012
Article Copyright 2011 by Huisheng Chen
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid