Simons SQL Blog

Due to a cancellation there is some last minute availability on Chris Webb’s Performance Tuning Analysis Services and Chris Testa-Oneil’s Implementing Reporting Services training days.

If you haven’t registered for a training day then register quick as I expect these few spaces to be taken up very quickly.

Tony Rogerson who co-ordinates the UK SQL Usergroup has set a challenge.

If we can get the number of members for the UK SQL Usergroup LinkedIn group to 1000 by the time of SQLBits. He’ll present in wellies and a Hawaiian shirt.

So get joining the linkedIn group now

This little script gives you the filegroups for each partition in a partitioned table. Because the data_space_id for a partition is a partition scheme for partitioned tables  you have to join to the destination_data_spaces dmv to get the actual filegroup.

I’ve also added the range criteria for each partition.

  select isnull(d.name,ps.name) FileGroupNameOrSchemeName

         ,isnull(d.type_desc , ps.type_desc ) FileGroupTypeOrSchemeType

         ,OBJECT_NAME(p.object_id) ObjectName

         ,p.rows

           ,p.data_compression_desc

           ,p.partition_id

         ,p.partition_number

         ,isnull(case when boundary_value_on_right =1 then '>='

               else '>' end + cast(minprv.value  as varchar(100)),'~') + ' to '

          +isnull( case when boundary_value_on_right =1 then '<'

               else '<=' end + cast(prv.value  as varchar(100)), '~') Criteria

         ,d2.name FilegroupName

     from sys.partitions              p

     join sys.indexes                 i       on i.index_id              = p.index_id

                                             and i.object_id             = p.object_id

left join sys.data_spaces             d       on d.data_space_id         = i.data_space_id

left join sys.partition_schemes       ps      on ps.data_space_id        = i.data_space_id

left join sys.partition_functions     pf      on pf.function_id          = ps.function_id

left join sys.partition_range_values  prv     on prv.function_id         = ps.function_id

                                             and prv.boundary_id         = p.partition_number

left join sys.partition_range_values  minprv  on minprv.function_id      = ps.function_id

                                             and minprv.boundary_id      = p.partition_number -1

left join sys.destination_data_spaces dds     on dds.partition_scheme_id = ps.data_space_id

                                             and dds.destination_id      = p.partition_number 

left join sys.data_spaces             d2      on d2.data_space_id        = dds.data_space_id

[Update 14/9/2010 : Corrected boundary conditions for RIGHT bounded partitions]

Just learnt and interesting tip on twitter from @martinpeck about piping output to the clipboard. Sound

This also works for other commands i.e.

cd | clip

copies the current directory to the clipboard. Its only one way but its quite handy

-- Create the table to accept the results

create table #output (dbname char(30),log_size real, usage real, status int)

-- execute the command, putting the results in the table

insert into #output

exec ('dbcc sqlperf(logspace)')

-- display the results

select *

from #output

go

--open cursor

declare output_cur cursor read_only for

select log_size

from #output

--make space computations

declare @v1 as real

declare @base_val as real

set @base_val = 0

open output_cur

FETCH NEXT FROM output_cur

INTO @v1

WHILE @@FETCH_STATUS = 0

BEGIN

set @base_val = @base_val + @v1

FETCH NEXT FROM output_cur

INTO @v1

END

set @base_val = @base_val + 15

PRINT 'BASE_VAL = ' + cast(@base_val as nvarchar) + ' MB'

--clean-up

close output_cur

drop table #output

This has to be the worst use of a cursor I’ve seen for a long time. Has this company not know about SUM().

Have you seen anything worse?

I’ve not seen anyone go into this details before in explaining costing of queries.

Whilst I would avoid getting hung up on the specifics of a cost as these will change from release to release. Its a very interesting read about how and why cost optimisation is done and then some quirky un documented stuff that gives you a little insight into possibly the first plan that would be considered by the query engine if there was no cost to anything.

http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspx

After some thought we’ve decided to change Buck Woody’s training day session. Instead of doing a full day on career development he will be doing a session on SQL Server for the Non DBA.

This is a great session for anyone that has been thrown into running their SQL Servers or has just started out with SQL Server and needs to know the core information to make sure they’re not going to be sacked for loosing the companies data.

If you’re in that situation, or perhaps you’re looking to become a data professional as a career advancement, this workshop is for you. Buck Woody, Microsoft’s “Real World DBA” will show you how to start with the basics and work all the way through a full database project, learning the tools, components and best practices along the way. In this one-day class, you’ll go hands-on to learn the basic steps and knowledge that you need to become a data professional. Buck works at Microsoft, teaches a database class at the University of Washington, and writes and speaks professionally. He’s also mentored technical professionals, and taught this course in three countries. Topics covered include: 

• SQL Server Architecture
• Server and Database Components
• Tools and utilities for working with SQL Server
• Designing a simple database project
• The Basics of Transact-SQL
• A security primer
• Maintenance and Monitoring basics

During the session, you’ll create a full database project, so you’ll need a laptop with a Microsoft Operating System (XP or higher) and good note-taking skills.  To register for the session go to the SQL Bits Registration page

Buck Woody

Buck (blog | twitter) is a SQL Server Senior Technical Specialist for Microsoft, working with enterprise-level clients to develop data platform architecture solutions within their organizations. He has over twenty years professional and practical experience in computer technology in general and database design and implementation in specific. He is a popular speaker at TechEd, PASS and many other conferences; the author of over 400 articles and five books on SQL Server; and he teaches a Database Design course at the University of Washington. For more on buck and what he’s done in the past look at his resume http://buckwoody.com/BResume.html

All the training day sessions for SQLBits 7 are really popular which means there are only a few places left on each of them.

I know lots of people that are thinking of going to the training day and haven’t booked yet. if you are one of them make sure you get your booking in quick to get the training day you want.

We look forward to seeing you there and get booking quick to also benefit from the £100 early bird discount

For details of all the training day seminars have a look at http://sqlbits.com/information/TrainingDay.aspx

Or just go straight to the  Registration page

If you are running with British english as your language or some other language with a different date format to the guys in the US then you will have come across this handy error.

This happens when you try and copy statements from RPC Events in SQL Server Profiler into Management Studio (or other tool) and try and run then.

But you say, it shouldn’t happen then statement ran fine the first time. That is may well be the case.

The problem arises because for date parameters an RPC call passes the value as a binary value and not a string representation. However if profiler displayed a binary value that would be a bit unhelpful and so it displays a string representation, and there’s the problem. Once you start bringing strings into the equation you get into localisation issues unless you use a standard date format.

The problem is that SQL Profiler has chosen the ANSI date format, and unfortunately SQL Server doesn’t treat ANSI dates properly, it allows the DATEFORMAT to affect the parsing of them. It shouldn’t but it does.

Interestingly if your parameters are the new datetime2 or date date types then there isn’t a problem because the SQL team fixed the parsing of ANSI dates but only for the new date types. You can replicate the issues by running this code

use tempdb

go

create procedure foo @a datetime as select @a

go

set dateformat dmy--British English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

go

set dateformat mdy--English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

The first execution of foo will fail but the second one will work. If we change the datatype for parameter @a it will work fine.

use tempdb

go

create procedure foo @a datetime2 as select @a

go

set dateformat dmy--British English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

go

set dateformat mdy--English uses this date format

exec foo @a='2010-08-31 17:28:40.423'

As an aside the error you get will be different to a normal datetime arsing error because it is being done by a different bit of code in SQL Server. A normal date parsing error would be something like

Msg 242, Level 16, State 3, Line x

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Getting a fix

What you need is the dates in a standard that works you can use either of the ISO formats yyyy-mm-ddThh:mi:ss.mmm or yyyymmdd ddThh:mi:ss.mmm.

The fix would be to get Profiler to output the correct dates. You might dig about and find a regional settings option but that seems to do nada. A connect item has already been raised for this and is found here https://connect.microsoft.com/SQLServer/feedback/details/538980/profiler-date-format?wa=wsignin1.0 please vote on this if you use languages with non mdy formats.

However there is no fix currently, and to be honest I don’t see one coming.

In frustration I decided to write a little app to do it that uses regex to replace the dates. I’ve used this regex to find the dates

and then these to replace the dates

This happens to another issue that occurs which is profiler outputs the microseconds part of dates which isn’t valid for the older dates. I think that is fixed but I’ve included a fix using the second replace anyway.

The exe which is attached can be run as a systray or from the command line to replace a file.

SimonSays <action> <filename>

<action> can be one of ANSI2ISO or ANSI2ISO_NO_MICRO

The latter means it can used as an external tool in Management Studio, but does require the file to be saved.

The systray option converts what ever is in the clipboard

Its great to see registrations flying in. Don’t forget the early bird finishes tomorrow.

So make sure you get your registration paid for today.

Numbers are looking good, but we’ve got huge capacity this time (we prefer everybody to have a seat although in Conor’s Session last time it got a little busy  http://www.flickr.com/photos/janed/4527758206/in/set-72157623748991321/) so plenty of room for more registrations.

If you are having problems with paying then please let us know and we will help you.