Index Code Snippet – Code Library

When we’re asked to look at a database whether there have been performance problems or not, many times we at some point or another will end up looking at indexes.  In the following example the T-SQL will return the table, the name of the index, and the fragmentation in percent of the index.  This piece of code should be put into your code library under indexes.

This query uses sys.dm_db_index_physical_stats which will give us the avg_fragmentation_in_percent of the index.  This query is then joined to sys.indexes to get the name of the index.  Finally, this query joins to sys.objects and sys.schemas so we can find the index more quickly in the result set. 

/*
Index Query
This query will return the Location, IndexName, IndexType, and Fragmentation of
 the indexes in a database.
*/
DECLARE @fragmentation tinyint
SET @fragmentation = 0

SELECT *
FROM
 (
 SELECT DB_Name() + '.' + ss.name + '.' + so.name [DB.Schema.Table],
     si.name IndexName,
     si.type_desc IndexType,
     sips.avg_fragmentation_in_percent Fragmentation
 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) sips
   INNER JOIN sys.indexes si  ON sips.object_id = si.object_id AND si.index_id = sips.index_id
   INNER JOIN sys.objects so ON so.object_id = si.object_id
   INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
 WHERE si.name IS NOT NULL
   AND avg_fragmentation_in_percent >= @fragmentation
 ) xx
ORDER BY [DB.Schema.Table], IndexType, Fragmentation DESC

Don’t Miss the Next PASS DBA Fundamentals Presentation:

January 8th 11:00 AM CST (Convert Time)

Louis Davidson – What Counts For a DBA

Meeting Details

The world of a DBA can be daunting for a person, either as a new or old, because not only do they need to keep up with new and emerging technologies, but also with the code and designs of their coworkers. In this highly participation driven session, we will employ a random topic chooser to pick several of these traits for discussion as a group. Possible topics include past blog topics such as Logic, Curiosity, Failure, Humility, Skill and Passion, as well as any other topics that might be added for that day. So come prepared to participate and voice your opinion about what counts for a DBA.

---

Louis has been in the IT industry for 17 years as a corporate database developer and architect. He has been a Microsoft MVP for 7 years and has written 4 books on database design. Currently serves as the Data Architect for the Christian Broadcasting Network supporting offices in Virginia Beach, Virginia and Nashville, Tennessee. For more information please visit drsql.org

 

 

Build Your Code Library

As a DBA I routinely add new code to my code library. I began compiling frequently used code years ago and it goes with me anywhere that I work. Our code library is like a physician’s little black bag that gives us the tools that we need to do our job. I would recommend that you keep yours as organized as possible as it will make finding the code easier when searching. Give the .sql file a name that will make it instantly recognizable as to what it will accomplish.  This may sound a bit elementary; however you’ll thank me in a few years when you have hundreds of code snippets to search through.  Sometimes when we’re looking for a snippet we’re under the gun and time counts, so organize your folders with a broad, methodical, and common sense approach.  It is also advisable to add a detailed comment to each portion of code.  If it needs any explanation or additional information create a comment block and write what’s needed.  Again when you are looking at it 2 years from now, the time that you took to write those instructions will be appreciated.

If you’re a new DBA and haven’t started yet then there’s no time like the present:

Create a folder on your computer called ‘DBA Code Library’ and then just to get started, let’s create a few subfolders similar to the following: 

 

Ok, now that we have our starting folder structure, let’s go ahead and get some code moved to the folder.  One thing we are asked to do very routinely is to search stored procedures, views, functions, and SQL agent jobs.  We could be looking for any queries that are inserting to or deleting from a particular field, or looking for any SQL Agent jobs that are doing the same.  The first snippet utilizes sp_MSForEachDB and will search through every database on the server you are connected to, and search for the string that you type between the percent signs.  The second will do the same but has two parameters that you can use to search.  So in this example it will search every stored procedure and function that uses the word ‘insert’ as well as the phrase ‘my table’, you get the idea.  The third will search all databases and search for a specific column name.  You will use these very frequently, so I thought it was fitting that we begin our code library with them.  The final piece searches all SQL Agent jobs for a specified string.  When the results are returned, they not only tell you which Agent job the string is found but which step in the job that it was found in.

--Searches all stored procedures, functions, and views for a particular string.
sp_MSForEachDB 'USE [?]; select DB_NAME() AS DbName, Name AS ObjectName 
    from sys.objects where object_id in 
    (select id from syscomments where text like ''%SearchCriteria1%'')'

GO

--Search with two parameters
sp_MSForEachDB 'USE [?]; select DB_NAME() AS DbName, Name AS ObjectName 
    from sys.objects where object_id in 
    (select id from syscomments where text like ''%SearchCriteria1%'') and
    object_id in (select id from syscomments where text like ''%SearchCriteria2%'')'

GO

--Search every database for a particular column name    
sp_MSForEachDB 'USE [?]; Select DB_Name() AS DbName, t.name as TableName
    From sys.tables t inner join
         sys.columns c on t.object_id = c.object_id
Where c.name like ''%SearchCriteria1%'''

--Search SQL Agent jobs for a particular string
USE [msdb]
GO

SELECT	j.job_id,
		s.srvname,
		j.name,
		js.step_id,
		js.command,
		j.enabled 
FROM	dbo.sysjobs j
JOIN	dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
JOIN	master.dbo.sysservers s
	ON	s.srvid = j.originating_server_id
WHERE	js.command LIKE N'%SearchCriteria1%'
		AND js.command LIKE N'%SearchCriteria2%'

Archived Recording

We have posted our first archived recording for the DBA Fundamentals VC (@DBAFundVC) by @SQLDiablo Adam Belebczuk ‘Intro to Service Broker’ http://fundamentals.sqlpass.org/MeetingArchive.aspx

Live Free Training

Don’t miss the DBA Fundamentals (@DBAFundVC) Virtual Meeting Tuesday October 2nd at 11:00AM CST. Jes Schultz Borland (@grrl_geek) – Make Your Voice Heard! #SQLPass http://fundamentals.sqlpass.org/Home.aspx

PASS Virtual Chapter DBA Fundamentals

The PASS DBA Fundamentals Virtual Chapter has been founded and the response has been great!  We have our first meeting July 10th at 11am CST.  I hope that we have a great turnout.