January 2010 | SansSQL

Wednesday, January 27, 2010

Useful DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.
Database Console Command statements are grouped into the following categories.
1. Maintenance
2. Miscellaneous
3. Informational
4. Validation


DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.


Some of the Maintenance DBCC Statements are:
DBCC CLEANTABLE
DBCC INDEXDEFRAG
DBCC DBREINDEX
DBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC SHRINKFILE
DBCC UPDATEUSAGE


Some of the Miscellaneous DBCC Statements are:
DBCC dllname (FREE)
DBCC HELP
DBCC FREESESSIONCACHE 
DBCC TRACEOFF
DBCC FREESYSTEMCACHE
DBCC TRACEON


Some of the Informational DBCC Statements are:
DBCC INPUTBUFFER
DBCC SHOWCONTIG
DBCC OPENTRAN
DBCC SQLPERF
DBCC OUTPUTBUFFER
DBCC TRACESTATUS
DBCC PROCCACHE
DBCC USEROPTIONS
DBCC SHOW_STATISTICS


Some of the Validation DBCC Statements are:
DBCC CHECKALLOC
DBCC CHECKFILEGROUP
DBCC CHECKCATALOG
DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS  
DBCC CHECKTABLE
DBCC CHECKDB


Some of the useful DBCC commands along with their syntax and examples are listed down and are available for download. Click here to download your copy now.
Reference: BOL

Monday, January 25, 2010

Index Recommendations

This Query when executed gives the recomendation to your indexes whether it has to reindexed or defraged.
This query also gives the fragmentation level of your indexes.

Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SO.name as TableName, SI.name as Indexname,index_type_desc as IndexType ,avg_fragmentation_in_percent as FragmentationPercentage,
(case when avg_fragmentation_in_percent between 10 and 30 then 'Defrag'
when avg_fragmentation_in_percent > 30 Then 'Reindex'
Else 'Can be Ignored Currently'
End) as Recomendation
from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), null, null, null, 'DETAILED') IPS ,
sys.indexes SI ,sys.objects SO , sys.schemas SS
where IPS.index_id=SI.index_id and IPS.object_id=SI.object_id and
SI.object_id=SO.object_id and SO.schema_id=SS.schema_id and
IPS.index_type_desc in ('NONCLUSTERED INDEX', 'CLUSTERED INDEX')
Order by Recomendation desc

Thursday, January 21, 2010

SQL Server Performance Tuning Checklist

It's our duty as a DBA to ensure our SQL Server databases and their applications run at their full potential.
To achieve this we have to take care of our databases. In this process, Performance Tuning plays a very major role and slightly difficult to manage. It will be easy if we have a checklist which will be help us making our jobs easy.

Here is a checklist which conatins various parameters which we can look at when we are doing performance tuning on the SQL Server databases.

This list can be downloaded from here (Office 2007 Format).
For Office 2003 Format, Click Here.

Monday, January 18, 2010

EXCEPT operation in SQL server 2005

The EXCEPT operation is a new feature that is introduced in SQL Server 2005
EXCEPT returns any distinct values from the left query that are not also found on the right query.
In general, except operations is used to find difference between two identical tables.
The basic rules for combining the result sets of two queries that use EXCEPT are the following:
1. The number and the order of the columns must be the same in all queries. 
2. The data types must be compatible. 


Consider the situation where we have to compare two tables for the correctness of their data or maybe they are tables in Test and Production databases, and you want to see if they are in sync or not. This can be done Using EXCEPT Operation on these two tables.
Let us try it out using two tables Table_A and Table_B. Consider that these tables have same schema.


Example 1:
Select * from Table_A
Except
Select * from Table_B
This example, compares Table_B with Table_A and give the distinct values from Table_A which are not present in Table_B. i.e. Any additional rows that are present in Table_A but not present in Table_B will be displayed along with the rows in Table_A which does not match in Table_B. But this does not display Vice-Versa.


Example 2:
SELECT * FROM
 (
 SELECT * FROM Table_A
 EXCEPT
 SELECT * FROM Table_B
 ) AS DifferenceFromLeft
 UNION
 SELECT * FROM
 (
 SELECT * FROM Table_B
 EXCEPT
 SELECT * FROM Table_A
 ) AS DifferenceFromRight
This example, give the complete differences between the two tables. i.e. any non matching records in any of the two tables as well as any new records in any of the two tables will be displayed. But the drawback of this query is that it does not show from which table the mismatch is from.


Example 3:
To overcome the drawback of Example 2, we will tune the query of Example 2 to include the source table names which will be helpful in identifying the source of the mismatch.
SELECT 'Table_A' as Source, * FROM
 (
 SELECT * FROM Table_A
 EXCEPT
 SELECT * FROM Table_B
 ) AS DifferenceFromLeft
 UNION
 SELECT 'Table_B' as Source, * FROM
 (
 SELECT * FROM Table_B
 EXCEPT
 SELECT * FROM Table_A
 ) AS DifferenceFromRight
To get the correct and expected results out of EXCEPT operation, it is mandatory to specify the columns in both the queries in the same order. If the order of the columns mismatch, then the result from the EXCEPT operation will mislead (you will not get the correct results).

This article is also available in pdf format for downloading.
Please Click here to get your copy.

Thursday, January 14, 2010

SQL Exercise Examples (Basics)

Download SQL Server Examples from Here.
This Contains basics and are usefull for starters.

Saturday, January 9, 2010

SQL Server 2008 requires Microsoft .NET Framework 2.0 SP2 to be installed

When you are installing SQL Server 2008 express edition, your installation might fail due to the lower version of .NET Framework.



To overcome this error
1. Install .NET Framework 3.5 and 3.5 SP1.
     .NET Framework 3.5 can be downloaded from Here.
     .NET Framework 3.5 SP1 can be downloaded from Here.
2. Install Windows installer 4.5.
    Windows installer 4.5 can be downloaded from Here.