The SET STATISTICS TIME command is used in order to identify parse, compile, and execution times that occur on the server-side. It is important to note, these times do not include the time it takes to transfer the data to the client. In order to turn on, simple execute the statement: SET STATISTICS TIME ON [...]
Archive of posts filed under the SQL Optimization category.
How to Write Optimized Stored Procedures
There are many factors to speeding up stored procedures. Knowing these in advance can save you and your company many hours of headaches. Using these practices when writing procedures, you can turn your SQL Server into a fine-tuned machine. This tutorial will cover the specifics to help you tune your procedures optimally. Know your tables [...]
Temp Table Recompiles
If you landed on this article, then you most likely know that temp tables can cause recompilation. This happens because temp tables are treated just like regular tables by the SQL Server Engine. When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change (using auto update statistics) and marks [...]
Performance Counters
The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness. Memory Object: – Memory Counter: – Available Mbytes Preferred Value: – > 2000MB Description: With 64 bit OS’s [...]
