Learning SQL Server Performance Tuning – Part One

I wrote a blog in January 2010, called Performance Tuning in SQL Server. It was kind of introductory non-technical blog. (If you have time I would recommend you read it, would not take much time to digest it). In this short blog we will learn various steps involved in SQL Server performance tuning.

When dealing with performance tuning SQL Server, we can broadly classify it into 2 major areas; Physical Server related and SQL Server related. I will explain what does these areas consists of in more detail shortly, but remember that both these areas needs to be addressed in order to reach the optimum performance from the SQL Server Databases.

Tuning physical server involves tuning those underlying hardware components that define the physical server on which the SQL Server Instance is running OR SQL Server needs to be installed on. Primary hardware resources that needs to be monitored and tuned are Memory, Processors, Disk Drives and Network. 

SQL Server tuning invloves those that can be configured at the Instance and database level in SQL Server and more importantly involves in T-SQL query tuning.

In the next part of this series we shall look more details with respect to both these areas.

Note: Links to other parts in the series will be included here as and when they are published.

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

– Bru Medishetty

Changing SQL Server Authentication Mode

This article explains the procedure, how to change the authentication mode in SQL Server.

Authentication mode of a SQL Server instance is the deciding factor how the SQL Server authenticates the users and logins. SQL Server supports 2 authentication modes: Windows Authentication Mode & SQL Server and Windows Authentication Mode. There are advantages and dis-advantages in choosing these authentication modes, which will be covered in a seperate blog later. Let’s take a look at the steps to change the SQL Server Authentication Mode.

We start by right clicking on the SQL Server instance and choose Properties from the pop-up menu, as shown in the below image.

Then, SQL Server properties window is displayed. (as shown below).

Choose Security page in left pane of the dialog box. Now in the right side pane, in the top section (image below) you have the Server authentication section under that the 2 authentication modes are listed as an options, (at any point onnly one of them can be selected). Choose the appropriate authentication mode you would like the SQL Server set to and click OK. 

The next step is to restart the SQL Server.

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

– Bru Medishetty

How to Troubleshoot SQL Server Connection Problems

In this blog we shall take a look at the steps to troubleshoot connection problems in SQL Sever. We need to keep in mind, there are lot of things that might be part of such an issue and also we need to consider which user or what application is reporting this connection problems. The exact cause of the connection failure can only be troubleshooted by testing and trying multiple remedial steps, eliminate that step and keep moving forward.

  • Is SQL Server currently running or SQL Server accepting new connections?
    • Make sure that SQL Server on that machine is currently running, before making any other attempts / trials. SQL Server status should be checked that it is not stopped  or paused ( of course the error message indicates if the SQL Server is not accepting any new connections). Either way see that SQL Server is running currently. Start SQL Server if needed from Configuration Manager or Services etc.
  • Are the required Protocols Enabled?
    • Ensure that all required protocols are enabled on the SQL Server. There is nothing wrong with enabling all protocols, but a word of caution about the VIA protocol, it should be disabled on most of the systems (depends on Server Hardware) and should be enabled only upon verifying with the Hardware Vendor.
  • Check the SQL Server Authentication Mode.
    • This step is also important, any connection coming to SQL Server either has to be a Windows User or a SQL Server user. If you are trying to connect using a SQL Server login, make sure the authentication mode on the SQL is Mixed Mode, else the login attemps will fail and you will not be able to connect to SQL Server.
  • Is the User / Login status active or enabled?
    • Make sure you are connecting to the SQL Server using an account which is not disabled. If a different user complains about connection issues (different credentials), make sure that account is not disabled in SQL Server (if SQL Login) or the Windows Active Directory account is not expired / locked.
  • Check the Firewall settings.
    • Make sure that the firewall is not blocking the port on which SQL Server listens. 
  • Check the SQL connection information.
    •  You need to check if the SQL Server being referred in the connection string is a default instance or a named instance. If the instance is a named instance, you will need to ensure that the connection string includes the instance information also, i.e, it has to be something like the IP Address,Port # such as,3456 or the Servername\Instance such as PhysicalServer\InstanceName.

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

– Bru Medishetty