SQL Query: How to retrieve the Table columns from a stored procedure

SELECT
name AS ColumnName
,system_type_name AS DataType
,CASE
WHEN is_nullable=0 THEN ‘NOT NULL’
WHEN is_nullable=1 THEN ‘NULL’
END AS IS_NULLABLE
,column_ordinal AS OrdinalPosition
,(‘Col_’ + CAST(column_ordinal AS varchar(4))) AS Alias
FROM sys.dm_exec_describe_first_result_set
(N'[dbo.Registration]’, null, 0) ;

 

SQL SERVER – How to retrieve the metadata of a stored procedure

In my earlier articles, I wrote about sys.dm_exec_describe_first_result_set
(A new dynamic management view shipped with SQL Server 2012), it is very handy when you need to know the metadata of the first possible result set of any Transact SQL. Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server. So I started developing the solution.

Given below are the solution.

SOLUTION 1 : For SQL Server 2012 and above using sys.dm_exec_describe_first_result_set

1
2
3
4
5
USE AdventureWorks2012;
GO
SELECT FROM sys.dm_exec_describe_first_result_set
(N'[dbo].[uspGetManagerEmployees] 16', null, 0) ;
 --OUTPUT

metadata of stored procedure.1.1

SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE AdventureWorks2012
GO
--DROP TABLE #temp1
GO
SELECT * INTO  #temp1
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=sa;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');
GO
SELECT * FROM tempdb.sys.columns
WHERE object_id=object_id('tempdb..#temp1')
GO
--View the column name / metadata of the
--above created temporary table.
--OUTPUT

metadata of stored procedure.1.2

Advertisements

SQL Query: Schedule multiple jobs to run over night

I had about 40 sql agent jobs. I had to schedule them to run one time only over night. It was a bit of hectic to open every job, change the ownership, modify the schedule time, then change the ownership back to the service account. In order to minimize the work effort, I have written a script based on CURSOR. Where I will have a control table, it will define the job name, schedule name, schedule start date, schedule start time. I will read this from the control table and the change the schedule accordingly.

 

USE msdb;USE msdb;go
/*create the control table and insert some dummy data.In order to make this script work, there has to be job exist defined in the control table.*/
CREATE TABLE [ETL].[SqlJobControl]( [SqlJobControlID] [int] IDENTITY(1,1) NOT NULL, [job_id] [int] NULL, [job_name] [nvarchar](200) NULL, [schedule_name] [nvarchar](50) NULL, [job_description] [nvarchar](100) NULL, [active_start_date] [int] NULL, [active_start_time] [int] NULL, [owner_login_name_user] [nvarchar](50) NULL, [owner_login_name_service_account] [nvarchar](50) NULL, [enabled] [bit] NULL, CONSTRAINT [PK__SqlJobCo__D32BB6AA03DE9823] PRIMARY KEY CLUSTERED ( [SqlJobControlID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GOSET IDENTITY_INSERT [ETL].[SqlJobControl] ON
INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (1, NULL, N’Developer_Test_Job1′, N’OneOff’, N’Dummy job’, 20170613, 175500, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (2, NULL, N’Developer_Test_Job2′, N’OneOff’, N’Dummy job’, 20170614, 180000, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)SET IDENTITY_INSERT [ETL].[SqlJobControl] OFF;
DECLARE  @job_id int, @job_name nvarchar(200), @schedule_name nvarchar(50), @job_description nvarchar(100), @active_start_date int, @active_start_time int, @owner_login_name_user nvarchar(50), @owner_login_name_service_account nvarchar(50), @enabled bit
;
DECLARE @oneoff_sqljob_cursor AS CURSOR;
SET @oneoff_sqljob_cursor = CURSOR FAST_FORWARD FOR SELECT  [job_id] ,[job_name] ,[schedule_name] ,[job_description] ,[active_start_date] ,[active_start_time] ,[owner_login_name_user] ,[owner_login_name_service_account] ,[enabled] FROM [UNIFY2].[ETL].[SqlJobControl] WHERE [enabled]=1;
OPEN @oneoff_sqljob_cursor;FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;
WHILE @@FETCH_STATUS = 0BEGIN EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_user; EXEC msdb.dbo.sp_detach_schedule  @job_name=@job_name,  @schedule_name=@schedule_name, @delete_unused_schedule=1 ;
EXEC msdb.dbo.sp_add_jobschedule  @job_name=@job_name,  @name=@schedule_name,  @enabled=1,  @freq_type=1,  @freq_interval=1,  @freq_subday_type=0,  @freq_subday_interval=0,  @freq_relative_interval=0,  @freq_recurrence_factor=1,  @active_start_date=@active_start_date,  @active_start_time=@active_start_time ; EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_service_account; FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;END

SQL Theory: How to Store (and Retrieve) Non-English Characters (e.g. Hindi, Czech, Arabic etc.) in SQL Server

If you have to store and retrieve characters of any other language besides English in SQL Server, you must do the following –

  1. Use a Unicode compatible data type for the table column. NVACHAR, NCHAR, NTEXT are the datatypes in SQL Server that can be used for storing non-English characters.
  2. Precede the Unicode data values with an N (capital letter) to let the SQL Server know that the following data is from Unicode character set. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
  3. The N should be used even in the WHERE clause.

REFERENCE: Microsoft Support KB 239530
You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
http://support.microsoft.com/kb/239530

If the correct data-type is not used or the data is not preceded with an N, SQL Server will save the data to the table as ‘?’ or another garbled character.

The following scripts demonstrate saving and retrieving multi-lingual data to and from SQL Server. I have used Google Translate to get the characters of other languages. I left out far-east languages like Japanese and Chinese from the following example on purpose because those languages have a few other considerations that I’ll save for another blog post.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
DROP TABLE dbo.unicodeData;
GO
CREATE TABLE dbo.unicodeData
( languageUsed VARCHAR(50)
, unicodeData NVARCHAR(200)
, nonUnicodeData VARCHAR(200) -- same data in a normal VARCHAR column for comparison
, comments VARCHAR(100)
);
GO
INSERT INTO dbo.unicodeData
( languageUsed
, unicodeData
, nonUnicodeData
, comments)
VALUES
('English'
, N'This is an example'
, N'This is an example'
, NULL)
,('Hindi'
, N'यह एक उदाहरण है.'
, N'यह एक उदाहरण है.'
, 'Using the preceding N in both strings but VARCHAR is still a ?')
,('Hindi'
, 'यह एक उदाहरण है.'
, 'यह एक उदाहरण है.'
, 'Not using the preceding N in both strings so both are a ?')
,('Kannada'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, NULL)
,('Arabic'
, N'هذا مثال على ذلك.'
, N'هذا مثال على ذلك.'
, NULL)
,('Czech'
, N'To je příklad.'
, N'To je příklad.'
, NULL);
GO
SELECT *
FROM dbo.unicodeData;
GO
-- Example of using N' in the WHERE clause
SELECT *
FROM dbo.unicodeData
WHERE unicodeData like N'%एक%';
Unicode Results
Unicode Results

Further Reading:

SQL Theory: LOOP, HASH and MERGE Join Types

LOOP, HASH and MERGE Join Types

Written By: Eitan Blumin 05/01/2012

Today I’ll talk about the available JOIN operator types in SQL Server (Nested Loops, Hash and Merge Joins), their differences, best practices and complexity.

For the samples in this post, we’ll use the free AdventureWorks database sample available here: http://msftdbprodsamples.codeplex.com/releases/view/4004

Introduction: What are Join Operators?

A join operator is a type of an algorithm which the SQL Server Optimizer chooses in order to implement logical joins between two sets of data.

The SQL Server Optimizer may choose a different algorithm for different scenarios based on the requested query, available indexes, statistics and number of estimated rows in each data set.

It’s possible to find the operator which was used by looking at the execution plan that SQL Server has prepared for your query.

For more information on execution plans and how to read them, I recommend checking out the first chapter out of Grant Fritchey’s excellent book:
http://www.simple-talk.com/sql/performance/execution-plan-basics/

NESTED LOOPS

“Nested Loops” is the simplest operator of the bunch.

We’ll take the following query as an example, which gets some order detail columns for orders placed during July 2001 (assuming the OrderDate column only includes dates without time):

The resulting execution plan looks like this:

 

The operator on the top right is called the outer input and the one just below it is called the inner input.

What the “Nested Loops” operator basically does is: For each record from the outer input – find matching rows from the inner input.

Technically, this means that the clustered index scan you see as the outer input is executed once to get all the relevant records, and the clustered index seek you see below it is executed for each record from the outer input.

We’ll verify this information by placing the cursor over the Clustered Index Scan operator and looking at the tooltip:

We can see that the estimated number of executions is 1. We’ll look at the tooltip of the Clustered Index Seek:

 

This time we can see that the estimated number of executions is 179 which is the approximate number of rows returned from the outer input.

In terms of complexity (assume N is the number of rows from the outer output and M is the total number of rows in the SalesOrderDetail table): The complexity of this query is: O(NlogM) where “logM” is the complexity of each seek in the inner input.

The SQL Server Optimizer will prefer to choose this operator type when the outer input is small and the inner input has an index on the column(s) by which the two data sets are joined. The bigger the difference in number of rows between the outer and inner inputs, the more benefit this operator will provide over the other operator types.

Having indexes and up-to-date statistics is crucial for this join type, because you don’t want SQL Server to accidently think there’s a small number of rows in one of the inputs when in fact there are a whole lot. For example: Performing 10 times index seek is nothing like performing 100,000 times index seek, especially if the table size of the inner input is around 120,000 and you’d be better off doing one table scan instead.

MERGE Join

The “Merge” algorithm is the most efficient way to join between two very large sets of data which are both sorted on the join key.

We’ll use the following query as an example (which returns a list of customers and their sale order identifiers):

The execution plan for this query is:

  • First, we’ll notice that both of the data sets are sorted on the CustomerID column: The Customer table because that’s the clustered primary key, and the SalesOrderHeader table because there’s a nonclustered index on the CustomerID column.
  • By the width of the arrows between the operators (and by placing the cursor over them), we can see that the number of rows returned from each set is rather large.
  • In addition, we used the equality operator (=) in the ON clause (the Merge join requires an equality operator).

These three factors cause SQL Server Optimizer to choose the Merge Join for this query.

The biggest performance gain from this join type is that both input operators are executed only once. We can verify this by placing the cursor over them and see that the number of executions is 1 for both of the operators. Also, the algorithm itself is very efficient:

The Merge Join simultaneously reads a row from each input and compares them using the join key. If there’s a match, they are returned. Otherwise, the row with the smaller value can be discarded because, since both inputs are sorted, the discarded row will not match any other row on the other set of data.

This repeats until one of the tables is completed. Even if there are still rows on the other table, they will clearly not match any rows on the fully-scanned table, so there is no need to continue. Since both tables can potentially be scanned, the maximum cost of a Merge Join is the sum of both inputs. Or in terms of complexity: O(N+M)

If the inputs are not both sorted on the join key, the SQL Server Optimizer will most likely not choose the Merge join type and instead prefer the Hash join (will be explained soon). However if it does anyway (whether it’s because it was forced to due to a join hint, or because it was still the most efficient), then SQL will need to sort the table which is not already sorted on the join key.

HASH Match

The “Hash” join type is what I call “the go-to guy” of the join operators. It’s the one operator chosen when the scenario doesn’t favor in any of the other join types. This happens when the tables are not properly sorted, and/or there are no indexes. When SQL Server Optimizer chooses the Hash join type, it’s usually a bad sign because something probably could’ve been done better (for example, adding an index). However, in some cases (complex queries mostly), there’s simply no other way.

We’ll use the following query as an example (which gets the first and last names of contacts starting with “John” and their sales order identifiers):

The execution plan looks like this:

Because there’s no index on the ContactID column in the SalesOrderHeader table, SQL Server chooses the Hash join type.

Before diving into the example, I’ll first explain two important concepts: A ”Hashing” function and a “Hash Table”.

“Hashing” is a programmatic function which takes one or more values and converts them to a single symbolic value (usually numeric). The function is usually one-way meaning you can’t convert the symbolic value back to its original value(s), but it’s deterministic meaning if you provide the same value as input you will always get the same symbolic value as output. Also, it’s possible for several different inputs to result in the same output hash value (meaning, the function isn’t necessarily unique).

A “Hash Table” is a data structure that divides all rows into equal-sized “buckets”, where each “bucket” is represented by a hash value. This means that when you activate the hash function on some row, using the result you’ll know immediately to which bucket it belongs.

As with the Merge join, the two input operators are executed only once. We can verify this by looking at the tooltips of the input operators.

Using available statistics, SQL Server will choose the smaller of the two inputs to serve as the build input and it will be the one used to build the hash table in memory. If there’s not enough memory for the hash table, SQL Server will use physical disk space in TEMPDB. After the hash table is built, SQL Server will get the data from the larger table, called the probe input, compare it to the hash table using a hash match function, and return any matched rows. In graphical execution plans, the build input will always be the one on top, and the probe input will be the one below.

As long as the smaller table is very small, this algorithm can be very efficient. But if both tables are very large, this can be a very costly execution plan.

SQL Server Optimizer uses statistics to figure out the cardinality of the values. Using that information, it dynamically creates a hash function which will divide the data into as many buckets with sizes as equal as possible.

Because  it’s so dynamic,  it’s difficult to estimate the complexity of the creation of the hash table and the  complexity of each hash match. Because SQL Server Optimizer performs this dynamic operation during execution time and not during compilation time, sometimes the values you see in the execution plan are incorrect. In some cases, you could compare a hash join and a nested loops join, see that according to the execution plans the nested loop is more expensive (in terms of logical reads etc.), when in fact the hash join executes much slower (because its cost estimation is incorrect).

For our complexity terms, we’ll assume hc is the complexity of the hash table creation, and hm is the complexity of the hash match function. Therefore, the complexity of the Hash join will be O(N*hc + M*hm + J) where N is the smaller data set, M is the larger data set and J is a “joker” complexity addition for the dynamic calculation and creation of the hash function.

Microsoft has an interesting page in Books Online that describes further Hash Join sub-types and additional aspects about them. I highly recommend you read it: http://msdn.microsoft.com/en-us/library/ms189313.aspx

Query Hints

Using Query Hints, it’s possible to force SQL Server to use specific join types. However, it’s highly not recommended to do so especially in production environments, because the same join type may not be the best choice forever (because data can change), and SQL Server Optimizer usually has it right (assuming statistics are up-to-date).

I’ll talk about them just so you can experiment with the different join types on your test environment and see the differences.

To force SQL Server to use specific join types using query hints, you add the OPTION clause at the end of the query, and use the keywords LOOP JOIN, MERGE JOIN or HASH JOIN.

Try executing the above queries with different join hints and see what happens:

Summary

Nested Loops

  • Complexity: O(NlogM)
  • Used usually when one table is significantly small
  • The larger table has an index which allows seeking it using the join key

Merge Join

  • Complexity: O(N+M)
  • Both inputs are sorted on the join key
  • An equality operator is used
  • Excellent for very large tables

Hash Match

  • Complexity: O(N*hc+M*hm+J)
  • Last-resort join type
  • Uses a hash table and a dynamic hash match function to match rows

Resources

The following resources were used for the making of this post:

SQL Query: Find all indexes, fragmention, and rebuild in the Database

–Find all the indexes in the database, find the index fragmention size in percentage, reorganize or rebuild indexes

— Find the average fragmentation percentage of all indexes
— in the HumanResources.Employee table.

–avg_fragment_in_percent=(number of recrods*record size)/(total page used*page size)

Generate Scripts for database objects with SMO for SQL Server

USE [DBName];
GO

SELECT f.object_id, s.name AS SchemaName, o.name AS TableName, i.index_id, i.name AS IndexName, p.rows AS TableRowCount, page_count, fragment_count, avg_fragment_size_in_pages, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N’DBNAME’), NULL, NULL, NULL, NULL) AS f
INNER JOIN sys.indexes AS i
ON f.object_id = i.object_id
AND f.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id=o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id=s.schema_id
INNER JOIN
sys.partitions p
ON i.object_id=p.object_id
AND i.index_id = p.index_id
WHERE o.type=’U’ –include only user table indexes
AND i.index_id>0 –exclude the null indexes
ORDER BY SchemaName, TableName, IndexName
;
GO

 

— Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee
REBUILD WITH (ONLINE = ON);  –rebuild works better than reorganizing index
GO

 

Defragmenting compacts as well as changes physical order

Index maintenance compacts a structure *and* changes physical order– both can be important!

Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption.

Once up on a time, there was a database server with 500GB of data and a heavy read workload of dynamic queries. Data was updated frequently throughout the day and index tuning was a serious challenge. At the best of times, performance was dicey.

Then things went bad

Application performance plummeted. Lots of code changes had been released recently, data was growing rapidly, and the hardware wasn’t the absolute freshest. There was no single smoking gun– there were 20 smoking guns!

A team was formed of developers and IT staff to tackle the performance issue. Early in the process they reviewed maintenance on the database server. Someone asked about index fragmentation. The DBA manager said, “Of course we’re handling fragmentation!” But a few queries were run and some large, seriously fragmented indexes were discovered in production.

The DBA explained that fragmentation wasn’t the problem. She didn’t have automated index maintenance set up, but she periodically manually defragmented indexes that were more than 75% fragmented.

Bad, meet ugly

At this point the whole performance team flipped out. Trust disappeared. Managers squirmed. More managers were called in. The DBA tried to change the subject, but it was just too late. More than a week was wasted over Fragmentation-Gate. It was a huge, embarrassing distraction, and it solved nothing.

Here’s the deal– the DBA was right. Fragmentation wasn’t the root cause of the performance problem. The DBA was a super smart person and very talented at performance tuning, too! (And no, I’m not secretly talking about myself here– this is not the “royal she”.) But she made a strategic miscalculation: she should have set up occasional automated index maintenance to align with her team’s normal practices and standards.

Why you need automated index maintenance

When performance gets bad, one of the very first things people look at is whether systems involved are configured according to best practices. If you’re not following a best practice, you need to have a really good reason for it.

Regular index maintenance still has a lot of merit: even in Shangri-La, where your data all fits into memory and your storage system is a rockstar with random IO, index maintenance can help make sure that you don’t have a lot of empty space wasting loads of memory.

It’s still a good idea to automate index maintenance. Absolutely don’t go too crazy with it– monitor the runtime and IO use and run it only at low volume times to make sure it helps more than it hurts. Be careful, but don’t skip it.

How much downtime can you spare?

Before you implement index maintenance, find out how much time tables can be offline in each of your databases. Then, figure out what operations you want to use.

To Rebuild or Reorganize: That is the Question

First off: ‘Reorganize’ and ‘Rebuild’ are two different operations that each reduce fragmentation in an index. They work differently toward the same end. You don’t need to run both against the same index. (I sometimes find that people are doing both against every index in a maintenance plan. That’s just double the work and NOT double the fun.)

Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).

Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Factors to consider:

  • Standard Edition rebuilds ain’t awesome. If you’ve got SQL Server Standard Edition, index rebuilds are always an offline operation. Bad news: they’re also single-threaded. (Ouch!)
  • Enterprise Edition rebuilds have gotchas. With SQL Server Enterprise Edition, you can specify an online rebuild — unless the index contains large object types. (This restriction is relaxed somewhat in SQL Server 2012). You can also use parallelism when creating or rebuilding an index— and that can save a whole lot of time. Even with an online rebuild, a schema modification lock (SCH-M) is needed at the time the fresh new index is put in place. This is an exclusive lock and in highly concurrent environments, getting it can be a big (blocking) problem.
  • There’s a bug in SQL Server 2012 Enterprise Edition Rebuilds that can cause corruption. If you’re running SQL Server 2012 SP1 – SP2, parallel online index rebuilds can cause corruption. Read about your options here.
  • Rebuilding partitioned tables is especially tricky. You can rebuild an entire partitioned index online– but nobody really wants to do that because they’re huge! The whole idea behind horizontal partitioning is to break data into more manageable chunks, right? Unfortunately, partition level rebuilds are offline until SQL Server 2014.
  • Reorganizing can be pretty cool. ‘Reorganizing’ an index is always an online op, no matter what edition of SQL Server you’re using. It doesn’t require a schema mod lock, so it can provide better concurrency. Reorganizing only defragments the leaf level of the index. On large tables it can take longer than a rebuild would take, too. But as I said above, it’s nice that you can reorganize for a while and then stop without facing a massive rollback.

SQL SERVER 2014: WAIT_AT_LOW_PRIORITY, MAX_DURATION, AND ABORT_AFTER_WAIT

I’m really excited about new index rebuild options that are shipping in SQL Server 2014. Check *this* out:

  1. ALTER INDEX OhSoFragmented ON dbo.MyTable REBUILD
  2. WITH (
  3. ONLINE = ON
  4. (WAIT_AT_LOW_PRIORITY
  5. (MAX_DURATION= 5, ABORT_AFTER_WAIT=BLOCKERS)
  6. )
  7. );

So we’ve got new tools for those concurrency problems I was talking about with online rebuilds. Now, we can say how long we’re willing to wait to get that schema modification lock (in minutes), and if we have to keep waiting what should happen. (Options: kill those who are blocking us, kill ourself, or do nothing.) Obviously there are some situations where just killing the blockers could be a terrible problem, but I’m interested to experiment with this.

You Didn’t Answer the Question: Do I Use Rebuild or Reorganize?

Yeah, I totally dodged that question, didn’t I?

If you have a regularly scheduled downtime every weekend, you’re probably fine with straight up index rebuilds, even if you have Standard Edition. Single threaded offline index maintenance may not be the hottest thing in the world, but hey, if you’ve got time for it then embrace the simplicity.

If you have Enterprise Edition, embrace parallel index rebuilds– and use the ONLINE option for indexes that allow it if people need to access the database during your maintenance window.

If you have database mirroring or AlwaysOn Availability Groups, tread with caution– particularly with rebuilds. It’s easy to generate a ton of IO with index maintenance, and it could mean putting your secondaries or mirror so far behind that they can’t catch up.

Maintenance plans or custom scripts?

You can go the easy way and use SQL Server Maintenance Plans, but unfortunately they’re very simplistic: you can only say “rebuild all the indexes” or “reorganize all the indexes”. You cannot say, “If the index is 45% or more fragmented, rebuild it– otherwise do nothing.” If you don’t spend much time with SQL Server and you’ve got downtime available every weekend, this can be a decent option.

If you need to minimize downtime, custom index maintenance scripts are the way to go. Our favorite: Ola Hallengren’s maintenance scripts. These are super flexible, well documented, and … free! The scripts have all sorts of cool options like time boxing and statistics maintenance.

Some tips for using Ola Hallengren’s index maintenance scripts:

  1. Download and configure them on a test instance first. There’s a lot of options on parameters, and you’ll need to play with them.
  2. Get used the ‘cmdexec’ job step types. When you install the scripts you’ll see that the SQL Server Agent jobs run index maintenance using a call to sqlcmd.exe in an MSDOS style step. That’s by design!
  3. Use the examples on the website. If you scroll to the bottom of the index maintenance page you’ll find all sorts of examples showing how to get the procedure to do different useful things.

Find out when maintenance fails

Don’t forget to make sure that your maintenance jobs are successfully logging their progress. Set up Database Mail and operators so jobs let you know if they fail.

Tell your boss you did a good thing

Finally, write up a quick summary of what you did, why you chose custom scripts or maintenance plans, and why. Share it with your manager and explain that you’ve set up automated index maintenance as a proactive step.

Having your manager know you’re taking the time to follow best practices certainly won’t hurt– and one of these days, it just might help you out. (Even if it’s just by keeping everyone from following a red herring.)