SimonS Blog on SQL Server Stuff

Don't forget if you are in the Cambridge area on Monday (6th July 2009) we are doing another usergroup meeting.

This one is going to be on SQL Server 2008 features.

We are holding this at the head quarters of Redgate software who are kindly sponsoring the event.

To register go to http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx

I look forward to seeing you there.

Martin is continuing to post some great new posts on his blog. I thought the last one is a real nasty one.

http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/29/After-disabling-tde-you-still-requires-certificates-to-restore-the-database.aspx

If you have enabled TDE on a database and encrypted some data then remove the TDE and backup, you may not be able to restore that backup if you don't have the certificate.

I am guessing that this is to do with the encryption when you turn TDE on being a background task, I expect the same is true in reverse, i.e. pages stay encrypted until a background process decrypts them.

I wonder if the database ever gets back to a totally unencrypted version

Many years ago I was working on a project that had some large batch processing in it. To do this processing we had some dedicated servers, one of which sat under the desk next to me.

Apart from the noise of the fan soudning like we had actually bought a jet fighter the most annoying thing about this processing was that it resulted caused the servers beep to continually sound. Something in the code caused it to fire.

Boy it was annoying.

We were never able to find out how to stop it, I think in the end we disconnected it. Well last week I found out and it was so annoyingly simple.

net stop beep

Yep thats it, thanks to the trainer that pointed this out last week. As you can imagine, having 12 training machines all beeping is not something you really want

To permanently stop it you have to disable the service using

sc config beep start= disabled

Whats really odd is that beep doesn't appear in the list of services

One feature introduced in SQL Server 2005 is reporting in management studio. You have a set of standard reports that allow you to see in a nice report format output from dmvs and the default system background trace. Whats neat is that you can write your own custom reports. For those that long after the taskpad view of a database from SQL 2000 Enterprise manager with the help from Jasper Smith I created this http://sqlblogcasts.com/blogs/simons/archive/2007/03/28/Update-to-the-taskpad-custom-report.aspx

The custom report feature is almost fantastic, there are 3 main features I think make them awkward to use.

1. You can select text from the report and copy it
2. You can't add additional parameters to allow additional filtering, i.e. filter audit changes for today.
3. You can't have hyperlinks.

The last one I raised a connect item for https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240407.

If we had this you could write reports that had links to KB articles, blog posts, or internal KB systems. Similar to what most monitoring systems provide.

If you think this is a good idea then please vote on the connect item. I've been asked by the tools team to see if it is something people want.

Some other connect items that you might be interested in supporting if you think they are worthwhile.

Use expressions for data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=238233

Be able to combine data from different data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240268

Be able to run reports against SQL 2000 database - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

I incorrectly said in my last blog post http://sqlblogcasts.com/blogs/simons/archive/2009/06/23/Cambridge-SQL-Server-Usegroup---SQL-2008.aspx that the usergroup was on the 6th June. That would be going some, whilst SQL is good it doesn't yet do time travel.

The usergroup is on the 6th July.

I've just updated the agenda for the Cambridge usergroup meeting on the 6th July.[This is now the correct date SS 23/6/08]

If you plan to attend make sure you register http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx  

We are holding the meeting at Redgates offices (http://www.red-gate.com/about/Map_colour.pdf which are very nice)

The topic for the evening is all things SQL Server 2008.

We will be trying live meeting again to view go to https://www.livemeeting.com/cc/usergroups/join?id=HWJRB6&role=attend&pw=R7R4N%217pq This will be open just before the meeting starts.

I look forward to seeing you there.

In the immortal words of Alan Rickman

"'you, redgate offices, 6:00 , you, 6:45.... bring a friend'

If you plan to attend make sure you register http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx  

Simon

James Rowland-Jones asked me this evening how to export XML data from SSIS. I've done this before but always used the Script component and I wondered if there was another way.

I tried to think of a component that saves data to a file. Oddly there isn't a control flow task that does that. The nearest I came to was the "export column" transform, this is a hardly known transform. It was a very specific function that most people don't need. For each row of data going through it, it exports the contents of a column to a file with a filename specified in another column.

Thinking of this I generated an example query that returns a rowset with a filename column and our XML column (data).

select 'c:\test.xml' filename

    , (select name, (select name

                  from sys.columns c where c.object_id = o.object_id

                  for xml path('column'),type) columns

     from sys.objects o

     for xml path('object'), root('schema'), type) data

This can then be plugged into an OLEDB source, connected to an Export Column transform configure the data column, filename and whether to append or truncate and away you go. You can get the video here http://media.sqlknowhow.com/nuggets/Exporting%20XML%20data%20from%20SSIS.wmv  or watch it below.

If you came to the usergroup meeting and completed the survey today and said your postcode was WC1X 8TG can you please ocntact me so we can discuss what nugget/session you would be inetersted in doing, as you forgot to put your name on the survey.

You can use the contact line on the right hand side of this blog.

Cheers

Simon

The live meeting on performance tuning and profiler has started.

To attend go to https://www.livemeeting.com/cc/usergroups/join?id=JK8HND&role=attend&pw=Wbg%5CM4%3FfR

Hope to see you there

I had an email from one the people that attends the usergroup I help run in London. She was suprised that when she had a create table statement intellisense is able to understand that this table is being created and provide intellisense on that table. This is just like any other object in your script, i.e. if you declare a variable then intellisense can provide that variable in a list for you. I guess tables are more complex than variables, but if with other languages like C# the intellisense is able to parse objects methods and even comments to provide information about those objects and methods without even compiling your code.

For a demo of this feature have a look at this nugget http://media.sqlknowhow.com/nuggets/Intellisense%20and%20objects%20created%20in%20scripts.wmv

I'm working on a whitepaper on when query plans go wrong, and I'm amazed at the number of scenarios that they can go wrong.

A recent one I just came across was related to SSIS. If you use the table or view option for OLEDB sources you can get really bad plans. Piers did some digging and found that to get the meta data for the query it issues a set rowcount 1. He found this resulted in a plan being cached that when used without the set rowcount was awful for performance. http://piers7.blogspot.com/2009/06/nasty-ssis-2008-issue-with-table-or.html

Below is a repro and highlight a typical bad plan situation. A plan is built on the assumption of processing a very small number of rows and so chooses a nested loop join. Nested loop joins dont' perform if you are processing large numbers of rows, do to the lookup nature.

In this repro you will see that we exceute the same logical query three times. The first and second queries are identical and so will reuse the same query plan, that is cached by the first one being executed, but due to the comments the third one will generate a new plan. Because the third one is not limited to 1 row it generates the best plan for readding all the data (should be a MERGE join in this case).

So the lesson here is be very careful when using rowcount to look at a query if you are going to be executing the same query to get all the data.

set rowcount 1

go

select H.SalesOrderID, H.SalesOrderNumber , O.ProductID , O.UnitPrice

from AdventureWorks2008.Sales.SalesOrderHeader H

join AdventureWorks2008.Sales.SalesOrderDetail O on H.SalesOrderID = O.SalesOrderID

go

set rowcount 0

go

select H.SalesOrderID, H.SalesOrderNumber , O.ProductID , O.UnitPrice

from AdventureWorks2008.Sales.SalesOrderHeader H

join AdventureWorks2008.Sales.SalesOrderDetail O on H.SalesOrderID = O.SalesOrderID

go

--Use some text to change the query to get a new plan

select H.SalesOrderID, H.SalesOrderNumber , O.ProductID , O.UnitPrice

from AdventureWorks2008.Sales.SalesOrderHeader H

join AdventureWorks2008.Sales.SalesOrderDetail O on H.SalesOrderID = O.SalesOrderID

go

There are sometimes when you are looking at somethin and the only possibly solution you can come up with is that there is a bug.

Imagine you have a calculation that is showing 1+1= 3, everything about everything you've ever learnt says there it just can't be so.

Thats what James (@jrowlandjones) faced the other day when loading in millions of rows of data.

His data sets where for multually exclusive ranegs of index keys and yet he was getting a deadlock. A deadlock can only occur when two processes require the same resources. The resource in question was a key, how could that be, how can to loads be trying to lock the same key when the keys they contain are don't overlap at all.

Read James's great blog post on how we diagnosed the issue and the solutions available to you.

http://blogs.conchango.com/jamesrowlandjones/archive/2009/05/28/the-curious-case-of-the-dubious-deadlock-and-the-not-so-logical-lock.aspx?CommentPosted=true#commentmessage

Paul Randal has a great survey this week. You have to decide on the ONE thing that is important when performance tuning.

It would have been good to have a second question to ask what your job role is. I would expect a correlation between job role and the answer.

http://www.sqlskills.com/BLOGS/PAUL/post/Weekly-survey-whats-the-most-important-thing-when-performance-tuning.aspx

I won't say what I decided, I was torn. I think indexing is essential as you can index out of many poor written queries but then again application code is also important. As they say "you can't polish a turd", but if you don't have enough memory then your performance won't be great either.

So which will you choose

Wrong, wrong, wrong.

This post was in response to a post in the forums "How do I delete the log file?" http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/69c8ecc6-6414-43b1-8c08-21fdf2ca9c54

The transaction log for a SQL Server database is not a log like the ones many applications produce that is just history of the stuff you've done in the application, say for debugging. Whilst it is a record of the changes you make to your data it is a critical element in how data is kept consistent in your database database, i.e. not corrupt.

Lets start by going over a few basics. A SQL Server is made of tables, the data for a table is stored on a page. These pages are stored in the data files (mdf/ndf ).

When you make changes to rows in a table, the pages containing those rows are read from disk and put into memory. The changes are recorded in the transaction log and are then the pages changed to reflect the chanegs you've made. The pages are only change IN MEMORY, not in the data files. If at this point your server dies, your changes that are currently only in memory and not in the data files will have been lost. However they are in the transaction log. So when SQL Server starts a recovery process is run. This can be seen in the errorlog. This process looks for transactions that are in the transaction log that didn't get harded to the data file and it makes the changes to the data file. So you don't loose your data.

There is an additional process that runs called the lazy writer, what this does is free pages from cache when under memory pressure. This means that the changes you have made to pages in memory may be hardened to the data files. Great you might think thats what I want. However this can also happen in the MIDDLE of a transaction. This means that you could have changes in your data files that have yet to be committed. In this case if SQL Server crashes mid transaction, when the recovery process runs it looks for changes that were made and recorded in the transaction log and hardened to disk but for which the transaction never completed. These changes are then rolled back thus leaving your data files in a consistent state.

So even if you don't need point in time recovery you still need your transaction log.

The key thing is that if you don't need point in time recovery then you should set your recovery model to simple. What this does is frees the space in the transaction log of committed transactions and so your transaction log shouldn't grow.

One note: If you have a single transaction that requires 10Gb of transaction log space then using simple recovery won't help. For example, updating every row in a very large table in one statement, this is one transaction and so the transaction log needs to be big enough to hold the changes. If space on the drive for the transaction log space is problem then you need to look at batching up your changes into smaller transactions, that once committed can be truncated from the transaction log. See my posts about DELETE and UPDATE with TOP http://sqlblogcasts.com/blogs/simons/archive/2009/05/22/UPDATE-and-DELETE-TOP-and-ORDER-BY---Part2.aspx

To say the type system in SQL is lax is an not quite correct, its actually lax, in consistent and very annoying.

The most common feature I come across is that of integers and decimals.

If I declare a variable as decimal(10,4) and assign it the value 10/4 you could expect a number of things to happen

1. The code doesn't run because you are casting an integer to a decimal

2. The code runs and stores 2.5 in the variable

3. The code runs and does the calculation based on integers 10/4 and realises truncation will occur (as the destination type is decimal) and raise a warning

4. The code calculates the 10/4 as integers so results in an integer of 2 and sticks it in the variable.

In a nice type safe system, i.e. c# one would get 1. The result of an integer/integer is an integer and you can't store an integer in a decimal. This is nice because to get the value stored you have to explicitly do a cast. This makes you think and realise that you probably have to cast the 10 or the 4 to get the required decimal value of 2.5 returned.

In TSQL however you get 4.

If you want to look at inconsistencies then look at ISNULL and COALESCE. ISNULL returns the same type as the first parameter and COALESCE returns the data type based on evaluating all the data types passed to the function and evaluating the top datatype

declare @i int

declare @s varchar(100)

select ISNULL(@s,'100') + '100', ISNULL(@s, 100) + '100'

      ,COALESCE(@s,'100') + '100', COALESCE(@s, 100) + '100'

This is sort of where I got stuck today, I was using the Radians function to use in a conversion from longitude and latitude to OS grid reference. This is a shocking formula and one that I have javascript and C# versions but not a TSQL one. I couldn't for the life of me figure out why the TSQL result was vastly different.

So I debugged the code, thank you SQL 2008 (although I did find a nice feature I will blog about later).

What I found was this line wasn't returning the same value in TSQL as in C#, it was returning 0 in TSQL, and even I know 49 degrees isn't 0 radians

declare @lat0 numeric(29,15)= radians(49)

Gosh I thought I had found bug in TSQL. Then I had a euraka moment, you're probably already there. I changed the code to

declare @lat0 numeric(29,15)= radians(49.0)

and it returned the correct value. On looking at BOL it does say that radians returns the same data type as the value passed. Which to me sounds mad because the most even for a value of 360 degrees the radians value is only 6 and so the decimal places are very very important. Whats more  any value of degrees between 0 and 90 is < 1 and so if you only specify an integer for the degrees, as in my case above, you will get 0.

Anyway lesson learnt. So from now on as well as always changing 10/2 to 10.0/2 I will be making sure any literals passed to functions like this will also have .0 tagged on the end.