May, 2014 | SQL RNNR

Auditing and Event SubClasses

Categories: News, Professional, Scripts, SSC

Tags: Audit, SQL Administration, SQL Script, TSQL

Comments: No Comments

Published on: May 28, 2014

A recent discussion got me to thinking about Auditing. To be honest, it got started with a complaint about some documentation that seemed overly light about the various fields related to auditing as it stands in SQL Server.

In talking to the person who raised the valid concern about the lack of good documentation, I was curious why he suddenly had so many questions about auditing and its functionality within SQL Server. Reflecting on the answers, it seems that it made good sense and it all kind of fell into place with the whole Audit Life Cycle. I hadn’t previously considered the Life Cycle, but it makes sense to map it out. Here is a simple rendition of what an audit Life Cycle might entail.

In order to audit anything, it is necessary to know what you really want to audit, why you want to audit it and how to achieve those goals within the tools given to you. In that vein, it makes sense that one would need to study up on the topic to figure out what different things meant within the tool.

Of course, once you start collecting that data, then you also need to figure out how to measure it and then to determine if adjustments to the auditing plan need to be made. In the end, it boils down to what is the data to be collected, what are you doing with that data and what does that data represent.

In our simple discussion, the data trying to be understood was related to the Event Subclass field in this View (sys.trace_subclass_values) and in this Microsoft document (one of several). The beauty of this field is that it is not just tied to Auditing, but you will also find it in Profiler, server side traces, and Extended Events.

With so little information to help understand what the field values represent, maybe it is better to just turn to the data to help understand what the values might represent or how to interpret them. To do this, we can query a few catalog views as in the following query.

[codesyntax lang=”tsql”]

DECLARE @ParentEvent VARCHAR(20) = '%Audit%';

SELECT TE.name AS event_name, TSV.subclass_name,TSV.subclass_value 
FROM sys.trace_events AS TE 
	INNER JOIN sys.trace_subclass_values AS TSV 
		ON TE.trace_event_id = TSV.trace_event_id 
	INNER JOIN sys.trace_columns AS TC 
		ON TSV.trace_column_id = TC.trace_column_id
		AND TC.name = 'EventSubClass'
WHERE TE.name LIKE @ParentEvent
ORDER BY event_name;

DECLARE @ParentEvent VARCHAR(20) = '%Audit%';

SELECT TE.name AS event_name, TSV.subclass_name,TSV.subclass_value

FROM sys.trace_events AS TE

INNER JOIN sys.trace_subclass_values AS TSV

ON TE.trace_event_id = TSV.trace_event_id

INNER JOIN sys.trace_columns AS TC

ON TSV.trace_column_id = TC.trace_column_id

AND TC.name = 'EventSubClass'

WHERE TE.name LIKE @ParentEvent

ORDER BY event_name;

[/codesyntax]

With the above query, I can filter down to just the Event Types that have Audit in the name. Or I could add a different filter so I can try and better understand the different subclasses in a more focused effort.

I hope this helps in your efforts to provide a better auditing or “profiling” type of experience in your environment.

Can you partition a temporary table?

by Jason Brimhall

Categories: News, Professional, Scripts, SQLServerPedia Syndication, SSC

Tags: administration, Partitioning, SQL Administration, SQL Myth, SQL Script

Comments: 6 Comments

Published on: May 23, 2014

Reading that title, you might sit and wonder why you would ever want to partition a temporary table. I too would wonder the same thing. That withstanding, it is an interesting question that I wanted to investigate.

The investigation started with a fairly innocuous venture into showing some features that do apply to temp tables which are commonly mistaken as limitations (i.e. don’t work with temp tables). To show this I set off to create a script with reproducible results to demonstrate these features. I have included all of those in the same script I will provide that demonstrates the answer to the partitioning question.

In fact lets just jump to that script now.

SET NOCOUNT ON;
USE tempdb;
GO

IF OBJECT_ID('#hubbabubba','U') IS NOT NULL
BEGIN
	DROP TABLE #hubbabubba;
END

CREATE TABLE #hubbabubba (
	someint INT PRIMARY KEY NONCLUSTERED IDENTITY(1,1)
	,somechar VARCHAR(50)
	,somedate DATE
	,somebit BIT DEFAULT(0))

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name = 'PartitionToPrimary')
	BEGIN
		DROP PARTITION SCHEME PartitionToPrimary
	END
IF EXISTS (SELECT name FROM sys.partition_functions WHERE name = 'PartitionByMonth')
	BEGIN
		DROP PARTITION FUNCTION PartitionByMonth
	END

CREATE PARTITION FUNCTION PartitionByMonth (DATE)
	AS RANGE RIGHT 
	FOR VALUES ('2014/01/01', '2014/02/01', '2014/03/01', '2014/04/01', '2014/05/01','2014/06/01'
		, '2014/07/01', '2014/08/01', '2014/09/01', '2014/10/01', '2014/11/01', '2014/12/01');

CREATE PARTITION SCHEME PartitionToPrimary 
	AS PARTITION PartitionByMonth 
	ALL TO ([PRIMARY]);

CREATE CLUSTERED INDEX idx_hubba_somedate ON #hubbabubba (somedate)
	ON PartitionToPrimary (somedate);
GO

/* 
Establish a Date range to be used for Random date generation and table population
We only have the table partitioned for the current year so limiting the dates to this
year is essential
*/
DECLARE @BeginDate DATE = '2014-01-01'
		,@EndDate DATE = '2014-12-31'

/* Populate some data */
INSERT INTO #hubbabubba
        ( somechar,somedate )
VALUES  ( 'DidmyDefaultApply?'
	,DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate))
GO 5000

SELECT *
	FROM #hubbabubba;

USE tempdb;
GO

sp_help '#hubbabubba'
/* Demonstrates the existence of 
2 constraints on the temp table 
2 indexes on the temp table
	1 clustered (supports the partition)
	1 nonclustered

*/
/* Base query for the following attributed to Kendra Little 
This demonstrates that Partitions can be created on temp tables 
*/
SELECT  OBJECT_NAME(si.object_id) AS object_name ,
		pf.name AS pf_name ,
        ps.name AS partition_scheme_name ,
        p.partition_number ,
        rv.value AS range_value ,
        SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
                    ELSE 0
            END) AS num_rows ,
        SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,
        SUM(CASE ISNULL(si.index_id, 0)
                WHEN 0 THEN 0
                ELSE 1
            END) AS num_indexes
FROM    sys.destination_data_spaces AS dds
        INNER JOIN sys.data_spaces AS ds 
			ON dds.data_space_id = ds.data_space_id
        INNER JOIN sys.partition_schemes AS ps 
			ON dds.partition_scheme_id = ps.data_space_id
        INNER JOIN sys.partition_functions AS pf 
			ON ps.function_id = pf.function_id
        LEFT OUTER JOIN sys.partition_range_values AS rv 
			ON pf.function_id = rv.function_id
            AND dds.destination_id = CASE pf.boundary_value_on_right
                                        WHEN 0 THEN rv.boundary_id
                                        ELSE rv.boundary_id + 1
                                    END
        LEFT OUTER JOIN sys.indexes AS si 
			ON dds.partition_scheme_id = si.data_space_id
        LEFT OUTER JOIN sys.partitions AS p 
			ON si.object_id = p.object_id
			AND si.index_id = p.index_id
			AND dds.destination_id = p.partition_number
        LEFT OUTER JOIN sys.dm_db_partition_stats AS dbps 
			ON p.object_id = dbps.object_id
            AND p.partition_id = dbps.partition_id
WHERE p.OBJECT_ID = OBJECT_ID('#hubbabubba','U')
GROUP BY p.partition_number ,pf.name,ps.name ,si.object_id ,rv.value;
GO

100

101

102

103

104

105

106

107

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('#hubbabubba','U') IS NOT NULL

BEGIN

DROP TABLE #hubbabubba;

END

CREATE TABLE #hubbabubba (

someint INT PRIMARY KEY NONCLUSTERED IDENTITY(1,1)

,somechar VARCHAR(50)

,somedate DATE

,somebit BIT DEFAULT(0))

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name = 'PartitionToPrimary')

BEGIN

DROP PARTITION SCHEME PartitionToPrimary

END

IF EXISTS (SELECT name FROM sys.partition_functions WHERE name = 'PartitionByMonth')

BEGIN

DROP PARTITION FUNCTION PartitionByMonth

END

CREATE PARTITION FUNCTION PartitionByMonth (DATE)

AS RANGE RIGHT

FOR VALUES ('2014/01/01', '2014/02/01', '2014/03/01', '2014/04/01', '2014/05/01','2014/06/01'

, '2014/07/01', '2014/08/01', '2014/09/01', '2014/10/01', '2014/11/01', '2014/12/01');

CREATE PARTITION SCHEME PartitionToPrimary

AS PARTITION PartitionByMonth

ALL TO ([PRIMARY]);

CREATE CLUSTERED INDEX idx_hubba_somedate ON #hubbabubba (somedate)

ON PartitionToPrimary (somedate);

Establish a Date range to be used for Random date generation and table population

We only have the table partitioned for the current year so limiting the dates to this

year is essential

DECLARE @BeginDate DATE = '2014-01-01'

,@EndDate DATE = '2014-12-31'

/* Populate some data */

INSERT INTO #hubbabubba

( somechar,somedate )

VALUES ( 'DidmyDefaultApply?'

,DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate))

GO 5000

SELECT *

FROM #hubbabubba;

USE tempdb;

sp_help '#hubbabubba'

/* Demonstrates the existence of

2 constraints on the temp table

2 indexes on the temp table

1 clustered (supports the partition)

1 nonclustered

/* Base query for the following attributed to Kendra Little

This demonstrates that Partitions can be created on temp tables

SELECT OBJECT_NAME(si.object_id) AS object_name ,

pf.name AS pf_name ,

ps.name AS partition_scheme_name ,

p.partition_number ,

rv.value AS range_value ,

SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows

ELSE 0

END) AS num_rows ,

SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,

SUM(CASE ISNULL(si.index_id, 0)

WHEN 0 THEN 0

ELSE 1

END) AS num_indexes

FROM sys.destination_data_spaces AS dds

INNER JOIN sys.data_spaces AS ds

ON dds.data_space_id = ds.data_space_id

INNER JOIN sys.partition_schemes AS ps

ON dds.partition_scheme_id = ps.data_space_id

INNER JOIN sys.partition_functions AS pf

ON ps.function_id = pf.function_id

LEFT OUTER JOIN sys.partition_range_values AS rv

ON pf.function_id = rv.function_id

AND dds.destination_id = CASE pf.boundary_value_on_right

WHEN 0 THEN rv.boundary_id

ELSE rv.boundary_id + 1

END

LEFT OUTER JOIN sys.indexes AS si

ON dds.partition_scheme_id = si.data_space_id

LEFT OUTER JOIN sys.partitions AS p

ON si.object_id = p.object_id

AND si.index_id = p.index_id

AND dds.destination_id = p.partition_number

LEFT OUTER JOIN sys.dm_db_partition_stats AS dbps

ON p.object_id = dbps.object_id

AND p.partition_id = dbps.partition_id

WHERE p.OBJECT_ID = OBJECT_ID('#hubbabubba','U')

GROUP BY p.partition_number ,pf.name,ps.name ,si.object_id ,rv.value;

In the beginning (after dropping objects if they exist), I start by creating a temp table that has a couple of mythical limitations. These mythical creatures are that temp tables can’t have indexes or that they can’t have constraints.

In this script, I show that a temp table (#hubbabubba) can indeed have indexes created on it (clustered and nonclustered). I also demonstrate the creation of two different kinds of constraints on the #hubbabubba table. The two constraints are a primary key and a default constraint. That stuff was easy!!

To figure out whether or not one could partition a temporary table, I needed to do more than simply create a “test” temp table. I had to create a partitioning function and a partitioning scheme and then tie that partition scheme to a clustered index that I created after table creation. Really, this is all the same steps as if creating partitioning on a standard (non-temporary) table.

With that partitioning scheme, function and the table created it was time to populate with enough random data to seem like a fair distribution. You see, I created a partition function for each month of the year 2014. To see partitioning in action, I wanted to see data in each of the partitions.

That brings us to the final piece of the whole script. Kendra Little produced a script for viewing distribution of data across the partitions so I used her script to demonstrate our data distribution. If you run the entire script including the data distribution segment at the end, you will see that there are 13 partitions with each of the monthly partitions containing data.

The distribution of data into the different partitions demonstrates soundly that partitioning can not only be created on a temporary table, but that it can be used. As for the secondary question today “Why would you do that?”, I still do not know. The only reason that pops into my mind is that you would do it purely for demonstration purposes. I can’t think of a production scenario where partitioning temporary data would be a benefit. If you know of a use case, please let me know.

Supported Compatibility Levels in SQL Server

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: administration, Scripts, SQL Administration, SQL Script

Comments: 1 Comment

Published on: May 21, 2014

It has been well documented and is well known that SQL Server supports certain older versions of SQL Server in a compatibility mode. This setting is something that can be configured on the database properties level. You can quickly change to an older compatibility level or revert the change to a newer compatibility level.

Changing the compatibility level is sometimes necessary. Knowing what compatibility modes are available for each database is also somewhat necessary. The common rule of thumb has been the current version and two prior versions. But even with that, sometimes it is warm and fuzzy to be able to see the supported versions in some sort of format other than through the GUI for database properties.

Sure, one could go and check Books Online. You can find that information there. Or you could fire up this script and run with the output (as a guideline).

Declare @buggers int;
Set @buggers = 0x1000000;

WITH compatversions AS (
	SELECT 65 AS MajorVersion ,'SQL Server 6.5' AS ServerVersion
UNION
SELECT 70,'SQL Server 7.0' 
UNION
SELECT 80 , 'SQL Server 2000' 
UNION 
SELECT 90 , 'SQL Server 2005' 
UNION 
SELECT 100 , 'SQL Server 2008/R2' 
UNION
SELECT 110 , 'SQL Server 2012' 
UNION 
SELECT 120 , 'SQL Server 2014' 
)

SELECT TOP 3 ServerVersion,MajorVersion
		,ServerVersion + ' ('+ CONVERT(VARCHAR(3),MajorVersion) +')' AS DropDownFormat
	FROM compatversions
	WHERE MajorVersion IN (
		SELECT TOP 3 MajorVersion 
			FROM compatversions
			WHERE MajorVersion <= CONVERT(INT,CAST(@@microsoftversion / @buggers AS VARCHAR(3)) + '0')
			ORDER BY MajorVersion DESC) 
	ORDER BY MajorVersion ASC;

Declare @buggers int;

Set @buggers = 0x1000000;

WITH compatversions AS (

SELECT 65 AS MajorVersion ,'SQL Server 6.5' AS ServerVersion

UNION

SELECT 70,'SQL Server 7.0'

UNION

SELECT 80 , 'SQL Server 2000'

UNION

SELECT 90 , 'SQL Server 2005'

UNION

SELECT 100 , 'SQL Server 2008/R2'

UNION

SELECT 110 , 'SQL Server 2012'

UNION

SELECT 120 , 'SQL Server 2014'

)

SELECT TOP 3 ServerVersion,MajorVersion

,ServerVersion + ' ('+ CONVERT(VARCHAR(3),MajorVersion) +')' AS DropDownFormat

FROM compatversions

WHERE MajorVersion IN (

SELECT TOP 3 MajorVersion

FROM compatversions

WHERE MajorVersion <= CONVERT(INT,CAST(@@microsoftversion / @buggers AS VARCHAR(3)) + '0')

ORDER BY MajorVersion DESC)

ORDER BY MajorVersion ASC;

This script will return results such as the following.

And if we wanted to see the results for a SQL Server 2014 installation, we would see the following.

The output is displayed in the same format you might see it if you were to use the Database Properties GUI. That said, if you are using the GUI in SQL Server 2014, you might run into the following.

Notice the additional compatibility level for SQL 2005? If you check the documentation, you will probably find that compatibility level 90 is not supported in SQL 2014. In fact it says that if a database is in 90 compatibility, it will be upgraded to 100 automatically (SQL 2008). You can find all of that and more here.

If you tried to select compatibility 90, you might end up with an error. If you are on 2014 CTP2, you will probably be able to change the compat level without error.

Anyway, this is the message you might see when trying to change to compatibility 90.

They sometimes say that “seeing is believing.” Well in this case, you may be seeing a compatibility level in the 2014 GUI that just isn’t valid. Keep that in mind when using the GUI or trying to change compatibility modes.

Missing Indexes Script v2

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Index, Index Maintenance, Scripts, TSQL

Comments: 9 Comments

Published on: May 15, 2014

Frequently we hear the analogy that <insert item here> is like opinions, everybody has one and not all of them are good (some may stink).

Well, this may just be another one of those <items>. Whether it stinks or not may depend on your mileage.

I had shared a similar script back in January 2012 and wanted to share something a little more current. As is the case for many DB professionals, I am always tweaking (not twerking) and refining the script to try and make it more robust and a little more accurate.

This version does a couple of things differently than the previous version. For one, this is a single database at a time (the prior version looped through all of the databases with a less refined query). Another significant difference is that this query is designed to try and pull information from multiple places about the missing indexes and execution statistics. I felt this could prove more advantageous and useful than to just pull the information from one place.

Here is the current working script.

The following script gets altered on display. n.VALUE is displayed but in the code it is actually n.value. The code display is wrong but it is correct in the code as presented in the editor. If copying from this page, please change the U-cased “VALUE” in the XML segment to “value” so it will work. A download of the script has been added at the end.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb..#MissingIndexInfo', 'U') IS NOT NULL
    DROP TABLE #MissingIndexInfo;
IF OBJECT_ID('tempdb..#MissingIdxSuperInfo', 'U') IS NOT NULL
    DROP TABLE #MissingIdxSuperInfo;
IF OBJECT_ID('tempdb..#top20', 'U') IS NOT NULL
    DROP TABLE #top20;

SET NOCOUNT ON;

WITH XMLNAMESPACES  
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 

SELECT query_plan, plan_handle,sql_handle,execution_count,
       n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text, 
       --n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact, 
       DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id, 
       OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID, 
       n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')  
       AS STATEMENT
INTO #MissingIndexInfo 
FROM  
( 
   SELECT query_plan,plan_handle,sql_handle,execution_count
   FROM (    
           SELECT DISTINCT plan_handle,sql_handle,execution_count
           FROM sys.dm_exec_query_stats
         ) AS qs 
       OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp     
   WHERE tp.query_plan.exist('//MissingIndex')=1 
) AS tab (query_plan,plan_handle,sql_handle,execution_count) 
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n) 
WHERE n.exist('QueryPlan/MissingIndexes') = 1 
	AND DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) = DB_ID()

CREATE CLUSTERED INDEX ci_sqlhandle ON #MissingIndexInfo(sql_handle)

SELECT  MII.database_id ,
        MII.OBJECT_ID ,
        MII.plan_handle ,
        MII.sql_handle ,
        MII.execution_count ,
        CA.equality_columns ,
        CA.inequality_columns ,
        CA.included_columns ,
        CA.Impact ,
        CA.unique_compiles ,
        CA.user_seeks ,
        CA.avg_total_user_cost ,
        CA.avg_user_impact ,
        CA.last_user_seek
INTO    #MissingIdxSuperInfo
FROM    #MissingIndexInfo MII
        CROSS APPLY ( SELECT    mid.database_id ,
                                mid.object_id ,
                                mid.equality_columns ,
                                mid.inequality_columns ,
                                mid.included_columns ,
                                migs.unique_compiles ,
                                migs.user_seeks ,
                                migs.avg_total_user_cost ,
                                migs.avg_user_impact ,
                                migs.last_user_seek ,
                                ( avg_total_user_cost * avg_user_impact )
                                * ( user_seeks + user_scans ) AS Impact
                      FROM      sys.dm_db_missing_index_group_stats AS migs
                                INNER JOIN sys.dm_db_missing_index_groups AS mig 
									ON migs.group_handle = mig.index_group_handle
                                INNER JOIN sys.dm_db_missing_index_details AS mid 
									ON mig.index_handle = mid.index_handle
                                    AND mid.database_id = DB_ID()
                    ) CA
WHERE   1 = 1
	AND CA.database_id = MII.database_id
	AND CA.object_id = MII.OBJECT_ID;

SELECT DISTINCT TOP 20
        plan_handle ,
        MAX(Impact) AS Impact
INTO    #top20
FROM    #MissingIdxSuperInfo
GROUP BY plan_handle
ORDER BY Impact DESC;

WITH    finalsel
          AS ( SELECT   SI.* ,
                        ROW_NUMBER() OVER ( PARTITION BY SI.equality_columns,
                                            SI.inequality_columns,
                                            SI.execution_count ORDER BY SI.Impact DESC ) AS RowNum
               FROM     #MissingIdxSuperInfo SI
                        INNER JOIN #top20 t 
							ON t.plan_handle = SI.plan_handle
             )
    SELECT  fs.* ,
            MII.query_plan ,
            MII.sql_text AS sql_text_inExecplan ,
            MII.STATEMENT AS DB_Schema_Obj ,
            sub.Name ,
            ROW_NUMBER() OVER ( PARTITION BY fs.plan_handle, sub.Name ORDER BY sub.Name ) AS InnerRowNum ,
            ( SELECT    COUNT(*)
              FROM      sys.dm_exec_query_stats s
              WHERE     s.query_hash = sub.query_hash
            ) AS SimilarQueries ,
            ( SELECT    COUNT(*)
              FROM      sys.dm_exec_query_stats s
              WHERE     s.query_plan_hash = sub.query_plan_hash
            ) AS SimilarQueryPlans ,
            ( SELECT    COUNT(qs.query_hash)
              FROM      sys.dm_exec_query_stats qs
              WHERE     qs.sql_handle = MII.sql_handle
              GROUP BY  qs.sql_handle
            ) AS QueriesRelatedtoPlan ,
            ( SELECT    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT
					(NVARCHAR(MAX), N'--' + NCHAR(13) + NCHAR(10) + ist.text
                                                              + NCHAR(13)
                                                              + NCHAR(10)
                                                              + N'--' COLLATE Latin1_General_Bin2),
                                                              NCHAR(31), N'?'),
                                                              NCHAR(30), N'?'),
                                                              NCHAR(29), N'?'),
                                                              NCHAR(28), N'?'),
                                                              NCHAR(27), N'?'),
                                                              NCHAR(26), N'?'),
                                                              NCHAR(25), N'?'),
                                                              NCHAR(24), N'?'),
                                                              NCHAR(23), N'?'),
                                                              NCHAR(22), N'?'),
                                                              NCHAR(21), N'?'),
                                                              NCHAR(20), N'?'),
                                                              NCHAR(19), N'?'),
                                                              NCHAR(18), N'?'),
                                                              NCHAR(17), N'?'),
                                                              NCHAR(16), N'?'),
                                                              NCHAR(15), N'?'),
                                                              NCHAR(14), N'?'),
                                                              NCHAR(12), N'?'),
                                                              NCHAR(11), N'?'),
                                                              NCHAR(8), N'?'),
                                                              NCHAR(7), N'?'),
                                                              NCHAR(6), N'?'),
                                                              NCHAR(5), N'?'),
                                                              NCHAR(4), N'?'),
                                                        NCHAR(3), N'?'),
                                                NCHAR(2), N'?'), NCHAR(1),
                                        N'?'), NCHAR(0), N'') AS [processing-instruction(query)]
              FROM      sys.dm_exec_sql_text(fs.sql_handle) AS ist
            FOR
              XML PATH('') ,
                  TYPE
            ) AS QueryDef ,
            cp.objtype
    INTO    #finalsel
    FROM    finalsel fs
            INNER JOIN #MissingIndexInfo MII 
				ON fs.database_id = MII.database_id
                AND fs.OBJECT_ID = MII.OBJECT_ID
                AND fs.plan_handle = MII.plan_handle
            INNER JOIN sys.dm_exec_cached_plans cp 
				ON fs.plan_handle = cp.plan_handle
            CROSS APPLY ( SELECT TOP 1
                                    ISNULL(p.name, 'ADHOC') AS Name ,
                                    query_hash ,
                                    query_plan_hash
                          FROM      sys.dm_exec_query_stats qs
                                    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
                                    AS qt
                                    LEFT OUTER JOIN sys.procedures p 
										ON qt.objectid = p.object_id
                          WHERE     qs.plan_handle = fs.plan_handle
                        ) sub
    WHERE   RowNum = 1;

SELECT  fls.database_id,fls.OBJECT_ID,fls.DB_Schema_Obj,fls.Name,fls.objtype,fls.execution_count
		,fls.equality_columns,fls.inequality_columns,fls.included_columns,fls.Impact,fls.unique_compiles
		,fls.user_seeks,fls.avg_total_user_cost,fls.avg_user_impact,fls.last_user_seek
		,fls.query_plan,fls.sql_text_inExecplan,fls.QueryDef
		,fls.SimilarQueries,fls.SimilarQueryPlans,fls.QueriesRelatedtoPlan
	FROM    #finalsel AS fls
	WHERE   InnerRowNum = 1
	ORDER BY Impact DESC;

DROP TABLE #MissingIndexInfo;
DROP TABLE #MissingIdxSuperInfo;
DROP TABLE #top20;
DROP TABLE #finalsel;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF OBJECT_ID('tempdb..#MissingIndexInfo', 'U') IS NOT NULL

DROP TABLE #MissingIndexInfo;

IF OBJECT_ID('tempdb..#MissingIdxSuperInfo', 'U') IS NOT NULL

DROP TABLE #MissingIdxSuperInfo;

IF OBJECT_ID('tempdb..#top20', 'U') IS NOT NULL

DROP TABLE #top20;

SET NOCOUNT ON;

WITH XMLNAMESPACES

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT query_plan, plan_handle,sql_handle,execution_count,

n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,

--n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,

DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,

OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +

n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +

n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,

n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +

n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +

n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')

AS STATEMENT

INTO #MissingIndexInfo

FROM

(

SELECT query_plan,plan_handle,sql_handle,execution_count

FROM (

SELECT DISTINCT plan_handle,sql_handle,execution_count

FROM sys.dm_exec_query_stats

) AS qs

OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp

WHERE tp.query_plan.exist('//MissingIndex')=1

) AS tab (query_plan,plan_handle,sql_handle,execution_count)

CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)

WHERE n.exist('QueryPlan/MissingIndexes') = 1

AND DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) = DB_ID()

CREATE CLUSTERED INDEX ci_sqlhandle ON #MissingIndexInfo(sql_handle)

SELECT MII.database_id ,

MII.OBJECT_ID ,

MII.plan_handle ,

MII.sql_handle ,

MII.execution_count ,

CA.equality_columns ,

CA.inequality_columns ,

CA.included_columns ,

CA.Impact ,

CA.unique_compiles ,

CA.user_seeks ,

CA.avg_total_user_cost ,

CA.avg_user_impact ,

CA.last_user_seek

INTO #MissingIdxSuperInfo

FROM #MissingIndexInfo MII

CROSS APPLY ( SELECT mid.database_id ,

mid.object_id ,

mid.equality_columns ,

mid.inequality_columns ,

mid.included_columns ,

migs.unique_compiles ,

migs.user_seeks ,

migs.avg_total_user_cost ,

migs.avg_user_impact ,

migs.last_user_seek ,

( avg_total_user_cost * avg_user_impact )

* ( user_seeks + user_scans ) AS Impact

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid

ON mig.index_handle = mid.index_handle

AND mid.database_id = DB_ID()

) CA

WHERE 1 = 1

AND CA.database_id = MII.database_id

AND CA.object_id = MII.OBJECT_ID;

SELECT DISTINCT TOP 20

plan_handle ,

MAX(Impact) AS Impact

INTO #top20

FROM #MissingIdxSuperInfo

GROUP BY plan_handle

ORDER BY Impact DESC;

WITH finalsel

AS ( SELECT SI.* ,

ROW_NUMBER() OVER ( PARTITION BY SI.equality_columns,

SI.inequality_columns,

SI.execution_count ORDER BY SI.Impact DESC ) AS RowNum

FROM #MissingIdxSuperInfo SI

INNER JOIN #top20 t

ON t.plan_handle = SI.plan_handle

)

SELECT fs.* ,

MII.query_plan ,

MII.sql_text AS sql_text_inExecplan ,

MII.STATEMENT AS DB_Schema_Obj ,

sub.Name ,

ROW_NUMBER() OVER ( PARTITION BY fs.plan_handle, sub.Name ORDER BY sub.Name ) AS InnerRowNum ,

( SELECT COUNT(*)

FROM sys.dm_exec_query_stats s

WHERE s.query_hash = sub.query_hash

) AS SimilarQueries ,

( SELECT COUNT(*)

FROM sys.dm_exec_query_stats s

WHERE s.query_plan_hash = sub.query_plan_hash

) AS SimilarQueryPlans ,

( SELECT COUNT(qs.query_hash)

FROM sys.dm_exec_query_stats qs

WHERE qs.sql_handle = MII.sql_handle

GROUP BY qs.sql_handle

) AS QueriesRelatedtoPlan ,

( SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT

(NVARCHAR(MAX), N'--' + NCHAR(13) + NCHAR(10) + ist.text

+ NCHAR(13)

+ NCHAR(10)

+ N'--' COLLATE Latin1_General_Bin2),

NCHAR(31), N'?'),

NCHAR(30), N'?'),

NCHAR(29), N'?'),

NCHAR(28), N'?'),

NCHAR(27), N'?'),

NCHAR(26), N'?'),

NCHAR(25), N'?'),

NCHAR(24), N'?'),

NCHAR(23), N'?'),

NCHAR(22), N'?'),

NCHAR(21), N'?'),

NCHAR(20), N'?'),

NCHAR(19), N'?'),

NCHAR(18), N'?'),

NCHAR(17), N'?'),

NCHAR(16), N'?'),

NCHAR(15), N'?'),

NCHAR(14), N'?'),

NCHAR(12), N'?'),

NCHAR(11), N'?'),

NCHAR(8), N'?'),

NCHAR(7), N'?'),

NCHAR(6), N'?'),

NCHAR(5), N'?'),

NCHAR(4), N'?'),

NCHAR(3), N'?'),

NCHAR(2), N'?'), NCHAR(1),

N'?'), NCHAR(0), N'') AS [processing-instruction(query)]

FROM sys.dm_exec_sql_text(fs.sql_handle) AS ist

FOR

XML PATH('') ,

TYPE

) AS QueryDef ,

cp.objtype

INTO #finalsel

FROM finalsel fs

INNER JOIN #MissingIndexInfo MII

ON fs.database_id = MII.database_id

AND fs.OBJECT_ID = MII.OBJECT_ID

AND fs.plan_handle = MII.plan_handle

INNER JOIN sys.dm_exec_cached_plans cp

ON fs.plan_handle = cp.plan_handle

CROSS APPLY ( SELECT TOP 1

ISNULL(p.name, 'ADHOC') AS Name ,

query_hash ,

query_plan_hash

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

AS qt

LEFT OUTER JOIN sys.procedures p

ON qt.objectid = p.object_id

WHERE qs.plan_handle = fs.plan_handle

) sub

WHERE RowNum = 1;

SELECT fls.database_id,fls.OBJECT_ID,fls.DB_Schema_Obj,fls.Name,fls.objtype,fls.execution_count

,fls.equality_columns,fls.inequality_columns,fls.included_columns,fls.Impact,fls.unique_compiles

,fls.user_seeks,fls.avg_total_user_cost,fls.avg_user_impact,fls.last_user_seek

,fls.query_plan,fls.sql_text_inExecplan,fls.QueryDef

,fls.SimilarQueries,fls.SimilarQueryPlans,fls.QueriesRelatedtoPlan

FROM #finalsel AS fls

WHERE InnerRowNum = 1

ORDER BY Impact DESC;

DROP TABLE #MissingIndexInfo;

DROP TABLE #MissingIdxSuperInfo;

DROP TABLE #top20;

DROP TABLE #finalsel;

As has been the case in the past, this is not the end query. I have a backlog of updates I want to make to the query and try to create something even more useful from it.

As a bit of fair caution, just because an index shows up as a recommended implementation from this script, it does not mean you should blanket implement it. Also, like the previous script, the higher the impact value, the more probable the index will have a positive impact on your system.

In addition to the impact, I also look at the “Similar Queries” and “Similar Plans” numbers from the result set. These values can sometimes lead to an understanding that their are other queries or other plans that could also benefit from the creation of the index. Your mileage may vary.

Download script Missing_Index.

T-SQL Tuesday #54 – Interviews and Hiring

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC, TSQL Tuesday

Tags: career, Community, TSQL Tuesday

Comments: 1 Comment

Published on: May 13, 2014

This month’s T-SQL Tuesday is hosted by Boris Hristov (blog|twitter) and his chosen topic is “Interviews and Hiring” – specifically interviewing and hiring of SQL Server Professionals.

This is a pretty interesting topic from a few different angles. Boris proposed a few topics such as the following list.

The story of how did you get hired on your latest position?
The most interesting interview you ever had?
How do you think an interview should be handled? What should it include?
Any “algorithms” of how to find the perfect candidate?
If you are the one that leads the technical interview – what do you focus on?
What are the most important questions to ask for the various SQL Server positions out there?

Any one of these ideas would be good fodder for a blog article. A combination of these topics might prove more interesting. I think I will try something a little different. I want to broach the topic of the use and abuse of interviews.

There are two interviews that come to mind that might be good examples. The first is the infinite interview.

In the infinite interview, the candidate comes in for a full day of interviews (a surprise to the candidate). If you were lucky you might have been informed in advance that the interview would be an all-day ordeal.

You arrive on-site and are shuttled from one interviewer to the next and so on throughout the day. Most of these people will have absolutely nothing to do with your work queue or your job duties. Most won’t be able to spell SQL other than maybe having a book that somebody might have given them.

In one such case, I had the opportunity to be grilled all day long. The peak of the interview(s) occurred when their dev team sat down in an office, gave me chalk and eraser and required me to redesign their database that they took 6+ months to design and were still in the process of fixing bugs. Lots of memorization based questions centered around developer (not database) terminology.

In short this pretty much felt like a free consultation session for them. Once finished, I got to show my own way out the door. Not by choice but by them being too busy for it. And in the end not a word from the company.

The second kind of interview comes in the form of stump the chump.

This is another fun type of interview. It can come in many forms. Sometimes it can be in the form of free consultation. Sometimes, the interviewer just gets his rocks off trying to prove he is smarter or that you are not as qualified as you say you are.

In the type where it comes as free consultation, the interviewer has usually been trying to resolve a production issue for quite some time and just can’t figure it out. They will present a partial scenario to you and see if you can figure it out on limited info. If you can’t, they might come back with “We already tried that” or they may provide more info. Again, this is all in an effort to try and resolve a problem that they couldn’t. Sometimes it is often to try and save face with the boss showing that even an expert couldn’t do it.

The alternate style, the interviewer knows from the start that you may be overqualified but really wants to just prove they are as smart or smarter. Often times it just proves that they have some really erroneous understandings about SQL Server. One such interview, the person seemed to have an explicit Oracle background and wanted to get into the internals of SQL Server. He wanted to get into index trees and tried to go down the path of some io statistics for queries based on a bunch of unknowns.

There is really only one thing to do in these types of interviews. Once you recognize what is going on (be it stump the chump or the never-ending interview), politely excuse yourself and look for a position somewhere else.

Omaha BI/SQL User Group – What?

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Community, PASS, SQLFamily

Comments: No Comments

Published on: May 7, 2014

I recently received an email from this guy named John Morehouse (blog | twitter) about the Omaha SQL/BI User Group Meeting in May.

Imagine my surprise when reading the email and discovering that I was presenting.

Here are the guts of that email.

Can you believe that it’s almost May?! Time sure does fly!

Our next meeting will be on Wednesday May 7th, 2014 @ 6PM. Food will be available approximately 20-30 minutes prior to the meeting. We will be at the Farm Credit Services of America facility and our sponsor for the evening is QCI.

Speaker: Jason Brimhall

Jason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2014. He has experience in performance tuning, high transaction environments, as well as large environments. He is currently a Principal Consultant and MCM. He is he VP of the Las Vegas User Group (SSSOLV).

Topic: SQL 2012 Extended Events

Extended Events were introduced in SQL Server 2008. With SQL 2012, we have seen a significant upgrade to this feature. Join me for a little adventure into what extended events are. We will discuss how to use extended events to aid in performance tuning and in day to day administration. We will also explore some background and the architecture of extended events.

Sponsor: QCI

QCI has a long history of attracting and retaining the best talent. Continuous investment in our consultants results in an immediate, positive impact on your projects. Contact Information: Omaha@qci.com 402-981-4264

For further information and to RSVP please go here: http://omahamtg.com/Events.aspx?ID=242

Please make sure to RSVP so that we have an accurate head count for food plus a chance to win some awesome SWAG!!

Thanks!

John Morehouse

Ok, so it really was not a surprise. John asked and I agreed to present. If you are in the Omaha area – come join the group for a pleasant evening and to learn something (hopefully) about SQL Server.

And yes – the meeting is tonight May 7, 2014.

page 1 of 1

SQL RNNR

Auditing and Event SubClasses

Can you partition a temporary table?

Supported Compatibility Levels in SQL Server

Missing Indexes Script v2

T-SQL Tuesday #54 – Interviews and Hiring

Omaha BI/SQL User Group – What?

Now Reading (2)

In Queue (1)

Recently Read (16)

M	T	W	T	F	S	S
« Apr				Jul »
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31