Tag Archives: indexes

PGH.NET Code Camp Review

Posted on April 23, 2010 by | Leave a comment

This past weekend I had the pleasure of helping out David Hoerster (bio, twitter) with the setup of PGH.NET Code Camp.  Why, would I want to get up before the crack of dawn to help?  Well for starters David is a die hard Pittsburgh Pirates fan like myself.  Okay the real answer, is that I wanted to help with the code camp before I ran my first SQLSaturday in West Virginia.   This was a great practice before the game starts on May 1st.

In short I learned that if you have good volunteers it’s actually not too hard to have a great event.  If the event is free there will be people who just don’t show.  I guess mowing their lawn is more important than free training?  People will also leave before the event is over.  I could be wrong but I thought about half of the crowd left before the last session ended.  Finally, if a speaker doesn’t show you could replace the session with speakers panel.  This was a great move and actually was my favorite session of the day. 

SQL Server 2008 for Developers

During the day I also did a presentation on SQL Server 2008 for Developers.  I presented right after lunch and expected the majority of the crowed to have a food coma from the free burritos but this was not the case.  It actually was a nice interactive session. The following are a few quick facts from the presentation. 

About 40 people attended this session and only five of them are currently using SQL 2008.  I know this is a small sample size but it makes me believe that there are a lot of people out there still on SQL 2005 or SQL 2000.

The majority of the people awake (yes,  a few experience the food coma) were really impressed with using the real-time debugger to debug stored procedures, using merge to replace truncate table insert into table and use TVP to pass a data table in as an input parameter.

Follow-up Answers

The following are answers to some questions that were asked after the session.  Thought I would answer them here so others could find them in the future.

Can I create an index to filter based on dates?

Yes, you can.  I believe this is a new feature added in SQL Server 2008.  The following script will execute.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801' AND StartDate < '20010801';
GO

Checkout this MSDN link for more on Filtering Indexes. 

How can I make my database run faster? 

This is always a fun question and a hard one to answer without looking at the database and the server it resides on.   I recommended that this individual start with the free performance boosts.  This is also known as managing your indexes.   To do this checkout the following great links with video from SQLServerPedia.

Can I find the last time database objects were modified?

Yes, check out this link for a script that does the following task

Leave a comment

Posted in Events, Professional Development, SQLServerPedia Syndication

Tagged , , ,

Get index fragmentation statistics

Posted on September 30, 2009 by | 3 comments

I recently attended a Pittsburgh SQL Server user group meeting where Brent Ozar gave a presentation on the silent performance killer.  This motivated me to create a stored procedure that could leverage the DMVs in SQL 2005/2008 to gather index fragmentation statistics for all databases on a given server.

Goal

The goal is very simple.  Build a query that could be scheduled to grab statistics that are helpful towards determining if an index needs to be defragged or reorganized.  I would like to throw these results into a table so I could analyze them at a later date.  I would also like to monitor the fill factor and padding to determine if I need to make changes and to analyze if the changes are really helpful.

Download Scripts

The following script uses the following DMV’s sys.dm_db_index_physical_stats, sys.objects and sys.indexes and this script is provided as is.

To download the script click here

To download the create table script for the table used click here


Table Definition

The following is an explanation of the columns.  The following descriptions come from MSDN.

Column Name

Description
databaseName

Name of database, unique within an instance of SQL Server.
objectName Object name.
indexName Name of the index. name is unique only within the object.

NULL = Heap
partitionNumber 1-based partition number within the owning object; a table, view, or index.

1 = Nonpartitioned index or heap.
fragmentation Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks.

0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.
fill_factor > 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = Default value
is_padded 1 = PADINDEX is ON.

0 = PADINDEX is OFF.
type_desc Description of index type:

HEAP
CLUSTERED
NONCLUSTERED
XML
SPATIAL
page_count Total number of index or data pages.

For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.
date this is the current date GETDATE()

Script

   1:  ALTER PROCEDURE [dbo].[GetStatsForIndexes]
   2:      @PageCount INT = 100
   3:  
   4:  AS
   5:  BEGIN
   6:      -- SET NOCOUNT ON added to prevent extra result sets from
   7:      -- interfering with SELECT statements.
   8:      SET NOCOUNT ON;
   9:  
  10:      -- Declare varables
  11:      DECLARE @dbID INT, @dbName VARCHAR(128), @SQL NVARCHAR(MAX)
  12:  
  13:      -- Create a temp table to store all active databases
  14:      CREATE TABLE #databaseList
  15:      (
  16:            databaseID        INT
  17:          , databaseName      VARCHAR(128)
  18:      );
  19:  
  20:      -- we only want non-system databases who are currenlty online
  21:      INSERT INTO #databaseList (databaseID, databaseName)
  22:      SELECT d.database_id, d.name FROM sys.databases d where d.[state] = 0 and d.database_id > 4
  23:  
  24:  
  25:      -- Loop through all databases 
  26:         WHILE (SELECT COUNT(*) FROM #databaseList) > 0  BEGIN
  27:  
  28:             -- get a database id
  29:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  30:          FROM #databaseList;
  31:  
  32:              SET @SQL = 'INSERT INTO DBA_Tools.dbo.IDX_FRAG (databaseName, ObjectName, indexName, partitionNumber, fragmentation, fill_factor, is_padded, type_desc, page_count, [date])
  33:                  SELECT
  34:                    db.name AS databaseName
  35:                  , obj.name AS ObjectName
  36:                  , idx.name AS indexName
  37:                  , ps.partition_number AS partitionNumber
  38:                  , ps.avg_fragmentation_in_percent AS fragmentation
  39:                  ,idx.fill_factor
  40:                  ,idx.is_padded
  41:                  ,idx.type_desc
  42:                  , ps.page_count
  43:                  , GETDATE() as [date]
  44:              FROM sys.databases db
  45:                INNER JOIN sys.dm_db_index_physical_stats ('+CAST(@dbID AS VARCHAR(10))+', NULL, NULL , NULL, N''Limited'') ps
  46:                    ON db.database_id = ps.database_id
  47:                INNER JOIN '+ @dbName+'.sys.objects obj ON obj.object_id = ps.object_id
  48:                INNER JOIN '+ @dbName+'.sys.indexes idx ON idx.index_id = ps.index_id AND idx.object_id = ps.object_id
  49:              WHERE ps.index_id > 0
  50:                 AND ps.page_count > 100
  51:              ORDER BY page_count desc
  52:              OPTION (MaxDop 1);'
  53:  
  54:          EXECUTE sp_executesql @SQL
  55:          -- remove the database from the databases table
  56:          DELETE FROM #databaseList WHERE databaseID = @dbID
  57:  
  58:          -- get the next database in the databases table
  59:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  60:          FROM #databaseList;
  61:  
  62:      END
  63:      -- temp table is no longer needed, so we will kill it.
  64:      DROP TABLE #databaseList;
  65:  END

3 Comments

Posted in Scripts, SQL Server, SQLServerPedia Syndication

Tagged ,