Average Salary

You should know what you are worth, and you can increase your worth to any company you work for by increasing your knowledge and ability through training.

Salary Guide

This image, showing average salary for a Senior Database Administrator helps you understand where your current salary fits into the range of salaries  for this position, swell as the top-end of pay for this position, in US Dollars.

You can find more at sites like this and this.

Advertisements

Optimize Memory for Indexing in SQL Server 2008

By default, SQL Server 2008 dynamically manages the amount of memory allocated for index creation operations. If additional memory is needed for creating indexes, and the memory is available based on the server memory configuration settings, the server will allocate additional memory for index creation operations. If additional memory is needed but not available, index creation will use the memory already allocated to perform index creation.

Normally, SQL Server self-tuning works very well with this feature. The main exception is in cases in which you use partitioned tables and indexes and have nonaligned partitioned indexes. In these cases, if there is a high degree of parallelism (lots of simultaneous index creation operations), you might encounter problems creating indexes. If this happens, you can allocate a specific amount of index creation memory.

To use a specific index creation memory allocation, complete the following steps:

1. In the Server Properties dialog box, go to the Memory page and set a value in the Index Creation Memory box. This value is set in kilobytes.
2. Click OK.

You can also use the stored procedure sp_configure to set the index creation memory size. The related commands are as follows:

T-SQL

exec sp_configure "index create memory", 
    <number of kilobytes>
Windows PowerShell
Invoke-Sqlcmd -Query "exec sp_configure 'index create memory', 
    <num kb>"-ServerInstance "Server\Instance"
Note that the amount of memory allocated to index creation operations should be at least as large as the minimum memory per query. If it is not, SQL Server will use the amount of memory specified as the minimum memory per query and display a warning about this.

Top 10 SQL Server DBA Interview Questions

[UPDATE: See the updated list of 20 questions here.]

The subject of interview questions is an interesting one from both the hiring perspective as well as those looking for a new position. As a manager tasked with hiring a new DBA (senior dba, junior dba, etc.), you want to do a good job and that means asking the correct questions. As someone looking for a new job, knowing what kinds of questions you may be asked is important because you want to be as prepared as possible to give the best answers.

There are several sources for sample questions out there, many of them are listed on the internet. I love the article by Brent Ozars’s team, and I suggest you read the entire article.

10. SOLVE THE FIZZBUZZ PROBLEM WITH SQL CODE.

9. I’M A MANAGER, AND YOU’RE MY SENIOR DBA. EXPLAIN TO ME WHY WE SHOULDN’T SWITCH TO MYSQL OR ORACLE.

8. I’M A DEVELOPER. EXPLAIN WHY I NEED A UNIQUE KEY ON MY TABLE.

7. A PROJECT MANAGER NEEDS A NEW SQL SERVER. WHAT DO YOU ASK HER?

6. WHEN AND WHERE DOES THE LOCAL USER GROUP MEET?

5. CAN YOU GIVE ME REFERENCES FROM OTHER DBAS AND DEVELOPERS WHO AREN’T AT YOUR COMPANY?

4. HOW DO YOU LEARN NEW THINGS?

3. WHAT THIRD PARTY DATABASE TOOLS ARE YOUR FAVORITES?

2. ASK STRESSFUL QUESTIONS.

1. WHY ARE YOU HERE?

I suggest you ask the questions targeted at the type of person you want to hire, not only targeted at job function but also personality. As a person looking for a job, always come prepared to ask you own questions.

Setting Up Alerts for Long-Running Transactions

If you have a long running transaction, you might want to send out send out an alert. You will need to define values for what amount of time constitutes a long running transaction (shown as 10 minutes in the example script), who is to get the email (sent to the General email address in the example script) and what operator is to be notified (Alerts in the operator in the example script). Once you’ve specified all of the parameters as needed you’ll then want to create a new SQL Server Agent job that runs every few minutes (where the actual frequency will depend upon your @AlertingThresholdMinutes value) so that you’ll be notified if/when something goes over your specified threshold. Don’t forget to test, test, test.

/* NOTE: You have to configure/set the following 3 variables */
DECLARE @AlertingThresholdMinutes int = 10;
DECLARE @MailProfileToSendVia sysname = 'General';
DECLARE @OperatorName sysname = 'Alerts';

-------------------------------------------------------------
SET NOCOUNT ON;

DECLARE @LongestRunningTransaction int;
SELECT
        @LongestRunningTransaction = 
                MAX(DATEDIFF(n, dtat.transaction_begin_time, GETDATE())) 
FROM 
        sys.dm_tran_active_transactions dtat 
        INNER JOIN sys.dm_tran_session_transactions dtst 
                ON dtat.transaction_id = dtst.transaction_id;

IF ISNULL(@LongestRunningTransaction,0) > @AlertingThresholdMinutes BEGIN 

        DECLARE @Warning nvarchar(800);
        DECLARE @Subject nvarchar(100);

        SET @subject = '[Warning] Long Running Transaction On ' + @@SERVERNAME;
        SET @Warning = 'Check SSMS > Server > Reports > Top Transactions By Age.';
        
        EXEC msdb..sp_notify_operator
                @profile_name = @MailProfileToSendVia,
                @name = @OperatorName,
                @subject = @subject, 
                @body = @warning;
END

Script Database Mail Setup in SQL Server using PowerShell

You can use a PowerShell script to enable SQL Server’s Database Mail feature, as shown in this article on the SQL Server Magazine site.

 

Build Your Own Google?

PageRank is the original algorithm of Google, and this page shows you how to do ranking in SQL Server using some basic concepts.

pagerank-funny

You could rank the entire internet in only 100 passes.

A little more information on this page.

The Bizzaro Guide to SQL Server Performance

This article is now not to do performance tuning, also known as “The World’s Worst Performance Tuning Advice“, provided as an April’s Fool Day joke from 2010.