Browse by Tags

Server Side Aliases
07 November 2010 21:37

Over the years i have come across a few situations where server side connections to SQL server fail when you use a DNS alias that points back to the server your initiating the connection from but you can connect remotely. Its an annoying problem which has a very unhelpful error message that changed in different versions of SQL. In SQL 2000 you are presented with... Read More...

by ACALVETT | with no comments

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

The evils of implicit conversions
18 January 2009 17:18

I wanted to put up a brief post showing the impact of an implicit conversion on the performance of a query (or not......). In the example i will show an implicit conversion negatively impacting query performance and an implicit conversion that does not impact performance..... So, we need to setup the test environment using the code below. SET ANSI_WARNINGS OFF... Read More...

by ACALVETT | with no comments

Filed under: SQL 2005, SQL 2000, Tips, Performance

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 | 9 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