Search results matching tag 'partitioning'

Public Release, SQL Server File Layout Viewer

merrillaldrich — Fri, 01 Mar 2013 21:36:00 GMT

Version 1.0 is Now Available!

I’ve been working off and on, as my real job permits, on this visualization tool for SQL Server data files. This is an educational or exploratory tool where you can more readily see how the individual data pages in MDF/NDF files are organized, where your tables and indexes live, what effect operations like index rebuild or index reorganize have on the physical layout of the data pages.

The viewer will scan a whole database, using only SQL and DBCC commands, and will render a color-coded representation of all the data pages represented in colored bands. Each partition of each index or heap in the database is assigned a color, so that you can see where all the bits and pieces of an object are located in the files. Above the colored bands there are grayscale or white pixels that show the page type in SQL Server (most are white, which are data pages. Unused/empty regions of the file show as gray). In the image above, for example, all the bright green areas are one index, all the purple areas are one index, and so on.

There is mouse-over functionality. If you move the mouse cursor over the graph, then details about each page populate the text fields at right, including the object and index the page belongs to, the page type, whether the page represents a fragment, where the previous and next pages are for the same object, etc.

Why?

Why create something like this? I am a visual person, and I have a theory that many issues we have in computing come down to not being able to see what’s going on. This is especially true as we learn about unfamiliar technology – we have to develop a mental model of structures like B-trees or linked lists or files in order to understand what’s happening. I hope this tool, combined with other knowledge, will help people form an accurate understanding of how data file internals work in SQL Server, faster than working purely in the abstract with tools like DBCC Page or DBCC Ind.

Instructions

Download the tool and unzip it. The package includes both an executable and the source code. If you don’t want the source, the .exe file is a standalone program and will run all on its own, so you are welcome to discard the source folder.
Validate you have the required prerequisites from the Prereq’s section below.
Locate a non-production/test database to analyze. The database can be local or on a remote server. I suggest something of a reasonable size, because scanning a really huge data set can take quite a long time.
Run SQLFileLayoutViewer.exe and select a database to scan. If the database is on a remote server, type the SQL Server name/instance name into the dialog.
Click Analyze.
Examine the resulting graph, and mouse over it with the cursor to view detailed information about each page.

Disclaimer

This is a freeware tool provided for your fun, education and entertainment. However, there is no warranty of any kind and you use it at your sole risk. The tool is free but offered under the GNU General Public License 3. If successful, and people are interested, I’ll move this work to some sort of open source project.

Prerequisites

The app requires .NET Framework 4.0 and the SQL Server management tools. I’ve tested it on Windows 7, Windows Server 2008 R2 and Windows 8. It can be run against a database on a local or remote SQL instance. I believe it will work on any database in SQL Server 2005 or later, but have not tested every possible scenario.

Risks?

I believe this tool to be relatively risk free, but I would avoid running it against live production data. The tool’s data collection is simple: it will issue a few system table selects to get things like object names, and then it will execute a DBCC PAGE statement against every page in the database. All other processing after that is done locally in the application itself. It does not modify the database.

Bugs?

I would love to hear about bugs you come across, or additional features you think would be valuable. Please contact me through this site. Note that I am a DBA first, and an amateur .NET developer a distant second, so please be gentle.

Enjoy!

Updated Warehouse Re-Index Script

merrillaldrich — Tue, 15 Jan 2013 00:30:00 GMT

As I talked about in my last post, I just went through a re-indexing project that took the partitioned fact rows from our warehouse and relocated them into new files. There are a lot of tables and indexes involved, so I have a PowerShell “helper” script to generate the actual T-SQL that moves the data. The idea is to find all the indexes that use a particular partition scheme in the database, and make the CREATE INDEX statements that would recreate them on the new partition scheme. This script doesn’t do the re-indexing work, it just outputs the T-SQL that would do the work, so that the SQL can be verified and/or edited and run later, in the context of other modifications to the warehouse.

(This is a much improved version of the work described in this even older post. It frankly wasn’t all that great back then.)

The script was developed on PowerShell 2 and SQL Server 2012 client tools against SQL Server 2005, but I believe the work that the script does (the process of generating the T-SQL and the results) is compatible with any SQL Server EE 2005 or later. It assumes you’ve loaded the SQL provider or SQLPS module into PowerShell.

 <#
 PowerShell Script to generate CREATE INDEX statements from existing indexes
 mapped to a new partition scheme.

 14 Jan 2013 Merrill Aldrich
 Free for your use, but without warranty. Use at your sole risk.
 #>

 $ErrorActionPreference = 'Stop'

 # Create a connection to the SQL Server instance
 Set-Location SQLSERVER:\SQL\<yourserver>\DEFAULT\

 # Names of existing and new partition schemes. New will replace existing in script output:
 $oldPartitionScheme = 'nameOfOldScheme'
 $newPartitionScheme = 'nameOfNewScheme'

 # Compose a query that will list out the tables that use a specific partition scheme
 $partitionedTables = @( Get-ChildItem .\Databases\<yourdatabase>\tables |
     Where-Object -filter { $_.PartitionScheme -eq $oldPartitionScheme } )

 # For each partitioned table, script out create statements for partitioned indexes
 $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ( get-item ( get-location ) )

 # Set scripting options here as needed
 # $scripter.Options.<someoption> = <somevalue>
 $scripter.Options.ScriptDrops = $False

 $partitionedTables | foreach {
     # Note current table in output:
     "/***  Table $($_.Schema).$($_.Name)   ***/"

     $partitionedIndexes = ( $_.indexes |
         Where-Object -Filter { $_.IsPartitioned -eq $True -and $_.PartitionScheme -eq $oldPartitionScheme }
      )

     # Script create statements

     $partitionedIndexes | foreach {
         $indexCreateScr = $scripter.Script( $_ )

         # Change the partition scheme for every statement to the new partition scheme
         $indexCreateScr = $IndexCreateScr -replace $oldPartitionScheme, $newPartitionScheme

         # Change ALTER TABLE ADD CONSTRAINT to CREATE INDEX statements for PKs
         $indexCreateScr = $indexCreateScr -replace `
             'ALTER TABLE (\[[^\]]*\])\.(\[[^\]]*\]) ADD  CONSTRAINT (\[[^\]]*\]) PRIMARY KEY CLUSTERED', `
             ('/* $& */' + "`n" + 'CREATE UNIQUE CLUSTERED INDEX $3 ON $1.$2')

         # For ALTER TABLEs the DROP_EXISTING index option has to be added to the script
         # Find any "WITH (" clause not containing the DROP_EXISTING option, and add it
         $indexCreateScr = $IndexCreateScr -replace 'WITH \((?!.*DROP_EXISTING)', 'WITH ( DROP_EXISTING = ON, '

         # Change index create options, if necessary. Edit to suit:
         $indexCreateScr = $IndexCreateScr -replace 'PAD_INDEX = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'STATISTICS_NORECOMPUTE = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'IGNORE_DUP_KEY = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'SORT_IN_TEMPDB = OFF, ', ''

         $indexCreateScr = $IndexCreateScr -replace 'ONLINE = OFF', 'ONLINE = ON'
         $indexCreateScr = $IndexCreateScr -replace 'DROP_EXISTING = OFF', 'DROP_EXISTING = ON'

         # Insert some line terminators for legibility
         $indexCreateScr = $IndexCreateScr -replace "ON \[$($newPartitionScheme)\]", "`nON [$($newPartitionScheme)]"
         $indexCreateScr = $IndexCreateScr -replace ', ', ", `n`t"

         # Output the revised script
         $indexCreateScr
         "GO`n"
     }
 }

Most of this is pretty basic – put the tables into a collection, loop over them, and for each table go through each partitioned index, scripting them out. The only parts that were tricky to develop involve the indexes that support primary keys.

The scripter from SMO wants to script out a PK as “ALTER TABLE … ADD CONSTRAINT,” and the problem with that is you can’t use it to recreate the existing index using the DROP_EXISTING option. But, in fact, in SQL Server it is perfectly valid to do a CREATE INDEX … WITH ( DROP_EXISTING = ON ) against the index that supports a primary key. It’s just that the scripter isn’t designed to deal with that idea (as far as I know).

I searched around for some solution to this issue to no avail, but instead fell back on good old hacky find and replace. There are a few semi-hairy regex expressions in the script that locate instances of ALTER TABLE … ADD CONSTRAINT and substitute the equivalent CREATE INDEX statement, and also locate the WITH clauses that don’t have the DROP_EXISTING option, and add that. The gibberish parts of those expressions are mostly looking for object names and escaping square brackets.

If it’s not clear what this sort of thing means (\[[^\]]*\])\.(\[[^\]]*\]) drop me a comment and I’ll try to clarify. Conversely, if you are better at regex than I am, which is likely, any advice you might have to simplify this would be welcome!

A side note about regex and PowerShell:

If you are used to using regex in the find and replace dialog in SSMS / Visual Studio, it’s not the same flavor of regex that PowerShell uses, and there are some important differences:

Tagging expressions/ Grouping is accomplished with parens (), not curlies {}

Substitution is done with dollar signs $1 and not backslashes \1

AND, epically confusing, the dollar signs in regex are not the dollar signs in PowerShell. So
"$1" and '$1'
in a –replace expression are different in important and confusing ways. One will look for a PowerShell variable $1 and try to expand it, while the other is the regex matching group #1 and does regex replacement.

What makes this sad is that the regex one might use in editing T-SQL in SSMS can’t be moved over to PowerShell without a lot of changes.

Why Doesn’t Partition Elimination Work?

Paul White — Tue, 11 Sep 2012 20:52:00 GMT

Given a partitioned table and a simple SELECT query that compares the partitioning column to a single literal value, why does SQL Server read all the partitions when it seems obvious that only one partition needs to be examined?

Sample Data

The following script creates a table, partitioned on the char(3) column ‘Div’, and populates it with 100,000 rows of data:

     USE Sandpit;

    GO

    CREATE PARTITION FUNCTION PF (char(3))

    AS RANGE RIGHT FOR VALUES

    (

        '1', '2', '3', '4', '5', '6', '7', '8', '9',

        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',

        'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',

        'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'

    );

    GO

    CREATE PARTITION SCHEME PS

    AS PARTITION PF

    ALL TO ([PRIMARY]);

    GO

    CREATE TABLE dbo.Test

    (

        Div     char(3) NOT NULL,

        Data    char(50) NOT NULL,

    )

    ON PS(Div);

    GO

    INSERT dbo.Test WITH (TABLOCKX)

        (Div, Data)

    SELECT TOP (100000)

        CONVERT(char(3), CONVERT(char(36), NEWID())),

        CONVERT(char(50), REPLICATE('X', 50))

    FROM sys.columns AS c

    CROSS JOIN sys.columns AS c2

    CROSS JOIN sys.columns AS c3;

Sample data:

The ‘Div’ column is pseudo-random so your results will be slightly different, but this is the distribution across partitions I saw:

    SELECT

        PartitionID = F.PartitionID,

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t WITH (TABLOCK)

    CROSS APPLY (VALUES($PARTITION.PF(t.Div))) AS F (PartitionID)

    GROUP BY

        F.PartitionID

    ORDER BY

        F.PartitionID;

(The partition function defines a total of 36 partitions but only 16 are used by the sample data.)

The Test Query

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF';

Since ‘Div’ is the partitioning column, the expectation is that only one partition will be scanned to count the rows that match the WHERE clause predicate. However, the execution plan shows that query execution scans all 36 partitions (a full table scan):

Why did SQL Server not just look at the one partition it knows the value ‘ABF’ must lie in?

Here’s Why:

The answer lies in the predicate:

The value ‘ABF’ is nowhere to be seen; it has been replaced by [@1]. This means the query has been considered for simple parameterization by SQL Server to promote query plan reuse. The presence of the [@1] does not mean that simple parameterization was successful (considered safe for all possible values by the optimizer). We have to check the plan cache to see if an associated prepared plan exists and is reused for different ‘parameter’ values. It turns out that this time the simple parameterization attempt is considered safe, so a prepared plan is created and is reused for different values. The full parameterized text is:

(@1 varchar(8000))SELECT COUNT_BIG(*) [RowCnt] FROM [dbo].[Test] [t] WHERE [t].[Div]=@1

Static Partition Elimination

The reason SQL Server cannot apply static partition elimination (determining the partition number at compile time) is that the plan now contains a parameter. Subsequent executions that reuse the plan might specify a different value for the parameter, so static elimination would not be safe. It is not possible to disable simple parameterization, but we can prevent it applying to this query in a number of ways. One way is to incorporate an inequality predicate of the form expression <> constant:

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF'

        AND 1 <> 2;

The redundant 1 <> 2 predicate is completely removed by the optimizer, and the query still qualifies for TRIVIAL optimization as it did previously:

There are a number of interesting things to notice here:

The query is no longer parameterized (the literal value ‘ABF’ appears)
Only one partition is touched (partition 11)
This unindexed heap table has a seek predicate
The ‘ordered’ property has changed from False to True

The problem with this plan is that it cannot be reused for different predicate values – so you could end up with a separate cached plan for each possible literal value.

Adding OPTION (RECOMPILE) also defeats simple parameterization and therefore achieves static elimination. In an ad-hoc SQL batch, OPTION (RECOMPILE) also means the query plan will not be cached (stored procedures are not so lucky). The downside is a fresh trip through the optimizer on each call. This query does qualify for trivial plan, so optimization time is minimal, but more complex queries with the same desire for partition elimination might well require full optimization.

Dynamic Partition Elimination

Ideally, we would like a query plan that is reusable but still only touches one partition for any given string literal value. What we need is dynamic partition elimination. In case the concept is new to you, the optimizer can produce plans that determine the correct partition at execution time (rather than at compile-time, as for static elimination). To achieve this, the optimizer builds a plan that uses an internal function called RangePartitionNew(). One might think the original simple-parameterized plan ought to have included this feature; the reason it didn’t is quite interesting.

When SQL Server parameterizes a query using simple or forced parameterization, it has to decide on a data type for the parameter. For string literals, it always chooses varchar(8000) for non-Unicode strings and nvarchar(4000) for Unicode strings. If it chose a specific type like varchar(3), we could end up with the same plan cache bloating and non-reuse problem as for the non-parameterized case – and there are potentially 8000 different lengths for varchar. Also, prior to SQL Server 2005 SP2, the parameter-declaration part of the query text was not included in the hash value used to decide in which cache bucket to store the plan. This could result in hundreds of same-text plans (with different parameters) occupying the same hash bucket (this does not work well).

Anyway, faced with the parameterized predicate Div = [@1], where Div is char(3) and [@1] is varchar(8000), dynamic partition elimination does not occur because the value of [@1] might be truncated. One way to address this is to explicitly convert the to-be-parameterized string literal to the same type as the target column (char(3)):

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(char(3), 'CDC');

Now we have a parameterized query plan that uses dynamic partition elimination:

This plan has all the interesting features of the static elimination plan, but the heap seek no longer selects a hard-coded partition ID. The single partition searched is the one returned at runtime by the call to RangePartitionNew(). The explicit convert prevents data truncation when the parameterized value is compared with the partition boundary values (defined as char(3) in the partition function). The extra CONVERT_IMPLICIT to char(3) is required for internal reasons, and only appears with non-Unicode partition function types.

If we explicitly convert to char(4) instead of char(3), the possibility of truncation arises again and we are back to scanning all 36 partitions:

Using varchar(3) instead of char(3) also results in dynamic elimination, which surprises some people:

Implicit Conversions and Precedence

An alternative explanation I have seen for the original behaviour is that the string literal ‘ABF’ is implicitly typed as a varchar(3) by SQL Server and data type precedence means the char(3) ‘Div’ column has to be implicitly converted to varchar(3) to perform the comparison. This is incorrect, but it is interesting to look at why that is so:

Literal Types

We can see the implicit type of the string literal ‘ABF’ using a query:

    SELECT

        BaseType = SQL_VARIANT_PROPERTY(V.v, 'BaseType'),

        MaxLength = SQL_VARIANT_PROPERTY(V.v, 'MaxLength')

    FROM (VALUES(CONVERT(sql_variant, 'ABF'))) AS V (v);

The implied type of ‘ABF’ does seem to be varchar(3), so that checks out.

Type Precedence

The data type precedence table in Books Online shows that varchar does indeed have a higher precedence than char:

Also, from the Books Online topic Data Types (Transact-SQL):

The example below uses a UNION ALL over char(5) and varchar(3) columns:

    DECLARE @T1 AS TABLE (col1 char(5) NOT NULL);

    DECLARE @T2 AS TABLE (col1 varchar(3) NOT NULL);

    INSERT @T1 VALUES ('12345');

    INSERT @T2 VALUES ('123');

    SELECT

        BaseType = SQL_VARIANT_PROPERTY(CONVERT(sql_variant, t.col1), 'BaseType'),

        MaxLength = SQL_VARIANT_PROPERTY(CONVERT(sql_variant, t.col1), 'MaxLength'),

        t.col1

    FROM

    (

        SELECT col1 FROM  @T1 

        UNION ALL

        SELECT col1 FROM @T2

    ) AS t;

The result column ‘col1’ has to have a well-defined type. As expected, the result is varchar(5) since varchar has a higher precedence than char and 5 is the maximum length of the result. All good so far.

Strings Are Special

We get used to seeing (and preferably avoiding) implicit conversions in query plans where two mismatched types are compared:

    DECLARE @T1 AS TABLE (col1 integer NULL);

    DECLARE @v tinyint;

    SELECT col1 FROM @T1 AS t WHERE col1 = @v;

The plan shows the tinyint variable being implicitly converted to the higher-precedence integer data type, as expected. Now let’s do the same thing with varchar and char (perhaps expecting char to be converted to the higher-precedence varchar):

    DECLARE @T1 AS TABLE (col1 varchar(5) NULL);

    DECLARE @v char(3);

    SELECT col1 FROM @T1 AS t WHERE col1 = @v;

Note the lack of an implicit conversion in the predicate.

Important

SQL Server can compare char with varchar without performing a conversion. They are fundamentally the same type, it’s just that one is fixed-length and the other is variable-length. The SQL standard requires padding for the character strings used in comparisons so that their lengths match before comparing, thereby removing the only distinction between char and varchar. The same optimization can apply to a comparison between nchar and nvarchar (though not to Unicode versus non-Unicode comparisons, since they are genuinely different types of course).

The present case involves a predicate “WHERE Div = ‘ABF’”. This is a comparison that can use this optimization, so there is no implicit conversion. This is the reason the explicit conversion to varchar(3) works in the main example – the precedence rules do not need to be invoked, and the data column is not converted from char(3) to varchar(3).

Why Use a Partitioned Heap?

In case you were wondering, the only reason I chose a partitioned heap for this post is just because I could (it’s more interesting in many ways). All the demonstrations work just as well for a partitioned clustered table. A complete script using a clustered partitioned table is below:

    USE Sandpit;

    GO

    CREATE PARTITION FUNCTION PF (char(3))

    AS RANGE RIGHT FOR VALUES

    (

        '1', '2', '3', '4', '5', '6', '7', '8', '9',

        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',

        'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',

        'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'

    );

    GO

    CREATE PARTITION SCHEME PS

    AS PARTITION PF

    ALL TO ([PRIMARY]);

    GO

    CREATE TABLE dbo.Test

    (

        ID      integer IDENTITY NOT NULL,

        Div     char(3) NOT NULL,

        Data    char(50) NOT NULL,

        CONSTRAINT PK__dbo_Test_Div_ID

        PRIMARY KEY CLUSTERED (Div, ID)

        ON PS(Div)

    );

    GO

    INSERT dbo.Test WITH (TABLOCKX)

        (Div, Data)

    SELECT TOP (100000)

        CONVERT(char(3), CONVERT(char(36), NEWID())),

        CONVERT(char(50), REPLICATE('X', 50))

    FROM sys.columns AS c

    CROSS JOIN sys.columns AS c2

    CROSS JOIN sys.columns AS c3;

    GO

    -- Show the distribution of data

    SELECT

        PartitionID = F.PartitionID,

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t WITH (TABLOCK)

    CROSS APPLY (VALUES($PARTITION.PF(t.Div))) AS F (PartitionID)

    GROUP BY

        F.PartitionID

    ORDER BY

        F.PartitionID;

    GO

    -- Seek on all 36 partitions

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF';

    GO

    -- Static elimination = 1 hard-coded partition ID

    -- Cached plan unlikely to be reused

    SELECT

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF'

        AND 1 <> 2;

    GO

    -- Static elimination

    -- Compilation overhead

    SELECT

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF'

    OPTION (RECOMPILE);

    GO

    -- Dynamic elimination with convert to char(3)

    -- Reusable plan

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(char(3), 'ABF');

    GO

    -- Dynamic elimination with convert to varchar(3)

    -- Data type precedence not applied:

    -- varchar(3) convert does not cause the char(3)

    -- column Div to be converted (would prevent a seek)

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(varchar(3), 'ABF');

    GO

    -- char(4) means truncation could occur when

    -- comparing with the char(3) partition function

    -- No partition elimination, seek on 36 partitions

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(char(4), 'ABF');

    GO

    -- Clean up

    DROP TABLE dbo.Test;

    DROP PARTITION SCHEME PS;

    DROP PARTITION FUNCTION PF;

This post applies to SQL Server 2008, 2008 R2 and 2012 only (partitioned table query execution was very different in 2005). As far as I can tell, SQL Server 2005 did not attempt simple parameterization on a partitioned table. I’m in two minds whether the SQL Server 2008+ behaviour is a bug, an oversight, or an undesirable consequence of fixing something else…so I opened a Connect item for it. Update: fixing this is under consideration for the next release.

Sean Broadley raises an important practical point in the comments below – the issue highlighted in this post only occurs when a literal value is used. By correctly parameterizing your queries (including application code and dynamic SQL) you will avoid simple parameterization, achieve dynamic partition elimination, and cache a more reusable query plan.

Acknowledgement

Thanks to Jonathan Kehayias (blog | twitter) who first altered me to the MSDN forum question that prompted this post.

Paul White
Twitter: @SQL_Kiwi
Email: SQLkiwi@gmail.com

Connect Digest : 2011-09-22

AaronBertrand — Thu, 22 Sep 2011 11:31:00 GMT

Where is SYSDATE()?

I've talked about this one before, but due to a recent conversation about building a datetime value from separate date/time columns or variables, it may be worth bringing up again. When SQL Server 2008 introduced several new date/time types, they also added new built-in functions, like SYSDATETIME() and SYSDATETIMEOFFSET(). But they forgot about the same functions they forgot about back when GETDATE() and GETUTCDATE() were introduced: functions that return only date or only time. So we're still stuck with using implicit conversion to assign CURRENT_TIMESTAMP or SYSDATETIME() to a DATE or TIME variable, only to throw away the irrelevant part.

Connect #293333 : SQL 2008: Add sysdate()

Where is my built-in sequence?

In Denali we've seen the addition of SEQUENCE, an easy and more efficient way to centralize IDENTITY-like identifier values. But often we want to generate these numbers without incrementing a counter or running a bunch of inserts. To generate numbers on the fly in cases where I haven't been able to create my own numbers table, I often use master..spt_values.number, convoluted CTEs, or ROW_NUMBER() OVER () a cross join against arbitrary system tables. Why can't there be an in-memory table of numbers that we could reference, without all this extra scaffolding?

Connect #258733 : Add a built-in table of numbers

Why is CLR still so hard?

For ages I've wished it were easier to implement CLR solutions. RegEx is still not in SQL Server, and CLR has proven much more efficient than traditional XML, numbers table and looping methods in dealing with string splitting operations as well. And probably dozens of use cases that I haven't had the necessity to research myself. But it is often hard to help folks implement this on their own, never mind deal with strict DBA/management environments where CLR is still off-limits. Wouldn't it be great if it were far easier to implement C# code directly within a stored procedure or function, without all the overhead of deploying a DLL, creating an assembly, etc.

Connect #265266 : Add server-side compilation ability to SQL CLR

Will CASE always evaluate in left-to-right order?

The documentation for CASE states that the WHEN expressions are evaluated in textual order. Most people infer from this that any subsequent expression will not be evaluated, but there have been several cases where this has not been true. While I didn't find this particular manifestation of the bug (that was Itzik), and while I do hope they correct this scenario, I'm much more interested in clarity in the documentation so that people know whether or not they can rely on this behavior to always be true.

Connect #690017 : CASE / COALESCE won't always evaluate in textual order

Why can't I have partition-level stats?

Partitioning is great, but if you have disproportionate partition sizes, or a lot of partitions, statistics can become a real challenge. Consider a table with 100 million rows, but only 1 million rows in the currently active partition. If you want to update the statistics on the active partition, your probable approach today is to update statistics on the whole table. (The same type of issue exists with filtered indexes - auto statistics updates kicks in based on the row count in the table, not the subset in the index.)

Connect #468517 : Update Statistics at the partition level

Connect #328093 : Statistics are saved on table level and not on partition level

Connect #457024 : Update statistics, top 100 percent and Sort warnings

sys2 scripts updated

manowar — Thu, 07 Jan 2010 20:54:10 GMT

I’ve updated my “sys2” scripts with three 3 new scripts:

sys2.objects_dependencies
A wrapper around sys.sql_expression_dependencies that shows also related informations taken from sys.object table, like object name, object type and schema name of the referencing entity.

sys2.objects_partition_ranges
Shows information on partitioned table like rows per partition, partition ranges and partition filegroup destination.

sys2.objects_data_spaces
List tables and indexes and show in which filegroup they reside.

They are freely available – along with all the others – here:

http://www.davidemauri.it/resources/sql-scripts.aspx

Enjoy!

Connect Digest : 2009-11-25

AaronBertrand — Wed, 25 Nov 2009 20:21:00 GMT

Can we improve on CHECKSUM()?

Mike C# is not happy with the reliability of CHECKSUM() and asks for a better function that has less (or no) chance of collisions.

#513376 : A Better Collision-Free Hash Function for Comparing Rows of Data

Service termination and fatal exceptions are bad, m'kay?

Victor Lobanov found an easy way to crash SQL Server, by using obscure but legitimate values as terminators in a BULK INSERT statement.

#513252 : Certain values for ROWTERMINATOR field in BULK INSERT crash SQL Server 2008 SP1

And Jeremiah Peschka found another bug involving parallel queries, this time leading to a fatal exception (C000005 EXCEPTION_ACCESS_VIOLATION).

#514477 : Parallel plans combined with PIVOT and UNION ALL produce fatal exception

SSMS has bugs? Say it ain't so!

Jonathan Kehayias pointed out two bugs with the Database Mail Wizard, which seem to be tightly correlated, even though one has been closed as Won't Fix, and one is active but allegedly a duplicate of an existing work item.

#207602 : Max of 1 Database Mail Wizard open at a time
#463953 : SSMS Configure Database Mail does not setup mail correctly in some circumstances

And earlier this week, I was futzing around with the partition wizards that finally appeared in the 2008 version of SSMS. While it's great to see them, it is too bad that, like the Policy-Based Management wizards, they have similar restrictions on valid syntax that is accepted for things like date literals.

#513997 : SSMS : Create a Partition Wizard does not accept YYYYMMDD

Connect digest : 2009-06-20

AaronBertrand — Sat, 20 Jun 2009 15:11:00 GMT

Here are the Connect items I'd like to draw your attention to this week. I have abandoned the idea of trying to keep track of vote counts and overall rating. Being lazy might be a small part of it, but it is mostly because I just don't think it makes sense to assume that my postings are all that influence Connect behavior. I know I have some impact on the visibility of certain items, but I'm going to leave it at that.

====================================

SQL Server 2008 connection pooling problems

Something to watch out for if you are using connection pooling and SQL Server 2008:

#468478 : SQL Server 2008 Periodically Does Not Accept Connections

====================================

Statistics on partitions

As partitioning becomes more of a viable and beneficial option, new requirements are creeping out of the woodwork, such as the ability to update statistics per partition instead of for the entire object:

#468517 : Update Statistics at the partition level

====================================

DBCC SHRINKFILE() flexibility

While personally I think the need is not all that dire, some people want to be able to shrink a database file to a size smaller than the original allocation (which is currently prevented by SQL Server). Joe Sack made the request formal:

#467285 : Shrink files beneath originally allocated space

====================================

Distributed transactions

Erland has filed a couple of interesting issues regarding distributed transactions:

#466739 : There should be a system function to tell whether the current transaction is a distributed transaction

#466749 : Cannot call stored procedures when a distributed transaction has been rolled back

====================================

Clickable URLs in error messages

Finally, Adam is asking for URLs in the messages pane (e.g. those you could embed in custom messages or ad hoc within RAISERROR) to be clickable. I think it makes a lot of sense, though it is currently resolved as "won't fix"...

#454907 : Make URLs clickable in the SSMS Messages pane

====================================

Please remember, I am not trying to coerce you to vote for issues you don't care about, just trying to raise awareness for some items that might have slipped under your radar...

Validating What is Stored in a Partition/Filegroup

sarahhen — Mon, 23 Jul 2007 13:13:00 GMT

How do you validate what is stored in a specific partition and the filegroup the partition resides on? I have found this handy when designing a partition strategy and to double check it is behaving as I expect and my data is stored correctly for the requirements. It also can help if you document as part of your disaster recovery planning, you can then quickly refer to your documentation and identify the specific filegroup you need to restore to recover specific partitions.

For example, you have a data warehouse that stores previous fiscal year data by quarter for Fiscal Year 2005 through 2006. Your partition function and scheme are defined as follows (see end of notes if you want the filegroup creation so you can step through these commands):

create partition function pf_MyFyQuarters (datetime) as

range right for values

(

'2004-10-01 00:00:00','2005-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00', -- 2005

'2005-10-01 00:00:00','2006-01-01 00:00:00','2006-04-01 00:00:00','2006-07-01 00:00:00') -- 2006

create partition scheme ps_MyFyQuarters

as partition pf_MyFyQuarters

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9

fgAfter2006) -- partition 10

When SQL creates the partition scheme, it maps in order each partition to the filegroup(s) listed and each are numbered in order as the comments demonstrate above. In this example, because the partition function is defined with a 'range right' - partition 1 (assigned to file group fgBefore2005) stores all values less than 10/1/2004. Partition 2 (assigned to file group fg2005Quarter1) stores values >= 10/1/2004 and < 01/01/2005 and so on through partition 10 (assigned to fgAfter2006) that stores values >= 07/01/2006.

Next, to validate the scenario, create a table on the partition scheme with some data:

CREATE TABLE [dbo].[PartitionTable]

(

[DateColumn] [datetime] NULL

)

ON ps_MyFyQuarters (DateColumn)

insert PartitionTable

Values('09/30/2004') -- This will go on partition 1 (file group fgBefore2000)

insert PartitionTable

Values('09/30/2005') -- partition 5 (file group fg2005Quarter4)

insert PartitionTable

Values('10/01/2005') -- partition 6 (file group fg2006Quarter1)

insert PartitionTable

Values('03/31/2006') -- partition 7 (file group fg2006Quarter2)

insert PartitionTable

Values('07/04/2006') -- partition 9 (file group fg2006Quarter4)

You can then use the $PARTITION function to validate what is actually stored by the partition number and the corresponding filegroup based on what you defined in your partition scheme earlier:

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 1

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 5

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 6

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 7

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 9

You can also query from the DMV to list out the partition/scheme and validate what is stored on your filegroups. For example, another administrator created another partition and scheme - but accidentally stores it on the same filegroups. Note what the DMV query returns after you create another partition function and scheme:

create partition function pf_MyFyQuarters_B (datetime) as

range right for values

(

'2002-10-01 00:00:00','2003-01-01 00:00:00','2003-04-01 00:00:00','2003-07-01 00:00:00', -- 2003

'2003-10-01 00:00:00','2004-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00') -- 2004

create partition scheme ps_MyFyQuarters_B

as partition pf_MyFyQuarters_B

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5:

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9:

fgAfter2006) -- partition 10:

-- this DMV query will list out what schemes and partition numbers are stored on your filegroups

-- note the different scheme and partition on the same filegroups

select ds.name AS [Filegroup Name], ds.type, destination_id AS [partition number], dds.partition_scheme_id, ps.name as [partition scheme]

from sys.data_spaces ds

join sys.destination_data_spaces dds

on (ds.data_space_id = dds.data_space_id)

join sys.partition_schemes ps

on (ps.data_space_id = dds.partition_scheme_id)

order by ds.name, ps.name ASC

--------- File/Filegroup Script For Examples Above --------------

create database my_db

-- Add filegroups that will contains partitioned values

alter database my_db add filegroup fgBefore2005;

alter database my_db add filegroup fg2005Quarter1;

alter database my_db add filegroup fg2005Quarter2;

alter database my_db add filegroup fg2005Quarter3;

alter database my_db add filegroup fg2005Quarter4;

alter database my_db add filegroup fg2006Quarter1;

alter database my_db add filegroup fg2006Quarter2;

alter database my_db add filegroup fg2006Quarter3;

alter database my_db add filegroup fg2006Quarter4;

alter database my_db add filegroup fgAfter2006;

-- Add files to filegroups

alter database my_db add file (name = 'fF05Q1', filename = 'C:\fF05Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter1;

alter database my_db add file (name = 'fF05Q2', filename = 'C:\fF05Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter2;

alter database my_db add file (name = 'fF05Q3', filename = 'C:\fF05Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter3;

alter database my_db add file (name = 'fF05Q4', filename = 'C:\fF05Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter4;

alter database my_db add file (name = 'fF06Q1', filename = 'C:\fF06Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter1;

alter database my_db add file (name = 'fF06Q2', filename = 'C:\fF06Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter2;

alter database my_db add file (name = 'fF06Q3', filename = 'C:\fF06Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter3;

alter database my_db add file (name = 'fF06Q4', filename = 'C:\fF06Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter4;

alter database my_db add file (name = 'fBefore05', filename = 'C:\fBefore05.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgBefore2005;

alter database my_db add file (name = 'fAfter06', filename = 'C:\fAfter06.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgAfter2006;