Category Archives: SQL Server

Performance Tuning in SQL Server

Posted on by
Reply

One of the frequently asked questions in forums, interviews and during general discussions, is performance tuning. In fact, a friend of mine, (who knows a little bit of SQL Server) when speaking about my blogs, asked me why I had not written any blog on performance tuning. I gave a pause and said, do you know that heavy usage of cursors result in poor performance, Database partitioning results in slightly better performance and Database Snapshots cause a performance overhead? Then I continued, though you see I had a blog on those topics,  it is not noticed that they are related to performance of the SQL Server.

The point is, there are many actions or the usage of certain features which directly or indirectly affect the performance of a SQL Server. I would like to post blogs on performance tuning SQL Server, it may be Query Tuning such as rewriting the query to perform better or looking at Indexes and ensure that there are proper Indexes that help the query. It can be related to locking / blocking happening on the database or improper settings of the database options.

There are certain other things to look at when looking to tune a SQL Server apart from those mentioned above. An improper placement of the database files can lead a performance degradation or excessive usage of cursors. There can be system bottlenecks such as Memory, Processor, Disk or Network that is the prime cause for a system slowness. 

Many of these shall be addressed individually in the forth-coming blogs.

— Bru Medishetty

Side by Side Installation of SQL Server

Posted on by
Reply

Many SQL Server users (starters and intermediate level users) have a common doubt about the possibilities of installing multiple SQL Server instances on a same physical server. The answer is a cautious yes; the reason for being cautious is, not being sure about the behavior of the application(s) using the previous version of SQL Server.  Such a setup is called Side-by-Side installation. It is possible to have multiple Versions of SQL Server Instances on the same machine and also multiple editions of same version of SQL Server.

In the picture below you see that on the same machine, 4 SQL Server instances have been installed and 3 of them are SQL Server 2005 and the other being SQL Server 2008. Each one of the Instances is different from the other.

I used multi-server querying capabilities in SQL Server 2008 to find the information of all the servers in a single query. Coming to Side by Side Installations, it must be noted that this might be required when performing an upgrade on the same machine as the current one. This requires no additional hardware for setting up another system, but on the other hand, there is a risk factor in this, if the installation of the newer instances is unsuccessful and the system becomes unstable or the newer version of executables have known / unknown issues with respect to the working applications.

The decision to go for a Side by Side Installation or a new installation on a seperate system, varies. If it is being performed on a test server for some testing additional functionalities or features it would be a worth a try.  Whereas performing this on a system which cannot have downtime, it is not worth a risk to take.

–Bru Medishetty 

Querying Multiple Servers in SQL Server 2008

Posted on by
1

SQL Server 2008 provides the functionality of querying multiple Servers from a single query window. This is helpful for tasks like retrieving server information such as Version, Edition, Instance Name, logins available across each instance, databases on the servers etc.

In order to query multiple servers the only prerequisite is that the servers need to be registered prior to run the query and of course, there should be a valid login to connect to those Server. The SQL Servers can be of other version such as SQL 2000 or 2005. In this example I have two SQL Server 2005 instances and one SQL Server 2008 instance all of them are installed on a single Machine.

For the purpose of this article, I assume you know how to register a SQL Server in the Local Server Groups. Open registered servers from View menu or using keyboard short-cut Ctrl + Alt+ G (picture below).

 

Right click on the server group, from the pop up menu choose New Query as shown in the picture below.

 A new query editor is opened which has is similar to a normal new query editor. The only difference you find is the status bar down at the the bottom of the query editor. The picture below displays the status bar and we see the difference with a usual query editor status bar. This status bar does not a single instance name rather, displays the Server Group Name, also SPID information is not displayed.

I am running a simple query to retrieve the list of the user Databases on all the instances in the server group. The query results is displayed along with the query. The Database names are displayed along with SQL Instance name.

Those of us who would like to find the backup information of the critical databases or to find the SQL Agent Jobs that failed across several instance, that can all be done from a single query in SQL Server 2008.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru that let’s you know when there is a new blog post.

–Bru Medishetty