Performance Tuning - SQL Server 2005

Few best resources :
SQL Server 2005 Waits and Queues - Performance Tuning
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

Database Mirroring Best Practices and Performance Considerations
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

Why SQL Server 2005 has Performance degrades after upgrade
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/09/26/compilation-time-issues-in-oltp-applications-when-upgrading-to-sql-2005.aspx

A Special Republic Day

My first republic day as a Ex- Sailor. I am no more in Delhi, so I don’t feel that warm and tension. I always believe that if you want to know the significance and true spirit Republic Day it is Delhi the place to be. I still remember, those days when I and Leena (my wife) went first time to watch republic day parade in 2002. On that day we could not make it because of high security though I was in uniform. But in the next year we could make it. The whole function will be around 3 hrs and it is always colorful, vibrant and worth to watch. It is a trailer show of the diverse India. I got a pass of enclosure just opposite to VVIP’s daises. Now all those days are gone. I am not sure I would ever watch republic day parade again from India Gate. I really miss Delhi.

SQL Server 2005 DMVs for monitoring/tuning TEMPDB

In SQL Server 2005 , TempDB plays a significant role in overall SLQ server performance. In earlier versions it was difficult to monitor and troubleshoot the bottle neck.

In SQL Server 2005, you have number of DMVs to see which users are accessing the TEMPDB database , the internal object being used and version Stores size. The DMV are as follows

Sys.dm_db_file_space_usage
Sys.dm_db_session_file_usage
Sys.dm_db_task_space_usage

Note : In SQL Server 2005 these DMVs only applicable to Tempdb

Counters to Monitor Tempdb Row versioning usage

(a) SQL Server:Transaction Object : Version Generation Rate (KB/s)
(b) SQL Server:Transaction Object : Version cleanup Rate (KB/s)

Refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Hide SQL Server Instance in SQL Server 2000 and SQL Server 2005

To hide SQL Server 2005 Instance

In SQL Server 2005 you can hide a specific instance while other instance continue to expose. To hide a instance steps are as follows
1. Open SQL Server Configuration Manager from Prgramfile – Microsoft SQL Server 2005 – Configuration Tool
2. In SQL Server Configuration Manager, Expand SQL Server 2005 Network Configuration,
3. Right Click on “Protocol for ” and select properties
4. In Flag pane , you can find HideInstance with No set. Change No to Yes.
5. No need to restart the service, when you pressed OK, the new setting is immediately applied and new connection will not be able to connect to the instance using SQL Browser Service. You can still connect to the instance by specifiying protocol , Server name/IP address and port number or the named pipe directly

To hide an instance of SQL Server 2000:

1. Open the SQL Server Network Utility.
2. Select the instance that you want to hide. (This is only available on SQL Server 2000 instances). 3. Select TCP/IP from the list of enabled protocols.
4. Click Properties.
5. Select the Hide Server check box.
6. Click OK.
Note : Hide Server Option Cannot Be Used on Multiple Instances of SQL Server 2000
Refer :http://support.microsoft.com/kb/308091

Apply Command : New in SQL Server 2005

Apply command can be used with a table-valued user defied function (UDF) so that the UDF accepts a different parameter value for each corresponding row being processed by the main query. There are two ways to apply, apply command.
(a) Cross Apply :- It is bit confusing because it has a contradictory behavior from name. ie. it operates more like an inner join tan a cross join. The Cross Apply command will join data from the main query with any table-valued data sets from the UDF. If no data is returned from the UDF, then the row from the main query is also not returned. So, its just behave like inner join
(b) Outer Apply :- It will return all the rows from main query , if at all no data is returned from the UDF. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

It is well explained in BOL with perfect example. But thing is that you must know something like this exists in SQL Server 2005.

MCITP SQL 2005 DBA - Yet another mile stone in my SQL journey

I am a MCITP in SQL 2005 track from 6 Jan 07. The exam was no. 70-447 and I scored 918 out of 1000. I was little late to achieve this goal as per my schedule , but at the end of the day I am more than happy now. By getting certified one does not become a master , but it of course prove that he/she has a learning habit and if u have done in the true spirit u will have fare understanding about the tools and the new features of the product. So, I would always recommend to get certified as soon as possible. The exam was not easy as I thought. The question pattern has totally changed. This is my 9 th Microsoft certification, so I took this exam for granted. But when I saw the questions and the new case study section I was sweating. You have to read 4-5 papers of the Case study document and understand the language and the actual requirement. And also each case study section has its own time limit. So anybody going for 70-447 , must know this pattern change and the new case study section.

Total time – 210 min
Total Questions – 66
Pass marks - 700
Total - 1000

Blocked Process Threshold Option – A useful New Feature of SQL Server 2005

SQL Server 2005 introduces a new advanced system configuration (sp_configure) option called "blocked process threshold." We can use this option to proactively monitor blocking and deadlocking instances. You can use sp_configure to set the blocked process threshold to an integer value between 0 (the default) and 86400 (24 hours). If you have set the blocked process threshold configuration value to 15. Now if there is a blocking for over 15 seconds, SQL Server will raise an event that can be captured by using the SQL Server event notification mechanism to perform an action, and the event can also be seen in SQL Profiler. The event is raised every 15 seconds after that until the blocking is resolved.

SQL Server raises the BLOCKED_PROCESS_REPORT event when there is a blocking for 15 seconds and every 15 seconds after that until blocking is resolved. This event can be captured by using the event notification mechanism introduced in SQL Server 2005. The event notification mechanism captures the event and notifies a Service Broker service by placing a message in the queue


Reference : http://msdn2.microsoft.com/en-us/library/ms181150.aspx

Insallation of SQL Server 2005 from Local Folder

If you are installing SQL Server from CD media dumps which is copied into a local/network folder (in SQL Server 2005 Installation CDs are Two where as in DVD media only one DVD) , the folders should be named as Servers and Tools for CD1 and CD2 respectively. Otherwise , client components and SQL Server BOL will not be installed. There will be error if you are not named the folder as above mentioned.

Ref : http://msdn2.microsoft.com/en-us/library/ms144259.aspx

AUTO_UPDATE_STATISTICS_ASYNC Database Option in SQL Server 2005

SQL Server 2005 supports a new option called AUTO_UPDATE_STATISTICS_ASYNC which can be used to fine tune your database. Normally, when an executing query triggers an automatic updating of statistics through the query optimizer , the query has to wait until the statistics are updated before proceed further . in other words it can also called synchronous operation. But When AUTO_UPDATE_STATISTICS_ASYNC option is set to ON, queries do not wait for the statistics to be updated before compiling. Ie. it is an asynchronous process Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan

Note : AUTO_UPDATE_STATISTICS_ASYNC option has no effect if the AUTO_UPDATE_STATISTICS database option is OFF

Refer : http://msdn2.microsoft.com/en-us/library/ms190397.aspx

New year resolution – 2007

By the end of this year I would, no…no… I must... be a Microsoft SQL Server Developer/ DBA who possess good knowledge in Oracle also. I am planning to join for a Oracle course now. My next goal is to get certified in Oracle. And also I have learnt a lot in data warehousing area, thanks to my current project. By the end of this project , I am sure I will have a clear understanding of data warehousing process also. So hopes to get certified in Microsoft 70-446.. so more or less I have a tight schedule in this year and hopes I will be able to achieve my goals.