Andrew Calvett

Exploring backup read io performance

ACALVETT — Sun, 24 Jan 2010 14:12:00 GMT

I was recently exploring how to increase the backup read throughput on one of our SQL servers. Below are some interesting facts i found. I would say that one of the most important reminders that came from the exercise is, do not assume that 2 databases...(read more)

Enter the SSAS server level lock......

ACALVETT — Mon, 23 Nov 2009 18:13:00 GMT

Ok, so your reaction to the title is probably the same as mine when i found out about SSAS server level locks! So, i will give you the scripts to reproduce the server level lock but first lets get down to business…. :) Server locks were introduced in...(read more)

SSAS 2008 - INI Files and in place upgrades

ACALVETT — Thu, 24 Sep 2009 21:13:00 GMT

Being the suspicious person i am i wondered if there would be any differences in the MSMDSRV.ini of an instance upgraded from 2005 as opposed to a clean install. Now obviously i expect an in place upgrade to preserve my settings and add any new ones because...(read more)

The cost of SSAS metadata management

ACALVETT — Fri, 21 Aug 2009 05:45:00 GMT

This post will explain how we found ourselves in a situation where when processing a partition the metadata checks took 50 times longer than the actual partition and how to identify what time penalty you are incurring for the metadata checks. The system...(read more)

Analysis Server appears to hang…..

ACALVETT — Sun, 05 Apr 2009 20:40:00 GMT

We had an ongoing problem where by users would suddenly start complaining that the Analysis Server was hanging. When investigating the box we could see that there appeared to be no physical constraints. Disks were a bit above average, CPU was very low...(read more)

The evils of implicit conversions

ACALVETT — Sun, 18 Jan 2009 17:18:00 GMT

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...(read more)

The overhead of a non-unique clustered index

ACALVETT — Sun, 19 Oct 2008 23:21:00 GMT

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...(read more)

Changing the Data Files Location after Installation

ACALVETT — Sun, 10 Aug 2008 09:28:00 GMT

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...(read more)

MS SQL Server Book of Wisdom

ACALVETT — Wed, 21 May 2008 17:07:00 GMT

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...(read more)

SSAS 2005 – Server side tracing starter kit

ACALVETT — Mon, 07 Apr 2008 21:32:00 GMT

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...(read more)

Transaction log backup deadlock

ACALVETT — Tue, 19 Feb 2008 23:10:00 GMT

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...(read more)

SSMS Log file viewer and Deadlock Graphs

ACALVETT — Wed, 23 Jan 2008 16:02:00 GMT

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...(read more)

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

ACALVETT — Sun, 30 Dec 2007 19:25:00 GMT

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>...(read more)

My old mate sp_recompile

ACALVETT — Fri, 12 Oct 2007 10:33:00 GMT

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

ACALVETT — Fri, 14 Sep 2007 14:31:00 GMT

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)