Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share

locked by voretaq7 Feb 27 '12 at 22:39

This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.

    
This list by Brad McGehee has some very good info! simple-talk.com/sql/database-administration/… –  Peter Gfader May 29 '09 at 1:26
    
Can you tag this as Community Wiki? –  squillman May 29 '09 at 2:24

6 Answers 6

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

share

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 ]

share
1  
Beware that it doesn't show everything correctly, and what do you think it uses under the covers to get the information? DBCC PAGE. –  Paul Randal May 29 '09 at 15:14
    
it's pretty though. Looks always count for something. –  Nick Kavadias May 29 '09 at 16:39
    
Never heard of that one before. Thanks –  Peter Gfader May 30 '09 at 3:23

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!

share

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)
share

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.

share

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

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.