Find Missing Indexes in a Database

Find Missing Indexes in a Database

 

Here is the SQL SCRIPT that can be helpful identifying missing indexes in a database. This query identifies missing indexes based on query cost benefit. I have used this script and found very useful. Again do not believe blindly on the output of this script and use your common sense before taking any decision.

Best option is to analyze the execution plan before creating any indexes as per output from the above script. You need to make sure that you do not create any unused indexes in your database. Remember having unused indexes affects the performance of the database. Also this script displays the missing indexes only when the query improvement cost is more than 10.

ALWAYS remember whenever the SQL instance gets restarted, data from dynamic management views get flushed out.

 

 

Here is the SCRIPT:

-- Identifying missing indexes based on query cost benefit.

SELECT 

            migs.avg_total_user_cost *
          ( migs.avg_user_impact / 100.0 )*
          ( migs.user_seeks + migs.user_scans )

          AS Query_Cost_Benefit ,

        'CREATE INDEX [missing_index_'

        + CONVERT (VARCHAR, mig.index_group_handle) + '_'
        + CONVERT (VARCHAR, mid.index_handle) + '_'
        + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
        AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')'
          + ISNULL(' INCLUDE ('+ mid.included_columns + ')', '')

          AS create_index_statement ,

 
        migs.* ,
        mid.database_id ,
        mid.[object_id]

FROM    sys.dm_db_missing_index_groups mig

        INNER JOIN sys.dm_db_missing_index_group_stats migs
                              ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid
                              ON mig.index_handle = mid.index_handle

WHERE   migs.avg_total_user_cost *

            ( migs.avg_user_impact / 100.0 )*
            ( migs.user_seeks + migs.user_scans ) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ) DESC

Find Service Pack installed in Windows server

To find out the service pack installed in windows server 2008 R2, we can query the registry as follows:

 

Hive: HKEY_LOCAL_MACHINE
Key: system\CurrentControlSet\control\windows
Name:CSDVersion
Type: REG_DWORD

The name is CSDVersion and here should be the value as per service pack installed:

Value: 0x100 SP1
Value: 0x200 SP2
Value: 0x300 SP3
Value: 0x400 SP4
Value: 0x500 SP5
Value: 0x600 SP6

SQL Query to Determine Current Space Utilization

SQL Query to Determine Current Space Utilization

Here is the script to determine space used by each database. This includes path of data and log file, database names, used space and free space.  This will also provide total database count in the server and total space available in each and every drive. I found this script very useful as a quick look of the space utilization.

The following query I use every day to monitor the space on my servers. If I found any issues, I can dig more and get the root cause. Following could be reason of log file growth though:

1.    Incorrect recovery models.

      2.    TempDB full
      3.    Not a good capacity planning
      4.    Too many indexes

Set NoCount On
--Check to see the temp table exists

IF EXISTS ( SELECT Name FROM tempdb..sysobjects Where name like '%HoldforEachDB%' )

--If So Drop it

DROP TABLE #HoldforEachDB_size

--Recreate it

CREATE TABLE #HoldforEachDB_size

(
[DatabaseName] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Size] [decimal] NOT NULL,
[Name] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Filename] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
)
ON [PRIMARY]

IF EXISTS ( SELECT name FROM tempdb..sysobjects Where name like '%fixed_drives%' )

--If So Drop it

DROP TABLE #fixed_drives

--Recreate it

CREATE TABLE #fixed_drives

(
[Drive] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MBFree] [decimal] NOT NULL
)
ON [PRIMARY]

--Insert rows from sp_MSForEachDB into temp table

INSERT INTO #HoldforEachDB_size

EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, Case
When [?]..sysfiles.size * 8 / 1024 = 0 Then 1 Else
[?]..sysfiles.size * 8 / 1024 End
AS size,[?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'

--Select all rows from temp table (the temp table will auto delete when the connection is gone.

INSERT INTO #fixed_drives

EXEC xp_fixeddrives
Select @@Servername
print '' ;

Select

rtrim(Cast(DatabaseName as varchar(75))) as DatabaseName,

Drive,
Filename,
Cast(Size as int) AS Size,
Cast(MBFree as varchar(10)) as MB_Free
from #HoldforEachDB_size
INNER JOIN #fixed_drives ON
LEFT(#HoldforEachDB_size.Filename, 1) = #fixed_drives.Drive
GROUP BY DatabaseName,Drive,MBFree,Filename,Cast(Size as int)
ORDER BY Drive,Size Desc

print '' ;

Select

Drive as [Total Data Space Used |],

Cast(Sum(Size) as varchar(10)) as [Total Size],
Cast(MBFree as varchar(10)) as MB_Free
from #HoldforEachDB_size
INNER JOIN #fixed_drives ON
LEFT(#HoldforEachDB_size.Filename, 1) = #fixed_drives.Drive
Group by Drive,MBFree

print '' ;

Select

count(Distinct rtrim(Cast(DatabaseName as varchar(75)))) as Database_Count

from #HoldforEachDB_size

FInd how much we have allocated in each FIlegroup

Here is the script that will provide the available/consumed space in each file group. By using this script we can get the total size of each file group and how much we have allocated in each file group. Test this script in DWV/QA environment and run in production (If required).

-- Find the total size of each Filegroup

select data_space_id, (sum(size)*8)/1000 as total_size_MB

into #filegroups
from sys.database_files
group by data_space_id
order by data_space_id

-- FInd how much we have allocated in each FIlegroup

select ds.name, au.data_space_id

, (sum(au.total_pages) * 8)/1000 as Allocated_MB
, (sum(au.used_pages) * 8)/1000 as used_MB
, (sum(au.data_pages) * 8)/1000 as Data_MB
, ((sum(au.total_pages) - sum(au.used_pages) ) * 8 )/1000 as Free_MB
into #Allocations
from sys.allocation_units as au inner join sys.data_spaces as ds
on au.data_space_id = ds.data_space_id
group by ds.name, au.data_space_id
order by au.data_space_id

-- Get the Required output

select f.data_space_id

, a.name
, f.total_size_MB
, a.allocated_MB
, f.total_size_MB - a.allocated_MB as free_in_fg_MB
, a.used_MB
, a.data_MB
, a.Free_MB
from #filegroups as f inner join #allocations as a
on f.data_space_id = a.data_space_id
order by f.data_space_id

drop table #allocations

drop table #filegroups

Recovering a SQL Server Database From SUSPECT Mode

How to Repair Database which is in SUSPECT mode in SQL Server 2005/2008

Morning you come to your office and you see your one of the critical database in SUSPECT mode and you are not able to do any action against that database. You need to repair the database at the earliest.

Why Database Is In SUSPECT Mode

There could be so many reasons for this but here are the main reasons that I can think of.

1.    Data files or log files are corrupt.

      2.    Database server was shut down improperly.
      3.    SQL cannot complete a rollback or rollforward operation.

If you want to find the exact reason why your database is in SUSPECT mode, you can use below query. This should give you the exact error message.

DBCC CHECKDB ('<Database Name>') WITH NO_INFOMSGS, ALL_ERRORMSGS

How to Repair the database and bring it online

Follow below steps to bring the database ONLINE and accessible.

1.    Change the status of your database by running following query

          EXEC sp_resetstatus '<Database Name>';

2.    Set the database in EMERGENCY mode by running following query

ALTER DATABASE <Database Name> SET EMERGENCY;

3.    Check the database for any inconsistency by executing below query

DBCC CHECKDB('<Database Name>');

4.    If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you are done.

          ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

5.    Take the backup of the database just to be in safe side.

6.    Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('<Database Name>', REPAIR_ALLOW_DATA_LOSS);

7.    Finally bring the database in MULTI USER mode by running following query.

ALTER DATABASE <Database Name> SET MULTI_USER;

8.    At last step you verify the connectivity from your database. Now users should be able to connect to the database properly. If any data loss, you may need to get from the backup taken in step 5.

Now your database is ONLINE and accessible. Now Rock and have the coffee to RELAX.

Find Currently Running Queries

How to find out currently running queries in SQL Server 2005/2008


We can find out currently running queries by using dynamic management views in SQL Server 2005 and 2008. Here is the script by using we can find out currently running queries. We can order our result by CPU cost. We will get complete query text in the output.

select r.session_id

      ,status
      ,substring(qt.text,r.statement_start_offset/2,
      (case when r.statement_end_offset = -1
      then len(convert(nvarchar(max), qt.text)) * 2
      else r.statement_end_offset end - r.statement_start_offset)/2)
      as query_text
      ,qt.dbid
      ,qt.objectid
      ,r.cpu_time
      ,r.total_elapsed_time
      ,r.reads
      ,r.writes
      ,r.logical_reads
      ,r.scheduler_id
from sys.dm_exec_requests r
      cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.cpu_time desc

Query to find Blocking

We can find out the blocking by running following query. If this query results something then definitely there is blocking in the server.

select

spid,
blocked,
waittype ,
waittime,
lastwaittype,
dbid ,
uid,
cpu,
physical_io,
memusage ,
login_time,
last_batch, hostname ,
program_name,
nt_domain,
nt_username,
loginame
from master..sysprocesses
where blocked <> 0
or spid in (select blocked from master..sysprocesses)

How to insert comma - delimited string value in a table

How to insert comma - delimited string value in a table

Here is the script that will insert comma separated string (values separated by comma). String may have only one value as well. This script will work in both the cases.

declare @string varchar(50)

declare @tabvar table(val varchar(50))

set @string = 'a,db,c,dd,eee,f'

while(LEN(@string) > 0)

begin

       if(charindex(',', @string) > 0)

       begin 

              insert into @tabvar

              select substring(@string,1 , charindex(',', @string) - 1)

       end

       else

       begin 

              insert into @tabvar

              select @string

              break

       end

       set @string = substring(@string,charindex(',', @string) + 1, len(@string))

end

select * from @tabvar

Why Transaction Log is Growing!!!

Why Transaction Log is Growing!!!

Reasons for Trnsaction Log Growth

Following are the basic reasons for Transaction log growth:

1. When we do bulk insert/update operation.
2. Your database is in Full Recovery model and you are doing Index rebuild operation. Or your database is involved in Mirroring/Log shipping and you are doing Index rebuild operation.
3. If you have Replication implemented in your environment and something is failing in that, you may see transaction log growing unexpectedly.
4. Check the Auto growth value. I have see most of the times high percentage of this causes transaction log growth.

How to Troubleshoot

1. Backup the transaction log if you have enough disk space and your database is not in SIMPLE recovery model.
2. We can user DBCC SQLPERF (LOGSPACE) to find out the current used percentage of the transaction log for the specified database.
3. There could be some transaction running on the server that could be recovered later. Kill those transactions. We can use the command DBCC OPENTRAN.
4. We can try shrinking log file by using command DBCC SHRINKFILE. We may need to user BACKUP LOG with TRUNCATE_ONLY. This is what I use in my QA environment if I face any issues with transaction log growth.

USE <Your DB Name>

GO

CHECKPOINT

GO

BACKUP LOG <Your DB Name> TO <Backup Device Name> WITH NOFORMAT, INIT,  NAME = N'<Your DB Name>-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

DBCC SHRINKFILE(<Name of the log file>, 1)

GO

Tips To Write A Good Stored Procedure

Some Tips to write a good stored procedure

Coding SPs is a very common task in the database world. This is done not only by database developers, but also by application developers. Most of the smart DBA also keep himself/herself involved in writing and reviewing stored procedures. One of the interviews I was asked “How good you are in writing complex stored procedures” and “What are the major tips you keep in your mind when you write the stored procedures as DBA”.  Notice the last word added by interviewer “as DBA”. You should consider all the performance issues when you answer this question. 

Stored procedures are having more importance in the database as it reduces network traffic and improve performance. In this post I have just tried to add some tips to write good stored procedures. I hope this will be helpful for fresher and for the experienced developers (Application/database).

1. Always maintain better readability in the stored procedure. For this use proper indentation and make sure you specify SQL keywords in upper case. For Example instead of ‘Select’ write ‘SELECT’. This makes other developers to understand the code properly. If you do this at first site you are the good programmer/developer.  

2. Until you need so many variables in the SP, use as few as possible variables. Remember doing this will free some spaces in cache and performance would be better.

3. We should avoid dynamic queries in the stored procedures. In case if dynamic queries each time SQL server recompiles the statement. It has very bad performance impact.

4. When you are calling stored procedures, always use fully qualified names. The format for fully qualified name could be “DB_NAME.SCHEMA_NAME.TABLE_NAME”.  Whenever you create the SP always use schema name in the create procedure statement even though the schema is dbo.

5. As I mentioned before SPs reduces network traffic and increase the performance. I have seen so many SPs written by developers where SET NOCOUT ON has not been used. If you don’t use this SQL server returns the message that shows number of rows affected. This can cause extra network traffic and can have major impact on the performace.

6. DO NOT USE ‘sp_’ prefix in a stored procedure name. This is reserved for system stored procedures in the master database. If you use the prefix ‘sp_’ then basically you are making SQL server to have an extra look up in the master database.

7. Most of the time we call other SP(s) in one SP. We should always use sp_executeSQL instead of directly EXEC.

8. When we assign value to a variable, we use SET. We can also use SELECT statement. The difference between both is: By using SET you can assign value to one variable at a time but by using SELECT you can assign values to multiple different variables. So, instead of multiple SET statement use SELECT statement. This is much faster than multiple SET statements.

9. Avoid unnecessary conditions in the WHERE Clause. Avoid using IN instead use EXISTS. This gives better performance as IN considers the NULL value also. Remember result set for IN is heavier than EXISTS.

10. Avoid using DISTINCT and ORDER BY until you need this. It makes the performance slower.

11. We should not use CURSORS instead use temp table/table variable. If you need iteration then create the table with identity column and use WHILE loop.

12. Avoid using “SELECT *”. Instead specify column names (required) in the SELECT clause.

13. What we should use SUBQUERY or JOIN? Remember SUBQUERY works faster in large tables whereas JOINS works faster in small tables. You should use both keeping this in mind. Also avoid using correlated sub queries and this affect the performance of the query.

14. Try to use table variables instead of Temporary Tables - Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution. If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size. 

15. Use proper indexes - You can use the help of the data tuning advisor, but it does not gives the proper result all the time. Index scans are much faster than table scans. So identify the table scans from the execution plans. But when a table returns smaller rows, then it is better to use a table scan. 

Remember for the good performance rules could be broken if needed. We should keep in mind that for any firm database performance is the ultimate goal.

Hope these tips help you to some extent. There could be some other tips that I can add later.