In Applications where SQL Server is used as a back-end for data storage and manipulation purposes, there are various reasons for SQL Queries or Transact SQL Statements or Code running slow when the Application is executed in a Production Server Environment. Some of the reasons for slow running queries and updates are:

• Low network speed or slow network communication.
• Inadequate memory in the server computer or not enough memory available for SQL Server.
• Improper indexing in database.
• Lack of usage of database statistics.
• Lack of proper database partitioning.

To facilitate development of applications with reliable and faster performance, there are some basic SQL Server Tools and Techniques to enhance the Query and Transact SQL Performance.

Tools like Performance Monitor and SQL Server Profiler can be used to monitor the Performance of SQL Server and non-SQL related components. Some of the SQL Server related components and events that are monitored are Events related to SQL Server Service Broker Components, SQL Server service Components, Database Operation Events, Cursor Events, Transaction Events , Replication Events etc. Other non-SQL components include System Memory, Processor, Other Application and System Processes, System Security etc.

Few techniques to determine slow running queries and Transact SQL statement include using dynamic management views - sys.dm_exec_query_stats and sys.dm_exec_request to find queries that collectively consume a large number of resources and analyzing query performance by producing a showplan.

Techniques for improving query performance include prioritizing SQL Server over other operating system processes, Choosing and Creating the Right indexes and use query optimization techniques to improve individual query performance.

Indexing of Tables should be used very judiciously by choosing the most critical queries and determining the index columns based on their usage frequency. Also judicious usage is recommended considering that there are trade-offs involved with this technique in the sense that your SELECT queries might run faster but your INSERT, UPDATE and DELETE operations will slow down. Some useful ways of determining creation of indexes are:

• Create selective indexes on columns that are used in the WHERE clause of queries.
• Create multiple column indexes which are used in filter expressions and also make sure that the most selected column is left most in the indexed key.
• Indexing small tables will deteriorate performance.

Following individual query optimization techniques help to improve the overall performance of your queries as well as the application:

• Use 'WHERE' clause to limit the number of rows wherever possible.
• Use 'UNION ALL' instead of 'UNION' where no duplicate rows are returned in the result sets of the queries which are being merged. However if there are duplicate rows and they need to be eliminated then use 'UNION'.
• Avoid using 'DISTINCT' clause where duplicate rows are not returned as it leads to SQL Server using more resources thereby causing performance bottlenecks in the long run.
• Avoid using Column or Row constraints in Tables as they degrade Insert and Update query performance.
• Avoid using function or calculation on the column in the where clause.

Other techniques include using database-wide statistics options, partitioning tables, views and indexes when large volume of data is involved and updating statistics more frequently

Posted by Krishnaraj Valthajamana at 12:05 PM | Permalink | Comments (0) | TrackBacks (0)

Unified Communication platform gives solution for an organization's communication and collaboration need. With the release of Lync Server 2010 Microsoft is taking collaboration to the next higher level;. ie. from LAN to over the cloud. Microsoft has added many capabilities with Lync when we compare it with the last release of Office Communication Server (OCS).

Microsoft OCS has been there around for more than 8 years starting with OCS 2003, the last release being OCS 2007 R2. OCS 2007 R2 delivered a unified platform that worked along with the organization's existing messaging and telephony infrastructure.

The main features of OCS 2007 R2 were:

1. Call Delegation, Team Call, Group Chat
2. Desktop Sharing, Extending Voice and Conferencing
3. Enhanced Media, Simplified Management

This along with unified communication basic functionalities including instant messaging, presence notifications, Audio/ Video messaging and integration with office suite of products made OCS the most favorable Unified communication platform. Also OCS 2007 SDK gave us immense capability to develop applications having communicator capabilities.

Lync 2010 is the next generation unified communication platform from Microsoft. Lync 2010 brings in a new experience with the below additions

1. The Lync client application has an easy-to-use interface that is consistent across PC, browser and mobile device.
2. Lync 2010 extensibility features that gives you the ability to embed communicator UI elements in your application and bring the communicator capabilities in the application.
3. There is the capability to have role based access control in Lync, with Lync defining 11 predefined roles for most of the administrative tasks.
4. The unified Microsoft Lync 2010 client has a simplified interface to provide access to enterprise needs for communication and collaboration.

Lync Online vs Lync On-Premises

Microsoft makes unified communication easier and more reliable with Lync Online, Lync On-premises and the federated communication feature between the two. Lync online is the communication service made available in the cloud. This transforms interactions to the online mode while bringing down the infrastructure cost. Lync Online opens up the possibility to connect thorough IM to external organizations without requiring VPN.

Lync On-premise also brings in the same set of features with it being deployed on-premise that is within the organization. What this also means is the infrastructure maintenance responsibility also lies with the organization.  There is Lync Federation with Lync Online and Lync Premise available for federated communication possibility.

The choice between Lync On-premise and Lync online would lie in the organization need for online collaboration, security policies and their take on cost towards infrastructure.

Lync 2010 SDK

Lync 2010 SDK can be used by developers to build communicator capability in their application. The SDK provides communicator UI elements that can be embedded in the applications. To hide the Lync client UI and develop a custom communicator client, Lync client installation has to be done in UI Suppression mode.

More on Lync SDK is available is available here.

New Features in Lync 2010 Server

Unified Experience

1. Lync 2010 client gives a more enhanced experience in accessing presence notifications, instant messaging and audio, video and web conferencing features.
2. Mobile workforce can now have access to all these capabilities via PCs, browsers and mobile phones.
3. Tight integration with Office applications. Can access all capabilities of unified communication from any of the Microsoft Office applications.

Conferencing

1. Improvements to Desktop sharing, application sharing, PowerPoint upload and white boarding makes collaboration much easier..
2. Scheduling, joining meetings and placing attendees in virtual lobby makes conferring more secure and easier.
3. There are enhanced calling features for users and also management features for administrators.

Deployment and Management Tools

1. The administration management is consistent with exchange server and active directory.
2. The role-based access control (RBAC) has the capability to have both predefined roles and customer-defined roles. This gives improved security and increased administrative productivity.
3. Server virtualization of most of the Lync server roles is possible. All IM capabilities including remote access with federation is possible in a virtualized deployment.
4. Network, hardware and software can be monitored in real-time by administrators to analyze any degradation in the performance.

The following were the benefits pointed out by Forrester's survey on Lync 2010 deployment:

1. PBX telephone systems could be replaced with Lync Server 2010 software.
2. Significant cost reductions on Web- and teleconferencing charges.
3. Customer experience with enterprise application would get better by having embedded communicator UI elements using the open and documented API's provided by Lync 2010.
4. Using federation unified communication could be extended beyond the enterprise or the organization.
5. Mobile workforce would benefit much from Lync's on-premise/ online federation service.

In summary the next generation unified communication platform Lync is going to change the way business and organizations communicate and collaborate.

Read more on Lync and OCS here and here.

Posted by Seena Latha Sasidharan at 6:07 AM | Permalink | Comments (0) | TrackBacks (0)