Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

Index Usage with Row Counts

I got into a bit of a situation today.  There were some performance issues in our performance testing environment and my changes (and a few others) were finally being tested.  I was also told by various levels of management not to work on this project – concentrate my efforts on Project B.   All I could think was my changes had to be using the correct indexes – how could I tell with out a lot of work – DMVs on index usage stats.  I fired up google and found this from mssqltips.com.  I changed the select a little and added the fairly accurate way to get row counts as a CTE.  Here’s what I came up with:

;With RowCounts_CTE as
(SELECT
so.id,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.id, so.name)

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
RC.[RowCount],
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
Join RowCounts_CTE RC on RC.id = S.object_id
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Order by 1
February 16th, 2011 | Tags: , , | Category: Scripts, SQLServerPedia Syndication | Leave a comment

T-SQL Tuesday #010: Partitioned Indexes and Data Types

TSQL2sDay150x150 This month’s T-SQL Tuesday, “T-SQL Tuesday #010: What are your thoughts on Database Indexes?” is hosted by Michael J Swart (Blog/Twitter) aka the Database Whisperer.  The topic is: Indexes.

Riddle me this:

Which method is faster? 

  • Partitioning an entire table, that is currently not partitioned, based on rebuilding a clustered index

Or

  • Moving data from a temporary table to an empty table that is partitioned based on the clustered index

We are currently testing both situations as we can’t logically figuring the fastest way based on theory.  There have been lots of debates and lots of theories on which is going to hold up.

Why?

You may ask why would one care about the faster option, well, here’s the situation we are currently in:

One of our logging tables in our production environment has an ID column that is an int.  This ID column is currently at about the 1,900,000,000 range – that alone makes me say YIKES!  An int “only” goes up to 2,147,483,647, so we are approaching this mark fast.  The solution that we are going with is to convert that column to a bigint data type.  A bigint’s max value goes up to 9,223,372,036,854,775,807 – now that ought to do it!  Part

Our Options:

Here’s our current options with the basic steps to change the data type:

Option 1

  1. Drop all the indexes on the table
  2. Drop the partition schema
  3. Drop the partition function
  4. Alter table to the new data type
  5. Create the partition function
  6. Create the partition schema
  7. Recreate the indexes on new functions which will partition the table
  8. Test

Option 2

  1. Copy data to new table
  2. Drop existing table
  3. Drop the partition schema
  4. Drop the partition function
  5. Create the partition function
  6. Create the partition schema
  7. Create table and indexes
  8. Copy data back to original table
  9. Test

We’ve started a pool on what’s going to be the fastest.  It’s all coming down to the indexing – leave a comment and let me know your educated guess and why.  I’ll definitely be blogging more about this one!

September 14th, 2010 | Tags: , , , | Category: TSQL Challenge, tsql2sday | 5 comments

SQL 2005 – Script to find missing indexes

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT weighted_cost = avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),
 s.*,
 'CREATE INDEX IX_' +
 REPLACE(REPLACE(COALESCE(equality_columns, inequality_columns), '[', ''), ']', '') +
 ' ON ' + d.statement + '(' + COALESCE(equality_columns, inequality_columns) +
 CASE WHEN equality_columns IS NOT NULL
  THEN
  CASE WHEN inequality_columns IS NOT NULL
   THEN ', ' + inequality_columns                
   END      
  END       + ')' +
  CASE WHEN included_columns IS NOT NULL
   THEN ' INCLUDE (' + included_columns + ')'
  END
FROM    sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g      
 ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s      
 ON g.index_group_handle = s.group_handle
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
January 29th, 2009 | Tags: , , | Category: Scripts, SQL2005 | Leave a comment