Andrew Calvett

The overhead of a non-unique clustered index

2008-10-19T23:21:00Z

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 not realise is that this is actually a variable length column so if your table has no variable length columns you have to incur another 4...(read more)

Changing the Data Files Location after Installation

2008-08-10T09:28:00Z

The other day i wanted to change the "Data Files" location for a 2005 database engine installation and a 2005 Analysis Services installation which you can specify under the advanced options during installation. I quickly found out that there appears to be no documented ways to do this other than uninstall SQL Server and install again specifying a new location for data files. It's also not as simple as moving your system databases as "Data files" covers things like server errors...(read more)

MS SQL Server Book of Wisdom

2008-05-21T17:07:00Z

I was chatting with a friend today and he asked “Have you ever seen those little books of wisdom?". We quickly decided that we could write a MS SQL Book of Wisdom, Below is a summary of what ensued for your amusement. Now, some of the statements are actually based on bad real life advice and many we just made up. Can you tell which is which? Also please comment if you have got any good entries for the Book….. Why would i want my server to be enabled for awe!? I`m already in awe of my coding...(read more)

SSAS 2005 – Server side tracing starter kit

2008-04-07T21:32:00Z

Analysis services 2005 (SSAS) added the ability to trace server side events and i have used this feature a number of times. To date i had always used the profiler gui to do the SSAS tracing but today i found myself needing to initiate and manage a trace with scripts. The good news is that it can be done! It did take a while to piece together how to do it though and i found some of the information quite a challenge to find so i am sharing with you the results and have attached a zip file with the...(read more)

Transaction log backup deadlock

2008-02-19T23:10:00Z

Recently we started to see deadlock errors when backing up our transaction logs. The "important" part of the error is shown below. Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful. What this meant was that the transaction log backup was occurring but the entry in the msdb was not being made as it was being chosen as a deadlock victim so we investigated the cause of...(read more)

SSMS Log file viewer and Deadlock Graphs

2008-01-23T16:02:00Z

Firstly I must say a big thank you to Microsoft for the new deadlock trace flag 1222. Compared to the trace flag output for 1204 & 1205 that you had to use in SQL 2000 it’s a walk in the park to interpret. Anyway, back to the post at hand! This is a quick FYI as i`m not going to go through how to interpret a deadlock graph because Bart Duncan does a fantastic job of it here . When you enable 1222, it will output the deadlock information to the error log. If your using the log file viewer and...(read more)

The Job whose owner kept coming back......

2007-12-30T19:25:00Z

I thought i would share this little quirk about the SQL Agent jobs for maintenance plans. One of our members of staff had left and we had the usual case of a few jobs failing with: "Unable to determine if the owner (DOMAIN\xxx) of job <JOB_Name> has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))." So, we went around and updated the job owners to one of our appropriate generic admin accounts. A few days later some of the jobs started to fail again with the same error...(read more)

My old mate sp_recompile

2007-10-12T10:33:00Z

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 changed after the cursor was declared". My gut reaction was to restart each application server in the cluster but having restarted the first one it made no difference. It suddenly clicked that SQL Server must be dishing out the cursor plan from cache. Now, I did not want to restart the SQL servers because only a small part of the application was affected ...(read more)

SSMS Restore backup error

2007-09-14T14:31:00Z

We had a requirement to allow someone to create and restore databases on a test server today and i thought to myself "Thats easy, i`ll just grant the "Create Any Database" right to the appropriate user, thats when the pain began! The user was using SSMS connecting using a SQL login to restore a database and when they went to specify the backup location they got an error to the effect "Cannot access the specified path or file on the server". After clicking ok the tree view in the locate backup dialog was empty and if you typed in the path and filename manually you still recieved an error. So, i dug out the profiler and found that xp_fixeddrives was being called and decided to check it out. It turns out that when executing xp_fixeddrives using a SQL login it returns no results! Because of this the error is generated in SSMS and the tree view is not populated. ...(read more)

Server level VLF report

2007-07-25T22:27:00Z

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 int,filesize bigint,startoffset bigint,fseqno bigint,status int,parity int,createlsn varchar(1000))

CREATE TABLE #Results (SRV_Name nvarchar(500),Database_Name nvarchar(500),VLFS INT)

exec master.dbo.sp_msforeachdb

@command1 = 'USE ? INSERT INTO #VLFS EXEC(''DBCC LOGINFO WITH TABLERESULTS'')',

@command2 = 'insert into #Results SELECT @@SERVERNAME,''?'',count(*) from #vlfs',

@command3 = 'truncate table #vlfs'

--For this example i`ve just returned the results but you can

--just as easily write the results to a local or central server.

--I write mine to a central server for reporting on.

SELECT * FROM #Results

drop table #vlfs

drop table #Results

Example output

For me the beauty of this little report is that i have set it up so that it runs on all 150 of my servers distributed around the world and logs to my central logging server. From here I get one report that tells me the databases and the servers they are hosted on that have excessive VLF's. Having said that in a well managed environment i should never find any databases with lots of VLF's........

You may also be wondering "How many VLF's are too many?". Tony did not cover this but Kimberly Tripp did in a post she put up a few years ago (See point 8) which was when I first learned about VLF's. I don't however think this is a hard and fast number and obviously the performance gains will be less if your not far off this number.

The last thing I am going to mention in this post is part of the code I used in the report. I used the undocumented procedure sp_msforeachdb. This is a great little procedure that will cycle through your databases executing up to 3 commands. To use it, where you would have put a database name you put a '?' and its as simple as that! Incidentally there is also a sp_msforeachtable.

The joy of template parameters

2007-07-15T19:23:00Z

I thought I would do a quick blog about template parameters since I have been writing allot of standard deployment scripts for our 2005 builds and have used them extensively.

So what are they? Well they are place holders and you would you use them in the same places that you would probably put a variable. The key difference comes when you assign the values. With variables you work though the code setting the values as appropriate, with template parameters all you do is press CTRL-SHIFT-M and you get the dialogue box shown below. Simply fill in the values, click ok and your ready to run your script.

The format of a parameter is <Parameter Name, SQL Data type, default value> so to get the Mirrored DB name shown in the screen shot you would enter <Mirrored_DB_Name,nvarchar(128),'My_Mirror'> where you wanted the value to appear.

This feature is available in Management Studio and Query Analyser, if you have ever used one of the standard Microsoft query templates you may have noticed that parameters are used in them as well.

I only realised template parameters existed a few years back because someone pointed it out to me. If this is the first time you have come across them (or had forgotten about them) I hope you find them as useful as I do.

The x64 experience

2007-07-09T23:23:00Z

The 64 bit beast has been out there for a while now and new servers are generally 64 bit compatible. The first thing that often comes into my mind when i think 64 bit is performance & memory with "compatibility" hot its heels (and recollection of a good article by Linchi Shea).

My fears around compatibility have been pretty much put to rest and we now recommend SQL 2005 x64 as our base build. However it does seem that 64 bit builds need a bit more attention. I say this because recently i've been having to register 64 bit dll's again and having mentioned this to a few DBA's the conversations went a bit like "Really? So was it x.dll or y.dll". Fortunately though the problems seem to only be affecting the tools (touch wood) but i am curious as to what experiences others are having with 64 bit servers so please do leave some comments.

Now, to help others who run head first into the very misleading errors i experienced i`ll detail the errors and the fixes below.

The first problem

This one occurred when we were doing a server build. One of our final tasks was to deploy a SSIS package so we went to open the package in BIDS and were greeted with the following error when loading the package.

"The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFormXML fails."

Some research threw up a few red herrings but the next clue came when i decided to connect to the SSIS instance using SSMS. At this point i got an error stating

"Error loading type library/DLL"

Further research threw up the following document which gave me a few options none of which i liked and more importantly the top solution was use the latest SP which i was already running so i decided to go hunting and use the sysinternals process monitor to see if i could find the issue. 15 minutes later the culprit was found to be the DTS.dll which process monitor identified as throwing some unusual errors so i located the 64 bit version and use REGSVR32 to register the DTS.DLL again and this resolved my problems.

The second problem

This one came a few weeks down the line on the same server. I was working in SSMS and connected to SSIS, when expanding the tree view i got the following error.

"Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))"

This time i went straight to my trusty process monitor and found that the server was trying to use the 32 bit version of msdtssrvrutil.dll. I located the 64 bit version and registered it and everything worked again. This time i also took to opportunity to check as best i could that everything else was working.

Its been a few weeks since the issues now and alls well so hopefully the server will behave, until the next time.........

Dotty about maintenance plans

2007-06-22T16:36:00Z

I went to remotely edit a maintenance plan the other day and found it took a long time to open any of the objects in the plan. I also found that I was getting errors when clicking the drop down to select a database.

The reason for the problem was that the local server connection embedded in the maintenance plan had the server name defined as "." rather than an actual server name. Because of this the objects were trying to connect to a default instance of SQL on my PC rather than the server. The delay in opening was down to the timeout and the subsequent errors because there was no server to talk to.

The reason this occurred was because when the maintenance plan was created the server was registered as "." rather than <server_name> in SQL Server Management Studio (SSMS) and the maintenance plan uses the registered name in SSMS to populate server name in the connection details. Unfortunately you can not edit the local server connection details so probably the quickest way to fix the problem is to recreate the plan with the full server name registered in SSMS. I say probably the quickest because there may be a way to fix it quickly using the Business Intelligence Development Studio to update the package but getting the package back in is awkward.

The real killer

In many ways I think I got off lightly with this "feature". Why? Well, lets say I was editing the maintenance plan from another location that had got a default instance of SQL installed…… Yep! You guessed it, the maintenance plan loads quickly and without error because its connected to the local instance. When you select databases from the drop down list you are seeing the databases from your local instance and not the remote server and you could then select to run tasks on a database that does not exist on the remote server.

I`m going to flag this on connect and add a check to our install documents to ensure that instances are not registered with a "." in SSMS on the server.

Back to the Blog!

2007-06-22T12:07:00Z

I recently got an e-mail from a friend pointing out it had been a year since I had last blogged so I made the usual mental note to myself that I really should pull my finger out and get blogging again. This time I am actually going to do something! There have been reasons for my absence but now i`m getting back into gear and will be posting regularly so watch this space!

I have also decided to take this opportunity to move my blog to http://sqlblogcasts.com/blogs/acalvett/ as I no longer feel that the SqlJunkies site is a suitable home. I will however cross post for a while.

SQL 2005: Procedure cache cleared

2006-06-05T17:15:00Z

I don't normally write about a technet article but i must say that 917828 caught my eye and i felt that it was worth highlighting it. The reason it caught my eye is that it describes a number of scenarios where by the whole procedure cache is flushed which will clearly lead to a decrease in query performance whilst the cache builds up again. The most alarming point was that there are at least 2 scenarios that a DBA may carry out and even schedule during core production hours without realising the...(read more)