Archive
Why Are Date Tricks in SQL 2005 a Problem in Waiting?
One of the long time annoyances about the Date functions in SQL is that a number of them do not function as developer would wish, the prime example I’m going to focus on is the DateDiff function. The way it operates is very contrary to what a developer might expect or want from it – the function counts the number of boundaries that crossed for the specific units selected, not the number of whole units between the dates, as an example:
declare @date1 datetime declare @date2 datetime set @date1 = '20100101 23:00:00' set @date2 = '20100102 01:00:00' select datediff(d, @date1, @date2)
And the result is 1, since the units selected was days, the boundary line is predictably at midnight, so even though the time span is only 2 hours, it would count as 1 day – that is not intuiative. Now this is all documented so we cannot complain or grumble. If you wanted to know whether a full day has passed, you used hourly units instead and made sure you had the logic to deal with this.
All of this leaves you with a pretty bad resolution however, you can get the hours difference, but minutes and seconds are not available – so you have to datediff on those units and do some maths. It really makes for a ham-fisted way of getting a duration.
So people work around the problem by converting the date to the numeric equivalent and manipulating that directly.
declare @date1 datetime set @date1 = '20100101 12:15:30' select convert(float,@date1) 40177.5107638889
The decimal part represents the percentage through the day, which is not really how the underlying binary storage stores it, it uses a number to represent the number of 1/300ths of a second since the day started.
This format was very forgiving though, if you wanted to add a day, instead of using DateAdd, you could just add 1 to the number, very convienient.
It does however make it easier to create a pseudo-timespan by deducting one date’s numeric representation from another dates, although the code is somewhat long-winded. As a side note, make sure you convert to float and not real – real has not go sufficient accuracy for this to work.
declare @date1 datetime declare @date2 datetime set @date1 = '20100101 12:00:00' set @date2 = '20100102 13:15:35' declare @result float set @result = convert(float,@date2) - convert(float,@date1) declare @DurationDays float declare @DurationTime float declare @DurationHours float declare @DurationMinutes float declare @DurationSeconds float set @DurationDays = floor(@result) set @DurationTime = (@result - floor(@result) ) set @DurationTime = @DurationTime * 86400 set @DurationHours = floor(@DurationTime / 3600) set @DurationTime = @DurationTime - @DurationHours * 3600 set @DurationMinutes = floor(@DurationTime/60) set @DurationTime = @DurationTime - @DurationMinutes * 60 set @DurationSeconds = @DurationTime select @DurationDays as Days, @DurationHours as Hours , @DurationMinutes as Minutes, @DurationSeconds as Seconds Days Hours Minutes Seconds ----------------- ------------------ ----------------- ----------------- 1 1 15 35.0000002188608
Bit of a hack and was it really any shorter or better? Debatable. Whilst it can get time span information out, when used within SQL 2008 using the new datetime2 types, the wheels fall off:
declare @date1 datetime2(7) set @date1 = '20100101 12:00:00' select convert(float,@date1) Msg 529, Level 16, State 2, Line 3 Explicit conversion from data type datetime2 to float is not allowed.
And that is where the problem comes in – the new datetime2 types will not allow the date to be converted to a number, and a number of these developer tricks no longer work.
Most if not all the tricks can be re-written to use multiple date functions with some mathematical logic – and it can be done without the code ever knowing if it had been embedded within a function / stored procedure. Where you would see a less transparent move to the datetime2 data types would be where developers had embedded some of the tricks directly into ad-hoc SQL, that will fail if the type is altered. In an ideal world, is that the code never contains these techniques of course, but we do not all live in that nirvana.
So on the one hand Datetime2 gives great accuracy and can reduce storage, but on the other hands, the tricks used in the past to deal with the inadequacies of the built-in Date functions no longer work.
What I would really like is a revamp of the Date functions and the introduction of a time span type – could be a long wait.
Case Sensitivity in the Query Plan Cache
A surprising facet of the query plan cache is that it matches ad-hoc queries not only on their text, but the case of the query text must exactly match as well. This is documented quite clearly on MSDN although it is a bit of a surprising behaviour. It also does not change based on whether the collation of the server is case-sensitive or not.
The documentation gives a statement on case-sensitivity in the plan cache, but no mention of whether the behaviour changes under ‘Forced’ parameterization, which asks SQL to be more aggressive in extracting query literals and generating a query cache hit – so I decided to have a look and see how it acted in ‘Forced’ vs ‘Simple’.
Whether the database was in ‘Simple’ or ‘Forced’ the behaviour did not change – but it turns out that it is not case-sensitive on keywords, just on the object names.
To show the case sensitivity behaviour I have used the AdventureWorks sample database as a testing ground. Prior to each test I cleared the procedure cache using DBCC FreeProcCache.
I then issued two very simple queries:
SELECT * from humanresources.employee Select * from humanresources.employee
When the query cache is inspected, there are two entries – it remains case-sensitive.
sql_statement execution_count --------------------------------------------- -------------------- SELECT * from humanresources.employee 1 SELECT * from humanresources.employee 1
The query so simple and has no parameters so I suspect the simple / forced parameterization routines must not activate.
If we add a parameter to the query, then the parameterization activates and gets to work on the query string prior to the cache lookup. Both simple and forced are able to cope with such a simple query so both performed the parameterization.
SELECT * FROM humanresources.employee WHERE employeeID = 1 Select * From humanresources.employee Where employeeID = 1
Inspect the query plan cache when running in forced:
sql_statement execution_count --------------------------------------------------------- -------------------- (@0 int)select * from humanresources . employee 2 where employeeID = @0
Inspect the query plan cache when running in simple:
sql_statement execution_count --------------------------------------------------------- -------------------- (@1 tinyint)SELECT * FROM [humanresources].[employee] 2 WHERE [employeeID]=@1
The results show a plan cache hit, but more importantly show up a rather obvious difference in the parameterization routines for each mode:
- Simple changes keywords to upper case, Forced changes them to lowercase.
- Simple places square brackets around the objects, forced does not.
- Simple chooses to replace the literal with a tinyint, Forced uses an Int.
- Simple starts the parameters at @1, forced starts at @0
The differences can be filed under bizarre, strange and just inconsistent, although they do both get the job done, which counts at the end of the day.
What is then disappointing is that the same is not true for the tables and fields named in the query. Changing the case of one of the objects prevents the caching again.
select * from humanresources.employee where EmployeeID = 1 Select * From humanresources.employee where employeeID = 1
Inspect the query cache (this one from forced mode):
sql_statement execution_count -------------------------------------------------------- -------------------- (@0 int)select * from humanresources . employee 1 where EmployeeID = @0 (@0 int)select * from humanresources . employee 1 where employeeID = @0
So we are back to the situation of no cache hit.
It seems very strange that the parameterization only ensures the casing of the keywords is consistant to give it a better chance of a query plan cache hit – if this was a case-insensitive server than it is a valid optimization to try increase the chances of a plan cache hit.
The converse you would think, is that it would be an inherently risky optimization on a case-sensitive database? – but in fact it is an optimization that would never be needed or made – if anything a case-sensitive database server will have a better chance of making a query plan cache hit since all the tables names and field names have to exactly match the stored object names – and so the queries will which have a greater chance of matching each other.
It could clearly do more to try give a match, but I suspect the complications and edge cases, such as database / server case-sensitive collation mis-match account for why it might seem easier than it really would be to make better.
Dynamic Partitioning : What objects are using a Partition Schema? (SQL Tuesday #002)
As part of the look at dynamic partitioning one of the first problems I have come across is finding what objects are currently placing data within a partition schema, this can be both tables as well as indexes for a table or indexes for a view (which can also be partitioned).
This has tied in nicely with Adam Machanic’s SQL Tuesday in which we were to describe a confusing situation and possible solution.
Certainly it has been a bit confusing to get to the bottom of what should be a relatively trivial question – programmatically determine what objects are using my partition schema?
If I am going to auto-balance the partitions then I have to know what objects are using the partition schema and will be affected by any balancing – we can not assume it is a single object, since we know both the table and non-clustered indexes will often be aligned on the same partition schema, as could other tables / objects.
So the first place I chose to check was the system views for the partition objects, sys.partition_functions and sys.partition_schemes – with the partition schemes being where you would expect to start.
SELECT * FROM sys.partition_schemes
| name | data_space_id | type | type_desc | is_default | function_id |
|---|---|---|---|---|---|
| psBalanceLeft | 65601 | PS | PARTITION_SCHEME | 0 | 65536 |
Unfortunately the results from partition schemes view is spectacularly unhelpful, aside from inheriting a number of columns from the data spaces system view, it only adds function_id – being the ID of the partition function used in the schema. It at least has the name of the partition scheme, so that definitely is going to have to be used at a later point.
The immediately useful looking value is the function_id linking the scheme to the partition function, so I had a look inside the partition functions view remember what it has.
SELECT * FROM sys.partition_functions
| name | function_id | type | type_desc | fanout | boundary_values_on_right |
|---|---|---|---|---|---|
| pfBalanceLeft | 65536 | R | RANGE | 10 | 0 |
The output does not particularily lead anywhere useful – the function most certainly is not going to tell me which objects are assigned to it, since the tables / indexes get directly assigned to the partition scheme, this looks a dead end. So the only other option is to go to the data spaces system view:
SELECT ps.name, ds.* FROM sys.partition_schemes ps INNER JOIN sys.data_spaces ds on ps.data_space_id = ps.data_space_id
| name | name | data_space_id | type | type_desc | is_default |
|---|---|---|---|---|---|
| psBalanceLeft | psBalanceLeft | 65601 | PS | PARTITION_SCHEME | 0 |
Not a stellar move – there are no obvious leads here.
So I can obtain the relation between the partition scheme and the storage but that it is so far. Given those two dead ends I next considered the problem from the opposite direction – sys.partitions claims to contain a row for each partitioned tables and index in the database – which should provide another starting point.
SELECT * FROM sys.partitions
Some of the output was as follows:
| partition_id | object_id | index_id | partition_number | hobt_id | rows |
|---|---|---|---|---|---|
| 72057594040549376 | 53575229 | 2 | 1 | 72057594040549376 | 10 |
| 72057594040614912 | 53575229 | 2 | 2 | 72057594040614912 | 10 |
| 72057594040680448 | 53575229 | 2 | 3 | 72057594040680448 | 10 |
| 72057594040745984 | 53575229 | 2 | 4 | 72057594040745984 | 10 |
| 72057594040811520 | 53575229 | 2 | 5 | 72057594040811520 | 10 |
| 72057594040877056 | 53575229 | 2 | 6 | 72057594040877056 | 10 |
| 72057594040942592 | 53575229 | 2 | 7 | 72057594040942592 | 10 |
| 72057594041008128 | 53575229 | 2 | 8 | 72057594041008128 | 10 |
| 72057594041073664 | 53575229 | 2 | 9 | 72057594041073664 | 10 |
| 72057594041139200 | 53575229 | 2 | 10 | 72057594041139200 | 136 |
| 72057594041204736 | 149575571 | 1 | 1 | 72057594041204736 | 10 |
| 72057594041270272 | 149575571 | 1 | 2 | 72057594041270272 | 10 |
| 72057594041335808 | 149575571 | 1 | 3 | 72057594041335808 | 10 |
| 72057594041401344 | 149575571 | 1 | 4 | 72057594041401344 | 10 |
| 72057594041466880 | 149575571 | 1 | 5 | 72057594041466880 | 10 |
| 72057594041532416 | 149575571 | 1 | 6 | 72057594041532416 | 10 |
| 72057594041597952 | 149575571 | 1 | 7 | 72057594041597952 | 10 |
| 72057594041663488 | 149575571 | 1 | 8 | 72057594041663488 | 10 |
| 72057594041729024 | 149575571 | 1 | 9 | 72057594041729024 | 10 |
| 72057594041794560 | 149575571 | 1 | 10 | 72057594041794560 | 136 |
This definitely has my partition schema in there somewhere since I know I have 10 partitions and have set the row quantities up to be 10 rows for the first 9 partitions and 136 rows in the tenth, it is pretty visible.
I’ve also got an indexed view on the same table which explains the duplicate set of values, and a NC index on the table which explained the triplicate set of values I’ve not pasted in. This is in essence what I am after though; finding out which object_id’s reference the partition schema.
A couple of immediate dead ends have also appeared:
- The partition_id looks useful but is the unique id of the partition record not an ID relating to the partition schema.
- The hobt_id is the heap or b-tree pointer for that specific partition so is not going to be able to help us since there are 10 hobt_id’s per object on the schema, all different.
It does however provide the object_id which we know we can translate into a name very easily and a partition_number column which only ever exceeds 1 on a partitioned table. So with a bit of a throw-away style query to just select those with a partition_number of 2 to make sure we only select partitioned objects gave me the following:
SELECT o.Name, s.* FROM sys.partitions s INNER JOIN sys.objects o on s.object_id = o.object_id WHERE partition_number = 2
| Name | partition_id | object_id | index_id | partition_number |
|---|---|---|---|---|
| tblTestBalance | 72057594039304192 | 53575229 | 1 | 2 |
| foo | 72057594039959552 | 85575343 | 0 | 2 |
| tblTestBalance | 72057594040614912 | 53575229 | 2 | 2 |
| vwTestView | 72057594041270272 | 149575571 | 1 | 2 |
So now I can see the partitioned objects from the other direction, but I have found no relation between the objects identified as partitioned and the partition schemas discovered earlier.
There is also an index_id being shown and documented as ‘the index within the object to which this partition belongs’, but this is not an object_id for an index, but the index_id you would normally use within a dbcc in command, 0 for heap, 1 for clustered index etc, so there must be a relation to the sys.indexes view – which when I thought about it made complete sense – the sys.indexes table is really badly named, since it is not a row per index, but a row per heap or index.
Not the best name in the world for it, but let’s move on – given we have both the object ID and the index_id we can join on both / filter in the where clause.
SELECT O.Name, p.* FROM sys.partitions p INNER JOIN sys.objects O on p.object_id = o.object_id INNER JOIN sys.indexes I on O.object_id = I.object_id and p.index_id = I.index_id WHERE partition_number = 2
Same output as before, since I have selected no fields from sys.indexes yet, checking the list an immediate candidate jumped out, data_space_id – I already had an odd-looking data_space_id earlier, so can the index link to it successfully?
SELECT O.Name, ds.name FROM sys.partitions p INNER JOIN sys.objects O on p.object_id = o.object_id INNER JOIN sys.indexes I on O.object_id = I.object_id and P.index_id = I.index_id INNER JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id WHERE partition_number = 2
Which gave the following results:
| name | name |
|---|---|
| tblTestBalance | psBalanceLeft |
| foo | psBalanceLeft |
| tblTestBalance | psBalanceLeft |
| vwTestView | psBalanceLeft |
I then rejoined in the partition schemes using the same join I did earlier:
SELECT O.Name, ds.name FROM sys.partitions p INNER JOIN sys.objects O on p.object_id = o.object_id INNER JOIN sys.indexes I on O.object_id = I.object_id and P.index_id = I.index_id INNER JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id INNER JOIN sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
Running this gave 40 rows, 4 objects x 10 partitions, so I was filtered down to the partitioned objects but I returned too many rows – I could have used a group by clause but it seemed simpler to just select a single partition number, and since I know partition number 1 will always exist, that was the simplest to use.
I am now down all the way to the partition scheme, time to select some of the more interesting columns I found along the way, that are applicable to the dynamic partitioning problem I am looking at, the main one being the object name and type.
The final solution I’ve arrived at to get to the tables / indexes and indexed views using a partition scheme is:
SELECT O.Name as TableName, I.Name as IndexName, I.Type, I.type_desc as IndexType, ps.name as PartitionSchema FROM sys.objects O INNER JOIN sys.partitions p on P.object_id = O.object_id INNER JOIN sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id INNER JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id INNER JOIN sys.partition_schemes ps on ds.data_space_id = ps.data_space_id WHERE p.partition_number = 1
| TableName | IndexName | Type_ID | Type_Desc | PartitionSchema |
|---|---|---|---|---|
| tblTestBalance | PK_tblTestBalance | 1 | CLUSTERED | psBalanceLeft |
| foo | NULL | 0 | HEAP | psBalanceLeft |
| tblTestBalance | testncindex | 2 | NONCLUSTERED | psBalanceLeft |
| vwTestView | IxViewTest | 1 | CLUSTERED | psBalanceLeft |
This can be further filtered to an individual partition scheme based on the name trivially, but the output is giving us the information I am after – a list of what objects and type of object is allocated to the partition schemas.
There should be an easier way to get to this information than joining 5 system views, but that seems to be the only way I could manage to solve the problem.
Dynamic Partitioning : Wishlist
Whilst I consider dynamic partitioning something that really doesn’t serve a valid purpose that I can find yet, I decided to use it as an exercise to program a basic form of it within T-SQL over the coming weeks.
Given a blank piece of paper and some realism, what are the aims for the design and T-SQL:
- Batch based rebalancing – real-time is not realistic so let’s start with an overnight job.
- Choice to Balance by different metrics (Rows vs Physical Storage)
- Balance across a setup-defined fixed number of partitions – so that they do not run out.
- Ability to migrate Filegroups into and out of the Partition Scheme – e.g. schedule them for removal over the coming nights.
- Ability to limit the processing to a window – this is not easy, but a log of earlier migrations would offer guidance on how much processing could be done within an allotted time span.
- Ability to choose the specify the balancing as an online operation – partitioning being enterprise only we can automatically rely on online index rebuilds being available.
That’s not a bad start although I bet it is harder than it sounds.
Let’s just consider the ‘balancing act’ itself regardless of the options. A partition schema is not a database table – which automatically complicates matters since multiple tables and indexes can use the same partition schema. This means that any change to a partition scheme / function will directly affect more than 1 table / index. Any calculations for the number of rows / size of data will have to take all the tables and indexes into account.
It might seem unusual to place more than one table on a partition schema, but it really isn’t. You would commonly place any NC indexes also on the same partition schema to keep them ‘aligned’, so having multiple tables for the same ‘alignment’ purpose shouldn’t seem weird. If you consider the multi-tenancy usage of the partitioned table, then you can see why you could have dozens of tables all on the same partition schema.
These requirements are the starting point for the T-SQL and as I come across issues I will write them up.
Is Dynamic Partitioning in SQL Server Possible?
I often see people asking whether dynamic table partitioning exists in SQL Server, or they provide a scenario that would effectively be asking the same question. So let’s get the easy answer out now – straight out of the box SQL Server has no dynamic partitioning.
To be fair, straight out of the box there is no tooling surrounding partitioning either except for a handful of DMV’s – if you want to automate a rolling window, then you need to program that yourself. SQL Server 2008 added a few bits; it struck me that if you need to use a wizard to turn an existing table into a partitioned table then your not really planning ahead.
So if it is possible to automate a rolling window system, surely it is possible to automate some kind of dynamic partitioning?
Well, that depends on what the definition of ‘dynamic partitioning’ is when it comes to SQL, which would normally be defined by the person who needs the feature to solve their specific issue. Before I start writing up a wish list of options and features to guide me hacking some SQL together to solve the problem – you have to ask; do you really need dynamic partitioning?
Table Partitioning by its nature suits larger volumes of data in a rolling window, where we migrate older data out and bring in new values. However, partitioning has been used for a variety of purposes that it possibly was not considered for originally such as:
- Performance gain through Partition Elimination
- Multi-Tenancy databases, placing each client in a separate partition
Bizarrely each of those reasons has a counter argument:
- Partition elimination only benefits queries that include the partition key in the where clause, otherwise it is detrimental to the query since it requires every partition is examined.
- Aside from the limit of 1000 partitions therefore 1000 customers, security is easier to compromise, upgrades per customer are not possible and the whole backup restore strategy for individual customers get’s very complex since you do not wish to restore the whole table but a single partition.
Back to the question, do we really need dynamic partitioning?
The complexity and scale of most partitioned tables indicates that they should not occur by ‘accident’, and retro-fitting a partitioned table indicates a lack of data modelling / capacity planning. The ‘alternative’ reasons for partitioning, are amongst some of the drivers for the dynamic partitioning request.
To make best use of the partitioned table feature requires planning and design, in which case it does not need to be ‘dynamic’.
That all being said, in the coming posts I am going to write-up my wish list of features to start building a basic dynamic partitioning system and then make it more complex over time – it makes for a fun exercise.
If you have any thoughts on features you would want to see in it, just add them in a comment.
SQL Fascination 