Deleting unused indexes from SQL server to free up space and improve performance

In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server’s overall performance by only maintaining useful indexes – but finding the ones you don’t need can be quite a manual process.

If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index.

Remember that the data from these DMVs is reset when SQL Server is restarted, so make sure you have collected data for a long enough period of time to determine which indexes may be good candidates to be dropped.

Run this in SQL Server:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()

Here we can see seeks, scans, lookups and updates.

The seeks refer to how many times an index seek occurred for that index. A seek is the fastest way to access the data, so this is good.
The scans refers to how many times an index scan occurred for that index. A scan is when multiple rows of data had to be searched to find the data. Scans are something you want to try to avoid.
The lookups refer to how many times the query required data to be pulled from the clustered index or the heap (does not have a clustered index). Lookups are also something you want to try to avoid.
The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.

To find the ones that can be safely removed, run this:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND OBJECT_NAME(S.[OBJECT_ID]) = '[your table name]'
       AND S.database_id = DB_ID() AND (USER_SEEKS = 0 AND USER_SCANS =0 AND USER_LOOKUPS=0)

You can then delete the unused indexes.

Advertisements

How to reduce index size and free up space in a SQL database.

First, run this script to figure out the worst offenders:

create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

Continue reading “How to reduce index size and free up space in a SQL database.”

Permanently erasing sensitive data or recovering formatted information.

I have recently come upon this post on  http://hostjury.com/blog/view/195/the-great-zero-challenge-remains-unaccepted  and decided to give it a try. What could be easier than writing a .NET application which will write 0’es on the free space of a hard drive, and then I would try to recover any bits of files that were previously deleted from the space in question. I know, the challenge is different but this is what I want to convert it to.

I don’t have UNIX nor the “dd” library but I do have a mind set to help the police recover deleted/formatted information from hard drives or maybe help a good company forever erase sensitive data. Wherever this quest might lead me, it will definitely help someone.

To start, let’s say I have a sensitive file I need to erase from a hard drive.

I have a Windows 7 system (pro), all the coding tools I need and a 5GB file (a DVD Movie I copied on my hard drive and then deleted it). I will try to recover it after I try to erase every trace of it.

This started off easy but then I hit my first road block. All the code samples I have found were written in C, C++ using low-level handles. I quickly found a C# implementation of a function which would work with the Defragmentation API and show a quick method to access the VolumeMap (to find the free space). After converting it to VB.NET (my preferred language) and fixing the few casting errors that appeared I found myself having a bit of a struggle to obtain the hard drive descriptior.

Using the file path as ?c: as was recommended by MSDN, brought on WIN32 error number 87. Now, this does not say much so I tried to implement a FormatMessage function using the Windows API and failed miserably. Upon digging a bit forward, I found out that to get the message from the windows error I would have to change this line

Throw New Exception(Marshal.GetLastWin32Error().ToString())

to:

Throw New Win32Exception()

This will convert the number to a string that makes sense. In my case, the error was “Invalid Argument”. So I changed the path format to “C:” and this time I got the dreaded “Access Denied” error (code 5).

At this point I started pulling my hair. How could the Windows security settings be the ones not allowing me to perform dangerous functions with my hard drive. 🙂 I know.

After reading on this issue a little bit online, I found this nice post explaining everything there is to know about elevation of rights in .NET so I can execute my program as an administrator.

After adding an extra piece of code on my form to elevate the rights on the program being executed, I had my handle but when it came to opening the handle and obtaining the hard drive image,

Dim fResult As Boolean = DeviceIoControl(hDevice, _
FSConstants.FSCTL_GET_VOLUME_BITMAP, p, _
CUInt(Marshal.SizeOf(i64)), pDest, q, _
             size, IntPtr.Zero)

The error I got was “Handle is Invalid!”. The handle number was 0. I had a read again on the FSCTL_GET_VOLUME_BITMAP function:

Operation

 This is generally the first FSCTL that is used by a
 defragmenter, because it is required to get a map of the
 clusters that are free and in use on a volume. Each bit
 in the bitmap returned in the output buffer represents
 one custer, where a 1 signifies an allocated cluster and
 a 0 signifies a free cluster.

FileHandle

  The file handle passed to this call must represent a
  volume opened with the GENERIC_READ flag. A volume can be
  opened with a name of the form, ".X:", where 'X' is
  the volume's drive letter.

I shall stop here and continue later. There must be something simple to do about this.