Author: Embarcadero USA
Here are 10 quick SQL Server tips and tricks for database developers compiled by SQL Server expert Pinal Dave.
- For inefficient query plans: Check for issues with bad cardinality estimates.
- The maximum degree of parallelism can be limited server-wide by using the max degree of parallelism option.
- DMV – sys.dm_os_nodes provides information about CPU node configuration for SQL Server.
- Slow query can be because of Missing indexes can force table scans and slow down the query.
- Plan guides can be created for ad hoc queries as well as queries inside a stored procedure.
- On both 32-bit and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out.
- Entities with the same query_hash value have a high probability of referring to the same query text.
- The query hash is computed from the tree structure produced during compilation.
- A change in the cardinality of a table variable does not cause a recompilation.
- You can use Performance Monitor and SQL Server Profiler to detect excessive compilation and recompilation.
Take your SQL Development to the Next Level
Embarcadero