SQL Server Wait Types in SQL 2008

While working on an assignment of troubleshooting performance issue few month back I have come across a situation where I have to provide good information about the different waittypes and what they do, while researching a good documentation on wait types I have found the most authentic information on PSS Engineer’s blog where Bob Ward has put them nicely. This blog was published in 2009 but its good to keep handy as reference.

Most of the time wait type says us where the bottleneck are, if you wait types like IO_Completion, PAGE IO LATCH, this is indication for disk IO subsystem has some bottleneck. We have to use perfmon counter to verify this and then work around on resolving this issues.

Let me quote some links for the further reading:

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx

http://support.microsoft.com/kb/822101

http://blogs.msdn.com/b/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Change the Collation setting in MS SQL Server

How to Change the Collation Settings for specific database or how to change the Collation Settings for specific column?

This question can be found multiple times on the ms sql server forums, so I've decided to pen down it on the,Simple!! Once can use Alter Database command to change the collation settings for the specific database, below is the command you've to execute:

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

photo credit: dorena-wm via photopin cc

Read More

How to know version of MS SQL Server

"How to check which version of SQL Server I am on" this is a commonly asked question in SQL Server community; I have created a script which can be run in MS SQL Server 2000 and MS SQL Server 2005 you may download script from here
read complete FAQ on it here

Read More

MS has released KB960082 and KB960089 for MS SQL Server 2000 and MS SQL 2005

MS has released one Vulnerability bulletin found for MS SQL Server

This is applicable to below products

SQL Server 2000 SP4 (All edition) --> (KB960082)
SQL Server 2000 Itanium-based Edition Service Pack 4 --> (KB960082)
Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Service Pack 4 --> (KB960082)

SQL Server 2005 SP2 --> (KB960089)
SQL Server 2005 x64 Edition Service Pack 2 --> (KB960089)
SQL Server 2005 with SP2 for Itanium-based Systems -->(KB960089)

SQL Server 2005 Express Edition Service Pack 2 --> (KB960089)
SQL Server 2005 Express Edition with Advanced Services Service Pack 2 --> (KB960089)

Patches are available at http://www.microsoft.com/technet/security/Bulletin/MS09-004.mspx

Read More