Hidden Features of SQL Server

Solution 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

Solution 2:

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 ]