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 Rapid SQL is the intelligent SQL IDE empowering database developers and DBAs the ability to create high-performing SQL code on all major databases from a single interface. This toolset simplifies SQL scripting, query building, object management, debugging and version control with intuitive, innovative tools.