Hidden Features of SQL Server

Question

On SO hidden features SQL Server we got a lot of useful stored procedures.

What hidden features should we know to tune our SQLServer database server?

PS Here is the similiar question about Oracle

Answer 1

To answer the actual question about tuning, rather than looking at internals, which can help you understand why SQL behaves in some ways, there's only 1 undocumented option I can think of.

You need to ensure that your transaction log file does not have too many VLFs inside it otherwise log operations will be slowed down. To view the number of VLFs you must use the undocumented DBCC LOGINFO command, and then take corrective action. Kimberly has a great blog post about it at http://sqlskills.com/blogs/Kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Apart from that ,there is no real magic to perf tuning. It's all about having the right design, the right indexing strategy, the right database maintenance, and re-evaluating it all over and over to make sure that your requirements are still met by all of the above. There's no /faster switch for SQL Server.

Hope this helps

Answer 2

There's the a whole bunch of undocumented DBCC commands such as:

DBCC PAGE and DBCC IND That let you peek inside the storage engine. Paul Randal has blogged about them!

Answer 3

SQL server internals viewer is a far more powerful tool than dbcc for peeking at the internals of how data is stored in SQL server. Especially for managing partitions, the allocation viewer provides an at a glance of what data is on which partition.

You can get it from here: [ url removed... google "Internals Viewer for SQL Server" site:codeplex.com ]

Answer 4

I'd echo Paul Randal that there's not much that isn't documented. The challenge is reading through the ungodly quantities of documentation. I've recapped the most important (yet not-so-obvious) ones in my SQL Server Setup Checklist:

http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/

Some examples include:

• Setting up security to take advantage of Instant File Initialization
• Aligning your partitions
• Use multipathing, and understand how it works
• Don't overcommit on memory (people RDP into boxes and run things, so plan for that)

Answer 5

The key to understanding SQL Server performance for any given query is to have an in depth understanding of query plans. Specifically understanding the types of joins used, the types of fetch steps (scans vs seeks) and the limitations of what the plans can tell you. Query plans almost always screw up the cost user defined functions in the SELECT list, for example. But if you understand how query plans work you will have an understanding of how SQL Server works, breaks, and how to fix it.

The key to understanding SQL Server server stability is understanding how to quickly read the error log. Not the windows event log, the actual, honest to god ERRORLOG text file.

The key to hiring a good SQL Server query writer is to ask them to write for you, with the correct syntax, how to use a cursor in T-SQL. If they get the answer right, do not hire them, they use cursors too much and cursors are nearly always the wrong approach.

Answer 6

IT's not undocumented, but one little know "hidden" feature is the Default Trace. Unless someone explicitly disables it, SQL Server 2005 and 2008 have a system Trace that by default begins at server start.

This can be very useful for answering "What Happened?" type questions after a performance issue. Just as good, it is invaluable for figuring out who was on your SQL Server at a particular time, after a security incident.

I learned about it from Jack Corbett in his article here:[1]. Jonathan M Kehayias did a Live Meeting for the PASS DBA SIG on it this past week also (see here:[2]).

The following script (also from Jack Corbett) shows how to see if the Default Trace is running on your SQL Server, and what the trace files are:

Select
    CAT.name as event_category,
    E.name as event_name,
    C.name as column_name,
    Case
        When FI.logical_operator = '0' Then 'AND'
        Else 'OR'
    End as logical_operator,
    Case   
        When FI.comparison_operator = 0 Then '='
        When FI.comparison_operator = 1 Then '<>'
        When FI.comparison_operator = 2 Then '>'
        When FI.comparison_operator = 3 Then '<'
        When FI.comparison_operator = 4 Then '>='
        When FI.comparison_operator = 5 Then '<='
        When FI.comparison_operator = 6 Then 'Like'
        When FI.comparison_operator = 7 Then 'Not Like'
    End as comparison_operator,
    FI.value as filter_value
From
    sys.traces T Cross Apply
    -- this function provides the details about the trace
    ::fn_trace_geteventinfo(T.id) EI Join
    sys.trace_events E On
        EI.eventid = E.trace_event_id Join
    sys.trace_columns C On
        EI.columnid = C.trace_column_id Join

    sys.trace_categories CAT On
        E.category_id = CAT.category_id Outer Apply
    --outer apply is like a left join as there may not be filters
    ::fn_trace_getfilterinfo(T.id) FI
--Optional
Where
    T.id = 1

-- RBarryYoung

(Note: I am not trusted with hyperlinks yet, so this is what I get instead)

1: www.sqlservercentral.com/scripts/Auditing/64335/

2: sqlblog.com/blogs/jonathan_kehayias/archive/2009/05/27/pass-dba-sig-default-trace-presentation-files.aspx