Performance analysis : Top-Down and Bottom-Up Approach

In this article we are going to see basic Performance Analysis approaches. I will be referring to Top-Down & Bottom-up approach. I will not compare them as both are used for analysis, I will only try to explain what are the basic steps and when to choose what type. This article mostly match for Java & DotNet application. You may use similar approaches for other platforms too.

Top-Down Analysis :

This is the most popular method. The idea is simple, performance monitoring application from top view. That means, client monitoring -> server monitoring in OS & Resource level, then app server-> then run time environment
And, when we get unusual behavior (or not expected or targeted), then profiling or instrument application. In this moment, we have found problems , and experiment with possible solution, choose the best one.
And then, we have to tune the system. Tune code with best solution, tune environment and tune run time.
Finally, we need to test for the impact. Typically, before starting an analysis, we should have some measurement data(or benchmarks) from performance test results. We need to retest those and compare with previous results. If that is significant, then it is done, if not, we need to get back to profiling and tuning application.

Here is a flow chart to show at a glance : Google drive link. Open with draw.io. 

When we use?
-> Application causing issues, we need optimize whole or a part of application.
-> Optimize application for given resources(CPU/Disk/Memory/IO/network)
-> Tune the system & application for best performance.
-> We have access to code change, now need to tune application for specific goal.(throughput, response time, longer service etc).
-> We need Root Cause Analysis for unexpected Performance Issues(OOM, Slowness, crashing in different level or sub-systems, unwanted application behavior primary suspected for performance, etc)

Bottom-Up Analysis :

This is another popular approach when we need to tune resource or platform (or hardware) for specific application. Let say, you have a java application , deployed. Now, bottom up analysis will allow you to analyze and find optimization scope for deployed system, hardware and resources. This is very common approach for application capacity planning, benchmarking for changed environments(migration). The key idea is, monitor application in specific environment and then tune environment(software+ hardware resources) that makes target application running at top performance.

Here is a flow chart to show at a glance : Google drive link. Open with draw.io. 

When we use?
-> When you need to increase performance but you cant change source code.

-> You need to optimize resources & environment for specific application( deployed environment)

-> You need to have benchmark and get to know resource usages as well as find possible area for tuning.

-> You need to optimize run time (JVM/CLR) for your application. You can see resource usages and tune as your app needs.

-> When you need capacity planning for your hardware which must run application in optimal way.

-> When you have optimized your application from code and there is not visible area to tune, you can use this technique to achieve some more. 

Please comment if you have any question.

Thanks.. :)

How to find memory leaks in application? General Ideas

Moved Here

How to get SQL Server instances from network?

In this article we are going to see how can we get SQL server instance shared in LAN(Loca Area Network). This is a technical code post. I am using VS 2010. And I will show how to do that. I have used Authentication mode for SQL Server(to keep it generalizing form)

So, let's break down the objective.
First, we have get the pc names which have sql server instances running.
Then we have to get the sql server instance names.
Then we have to get the Data Bases which are stored in that instance.
We must know the userName and password of the sql server instance to log in and access the database.
I have use List to store server & instance names. You may use array list or your own collection.
There are two data class(as data contract) SQLServer & Database which are basically property classes.

public class SQLServer
    {
        public string PCName { get; set; }
        public string InstanceName { get; set; }
        public string IP { get; set; }
        public string Version { get; set; }
        public bool IsClustered { get; set; }
    }

public class Database
    {
        public string Name { get; set; }
        public string Remarks { get; set; }
        public int size { get; set; }
 
    }

Then, I have used single Manager Class that handle the operation of locating the server and instances. In the manager class, GerServers() will search for instances and provide list of SQLServer objects(that means we are getting an array where each element contains information of a single instance) . I have used SqlDataSourceEnumerator.Instance.GetDataSources() method to get server information. Actually we are parsing table and getting information as object initiation(new). Main point is, when we get the instance information as data source, first element is host name, then instance name, then clustered property info, then version info.

 public List<SQLServer> GetServers()
        {
            DataTable dataSource = SqlDataSourceEnumerator.Instance.GetDataSources();
            List<SQLServer> servers = new List<SQLServer>();
            foreach (var row in dataSource.Rows)
            {
                DataRow dataRow = row as DataRow;
                string serverName = dataRow.ItemArray[0].ToString();
                string instanceName = dataRow.ItemArray[1].ToString();
                bool isClustered = dataRow.ItemArray[2].ToString() == "Yes";
                string version = dataRow.ItemArray[3].ToString();
                SQLServer myServer = new SQLServer() { InstanceName=instanceName,
                PCName=serverName,Version=version,IsClustered=isClustered};
                servers.Add(myServer);
            }
            return servers;
        }

And, I have used GetDBs method that takes a sql connection and provides list of database obects(an list of db info)

public List<Database> GetDBs(SqlConnection connection)
        {
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("sp_databases", connection);
                SqlDataReader reader = command.ExecuteReader();
                List<Database> dbs = new List<Database>();
 
                while (reader.Read())
                {
                    Database mydb = new Database()
                    {
                        Name = reader[0].ToString(),
                        Remarks = reader[2].ToString(),
                        size = (int)reader[1]
                    };
                    dbs.Add(mydb);
                }
                return dbs;
            }
            catch(Exception ex)
            {
                throw ex;                
                
            } 
            finally
            {        
                connection.Close();
            }            
        } 

These are main 2 method that works. There are several helper methods used for string management. Full project is very simple. You may use in your project . Here is the project code.

Note : To keep the sql server in LAN Share,  go to SQL Server configuration Manager ->

 
then , from left panel select SQL Server Network Configuration , -> select instance -> select TCP/IP from right panel and double click. From up coming properties window, select enable = Yes.
 

Now your sql server instance is accessible from LAN Share. We need this to test our previous code. And, be careful that your instance is tuning(from windows service).

Thanks...:)