Sunday, 31 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: http://virtualization.sqlpass.org/ 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, is the fact they record the sessions and put them online to download and view later, so if you miss one or you can’t make it, you don’t lose out, obviously you can’t make a contribution or ask any questions but you can benefit from the content. You can get to the DBA chapter here http://dba.sqlpass.org/

I have downloaded and viewed one of these sessions so far, the session was called:

End-to-End Troubleshooting for SQL Server 2005/2008

Presenter: Kevin Kline (Blog)

The session covered a Methodology for Troubleshooting SQL Server using the native SQL Server tools like Performance Monitor (Perf Mon), Profiler and DMVs etc. The highlight of the session for me was Kevin’s ‘brain dump’ of the perf mon counters and what the results mean.

Saturday, 30 January 2010

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.

Wednesday, 27 January 2010

Wildcards in WHERE Clauses

This is just a very short post regarding the use of wildcards in a WHERE Clause

It is possible that the first WHERE clause below can use an Index seek

Where LastName like ‘[a-z]%ent’ can use an index

This second statement will not be able to use a seek and will result in a scan

WHERE LastName like ‘%ent’

Sunday, 24 January 2010

XML Index - ALTER INDEX with REBUILD Issue

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 1

Table 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 above

I 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 (http://www.sqlservercentral.com/articles/65804/) and Paul Randal’s blog (http://www.sqlskills.com/BLOGS/PAUL/). 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 future reference:

This is the knowledge base article detailing the issue and possible workarounds.

http://support.microsoft.com/kb/974985

There is a cumulative update (CU) that fixes this issue and it is fixed in SQL Server 2008. One work around suggested and the one I’m currently using was to drop and recreate the index instead of using ALTER INDEX with REBUILD statement.

Thursday, 21 January 2010

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(http://www.sqlskills.com/blogs/kimberly/post/Instant-Initialization-What-Why-and-How.aspx ), Paul Randal (http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx) 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 (http://blogs.msdn.com/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx) recommend that you give permission to the appropriate SQL Server group as giving it to a specific user account. That way if the SQL Service account changes the new service account will be able to make use of instant file initialisation.

Cindy in her post talks you through enabling this feature for your SQL Server. I won’t repeat the steps and I would recommend you read the post if you need a step by step guide, but in essence you give the relevant SQL Server instance group permissions to Perform volume Maintenance tasks using secpol.msc snap-in.

Wednesday, 20 January 2010

SQL Server Resource Governor

I didn’t know this bit Resource Governor in SQL Server 2008 is only available in Enterprise edition

 

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 database

Prior 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.

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK

Some may find this a little restrictive and may like to tie the trigger down to a particular table or object...This takes a little more invention:

CREATE TRIGGER Trg_Specific_Object ON DATABASE

FOR alter_table

AS

DECLARE @data XML

SET @data = EVENTDATA()

DECLARE @ObjectName NVARCHAR(100)

SET @ObjectName = 'DDL_Test'

DECLARE @object nvarchar(100)

SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)')

PRINT 'Altering object :' + @object

IF @Object = @ObjectName

BEGIN

PRINT 'Alter schema not allowed on this database - disable the DDL trigger'

ROLLBACK;

END

In the above statement the trigger fires for each ALTER statement BUT it will only rollback the DDL statement if it includes the object specified.

SQL Server 2008 R2 - Release May2010

SQL Server 2008 R2 now has a release date...May 2010.

Check out the microsoft site for more details...link to follow

Saturday, 16 January 2010

January Roundup

I have just put together the January newsletter for www.gre-sqlserver-solutions.com 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.

Monday, 11 January 2010

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 statements that comprise a transaction in a single batch to avoid unnecessary delays
  • · Consider coding transaction in store procedures
  • · If you have to use cursors commit updates frequently and as soon as is possible. Being much slower than set based processing locks in cursors will be held for longer.
  • · Use the lowest isolation level required by the process.
  • · Don’t allow users interaction between a BEGIN TRAN and a COMMIT TRAN 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.