If you talk to Sarah (twitter), she’ll tell you that I like lists.  Lists are awesome – they are almost like non-structured data.  They are hierarchies of information that I can work with.  They are Data.

Microsoft must have known I liked lists because over the weekend I discovered (see below) their SQL Server Best Practice Whitepapers list.

If you are looking to shore up your environment and skills, this is the place to start.  Check it out – you’ll find some great stuff here.

UPDATE:

By discover above… I should have mentioned that I saw it on a post by Mark Broadbent (blog | twitter).  Attribution is always a good thing and I feel bad that I missed that one.

One of SQL Server 2008 features I’ve been especially thrilled about is the Management Data Warehouse (MDW).  Just recently, Microsoft released a white paper  by Kalen Delany that goes into a bit a depth on MDW and covers some best practices and things to watch out for.

Happy reading!

One of the things I get to do pretty often is to visit with clients and discuss how they’ve implemented their SQL Server environment.  And if the title of this post is any indication of the contents there are a number of issues that I tend to be able to make recommendations on in all of these environments.

Below are five of the most common tempdb items that I come across:

1. Just a single tempdb data file: As a rule of thumb, Tempdb should have as many data files as there are CPUs/cores for the SQL Server instance.  This prevents helps contention that can occur during peak tempdb activity.  Of course, too many files can be a problem as well so if you are thinking of adding more than 8 data files, look at doing some testing to verify the additional files are not causing performance issues of their own.
2. Multiple tempdb transaction logs: The transaction log, of course, serves a much different purpose than the data files.  It’s there to log all of the transactions from tempdb and performs this function sequentially.  No matter how many files you have only one will be active at any time.  Having extra files won’t necessarily change performance or how tempdb behaves but it’s not going to provide any benefit.
3. Size Doesn’t Matter: The data files for tempdb should all be the same size and large enough to handle the workload for all 24 hours of the day.  If one of the files grows for some reason, the other files should be grown as well to keep all of the files the same size.  This helps SQL Server distribute the work more better evenly across the tempdb data files.  Also, there are a number of operations that utilize tempdb – make sure you have the room.  Skipping DBCC CHECKS because tempdb is too small is a recipe for disaster a la mode.
4. Tempdb and user database data files sharing a disk: Tempdb should be separated and isolated from user databases.  This creates contention between the user databases and tempdb.  Contention with temporary objects when executing a query and reading data from a user data typically has one unfortunate result… poor performance.  And no one likes that…
5. Tempdb on the slowest disk: Ugh!  Just because the databases on the server aren’t creating temporary tables doesn’t mean tempdb isn’t being used.  Queries that use operations such as Sort, Hash Match, and Spool will use tempdb and the faster the disk for tempdb the faster the performance of the query.  Tempdb has a lot of activity from all of the databases on the server and if it can’t push it’s IO quickly nearly everything will suffer.

I’ve kept these items fairly short and without a lot of detail because if you are doing any of these you need to read up on tempdb performance in Books Online and read the whitepaper Working with Tempdb.  Tempdb is a crucial resource in the SQL Server environment and it’s configuration should be a first thought rather than an after thought to prevent from having it bite you in the face.

Taking care of these kinds of easy configuration issues will help make certain that your tempdb is Harder, Better, Faster, Stronger.

While working on some parallelism issues today I started using the query that detects whether there are queries currently running utilizing parallelism.  It’s the same query that can be found in the white paper Troubleshooting Performance Problems in SQL Server 2005.

Which I’ve included below:

SELECT 

    r.session_id,

    r.request_id,

    MAX(ISNULL(exec_context_id, 0)) as number_of_workers,

    r.sql_handle,

    r.statement_start_offset,

    r.statement_end_offset,

    r.plan_handle

FROM 

    sys.dm_exec_requests r

    JOIN sys.dm_os_tasks t ON r.session_id = t.session_id

    JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id

WHERE 

    s.is_user_process = 0x1

GROUP BY 

    r.session_id, r.request_id, 

    r.sql_handle, r.plan_handle, 

    r.statement_start_offset, r.statement_end_offset

HAVING MAX(ISNULL(exec_context_id, 0)) > 0

It sure is to know the queries currently executing using parallelism.  But the query doesn’t provide enough information when it executes to take some immediate action without further research.  The query below provides this additional information.

WITH CurrentParallelism

AS (

    SELECT 

        r.session_id

        ,r.request_id

        ,r.database_id

        ,MAX(ISNULL(exec_context_id, 0)) as number_of_workers

        ,r.sql_handle

        ,r.statement_start_offset

        ,r.statement_end_offset

        ,r.plan_handle

    FROM sys.dm_exec_requests r

        INNER JOIN sys.dm_os_tasks t on r.session_id = t.session_id

        INNER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id

    WHERE s.is_user_process = 0x1

    GROUP BY 

        r.session_id, r.request_id, r.database_id,

        r.sql_handle, r.plan_handle, 

        r.statement_start_offset, r.statement_end_offset

    HAVING MAX(ISNULL(exec_context_id, 0)) > 0

)

SELECT

    cp.session_id

    ,cp.request_id

    ,db_name(cp.database_id) AS database_name

    ,cp.number_of_workers

    ,CASE statement_end_offset WHEN -1 THEN q.text

        ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement

    ,p.query_plan

    ,q.text

    ,cp.plan_handle

FROM CurrentParallelism cp

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

The information that I like to have on hand is the exact statement and query plan that is executing so that I know what it is that I am dealing with.  Hopefully others feel the same and find some use in this.

I keep hearing this story and similar variations…

”On a dark and stormy night, I developed a SQL Server Integration Services (SSIS) package.  It worked wonderfully on my desktop development environment.  All the files were properly accessed and the data was processed in a magnificent matter.”

”But suddenly I deployed the SSIS package to production environment, or sometimes even the test environment first.  And BAM! the SQL Agent job I created to execute the package would fail.”

”I laughed and cried and checked all of the settings and permissions and everything is identical to the development environment.  I just don’t know what is going wrong.  Help me Obi-Won, you’re my only hope.”

The first thing I do any time I hear this story is to tell the developer that I think it’s really weird to actually say “open parenthesis” and “close parenthesis”.  But, right after that I check the execution log for the package.  The execution log will usually have all this verbiage around it not being able to this, that, or the other thing with some external resource that the SSIS package is referencing.

But the developer had stated that everything is identical to the development environment but this, of course, doesn’t mean that the SQL Agent account is actually the same between the two environments.  And this is where the problem often lies.  In the development environment the package was executed by a power user, the developer, that could access anything the developer wanted.  In nice and secure SQL environments, the SQL Agent service account will have minimal permissions within SQL Server and no permissions outside of the server that it doesn’t require.

New package.  New requirements.  New network permissions.  New proxy.

As I mentioned, the account executing the SSIS package has different between the environments.  What if I could run the SSIS package on the production environment with the SAME permissions I ran the package with in the development environment.  SQL Server 2005 and 2008 allows for this through the use of CREDENTIALS.  Books Online defines CREDENTIALS as such:

A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. This information is used internally by SQL Server. Most credentials contain a Windows user name and password.

The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. However, a SQL Server login can be mapped to only one credential.

To execute a SQL Agent job under the developers account the following four things need to be done:

1. Create the CREDENTIAL using CREATE CREDENTIAL.

-- Create a credential with the account Domain\User and its password

USE [master]

GO

CREATE CREDENTIAL MyCredential WITH IDENTITY = N'Domain\User', SECRET = N'Password'

GO

2. Create a proxy that references the CREDENTIAL using msdb.dbo.sp_add_proxy.

-- Create a proxy and assign the credential to it

USE [msdb]

GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MySSISProxy',@credential_name=N'MyCredential',@enabled=1

GO

3. Grant the proxy access to the SSIS subsystem using msdb.dbo.sp_grant_proxy_to_subsystem.

-- Grant proxy access to the 'SSIS package execution' subsystem, aka #11

USE [msdb]

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MySSISProxy', @subsystem_id=11

GO

4. Assign the Run As value for the SQL Agent Job Step to the proxy.

Often by going through these steps I’m able to show that the package that runs in the developer’s development environment under his login will also run in the production environment under his login.   Now identical really means identical and through either adding permissions to the SQL Agent service account or, even better, creating a CREDENTIAL with the appropriate permissions that package can be executed as intended.

Nice and Secure Environments

I should note that using the developer’s login is a temporary troubleshooting technique.  The developer’s login should not be used on an ongoing basis for the SQL Agent job.  This will open up a whole host of issues such as password resets and proxy accounts that stop working with developers logins are disabled.

And as I mentioned above, some people like nice and secure SQL Agent environments.  I am a big fan of those as well.  CREDENTIALS are a great tool that should be used to keep SQL Agent secure.  By limiting the scope of the SQL Agent service accounts, SQL Agent can be prevented from accessing resources and data that it shouldn’t normally access.  I’m not going to get too much into security best practices around this other than to say if restricting SQL Agent permissions isn’t a current consideration you should read the SQL Server 2005 Security Best Practices – Operational and Administrative Tasks white paper.

Tagged

I’ve been tagged…  Jason Massie tagged me this week and I’ll be responding to it in Monday’s blog.

I was working through some performance issues on a "fun" table.  A bit of half a billion rows and with some apparent I/O issues as the entire table is being read by some of the clients services to create an extract.  In working through some of the performance bottlenecks, I found the following white paper quite helpful… of course, the name of the white paper indicates how helpful it was… Troubleshooting Performance Problems in SQL Server 2005.

A good read on the benefits of going to Enterprise Edition on SQL Server 2005… sure I contributed to the white paper but that would never color my vision… Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition.

There is a new white paper out regarding connectivity with SSIS to various data sources.  These include connections to SQL Server, DB2, Oracle, Access, Excel, and other file-based database systems.  There is some nice information in there.

The summary from the white paper:

This white paper enumerates the connectivity options for databases with SQL Server 2005 Integration Services and mentions special considerations that are required when using some database sources with some of the more complex transformations.

How quickly a week goes by… minute by minute… get a little busy and no blogs.

Well, here’s a quick link to a white paper on Online Index Operations in 2005.  A very nice 2005 Enterprise feature that allows building and maintaining of indexes without sacrificing DML concurrency.  Or in other words, how to keep from completely locking a production table with index maintenance.