Robs Online Reference

Friday, January 4, 2013

sys.dm_db_stats_properties() Test Script - Update 1

-- Ref:
--  http://www.jasonstrate.com/2012/10/what-happened-to-rowmodctr-from-sysindexes/
--  http://msdn.microsoft.com/en-us/library/jj553546.aspx
--  http://www.sqlskills.com/blogs/erin/post/new-statistics-dmf-in-sql-server-2008r2-sp2.aspx
--  http://persistencevision.blogspot.com/2012/12/sysdmdbstatsproperties-initial-results.html
--  http://persistencevision.blogspot.com/2012/12/sysdmdbstatsproperties-initial-results_20.html

USE tempdb;
GO
PRINT @@VERSION;
GO
IF 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)
);
GO
CREATE NONCLUSTERED INDEX IX_Test1_ColId_WhereDeletedEq1 ON dbo.Test1 (
   ColId
) WHERE Deleted = 1;
GO
CREATE NONCLUSTERED INDEX IX_Test1_StatusId_WhereDeletedEq1 ON dbo.Test1 (
   StatusId
) WHERE Deleted = 1;
GO
CREATE NONCLUSTERED INDEX IX_Test1_Deleted ON dbo.Test1 (
   Deleted
);
GO
CREATE NONCLUSTERED INDEX IX_Test1_StatusId ON dbo.Test1 (
   StatusId
);
GO
-- ==================== Initial insert
INSERT INTO dbo.Test1 DEFAULT VALUES;
GO 10000
-- ==================== Call sys.dm_db_stats_properties()
SELECT
   s.name,
   CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
   p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO
-- ==================== UPDATE STATISTICS
UPDATE STATISTICS dbo.Test1;
GO
-- ==================== Call sys.dm_db_stats_properties()
SELECT
   s.name,
   CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
   p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO
-- ==================== Update 50% of records (StatusId, Deleted)
UPDATE dbo.Test1
SET
   StatusId = 2,
   Deleted  = 1
WHERE ColId % 2 = 0;
PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);
GO
-- ==================== Call sys.dm_db_stats_properties()
SELECT
   s.name,
   CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
   p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
  AND s.name IN ('IX_Test1_Deleted', 'IX_Test1_StatusId')
ORDER BY s.has_filter, s.name;
GO
-- ==================== Update 1 record (StatusId, Deleted)
UPDATE dbo.Test1
SET
   StatusId = 2,
   Deleted  = 1
WHERE ColId = 1
  AND Deleted <> 1;
PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);
SELECT * FROM dbo.Test1 WHERE ColId = 1;
GO
--WAITFOR DELAY '00:00:00.001';
--GO
-- ==================== Call sys.dm_db_stats_properties()
SELECT
   s.name,
   CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
   p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
  AND s.name IN ('IX_Test1_Deleted', 'IX_Test1_StatusId')
ORDER BY s.has_filter, s.name;
GO

sys.dm_db_stats_properties() Test Script

-- Ref:
--  http://www.jasonstrate.com/2012/10/what-happened-to-rowmodctr-from-sysindexes/
--  http://msdn.microsoft.com/en-us/library/jj553546.aspx
--  http://www.sqlskills.com/blogs/erin/post/new-statistics-dmf-in-sql-server-2008r2-sp2.aspx
--  http://persistencevision.blogspot.com/2012/12/sysdmdbstatsproperties-initial-results.html
--  http://persistencevision.blogspot.com/2012/12/sysdmdbstatsproperties-initial-results_20.html

USE tempdb;
GO
PRINT @@VERSION;
GO
IF 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)
);
GO
CREATE NONCLUSTERED INDEX IX_Test1_ColId_WhereDeletedEq1 ON dbo.Test1 (
   ColId
) WHERE Deleted = 1;
GO
CREATE NONCLUSTERED INDEX IX_Test1_StatusId_WhereDeletedEq1 ON dbo.Test1 (
   StatusId
) WHERE Deleted = 1;
GO
CREATE NONCLUSTERED INDEX IX_Test1_Deleted ON dbo.Test1 (
   Deleted
);
GO
CREATE NONCLUSTERED INDEX IX_Test1_StatusId ON dbo.Test1 (
   StatusId
);
GO
-- ==================== Initial insert
INSERT 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_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO
-- ==================== UPDATE STATISTICS
UPDATE 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_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO
-- ==================== Update 50% of records (StatusId, Deleted)
UPDATE dbo.Test1
SET
   StatusId = 2,
   Deleted  = 1
WHERE 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_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO
-- ==================== Update 1 record (StatusId, Deleted)
UPDATE dbo.Test1
SET
   StatusId = 2,
   Deleted  = 1
WHERE ColId = 1
  AND Deleted <> 1;
PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);
SELECT * FROM dbo.Test1 WHERE ColId = 1;
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_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO

Monday, April 16, 2012

iTunes purchase issue

Tuesday, June 22, 2010

Image 1:

Image 2:

Thursday, April 1, 2010

2000 Mazda Miata

Reference: http://portland.craigslist.org/wsc/cto/1672484061.html

XL:

Monday, January 4, 2010

Permissions issue in SSMS

Related stackoverflow question:

Permissions issue in SSMS: “The SELECT permission was denied on the object ‘extended_properties’, database ‘mssqlsystem_resource’, … Error 229)”

Here is the specific portion of the larger command that is causing the permissions issue:

SELECT major_id FROM sys.extended_properties

Below is the complete command that causes the error. This was pulled from a trace. The user was robg_test.

EXEC sp_executesql N'

        DECLARE @is_policy_automation_enabled bit

        SET @is_policy_automation_enabled  = (SELECT CONVERT(bit, current_value)

                                              FROM msdb.dbo.syspolicy_configuration

                                              WHERE name = ''Enabled'')

SELECT

''Server[@Name='' + quotename(CAST(

        serverproperty(N''Servername'')

       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/Table[@Name='' + quotename(tbl.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(tbl.schema_id),'''''''') + '']'' AS [Urn],

tbl.name AS [Name],

SCHEMA_NAME(tbl.schema_id) AS [Schema],

CAST(

 case

    when tbl.is_ms_shipped = 1 then 1

    when (

        select

            major_id

        from

            sys.extended_properties

        where

            major_id = tbl.object_id and

            minor_id = 0 and

            class = 1 and

            name = N''microsoft_database_tools_support'')

        is not null then 1

    else 0

end   

             AS bit) AS [IsSystemObject],

tbl.create_date AS [CreateDate],

stbl.name AS [Owner],

case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like ''Server'' + ''/Database\[@ID='' + convert(nvarchar(20),dtb.database_id) + ''\]''+ ''/Table\[@ID='' + convert(nvarchar(20),tbl.object_id) + ''\]%'' ESCAPE ''\'') then 1 else 0 end AS [PolicyHealthState]

FROM

master.sys.databases AS dtb,

sys.tables AS tbl

INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, ''OwnerId'')))

WHERE

(CAST(

 case

    when tbl.is_ms_shipped = 1 then 1

    when (

        select

            major_id

        from

            sys.extended_properties

        where

            major_id = tbl.object_id and

            minor_id = 0 and

            class = 1 and

            name = N''microsoft_database_tools_support'')

        is not null then 1

    else 0

end   

             AS bit)=@_msparam_0)and((db_name()=@_msparam_1)and(dtb.name=db_name()))

ORDER BY

[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'RGTest'