Home SQL Secrets

Spotlight Module Content with Image

 

Newsflash 4

Knowledgy Solutions of Atlanta, Ga opens offices in New York!

 

Predictive Analysis with SQL Server 2008

Predictive Analysis with SQL Server 2008I've always found predictive analysis and data mining to be interesting and fun. I feel like a child walking through a cave amazed at the disoveries they are finding. So I couldn't wait to play around with the new predictive analysis tools in SQL Server 2008 Analysis Services. SSAS provides a complete data mining platform that is easy and fun to use. Organizations can use SSAS to infuse insight and prediction into everyday business decisions. The 2007 Office system uses Pervasive delivery through the Data Mining Add-Ins. These tools are a blast to use and will help you uncover knowledge hidden in your data. The comprehensive development environment and extensible range of innovative data mining algorithms combined with the enterprise-level scalability and manageability of SQL Server Analysis Services makes SQL Server 2008 an ideal and easy way to bring the benefits of predictive analysis to your enterprise. The predictive analysis capabilities of MSSQL 2008 makes it easy to incorporate intelligence into reporting, data integration, OLAP analysis, and business performance monitoring. It will help your business drive increased business agility, create a tangible competitive advantage and bottom line, grow revenues. The ability to extend the data mining technologies of SQL Server through custom algorithms and visualizations, together with the ability to embed predictive functionality into line-of-business applications makes SQL Server 2008 a powerful platform for introducing predictive analysis into existing business processes to add insight and recommendations into every operation.

 

Per-Se Technologies

DBA / Database DeveloperPer-Se Technologies (formerly Medaphis Corporation)Aug 1998 - Sept 2000DBA providing administration and security services, SQL, T-SQL and PL/SQL development, complex query development, performance analysis and SQL optimization for over 100 Oracle and SQL Server database instances reporting and ad hoc querying.
Ulti Clocks content
PDF Print E-mail

Undocumented Secrets


It's always fun to look under the hood of a relational database management system to find out what makes it tick. All of the secret, undocumented code that the vendor creates, not only is a great resource, it allows you to really understand the internal engineering allowing you to optimize the system for outstanding performance in it's particular environment. Here I'll show you some of that hidden code, focusing on Sql Server 2005 and 2008.

In Sql Server 2005, Microsoft implemented a large number of dynamic views for administration, monitoring and performance. These views typically access tables that are invisible to DBA's and anyone else working the system. You won't see them in any tool including Studio Manager. Here is the SQL for some of these dynamic. You'll see that if you try to locate the tables these views are accessing you won't find them and in fact, you'll get an error message that says they don't exist. Here are some more undocumented system views.


Here's the underlying query for the sys.sysmembers dynamic management view:


SELECT
memberuid = CONVERT(smallint, member_principal_id),
groupuid = CONVERT(smallint, role_principal_id)
FROM
sys.database_role_members

 


This is the query the sys.database_role_members. Notice the undocumented invisible system table it's accessing:


SELECT
indepid AS role_principal_id,
depid AS member_principal_id
FROM
sys.sysmultiobjrefs
WHERE
class = 25
AND
depsubid = 0
AND
indepsubid = 0
AND
Has_access('RL', depid, indepid) = 1 -- MRC_DBROLEMEMBER

 



The sys.sysobjects dynamic view:

 

CREATE VIEW sys.sysobjects
            AS
SELECT name,
            id,
            xtype = TYPE COLLATE database_default,
            uid = CONVERT(SMALLINT,nsid),
            info = CONVERT(SMALLINT,0),
            status = CONVERT(INT,0),
            base_schema_ver = CONVERT(INT,0),
            replinfo = CONVERT(INT,0),
            parent_obj = pid,
            crdate = created,
            ftcatid = CONVERT(SMALLINT,CASE TYPE
                    WHEN 'U' THEN OBJECTPROPERTY(id,'TableFulltextCatalogId')
        ELSE 0
        END),
            schema_ver = CONVERT(INT,0),
            stats_schema_ver = CONVERT(INT,0),
            TYPE = CONVERT(CHAR(2),CASE TYPE
                    WHEN 'UQ' THEN 'K'
                    WHEN 'PK' THEN 'K'
        ELSE TYPE COLLATE database_default
        END),
            userstat = CONVERT(SMALLINT,CASE TYPE
                    WHEN 'S' THEN 1
                    WHEN 'U' THEN 1
        ELSE 0
        END),
            sysstat = CONVERT(SMALLINT,CASE TYPE
                    WHEN 'S' THEN 1
                    WHEN 'V' THEN 2
                    WHEN 'U' THEN 3
                    WHEN 'P' THEN 4
                    WHEN 'RF' THEN 4
                    WHEN 'X' THEN 4
                    WHEN 'D' THEN 6
                    WHEN 'R' THEN 7
                    WHEN 'TR' THEN 8
                    WHEN 'PK' THEN 9
                    WHEN 'UQ' THEN 9
                    WHEN 'C' THEN 10
                    WHEN 'F' THEN 11
        ELSE 0
        END),
            indexdel = CONVERT(SMALLINT,0),
            refdate = created,
            version = CONVERT(INT,0),
            deltrig = CONVERT(INT,CASE TYPE
                    WHEN 'U' THEN OBJECTPROPERTY(id,'TableDeleteTrigger')
                    WHEN 'TR' THEN pid
        ELSE 0
        END),
            instrig = CONVERT(INT,CASE TYPE
                    WHEN 'U' THEN OBJECTPROPERTY (id,'TableInsertTrigger')
        ELSE 0
        END),
            updtrig = CONVERT(INT,CASE TYPE
                    WHEN 'U' THEN OBJECTPROPERTY(id,'TableUpdateTrigger')
        ELSE 0
        END),
            seltrig = CONVERT(INT,0),
            category = CONVERT(INT,CASE
                    WHEN TYPE = 'D'
                AND pid <> 0 THEN 2048
        ELSE 0
        END
                + CASE
                    WHEN TYPE = 'P'
                AND (status & 256) <> 0 THEN 16
        ELSE 0
        END
                + (status & 1)
                * 2
                + (status & 16)
                * 2),
            cache = CONVERT(SMALLINT,0)
    FROM sys.sysschobjs
    WHERE nsclass = 0
        AND pclass = 1
        AND Has_access('CO',id) = 1
    UNION ALL
SELECT name,
            id = OBJECT_ID,
            xtype = TYPE COLLATE database_default,
            uid = CONVERT(SMALLINT,schema_id),
            info = CONVERT(SMALLINT,0),
            status = CONVERT(INT,0),
            base_schema_ver = CONVERT(INT,0),
            replinfo = CONVERT(INT,0),
            parent_obj = Sysconv(INT,0),
            crdate = create_date,
            ftcatid = CONVERT(SMALLINT,0),
            schema_ver = CONVERT(INT,0),
            stats_schema_ver = CONVERT(INT,0),
            TYPE = TYPE COLLATE database_default,
            userstat = CONVERT(SMALLINT,0),
            sysstat = CONVERT(SMALLINT,CASE TYPE
                    WHEN 'V' THEN 2
                    WHEN 'P' THEN 4
                    WHEN 'X' THEN 4
        ELSE 0
        END),
            indexdel = CONVERT(SMALLINT,0),
            refdate = create_date,
            version = CONVERT(INT,0),
            deltrig = CONVERT(INT,0),
            instrig = CONVERT(INT,0),
            updtrig = CONVERT(INT,0),
            seltrig = CONVERT(INT,0),
            category = CONVERT(INT,2),
            cache = CONVERT(SMALLINT,0)
    FROM sys.system_objects$
            sys.key_constraints
CREATE
            VIEW sys.key_constraints AS
SELECT
            o.name,
            o.OBJECT_ID,
            o.principal_id,
            o.schema_id,
            o.parent_object_id,
            o.type,
            o.type_desc,
            o.create_date,
            o.modify_date,
            o.is_ms_shipped,
            o.is_published,
            o.is_schema_published,
            idx.indid AS unique_index_id,
            o.is_system_named
    FROM
            sys.objects$ o
            LEFT JOIN sys.sysidxstats idx
            ON
            idx.id = o.parent_object_id 
        AND
            idx.name = o.name
    WHERE
            o.type IN ( 'PK', 'UQ' )
            sys.sql_dependencies
CREATE VIEW
            sys.sql_dependencies
            AS
SELECT
            dp.class,
            i.name AS class_desc,
            dp.depid AS OBJECT_ID,
            dp.depsubid AS column_id,
            dp.indepid AS referenced_major_id,
            dp.indepsubid AS referenced_minor_id,
            sysconv ( bit, dp.status & 4 ) AS is_selected, -- OBJDEP_READCS
            sysconv ( bit, dp.status & 2 ) AS is_updated, -- OBJDEP_RESULT
            sysconv ( bit, dp.status & 1 ) AS is_select_all -- OBJDEP_SELECT
    FROM
            sys.sysmultiobjrefs dp
            LEFT JOIN sys.syspalvalues i
            ON
            i.class = 'DPCL' 
        AND
            i.value = dp.class
    WHERE
            dp.class <= 4 -- MRC_OLDDEPENDS,MRC_EXPRTOOBJ,MRC_EXPRTOTYPE,MRC_EXPRTOXMLNS,MRC_EXPRTOPRTFUNC