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: http://msdn.microsoft.com/en-us/library/ms187526.aspx

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,
       o.name               object_name,
       i.name               index_name,
       i.type_desc,
       ds.name,
       ds.type_desc,
       s.user_seeks,
       s.user_scans,
       s.user_lookups,
       s.user_updates,
       s.last_user_seek,
       s.last_user_update
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 o.name IN (SELECT a.name
                      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 ob.name,
                                        ds.type_desc) a
                      GROUP  BY a.name
                      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!

SET NOCOUNT ON

SELECT db_name(database_id) as DBName,o.name Object_Name,
        i.name Index_name,
       i.Type_Desc as IDX_typeDesc ,DS.name , ds.type_desc
		,sch.name + '.'+o.name 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 a.name 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 ob.name, ds.type_desc ) a
  group by a.name having COUNT (*) > 1
	)
order by Object_Name desc;

update a
	set partitonOrdinal = c.name
	FROM #tmp1 A JOIN
	  sys.tables          t ON A.Object_Name = t.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 I.name = a.Index_name and a.IDX_typeDesc = 'clustered'    ;    

update a
	set name = b.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)
as(
select sch.name+'.'+obj.name [table_name], i.name [index_name], i.type_desc,
REPLACE(
(select '['+col.name+'] '+
	CASE WHEN k.is_descending_key = 1 THEN 'DESC'
	ELSE 'ASC'
	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],
isnull(
REPLACE(
	(select '['+col.name+']' 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.name, 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 = i.name AND a.object_name = obj.name and a.type_desc = 'rows_filegroup'

where i.name is not null and obj.type<>'S' and sch.name<>'sys' and i.index_id>1
)
SELECT
'CREATE ' +
CASE is_unique
	WHEN 1 THEN 'UNIQUE '
	ELSE ''
END
+ 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 + ') '
END+
CASE filter_definition
	WHEN '' THEN ''
	ELSE ' WHERE ' + filter_definition
END+
'
	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+
	', STATISTICS_NORECOMPUTE = ' + CASE no_recompute WHEN 1 THEN 'ON' ELSE 'OFF' END+
	', DATA_COMPRESSION = ' + data_compression_desc +
	', SORT_IN_TEMPDB = ON'+
	', ONLINE = ON'+
	', MAXDOP = 32'+
	', DROP_EXISTING = ON'+
	')' + '
	 ON  ' +  partitionname + ' (' + partitonOrdinal + ')
 GO'
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

In November 2008 I had the opportunity to speak at Dev Connections.  One of the subjects that I covered was Plan Guides.  The abstract read “In SQL Server 2008, there exists a way to change or create a new plan for a query without touching the query itself. In environments where the database was provided by a third-party or in a high availability environment, these will help to decrease your impact on production systems while improving performance at the same time. Over the course of the session, we will walk through the creation of a plan guide.“ 

The reason for this blog post is that I was discussing the upcoming event that the Denver SQLUG is hosting with Kalen Delaney on June 9th, 2010.  Register here. 

I’ve already registered and I’m very much looking forward to hearing her talk on this subject.  I wanted to provide some background and familiarity with to plan guides for those who follow my blog and will be attending the UG meeting on the 9th.  

For me personally, understanding plan guides, why they matter, how to use them and what it’s all about stems from a solid understanding of execution plans and how SQL Server chooses to get to the data based on the query that you are passing it.  This is not a trivial subject and one that is best understood with a solid foundation of HOW the optimizer reports the methods it is utilizing in the retrieving of data. 

In my slide deck these questions are answered:  

     Q:  Why Plan Guides?  

     A:  Due to increased complexity in database environments today.  These complexities include Linq to SQL and it’s impact on plan cache.  C.O.T.S (Commercial Off the Shelf) implementations and the ability to be Proactive vs. Reactive in your environments. 

     Q:  What are they?  (Plan Guides) 

     A:  Plan Guides can be used to optimize the performance of queries when you cannot or do not want to change the text of the query directly.  Plan Guides can be useful when a subset of queries in a database application deployed from a third-party vendor are not performing as expected and the ability to change the query does not exist.  Plan Guides influence optimization of queries by attaching query hints or a fixed query plan to them.  (The last answer is from Microsoft). 

The slide deck and the demo also talk about the different memory pools in SQL Server and how to “see” the cache on the server.  I also touch on the life of cache and why it’s relevant. 

One of the biggest items in this presentation revolved around parameterization (forced or simple).  This is an incredibly powerful option that must be tested thoroughly in your environment.  The gist of it is that if you have a ton of compilations in your environment the changing of parameterizataion to forced might be something to really look into as it will increase your concurrency due to the decrease in compilations. 

Up until now in the demonstration, all I had covered was background information that is helpful to truly grasp Plan Guides… the last half of the presentation is on plan guides, how to create them, disable them, view them etc…    

Without further ado, here is the Powerpoint and the set of t-sql scripts that I used in my demo’s.  I’ve also commented the code that you can find below as it should help you when you walk through the powerpoint presentation. 

We begin with an Object Plan Guide   

USE AdventureWorks2008; 

go 

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60)) 

AS 

BEGIN 

    SELECT * 

    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 

        Sales.SalesTerritory AS t 

    WHERE h.CustomerID = c.CustomerID 

        AND c.TerritoryID = t.TerritoryID 

        AND CountryRegionCode = @Country_region 

END;        

GO 

– Create an OBJECT plan guide 

 

sp_create_plan_guide 

@name = N'Guide1', 

@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,       

        Sales.Customer AS c, 

        Sales.SalesTerritory AS t 

        WHERE h.CustomerID = c.CustomerID 

            AND c.TerritoryID = t.TerritoryID 

            AND CountryRegionCode = @Country_region', 

@type = N'OBJECT', 

@module_or_batch = N'Sales.GetSalesOrderByCountry', 

@params = NULL, 

@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'  

– @hints || base this on selectivity of the data “US” is good because it will be representative of rougly 2/3rds 

–                           of the depth in the index / heap 

—  Where do the created plans exist? 

 

select * from sys.plan_guides  -- (notice the HINTS column) 

GO 

--Disable the plan guide. 

EXEC sp_control_plan_guide N'DISABLE', N'Guide1'; 

GO 

select * from sys.plan_guides 

-- Now shows as disabled 

--Enable the plan guide. 

EXEC sp_control_plan_guide N'ENABLE', N'Guide1'; 

GO 

select * from sys.plan_guides 

-- Now shows as enabled 

--Drop the plan guide. 

EXEC sp_control_plan_guide N'DROP', N'Guide1'; 

GO 

select * from sys.plan_guides 

-- Now it's gone 

-- Disable ALL plan guides. 

EXEC sp_control_plan_guide N'DISABLE ALL'; 

GO 

– SQL Plan Guide 

– Example:  Set the Maxdop (Degree of parallelism = 1 

 

EXEC sp_create_plan_guide 

    @name = N'Guide1', 

    @stmt = N'SELECT TOP 1 * 

              FROM Sales.SalesOrderHeader 

              ORDER BY OrderDate DESC', 

    @type = N'SQL', 

    @module_or_batch = NULL, 

    @params = NULL, 

    @hints = N'OPTION (MAXDOP 1)'; 

GO 

 – NOTE ** any Option clause in a select statement will work with the @hints parameter. 

– Template Plan Guide 

          — Let’s Force the parameterization on this statement.  

 

DECLARE @stmt nvarchar(max); 

DECLARE @params nvarchar(max); 

EXEC sp_get_query_template 

    N'SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader AS h 

      INNER JOIN AdventureWorks2008.Sales.SalesOrderDetail AS d 

          ON h.SalesOrderID = d.SalesOrderID 

      WHERE h.SalesOrderID = 45639;', 

    @stmt OUTPUT, 

    @params OUTPUT 

EXEC sp_create_plan_guide N'TemplateGuide1', 

    @stmt, 

    N'TEMPLATE', 

    NULL, 

    @params, 

    N'OPTION(PARAMETERIZATION FORCED)'; 

GO 

  — NOTE:  This forces the query to be parameterized and then a guide can be created against it. 

  — Creating a plan using XML_showplan 

 

USE AdventureWorks2008; 

GO 

SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; 

GO 

DECLARE @xml_showplan nvarchar(max); 

SET @xml_showplan = (SELECT query_plan 

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 

    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp 

    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%'); 

EXEC sp_create_plan_guide 

    @name = N'Guide1_from_XML_showplan', 

    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 

    @type = N'SQL', 

    @module_or_batch = NULL, 

    @params = NULL, 

    @hints =@xml_showplan; 

GO 

– Create a plan guide for the query by specifying the query plan in the plan cache via the plan handle.  

 

USE AdventureWorks2008; 

GO 

SELECT WorkOrderID, p.Name, OrderQty, DueDate 

FROM Production.WorkOrder AS w 

JOIN Production.Product AS p ON w.ProductID = p.ProductID 

WHERE p.ProductSubcategoryID > 4 

ORDER BY p.Name, DueDate; 

GO                           

– Inspect the query plan by using dynamic management views. 

 

SELECT * FROM sys.dm_exec_query_stats AS qs 

CROSS APPLY sys.dm_exec_sql_text(sql_handle) 

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp 

WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%'; 

GO 

  — Create a plan guide for the query by specifying the query plan in the plan cache via the plan handle. 

 

DECLARE @plan_handle varbinary(64); 

DECLARE @offset int; 

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset 

FROM sys.dm_exec_query_stats AS qs 

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st 

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp 

WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%'; 

EXECUTE sp_create_plan_guide_from_handle 

    @name =  N'Guide8', 

    @plan_handle = @plan_handle, 

    @statement_start_offset = @offset; 

GO 

Reference the previous example of ….. “Means that there are 4811 plans for three nearly identical statements” 

With this, a plan can be created against that string….   ‘SELECT WorkOrderID, p.Name, OrderQty, DueDate%’ 

This is HUGE!!!  Think about the Commercial-Off-The-Shelf (COTS) implementations in your environment where you are largely bound by licensing and cannot physcially modify the schema, triggers, clustered indexes etc…  Additionally, there are still many applications that use a lot of dynamically built or in-line t-sql that don’t always perform well or, in the case of the example that I cited, there are hundreds or thousands of execution plans for the same t-sql statement. 

This next piece of t-sql checks the validity of the plan guides in the system. 

             An empty result set means that all plan guides are valid. 

 

USE AdventureWorks2008; 

GO 

SELECT plan_guide_id, msgnum, severity, state, message 

FROM sys.plan_guides 

CROSS APPLY fn_validate_plan_guide(plan_guide_id); 

GO 

Select statement taken from SQL Profiler 

Look at the execution plan. 

 

 SELECT COUNT(*) AS c 

FROM Sales.SalesOrderHeader AS h 

INNER JOIN Sales.SalesOrderDetail AS d 

  ON h.SalesOrderID = d.SalesOrderID 

WHERE h.OrderDate BETWEEN '20000101' and '20050101'; 

GO 

Look at the execution plan and note the Hash Match Inner Join  

Now, let’s make it a Merge Join instead. 

 

EXEC sp_create_plan_guide 

    @name = N'MyGuideProfiler1', 

    @stmt = N' SELECT COUNT(*) AS c 

FROM Sales.SalesOrderHeader AS h 

INNER JOIN Sales.SalesOrderDetail AS d 

  ON h.SalesOrderID = d.SalesOrderID 

WHERE h.OrderDate BETWEEN ''20000101'' and ''20050101''; 

', 

    @type = N'SQL', 

    @module_or_batch = NULL, 

    @params = NULL, 

    @hints = N'OPTION (MERGE JOIN)'; 

Spacing is critical on this. 

Check the statement to ensure that the planguide will force a merge join 

 

 SELECT COUNT(*) AS c 

FROM Sales.SalesOrderHeader AS h 

INNER JOIN Sales.SalesOrderDetail AS d 

  ON h.SalesOrderID = d.SalesOrderID 

WHERE h.OrderDate BETWEEN '20000101' and '20050101'; 

--Drop the plan guide. (if needed) 

EXEC sp_control_plan_guide N'DROP', N'MyGuideProfiler1'; 

EXEC sp_control_plan_guide N'DROP', N'TemplateGuide1'; 

EXEC sp_control_plan_guide N'DROP', N'Guide1'; 

– Jeremy Lowell http://www.datarealized.com || http://datarealized.wordpress.com || http://sqlperspectives.wordpress.com 

Feel free to ask questions as you step through this and I look forward to seeing everyone on the 9th!

The Rocky Mountain Tri-Fecta was today and I had the opportunity to speak.  I mentioned to those brave souls who attended my session that I would post the slide deck and the demo’s.

Feel free to download them (links are at the end of this post) and provide your comments / thoughts.

I had a great time attending the event and I learned a great number of things.  A special thanks to everyone involved with putting this together.  I’m sill amazed and the stacks of empty pizza boxes.  WOW!

Part of this post is also to ask the readers of this blog if they would be interested in checking out a new tool that I’ve been testing over the last couple of months.  It’s formally known as SmartMon and it provides a form of pattern analysis against the data looking for anomalies and patterns of similar behavior.  It has decreased the amount of time that I spend doing analysis on perfmon files by many factors.  It’s a great tool and the guys building it are stand up guys in the San Jose, CA area.  If you are interested in becoming one of the first handful of people to see the release of this product, e-mail feedback@perfmonanalysis.com.  Please mention that I referred you.

Without further ado, enjoy the SQL and thanks again for the opportunity to share what I’ve learned with all of you.

RockyMountainTriFecta (Link to the slide deck PPT)

Essentials-DMVs_tsql_dba (t-sql – Demo)

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.

So, over the years I have needed to move an index or a set of indexes to a new or different file group. I’ve written scripts for this in the past and lost them along the way and I couldn’t find anything out there on the bing or google interwebs.

The concept of multiple files and file groups for a database is beginning to sink in with a lot of developers and dba’s. The rationale for using them is typically sound and in my experience it’s one of wanting to decrease the internal file fragmentation of the data and the indexes. Often, one can find that there is a performance improvement as well because this allows you to move those indexes to that raid 1+0 array that your SAN admin has been waiting for you to use.

Here’s a stab at it that makes sense to me and it worked great for what I needed to accomplish today. What it doesn’t take into account are partitions and their related indexes, so be sure to read this code and test it prior to just taking my word for it working well.

I’m 100 percent open to ideas and suggestions, in the form of t-sql. I certainly appreciate comments and ideas, however, I will not write the t-sql for your idea. I’m giving this away and it’s my hope that as you improve upon it, you will kindly re-send or re-post it with the improvements.

Lastly, this handles non-clustered indexes only. It will ignore system indexes and statistics. Both of those are named out in the script so in the event that you want them; it’s an easy modification. I have comments in the code posted below and I STRONGLY advise you to read them before you run on a live system.

Click on the word doc attached to this post.  WordPress really makes the formatting of the code look ugly and I haven’t yet figured out how to make it look decent.

Enjoy!

 NonClusteredIndex_MOVE_newFG