Sql-server – Hidden Features of SQL Server

sql server

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

Best Answer

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

Related Topic