Category Archives: SQL 2008 R2

A SQL Read with NOLOCK, Causes a ALTER Table DDL statement to be Blocked or to Fail

If you are using SQL table hint NOLOCK, you would expect that this hint would always be honoured. However when reading data from a table this will always cause a schema stability lock (Sch-s); even when the NOLOCK hint is … Continue reading

Posted in SQL 2008 R2, SQL 2016 | Tagged | Leave a comment

How to Get a SQL String Variable to be Larger Than 4000 Characters or 8000 Characters for Dynamic SQL

Sometimes desperate messages are required because SQL Server parameterisation does not work (i.e. can’t use a variable in place of a table name). Recently I was constructing some dynamic SQL and the code was failing execution because the SQL dynamic … Continue reading

Posted in SQL 2008 R2, SQL 2016, Uncategorized | Tagged | Leave a comment

Executing the same SQL statements against multiple servers and databases using SQL Server Management Studio, and Merging the Results

Validated against: SQL Server: 2008 R2. If you need to apply the same SQL statements against multiple servers and databases, this can be accomplished using SQL Server Management Studio (version 2008 onwards). Simply with the Registered Servers window, select the … Continue reading

Posted in SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Executing the same SQL statements against multiple servers and databases using SQL Server Management Studio, and Merging the Results

SSIS Package Upgrade then error “Cannot use the OUTPUT option when passing a constant to a stored procedure”

I have been recently upgrading SQL Server 2005 SSIS packages to 2008 R2 using the automated wizard. The wizard performed the upgrades without error, however on executing the packages I would get the following error in a data flow OLE … Continue reading

Posted in SQL 2005, SQL 2008 R2 | Tagged | Comments Off on SSIS Package Upgrade then error “Cannot use the OUTPUT option when passing a constant to a stored procedure”

Blocked SQL Sessions and Blockers

Validated against: SQL Server 2005, 2008, 2008 R2. Below is a SQL script that returns SQL sessions waiting (blocked) for one second or more, and also the associated SQL sessions that are doing the actual blocking. The script returns pretty … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Blocked SQL Sessions and Blockers

Tracking Down a Deadlock Issue.

Validated against: SQL Server 2008 R2. Tracking down the reason for a SQL Server dead lock issue is straight forward enough, simply use the SQL Server Profiler and place a ‘Locks’ trace against the event ‘Deadlock graph’. Start the profiler … Continue reading

Posted in SQL 2008 R2 | Tagged | Comments Off on Tracking Down a Deadlock Issue.

Very Quick Accurate Row Counts

Validated against: SQL Server 2005, 2008, 2008 R2.

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Very Quick Accurate Row Counts

Current Database Update All Table Statistics with Fullscan

Validated against: SQL Server 2005. Updating statistics against each table in the current database by using the undocumented stored procedure [sp_MSforeachtable].

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Current Database Update All Table Statistics with Fullscan

Efficient Numbers Table

Validated against: SQL Server 2005. Just performed a quick Google of the various methods of acquiring a list of sequential numbers and recommend the approach by Itzik Ben-Gan. The approach uses a small footprint of code and allows numbers up … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Efficient Numbers Table

Disk Subsystem Benchmarking

When benchmarking disk subsystems, I generally benchmark against the most challenging task; which is random writes, with no buffering! The tool I use is the Microsoft SQLIO tool that measures IO performance. The SQLIO tool can be acquired from: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163 … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Disk Subsystem Benchmarking