Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.

The question that I would address today is:

How can one filter trace events based on the Windows Login/User Name?

This is a very important requirement, especially when troubleshooting terminal-server based environments. Normally, in development environments, simply filtering on the host name is sufficient. However, in a terminal server configuration, multiple users may be logged on to the same host, which makes filtering on the windows login/username important.

Preparing the Environment

Before I execute the tests, allow me to prepare my test environment. I will be creating one stored procedure, which will be executed by 3 different windows users.

USE AdventureWorks2012
GO
CREATE PROCEDURE HumanResources.proc_GetEmployes
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT SYSTEM_USER,
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

As seen in the screenshot below, we have 3 Windows Users which have been granted access to SQL Server. (NOTE: In practice, it is not advisable to add users to the sysadmin fixed server role. This has been done here for demonstration purposes only):

1. WIN2K8Administrator
2. WIN2K8SQLTestUser01
3. WIN2K8SQLTestUser02

Executing the Test

To execute the test, I have already prepared the SQL Server Profiler to use a custom template based on the “TSQL_SPs” default template as shown in the tutorial: getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-c. As part of the customization, I have chosen to capture and display the following four columns:

• LoginName
• LoginSid
• NTDomainName
• NTUserName

Now that the SQL Server Profiler is ready, I will execute the stored procedure under the context of the 3 users referenced above. To switch the execution context, I have used the EXECUTE AS LOGIN clause.

USE AdventureWorks2012
GO
--Executing as the first test user - WIN2K8SQLTestUser01
EXECUTE AS LOGIN = 'WIN2K8SQLTestUser01'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 1
GO
--Revert to original logged-in user
REVERT
GO

--Executing as the first test user - WIN2K8SQLTestUser02
EXECUTE AS LOGIN = 'WIN2K8SQLTestUser02'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 2
GO
--Revert to original logged-in user
REVERT
GO

EXECUTE AS LOGIN = 'WIN2K8Administrator'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 3
GO
--Revert to original logged-in user
REVERT
GO

Looking at the Profiler, I can see that I have been able to capture the fact that the statements were executed under 3 different user contexts.

To filter the results, all one has to do is to filter one of the four columns mentioned below (as applicable):

• For both SQL & Windows Logins:
• LoginName
• LoginSid
• For Windows Logins:
• NTDomainName
• NTUserName

Trust that the above exercise has cleared the queries of all my dear readers. If you still have any queries, do send them across – I will be happy to help you out.

References:

• EXECUTE AS: http://msdn.microsoft.com/en-us/library/ms181362.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.

The question that I would address today is:

How can I filter multiple objects of the same type?

For example, add a condition like ObjectName NOT LIKE ‘%SP1%’ AND ObjectName NOT LIKE ‘%SP2%’

The SQL Server Profiler is a very powerful performance analysis tool, and this is a fairly common requirement. Here’s a step-by-step guide to realize this requirement. For this demo, let me create 3 very stored procedures. Before we execute these procedures, we will use the Profiler to filter out two of them from the trace.

USE AdventureWorks2012
GO
CREATE PROCEDURE HumanResources.proc_GetEmployes1
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP1',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes2
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP2',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes3
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP3',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

Now that these procedures are created, let us setup the Profiler to capture the stored procedure execution, filtered such that only the procedure is seen in the trace.

1. Follow the steps provided in the Profiler Tutorial Part #2: http://beyondrelational.com/modules/12/tutorials/631/tutorials/15252/getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-custo.aspx to create a customized template
   • I generally prefer using the “TSQL_SPs” default template as the base and then customize it to suite the requirement
2. When customizing the column filters, add the first line:
3. 
4. Now, hit enter to add a new line
5. 
6. Now, add the second line of filter
7. 
8. Click “OK” to complete configuration of the template

Now, start the Profiler trace and execute the following query:

USE AdventureWorks2012
GO
EXEC HumanResources.proc_GetEmployes1 @businessEntityId = 1
GO
EXEC HumanResources.proc_GetEmployes2 @businessEntityId = 2
GO
EXEC HumanResources.proc_GetEmployes3 @businessEntityId = 3
GO

The Results tab of the SQL Server Management Studio (SSMS) will show the results from the execution of all 3 procedures.

The Profiler would capture the following trace, which only captures the activity from the stored procedure HumanResources.proc_GetEmployees2 and not from the other two procedures. Adding new lines to the Profiler template column filters makes the Profiler to apply an “AND” condition on the filter.

I hope the above simple steps help you to filter your Profiler Results efficiently.

Until we meet next time,

Be courteous. Drive responsibly.

It’s close to a week-end, and hence, I have a very short post for today.

Very often I find teams and SQL Server enthusiasts coming up to me and asking me some of the following questions:

• How many databases can I host on a SQL Server instance?
  
• How large can these databases be?
  
• Is there a limit to the number of records in a table?

I often point them to Books On Line/MSDN where they can find the official specification which would be able to answer all of their questions. Today, I thought of putting an end to this almost endless cycle of same question-answer sessions and provide the direct links to the Books on Line/MSDN maximum capacity specification for SQL Server. So, here goes:

Maximum Capacity Specifications for SQL Server

• SQL 2012: http://msdn.microsoft.com/en-us/library/ms143432.aspx
  
• SQL 2008 R2: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx
  
• SQL 2008: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
  
• SQL 2005: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.90).aspx

How do you calibrate your systems?

Whenever someone asks a minimum/maximum capacity specification related question, I always wonder how they would calibrate their systems and more importantly, certify their product against these numbers. So, here’s what I would like to learn from you, my kind readers:

How do you derive minimum/maximum capacity specifications for your systems?

Ideally, the situation would be that a production system would have the same limitations as the underlying platform. But, that would not happen in practice due to the choice of the architecture and/or technical design decisions.

Do leave a note as you go. It’s a very interesting question, with no fixed answer – every system and every team would have their own methods, which is why I am sure it would be a great discussion.

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is a very simple tip based on a question I received from one of my colleagues. They had dates stored in their database and wanted to extract day of the week and day of the year information from the date column.

Now, this would have been a very easy task for the team had this requirement been related to displaying the day of the week and day of the year information on the UI. But, in this particular case, they had to dump it out to a text file (using SQL Server BCP) for exchanging with a legacy reader-board feeding system (If you are unfamiliar with the term, the display boards on railway stations and airports that inform travellers about the schedule and other stages of the boarding process and the associated data stores are reader-board systems). When they approached me, they told me it was a big problem and were wondering if I could spend a day with them to help them figure things out. When I said it won’t even take more than 10 minutes, they were awestruck!

The DATEPART function

Most of us have worked with T-SQL date manipulation functions in Microsoft SQL Server, with the most common use being extracting parts like date, month and year from the date or a date/time value. However, there are many more options, which are what I shared with the team.

Besides extracting parts of a date, the DATEPART function can also help us determine the following for a given date or date/time value:

• The Day of the Week
• The Day of the Year
• Week number
• ISO Week Number

Here’s the associated query:

USE tempdb
GO
DECLARE @currentDate DATE = GETDATE()

SELECT @currentDate                      AS CurrentDate,
       DATEPART(DAYOFYEAR, @currentDate) AS DayOftheYear,
       DATEPART(WEEKDAY  , @currentDate) AS DayOftheWeek,
       DATEPART(WEEK     , @currentDate) AS WeekNumber,
       DATEPART(ISO_WEEK , @currentDate) AS ISOWeekNumber
GO

/*
DAYOFYEAR --Equivalent interval identifiers: DY
WEEKDAY   --Equivalent interval identifiers: DW
WEEK      --Equivalent interval identifiers: W
ISO_WEEK  --Equivalent interval identifiers: ISOWK, ISOWW
*/

For a test value of ‘2012-08-25’, the results are:

All the team had to do was to incorporate the DATEPART function call in their T-SQL queries. There – did it take more than 10 minutes?

Further Reading

If you would like to explore all the options of the DATEPART function, I would redirect you to the Books-On-Line page: http://msdn.microsoft.com/en-us/library/ms174420.aspx

Also, if you would like to read more about how the team exported bulk data from their SQL Server tables into a text/comma-separated file, you may refer my previous posts:

• BCP & Bulk Inserts – Underappreciated features of Microsoft SQL Server
• SSMS – Query Result configuration – Generate comma separated output without BCP or query changes

Until we meet next time,

Be courteous. Drive responsibly.

One of my favourite interview questions at an intermediate level has always been: “Can I have a primary key on a table that does not contribute to the clustered index?”. Of course, it’s not always the same question – there are multiple variants, such as :

• Can a table have a primary key that does not participate in a clustered index?
• Does designating a column(s) as a primary key automatically create a clustered index on the table based on the key column(s)?
• Can a clustered index be created using a column(s) other than the primary key of the table?

The answer that most candidates come up with is that there is a direct dependency between the primary key and the clustered index – the primary key has to be the set of columns on which one wishes to create a clustered index.

Contrary to popular belief, the correct answer is “Yes, a table may have a clustered index  defined on a column other than the primary key of the table”. It’s just that most developers are so used to the default design and coding mechanisms that generally the thought of going against the flow may not come to the mind.

Please note that this post is not about the recommended practices for creation of clustered index and primary keys, but is intended to achieve an understanding of the fact that having a primary key does not automatically imply a clustered index in all situations.

Demo

As supporting proof, allow me to create a simple scenario. As part of the demonstration, I would be executing the following steps:

1. Create a test table without any keys or indexes defined
2. Define a primary key on the test table, with an explicit NONCLUSTERED keyword added to the definition
3. Define a CLUSTERED index on the table on a column that’s not the primary key
4. Check the details of the indexes created using sys.indexes catalog view

USE tempdb
GO

--Safety Check
IF OBJECT_ID('pkTest') IS NOT NULL
    DROP TABLE pkTest
GO

--Create test table definition
CREATE TABLE pkTest (Id INT NOT NULL IDENTITY(1,1),
                     DummyId INT NOT NULL,
                     KeyName VARCHAR(20)
                    )
GO

--Notice the use of NONCLUSTERED 
ALTER TABLE pkTest
    ADD CONSTRAINT pk_pkTest_pkTestId PRIMARY KEY NONCLUSTERED (Id)
GO

--Now create a clustered index on a column other than the Primary Key
CREATE CLUSTERED INDEX idx_pkTest_DummyId ON pkTest (DummyId)
GO

As you can see, we created the Primary Key with a keyword – NONCLUSTERED. This should create a non-clustered index for the primary key and a clustered index for the DummyId column. Let’s run the following query to check:

--Check the types of Indexes that have been created
SELECT si.object_id,
       OBJECT_NAME(si.object_id) AS ObjectName,
       si.name,
       si.index_id,
       si.type,
       si.type_desc,
       si.is_unique,
       si.is_unique_constraint,
       si.is_primary_key
FROM sys.indexes AS si
WHERE si.object_id = OBJECT_ID('pkTest')
GO

Query execution yields the following results:

Conclusion

A clustered index can be defined on any column of a table as long as the column satisfies the criteria for a clustered index. Having a primary key does not imply that clustered index will exist on the primary key under all situations.

Reference

• Clustered Index Design Guidelines (BOL/MSDN): http://msdn.microsoft.com/en-us/library/ms190639(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.