Recently I was reviewing one of our supported instances and I saw a bunch of databases with 1MB autogrowth on the data file.  Most were quite new, and some had grown to quite a large size.  Paul Randal deals with ‘the default’ settings for autogrowth in this blog post, and says 1MB is not a good option.  No surprise there as for a highly transactional system you are going to have the data file growing all the time.  So I want to change it, but I also want to understand where it came from.

This is a managed server, so I know the model database is not going to have 1MB autogrowth set up, but just to be sure I check and confirm.  So if it’s not getting the default setting from the model database, where is it coming from?  Time to test. So when I use the Management Studio GUI it pulls settings from the model database as I’d expect.  And when I script those out using the ‘SCRIPT’ option in the create database wizard it faithfully transfers all the settings.  But what about if I just use ‘create database’ by itself.

CREATE DATABASE MODELTEST

Sure enough this grabs the default setting from when you install SQL Server – 1MB, and not the updated value in the model database.  I also noted it grabs the default data file size, but not the default log file size.  This was news to me, and led me to wonder what other settings were not being pulled from model.  So I changed a bunch of options in model on the options tab to test.

All of the other options I tested transferred properly.  Or at least as I anticipated.  So….nice one SQL Server…..you will transfer autoshrink being enabled but not transfer the log startup size or data growth settings.

From the moment I started playing with this SQL Server thing I have certain things repeated to me over and over again.  One of those is the importance of removing fragmentation from indexes.  There’s a whole bunch of stuff out there on the interweb on people rebuilding indexes and performance increasing, and so now…a few years further on in my journey it’s quite common for a client to come to me with questions about whether such and such an index has been rebuilt, or concern over fragmentation levels.  Increasingly I find myself explaining that rebuilding the index may help, but it’s quite an expensive operation, can cause blocking and may not be the problem anyway.

Now, I’m not saying that indexes shouldn’t be rebuilt or maintained.  Clearly they should, but there’s a bunch of other things that happen when we rebuild an index which may be what is actually resolving the performance issue that has raised the original flag.  Let’s look at them:

Statistics Are Updated

Whenever you rebuild an index, all the statistics related to that index are updated.  (Take Note:  This applies to rebuilding the index, if you are reorganising it the statistics are not automatically maintained).  Statistics are very powerful part of how SQL Server decides to access data.  Even with autoupdate statistics enabled statistics can get out of date really quickly, and query plans can be generated using incorrect information.  That can bring back information significantly slower than if the statistics are up to date.  The most common affect I see around this is around queries based on dates(and in particular that use a comparison against something like getdate() or CURRENT_TIMESTAMP).  Recently I saw a query which was running around 800 times a day and taking around 17 seconds per execution.  Without adjusting anything but updating the statistics that dropped down to 8 seconds.  Rebuilding the index would have resulted in the same performance improvement, but has a much higher impact on the server to achieve the same result.

Plans are recalculated

Related to the above, any plan that uses the index is recalculated based on the latest information – Fragmentation is removed(hopefully) and statistics are up to date, so SQL should choose a more appropriate plan.  Or it could be slow due to a parametrization issue which generated a plan based on one value and is now re-using the plan for other, less appropriate values.  Imagine a database which contained everyone in the worlds favourite colour.  If a plan is run the first time to get information for New Zealand population, and all subsequent queries are trying to get information about the US or Europe, SQL is obviously going to have trouble accessing the larger volumes of data in the same way it would for the smaller set of data associated with New Zealand.  Now, everyone in the world is going to be a big index to rebuild, where as in that case a recompile hint could see the same performance improvement.

So, in conclusion – yes your indexes need to be maintained, but if there is a performance issue on a server, it’s much better if you can narrow it down to a specific problem which can potentially be resolved surgically, than get out the second biggest hammer you have(behind restarting your server….and one day when I am brave enough I’ll post on what that is actually doing when it magically fixes your performance issues.)

I had a situation where a 500 GB drive needed to be repurposed and the…oh 20GB worth of databases on it needed to be moved somewhere else.  Even though this included the system databases this was not overly complicated and not something you can’t find instructions for anywhere else thanks to our friend google.  A little more complicated was that the default trace files were also being written to that location.  Although I suspect the worst result of leaving that location would be a failed message in the logs and no default trace, I didn’t want SQL to do anything silly like fail to start the moment it couldn’t write to the default trace.  It seems this is either such a blindingly simple thing to do that people haven’t posted a how to online, or that it’s not something people have a call to do much.  At any rate, I found this blog post on sqlconcepts that indicates you can’t change default settings for the default trace.  Luckily while that is true of the size or the number of files retained, it is a relatively simple matter to change the location.

Reading the microsoft article on the default trace it says that the default location is MSSQL\LOG which is technically true, and also led me to believe that the default trace was stored, by default, in the same location as the error logs.  You can change the location your error logs are written to by adjusting the location of your current error logs in your SQL Server startup parameters.  I tested this on my local instance(SQL 2012) and everything ran fine, switching to the new location after a server restart.  I guess this makes sense given that the default trace really is another sort of error log.

On a slight tangent due to the sql concepts post above:  If you want to retain more history than the default(5 20MB files – which can equate to not very much at all on a heavily used server) it is possible to do so by simply scheduling a job which copies the trace files to another location.  You can either just copy all *.trc files out of your errorlog location to your archive location, or if you want to get fancy and only copy the new files you can get the current file name with:

select path from sys.traces where id=1

So long as the default trace is enabled it should always have an id of 1 as it is started(and rolled) whenever SQL Server restarts.

I hope this is helpful to someone.

Sometimes it bother me how long it takes to throw a single switch which resolves a problem.  This was one of those times.

Here’s the scenario.  An availability group is set up and you want to log ship one of the databases to a designated DR server so you have both HA and DR at your disposal.  Everything sets up just as it should, but when you run the backup no file appears in your backup directory.  What’s going on?

For a while I went around in circles trying to second guess my log shipping setup.  Then I made a second database, successfully configured log shipping, but the moment I added it to the availability group it began exhibiting the same behaviour.  There are no errors in the logs, the backup job says it’s successful, but when you go into the error logs all you find is it is deleting old backups that have fallen out of retention.  No backup is made.  The databases last backup date does not change.

I went so far as reading the entire support notes on books online around availability groups trying to find anything to suggest this couldn’t be done.  Then I repeated my experiments to confirm that log shipping worked fine outside the availability group, but failed when it was added.  Finally I came to the conclusion that should have been obvious an hour earlier.  The problem must be something to do with the group set up.  Once I got there the likely culprit was clear – the availability group was trying to enforce a backup being taken off the secondary replica, and the log shipping job didn’t like that.  It seems to check if it can take a local backup, see that it can’t and so skip straight to the historic backup purge.

Here’s how to correct it.

1.  In management studio expand Always On High Availability and then Availability Groups.
2. Right click on your chosen group and choose properties.
3. In the left pane select backup preferences.
4. Select ‘Primary’ as your backup source.  Click Okay.

Now run your log shipping log backup job and everything should perform as expected.

This article was linked to from a recent Brent Ozar PLC newsletter.  It contains a bunch of tips about how to be a programmer(Not how to program, but how to be a programmer – the distinction is important).  I think it was absolutely brilliant and has huge areas of crossover to other technical roles…such as DBA’s.

(Dead Link)http://samizdat.mines.edu/howto/HowToBeAProgrammer.html?x#id2855381(Dead Link)

I particularly liked the sections on how to deal with difficult people, how to provide estimates, and perhaps best was knowing when to go home:)

I’ve recently had to look over a client server where a maintenance plan had been failing for some time.  The inhouse DBA was unaware of the failures because he had no alerting on the jobs and the big red crosses weren’t big or red enough to have caught his attention.  When I looked at the cause of the failures it was due to a native maintenance plan not being able to reoganise an index with page level locking disabled.  Whenever a reorganise plan hits a database with page level locking disabled it generates the following error:

Executing the query “ALTER INDEX Indexname] ON [dbo]….” failed with the following error: “The index “Indexname” (partition 1) on table “tablename” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

There are two ways of getting around this.  The first way is to generate TSQL scripts to enable Page level locking prior to the re-organise and then re-enable it after the maintenance is completed.  JP Chen has the scripts to do that here.  Note:  Haven’t tested those, but they look right.  I suspect you may need to exclude the system tables as well.

The second, and easier way, is to set up a rebuild step and move the affected databases into that step of your maintenance plan.  Rebuilds do not require a page lock because the index is completely rebuilt rather than shuffled around.  The TSQL to figure out which database you need to move from the re-organise to the rebuild step is:

exec sp_MSforeachdb ‘use ? select db_name(), (case when count(*)>0 then ”Page Locks Disabled:” ELSE ”Page Locks” END) from sys.indexes where allow_page_locks=0 and name not in(”queue_clustered_index”,”queue_secondary_index”)’

and if you want to get the specific indexes you can use this:

exec sp_MSforeachdb ‘use ? select db_name(), object_id,name from sys.indexes where allow_page_locks=0 and name not in(”queue_clustered_index”,”queue_secondary_index”)’

Standard disclaimer about sp_MSforeachdb being an undocumented stored procedure and maintenance plans being somewhat limited form of doing your index mainteance apply:)