SQL Server Database Administration

ER - She canny no take it Captain!

2007-10-08T15:06:00Z

In Star Trek it was pretty obvious when the Enterprise had problems; it was always something to do with the Warp-Drive anyway. On production systems, its normally SQL Server Enterprise having problems, but when your ship is going down, what should you check? Heres a list, we've cobbled together. Fire up Task Manager immediately. Then come back to this after it's built up a history; when you've checked over the other usual suspects, come back to here and check out CPU Usage, if its too low, this could...(read more)

SQL Server 2000/2005 Failover Clustering 101 by MS

2007-04-05T17:26:00Z

Had a funny problem at work to do with clustering, we're not entirely sure if there was a problem with the cluster because we removed Builtin\Administrators. I was the third person brought in to resolve this problem so I'm not quite sure what fixed it. I find clustering kinda scary but also really interesting. Anyways, two links if you're interested

SQL Server 2000 Failover Clustering

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx

SQL Server 2005 Failover Clustering White Paper ( for download )

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

The problem we had was related to this...

BUG: IsAlive check does not run under the context of the BUILTIN\Administrators account in SQL Server 2000 Enterprise Edition

http://support.microsoft.com/?id=291255

Stored Procedure or System Procedure?

2007-03-24T10:51:00Z

If you could somehow link all the SQL Servers in the world together and then capture a Profiler trace on it, it would be extremely interesting to analyse the results. I bet table scans and therefore missing indexes would be quite high on this list. I wonder if recompilation of stored procedures called sp_ would be in the top ten culprits, eating up precious CPU cycles and causing wait time? I've been using SQL Server since 1999 as a Developer/DBA and it's easy to be cynical about developers and the way they code queries. I have lost count of the number of times I've seen developers create user stored procedures called sp_ not knowing that this forces recompilation ever time the query's run. I started thinking about why they did this, I mean, it happens a lot.

1. Microsoft call their own system stored procedures sp_, so that they recompile when they're executed. Developers see this and think it's a standard and trying to be good developers, replicate this Microsoft standard.

2. Developers like to adhere to naming conventions to make coding easier. I expect they think that sp_ means stored procedure but I wonder if it actually stands for system procedure? Surely though they should be called SSP (System Stored Procedures?)

3. They just don't know, and no-one has ever told them. It's eays to criticise other people's lack of knowledge, but I've seen this countless times. How can they all be uninformed? Maybe they don't have a DBA or SQL developer on site.

Thinking about this, I think Microsoft are slightly at fault here because of the reasons above. People who develop in SQL Server may not have the time to pick up a decent programming book and start learning the nuances of the product, they have to get a solution from SQL within the whole project they're working on and also to a deadline. Is it any surprise they don't pick up a DBA or SQL Server programming book? Perhaps if they did then I would be out of a job. Microsoft have done a very good job at creating a self-tuning RDBMS, however, I can't help but think the easiest solution to this problem is when someone creates a stored procedure called sp_, the informational message should be 'Stored Procedures called sp_ will be recompiled every time they are run and this may affect performance'. Surely this would force even the most reluctant developer to investigate this?

I'm off to the SQL Server Connect website and logging this recommendation. If Microsoft added this in a future Service Pack, I wonder if that World-Wide Trace would get any smaller?