Category: FromSitesTree.com

Check that only the required columns are selected [in select statements] Check that the right types of joins are used Check that if re-ordering the joins will help or not Check the indexes of the tables [is indexing done on the right columns] Check that in joins you used the indexed columns in the join …

Continue reading

Query Optimization How to optimize query response time? SQL Server How to optimize SQL Server Cursors What are cursors? SQL Server Hints Scalability: SQL Server 2005 Scaling Out Dynamic Management Views SQL Server Database System Tuning SQL Server: Index SQL Server : Backups From: http://sitestree.com/?p=5221 Categories:38Tags: Post Data:2012-10-04 19:57:27 Shop Online: <a href=’https://www.ShopForSoul.com/’ target=’new’ rel=”noopener”>https://www.ShopForSoul.com/</a> …

Continue reading

Dynamic Management Views provide ways to analyze database performance and to find the cause behind bad-performing SQL server databases/instances. Bad performance can be due to: poor design, insufficient memory, poorly configured system, disk bottlenecks, poorly written queries and many others Previously Windows System Monitor(perfmon.exe) and SQL server profiler were used to find the cause of …

Continue reading

Database tuning approach Proactive approach Reactive approach Best Approach: Use a mix of both. Be aware (collect information) of the followings that may have significant impact on the performance. Hardware: Server, Available memory, number of processors, disk subsystems. Network Infrastructure: Network Cards, Switches, and the rest of your LAN and WAN. Operating System: Make sure …

Continue reading

SQL Server: Index Indexes make database access faster. Without index your queries will run but indexes can increase the performance dramatically. You can define multiple indexes for a table and select the index that is required for current operations. Only one clustered index is supported for a table To get advantage of indexes you have …

Continue reading

Scalability: SQL Server 2005 After you have optimized your database design and the application design, you can improve the scalability of SQL Server in two ways1.Scaling Up2.Scaling Out Scaling Up: Improve performance by 1. Adding more processors, memory, and storage capacity 2. Replacing existing hardware with faster hardware Note: You also need the right version …

Continue reading

Methods SQL Server instances Clustering Database mirroring Log shipping Replication Shared scalable databases SQL Server instancesIn the same computer install multiple instance of SQL Server to take advantage of multiple processors. Each instance may use separate processor with separate lock manager, worker threads, tempdb system database. ClusteringFailover Clustering can provide robustness and protection against failure. …

Continue reading

SQL Server Hints —————- What are hints? Simply to influence query execution plans to retrieve data faster. syscacheobjects —————- SQL server after compiling a query creates the execution plan and keeps it on syscacheobjects object. You can query the object to see the existing query plans as follows: select * from sys.syscacheobjects where cacheobjtype=’compiled plan’ …

Continue reading

Cursors are server side database objects that are used by applications to apply operations on the database table data on a row-by-row basis. The operations may vary from one row to another row dynamically based on the requirements (business logic) and also multiple operations can be performed on the same row. Typical SQL commands apply …

Continue reading

Optimize Cursors Avoid using SQL Server cursors whenever possible Always close SQL Server cursors when result sets are not needed Deallocate SQL Server cursors when the data structures comprising the cursors are not needed Reduce the number of records to process in the cursor Only use the required columns in the cursors Use READ ONLY …

Continue reading