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
============================================