May 2011 - Posts

PAL for a DBA
14 May 11 10:05 PM | MartinBell | with no comments

There are many articles about what performance counters you should collect when analysing the performance of SQL Server. Even if you decide what counters to collect, then analysing them to find issues can be also be difficult and time consuming. This is where a tool such as PAL (Performance Analysis Tool) proves itself to be invaluable. PAL is available on Codeplex and as a default comes with a template for analysing SQL Server 2005/2008. To make sure you collect the performance counters analysed by PAL you can create a logman input file by using the export function

image

image

The export a list of counters that you can use with the logman utility to create a counter log which can then be used with logman or Performance/System monitor. Alternatively your can use SQLDiag to collect your performance monitor counters and analyse them with PAL.

Filed under:
SQL Profiler and SQL Trace
04 May 11 09:57 PM | MartinBell | with no comments

The death knell may have been sounded for SQL Profiler and SQL Trace, but it still may be a while before we completely stop using them. I was looking at the functionality provided with the for Server side traces and concluded that the feature was never really completed! For instance comparing SQL trace to Perfmon/System Monitor here are a few things that SQL Trace doesn’t do.

With Perfmon you can create counter collections and then start/stop those collections manually or through a schedule. With SQL Profiler/SQL Trace this functionality has never been included. The usual way to schedule a trace is to write/script the trace definition and then include that in a SQL Agent job step. Unfortunately many SQL Trace definitions will be too large to be included in a job step so you may end up creating a stored procedure that will create the trace and call that.

SQL Server trace definitions don’t survive a restart of SQL Server unless you write your own method to re-create them.

If you stop and re-start a Perfmon counter collection, it will automatically append a number to the file name, if you stop a SQL Server Trace and the re-start, then the same filename is used. This forces you to drop the trace and re-create it each time you wish to create a new file.

It’s a wonder how such a pivotal tool such as SQL Trace/Profiler has ever been left in such a state. It hasn’t really been significantly enhanced since the original release (The SQL 2005 interface rejig and perfmon file imports excepted!). If Profiler was released today I suspect it would be on Codeplex and not part of the product.

With Extended Events slated to replace SQL Trace it was a bit worrying that the initial release with SQL 2008 the only way to manage them was with T-SQL, later a SSMS add in was released on Codeplex, and the beginnings of a management feature is present in Denali CTP1, but nothing like  the functionality of SQL Profiler. This is a bit disappointing as the potential for extended events could be so much greater than SQL Trace/SQL Profiler.

TRIGGERS, IDENTITY, INSERTED and OUTPUT
03 May 11 08:55 PM | MartinBell | 1 comment(s)

Last week I saw yet another post proposing that you always use SCOPE_IDENTITY to return the value of the last identity column inserted into table. Unfortunately what these posts often ignore is that as well as AFTER triggers, there are also INSTEAD of triggers; and in this situation the advice given may not be correct. If you have never used INSTEAD of triggers I will re-phrase books online: “INSTEAD OF triggers can be defined on a view or table to replace the standard action of the triggering statement.”

Most articles will show a situation when @@IDENTITY does not return the correct value for the identity value inserted into the table. In this example I’ve also included what can be returned using OUTPUT parameters which is often ignored by articles that talk about methods of returning identity values:      

USE
tempdb
GO

SET
NOCOUNT ON
;
GO

CREATE
TABLE TriggerTable
(
  ID INT IDENTITY(100,1) NOT
NULL,
  Description  NVARCHAR(100
));
GO

CREATE
TABLE AlternateTriggerTable
(
  ID INT IDENTITY(100,1) NOT
NULL,
  [Description] NVARCHAR(100
));
GO

DECLARE
@ID TABLE ( id int )
;
INSERT INTO TriggerTable  ( [Description] 
)
OUTPUT INSERTED.id INTO @id
VALUES ( 'Expected identity' )
;
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY], IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT], ID
FROM @id
;
GO
/*
@@IDENTITY SCOPE_IDENTITY IDENT_CURRENT ID
---------- -------------- ------------- -----------
100        100            100           100
*/

SELECT *
FROM TriggerTable
;
GO
/*
ID   Description
---- ------------------
100  Expected identity
*/

CREATE TRIGGER InsTrigger ON TriggerTable
AFTER INSERT
AS
INSERT
INTO TriggerTable VALUES ('Additional Data');
GO

DECLARE
@ID TABLE ( id int )
;
INSERT INTO TriggerTable  ( [Description] 
)
OUTPUT INSERTED.id INTO @id
VALUES ( 'Unexpected Values' )
;
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY], IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT], ID
FROM @id
;
GO
/*
@@IDENTITY  SCOPE_IDENTITY  IDENT_CURRENT  ID
----------- --------------- -------------- ----
102         101             102            101
*/

SELECT *
FROM TriggerTable
;
GO
/*
ID   Description
---- ------------------
100  Expected identity
101  Unexpected Values
102  Additional Data
*/

DROP TRIGGER insTrigger
;
GO

CREATE
TRIGGER InsTrigger ON TriggerTable
AFTER INSERT
AS
INSERT
INTO AlternateTriggerTable VALUES ('Alternate Trigger Data');
GO

DECLARE
@ID TABLE ( id int )
;
INSERT INTO TriggerTable  ( [Description] 
)
OUTPUT INSERTED.id INTO @id
VALUES ( 'Expected @@Identity' )
;
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY], IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT], ID
FROM @id
;
GO
/*
@@IDENTITY  SCOPE_IDENTITY  IDENT_CURRENT  ID
----------- --------------- -------------- ----
100         103             103            103
*/

SELECT *
FROM TriggerTable
;
GO
/*
ID   Description
---- --------------------
100  Expected identity
101  Unexpected Values
102  Additional Data
103  Expected @@Identity
*/

SELECT *
FROM AlternateTriggerTable
;
GO
/*
ID   Description
---- -----------------------
100  Alternate Trigger Data
*/

DROP TRIGGER insTrigger
;
GO

In the following examples with an AFTER trigger @@IDENTITY will return the expected value and SCOPE_IDENTITY returns NULL instead.

/* Effect of INSTEAD of triggers */
CREATE
TRIGGER insTrigger ON  TriggerTable
INSTEAD OF INSERT
AS
BEGIN
       SELECT * FROM inserted
;
       INSERT INTO TriggerTable ( [Description]
)
       SELECT [Description] + ' - Adjusted' FROM INSERTED
;
END
GO

DECLARE
@ID TABLE ( id int )
;
INSERT INTO TriggerTable  ( [Description] 
)
OUTPUT INSERTED.id INTO @id
VALUES ( 'Unexpected Scope_Identity' )
;
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY], IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT], ID
FROM @id
;
GO

/*
ID  Description
--- --------------------------
0   Unexpected Scope_Identity

@@IDENTITY SCOPE_IDENTITY  IDENT_CURRENT  ID
---------- --------------- -------------- ----
104        NULL            104            0

*/

SELECT *
FROM TriggerTable
;
GO

/*
ID   Description
---- -------------------------------------
100  Expected identity
101  Unexpected Values
102  Additional Data
103  Expected @@Identity
104  Unexpected Scope_Identity - Adjusted
*/

DROP TRIGGER insTrigger
;
GO

So if you want a more robust code pattern to get the last identity value you may want a construct something similar to:

SET @NewIdentity = ISNULL(SCOPE_IDENTITY(),@@IDENTITY) ;

This would cope with the scenario where the value inserted into the identity column is overridden.


CREATE TRIGGER insTrigger ON  TriggerTable
INSTEAD OF INSERT
AS
BEGIN
       SET IDENTITY_INSERT TriggerTable ON
;
       SELECT * FROM inserted
;
       INSERT INTO TriggerTable ( ID, [Description]
)
       SELECT -1, [Description] FROM INSERTED
;
       SET IDENTITY_INSERT TriggerTable OFF
;
END
GO

DECLARE
@ID TABLE ( id int )
;
INSERT INTO TriggerTable  ( [Description]
  )
OUTPUT INSERTED.id INTO @id
VALUES ( 'Unexpected IDENT_CURRENT' )
;
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY], IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT], ID
FROM @id
;
GO
/*
ID  Description
--- -------------------------
0   Unexpected IDENT_CURRENT

@@IDENTITY SCOPE_IDENTITY  IDENT_CURRENT   ID
---------- --------------- --------------- ---
-1         NULL            104             0

*/

SELECT *
FROM TriggerTable
;
GO
/*
ID   Description
---- -------------------------------------
100  Expected identity
101  Unexpected Values
102  Additional Data
103  Expected @@Identity
104  Unexpected Scope_Identity - Adjusted
-1   Unexpected IDENT_CURRENT
*/

DROP TRIGGER insTrigger
;
GO

But the code pattern may be inadequate if the INSTEAD of trigger subsequently inserts data into tables that do also have identity columns e.g.

CREATE TRIGGER insTrigger ON  TriggerTable
INSTEAD OF INSERT
AS
BEGIN
       SELECT * FROM inserted
;
       INSERT INTO AlternateTriggerTable ( [Description]
)
       SELECT [Description] FROM INSERTED
;
END
GO

DECLARE
@ID TABLE ( id int )
;
INSERT INTO TriggerTable  ( [Description] 
)
OUTPUT INSERTED.id INTO @id
VALUES ( 'Unexpected everything!' )
;
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY], IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT], ID
FROM @id
;
GO
/*
ID  Description
--- -----------------------
0   Unexpected everything!

@@IDENTITY SCOPE_IDENTITY  IDENT_CURRENT  ID
---------- --------------- -------------- ---
101        NULL            104            0
*/

SELECT *
FROM TriggerTable
;
GO
/*
ID  Description
--- -------------------------------------
100 Expected identity
101 Unexpected Values
102 Additional Data
103 Expected @@Identity
104 Unexpected Scope_Identity - Adjusted
-1  Unexpected IDENT_CURRENT
*/

SELECT *
FROM AlternateTriggerTable
;
GO
/*
ID   Description
---- -----------------------
100  Alternate Trigger Data
101  Unexpected everything!
*/

DROP TABLE AlternateTriggerTable 
;
DROP TABLE TriggerTable
;

In my previous article I showed how the OUTPUT parameter can be used to get GUID values inserted into a table using a default. From the above code example you can also see that triggers may also cause problems for this method as well. For example:

CREATE TABLE NewTriggerTable (
       id int not null identity,
       guidid uniqueidentifier not null default NEWSEQUENTIALID()
) ;

DECLARE @ID TABLE ( id int, guidid uniqueidentifier ) ;
INSERT INTO NewTriggerTable 
OUTPUT INSERTED.id, INSERTED.guidid INTO @id
DEFAULT VALUES ;

SELECT *
FROM @id ;
GO
/*
id  guidid
--- ------------------------------------
1   FFBA6AB8-BD75-E011-A8A1-001E377AFE56
*/

SELECT * FROM NewTriggerTable ;
GO
/*
id  guidid
--- ------------------------------------
1   FFBA6AB8-BD75-E011-A8A1-001E377AFE56
*/

CREATE TRIGGER InsTrigger ON NewTriggerTable
AFTER INSERT AS
SELECT
* FROM inserted ;
UPDATE A
SET guidid = '00000000-0000-0000-0000-000000000000'
FROM NewTriggerTable A
JOIN inserted I ON a.id = i.id ;
GO

DECLARE
@ID TABLE ( id int, guidid uniqueidentifier ) ;
INSERT INTO NewTriggerTable 
OUTPUT INSERTED.id, INSERTED.guidid INTO @id
DEFAULT VALUES ;

SELECT *
FROM @id ;
GO
/*
id  guidid
--- ------------------------------------
2   FB3039D1-BD75-E011-A8A1-001E377AFE56

id  guidid
--- ------------------------------------
2   FB3039D1-BD75-E011-A8A1-001E377AFE56
*/

SELECT * FROM NewTriggerTable ;
GO
/*
id  guidid
--- ------------------------------------
1   FFBA6AB8-BD75-E011-A8A1-001E377AFE56
2   0000000-0000-0000-0000-000000000000
*/

DROP TRIGGER InsTrigger ;
GO

CREATE
TRIGGER InsTrigger ON NewTriggerTable
INSTEAD OF INSERT AS
SELECT
* FROM inserted ;
INSERT INTO NewTriggerTable ( guidid ) VALUES ('00000000-0000-0000-0000-000000000000');
GO

DECLARE
@ID TABLE ( id int, guidid uniqueidentifier ) ;
INSERT INTO NewTriggerTable 
OUTPUT INSERTED.id, INSERTED.guidid INTO @id
DEFAULT VALUES ;

SELECT *
FROM @id ;
GO
/*
id  guidid
--- ------------------------------------
0   D8CBF71C-BE75-E011-A8A1-001E377AFE56

id  guidid
--- ------------------------------------
0   D8CBF71C-BE75-E011-A8A1-001E377AFE56
*/

SELECT * FROM NewTriggerTable ;
GO
/*
id          guidid
----------- ------------------------------------
1           FFBA6AB8-BD75-E011-A8A1-001E377AFE56
2           00000000-0000-0000-0000-000000000000
3           00000000-0000-0000-0000-000000000000
*/

DROP TABLE NewTriggerTable ;

Triggers have traditionally been held up as a means of extending the functionality of a system without changing the system itself. Hopefully this article has shown that this may not be the case, in fact the consequence of adding a trigger without knowing the impact the trigger can have could be quite dangerous.

This Blog

SQL Blogs

Syndication