How to find out the CPU usage information for the SQL Server process using Ring Buffers
SQL Server 2005 Ring Buffers stored information related to CPU usage. This information is updated every 1 minute. So at any given point of time you can trace back the CPU usage for 4 hours 15 minutes. A sample output below shows the SQL instance CPU utilization against the other processes (active on the server) CPU utilization. This will definitely help us identify whether the SQL Server process is actually driving CPU. Sample Output: record_id   EventTime               SQLProcessUtilization SystemIdle  OtherProcessUtilization----------- ----------------------- --------------------- ----------- -----------------------10832       2009-11-19

How to find out the missing indexes on a SQL Server 2008 or 2005 instance along with the CREATE INDE
I had previously blogged on Missing Indexes and how useful a feature this is for troubleshooting query performance issues. Here is a script for identifying the missing indexes currently on your instance. However, you would definitely need to show prudence in implementing the indexes reported by these DMVs after a due round of testing. You would also need to make sure that nothing else is broken by implementing new indexes. SELECT    mig.index_group_handle, mid.index_handle,   CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.u

SQL Server 2005 Service Pack 2 support is drawing to a close!!!
This is an important piece of information for all the DBAs who are managing SQL Server 2005 SP2 environments. SQL Server 2005 SP2 support will end on January 12, 2010 and the minimum level of support for SQL Server 2005 instances would be Service Pack 3 (Build 4035) and above. Since the SQL Release Services has already blogged about this, my two cents would be that everyone start conversations with their application vendors (if they are using vendor apps) to find out if the applications are supported on SP3 and start testing cycles to cover all UAT and other testing scenarios so that the instances in question can be upgraded to SQL Server 2005 SP3 before they become *

How to purge MSDB history using T-SQL scripts
One of the Maintenance Plan Tasks available is the History Cleanup Task which can help you keep the MSDB database size in check. This Maintenance Plan Task uses the MSDB Stored Procedures sp_delete_backuphistory and sp_purge_jobhistory to cleanup the historical information inside the MSDB system tables. I have seen multiple scenarios where trying to delete all the historical data has proved to be fatal (because

How to find out Security Errors on SQL Server 2005 instance and above including the API call it fail
More often than not, while troubleshooting SQL Server Security Issues, you end up performing post-mortem analysis. Under such circumstances, the Ring Buffer output comes in handy because it stores the Security Errors that were reported in the past for a certain period of time. For an example, let’s say if you tried to create a SQL Login with the Password Policy enabled and provided a password that didn’t match a password policy, then you will receive an error stating that the password is not complex enough. This error will be stored in the ring buffer. When you execute the query below, you will find out the SPID that raised the error along with the API Name that is failing. I