My two cents

Which queries are missing indexes?

2008-05-08T20:48:28Z

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...(read more)

SQLBits

2008-03-02T21:47:24Z

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...(read more)

Data purity

2007-11-26T22:39:41Z

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...(read more)

Losing your Transaction log

2007-11-20T00:17:50Z

Technorati Tags: SQL Server , Recovery 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...(read more)

The sound of silence

2007-09-06T22:14:01Z

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...(read more)

Page splits

2007-08-13T20:36:07Z

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...(read more)

Database CCTV*

2007-07-10T21:21:00Z

* 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...(read more)

Instant gratification

2007-07-02T23:14:49Z

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...(read more)

Getting on board

2007-06-25T22:50:00Z

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...(read more)