Thursday, March 31, 2011

SQL Server: TSQL Script to Get Hard Drives Detail


Recently, I need a script which can provide me detail (Driver Letter, Drive Label, Free Space, Used Space etc) of all installed hard drives. I found following script by G. Rayburn very helpful.

Valid for versions: SQL Server 2005 and above
Expected Result:
 Note: Before executing script, don't forget to enable Ole Automation Procedures from Surface Area Configuration or using sp_configure.
/********************************************************
**    Author:  G. Rayburn
*********************************************************/
SET NOCOUNT ON

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
      DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
      DROP TABLE ##_DriveInfo

DECLARE @Result INT
      , @objFSO INT
      , @Drv INT
      , @cDrive VARCHAR(13)
      , @Size VARCHAR(50)
      , @Free VARCHAR(50)
      , @Label varchar(10)
CREATE TABLE ##_DriveSpace
      (
        DriveLetter CHAR(1) not null
      , FreeSpace VARCHAR(10) not null
       )
CREATE TABLE ##_DriveInfo
      (
      DriveLetter CHAR(1)
      , TotalSpace bigint
      , FreeSpace bigint
      , Label varchar(10)
      )

INSERT INTO ##_DriveSpace
      EXEC master.dbo.xp_fixeddrives
-- Iterate through drive letters.
DECLARE  curDriveLetters CURSOR
      FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)
      OPEN curDriveLetters
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
             SET @cDrive = 'GetDrive("' + @DriveLetter + '")'
                  EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT
                        IF @Result <> 0
                              EXEC sp_OADestroy @Drv
                              EXEC sp_OADestroy @objFSO

                  SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
                  SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
                  INSERT INTO ##_DriveInfo
                        VALUES (@DriveLetter, @Size, @Free, @Label)
      END
      FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''
-- Produce report.
SELECT DriveLetter
      , Label
      , FreeSpace AS [FreeSpace MB]
      , (TotalSpace - FreeSpace) AS [UsedSpace MB]
      , TotalSpace AS [TotalSpace MB]
      , ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC  
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo

Wednesday, March 30, 2011

SQL Server: Small Tables’ Clustered Indexes Fragmentation

Recently, I have received a mail from my friend who was angry that, the defragmentation script that I have mentioned in my post Simple Method to Resolve All Indexes Fragmentation is not working properly, even he executed said script multiple times. sys.dm_db_index_physical_stats is still showing few tables with high fragmentation.
On further inquiry I found that, said tables are from setup schema and have small number of rows. And I just replied him that I am HAPPY that script is not working for these tables.
Actually, when we create a table and start inserting rows, SQL Server  initially allocates pages from mixed extents until it has enough data to deserve a full extent, then SQL Server will allocate a uniform extent to it. Similarly if you build an index on a table that have fewer then eight pages SQL Server will allocate pages from mixed extents for storing the index data. And if these mixed extents are not located side by side then database management view sys.dm_db_index_physical_stats will show HIGH external fragmentation. So no need to worry about fermentation of clustered index of small tables which have fewer then eight pages.

Wednesday, March 23, 2011

My Hero


It’s my 100th post and today I have decided NOT to write about SQL Server, rather I would like to share something about my aim in life. What I want to be.
A Database Administrator or Database Architect or something like that. NO, I have always wished to be like Abdul Sattar Edhi.
Edhi is a common person, who is running an NGO named “EDHI Foundation”. With Worlds largest Ambulatory service, Over 400 Help Centers, Dozens of Child Houses and Women Safe Houses, its annual budget is more then $50 millions. But his personal (whole family) monthly budget is only $100. A person who never spent more then $5 dollars for his own dressing.


Do you know, how he collects this $50 million every year, to help others. At the age of 80 he daily goes out on streets and collect funds himself.

 According to wikipedia some 20000 abandoned babies have been saved and about a million babies have been delivered in the Edhi maternity homes.

If we have no peace, it is because we have forgotten that we belong to each other. (Mother Teresa)

Tuesday, March 22, 2011

SQL Server: Querying Case Insensitive Data With Case Sensitive Filters


Today, I have faced a very simple but still annoying problem. I was asked by a client that queries are not working correctly with given WHERE clause. Actually database was design as case insensitive but SOMEHOW on application side there were some business logics which client needs case sensitive inputs (Just like password) and comparisons.  
Here is an example for easy understanding. We have following simple table and data.
USE AdventureWorks
GO
CREATE TABLE #CaseInSensitive
(cisId INT identity(1,1), cisText varchar(50))

INSERT INTO #CaseInSensitive (cisText)
SELECT 'Abc'
UNION ALL
SELECT 'ABc'
UNION ALL
SELECT 'ABC'
UNION ALL
SELECT 'AbC'

If we need all records where column cisText value is ‘ABC’ then here is simple query
SELECT * fROM #CaseInSensitive
WHERE cisText = 'ABC'
Ops. We got all four records as output but we need only one records where cisText = ‘ABC’. But at the time of table creation we have not set any case sensitive collation. Don’t worry; here is a query which can help us.
SELECT * fROM #CaseInSensitive
WHERE cisText COLLATE SQL_Latin1_General_CP1_CS_AS = 'ABC'

During table design we can also make a column case sensitive
CREATE TABLE #CaseSensitive (csId INT identity(1,1), csText varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS)

Monday, March 21, 2011

SQL Server: Understanding Output of SET STATISTICS TIME ON


In one of my early post Which is More Important for Query Optimization, CPU or Logical Reads? We have discussed about importance of logical page read values, that when we need to compare performance of two different versions of same query. If fewer pages are being accessed by one of our query, then it would be considered efficient as compare to other one.
But how much time a query is taking can be best measured by setting STATISTICS TIME on by using statement SET STATISTICS TIME ON.
 
--DBCC FREEPROCCACHE
SET STATISTICS TIME  ON
SELECT TOP (10) ProductID,[Name],Color,ListPrice,[Size]
FROM Production.Product
SET STATISTICS TIME  OFF
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 6 ms.
(10 row(s) affected)
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
This output can be grouped as
  • Parse Time (in milliseconds) 
  • Compile Time (in milliseconds) 
  • Execution Time (in milliseconds)
Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree
Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.
And execution time is total time spent during execution of compiled plan.
All above three outputs are further divided to two subparts each i.e. CPU time and Elapsed time. CPU time is time used by CPU resources to complete a task (parse, compile or execute), while elapsed time is the total time took by a task from start to its end.
For example execution CPU time is just the time for which the CPU was busy executing the task and Elapsed time is the amount of time it took for the query to execute from start to completion. Most of the time elapsed time larger then CPU time because it also includes time spent during I/O operations required by query.
Elapsed time can be different for same query syntax on same server during different execution times because it depends upon other resources availability. Hence CPU time for execution of a query is important during performance comparison process.
Keep in mind that, ZERO parse and compile time is an indication that optimizer has reused the existing plan and hence made no effort and took no time for these processes.
DBCC FREEPROCCACHE is used to clear out the cache BUT DON’T use it on production servers.

Saturday, March 19, 2011

SQL Server: How to Analyze Blocking and Deadlocking


In response to an early post Difference Between Locking, Blocking and Dead Locking , I have received few mails where I was asked “How to analyze this blocking and dead locking phenomenon”.
I like to use performance monitor counters to check the frequency of blocking and dead locking. You can find these counters by selecting SQL Server: Locks. Three counters under this group are very useful.
  1. Lock Timeouts/sec
  2. Lock Wait Time (ms)
  3. Deadlocks/sec
Out of these three counters first two are used to analyze blocking. Value for “Lock Timeouts/sec” should be zero (0) and “Lock Wait Time (ms)” must also be very low. If you are observing nonzero value for “Lock Timeouts/sec” and continuous high value for “Lock Wait Time (ms)”, then there is excessive blocking occurring. Your long running queries can cause this blocking. Use profiler or sys.dm_exec_query_stats to identify such culprit queries.
None zero values “Deadlocks/sec” counter is an indication of deadlocks. Value for this counter must always be zero.
We can also use following query
SELECT *
FROM    sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Locks' 
AND instance_name = '_Total'
AND counter_name IN ('Lock Waits/sec','Lock Wait Time (ms)','Number of Deadlocks/sec')
Do you have any better idea ? Please do share with us.

Thursday, March 17, 2011

SQL Server: Cascade Delete


To delete a record, with all of its reference records, in other tables is not an easy task, especially when database has dozens of tables and you have not designed your tables with cascade delete option. Then cascade delete in SQL Server is only possible by writing your own script.
Here is a script, which I like to use for such cascade deletes. Script is originally written by Danial Crowther. I just made few changes as script was created for older versions.

Download Script

USE AdventureWorks
GO
--============== Supporting function dbo.udfGetFullQualName
IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
DROP FUNCTION dbo.udfGetFullQualName
GO
CREATE FUNCTION dbo.udfGetFullQualName ( @ObjectId INTEGER )
RETURNS VARCHAR(300)
AS BEGIN 
DECLARE @schema_id BIGINT
SELECT @schema_id = schema_id FROM sys.tables
WHERE object_id = @ObjectId
RETURN '[' + SCHEMA_NAME(@schema_id) + '].['
+ OBJECT_NAME(@ObjectId) + ']' 
END 

GO
--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
DROP FUNCTION dbo.udfGetOnJoinClause
GO
CREATE FUNCTION dbo.udfGetOnJoinClause ( @fkNameId INTEGER )
RETURNS VARCHAR(1000)
AS BEGIN
DECLARE @OnClauseTemplate VARCHAR(1000)
SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND '

DECLARE @str VARCHAR(1000)
SET @str = ''
SELECT 
@str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate,
'<@pTable>',
OBJECT_NAME(rkeyid)),
'<@pCol>',
COL_NAME(rkeyid, rkey)),
'<@cTable>', OBJECT_NAME(fkeyid)),
'<@cCol>', COL_NAME(fkeyid, fkey))
FROM    dbo.sysforeignkeys fk
WHERE   fk.constid = @fkNameId --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')

RETURN LEFT(@str, LEN(@str) - LEN(' AND '))
END
GO
--=========== CASECADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
DROP PROCEDURE dbo.uspCascadeDelete
GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR(300), -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location)
@WhereClause VARCHAR(2000), -- WHERE CLAUSE (Location.LocationID = 7)
@ExecuteDelete CHAR(1) = 'N', -- 'Y' IF WANT TO DELETE DIRECTLY FROM SP, 
--'N' IF YOU NEED DELETE SCRIPT
@FromClause VARCHAR(8000) = '', -- IF LEVEL 0, THEN KEEP DEFAULT
@Level INTEGER = 0
AS -- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys

SET NOCOUNT ON

/* Set up debug */
DECLARE @DebugMsg VARCHAR(4000),
@DebugIndent VARCHAR(50)

SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> '

IF ISNUMERIC(@ParentTableId) = 0
BEGIN -- assume owner is dbo and calculate id
IF CHARINDEX('.', @ParentTableId) = 0
SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId
+ ']')
ELSE
SET @ParentTableId = OBJECT_ID(@ParentTableId)
END

IF @Level = 0
BEGIN
PRINT @DebugIndent
+ ' **************************************************************************'
PRINT @DebugIndent + ' *** Cascade delete ALL data from '
+ dbo.udfGetFullQualName(@ParentTableId)
IF @ExecuteDelete = 'Y'
PRINT @DebugIndent
+ ' *** @ExecuteDelete = Y *** deleting data...'
ELSE
PRINT @DebugIndent
+ ' *** Cut and paste output into another window and execute ***'
END


DECLARE @CRLF CHAR(2)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @strSQL VARCHAR(4000)

IF @Level = 0
SET @strSQL = 'SET NOCOUNT ON' + @CRLF
ELSE
SET @strSQL = ''

SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent
+ dbo.udfGetFullQualName(@ParentTableId) + ' Level='
+ CAST(@@NESTLEVEL AS VARCHAR) + ''''
IF @ExecuteDelete = 'Y'
EXEC ( @strSQL
)
ELSE
PRINT @strSQL

DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT
fkNameId = constid,   -- constraint name
cTableId = fkeyid     -- child table
FROM    dbo.sysforeignkeys fk
WHERE   fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
AND fk.rkeyid = @ParentTableId

OPEN curs_children

DECLARE @fkNameId INTEGER,
@cTableId INTEGER,
@cColId INTEGER,
@pTableId INTEGER,
@pColId INTEGER

FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId --, @cColId, @pTableId, @pColId

DECLARE @strFromClause VARCHAR(1000)
DECLARE @nLevel INTEGER

IF @Level = 0
BEGIN
SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId)
END

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT  @strFromClause = @FromClause + @CRLF + '      INNER JOIN '
+ dbo.udfGetFullQualName(@cTableId) + @CRLF
+ '       ON ' + dbo.udfGetOnJoinClause(@fkNameId)
SET @nLevel = @Level + 1
EXEC dbo.uspCascadeDelete @ParentTableId = @cTableId,
@WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete,
@FromClause = @strFromClause, @Level = @nLevel

SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId)
+ @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause
+ @CRLF
SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent
+ 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId)
+ '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)'
+ @CRLF + @CRLF

IF @ExecuteDelete = 'Y'
EXEC ( @strSQL
)
ELSE
PRINT @strSQL

FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId 
--, @cColId, @pTableId, @pColId
END

IF @Level = 0
BEGIN
SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent
+ dbo.udfGetFullQualName(@ParentTableId) + ' Level='
+ CAST(@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE'''
+ @CRLF
SET @strSQL = @strSQL + 'DELETE FROM '
+ dbo.udfGetFullQualName(@ParentTableId) + ' WHERE '
+ @WhereClause + @CRLF
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM '
+ dbo.udfGetFullQualName(@ParentTableId)
+ ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF

IF @ExecuteDelete = 'Y'
EXEC ( @strSQL
)
ELSE
PRINT @strSQL
END

CLOSE curs_children
DEALLOCATE curs_children


GO


/*
-- Example 1
EXEC uspCascadeDelete
@ParentTableId = 'Production.Location',
@WhereClause = 'Location.LocationID = 2'
--    ,@ExecuteDelete = 'Y'


-- Example 2
EXEC uspCascadeDelete
@ParentTableId = 'dbo.brand',
@WhereClause = 'brand.brand_name <> ''Apple'''
--    ,@ExecuteDelete = 'Y'

exec uspCascadeDelete
@ParentTableId = 'dbo.product_type',
@WhereClause = 'product_type.product_type_id NOT IN 
(SELECT bpt.product_type_id FROM dbo.brand_product_type bpt)'
--    ,@ExecuteDelete = 'Y'
*/