Israeli SQL User Group September 2009

I spoke about Locking and Concurrency in SQL Server.

The session focused on the different lock types available with SQL Server and how they affect database concurrency.

We looked at the available Concurrency Models supported by SQL Server, Isolation Levels, Lock Modes, Conversion Locks, Lock Duration, Key Locks, Lock Starvation, Lock Escalation, Table Hints and how we can resolve Blocking issues and improve Concurrency which in terms improves our applications response time.

The presentation includes many examples in T-SQL that you can try out for your self to see with your own eyes which helps remembering better.

To download the presentation:
http://www.sqlserverutilities.com/download/Locking-Concurency-ppt.zip


-Yaniv
====================================================

SQLScripter version 2.0.0.0 is now available for download

After three years being widely distributed as a free software SQLScripter now requires a license for the fully featured Standard Edition while the Freeware Edition is still available and remains free but gets limited in features.


To go to the Product Details page:
http://www.sqlserverutilities.com/SQL-Server-Utilities-SQLScripter-product-details.htm

For additional details and a comparable table see the ReadMe file

To go to the download page:
http://sqlserverutilities.com/SQL-Server-Utilities-SQLScripter-download.htm


-Yaniv
============================================

BlocksTracer is now available for download

I would like to share with you BlocksTracer - a utility I recently developed.
BlocksTracer is a Monitoring (and diagnostic) tool helping to troubleshoot blocking and concurrency issues.

This tool monitors and captures SQL Server’s blocking locks that occur inside the database engine and provides an insight to this crucial information at real time so that when a blocking event occurs you get to know about it immediately on the spot.

In addition to real time notification all related data such as the blocking statement, the statement blocked, the blocking host, the host being blocked, program, database, duration of the block…. and more, are kept in a database allowing you to further investigate latter on. You can also perform various queries against this important data which will teach you a lot about the concurrency your database(s) provides. Armed with this information you now know what areas/ statements require a modification in order to improve concurrency which in terms will improve applications response time.

Running BlocksTracer on a production environment provides a central repository holding every blocking issue that occurred on any one of the servers being monitored.

To read more go to: http://www.sqlserverutilities.com

To download the Freeware version: http://sqlserverutilities.com/download/BlocksTracer.msi


-Yaniv

Israeli SQL Users Group May 2009

I spoke about SQL Trace and Profiler.

I talked about the differences between Server Side Tracing and Profiler and helped to clarify some mists and confusion related to both.

In addition I presented a practical scenario implemented using SQL Trace which traps exceptions that occur in the database engine along with the statement that caused the exception. To download the code: http://blogs.microsoft.co.il/blogs/yaniv_etrogi/TraceErrors.zip

And finally I presented a .NET utility I wrote that subscribes to SQL Server and listens to events. The utility reports of blocking locks and enables to receive notifications at real-time as well as get statistical reports.

To download the presentation:
http://blogs.microsoft.co.il/blogs/yaniv_etrogi/Tracing_SQL_Server_2005.zip


To leave a comment:
http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/05/05/israeli-sql-users-group-may-2009.aspx

Replication Performance and the @status parameter in sp_addarticle

When replicating data over a WAN using a Push Subscription in Transactional Replication there is one parameter related to the article’s properties that has a significant impact on the transactions delivery rate, this is the @status parameter defined with sp_addarticle.

The tests I have done (see bellow table) show that if your replication is configured with the @status parameter set to 8 you should gain a noticeable improvement in performance by setting a value of 16 or 24 instead.

To modify the article’s property use sp_changearticle and to to view the article’s properties use sp_helparticle

This is a copy of the @status in sp_addarticle from BOL (Books on Line)

[ @status=] status

Specifies if the article is active and additional options for how changes are propagated. status is tinyint, and can be the | (Bitwise OR) product of one or more of these values.

Value Description

1 Article is active.

8 Includes the column name in INSERT statements.

16 (default) Uses parameterized statements.

24 Includes the column name in INSERT statements and uses parameterized statements.

64  Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For example, an active article using parameterized statements would have a value of 17 in this column. A value of 0 means that the article is inactive and no additional properties are defined.

The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2005 x64 Enterprise Edition sp2 build 3282 (CU9) on top of VMware virtual machines running Windows Server 2008 Build 6001 Service Pack 1.

Publisher	Subscriber	Type	Status	Transactions	Commands	Sec/Delivered	Trans/Sec
2005	2005	Push	8	20000	20000	Too slow - stopped after 20 minutes	NA
2005	2005	Push	16	20000	20000	215	93
2005	2005	Push	25	20000	20000	210	95

 

To leave a comment: http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/30/replication-performance-and-the-status-parameter-in-sp-addarticle.aspx

-Yaniv
===================================

Boost up replication performance with SQL Server 2008

A couple of months a go I came a cross a Microsoft white paper article http://msdn.microsoft.com/en-us/library/dd263442.aspx that talks about a performance improvement that can be gained by using SQL Server 2008 on top of Windows Server 2008 when replicating data over a WAN (Wide Area Network).

The article talks about an improvement done in Windows Server 2008 to the TCP stack. SQL Server 2008 as an application benefits from these changes in the communication package exposed by the operating system.

The boost in performance is mainly derived from the fact that the TCP Receive Window is now defined dynamically as apposed to a fixed value in earlier versions.

To take advantage of this improvement you got to have SQL Server 2008 on top of Windows Server 2008. No other SQL/OS combination will yield the desired results.

I say it is about time and better latter than never. Think about it, a communication channel established on a WAN is necessarily different in nature and characteristic than a communication channel established on a LAN (Local Area Network). So having the TCP Window Size being the same one value across a given server in operating systems prior to srv 2008 does not make sense. Further more, in my experience no one ever dears touch or tune it as the basic assumption always was that touching this value you win at some connections and loose at others. So there is a real trade off here that has to be dealt with extra care.

Being in a position where I manage 5 data centers around the globe I found that article interesting and relevant enough to set up my own environment and perform some tests.

The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2008 x64 Enterprise Edition (2008 RTM 10.0.1600.22) and SQL Server 2005 sp2 build 3282 (CU6) x64 with similar HW on top of VMware virtual machines running Windows Server 2008 Build 6001 Service Pack 1.

The test results point out some important facts:

Push Replication in sql 2008 becomes as fast as Pull in sql 2005

Pull Replication in sql 2008 performs significantly faster than Pull in sql 2005

Version	Type	Transactions	Commands	Sec Delivered	Trans/Sec
2005	Push	10000	10000	110	91
2005	Pull	10000	10000	54	185
2008	Push	10000	10000	57	175
2008	Pull	10000	10000	19	526

If you like to leave a comment to this post pls do so at:

http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/09/boost-up-replication-performance-with-sql-server-2008.aspx

-Yaniv

============================================

SQLScripter - new version available for download

SQLScripter Version 1.0.07 is now available for download.

This is a small version that mostly fixes minor issues.

New features:
SQL Server 2008 compatibleAdded the passwords hashed to the Logins script (SQL 2005 only)
Add the possibility to supply a list of database names separated by a semi column to the Database parameter
Add the UG switch

Fixes:Fixed a problem that the zip file was corrupted


Download:
http://www.valinor.co.il/tools-sqlscripter.asp


To leave a comment please follow this link:
http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/05/sqlscripter-new-version-available-for-download.aspx


-Yaniv
============================================

Pull Replication vs. Push Replication Part-II

Here is what I learned while working on an MSDN case with Microsoft in order to troubleshoot a serious degrade in performance that we faced following an upgrade from SQL Server 2000 to SQL Server 2005.

The distribution agent goes through the following steps to "move" a piece of data change from the distribution DB to a subscriber.

(1) querying the data change from the distribution DB
(2) moving the data change from distributor side to the subscriber side
(3) applying the data change to the subscriber DB

Step (1) is quite fast. Step (2) is slow in WAN. Step (3) is not fast. The distribution agent has two buffers.

In case of a push subscription, the distribution agent has two parallel tasks: (I) doing (1); (II) doing (2) and (3). At step (1), the data change is stored into a buffer of the distribution agent located at the distributor side.

In case of a pull subscription, the distribution agent has two parallel tasks: (I) doing (1) and (2); (II) doing (3). At step (2), the data change is stored into a buffer of the distribution agent located at the subscriber side.

We know that (2) and (3) are relatively slow. They are parallelized in a pull subscription, but serialized in a push subscription. So generally speaking, a pull subscription can perform better than a push subscription when it comes to WAN (Wide Area Network).


To leave a comment pls follow this link:
http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/03/pull-replication-vs-push-replication-part-ii.aspx

-Yaniv
============================================

Pull Replication vs. Push Replication

Did you know that Pull replication performs much better than Push?

When replicating data over a WAN (Wide Area Network) using Transactional Replication you can improve the transactions delivery rate just by having the Distribution Agent located at the Subscriber (Pull) instead of having the Distribution Agent located at the Distributor (Push).

In an upcoming post I will talk more about that.

The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2005 sp2 build 3282 (CU6) x64 with similar HW.

Type	Status	Transactions	Commands	Sec Delivered	Trans/Sec
Push	17	40000	40000	430	93
Pull	17	40000	40000	205	195

To leave a comment pls follow this link: http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/02/25/pull-replication-vs-push-replication.aspx

-Yaniv
=================================================

Detecting None Pooled Connections (Logins)

Some time ago while working on a problem that SQL Server reached a very high number of User Connections I found out another useful capability that the SQL Trace technology exposes. This is the ability to determine if a given connection is a Pooled Connection or not. Remember that in a "Pooled Environment", that is an environment that uses Connection Pooling, a single connection services more than one user and vise versa, every user browsing your site can be using more than one connection.

To find out at the application's side if all connections to SQL Server are correctly implemented and do "Pool" their connections can be time consuming and also if you are not well familiar with the application(s) it is difficult to be certain that you have covered all spots.
So tackling this issue at one focal point, at the server side, has a clear advantage and makes sense.

Fortunately, you can Trace or Profile your server and easily get to find out those connections that are not correctly implemented at the application and therefore do not make use of Connection Pooling thus establishing a None Pooled Connection to SQL Server.

The above applies to SQL Server 2005 and SQL Server 2008. In those versions when a Login Event is fired the EventSubClass column gets populated with a value of 1 or 2 for a None Pooled Connection or a Pooled Connection respectively.

This behavior does not take place in SQL Server 2000 even though both exists - the Login Event and the EvenSubClass column.

Not all events populate the EventSubClass column but at times it can be valuable as I found in this case.


I have attached the NonePooledLogins.zip file to the post which includes a Profiler template file that can be imported (File --> Templates --> Import Template) into Profiler. The template defines the event /columns combination and the filtering.
In addition the zip file contains the NonePooledLogins.sql script file that creates the same trace but only at the server side (known as SQL Trace) while not using the Profiler application.
=======================================================

My very first blog post - SQLScripter

Technorati Tags: sql,scripts

This is my very first blog I ever post.

I would like to dedicate this post to all my SQLScripter users and let you know that I very much appreciate all the emails, feedback and ideas you have been sharing with me. Thank you!

In the near by future I intend to add more of the requests I have received into an up coming release. Will keep you posted...

For readers who are not familiar with this utility I can say in brief that SQLScripter is a free tool that automates the process of schema generation.

SQLScripter on SQL Server Magazine:

http://www.sqlmag.com/Article/ArticleID/100140/sql_server_100140.html

SQLScripter direct download:

http://www.valinor.co.il/tools-sqlscripter.asp

-Yaniv