My SQLPass Schedule [Training]

This is my SQL Pass Summit schedule. I am double booked in some cases as I have not decided which one should I attend yet. Other than that, I just need to figure out to which parties should I make it 🙂

Schedule for Oscar Zamora

Sunday, November 7

6:00 PM

PASS Booth Open

Registration

Monday, November 8

7:15 AM

Internet Pavilion

Registration

7:30 AM

Continental Breakfast

8:00 AM

Microsoft Labs

12:00 PM

Pre-Conference Lunch

Tuesday, November 9

6:30 AM

Internet Pavilion

Registration

7:00 AM

Continental Breakfast

7:15 AM

Speaker Ready Room

8:00 AM

Microsoft Labs

8:15 AM

Keynote Speaker Day 1 – Ted Kummert

10:00 AM

SQL Server Clinic

10:15 AM

Consulting – Should I Consider It?

SQLCAT: HA DR Customer Panel — Architectures & Lessons Learned (90 mins)

11:00 AM

Community Learning Center

Exhibit Hall

11:15 AM

SQLCAT: Customer Learning of using FileStream and Remote Blob Store (RBS) for a Large Scale Deployment

11:30 AM

Lunch – Birds of a Feather with MVP’s

12:00 PM

Energizing the Next Generation: Encouraging and Inspiring Young Women to Choose Tech Careers

12:30 PM

PASS Summit Book Signing

1:00 PM

PASS Summit Book Signing

1:30 PM

Advanced SQL Server Deployment Techniques for Virtualization (DBA-313)

3:00 PM

Demystifying MDX in Reporting Services (BID-218)

Zero to Cube: Fast Track to Analysis Services Development (BIA-202)

4:30 PM

Inside DAX

6:15 PM

Introduction to the Entity Framework

7:00 PM

Speaking at PASS – How to Write An Abstract

Wednesday, November 10

6:45 AM

Sponsor-Hosted Breakfast (Microsoft)

7:00 AM

Continental Breakfast

Sponsor-Hosted Breakfast (VMware)

7:15 AM

Internet Pavilion

Registration

Speaker Ready Room

8:00 AM

Microsoft Labs

8:15 AM

Keynote Speaker Day 2 – Quentin Clark

10:00 AM

SQL Server Clinic

10:15 AM

Business Intelligence and the Cloud (90 mins) (90R1)

Don’t Wait Consolidate (DBA-245)

11:00 AM

Community Learning Center

Exhibit Hall

11:30 AM

Lunch

Women in Technology Luncheon (Sponsored by GoDaddy.com)

1:30 PM

50 surprising features of SQL Server Business Intelligence

Building a Comprehensive Professional Development Plan (PD-468)

3:00 PM

Building NextGen Scalable & Highly Available Architectures with Microsoft SQL Server 2008 R2 (DBA-461)

Cooking with SSRS: Advanced Report Design Recipes (BID-234)

4:30 PM

Managing system performance with the Data Collector and Management Data Warehouse with Reporting Services

Master Data Services: Fixing Data before ETL (BIA-204)

Thursday, November 11

6:45 AM

Registration

Sponsor-Hosted Breakfast (Quest Software)

7:00 AM

Continental Breakfast

7:15 AM

Internet Pavilion

Speaker Ready Room

8:00 AM

Microsoft Labs

8:15 AM

Keynote Speaker Day 3 – David Dewitt

10:00 AM

SQL Server Clinic

10:15 AM

Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance (DBA-242)

SQL Azure Data Sync – Integrating On-Premises Data with the Cloud

11:30 AM

Boxed Lunch

12:00 PM

Chapters Lunch

PASS Summit Book Signing

12:30 PM

PASS Summit Book Signing

1:00 PM

Real World Analysis Services Stored Procedures (BIA-206)

2:30 PM

Automating SQL Buildouts with Hyper-V and SQL Server 2008 R2 (DBA-249)

Monitor Your Business with PerformancePoint Services Monitoring and Analytics (BID-217)

4:00 PM

Optimizing Large-Scale OLTP Workloads

Friday, November 12

7:00 AM

Internet Pavilion

7:15 AM

Registration

7:30 AM

Continental Breakfast

8:30 AM

SharePoint for the DBA

12:00 PM

Post-Conference Lunch

Incorrect Bios Settings Can Reduce Database Performance [SQL Server]

Sam Saffron commented on my blog post asking me if I could share my thoughts on an issue he was experiencing. This was on a newly migrated SQL Server 2008 R2 database solution running on Nehalem Processor based CPUs. I went ahead and shared what I experienced in my environment.

It turned out that the issue was not Hyper Threading related, but a Bios misconfiguration instead. This is an excerpt of his blog post:

In the process of asking the question and researching it ourselves, we discovered the answer. These Dell servers were inexplicably shipped with BIOS settings that …

  • did not allow the host operating system to control the CPU power settings
  • did not set the machine to high performance mode
  • did not scale CPU speed under load properly

… kind of the worst of all worlds. But Kyle quickly flipped a few BIOS settings so that the machine was set to “hyperspeed mode”, and performance suddenly got a lot better. How much better?

A conclusion of this experience, check for Bios Configurations. Dell needs to tell their customers which settings can be beneficial for certain applications, like SQL Server.

Summarizing Performance issues and workarounds after migrating from 2005 to 2008 R2 [SQL Server]

Based on our experience, having Hyper Threading (HT) enabled on one particular node let to I/O operations take a very long time. This was on a Windows 2008 R2 Cluster running SQL Server 2008 R2. An interesting fact was that it was neither reflected in the wait stats nor in the pssdiag we ran for Microsoft support.

The way we noticed low I/O was just by watching the OS counters for physical disk. I wrote about it here and here.

After fixing the issue by disabling HT, we started experiencing a very high CPU utilization due to a excessive amount of logical reads (20 million per query). This was due to a really bad plan. Our processes were performing anti-semi joins with tables that were partitioned and  the code that was performing extremely bad in 2008 R2 while doing just fine in 2005. I wrote about it here.

We pinpointed it out by running Adam Machanic’s sp_whoisactive while under high load  (which can be downloaded from here).

We also ran server side traces to find out the most expensive operations by sorting the highest I/O and CPU utilization metrics.

With the steps above we were able to tune the offending processes and go from 85% sustained CPU utilization to almost nil.

High CPU Utilization

Kerberos Double Hop and Delegation between different Active Directory Accounts [SQL Server]

In the past, when I had bare knowledge of Active Directory, I dealt with double hop queries using SQL Server Authentication.

What is the problem with SQL Authentication? Maintenance. The DBA team can literally spend hours maintaining usernames/passwords, linked servers, and of course giving appropriate grants/roles to a new user added to several database instances. There are also security concerns which is outside the scope of this post.

So what is a double hop query? It is a query ran from Machine A that requests data from machine B that at the same time requests data from Machine C.

For example:

If I execute a query on my local machine (A) connecting to Server1 (B) that requests data from Server2 (C):

1
2
3
4
5
-- From my desktop:
SELECT a.*
FROM OPENROWSET('SQLNCLI',
'Server=server2.domain.com\instance;Trusted_Connection=yes;',
'SELECT * from master.sys.objects') AS a;

The solution: Active Directory. A user can be added to the DBA Domain Group, and that group can be granted the sysadmin role on every production database server for example. Once that is defined, we can add/remove members of the group without even touching the database instances.

Now, there is a consensus of having each database instance use its own Active Directory Account as SQL Server Service user. Some shops have 1 account, others a few. It depends on the policy adopted.

Our challenge: Allow double hop queries between 2 database instances running different Active Directory Accounts.

The solution was similar to what we have done with 1 single account.

This is how me made it work for 1 account:

Prerequisites:

  1. You need domain admin or access to Active Directory under the domain managed
  2. The person in charge of making the change has access to the domain controller or has Remote Server Administration Tools installed locally.

Steps:

  • Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.
1
setspn -a MSSQLSvc/server1.domain.com:1433 DOMAIN\srvc_sqlaccount
  • For clustered solutions, run it with and without port number. It does not matter if it is a named instance.
1
2
setspn -a MSSQLSvc/serverclust1.domain.com DOMAIN\srvc_sqlaccount
setspn -a MSSQLSvc/serverclust1.domain.com:1433 DOMAIN\srvc_sqlaccount
  • This needs to be repeated for all the SQL Server Instances that you need to trust delegation
  • Open Active Directory Users and Computers, search for the domain account just used (DOMAIN\srvc_sqlaccount) in the example, double click and open the properties window.
  • Enable “Trust this user for delegation to specified services only”, “Use Kerberos Only”, Add, User and Computers, type in the account used (e.g. srvc_sqlaccount), select the machine(s) you want to trust delegation, OK, OK.

This is how we made it work with 2 accounts:

You need to follow the same exact process, but when enabling Kerberos delegation, you need to open the properties of the first domain account and add Kerberos Delegation to the second domain account.

Assuming non-clustered instances:

Steps:

  • For the first account and the first instance. Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.
1
setspn -a MSSQLSvc/server1.domain.com:1433 DOMAIN\srvc_sqlaccount_1
  • For the second account and the second instance. Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.
1
setspn -a MSSQLSvc/server2.domain.com:1433 DOMAIN\srvc_sqlaccount_2
  • Open Active Directory Users and Computers, search for the first domain account (DOMAIN\srvc_sqlaccount_1) in the example, double click and open the properties window.
  • Enable “Trust this user for delegation to specified services only”, “Use Kerberos Only”, Add, User and Computers, type in the first account used (e.g. srvc_sqlaccount_1)
  • Select the machine you want to trust delegation (server1.domain.com in the example), OK, OK.
  • Click Add again, User and Computers, type in the second account used (e.g. srvc_sqlaccount_2)
  • Select the machine you want to trust delegation (server2.domain.com in the example), OK, OK.
  • Wait enough time for the Domain Controllers to propagate the changes (15 minutes should be fine)

Verify Logins are Connecting Via Kerberos:

Run the following query, paying special attention to the auth_scheme column. If Kerberos authentication was successful, you should see the auth_scheme reflect Kerberos instead of NTLM:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
s.session_id
, c.connect_time
, s.login_time
, s.login_name
, c.protocol_type
, c.auth_scheme
, s.HOST_NAME
, s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id

Now we can have multiple Domain Accounts for multiple SQL Database Instances, each one delegating for double hop.

Hope this helps.

SQL Server: Truncating Dates

TRUNC is a function I used a lot when I worked with Oracle and helped me slicing my data in several different ways, and keeping the columns in date format.

That function is not available in SQL Server unless you work it around by using CONVERT or DATEPART. But both change the content to VARCHAR.

Well, several years ago I found a way to workaround the issue and keep the data in DATE format (and it took me this long to post it):

1
2
-- Truncate to the current hour
SELECT DATEADD(HH, DATEDIFF(HH,0,GETDATE()), 0)
1
2
-- Truncate to the current day
SELECT DATEADD(DD, DATEDIFF(DD,0,GETDATE()), 0)
1
2
-- Truncate to the current month
SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()), 0)

As per BOL, we can use any of these datepart arguments:

  1. year (or yy, yyyy)
  2. quarter (or qq, q)
  3. month (or mm, m)
  4. dayofyear (or dy, y)
  5. day (or dd, d)
  6. week (or wk, ww)
  7. weekday (or dw, w)
  8. hour (or hh)
  9. minute (or mi, n)
  10. second (or ss, s)
  11. millisecond (or ms)
  12. microsecond (or mcs)
  13. nanosecond (or ns)

You can come up with all kinds of GROUP BYs, and filtering and keep the data in Date format at the same time.

A couple of days ago, while browsing the AskSSC community forum, a question came up: “How to get quarter start date and end date”. Because I wanted to exercise my brain a little, decided to help out and posted:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @min_date DATETIME, @max_date DATETIME;
SET @min_date = '1/1/2009';
SET @max_date = '12/31/2010';

-- Working Vars
SET @max_date = DATEADD(QUARTER, DATEDIFF(QUARTER,0,@max_date), 0) + 1;

WITH CTE_dummy AS (
    SELECT TOP(DATEDIFF(QUARTER, @min_date, @max_date) + 1) ROW_NUMBER() OVER(ORDER BY object_id) - 1 AS sequence from sys.columns
)
SELECT CASE WHEN (DATEPART(QUARTER, @min_date) + sequence) % 4 = 0 THEN 4 ELSE (DATEPART(QUARTER, @min_date) + sequence) % 4 END AS [Quarter],
    DATEADD(QUARTER, sequence, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) AS [Start_Date],
    DATEADD(QUARTER, sequence, DATEADD(QUARTER, 1, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) - 1) AS [End_Date]
FROM CTE_dummy;

For the solution I used the ROW_NUMBER() windowing function, but deals with dates truncation to tackle the core question.

Hope it helps.