General Approach To SQL Server Performance Tuning

General Approach to Performance Tuning

Examine the Nature of Software/Application which uses the SQL Server.

Gather the average number of users/sessions during peak hours/non-peak hours.

Gather/Review the existing hardware
a. RAM
b. CPU
c. Hard Disk
d. Disk level
e. Physical server or virtual server

Gather information on various network bandwidth/latency/limitations

Gather the exiting SQL Server Maintenance Plans
a. Backup Strategy 
b. Index Rebuild
c. Index Reorganise
d. Update Statistics

Gather top expensive queries/long running queries over period of time

Gather all missing/unused indexes

Get the database design, so as to check whether the queries coming in are dynamic or Ad Hoc queries or using stored procedures.

Once all this is done

Tune the Software/Application by increasing the heap memory/code modification, avoid ad hoc queries, use stored procedure etc.

Try if you can upgrade the existing hardware to maximum level affordable

Build a clean backup strategy so as to have full backup outside business hours

Use Perfmon to capture DISK IO, Index Information, Page Latency, User connections, Blocked process, lock waits, memory, CPU etc

Review and rebuild the existing maintenance plans to achieve more performance
Check for blockings

Review the expensive queries, study the query plan and improve the same

RUN Database Tuning Advisor on Poor performance/long running/Expensive queries, apply the necessary changes, and do not go with all the things suggested.

At this moment of time you should be able to have fair amount of increase in performance and decision on further troubleshooting and performance tuning.

Edition Upgrade SQL Server Step by Step Instruction

I was wondering how can I upgrade my SQL Server Edition. Say I have a SQL Server Standard edition installed on one of my Server. Now due to some business needs, I need to upgrade my SQL Server Standard Edition to SQL Server BI Edition. I was able to achieve this and have documented these step by step instruction below. This is for Microsoft SQL Server Edition Upgrade from Standard Edition Installation to BI Edition Installation. This edition Upgrade Does not need any Re-Boot of server until there is any dependency.

Note: Upgrade can be done only to Higher Edition. Ex, from Evaluation to Standard or Standard to Enterprise etc.

Step1: Open the SQL Server Installation Media of BI Edition and run the setup.exe as below. SQL Server Setup processes.

Step2: SQL Server Installation Center appears, click on Maintenance Tab on Left and Click on Edition Upgrade Option as shown below.

Step3: SQL Server Setup starts and setup support rules are run, once the operation is completed and the setup rules are passed, if any of them fail, check verify and re-run the rules and  click ok as shown below.

Step4: SQL Server Setup support rules for Edition Upgrade are run, once the operation is completed and the setup rules are passed, if any of them fail, check verify and re-run the rules and  click Next to continue as shown below.

Step5: Product Key window appears. Enter the BI Edition product key(sometimes this get populated automatically if you are taking this from some iso) and click Next as shown below.

Step6: Accept the license terms and click next as shown below.

Step7: Select the instance which you want to upgrade(Only if multiple instances are installed on the machine else MSSQLSERVER default instance will appear) and click Next as shown below.

Step8: Edition Upgrade Rules are run, once the operation is completed and Edition upgrade rules are passed, if any of them fail, check verify and re-run the rules and  click Next as shown below.

Step9: Your installation is now ready to upgrade. Review the changes to your standard edition. Make a Note of Path for configuration File Path. This file can be used to automate the things in future if you want to run bulk upgrade. Click Next to Upgrade as shown below.

Step10: Once the upgrade is completed successfully review the information to see all the feature status is succeeded. Check the summary log for any more information. Click Close to exit Installation.

Step11. Connect to SQL Server instance which you have just upgraded and run the query “Select SERVERPROPERTY(‘Edition’) to check you get the below output just to verify your Instance has been upgraded to BI edition.

That’s it. Done. You have now successfully upgraded your Existing SQL Server Standard Edition Installation to SQL Server BI Edition Installation.

Technorati Tags: SQL Server Edition Upgrade,Upgrade,Edition Upgrade,SQL Server Upgrade

T-SQL to get Database backup details

Below is a simple yet powerful query to get the details about your database backup. This query can be customised as per your needs as this is simple join to two tables in MSDB Database.

  use msdb
  go
  select bus.name,bus.[user_name],bus.backup_finish_date,
  ((((bus.backup_size)/1024)/1024)/1024) as 'backup_size in GB',bus.database_name,
  CASE
  WHEN bus.[type] = 'D' Then 'Full Backup'
  WHEN bus.[type] = 'I' Then 'Differential Database Backup' 
  WHEN bus.[type] = 'L' Then 'Log Backup' 
  WHEN bus.[type] = 'F' Then 'File or filegroup Backup' 
  WHEN bus.[type] = 'G' Then 'Differential file Backup' 
  WHEN bus.[type] = 'P' Then 'Partial Backup'
  WHEN bus.[type] = 'Q' Then 'Differential partial Backup'
  End AS 'backup_Type',
  bumf.physical_device_name,
  CASE 
  WHEN bumf.device_type = '2' Then 'Disk'
  WHEN bumf.device_type = '5' Then 'Tape'
  WHEN bumf.device_type = '7' Then 'Virtual device'
  WHEN bumf.device_type = '105' Then 'permanent backup device'
  End AS 'Backup_Device_Type'
  from backupset bus
  left outer Join backupmediafamily bumf
  on bus.media_set_id = bumf.media_set_id
  where bus.database_name = 'YourDatabaseName'
  order by bus.backup_finish_date desc

Technorati Tags: Database backup,backup,backup details

The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.[]

There can be many reasons for a Login Failure to SQL Server Instance.

Out of this you may get one as “The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.[]”

This error may occur when The SQL Server computer was unable to process the client login packet. Inside the square braces, will be the IP of the client from which they are trying to login.

When the user is a member of many groups or has many policies, the token may grow larger than normal to list them all. If the token grows larger than the MaxTokenSize value of the server computer, the client fails to connect with a General Network Error (GNE) and error 17832 can occur. This problem may affect only some users: users with many groups or policies.

To Resolve this the MaxTokenSize value of the server computer needs to be increased.

To change the MaxTokenSize on the server computer, You need to have administrator rights on the server to perform this action.

1. On the Start menu, click Run.

2. Type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters.

4. Right-click MaxTokenSize, and then click Modify.

5. In the Value data box type the desired MaxTokenSize value.

1. Note: Hexadecimal value ffff (decimal value 65535) is the maximum recommended token size. Providing this value would probably solve the problem, but could have negative computer-wide effects with regard to performance. We recommend that you establish the minimum MaxTokenSize value that allows for the largest token of any user in your organization and enter that value.

6. Click OK.

7. Close Registry Editor.

8. Restart the computer.

Changing registry value incorrectly may hamper the system. Please backup the registry and have this done by a System Administrator to be sure what is done.

Technorati Tags: MaxTokenSize,login packet,Login Error