My two cents

One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several SQL 2000 and some SQL 2005) is the sys.dm_db_missing_index* DMVs. As the query processor evaluates queries, it detects if that specific query could benefit from an index and how much it expect that index would reduce the cost (in terms of IO), exposing these information as views that we can query. I won't delve in the structure of it, but you can use my procedure sp_dba_missingindex as an example (based on Bart Duncan's work). It will return the suggested CREATE INDEX statements along with the expected improvement information, like this:

USE AdventureWorks
exec sp_DBA_MissingIndexes

GO

create_index_statement
improvement_measure                     unique_compiles      user_seeks           user_scans           avg_user_impact        avg_system_impact      last_user_seek          last_user_scan

Nevertheless, I don't like to put an index in my schema without knowing exactly which query it is supposed to improve. How would I know if it actually helped? How can I test it? Am I sure it won't make matters worse?

So, inspired by Greg Linwood procedure to find which queries are doing large index scans, I thought that I could use tweak that idea to answer those questions. The trick is that SQL Server query processor will include the missing index information in the showplan output as well, so I can parse the showplan XML for occurrences of missing indexes entries on the table I want. This way I an see the actual query and judge if that is indeed the correct index or if adjustments are necessary (the code is available to download here):

USE AdventureWorks
EXEC sp_DBA_QueriesMissingIndexes 'SalesOrderHeader',1

EXEC sp_DBA_QueriesMissingIndexes 'SalesOrderDetail',1
GO

Statement
Stored Procedure
query_plan
execution_count      min_logical_reads    max_logical_reads    avg_logical_reads    min_logical_writes   max_logical_writes   avg_logical_writes   min_worker_time      max_worker_time      avg_worker_time      min_elapsed_time     max_elapsed_time     avg_elapsed_time

(1 row(s) affected)

Statement
Stored Procedure
query_plan
execution_count      min_logical_reads    max_logical_reads    avg_logical_reads    min_logical_writes   max_logical_writes   avg_logical_writes   min_worker_time      max_worker_time      avg_worker_time      min_elapsed_time     max_elapsed_time     avg_elapsed_time

(1 row(s) affected)

The obvious limitation of that procedure is that it can only pinpoint queries that still have its plan stored in SQL Server cache. I found that, specially for low-cost queries, SQL tends to throw away the plan as soon as it finishes executing it. Setting "PARAMETERIZATION FORCED" for the database helps to keep query plans in the cache for more time. In "real life" scenarios it seems to find the "villains" pretty often.

I am not finished with it yet, as it is a nice excuse to learn XQuery in SQL Server and there are some quirks in the logic to extract the statement text (the open bracket at the beginning) that I want to fix. But I think it can be useful as it is, so I am making it public.

I also wouldn't mind getting some feedback on them. :-)

Yesterday I attended the SQL Server focused conference called SQLBits, which this year took place in Birmingham. First of all, many thanks to Tony Rogerson, Simon Sabin and all organisers. You did a hell of a good job!!!

As a suggestion for the next ones, I would find useful to have an indication if it is a session intended for beginners or if it will be more in-depth sessions. I got a couple of sessions "wrong" because I misjudge it from the Session title and description.

In this post and in the following I will post a summary of what I managed to grasp from the presentations I attended:

1 - Administering SQL Server with PowerShell

Richard Siddaway gave a quick but instructive overview on PowerShell with enough samples for us to get the "feel"of the language. He was also very honest and pointed a lot of quirks and pitfalls he (and the community) experienced.

He also showed us the version of PowerShell included in SQL Server 2008. The integration of SQL Server in the shell is well done, navigating through the objects hierarchy using "DOS" commands like "cd" and "dir" is very cool :-). It also includes support in SQL Agent for PowerShell tasks.

But I am not happy with the fact that the SQL Server team needed to make their own "SQL" PowerShell version. I mean, do we really need the hassle of testing our scripts and functions for several (potentially incompatibles) variations of the shell? There are times when Microsoft REALLY gives me the impression that their dev teams don't talk to each other.

I looked at PowerShell last year but it did not impressed me at that time. I mean, it is more elegant than VBScript and I suppose it is a lot more powerful, but until it is more widely available I am not tempted to use it (the idea of having to convince security and admin guys to allow me to install it on every server does not entice me at all). But now that it is included in Windows 2008, the time for me to revisit it might be coming.

The only thing that keeps bothering me is performance. It was painfully slow during the demo. I know, Richard had lots of VMs and other stuff running at the same time, but it didn't looked good. The fact that one of the recommended books showed a turtle in the cover did not cheered me up as well. :-)

2 - What are you waiting for?

Christian Bolton's session was very interesting. I already use wait statistics as one of my tools to diagnose performance problems, and it has proved itself to be very effective time and time again.

Nevertheless, as my bread and butter still is SQL 2000, I had not delved too much in the DMV's related to waits. Basically you will look at:

sys.dm_os_waiting_tasks - exposes data from all tasks that are currently waiting for some resource. Much better than SQL Server 2000 sysprocesses lastwaittype and waittime columns

sys.dm_os_wait_stats - It is the evolution of DBCC WAITSTATS. As it's predecessor, it accumulates data since last reboot. You can clear it by running DBCC SQLPERF ('sys.dm_os_wait_stats',clear), although I rather write procedures to calculate and show a delta.

A couple of tools you might use to look at statistics was also briefly presented. I already use Performance Dashboard, tough I have not installed yet the DMVStats and Christian inspired me to give it a go. Maybe this could turn into a new blog post later?

He talked about SQLNexus, but did not showed it working, but I confess I think it is a bit overkill and too complex for my taste. Now that unfortunately we lost Ken Henderson, who was deeply involved in it I decided to wait a bit to see how it develops before trying it again.

Reviewing DBCC CHECKDB syntax for SQL Server 2005, I found a [DATA_PURITY] option added to its syntax.

This option enable the following checks on each column value of those datatypes:

Unicode character - The data length should be a multiple of 2.

Datetime - The days field should be between Jan 1 1753 and Dec 31 9999. The time field must be earlier than '11:59:59:999PM' .

Real and Float - Check for existence of invalid floating point values like SNAN, QNAN, NINF, ND, PD, PINF.

For a database created on SQL Server 2005, these checks are always performed, but upgraded databases won't do it until you explicitly call using that option. If it reports no errors, then all subsequent CHECKDB in that database will perform those checks automatically.

Add this in your migration checklist.

KB923247 has all the details about it. The guys at mssqltips.com also have a good article about it.

Some days ago, my boss got on my desk asking if I could take a look at one database that was not opening. That DB was in a virtual machine used for our sales presentations.

For some reason, several files got corrupted in that VM, and between those was our beloved transaction log file.

It was a perfect opportunity to sharpen my database recovery skills, there was quite some time that I didn’t played with those tools and to be honest I had forgot almost everything.

So, as I will probably forget about it soon (thankfully, SQL Server does not crash that often), and as there is quite some time that I don’t post anything here, I think it will be good to blog about it.

The error recorded in the log was:

Error: 9004
An error occurred while processing the log for database AnyDB.

I love clear messages like that. This helps a lot to identify what caused the problem, right? No other meaningful error on SQL Server Errorlog nor in EventViewer.

A quick look at google confirmed my suspicion:

Time to restore from our backups.

To be honest, this is what I would do in any critical database, but where is the fun it that? :-)

So I went for the not recommended neither supported procedure in SQL Server 2000, which is:

1 - Enable updates to system tables

EXEC sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

2 - Save the current status for your database

SELECT dbid, name, status

FROM sysdatabases

WHERE dbname = 'test'

3 - Set it to “Emergency” mode (Status = 32768)

UPDATE sysdatabases

SET status = 32768

WHERE dbname = 'test'

4 - Recreate an empty log

DBCC REBUILD_LOG (test,'<PATH>\test.ldf')

5 - Check if your database is physically ok

ALTER DATABASE test SET SINGLE_USER

DBCC CHECKDB (test)

6 - Change your DB status to something “neutral”

UPDATE sysdatabases

SET status = 28

WHERE dbname = 'test'

7 - Disable updates to system tables

EXEC sp_configure 'allow updates',0

RECONFIGURE WITH OVERRIDE

8 - Restart SQL Server

In SQL Server 2005, the procedure is much easier, and tough obviously still not recommended, it appears to use only supported commands:

ALTER DATABASE test SET SINGLE_USER, EMERGENCY

DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER, ONLINE

Nice, huh? Obviously, both procedures will leave your database in a not-so-good shape because losing your transaction log, means losing the ability to undo uncommitted work and redo committed work that was not yet copied to your data file. So you should not trust anymore the integrity of you data.

Even so, I believe that there are times when this is worthwhile. If you don’t have a good backup and/or you are dealing with development databases this might be a useful last resort.

For more details, see Paul Randal's blog (always worth reading).

This one isn't related to SQL Server. But do you know when sometimes you get stuck in an almost infinite loop of dialog boxes and for some reason you don't want to kill the application?

"Sorry <beep> guys, this <beep> should <beep> end in <beep> any mome<beep>nt now <beep>."

Well it happened to me these days and I decided that enough was enough and I would request the help of Saint Google!

It turns out  that at least on XP and Vista there is a nice registry key at: [HKEY_CURRENT_USER\Control Panel\Sound] called, have a guess: "beep". Change it to "no" and logoff / logon.

No more awkward noises since then....

These days, investigating a fairly simple insert which was taking longer than what I would consider reasonable for that particular piece of code, I explored several possible causes. One thing that raised my attention was that the "Pages split/sec" perfmon counter was constantly above 0, so I began to wonder which index was causing most of it. I ended up reading an interesting blog entry from Greg Linwood which inspired me to write a small stored procedure to monitor it for a longer period:

USE master
IF OBJECT_ID('dbo.sp_dba_CheckPageSplits') IS NOT NULL
  DROP PROC dbo.sp_dba_CheckPageSplits
GO
CREATE PROCEDURE dbo.sp_dba_CheckPageSplits (@MaxLSN varchar(255)= NULL OUTPUT)
AS
/*
Author: Leonardo Pasta
Date: 13/08/2007
Summary: List the objects that are suffering most from PAGE SPLITS.
         This procedure is compatible with SQL 2000. To use it on SQL2005, replace "[Object Name], [Index Name]" with "[AllocUnitName]"
*/
  SET NOCOUNT ON
  IF @MaxLSN IS NULL
    SET @MaxLSN = ''

  RAISERROR('Verifying logs greater than %s',1,1,@MaxLSN) WITH NOWAIT
  SELECT TOP 15 [Object Name], [Index Name], Context, count([Current LSN]) [Splits]
  FROM ::fn_dblog(default,default)
  WHERE Operation = N'LOP_DELETE_SPLIT'
    AND [Current LSN] > @MaxLSN
  GROUP BY [Object Name], [Index Name], Context
  ORDER BY [Splits] DESC

  SELECT @MaxLSN=MAX([Current LSN])
  FROM ::fn_dblog(default,default)
GO

Then ran something like the infinite loop below to get a quick & dirt report of the worst offender indexes:

DECLARE @MaxLSN varchar(255)
WHILE 1=1
BEGIN
  EXEC dbo.sp_dba_CheckPageSplits @MaxLSN OUTPUT
  WAITFOR DELAY '00:00:30'
END

To be honest, I don't believe that my IO problems will be explained by page splits, but I think this will be a valuable tool to fine-tuning our indexes strategies in the long run.

* CCTV - Closed Circuit Television. Widely used in UK (and for sure in many other parts of the globe) to monitor against social misbehavior.

Policy Manager

A policy manager is one important piece in the centralized administration puzzle. The general idea is to define a set of rules and have it enforced consistently for all related objects (servers, databases, tables, etc) you manage.

Databases, for example, let’s say you have a (sensible) rule that prohibits databases with Auto-shrink ON. How can you be sure that while you were enjoying your holidays the junior DBA didn’t enable it in a new database? Or as happened to me in the past, the setup of a third-party software configured it automatically.

That’s the goal of a police manager, ideally prevent breaking the standards or at least notify you when it happens. And that is what Microsoft promises for us in the next release of SQL Server, under the name of “Declarative Management Framework”.

In the next days, I am planning have a go on it and will post my impressions here.

So, let’s start by a little theory, there is a brave new world of new expressions and terms waiting for us in the Katmai, let’s look at some of them:

Terminology

Declarative Management Framework – Fancy name for the Policy manager.

DMF target – Any entity managed by a DMF policy, like instances, databases, tables, etc.

DMF facet – A group of related configurable parameters. Examples are: database facet, Linked Server Facet and Surface Area Facet.

DMF condition – A specific configuration created on top of a facet.

DMF policy – Defines how (execution mode) and where (possible targets) a defined condition will be applied

DMF policy group – A user defined group of policies that can be subscribed at once by a target. All targets subscribes to the default group, and you can create specific ones as you need.

DMF execution mode – When and how the policy should be enforced. Policies can be checked periodically (on schedule), at modification time (on change) or can be enforced at modification times. The last two isn’t available for all types of policy.

Effective policies – Are the set of policies active for a specified target.

Test drive

My first intention is to try the basics, i.e. test the feature on it’s most basic form, so let’s go:

1 – Add a condition:

The test will be to create a rule to enforce a naming standard, in this example I have decided that the name for our stored procedures should begin with "usp" (which stands for User Stored Procedure)

2 – Add a police:

Note that I have chosen to:

· Place the police under the default police group, so that I don’t need to take any additional step for it to be available on all databases on the server.

· enforce the police, this isn’t available for all conditions:

3 – Test it

After that encouraging error message, time to google. Didn’t took long to notice that I had omitted the following bugfix (pretty normal considering it is a CTP1):

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe -Vr Microsoft.SqlServer.DmfSqlClrWrapper,89845DCD8080CC91 followed by instance recycle (See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1704822&SiteID=1 )

4 – Test again

This time at least I appears to be a “normal” error, this is the error that you should expect when you violate a police, but the error was raised even for the “good” procedure.

I turn out that I should not include quotes around the LIKE pattern.

5 –Test it AGAIN:

Success at last.

So, here we are, proud owners of a standardized database.

I expected that my first “real” post would be about one of the features that had more appeal to me on SQL Server Katmai, but I will have to post about a discovery that was so useful to me, and yet so basic that I am almost ashamed to have found it only today. So let’s put aside my desire to pretend I knew it for a long time and post it, after all I hope that there is at least a couple of DBAs who still don’t know it:

Having beginning to work with VLDB recently, I am more involved in optimizing long running processes. So it is very useful to print the timings of the events as they occur. Since my Sybase days I always used the plain old “PRINT GETDATE()”.

But this will only print the output at the end of the batch, and when you are optimizing a job that takes 4 hours to complete, you get pretty anxious to know what the hell he is doing at any given time, even to decide if its better to cancel and try another change.

The solution is simple, instead of PRINTs, use:

DECLARE @Message varchar (100)
SET @Message = '<Step Description>: ' + CONVERT (CHAR (20), GETDATE (), 8)
RAISERROR (@Message, 0, 1) WITH NOWAIT

And you get the output as soon as they are generated.

By the way, the WITH NOWAIT option is properly described in the good old BOL.

Ok, I confess, I always wanted to be part of the blog community, but I have never found a compelling reason to fight the inertia and start blogging.

But after Tony Rogerson invitation on http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/06/22/sqlblogsters-first-quarterly-meal-in-london.aspx I could not resist the temptation (not that I like KFC that much).

So, I have decided to blog about my tests on SQL Server “Katmai” (the 2008 version, if the usual delays don’t end up forcing a name change).
I am more of an OLTP DBA, so don’t expect much from the Analysis Server side of things.

Besides that, I hope to get from time to time some inspiration from my duties as a DBA in a ISV focused on the financial sector. 

Well, let's start some BOL readings and testing as there are many things to try out.