Page History
...
- Reduce the number of SQL re-compilations since they are CPU intensive. There are many reasons that an object such as a stored procedure is recompiled and you can remove most of these reasons by careful coding. See the SQL Re-compilations counter for ways to dramatically reduce recompiles.
- Make sure that all T-SQL statements (whether in a stored procedure, trigger, or ad hoc statement) that reference objects fully qualify the object referenced. For example:
SELECT * FROM Northwind.dbo Employees
is a fully-qualified object reference whereasSELECT * FROM Employees
is a poorly-qualified object. You can reuse the execution plans of fully-qualified objects "as is," whereas plans where you either cannot reuse the not fully-qualified objects or, if they are reused, then they are subject to a highly restrictiveCOMPILE
lock while SQL Server determines if all of the objects referenced in the T-SQL code have the same owners as the execution plan currently in cache. Both of these situations consume a significant amount of CPU time. - Stop unnecessary programs (such as the Print Spooler or activities such as the database server acting as either a BDC or PDC) from running.
Tip |
---|
Create an alert response bundle with the OS Disk Time Per Disk (Percent) alert and related alerts. For additional information, see Create alert response bundles. |
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
...