Browse by Tags

The overhead of a non-unique clustered index
20 October 2008 00:21

So, we all know that if we create a clustered index that is not unique that we will incur a 4 byte overhead right? Well not always because as usual, it depends..... When you create a non-unique clustered index SQL server must maintain uniqueness so it adds a hidden 4 byte column which is populated for each non-unique row (not every row) but what many people may... Read More...

by ACALVETT | 3 comment(s)

Filed under: SQL 2005, SQL 2000, Tips, Database Engine

My old mate sp_recompile
12 October 2007 11:33

As soon as i saw the error messages in the logs i thought to myself "Oh my, that did not happen in testing" (ok, maybe it was more colourful than that). We were creating a clustered index on a tiny little table and the index went through fine. However, the application started to generate the message " Could not complete cursor operation because the table schema... Read More...

by ACALVETT | 2 comment(s)

Filed under: SQL 2005, SQL 2000, Tips, System Procedures

Server level VLF report
25 July 2007 23:27

I read Tony Rogerson's blog on Virtual Log Files today and it reminded me that i really should knock up a little report to list all databases on a server and the number of VLF's per database. Since I had been busy writing some other Operational reports I was in the right frame of mind so knocked up what you see below. The Report code CREATE TABLE #VLFS ( fileid... Read More...

by ACALVETT | 2 comment(s)

Filed under: SQL 2000, Tips, Reports

What is that SPID doing?
27 March 2006 17:44

This is certainly not a new topic but something that i felt was worth revisiting and reminding people about, especially because i find many people are not aware of the ability to extract the statement within a stored procedure being executed. So, prior to SQL 2000 SP3, when we want to know what code a SPID is executing we have to use DBCC INPUTBUFFER (). The... Read More...

by ACALVETT | with no comments

Filed under: SQL 2000, Tools, Tips

A performance analysis example
15 January 2006 17:53

Last week i was dragged kicking and screaming away from my allocated SQL 2005 days to spend 2 days diagnosing performance problems on one of the firms Systems and write a report. Due to the sensitive nature of the data on the system it has been managed by another team and this was the first time i had looked at it. I thought i'd share my thought process to identifying... Read More...

by ACALVETT | with no comments

Filed under: SQL 2000, Tips, Performance