I love to see a creative solution to a technical problem.
How can I get that user out of my table quickly by JackLi
Great creative solution using indexed persisted computed columns.
The beauty of indexed persisted computed columns is that, yes, you have to pay the cost of doing the computation, but you only pay that cost at the point where you INSERT or UPDATE that particular column. If the data is mostly static as it is in this case, the one-time cost is very small.
Monday, January 28, 2013
Wednesday, January 16, 2013
You Can’t Sacrifice Partition Tolerance
Just finished reading this excellent article by Coda Hale:
I thought the bit about yield versus uptime was very interesting, as was the bit about harvest (harvest = data available/complete data). Historically, [the corporate] we have built systems that sacrifice yield in the event of a failure. I like the challenge/opportunity of building a system that reduces harvest but maintains yield.
Thursday, December 20, 2012
sys.dm_db_stats_properties() - Initial Results on SQL 2008 R2 SP2
My first sys.dm_db_stats_properties() post showed results for SQL Server 2012 SP1. Here, I will show how the results for filtered indexes on 2008 R2 SP2 are different.
The test script is exactly the same as before, just running on a different version.
Here are the only differences:
Filtered indexes are excluded from these results where they return NULLs in 2012.
The difference appears to be in the fact that the function returns a row in 2012 for a filtered index and does not return a row in 2008 R2 for a filtered index.
The test script is exactly the same as before, just running on a different version.
MSDN Docs
The 2008 R2 version of the docs is here. Because the results were different, I pulled the text for both the 2012 document and the 2008 R2 document and compared them. There are no substantive differences.Here are the only differences:
2008 R2 SP2"Requires that the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."
2012 SP1 adds the highlighted section"Requires that the user has select permissions on statistics columns or the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."
Results
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Beginning execution loop
Batch execution completed 10000 times.
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-20 09:10:09 10000 10000 132 10000 0
IX_Test1_Deleted NULL 2012-12-20 09:10:09 10000 10000 1 10000 0
IX_Test1_StatusId NULL 2012-12-20 09:10:09 10000 10000 1 10000 0
PK_Test1_ColId NULL 2012-12-20 09:10:09 10000 10000 3 10000 0
Update Count: 5000
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-20 09:10:09 10000 10000 132 10000 0
IX_Test1_Deleted NULL 2012-12-20 09:10:09 10000 10000 1 10000 5000
IX_Test1_StatusId NULL 2012-12-20 09:10:09 10000 10000 1 10000 5000
PK_Test1_ColId NULL 2012-12-20 09:10:09 10000 10000 3 10000 0
Observations
Filtered Index ResultsFiltered indexes are excluded from these results where they return NULLs in 2012.
The difference appears to be in the fact that the function returns a row in 2012 for a filtered index and does not return a row in 2008 R2 for a filtered index.
Wednesday, December 19, 2012
sys.dm_db_stats_properties() - Initial Results on SQL 2012 SP1
The SQL Server team just released a new feature, the DMF sys.dm_db_stats_properties(). This new feature is available in 2008 R2 SP2 and 2012 SP1.
These tests were run on SQL Server 2012 SP1 and will illustrate what the function returns in the normal case.
First, some references:
Not surprisingly, the result columns are all NULL before calling UPDATE STATISTICS.
Steps
Steps is an interesting statistic. Steps indicates the "number of steps in the histogram." Note that "The maximum number of steps is 200." (ref) You won't get 200 by simply adding more rows. Inserting 100,000 rows instead of 10,000 gives a steps value of 56 for the GUID index.
Filtered Indexes
I was never able to get filtered rows to return any statistics even though the docs say for the rows column, "If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table."
Is this a bug in SQL Server or in my query?
modification_counter
As expected, modification_counter is 0 before there are any modifications and after modifying 5,000 rows, the counts are 5,000 (except for the filtered indexes).
These tests were run on SQL Server 2012 SP1 and will illustrate what the function returns in the normal case.
First, some references:
- sys.dm_db_stats_properties at MSDN
- What Happened to ROWMODCTR from SYSINDEXES? by Jason Strate
- New Statistics DMF in SQL Server 2008R2 SP2 by Erin Stellato of SQLskills
The test code below will
- create a new table with multiple indexes.
- insert 10,000 rows, then UPDATE STATISTICS.
- call sys.dm_db_stats_properties().
- update the StatusId and Deleted columns of 50% of the rows.
- call sys.dm_db_stats_properties().
Test Code
USE tempdb;GOPRINT @@VERSION;GOIF EXISTS (SELECT * FROM sys.tables WHERE OBJECT_ID('dbo.Test1') IS NOT NULL) DROP TABLE dbo.Test1;CREATE TABLE dbo.Test1 ( ColId INT IDENTITY, ColGuid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), StatusId tinyint NOT NULL DEFAULT 1, Deleted bit NOT NULL DEFAULT 0, CONSTRAINT PK_Test1_ColId PRIMARY KEY CLUSTERED (ColId), CONSTRAINT AK_Test1_ColGuid UNIQUE (ColGuid) );GOCREATE NONCLUSTERED INDEX IX_Test1_ColId_WhereDeletedEq1 ON dbo.Test1 ( ColId) WHERE Deleted = 1;GOCREATE NONCLUSTERED INDEX IX_Test1_StatusId_WhereDeletedEq1 ON dbo.Test1 ( StatusId) WHERE Deleted = 1;GOCREATE NONCLUSTERED INDEX IX_Test1_Deleted ON dbo.Test1 ( Deleted);GOCREATE NONCLUSTERED INDEX IX_Test1_StatusId ON dbo.Test1 ( StatusId);GO-- ==================== Initial insertINSERT INTO dbo.Test1 DEFAULT VALUES;GO 10000-- ==================== Call sys.dm_db_stats_properties()SELECT s.name, s.filter_definition, CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated, p.[rows], p.rows_sampled, p.steps, p.unfiltered_rows, p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO-- ==================== UPDATE STATISTICSUPDATE STATISTICS dbo.Test1;GO-- ==================== Call sys.dm_db_stats_properties()SELECT s.name, s.filter_definition, CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated, p.[rows], p.rows_sampled, p.steps, p.unfiltered_rows, p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO-- ==================== Update 50% of records (StatusId, Deleted)UPDATE dbo.Test1SET StatusId = 2, Deleted = 1WHERE ColId % 2 = 0;PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);GO-- ==================== Call sys.dm_db_stats_properties()SELECT s.name, s.filter_definition, CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated, p.[rows], p.rows_sampled, p.steps, p.unfiltered_rows, p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO
Results
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Beginning execution loop
Batch execution completed 10000 times.
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ---- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL NULL NULL NULL NULL NULL NULL
IX_Test1_Deleted NULL NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId NULL NULL NULL NULL NULL NULL NULL
PK_Test1_ColId NULL NULL NULL NULL NULL NULL NULL
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-19 14:59:41 10000 10000 136 10000 0
IX_Test1_Deleted NULL 2012-12-19 14:59:41 10000 10000 1 10000 0
IX_Test1_StatusId NULL 2012-12-19 14:59:41 10000 10000 1 10000 0
PK_Test1_ColId NULL 2012-12-19 14:59:41 10000 10000 3 10000 0
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
Update Count: 5000
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------------- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-19 14:59:41 10000 10000 136 10000 0
IX_Test1_Deleted NULL 2012-12-19 14:59:41 10000 10000 1 10000 5000
IX_Test1_StatusId NULL 2012-12-19 14:59:41 10000 10000 1 10000 5000
PK_Test1_ColId NULL 2012-12-19 14:59:41 10000 10000 3 10000 0
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
Observations
NULLsNot surprisingly, the result columns are all NULL before calling UPDATE STATISTICS.
Steps
Steps is an interesting statistic. Steps indicates the "number of steps in the histogram." Note that "The maximum number of steps is 200." (ref) You won't get 200 by simply adding more rows. Inserting 100,000 rows instead of 10,000 gives a steps value of 56 for the GUID index.
Filtered Indexes
I was never able to get filtered rows to return any statistics even though the docs say for the rows column, "If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table."
Is this a bug in SQL Server or in my query?
modification_counter
As expected, modification_counter is 0 before there are any modifications and after modifying 5,000 rows, the counts are 5,000 (except for the filtered indexes).
Subscribe to:
Posts (Atom)