Archive for the ‘ Uncategorized ’ Category

Index Aligned Partitions and Resolution

Most of us are aware of partitioning in SQL Server.  It was introduced with SQL Server 2005 and then further enhanced in later releases.  The value of partitioning a table can vary.  Some use it as a means of archiving off older data, others use it for the sake of performance gains, either way, there are some important things to keep in mind when one partitions a table.

One of those items brings us to the point of the post, aligning your non-clustered indexes on the partition scheme.  This is an important rule and more often than not it should be followed.

One of the reasons that it’s important is if you ever want to switch partitions, all of the indexes associated to that object must be aligned.  ref:

Another reason is around maintenance.  If the indexes are partitioned then only the active partition (based on your partitioning strategy) needs to be maintained (re-org’d).  For larger indexes, it can become prohibitive to re-build these when they become fragmented.

Probably the best reason to have your indexes aligned is that the amount of reads to satisfy your queries will significantly drop when that index is used.

I recently came across a scenario where I found that there were a couple of tables where the indexes weren’t aligned with the partition scheme.  Instead of going through every partitioned table in the database, I wrote a quick query that would tell me which objects had indexes that weren’t created on the partition scheme for the table.

This query will also show you the usage of the indexes which can be interesting as well.  Without further ado, here’s the query:

SELECT Db_name(database_id) AS dbname,               object_name,               index_name, 
FROM   sys.objects AS o 
       JOIN sys.indexes AS i 
         ON o.object_id = i.object_id 
       JOIN sys.data_spaces ds 
         ON ds.data_space_id = i.data_space_id 
       LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s 
         ON i.object_id = s.object_id 
            AND i.index_id = s.index_id 
WHERE  o.TYPE = 'u' 
       AND i.Type in (1, 2) 
       AND IN (SELECT 
                      FROM   (SELECT, 
                              FROM   sys.objects ob 
                                     JOIN sys.indexes ind 
                                       ON ind.object_id = ob.object_id 
                                     JOIN sys.data_spaces ds 
                                       ON ds.data_space_id = ind.data_space_id 
                              GROUP  BY, 
                                        ds.type_desc) a 
                      GROUP  BY 
                      HAVING COUNT(*) > 1) 
ORDER  BY object_name DESC  

That’s fun stuff… But then what happens when you actually want to FIX them?  If there are a lot of them, it makes for a fairly tedious task.  The code below will help.  Be absolutely certain that you check the options chosen.  For my database, I used MAXDOP = 32, ONLINE = ON, etc… Ensure that the the settings are appropriate for your environmnent… enjoy!


SELECT db_name(database_id) as DBName, Object_Name,	 Index_name, 		
       i.Type_Desc as IDX_typeDesc , , ds.type_desc
		, + '.' as SchemaName_TableName, CAST(NULL as varchar(128)) as partitonOrdinal
into #tmp1
 FROM sys.objects AS o		
      JOIN sys.indexes AS i		
  ON o.object_id = i.object_id 		
	JOIN sys.schemas sch on sch.schema_id = o.schema_id
	JOIN sys.data_spaces DS on DS.data_space_id = i.data_space_id		
	LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id		

 WHERE  o.type = 'u'		
   AND i.type IN (1, 2) -- Clustered and Non-Clustered indexes
   AND O.NAME in 		
	SELECT from 		
		(SELECT OB.NAME, ds.type_desc 
		 FROM sys.objects OB 
			JOIN sys.indexes ind on ind.object_id = ob.object_id 
			JOIN sys.data_spaces ds on ds.data_space_id = ind.data_space_id		
		GROUP BY, ds.type_desc ) a 
  group by having COUNT (*) > 1
order by Object_Name desc;

update a
	set partitonOrdinal = 
	FROM #tmp1 A JOIN 
	  sys.tables          t ON A.Object_Name =  
	join sys.schemas s on s.schema_id = t.schema_id
	join  sys.indexes         i 
		  on(i.object_id = t.object_id 
		  and i.index_id < 2)
	join  sys.index_columns  ic 
		  on(ic.partition_ordinal > 0 
		  and ic.index_id = i.index_id and ic.object_id = t.object_id)
	join  sys.columns         c 
		  on(c.object_id = ic.object_id 
		  and c.column_id = ic.column_id)
	WHERE = a.Index_name and a.IDX_typeDesc = 'clustered'    ;    

update a
	set name =, partitonOrdinal = b.partitonOrdinal
	FROM #tmp1 a join #tmp1 b on a.object_name = b.object_name where b.idx_typedesc = 'clustered';

WITH index_cte (table_name, index_name, type_desc, index_columns, index_included_columns,
filter_definition, is_unique, is_primary_key, is_unique_constraint,fill_factor,
is_padded, allow_row_locks, allow_page_locks, ignore_dup_key, data_compression_desc,
no_recompute, partitionname, partitonOrdinal)
select'.' [table_name], [index_name], i.type_desc,
(select '[''] '+
	CASE WHEN k.is_descending_key = 1 THEN 'DESC'
	END as [data()]
from sys.index_columns as k
inner join sys.columns col
		on k.object_id=col.object_id and k.column_id=col.column_id
	where k.object_id = i.object_id
	and k.index_id = i.index_id	and k.index_id<>0 and k.is_included_column<>1
	order by key_ordinal, k.column_id
for xml path(''))
,'C [','C, [') as [index_columns],
	(select '['']' as [data()]
from sys.index_columns as k
inner join sys.columns col
		on k.object_id=col.object_id and k.column_id=col.column_id
	where k.object_id = i.object_id	and k.index_id = i.index_id and k.index_id<>0 and k.is_included_column<>0
	order by key_ordinal, k.column_id

for xml path(''))
,'] [','], [')
,'') as [index_included_columns],

	ISNULL(i.filter_definition,'') as filter_definition,
	is_unique, is_primary_key, is_unique_constraint, fill_factor, is_padded, allow_row_locks, allow_page_locks, ignore_dup_key, 
	data_compression_desc, no_recompute,, a.partitonOrdinal 
from sys.indexes as i
inner join sys.objects obj
	on i.object_id=obj.object_id
inner join sys.schemas sch
	on obj.schema_id=sch.schema_id
inner join sys.partitions part
	on i.object_id=part.object_id
	and i.index_id=part.index_id
inner join sys.stats stats
	on i.object_id=stats.object_id
	and i.index_id=stats.stats_id

JOIN #tmp1 a ON a.index_name = AND a.object_name = and a.type_desc = 'rows_filegroup'

where is not null and obj.type<>'S' and<>'sys' and i.index_id>1
CASE is_unique
	ELSE ''
+ index_cte.type_desc +' INDEX ' + index_cte.index_name COLLATE DATABASE_DEFAULT +' ON ' +table_name +' ('+index_columns+')'+
CASE index_included_columns
	WHEN '' THEN ''
	ELSE ' INCLUDE (' + index_included_columns + ') '
CASE filter_definition
	WHEN '' THEN ''
	ELSE ' WHERE ' + filter_definition
	WITH ('+
	'FILLFACTOR = ' + CASE fill_factor WHEN 0 THEN '100' ELSE CAST(fill_factor as varchar) END+
	', PAD_INDEX = ' + CASE is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END+
	', ALLOW_PAGE_LOCKS = ' + CASE allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END+
	', ALLOW_ROW_LOCKS = ' + CASE allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END+
	', IGNORE_DUP_KEY = ' + CASE ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END+
	', DATA_COMPRESSION = ' + data_compression_desc +
	', ONLINE = ON'+
	', MAXDOP = 32'+
	')' + '
	 ON  ' +  partitionname + ' (' + partitonOrdinal + ')
as 'USE Database statement goes here (TNW)'
from index_cte
 order by table_name, index_cte.type_desc, index_cte.index_name

drop table #tmp1

What three events brought you here?

I was tagged by Chris Shaw for this…  It’s an open-ended question that I could have a lot of fun with.  For instance, I could simply say that what brought me here was a need for a larger house, the need for a dedicated home office and a killer deal.  That’s what brought me to where I’m currently sitting as I write this blog post…  I don’t suppose that’s the intent, so I’ll do my best to share a bit of my history.

1.)  I was in marketing.  I did a lot of work with pulling data from an AS400, “importing” it into Access so that I could then “import” it into Word and mail merge direct marketing campaigns.  A lot of fun.  After having all of that fun, there came the day that I was introduced to SQL Server.  Not knowing any better, I thought it to be a form of Access.  Thankfully it wasn’t and thus began my career as a ‘geek’.

2.)  I did some bouncing around in the early years at companies such as MCI (WorldCom), Norwest (WellsFargo), Lucent(Avaya).  Do you see the trend there?  Every company that I went to work for was purchased by another company.  I thought to myself, there has to be a better way to life than going through these mergers / acquisitions etc… so I went independent.  Since that time, I have had a couple of stints being an employee at startup’s but by and large, I’ve been independent and I love it.

3.)  Being independent has upsides and downsides.  For one, I don’t have the traditional “boss”, instead, I have many of them in the form of clients.  This is awesome in a lot of ways, yet significantly more demanding in others.  It has allowed me the freedom to look for the type of work that I want to do and not worry about being on a certain project team or growing stagnant in a particular organization fulfilling a particular role.

Going forward I hope to expand my little business as I believe that I’m able to deliver quality better than most of the big consulting shops out there for less money overall.

With that said, if you are in need of some SQL Server help, I’d love to hear from you.

On an Island with no Umbrella Drinks?

I was tagged by Chris Shaw to answer this question.

“So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wish list of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?”

heh… well, in the event that my golf clubs didn’t make it, I suppose that I would spend a great deal of time learning more about the guts or internals of SQL Server. It’s a subject area that I’m very interested in, yet I find that the time to truly dig in is often cut short by work.

I would also spend a fair bit of time coming up with ideas for articles, blog posts and presentations. These are all activities that I enjoy and it’s one way that I can say “thank-you” to those who have helped me along the way.

Given enough time and concentration, I would write a tool that could take any schema and the data in that schema and build a true load test. Over the years I have encountered many clients who face the challenge of truly perf testing their environment prior to going “live”. Often, those who have invested the time and money into this activity find that the performance they experienced in the lab is different than in production… The reasons vary but I believe that I’ve seen it enough to be able to hit this pretty closely.

Wow, *if* I could accomplish all of that in a month, I’d pack up my laptop, flipflops and hit the beach. :)

Since I’m new to blogging, I’m not going to tag anyone just yet.

Search… Are you Lijit?

I’ve recently added a new button on the blog. You can see it in the upper right hand corner. It’s labeled “Search my Network”. Click it!

This is pretty cool actually. What it does is search my blog for your criteria and it also searches other sites that I’ve added as “trusted”. Sites include Microsoft’s SQL CAT team, SSWUG, Ted Malone’s blog, Brent Ozar’s blog etc…

Think about this for your own blog or website! Wow, to be able to direct your readers to information that you deem as trustworthy and accurate. It is the opposite of a Google search in that the results are quality based (based on them being “trusted” by you), versus quantity based (Google).

This clever technology is provided free of charge, minus the ad pollution, but hey, it’s free! The company name is Lijit, based here in Colorado at the foothills of the majestic Rocky Mountains.

A shout out to a great colleague and even better friend, Zach Conger for clueing me in.

SQL Quiz #4: Tagged by Chris Shaw


Chris Shaw asks a great question that I’m going to respond to.  Since this blog is all of two days old, I’m going to refrain from tagging anyone at this point.  Later tonight, be on the lookout for a technical post around partitioning and managing the partitioned indexes.

Who has been a great leader in your career and what made them a great leader.

I’ve personally had many great leaders in my career.  Oddly some of the guys that come to mind aren’t in the technical field at all. 

A few folks come to mind, one of them is a manager that I have had a couple of times in my career, Jeff.  As we speak, I’m currently doing an engagement for the company he works for and at the same time I’m ramping up my consulting company, working with several clients and trying to build out a practice.  The reason that I’d call him a great leader is that his advice, to date, has been invaluable.  It’s a type of advice that’s not always easy to recognize, however.  Many people that I’ve encountered in this life will “give” advice in verbal form.  One of the reasons that Jeff is listed is because his advice is rarely verbal but it’s in the way that he lives.  So, instead of the old “do as I say, not as I do” mantra, his is a quiet, understated and living example of leadership.

My father always used to tell me that if I chose to play sports, always play with people better than yourself, if you choose to go into business, work with people more successful than you.  Many of you have probably heard the same advice over the years and I truly believe that it rings true.  So, with that in mind, I’m going ask another question, now that you have been mentored or seen great leadership in practice, do you find that others look to you for that leadership now?  If so, how does that impact your conversataions with them and decisions regarding them? 

Heck, that’s a great question… I’m turning around and pining Chris Shaw again.