Skip to main content


Showing posts from January, 2010

Professional Association of SQL Server (PASS)

I recently discovered the Professional Association of SQL Server (PASS) virtual chapters. For those of you who struggle to make chapter and user group meetings (like me) this can be an excellent way of getting some excellent SQL Server training. I only came across the virtual chapters recently. There are several virtual chapters including one on Database Administration, Virtualisation, Performance etc there are about seven in total but these three are ones that interest me at the moment. The virtualisation chapters next online meeting will be held on 9th Feb @ 1700 GMT (1200 Eastern) the presenter is Brent Ozar (twitter | Blog) and I intend to make every effort to make this meeting. You can get further details here: I saw Brent present a session at the SQL BIT community day at the end of last year and his session was excellent so I think this will be my first live event.The other good thing about the virtual chapters, well for the DBA chapter anyhow,…

SEO my SQL Server Blog

I was going to spend today finishing off some SQL Server related blog posts, I currently have a list of four or five titles saved as draft that I am keen to publish including “special data file pages”, “Restoring master DB”, “Locks” but instead I have spent the day looking at what small changes I need to make to my blog and other websites to make them search engine friendly (SEO friendly). My diversion was the result of an email I received yesterday from my good friend Mandeep Kaler who is an Internet Marketing specialist who kindly gave my sites the once over. He came back with a list of recommendations, and pointed me in the direction of site maps and meta tags amongst others. One of the main things he suggested, and to be honest I didn’t quite know how to take this, was to “Change my photo because I look kind of weird” so I have...hopefully I’m not scaring my readers away anymore!
A big thanks to Mandeep for the pointers.

Wildcards in WHERE Clauses

This is just a very short post regarding the use of wildcards in a WHERE ClauseIt is possible that the first WHERE clause below can use an Index seek Where LastName like ‘[a-z]%ent’ can use an indexThis second statement will not be able to use a seek and will result in a scanWHERE LastName like ‘%ent’


I run into a small issue at the end last week, a CHECKDB job was reporting corruption on one of my development databases:Msg 8964, Level 16, State 1, Line 1Table error: Object ID 965578478, index ID 1, partition ID 72057594042515456, alloc unit ID 72057594052083712 (type LOB data). The off-row data node at page (1:569), slot 0, text ID 1881079808 is not referenced. After an initial hiccup and slight panic I started with the SQL Server error log which had entries similar to the aboveI won’t go into all the details in this post but after troubleshooting for a short while and reading a few posts and articles including Gail Shaw’s article on SQL Server Central ( and Paul Randal’s blog ( I discovered it was known issue.Earlier in the week I had created an XML index on one of the tables and since its creation an index rebuild job had run on the newly created XML index.I wanted to note down this URL for fut…

Instant File Initialisation

I have been reading up on instant file initialization and I have read a couple of good blog posts by Kimberly Tripp( ), Paul Randal ( and Cindy Gross. From SQL Server 2005 onwards if you grant the SQL Service Account “Perform volume Maintenance tasks” then with the necessary permissions SQL Server, if circumstance permit, can take advantage of not having to zero out a file allocation. (NOTE: This does not work on the log file) which can greatly enhance the speed of operations like data file growths (either initiated automatically or manually), database creation, and probably most importantly database restore operations.Cindy Gross in her post ( recommend that you give permission to the appropriat…

DDL Triggers

We had a request from a developer who wanted to access data from a source database and another database on the same server. We needed to ensure that the underlying tables are not changed in a way that would impact on the view in either databasePrior to 2005 I would have to have done this using a myriad of views and the schemabinding option. Since SQL Server 2005 you can use DDL triggers to fire when certain DDL statements, like CREATE TABLE, ALTER TABLE, DROP TABLE are fired.DDL triggers focus on changes to the definition of the database schema/object rather than actual data. DDL triggers can be scoped at database level and server level. The server scoped triggers apply to server objects such as logins and the former are database scoped and apply to database objects such as tables and indexes. The following trigger fires for every DROP TABLE or ALTER TABLE execution and stops any table changes being made without first disabling the trigger. CREATETRIGGERsafetyONDATABASEFOR DROP_TABLE…

January Roundup

I have just put together the January newsletter for and scheduled it to run Sunday night/Monday morning. It is a little late this month because of the Christmas holidays hopefully we get back into sending them at the start of month from next month. If you would like sign up you can do here.

I’m starting work on a “Configuring Database Mirroring” article which will be a nice supplement to JHD’s Introduction to Mirroring article. This will be published in the next few weeks.

Lock configuration setting and preventing locking contention

The locks configuration option for SQL Server controls the number of locks available in SQL Server. You can view this through Management Studio by right clicking on the Server in object exploer, selecting and selecting the tab. The default is 0 which allows SQL server to allocate and de-allocate locking structure dynamically. Initially SQL server allocates enough memory for a pool of 2500 locks (Each lock structure consumes 96 bytes)
It is recommended that you leave the locks configuration set to 0 to allow SQL Server to manage lock structure dynamically.Lock contention is one of the things that can causeyour SQL Server to perform poorly, but unlike causes like poorly written queries, Poor database and index design which will cause poor performance regardless of the number of users on the system. Lock contention becomes more of a performance problem as the number of users increase.
Tips on avoiding locking contention:·Keep transactions as short and concise as possible·Keep statement…

Permon Counters Missing Windows 64 Bit SQL Server 32 Bit

For reasons beyond the scope of this post I have an environment where I have Windows 2003 R2 64 bit running but SQL Server 2005 running on 32 bit.

Having fired up perfmon I could not see the SQL Server 2005 counters which left me in a state of confusion. Having had a quick google I found a very good post from Tony Rogerson, this can be found here.

I had fired up the 64 bit version of perfmon so the 32-bit counters for SQL Server do not appear. To get around this you need to open the 32 bit version of Perfmon which you can do by running the following:

mmc /32 perfmon.msc

Having fired up this version of PerfMon all the SQL counters were available to me including the named instance counters listed under the name of the instance.