Andrew Calvett

Exploring backup read io performance

2010-01-24T14:12:00Z

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 being backup up on the same server using an identical backup command means that the processes are identical under the hood. Backup read threads are spawned 1 per physical device used by the database. (This is documented in Optimising Backup & Restore...(read more)

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

2009-11-23T18:13:00Z

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 one of the SP2 cumulative updates. At the moment i can say it was pre CU12. I`m not sure why it was introduced but it likely to be in response to a “feature” :) Fortunately the lock only appears at the end of processing when SSAS commits its data and...(read more)

SSAS 2008 - INI Files and in place upgrades

2009-09-24T21:13:00Z

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 it should not overwrite anything since i might have change from defaults for a good reason……. Below is what i found followed by my thoughts…. IN Place Upgrade Value (Effectively 2005) <ServerSendTimeout>-1</ServerSendTimeout> <ServerReceiveTimeout>...(read more)

The cost of SSAS metadata management

2009-08-21T05:45:00Z

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 i was investigating had around 80 cubes with around 100 partitions per cube all hosted in one database and we would add 1 partition per cube per day. The partitions in each cube were small but were taking far to long to process and as each day passed...(read more)

Analysis Server appears to hang…..

2009-04-05T20:40:00Z

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 and there was plenty of memory. When looking at the server through the SSAS activity viewer (part of the community samples) we could see that users were definitely queuing up and many of the queries should have returned in less than a second but were...(read more)

The evils of implicit conversions

2009-01-18T17:18:00Z

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 --******** Create Test Data ****************** CREATE TABLE #data_test ( ukey I NT IDENTITY ( 1 , 1 ) PRIMARY KEY , first VARCHAR ( 200 )...(read more)

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)