Performance Tuning

Introducing SQL Server Performance Tuning

Are you frustrated by poor SQL server performance (poor performance can affect the user experience, the business operations, and the scalability of your system)? If yes, then you have come to the right place. Here and now you will receive assistance in tuning the performance of the SQL server and full support with a detailed report on the work done. Our strategy can significantly affect the speed and efficiency of your application. We will unlock the full potential of your database. Get ready to overload your SQL server!

Query optimization is a critical technique for improving SQL Server performance. SQL query optimization involves identifying the most efficient and effective methods for improving query performance. There are several query optimization strategies that can be employed to achieve this goal. One common approach is to create appropriate indexes on columns used in WHERE, JOIN, and ORDER BY clauses to speed up data retrieval . Additionally, selecting only the necessary fields instead of using SELECT * and avoiding the use of DISTINCT in SELECT statements can also help to optimize query performance. By employing these techniques, SQL Server users can significantly improve the speed and efficiency of their queries, leading to better overall performance.
Indexing is another important technique for improving SQL Server performance. SQL Server indexes help to speed up data retrieval by providing a faster way to locate data within a table. There are several best practices for indexing in SQL Server, such as creating relevant indexes and avoiding over-indexing. Additionally, performance tuning tools can be used to identify and address indexing issues, such as recommending the creation of new indexes or the removal of unnecessary ones. By properly implementing indexing techniques, SQL Server users can ensure that their databases are running at peak performance levels.
Server configuration is also a critical factor in SQL Server performance tuning. Properly configuring the SQL Server instance and hardware can help to ensure that the server is running efficiently and effectively. For example, configuring the server's memory settings and processor affinity can help to optimize performance. Additionally, monitoring and performance tuning tasks can help to identify and isolate performance problems, such as bottlenecks or database performance issues. By following best practices for server configuration and monitoring, SQL Server users can optimize their server's performance and ensure that it is running at peak efficiency.

Techniques for improving SQL Server performance

In conclusion, SQL Server performance tuning is crucial for ensuring that database systems run efficiently and effectively. By implementing query optimization strategies, indexing techniques, and server configuration best practices, organizations can improve their SQL Server performance and ultimately enhance their overall business operations. It is important to regularly assess and fine-tune SQL Server performance to ensure that it meets the needs of the organization and its users. With the right techniques and strategies in place, organizations can optimize their SQL Server performance and achieve greater success in their business endeavors.

The sys.dm_exec_sessions DMV includes user sessions as well as internal SQL Server sessions used to run background tasks.
sys.dm_exec_connections contains additional information for sessions established by external clients, including protocol details.
sys.dm_exec_requests contains information about active SQL Server requests (e.g. executing queries).

Allocated memory view

Provides more detailed information about top 20 memory allocation and usage of specific database queries, along with additional fields
for data management purposes.Source tables of database perf_querystore from where take data:

perfsys.dm_exec_sessions – provides information about all active user connections and internal tasks.
This includes client information such as application and host names, security settings, configuration settings, and other session-specific data.
perfsys.dm_exec_requests – provides information about each request that is executing within SQL Server.
This includes requests from user sessions, system sessions and internal processes.
perfsys.dm_exec_query_memory_grants – provides information about all queries that have requested and are waiting for a memory grant or have been given
a memory grant. perfsys.dm_exec_sql_text – information about SQL query text according to query sql_handle.
dbo.tbl_query_store_plan_handle – represents the identifier for the memory location at which the actual execution plan for a given query is stored.

Script WhoTheFuckIsWrong.sql is powerful tool for performance analyze on SQL Server.

With script WhoTheFuckIsWrong.sql you can easy check which problem queries running on SQL Server in real-time.

In results of execution this script you will see all metrics to identify problem queries which take most resources of the SQL Server.

Some of most important metrics you can analyze in results of WhoTheFuckIsWrong.sql execution:

Granted memory – allocated memory on server for query
Used memory – fact used memory during query run
Reads – number of reads performed by query
Writes – number of writes performed by query
CPU time – show in milliseconds how long CPU was busy by query
Total elapsed time - show in milliseconds duration of query run
SQL Statement – is SQL text of query
SQL Plan – show Execution plan of query statement

You can also see from which server and application started query, and which user run the query.
Script WhoTheFuckIsWrong.sql is powerful tool for performance analyze on SQL Server.

Buy script ➜

Database perf_neartime

The database collects real-time data by capturing system activities and storing them in various tables. This data is saved every 5 minutes to ensure an up-to-date record of system performance. The process of data saving is initiated by a job named "Performance_Neartime" which executes a stored procedure called "StorePerformanceData" This job and stored procedure work together to retrieve the necessary information from the system and store it in the appropriate tables within the database.

The collected data provides valuable insights into the system's performance, allowing users to analyze and monitor various aspects of the system activities. By gathering data at regular intervals, it enables tracking changes and identifying trends over time. This information is crucial for system administrators and analysts to optimize system performance, troubleshoot issues, and make informed decisions regarding resource allocation and system configuration.

The data collected includes server information such as server name, version, login time, and session details. It also captures active requests, execution plans, database sizes, blocking sessions, workload group statistics, connected users, page allocation and deallocation activity, memory grants, executing queries, resource pool state, open transactions, wait status, scheduled jobs, and current memory usage. Each of these tables contains specific columns that hold relevant information related to the respective data category.

Performance tunung Overview Full Database perf_neartime

Database perf_querystore

The Realtime Data Collection for the Query Store feature offers valuable insights into query plan choices and performance for SQL Server. It greatly simplifies performance troubleshooting by enabling you to swiftly identify performance discrepancies caused by changes in the query plan. The Query Store automatically captures and retains a history of queries, plans, and runtime statistics for your review. The data saving process is initiated by a job named Performance_QueryStore. This job calls a set of stored procedures which save data from system tables of Query Store and Dynamical Management Views to tables of perf_querystore database. Saved data used to create reporting views and tables with information to analyse and define most load queries.

Performance tunung Overview Full Database perf_querystore

SQL Server Performance Tuning Best Practices

Before we dive into the specific tools and techniques for performance tuning, let's review some of the general best practices that can help you improve the performance of your SQL Server instances.

Use a consistent naming convention and coding style for your objects, variables, and queries. This will make your code more readable, maintainable, and easier to debug.
Avoid using unnecessary or redundant indexes, constraints, triggers, views, functions, and stored procedures. These can add overhead and complexity to your system, and affect the query execution plan and performance.
Use appropriate data types and sizes for your columns and variables. This will reduce the storage space and memory usage, and improve the data quality and integrity.
Use parameterized queries and stored procedures instead of dynamic SQL or concatenated strings. This will prevent SQL injection attacks, reduce the parsing and compilation time, and improve the query plan reuse and caching.
Use batch operations and bulk insert methods instead of row-by-row operations. This will reduce the network round trips and transaction log activity, and improve the throughput and concurrency.
Use proper indexing strategies and techniques for your tables and queries. This will improve the query performance by reducing the number of disk I/O operations and data scans.
Use query hints and options only when necessary and with caution. These can override the default behavior of the query optimizer and affect the query execution plan and performance. Some of these hints and options are: NOLOCK, RECOMPILE, OPTIMIZE FOR, MAXDOP, etc.
Monitor and analyze the performance metrics and counters of your SQL Server instances regularly. This will help you identify the baseline performance, detect any anomalies or bottlenecks, and troubleshoot any issues.

SQL Server Performance Tuning Tools

SQL Server provides a number of tools and features that can help you with performance tuning. Some of these tools are:

SQL Server Management Studio (SSMS): This is the primary tool for managing and administering your SQL Server instances. It provides a graphical user interface (GUI) for performing various tasks, such as creating and modifying objects, executing queries, viewing results, monitoring performance, etc.
SQL Server Profiler: This is a tool for capturing and analyzing the events that occur on your SQL Server instances. It allows you to trace the activity of your server, such as queries executed, errors occurred, transactions committed or rolled back, etc. You can use this tool to identify the queries that consume the most resources or cause performance issues.
Database Engine Tuning Advisor (DTA): This is a tool for analyzing your database schema and workload, and recommending optimal indexes, statistics, partitions, etc. It uses the trace data collected by SQL Server Profiler or other sources to generate tuning recommendations.
Query Store: This is a feature that collects and stores the query execution plans and performance metrics for your databases. It allows you to track the history of your queries, compare different plans and metrics over time, identify any plan changes or regressions, etc.
Extended Events: This is a feature that provides a lightweight and flexible framework for collecting and analyzing the events that occur on your SQL Server instances. It allows you to create custom sessions with various events, targets, actions, filters, etc. You can use this feature to monitor various aspects of your server performance, such as waits, locks, deadlocks, memory usage, etc.
Dynamic Management Views (DMVs) and Functions (DMFs): These are system views and functions that expose various information about your SQL Server instances. They allow you to query various aspects of your server state, configuration, performance, etc. Some of these views and functions are: sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_os_wait_stats, sys.dm_os_performance_counters, sys.dm_db_index_usage_stats, etc.

SQL Server Performance Tuning Techniques

In addition to using the tools mentioned above, there are some specific techniques that you can use to tune the performance of your SQL Server instances. Some of these techniques are:

Query Optimization: This involves writing efficient queries that return the desired results with minimal resource consumption. You can use various methods to optimize your queries,such as:

Using joins instead of subqueries or correlated subqueries
Using EXISTS instead of IN or NOT IN
Using WHERE clauses to filter out unnecessary data
Using ORDER BY clauses only when needed
Using GROUP BY clauses with aggregate functions instead of DISTINCT
Using common table expressions (CTEs) or temporary tables instead of derived tables
Using set-based operations instead of cursors or loops
Using UNION ALL instead of UNION
Using appropriate operators and functions, such as LIKE, BETWEEN, ISNULL, COALESCE, etc.

Index Optimization: This involves creating and maintaining optimal indexes for your tables and queries. You can use various methods to optimize your indexes, such as:

Choosing the right index type, such as clustered, nonclustered, filtered, columnstore, etc.
Choosing the right index columns, such as the ones used in the WHERE, JOIN, ORDER BY, or GROUP BY clauses.
Choosing the right index order, such as ascending or descending.
Choosing the right index options, such as fill factor, compression, partitioning, etc.
Updating the index statistics regularly to reflect the data changes.
Rebuilding or reorganizing the index fragmentation periodically to improve the index performance.
Dropping the unused or duplicate indexes to reduce the index maintenance overhead.

Database Optimization: This involves designing and maintaining optimal database structures and settings for your system. You can use various methods to optimize your database, such as:

Choosing the right data types and sizes for your columns and variables
Choosing the right collation and compatibility level for your database
Choosing the right recovery model and backup strategy for your database
Choosing the right filegroup and file layout for your database
Choosing the right partitioning scheme and strategy for your database
Choosing the right compression option and level for your database
Normalizing or denormalizing your database schema according to your needs

Conclusion

SQL Server performance tuning is a complex and ongoing process that requires a lot of knowledge and experience. However, by following some of the best practices and tools mentioned in this blog post, you can improve the performance of your SQL Server instances significantly. Remember to always test your changes in a development or staging environment before applying them to production, and monitor the impact of your changes on the system performance. Happy tuning!

Documentation

Database perf_neartime.docx (1,52 MB)

Database perf_neartime_red.docx (1,79 MB)

Query store results description.docx (359,29 KB)

Database perf_querystore.docx (3,95 MB)

SQL Server Performance Tuning is a complex and painstaking process. Entrust this work to professionals.

I will gladly assist you in optimizing and setting up your company's database.

Just call +41 (0)58 590 10 04 or contact us in any convenient way.

our Services for you

Performance Tuning

Introducing SQL Server Performance Tuning

User Agreement